38讲数据库参数设置优化，失之毫厘差之千⾥

你好，我是刘超。

MySQL是⼀个灵活性⽐较强的数据库系统，提供了很多可配置参数，便于我们根据应⽤和服务器硬件来做定制化数据库服 务。如果现在让你回想，你可能觉得在开发的过程中很少去调整MySQL的配置参数，但我今天想说的是我们很有必要去深⼊了解它们。

我们知道，数据库主要是⽤来存取数据的，⽽存取数据涉及到了磁盘I/O的读写操作，所以数据库系统主要的性能瓶颈就是I/O 读写的瓶颈了。MySQL数据库为了减少磁盘I/O的读写操作，应⽤了⼤量内存管理来优化数据库操作，包括内存优化查询、排序以及写⼊操作。

也许你会想，我们把内存设置得越⼤越好，数据刷新到磁盘越快越好，不就对了吗？其实不然，内存设置过⼤，同样会带来新的问题。例如，InnoDB中的数据和索引缓存，如果设置过⼤，就会引发SWAP⻚交换。还有数据写⼊到磁盘也不是越快越
好，我们期望的是在⾼并发时，数据能均匀地写⼊到磁盘中，从⽽避免I/O性能瓶颈。

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

所以，这些参数的设置跟我们的应⽤服务特性以及服务器硬件有很⼤的关系。MySQL是⼀个⾼定制化的数据库，我们可以根
据需求来调整参数，定制性能最优的数据库。

不过想要了解这些参数的具体作⽤，我们先得了解数据库的结构以及不同存储引擎的⼯作原理。

MySQL体系结构

我们⼀般可以将MySQL的结构分为四层，最上层为客户端连接器，主要包括了数据库连接、授权认证、安全管理等，该层引
⽤了线程池，为接⼊的连接请求提⾼线程处理效率。

第⼆层是Server层，主要实现SQL的⼀些基础功能，包括SQL解析、优化、执⾏以及缓存等，其中与我们这⼀讲主要相关的就
是缓存。

第三层包括了各种存储引擎，主要负责数据的存取，这⼀层涉及到的Buffer缓存，也和这⼀讲密切相关。最下⾯⼀层是数据存储层，主要负责将数据存储在⽂件系统中，并完成与存储引擎的交互。
接下来我们再来了解下，当数据接收到⼀个SQL语句时，是如何处理的。

查询语句

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

解析器主要对SQL进⾏语法以及词法分析，之后，便会进⼊到优化器中，优化器会⽣成多种执⾏计划⽅案，并选择最优⽅案执
⾏。

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

更新语句

数据库更新SQL的执⾏流程其实跟查询SQL差不多，只不过执⾏更新操作的时候多了记录⽇志的步骤。在执⾏更新操作时
MySQL会将操作的⽇志记录到 binlog（归档⽇志）中，这个步骤所有的存储引擎都有。⽽InnoDB除了要记录 binlog 之外，还需要多记录⼀个 redo log（重做⽇志）。

redo log 主要是为了解决 crash-safe 问题⽽引⼊的。我们知道，当数据库在存储数据时发⽣异常重启，我们需要保证存储的数据要么存储成功，要么存储失败，也就是不会出现数据丢失的情况，这就是crash-safe了。

我们在执⾏更新操作时，⾸先会查询相关的数据，之后通过执⾏器执⾏更新操作，并将执⾏结果写⼊到内存中，同时记录更新操作到redo log的缓存中，此时redo log中的记录状态为prepare，并通知执⾏器更新完成，随时可以提交事务。执⾏器收到通知后会执⾏binlog的写⼊操作，此时的binlog是记录在缓存中的，写⼊成功后会调⽤引擎的提交事务接⼝，更新记录状态为
commit。之后，内存中的redo log以及binlog都会刷新到磁盘⽂件中。

内存调优

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

很明显，Query Cache可以优化查询SQL语句，减少⼤量⼯作，特别是减少了I/O读取操作。我们可以通过以下⼏个主要的设置参数来优化查询操作：

