青铜到王者，快速提升你 MySQL 数据库的段位！

正文

请到「今天看啥」查看全文

1. 通过参数 innodb_max_dirty_pages_pct 控制：它的含义代表脏页刷新占 buffer_pool 的比例；个人建议调整为 25-50%；

2. 日志切换会产生检查点 checkpoint，可以诱发对脏页的刷新

——线程工作：

Innodb 四大 IO 线程： write thread，read thread，insert buffer thread，redo log thread

master thread 是数据库的主线程，优先级别最高，里面包含 1s 和 10s 对数据库的操作。

page cleaner thread：帮助刷新脏页的线程，5.7 版本可以增加多个。

purge thread ：删除无用 undo 页。默认1个，最大可以调整到 32。

主要的数据文件也是我们需要学习：

参数文件：MySQL 5.6 版本 my.cnf 和 MySQL 5.7 版本的 my.cnf

这里给大家两个模板： 老张根据生产环境上测试而出的参数。其中根据真实内存去适当调整 innodb_buffer_pool 大小就可以了。（建议物理内存的50-80%）

[client]
port    = 3306
socket    = /tmp/mysql.sock
#default-character-set=utf8
 [mysql]
#default-character-set=utf8
[mysqld]
port    = 3306
socket    = /tmp/mysql.sock
basedir    = /usr/local/mysql
datadir    = /data/mysql
open_files_limit    = 3072
back_log = 103
max_connections = 512
max_connect_errors = 100000
table_open_cache = 512
external-locking = FALSE
max_allowed_packet = 128M
sort_buffer_size = 2M
join_buffer_size = 2M
thread_cache_size = 51
query_cache_size = 32M
tmp_table_size = 96M
max_heap_table_size = 96M
slow_query_log = 1
slow_query_log_file = /data/mysql/slow.log
log-error = /data/mysql/error.log
long_query_time = 0.05
server-id = 1323306
log-bin = /data/mysql/mysql-bin
sync_binlog = 1
binlog_cache_size = 4M
max_binlog_cache_size = 128M
max_binlog_size = 1024M
expire_logs_days = 7
key_buffer_size = 32M
read_buffer_size = 1M
read_rnd_buffer_size = 16M
bulk_insert_buffer_size = 64M
character-set-server=utf8
default-storage-engine=InnoDB
binlog_format=row
#gtid_mode=on
#log_slave_updates=1
#enforce_gtid_consistency=1
interactive_timeout=100
wait_timeout=100
transaction_isolation = REPEATABLE-READ
innodb_additional_mem_pool_size = 16M
innodb_buffer_pool_size = 1434M
innodb_data_file_path = ibdata1:1024M:autoextend
innodb_flush_log_at_trx_commit = 1
innodb_log_buffer_size = 16M
innodb_log_file_size = 256M
innodb_log_files_in_group = 2
innodb_max_dirty_pages_pct = 50
innodb_file_per_table = 1
innodb_locks_unsafe_for_binlog = 0
[mysqldump]
quick
max_allowed_packet = 32M

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; 选择性越高，越适合创建索引

3. 创建索引的过程中，学会查看执行计划。 内功心法： 先看 type 值，再看 key，再看 rows，最后看 extra；

mysql> use test; 
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> explain select * from sbtest;
+----+-------------+--------+------+---------------+------+---------+------+-------+-------+
| id | select_type | table  | type | possible_keys | key  | key_len | ref  | rows  | Extra |
+----+-------------+--------+------+---------------+------+---------+------+-------+-------+
|  1 | SIMPLE      | sbtest | ALL  | NULL          | NULL | NULL    | NULL | 98712 | NULL  | 
+----+-------------+--------+------+---------------+------+---------+------+-------+-------+

4. 了解创建索引的好处

a. 提高数据检索效率

b. 提高聚合函数效率

c. 提高排序效率

d. 个别时候可以避免回表

e. 减少多表关联时扫描行数

f. 主键、唯一索引可以作为约束

——对事务的学习

先要知道事务的四大特性（ACID）：

a. 原子性(Atomicity)

事务的原子性是指事务中包含的所有操作要么都做，要么都不做，保证数据库是一致的

b. 一致性(Consistency)

一致性是指数据库在事务操作前和事务处理后，其中的数据必须都满足业务规则约束.

c. 隔离性(Isolation)

隔离性是数据库允许多个并发事务同时对数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致.

d. 持久性(Durability)

事务处理结束后，对数据的修改就是永久的

熟悉 mysql 数据库四种事务隔离级别：

1. read uncommitted（RU）读未提交：

一个事务中，可以读取到其他事务未提交的变更

请到「今天看啥」查看全文