MySQL 初始化推荐关注的参数
前言
新部署的 MySQL 实例如何配置？本 SOP 将提供一些 MySQL 关键参数及设置方法。
必须设置的参数
1. innodb_buffer_pool_size
对于 innodb 表引擎来说，用户数据和索引及系统元数据，都是以页的形式存储在表空间中，表空间是 innodb 对文件系统上一个或多个物理文件的抽象，也就是说数据到底还是存储在磁盘中的。但是磁盘的速度要比内存慢太多，速度跟不上 CPU 的计算速度，所以 innodb 引擎需要访问某个页的数据时，就会把完整的页全部加载的内存中（页大小默认 16 k）即使访问一个页的一行数据，也需要先把完整的页加载的内存中，Innodb 所有读写操作都是在内存中完成的，完成读写后 innodb 并不会立刻释放掉，而是先缓存起来，后面如果有请求需要用到这张页的话，就可以直接从内存读取，可以省去磁盘 IO 的开销。
所以 Buffer Pool 的大小对 MySQL 性能影响非常大，如果是专属的数据库服务器，官方文档建议将 innodb_buffer_pool 参数设置为物理内存 70% ～ 80%。
2. innodb_buffer_pool_instances
MySQL Configuring Multiple Buffer Pool Instances
该参数可以将 buffer pool 分成几个区，每个区用独立的锁保护，这样就减少了访问 buffer pool 时需要上闩锁的粒度，以提高性能。
参考 innodb_buffer_pool_size 的设置，每个缓冲池至少为 1 G。无特殊场景，经验值 4 个左右最佳。
3. innodb_log_file_size
这个值定义了日志文件的大小，innodb 日志文件的作用是用来保存 redo 日志。一个事务对于数据或索引的修改往往对应到表空间中的随机的位置，因此当刷新这些修改到磁盘中就会引起随机的 I/O，而随机的 I/O 往往比顺序的 I/O 更加昂贵的开销，因为随机的 I/O 需要更多的开销来定位到指定的位置。innodb 使用日志来将随机的 I/O 转为顺序的 I/O，只要日志文件是安全的，那么事务就是永久的，尽管这些改变还没有写到数据文件中，如果出现了当机或服务器断电的情况，那么 innodb 也可以通过日志文件来恢复以及提交的事务。但是日志文件是有一定的大小的，所以必须要把日志文件记录的改变写到数据文件中，innodb 对于日志文件的操作是循环的，即当日志文件写满后，会将指针重新移动到文件开始的地方重新写，但是它不会覆盖那些还没有写到数据文件中的日志，因为这是唯一记录了事务持久化的记录。如果业务更新非常频繁 Redo 很快被写满，此时写入会被短暂堵塞，必须等待 checkpoint 脏页刷完。如果对 Innodb 数据表有大量的写入操作，那么选择合适的 innodb_log_file_size 值对提升 MySQL 性能很重要。
MySQL 8.0 推出 innodb_dedicated_server 参数，开启后可以根据服务器的配置，自适应一些重要的参数，下图就是 log_file_size 设置逻辑。5.7 没有该参数，当遇到部署 MySQL 5.7 的需求，可以参考该逻辑初始化你的 MySQL。
可以参考上图进行配置，不一定是最优的，但肯定对初始化 MySQL 有益的。有一篇文章介绍了如何将 log_file 调整到最优，可以参考：如何计算一个好的 InnoDB 日志文件大小？
4. innodb_log_files_in_group
重做日志的组数，一般不会设置很多。
官方文档：The default (and recommended) value is 2 推荐和默认值都为 2。
5. innodb_log_buffer_size
尚未提交的事务缓冲区的大小，一般也不需要设置的很大，但是如果涉及到大字段事务，日志缓冲区很快会被填满造成额外的 I/O 消耗。
可以通过查询状态变量 show status like 'Innodb_log_waits'; 如果不为 0 则可以适当调大该参数。
默认 16M 建议值：32M
6. innodb_flush_neighbors
一旦一个查询请求需要在执行过程中先 flush 掉一个脏页时，这个查询就可能要比平时慢了。而 MySQL 中的一个机制，可能让你的查询会更慢：在准备刷一个脏页的时候，如果这个数据页旁边的数据页刚好是脏页，就会把这个“邻居”也带着一起刷掉；而且这个把“邻居”拖下水的逻辑还可以继续蔓延，也就是对于每个邻居数据页，如果跟它相邻的数据页也还是脏页的话，也会被放到一起刷。在 InnoDB 中，innodb_flush_neighbors 参数就是用来控制这个行为的，值为 1 的时候会有上述的 “连坐” 机制，值为 0 时表示不找邻居，自己刷自己的。
该参数 5.7 默认开启，8.0 版本默认关闭，传统机械硬盘可以使用，现在大多都是 SSD 盘，可以关闭。
7. innodb_undo_settings
下面介绍的是和 undo 相关的几个参数。为什么要将它也列入必须设置的参数里面呢？
Undo Log 是 InnoDB 十分重要的组成部分，它的作用横贯 InnoDB 中两个最主要的部分，并发控制（Concurrency Control）和故障恢复（Crash Recovery）在 MySQL 5.0 时代 Undo Log 存储在 ibdata1 共享表空间中，一些长事务可能会导致 Undo 暴涨，这部分空间可以重用却无法被回收，眼睁睁看着 ibdata1 越来越大，只能通过重建实例解决。MySQL 5.6 版本支持将 Undo Log 从共享表空间中剥离出来，单独存储，依然有空间无法回收的问题。5.7 版本提供了参数，开启后支持回收 Uodo 空间，8.0 默认会将 Undo Log 分离，且支持动态收缩和扩容。那么如果你是部署 MySQL 5.7 下面的参数需要你关注。
innodb_undo_tablespace：
Undo 表空间的数量，只能在初始化阶段设置，必须大于等于 2 才支持 Undo 页面截断。开启后默认会在数据目录生成两个 Uodo 文件，当然也可以使用innodb_undo_directory参数规划它的存储路径。
-rw-r----- 1 mysql mysql 10485760 Jun 22 14:04 undo001 -rw-r----- 1 mysql mysql 10485760 Jun 22 14:04 undo002
innodb_undo_log_truncate：
开启后 Uodo 表空间如果超过innodb_max_undo_log_size会自动 truncate。
详细可参考：MySQL Undo Log
如果你初始化的是 MySQL 5.7 版本，那么建议将 innodb_undo_tablespaces 调整为 >= 2 因为它在实例生命周期内是固定的。
8. innodb_io_capacity
innodb 线程执行的任务大多与磁盘 I/O 有关，例如从缓冲池中刷新脏页及从 Change Buffer 写入更改的二级索引。Innodb 会尝试不影响服务器正常工作的情况下完成这些任务，它会尝试估计服务器可用的 I/O 带宽，并调整任务可利用的容量。那这个估计的 I/O 能力的参考值就是 innodb_io_capacity一般建议你设置为磁盘的 IOPS。正确告诉 Innodb 所在主机的 IO 能力，这样 Innodb 才知道需要刷脏页的时候，可以刷多快。可以使用下方命令测试你磁盘的 IOPS。
fio -filename=$filename -direct=1 -iodepth 1 -thread -rw=randrw -ioengine=psync -bs=16k -size=500M -numjobs=10 -runtime=10 -group_reporting -name=mytest
9. max_connections
MySQL 的最大连接数限制，默认值为 151 如果经常遇到 Too many connections 异常，那就是该参数设置的太低了。
可以先设置为 1000 或更大，然后通过 show status like 'Max_used_connections'; 去查询实际使用的连接数，再修改为一个适合的值。
可选参数
1. query_cache_type
MySQL 查询缓存功能，5.7 版本默认已不开启，8.0 该功能已经删除，如果是初始化 5.6 版本记得关闭它。
执行的语句和结果会以 key-value 的形式存储在内存中。key 是查询的语句，value 是查询的结果。如果你的查询能够直接在这个缓存中找到 key， 那么这个 value 就会被直接返回给客户端。如果查询命中缓存，MySQL 不需要执行后面的复杂操作，就可以直接返回结果，这个效率会很高。但是大多数情 况下建议不要使用查询缓存，为什么呢? 因为查询缓存往往弊大于利。查询缓存的失效非常频繁，只要有对一个表的更新，这个表上所有的查询缓存都会被 清空。因此很可能你费劲地把结果存起来，还没使用呢，就被一个更新全清空了。对于更新压力大的数据库来说，查询缓存的命中率会非常低。除非你的业务就是有一张静态表，很长时间才会更新一次。比如，一个系统配置表，那这张表上的查询才适合使用查询缓存。
2. lower_case_table_names
MySQL 大小写是否敏感的参数，
在不同系统上运行，会有不同的默认值👇
需要注意的是 在 Linux 系统中不支持设置为 2 当用户设置为 2 时，将被强制改为 0。
官网文档：MySQL lower_case_table_names
3. log_timestamps
在 MySQL 5.7.2 新增了 log_timestamps 这个参数，该参数主要是控制 error log、genera log，等等记录日志的显示时间参数。 在 5.7.2 之后改参数为默认 UTC 这样会导致日志中记录的时间比中国这边的慢，导致查看日志不方便。修改为 SYSTEM 就能解决问题。
设置建议：log_timestamps = SYSTEM
4. slow_log
MySQL 慢日志相关的参数，生产环境建议开启。
slow_query_log=ON
slow_query_log_file=$path
long_query_time=1
log_slow_admin_statements=1
5. innodb_print_all_deadlocks
Innodb 支持行锁，死锁问题无法避免，如何监控死锁？可以通过开启 innodb_print_all_deadlocks 将死锁信息打印到错误日志中，可以起到监控死锁目的。
innodb_print_all_deadlocks = 1
后记
目前就想到了这么多，当然使用复制另说，欢迎补充。另外上面参数的设置思路集成到 MySQL 自动化部署 脚本中，使用它也可以快速搭建出合格的 MySQL 实例。