我们可以通过设置合适的 query_cache_min_res_unit 来减少碎⽚，这个参数最合适的⼤⼩和应⽤程序查询结果的平均⼤⼩直接相关，可以通过以下公式计算所得：

（query_cache_size - Qcache_free_memory）/ Qcache_queries_in_cache

Qcache_free_memory 和 Qcache_queries_in_cache 的值可以通过以下命令查询：

show status like ‘Qcache%’
Query Cache虽然可以优化查询操作，但也仅限于不常修改的数据，如果⼀张表数据经常进⾏新增、更新和删除操作，则会造成Query Cache的失效率⾮常⾼，从⽽导致频繁地清除Cache中的数据，给系统增加额外的性能开销。

这也会导致缓存命中率⾮常低，我们可以通过以上查询状态的命令查看 Qcache_hits，该值表示缓存命中率。如果缓存命中率

特别低的话，我们还可以通过query_cache_size = 0或者query_cache_type来关闭查询缓存。

经过了Query Cache缓存之后，还会使⽤到存储引擎中的Buffer缓存。不同的存储引擎，使⽤的Buffer也是不⼀样的。这⾥我们主要讲解两种常⽤的存储引擎。

MyISAM存储引擎参数设置调优

MyISAM存储引擎使⽤key buffer缓存索引块，MyISAM表的数据块则没有缓存，它是直接存储在磁盘⽂件中的。

我们可以通过key_buffer_size设置key buffer缓存的⼤⼩，⽽它的⼤⼩并不是越⼤越好。正如我前⾯所讲的，key buffer缓存设置过⼤，实际应⽤却不⼤的话，就容易造成内存浪费，⽽且系统也容易发⽣SWAP⻚交换，⼀般我是建议将服务器内存中可⽤内存的1/4分配给key buffer。

如果要更准确地评估key buffer的设置是否合理，我们还可以通过缓存使⽤率公式来计算： 1-((key_blocks_unusedkey_cache_block_size)/key_buffer_size)
key_blocks_unused表示未使⽤的缓存簇（blocks）数
key_cache_block_size表示key_buffer_size被分割的区域⼤⼩key_blocks_unusedkey_cache_block_size则表示剩余的可
⽤缓存空间（⼀般来说，缓存使⽤率在80%作⽤⽐较合适）。

InnoDB存储引擎参数设置调优

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

我们⼀般可以通过多个设置参数来调整IBP，优化InnoDB表性能。

innodb_buffer_pool_size

IBP默认的内存⼤⼩是128M，我们可以通过参数innodb_buffer_pool_size来设置IBP的⼤⼩，IBP设置得越⼤，InnoDB表性能就越好。但是，将IBP⼤⼩设置得过⼤也不好，可能会导致系统发⽣SWAP⻚交换。所以我们需要在IBP⼤⼩和其它系统服务所需内存⼤⼩之间取得平衡。MySQL推荐配置IBP的⼤⼩为服务器物理内存的80%。

我们也可以通过计算InnoDB缓冲池的命中率来调整IBP⼤⼩：

(1-innodb_buffer_pool_reads/innodb_buffer_pool_read_request)*100

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

innodb_buffer_pool_instances

InnoDB中的IBP缓冲池被划分为了多个实例，对于具有数千兆字节的缓冲池的系统来说，将缓冲池划分为单独的实例可以减少不同线程读取和写⼊缓存⻚⾯时的争⽤，从⽽提⾼系统的并发性。该参数项仅在将innodb_buffer_pool_size设置为1GB或更⼤时才会⽣效。

在windows 32位操作系统中，如果innodb_buffer_pool_size的⼤⼩超过1.3GB，innodb_buffer_pool_instances默认⼤⼩就为
innodb_buffer_pool_size/128MB；否则，默认为1。

⽽在其它操作系统中，如果innodb_buffer_pool_size⼤⼩超过1GB，innodb_buffer_pool_instances值就默认为8；否则，默认为1。

