MySQL 参数设置优化

一、MySQL 体系结构

一般可以将 MySQL 的结构分为四层：

• 第一层是客户端连接器，主要包括了数据库连接、授权认证、安全管理等，该层引用了线程池，为接入的连接请求提高线程处理效率
• 第二层是 Server 层，主要实现 SQL 的一些基础功能，包括 SQL 解析、优化、执行以及缓存等
• 第三层包括了各种存储引擎，主要负责数据的存取，这一层涉及到的 Buffer 缓存
• 最下面一层是数据存储层，主要负责将数据存储在文件系统中，并完成与存储引擎的交互

以查询语句为例，我们看一下整体的查询流程：

一个应用服务需要通过第一层的连接和授权认证，再将 SQL 请求发送至 SQL 接口。SQL 接口接收到请求之后，会先检查查询 SQL 是否命中 Cache 缓存中的数据，如果命中，则直接返回缓存中的结果；否则，需要进入解析器

解析器主要对 SQL 进行语法以及词法分析，之后，便会进入到优化器中，优化器会生成多种执行计划方案，并选择最优方案执行

确定了最优执行计划方案之后，执行器会检查连接用户是否有该表的执行权限，有则查看 Buffer 中是否存在该缓存，存在则获取锁，查询表数据；否则重新打开表文件，通过接口调用相应的存储引擎处理，这时存储引擎就会进入到存储文件系统中获取相应的数据，并返回结果集

二、内存调优

2.1 QueryCache

SQL 执行过程，我们可以发现，在执行查询 SQL 语句时，会涉及到两个缓存。第一个缓存是刚进来时的 Query Cache，它缓存的是 SQL 语句和对应的结果集。这里的缓存是以查询 SQL 的 Hash 值为 key，返回结果集为 value 的键值对，判断一条 SQL 是否命中缓存，是通过匹配查询 SQL 的 Hash 值来实现的

我们可以通过以下几个主要的设置参数来优化查询操作：

我们可以通过 show status like 'Qcache%' 查询状态的命令查看 Qcache_hits，该值表示缓存命中率。如果缓存命中率特别低的话，我们还可以通过 query_cache_size = 0 或者 query_cache_type = OFF 来关闭查询缓存

2.2 Buffer Cache

经过了 Query Cache 缓存之后，还会使用到存储引擎中的 Buffer 缓存，我们这里重点看一下 InnoDB 存储引擎参数设置调优

InnoDB Buffer Pool（简称 IBP）是 InnoDB 存储引擎的一个缓冲池。它不仅存储了表索引块，还存储了表数据。查询数据时，IBP 允许快速返回频繁访问的数据，而无需访问磁盘文件。InnoDB 表空间缓存越多，MySQL 访问物理磁盘的频率就越低，这表示查询响应时间更快，系统的整体性能也有所提高

2.2.1 innodb_buffer_pool_size

默认的内存大小是 128M，我们可以通过参数 innodb_buffer_pool_size 来设置 IBP 的大小，IBP 设置得越大，InnoDB 表性能就越好（设置得过大可能会导致系统发生 SWAP 页交换）。MySQL 推荐配置 IBP 的大小为服务器物理内存的 80%

SWAP 页交换：SWAP 分区在系统的物理内存不够用的时候，就会把物理内存中的一部分空间释放出来，以供当前运行的程序使用。被释放的空间可能来自一些很长时间没有什么操作的程序，这些被释放的空间的数据被临时保存到 SWAP 分区中，等到那些程序要运行时，再从 SWAP 分区中恢复保存的数据到内存中

如果我们将 IBP 的大小设置为物理内存的 80% 以后，发现命中率还是很低，此时我们就应该考虑扩充内存来增加 IBP 的大小

2.2.2 innodb_buffer_pool_instances

InnoDB 中的 IBP 缓冲池被划分为了多个实例，对于具有数千兆字节的缓冲池的系统来说，将缓冲池划分为单独的实例可以减少不同线程读取和写入缓存页面时的争用，从而提高系统的并发性

该参数项仅在将 innodb_buffer_pool_size 设置为 1GB 或更大时才会生效

通常，建议 innodb_buffer_pool_instances 的大小不超过 innodb_read_io_threads + innodb_write_io_threads 之和，建议实例和线程数量比例为 1:1

innodb_read_io_threads 和 innodb_write_io_threads 通过 show variables 查看

2.2.3 innodb_read(write)_io_threads

在默认情况下，MySQL 后台线程包括了主线程、IO 线程、锁线程以及监控线程等

其中读写线程属于 IO 线程，主要负责读取和写入 innodb_buffer_pool_instances 创建的各个内存页面

innodb_read_io_threads 和 innodb_write_io_threads 设置的读写线程数量，与 innodb_buffer_pool_instances 的大小有关，两者的协同优化是提高系统性能的一个关键因素

实际工作中，我们要根据系统读写比率来调整读写线程的数量，可以通过以下命令查询读写比率：

//读取数量
SHOW GLOBAL STATUS LIKE 'Com_select';
//写入数量
SHOW GLOBAL STATUS WHERE Variable_name IN ('Com_insert', 'Com_update', 'Com_replace', 'Com_delete');

2.2.4 innodb_log_file_size

理论上来说，innodb_log_file_size 设置得越大，缓冲池中需要的检查点刷新活动就越少，从而节省磁盘 I/O。在大多数情况下，我们 将日志文件大小设置为 1GB 就足够了

2.2.5 innodb_log_buffer_size

这个参数决定了 InnoDB 重做日志缓冲池的大小，默认值为 8MB。如果高并发中存在大量的事务，该值设置得太小，就会增加写入磁盘的 I/O 操作。我们可以通过增大该参数来减少写入磁盘操作，从而提高并发时的事务性能

2.2.6 innodb_flush_log_at_trx_commit

这个参数可以控制重做日志从缓存写入文件刷新到磁盘中的策略，默认值为 1

• 该参数为 0 时，InnoDB 每秒种就会触发一次缓存日志写入到文件中并刷新到磁盘的操作，这有可能在数据库崩溃后，丢失 1s 的数据
• 该参数为 1 时，则表示每次事务的 redo log 都会直接持久化到磁盘中，这样可以保证 MySQL 异常重启之后数据不会丢失
• 该参数为 2 时，每次事务的 redo log 都会直接写入到文件中，再将文件刷新到磁盘

可以容忍数据库崩溃时丢失 1s 数据的场景中。可以将该值设置为 0 或 2，这样可以明显地减少日志同步到磁盘的 I/O 操作

参考

https://zhuanlan.zhihu.com/p/74045370

Java性能调优实战