【八股消消乐】MySQL参数优化大汇总

😊你好，我是小航，一个正在变秃、变强的文艺倾年。
🔔本专栏《八股消消乐》旨在记录个人所背的八股文，包括Java/Go开发、Vue开发、系统架构、大模型开发、具身智能、机器学习、深度学习、力扣算法等相关知识点，期待与你一同探索、学习、进步，一起卷起来叭！

题目

💬技术栈：MySQL

🔍简历内容：熟悉MySQL体系结构，了解SQL语句处理底层，熟悉常用的MySQL参数调优手段。

🚩面试问：你了解SWAP 页交换吗？InnoDB 的 IBP 的内存大小是有限的，它是如何将热点数据留在内存中，淘汰非热点数据的？

---

💡建议暂停思考10s，你有答案了嘛？如果你有不同题解，欢迎评论区留言、打卡。

---

答案

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

（2）MySQL基于LRU算法来实现淘汰非热点数据，但与我们熟悉的LRU算法不同的是，MySQL新增了一个midpoint insertion startegy策略，就是默认情况下，读取到的新页并不是直接放入的LRU列表的首部，而是LRU列表长度的5/8处，目的是为了避免由于一些不常查询SQL偶尔一次查询就把之前热点数据淘汰的情况。

MySQL体系结构

MySQL体系结构分为四层：

（1）客户端连接器：数据库连接、授权认证、安全管理等，该层引用了线程池，为接入的连接请求提高线程处理效率。
（2）Server 层：实现 SQL 的一些基础功能，包括 SQL 解析、优化、执行以及缓存等。
（3）各种存储引擎：主要负责数据的存取，例如 Buffer 缓存。
（4）数据存储层：负责将数据存储在文件系统中，并完成与存储引擎的交互。

SQL语句处理

（1）查询语句

• 通过第一层的连接和授权认证。
• 将 SQL 请求发送至 SQL 接口。
• SQL 接口接收到请求之后，会先检查查询 SQL 是否命中 Cache 缓存中的数据，如果命中，则直接返回缓存中的结果；否则，需要进入解析器。
• 解析器主要对 SQL 进行语法以及词法分析。
• 之后进入到优化器中，优化器会生成多种执行计划方案，并选择最优方案执行。
• 执行器检查连接用户是否有该表的执行权限，有则查看 Buffer 中是否存在该缓存，存在则获取锁，查询表数据；否则重新打开表文件，通过接口调用相应的存储引擎处理，这时存储引擎就会进入到存储文件系统中获取相应的数据，并返回结果集。

（2）更新语句

• 和查询语句差不多，只不过执行更新操作时多了一个记录日志的步骤。
• 执行更新操作时 MySQL 会将操作的日志记录到 binlog（归档日志）【这个步骤所有的存储引擎都有，InnoDB 除了要记录 binlog 之外，还需要多记录一个 redo log（重做日志）】
  • redo log 主要是为了解决 crash-safe 问题而引入的。
  • crash-safe 问题：当数据库在存储数据时发生异常重启，我们需要保证存储的数据要么存储成功，要么存储失败，也就是不会出现数据丢失的情况。
• 执行更新操作时，首先会查询相关的数据，之后通过执行器执行更新操作，并将执行结果写入到内存中，同时记录更新操作到 redo log 的缓存中，此时 redo log 中的记录状态为 prepare，并通知执行器更新完成，随时可以提交事务。
• 执行器收到通知后会执行 binlog 的写入操作，此时的 binlog 是记录在缓存中的，写入成功后会调用引擎的提交事务接口，更新记录状态为 commit。
• 内存中的 redo log 以及 binlog 都会刷新到磁盘文件中。

内存调优

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

适用场景：仅限于不常修改的数据。如果一张表数据经常进行新增、更新和删除操作，则会造成 Query Cache 的失效率非常高，从而导致频繁地清除 Cache 中的数据，给系统增加额外的性能开销。

查询缓存命中率：

show status like 'Qcache%'

查看 Qcache_hits，如果缓存命中率特别低的话，可以通过 query_cache_size = 0 或者 query_cache_type 来关闭查询缓存。

相关参数：

可以通过设置合适的 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%'

（2）存储引擎中的 Buffer 缓存

不同的存储引擎，使用的 Buffer 也是不一样的。

（1）MyISAM 存储引擎参数设置调优

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

参数设置：通过 key_buffer_size 设置 key buffer 缓存的大小