为了获取最佳效率，建议指定innodb_buffer_pool_instances的⼤⼩，并保证每个缓冲池实例⾄少有1GB内存。通常，建议
innodb_buffer_pool_instances的⼤⼩不超过innodb_read_io_threads + innodb_write_io_threads之和，建议实例和线程数量
⽐例为1:1。

innodb_read_io_threads / innodb_write_io_threads

在默认情况下，MySQL后台线程包括了主线程、IO线程、锁线程以及监控线程等，其中读写线程属于IO线程，主要负责数据库的读取和写⼊操作，这些线程分别读取和写⼊innodb_buffer_pool_instances创建的各个内存⻚⾯。MySQL⽀持配置多个读写线程，即通过innodb_read_io_threads和innodb_write_io_threads设置读写线程数量。

读写线程数量值默认为4，也就是总共有8个线程同时在后台运⾏。innodb_read_io_threads和innodb_write_io_threads设置的读写线程数量，与innodb_buffer_pool_instances的⼤⼩有关，两者的协同优化是提⾼系统性能的⼀个关键因素。

在⼀些内存以及CPU内核超⼤型的数据库服务器上，我们可以在保证⾜够⼤的IBP内存的前提下，通过以下公式，协同增加缓存实例数量以及读写线程。

( innodb_read_io_threads + innodb_write_io_threads ) = innodb_buffe_pool_instances

如果我们仅仅是将读写线程根据缓存实例数量对半来分，即读线程和写线程各为实例⼤⼩的⼀半，肯定是不合理的。例如我们的应⽤服务读取数据库的数据多于写⼊数据库的数据，那么增加写⼊线程反⽽没有优化效果。我们⼀般可以通过MySQL服务 器保存的全局统计信息，来确定系统的读取和写⼊⽐率。

我们可以通过以下查询来确定读写⽐率：

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

如果读⼤于写，我们应该考虑将读线程的数量设置得⼤⼀些，写线程数量⼩⼀些；否则，反之。

innodblogfile_size

除了以上InnoDB缓存等因素之外，InnoDB的⽇志缓存⼤⼩、⽇志⽂件⼤⼩以及⽇志⽂件持久化到磁盘的策略都影响着
InnnoDB的性能。 InnoDB中有⼀个redo log⽂件，InnoDB⽤它来存储服务器处理的每个写请求的重做活动。执⾏的每个写⼊查询都会在⽇志⽂件中获得重做条⽬，以便在发⽣崩溃时可以恢复更改。

当⽇志⽂件⼤⼩已经超过我们参数设置的⽇志⽂件⼤⼩时，InnoDB会⾃动切换到另外⼀个⽇志⽂件，由于重做⽇志是⼀个循环使⽤的环，在切换时，就需要将新的⽇志⽂件脏⻚的缓存数据刷新到磁盘中（触发检查点）。

理论上来说，innodblogfile_size设置得越⼤，缓冲池中需要的检查点刷新活动就越少，从⽽节省磁盘I/O。那是不是将这个
⽇志⽂件设置得越⼤越好呢？如果⽇志⽂件设置得太⼤，恢复时间就会变⻓，这样不便于DBA管理。在⼤多数情况下，我们将
⽇志⽂件⼤⼩设置为1GB就⾜够了。

innodb_log_buffer_size

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

innodb_flush_log_at_trx_commit

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

当设置该参数为0时，InnoDB每秒种就会触发⼀次缓存⽇志写⼊到⽂件中并刷新到磁盘的操作，这有可能在数据库崩溃后，丢失1s的数据。

当设置该参数为 1 时，则表示每次事务的 redo log 都会直接持久化到磁盘中，这样可以保证 MySQL 异常重启之后数据不会丢失。

当设置该参数为 2 时，每次事务的 redo log 都会直接写⼊到⽂件中，再将⽂件刷新到磁盘。

