隐藏在各项配置文件之后的，是 MySQL 的思想和设计方案。
查看当前所有配置
show variables;一、表相关
表数据独立存储
表数据既可以存在共享表空间里，也可以是单独的文件。这个行为是由参数 innodb_file_per_table 控制的，从 MySQL 5.6.6 版本开始，它的默认值就是 ON 了。
这个参数设置为 OFF 表示的是，表的数据放在系统共享表空间，也就是跟数据字典放在一起；
这个参数设置为 ON 表示的是，每个 InnoDB 表数据存储在一个以 .ibd 为后缀的文件中。
我建议你不论使用 MySQL 的哪个版本，都将这个值设置为 ON。
因为，一个表单独存储为一个文件更容易管理，而且在你不需要这个表的时候，通过 drop/truncate table 命令，系统就会直接删除这个文件。而如果是放在共享表空间中，即使表删掉了，空间也是不会回收的。
innodb_file_per_table = ON二、日志相关
bin log 格式
如果是使用 delete 语句误删了数据行，可以用 Flashback 工具通过闪回把数据恢复回来。Flashback 恢复数据的原理，是修改 binlog 的内容，拿回原库重放。而能够使用这个方案的前提是，需要确保以下两个配置。
binlog_format=row
binlog_row_image=FULLbin log 文件体积限制 / 自动过期
row 格式记录的数据更完整，在数据恢复方面有极大优势，但缺点是生成的日志量大。因此需要根据业务需求和服务器配置对 binlog 设置单个文件最大体积限制和保留时长的限制。
expire_logs_days = 30
max_binlog_size = 512M双 “1” 配置
一个事务完整提交前，需要等待两次刷盘，一次是 redo log（prepare 阶段），一次是 binlog。 sync_binlog 这个参数设置成 1 的时候，表示每次事务的 binlog 都持久化到磁盘。这个参数我也建议你设置成 1，这样可以保证 MySQL 异常重启之后 binlog 不丢失。
redo log 用于保证 crash-safe 能力，innodb_flush_log_at_trx_commit 这个参数设置成 1 的时候，表示每次事务的 redo log 都直接持久化到磁盘。这个参数我建议你设置成 1，这样可以保证 MySQL 异常重启之后 redo log 数据不丢失。
sync_binlog = 1
innodb_flush_log_at_trx_commit = 1redo log 文件体积 / 个数
利用 WAL 技术，MySQL 将随机写转换成了顺序写，大大提升了数据库的性能。但是，由此也带来了内存脏页的问题。脏页会被后台线程自动 flush，也会由于数据页淘汰而触发 flush，而刷脏页的过程由于会占用资源，可能会让你的更新和查询语句的响应时间长一些，造成性能的间歇性下降。
“redo log 写满了，要 flush 脏页”，这种情况是 InnoDB 要尽量避免的。因为出现这种情况的时候，整个系统就不能再接受更新了，所有的更新都必须堵住。如果你从监控上看，这时候更新数会跌为 0。 一个内存配置为 128GB、innodb_io_capacity 设置为 20000 的大规格实例，正常会建议你将 redo log 设置成 4 个 1GB 的文件。
这两个属性是只读的，需要通过修改配置文件并重启 MySQL 生效。
innodb_log_file_size=512M
innodb_log_files_in_group=2三、buffer 相关
缓冲池大小
缓冲池内存大小不够，意味着可用的数据页少，脏页比例容易接近 75%，造成性能间歇性抖动。
innodb_buffer_pool_size=1Gjoin 联表查询缓冲池大小
join_buffer 的大小是由参数 join_buffer_size 设定的，默认值是 256k。如果放不下表 t1 的所有数据话，策略很简单，就是分段放。如果 join 语句较慢，有可能是因为缓冲池过小。
join_buffer_size=32M四、硬盘 IO 相关
IO 读写能力
如果没能合理地设置 innodb_io_capacity 参数，会导致一些性能问题。比如 MySQL 的写入速度很慢，TPS 很低，但是数据库主机的 IO 压力并不大。
如果主机磁盘用的是 SSD，但是 innodb_io_capacity 的值设置的是 300。于是，InnoDB 认为这个系统的能力就这么差，所以刷脏页刷得特别慢，甚至比脏页生成的速度还慢，这样就造成了脏页累积，影响了查询和更新性能。
测试完成后可以得到服务器硬盘的 IOPS，根据这个值来设置：（200 是默认值）
innodb_io_capacity = 200centos 安装 fio，测试硬盘 io
|
windows 可以使用 AS SSD 软件测试 IOPS
脏页刷写连坐机制
一旦一个查询请求需要在执行过程中先 flush 掉一个脏页时，这个查询就可能要比平时慢了。而 MySQL 中的一个机制，可能让你的查询会更慢：在准备刷一个脏页的时候，如果这个数据页旁边的数据页刚好是脏页，就会把这个 “邻居” 也带着一起刷掉；而且这个把 “邻居” 拖下水的逻辑还可以继续蔓延，也就是对于每个邻居数据页，如果跟它相邻的数据页也还是脏页的话，也会被放到一起刷。在 InnoDB 中，innodb_flush_neighbors 参数就是用来控制这个行为的，值为 1 的时候会有上述的 “连坐” 机制，值为 0 时表示不找邻居，自己刷自己的。
找 “邻居” 这个优化在机械硬盘时代是很有意义的，可以减少很多随机 IO。机械硬盘的随机 IOPS 一般只有几百，相同的逻辑操作减少随机 IO 就意味着系统性能的大幅度提升。
而如果使用的是 SSD 这类 IOPS 比较高的设备的话，建议把 innodb_flush_neighbors 的值设置成 0：
因为这时候 IOPS 往往不是瓶颈，而 “只刷自己”，就能更快地执行完必要的刷脏页操作，减少 SQL 语句响应时间。在 MySQL 8.0 中，innodb_flush_neighbors 参数的默认值已经是 0 了。
innodb_flush_neighbors = 0五、线程相关
并发线程上限数
通常情况下，我们建议把 innodb_thread_concurrency 设置为 64~128 之间的值。并发连接和并发查询并不是同一个概念，你在 show processlist 的结果里，看到的几千个连接，指的就是并发连接。而 “当前正在执行” 的语句，才是我们所说的并发查询。
并发连接数达到几千个影响并不大，就是多占一些内存而已。我们应该关注的是并发查询，因为并发查询太高才是 CPU 杀手。这也是为什么我们需要设置 innodb_thread_concurrency 参数的原因。
在线程进入锁等待以后，并发线程的计数会减一，也就是说等行锁（也包括间隙锁）的线程是不算在 128 里面的。MySQL 这样设计是非常有意义的。因为，进入锁等待的线程已经不吃 CPU 了；更重要的是，必须这么设计，才能避免整个系统锁死。
innodb_thread_concurrency=128六、锁相关
自增锁模式
MySQL 5.1.22 版本引入了一个新策略，新增参数 innodb_autoinc_lock_mode，默认值是 1。
这个参数的值被设置为 0 时，表示采用之前 MySQL 5.0 版本的策略，即语句执行结束后才释放锁；
这个参数的值被设置为 1 时：普通 insert 语句，自增锁在申请之后就马上释放；类似 insert … select 这样的批量插入数据的语句，自增锁还是要等语句结束后才被释放；
这个参数的值被设置为 2 时，所有的申请自增主键的动作都是申请后就释放锁。
在生产上，尤其是有 insert … select 这种批量插入数据的场景时，从并发插入数据性能的角度考虑，我建议你这样设置： innodb_autoinc_lock_mode=2 ，并且 binlog_format=row. 这样做，既能提升并发性，又不会出现数据一致性问题。
innodb_autoinc_lock_mode=2七、SQL 部分
安全模式
为了防止忘记在 delete 或者 update 语句中写 where 条件、where 条件里面没有包含索引字段、没有加入 limit 限制引起的非安全 SQL 语句造成数据误删等，写入该配置后，这些非安全语句执行就会报错。
sql_safe_updates = ON配置模板
innodb_file_per_table = ON
binlog_format=row
binlog_row_image=FULL
expire_logs_days = 30
max_binlog_size = 512M
sync_binlog = 1
innodb_flush_log_at_trx_commit = 1
innodb_log_file_size=512M
innodb_log_files_in_group=2
innodb_buffer_pool_size=1G
join_buffer_size=32M
innodb_io_capacity = 300
innodb_flush_neighbors = 0
innodb_thread_concurrency=128
innodb_autoinc_lock_mode=2
评论