它的大小并不是越大越好，key buffer 缓存设置过大，实际应用却不大的话，就容易造成内存浪费，而且系统也容易发生 SWAP 页交换。
建议将服务器内存中可用内存的 1/4 分配给 key buffer。
也可以通过缓存使用率公式计算：

1-((key_blocks_unused*key_cache_block_size)/key_buffer_size)key_blocks_unused 表示未使用的缓存簇（blocks）数
key_cache_block_size 表示 key_buffer_size 被分割的区域大小 
key_blocks_unused*key_cache_block_size 则表示剩余的可用缓存空间（一般来说，缓存使用率在 80% 作用比较合适）。

（2）InnoDB 存储引擎参数设置调优

InnoDB Buffer Pool（简称 IBP）是 InnoDB 存储引擎的一个缓冲池，与 MyISAM 存储引擎使用 key buffer 缓存不同，它不仅存储了表索引块，还存储了表数据。

• 查询数据时，IBP 允许快速返回频繁访问的数据，而无需访问磁盘文件。
• InnoDB 表空间缓存越多，MySQL 访问物理磁盘的频率就越低，这表示查询响应时间更快，系统的整体性能也有所提高。

参数设置：

• innodb_buffer_pool_size：IBP 默认的内存大小是 128M。MySQL 推荐配置 IBP 的大小为服务器物理内存的 80%。
  • 命中率公式：(1-innodb_buffer_pool_reads/innodb_buffer_pool_read_request)*100
  • 将 IBP 的大小设置为物理内存的 80% 以后，发现命中率还是很低，此时我们就应该考虑扩充内存来增加 IBP 的大小。
• innodb_buffer_pool_instances：将缓冲池划分为单独的实例可以减少不同线程读取和写入缓存页面时的争用，从而提高系统的并发性。
  • 该参数仅在将 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。

MySQL 后台线程包括了主线程、IO 线程、锁线程以及监控线程等。其中读写线程属于 IO 线程，主要负责数据库的读取和写入操作，这些线程分别读取和写入。

• innodb_read_io_threads / innodb_write_io_threads：MySQL 支持配置多个读写线程，即通过 innodb_read_io_threads 和 innodb_write_io_threads 设置读写线程数量。
  • 读写线程数量值默认为 4，也就是总共有 8 个线程同时在后台运行。
  • 协同增加缓存实例数量以及读写线程：( 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');// 写入数量

InnoDB 的日志缓存大小、日志文件大小以及日志文件持久化到磁盘的策略都影响着 InnnoDB 的性能
InnoDB 中有一个 redo log 文件，InnoDB 用它来存储服务器处理的每个写请求的重做活动。执行的每个写入查询都会在日志文件中获得重做条目，以便在发生崩溃时可以恢复更改。

• innodb_log_file_size：当日志文件大小已经超过我们参数设置的日志文件大小时，InnoDB 会自动切换到另外一个日志文件，由于重做日志是一个循环使用的环，在切换时，就需要将新的日志文件脏页的缓存数据刷新到磁盘中（触发检查点）。

  • 如果日志文件设置得太大，恢复时间就会变长，这样不便于 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 并发

---

往期精彩专栏内容，欢迎订阅：

🔗【八股消消乐】20250605：端午节产生的消费数据，如何分表分库？
🔗【八股消消乐】20250604：如何解决SQL线上死锁事故
🔗【八股消消乐】20250603：索引失效与优化方法总结
🔗【八股消消乐】20250512：慢SQL优化手段总结
🔗【八股消消乐】20250511：项目中如何排查内存持续上升问题
🔗【八股消消乐】20250510：项目中如何优化JVM内存分配？
🔗【八股消消乐】20250509：你在项目中如何优化垃圾回收机制？
🔗【八股消消乐】20250508：Java编译优化技术在项目中的应用
🔗【八股消消乐】20250507：你了解JVM内存模型吗？
🔗【八股消消乐】20250506：你是如何设置线程池大小？
🔗【八股消消乐】20250430：十分钟带背Duubo中大厂经典面试题
🔗【八股消消乐】20250429：你是如何在项目场景中选取最优并发容器？
🔗【八股消消乐】20250428：你是项目中如何优化多线程上下文切换？
🔗【八股消消乐】20250427：发送请求有遇到服务不可用吗？如何解决？

📌 [ 笔者 ]   文艺倾年
📃 [ 更新 ]   2025.6.6
❌ [ 勘误 ]   /* 暂无 */
📜 [ 声明 ]   由于作者水平有限，本文有错误和不准确之处在所难免，本人也很想知道这些错误，恳望读者批评指正！