在⼀些对数据安全性要求⽐较⾼的场景中，显然该值需要设置为1；⽽在⼀些可以容忍数据库崩溃时丢失1s数据的场景中，我们可以将该值设置为0或2，这样可以明显地减少⽇志同步到磁盘的I/O操作。

总结

MySQL数据库的参数设置⾮常多，今天我们仅仅是了解了与内存优化相关的参数设置。除了这些参数设置，我们还有⼀些常
⽤的提⾼MySQL并发的相关参数设置，总结如下：

思考题

我们知道，InnoDB的IBP的内存⼤⼩是有限的，你知道InnoDB是如何将热点数据留在内存中，淘汰⾮热点数据的吗？ 期待在留⾔区看到你的答案。也欢迎你点击“请朋友读”，把今天的内容分享给身边的朋友，邀请他⼀起讨论。

    精选留⾔         <br />![](https://cdn.nlark.com/yuque/0/2022/png/1852637/1646315680239-a40c8914-d492-4a40-911a-6b82e1f5437c.png#)张学磊<br />通过在内存中维护⼀个链表，并使⽤LRU(最近最少使⽤)算法淘汰⾮热点数据<br />2019-08-20 00:43<br />作者回复<br />对的，MySQL基于LRU算法来实现淘汰⾮热点数据，但与我们熟悉的LRU算法不同的是，MySQL新增了⼀个midpoint insertion startegy策略，就是默认情况下，读取到的新⻚并不是直接放⼊的LRU列表的⾸部，⽽是LRU列表⻓度的5/8处，⽬的是为了避免由于⼀些不常查询SQL偶尔⼀次查询就把之前热点数据淘汰的情况。<br />2019-08-20 09:50

许童童
我来回答⼀下思考题：
InnoDB 的缓存淘汰策略是分代的，分为⽼年代和新⽣代，⼤致是7：3，新数据总是先加⼊到新⽣代，如果在⼀定时间内有被再次查询，才会进⼊到⽼年代。⽼年代和新⽣代内部都是LRU算法来淘汰缓存⻚的。
2019-08-20 14:42

-W.LI-
⽼师好!问个问题，这些IO操作，是同步阻塞，还是同步⾮阻塞啊?
2019-08-20 08:47
超威⼂
其实内存分配也是分了新⽼区，防⽌⼤量的历史数据数据的查询占⽤整个内存，热点数据缓存命中率降低，不⽌是LRU淘汰这么简单
2019-08-20 08:10

vic
这个专栏的综合质量真的很⾼， 不懂为什么订阅量这么少。
2019-08-21 08:16

LW
IBP通过LRU链表管理热点数据和淘汰⾮热点数据，LRU本身分为两⼤区域，young区域和old区域，热点数据在young区，⾮热 点数据在old区。其实LRU的划分还更细致，young区还划分成⼏个不同的区域。
2019-08-20 10:35
晓杰
mysql的缓冲池（buffer_pool）使⽤改进版的lru算法来缓存数据，如果使⽤传统的lru算法，会存在预读失效和缓冲池污染的问 题，为了解决预读失效问题，mysql将lru分为新⽣代和⽼⽣代，为了解决缓冲池污染的问题，引⼊⽼⽣代停留时间窗⼝，只有
⼤于设置的值，才能加⼊新⽣代头部
2019-08-21 14:30
作者回复
解释的⽐我全⾯，赞
2019-08-24 10:41

晓杰
⽼师，redo log和bin log好像不是在更新语句提交事务成功就刷新到磁盘的。
2019-08-21 11:29
作者回复
redo log是根据innodb_flush_log_at_trx_commit参数来设置刷新到磁盘的策略。
2019-08-22 10:15

晓杰
mysql8.0已经没有⽤查询缓存了
2019-08-21 11:10
作者回复
是的，MySQL8.0版本已经不⽀持查询缓存，我们⽂中也提到了其中的弊端。MySQL官⽅建议使⽤服务器端使⽤缓存或ProxyS
QL作为中间缓存。
2019-08-22 10:08