青铜到王者，快速晋升你 MySQL 数据库的段位！
|
MySQL 5.7 版本的参数文件： [client] port = 3306 socket = /data/mysql/mysql.sock [mysql] prompt="u@db R:m:s [d]> " no-auto-rehash [mysqld] user = mysql port = 3306 basedir = /usr/local/mysql datadir = /data/mysql/ socket = /data/mysql/mysql.sock character-set-server = utf8mb4 skip_name_resolve = 1 open_files_limit = 65535 back_log = 1024 max_connections = 500 max_connect_errors = 1000000 table_open_cache = 1024 table_definition_cache = 1024 table_open_cache_instances = 64 thread_stack = 512K external-locking = FALSE max_allowed_packet = 32M sort_buffer_size = 4M join_buffer_size = 4M thread_cache_size = 768 query_cache_size = 0 query_cache_type = 0 interactive_timeout = 600 wait_timeout = 600 tmp_table_size = 32M max_heap_table_size = 32M slow_query_log = 1 slow_query_log_file = /data/mysql/slow.log log-error = /data/mysql/error.log long_query_time = 0.1 server-id = 3306101 log-bin = /data/mysql/mysql-binlog sync_binlog = 1 binlog_cache_size = 4M max_binlog_cache_size = 1G max_binlog_size = 1G expire_logs_days = 7 gtid_mode = on enforce_gtid_consistency = 1 log_slave_updates binlog_format = row relay_log_recovery = 1 relay-log-purge = 1 key_buffer_size = 32M read_buffer_size = 8M read_rnd_buffer_size = 4M bulk_insert_buffer_size = 64M lock_wait_timeout = 3600 explicit_defaults_for_timestamp = 1 innodb_thread_concurrency = 0 innodb_sync_spin_loops = 100 innodb_spin_wait_delay = 30 transaction_isolation = REPEATABLE-READ innodb_buffer_pool_size = 1024M innodb_buffer_pool_instances = 8 innodb_buffer_pool_load_at_startup = 1 innodb_buffer_pool_dump_at_shutdown = 1 innodb_data_file_path = ibdata1:1G:autoextend innodb_flush_log_at_trx_commit = 1 innodb_log_buffer_size = 32M innodb_log_file_size = 2G innodb_log_files_in_group = 2 innodb_max_undo_log_size = 4G innodb_io_capacity = 4000 innodb_io_capacity_max = 8000 innodb_flush_neighbors = 0 innodb_write_io_threads = 8 innodb_read_io_threads = 8 innodb_purge_threads = 4 innodb_page_cleaners = 4 innodb_open_files = 65535 innodb_max_dirty_pages_pct = 50 innodb_flush_method = O_DIRECT innodb_lru_scan_depth = 4000 innodb_checksum_algorithm = crc32 innodb_lock_wait_timeout = 10 innodb_rollback_on_timeout = 1 innodb_print_all_deadlocks = 1 innodb_file_per_table = 1 innodb_online_alter_log_max_size = 4G internal_tmp_disk_storage_engine = InnoDB innodb_stats_on_metadata = 0 innodb_status_file = 1 innodb_status_output = 0 innodb_status_output_locks = 0 performance_schema = 1 performance_schema_instrument = '%=on' [mysqldump] quick max_allowed_packet = 32M ——日记文件： 1. 错误日记 error log：对 mysql 启动，运行，封锁进程举办了记录。 2. 全量日记 general log：查询日记记录了全部对 mysql 数据库哀求的信息，岂论这些哀求是否获得了正确的执行。 3. 二进制日记 binlog：记录了对数据库执行变动的全部操纵。可是并不包罗 select 和 show 这类操纵。 4. 中继日记 relay log：主从同步，从库必要把主库转达过来的日记，记录到本身的 relay log 内里。 5. 慢查询日记 slow log：运行时刻高出某值的全部 sql 语句都记录到慢查询日记文件中。 ——对数据库的表计划也要进修清晰 数据范例的选择，首要参考官方文档。 ——数据碎片的清算 发生碎片的缘故起因： 1. 首要是由于对大表举办删除操纵； 2. 其次随机方法插入新数据，也许导致帮助索激发生大量的碎片； 清算碎片的要领： 1. 备份数据表，导入导出，删除旧表 2. 执行 alter table table_name engine=innodb； ——网络统计信息 担保统计信息的精确性，才气确保我们的 sql 执行打算精确。网络要领： 1. 重启 mysql 处事 2. 遍历 tables 表 ——进修分区表 分区表的种类： 1. range 2. list 3. hash 4. key ——进修对索引的熟悉 大抵分为： 1. 怎样查察数据库中索引：show index from table_name; 2. 学会查察数据库索引的选择性：select count(distinct c1)/count(*) from table_name; 选择性越高，越得当建设索引 （编辑：湖南网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
