大厂计划 | MySQL 高频面试题05：性能调优

目录

MySQL 的问题排查手段有哪些？

一条 SQL 语句执行很慢的原因？

MySQL 你是怎么调优的？

---

 

MySQL 的问题排查手段有哪些？

---

• show processlist：用来查看当前所有事务所在线程的情况。其中的 state 比较重要，可以判断事务所在线程是否被阻塞了。

• explain：用来查看当前 SQL 语句的执行计划。比较重要的有： key 是将会使用到的索引；rows 是预估的扫描行数。可以通过这些来判断当前 SQL 语句是否使用到了索引、索引是否有效、是否选错了索引。

• 慢查询日志：开启慢查询日志，查看慢查询日志中我们需要的 SQL 语句的情况。具体的话可以查到这条 SQL 语句执行的时间 Query_time，以及扫描的行数 Rows_examined。

一条 SQL 语句执行很慢的原因？

---

需要分两种情况讨论：1. 偶尔很慢 2.一直都很慢

偶尔很慢

第一种情况可能是后台线程在主动刷脏页导致的，也就是把脏页数据持久化到磁盘里

• 比如 redo log 日志写满了，写性能跌为 0；又或者是 InnoDB 的 Buffer Pool 缓冲池管理的内存中的干净页不够用了；这两种情况都会导致后台线程主动进行刷脏页的操作，进而影响性能。

• 解决办法是控制好 InnoDB 内存中的脏页比例，尽量不让它超过 75%：我们可以先通过 Innodb_buffer_pool_pages_dirty / Innodb_buffer_pool_pages_total 得到脏页比例，然后通过设置 innodb_io_capacity 参数，让 InnoDB 知道我们刷脏页可以有多快，脏页比例如果超过 75%，那这个参数就调大一点，刷得快一点，否则调小一点。但也不用太小，假如我们的磁盘 I/O 能力很强，但这个参数却设置得很小，那自然刷脏页就很慢，进而影响性能了。

第二种情况可能是执行的时候遇到了 MDL 写锁或者行级别的排它锁

• 典型的例子是查询的时候碰到了 MDL 写锁，由于整张表被锁住了，导致查询没办法立即返回，只能等待对表加了 MDL 写锁的事务提交后才能释放 MDL 写锁。可以通过 show processlist 命令，通过查看当前事务所在的线程的 state 下是否出现 Waiting for table metadata lock 来判断当前事务是否在等待 MDL 写锁。如果是的话，解决办法就是通过查询 sys.schema_table_lock_waits 表中 blocking_pid，把这个线程连接用 kill 命令断开就可以了。

• 其次是查询一条记录的时候碰到了行级别的排它锁，也就是写锁，同样会导致查询没办法立即返回，要等待对该行记录加了写锁的事务提交后才能释放写锁。同样可以用 show processlist 的 state 状态来判断是否遇到了行锁，然后通过查询sys.innodb_lock_waits 表中的 blocking_pid，把这个线程连接用 kill 命令断开就行了。

一直很慢

第一种情况可能是索引失效，也就是没有用上索引

• 可以通过 explain 命令来查看 SQL 语句的执行情况，看看其中的 key 是否有用上索引，如果没有，那就表示索引失效了，要走全表扫描，自然性能就变差了。这种情况就要好好检查我们的 SQL 语句，看看查询条件中的索引列字段是否进行了函数运算从而导致了索引失效。

如果不是索引失效，那第二种情况可能就是 MySQL 的优化器选错了索引

• 假设我们在执行查询语句的时候已经确定是用上了索引并且索引有效，但发现这条语句还是执行得很慢，我们就可以用 explain 命令来查看 key 判断是否是选错了索引导致的，比如查询语句的条件字段使用了索引 a 但 explain 命令中的 key 却为其它的索引或者为 null，表示走的全部扫描，那这个情况就是选错索引了。

• MySQL 的优化器选错索引有很大的几率是由于索引统计不准确造成的，从而导致 explain 预估的扫描行数 rows 会偏大。至于索引统计为什么会不准确，这是由于 MySQL 统计索引的时候是采样统计的，在频繁的删除和插入数据的场景下就很容易统计错误。

• 这个时候直接执行 analyze table + 表名 来重新统计索引信息即可解决问题。

• 或者也可以直接使用 force index(索引名) 来强行选择一个索引，起到矫正作用。

MySQL 你是怎么调优的？

---

我觉得对于开发者而言，调优的重点分五方面吧：

• 第一是索引优化。
• 第二是查询优化。

• 第三是遇到磁盘 I/O 瓶颈，考虑参数调优，优化磁盘 I/O。
• 第四是建立在前面两者的基础上如果读性能仍然有瓶颈，那可以考虑用 缓存 或者 搜索引擎。

• 第五是如果读和写性能都遇到了瓶颈，那就可以考虑将 MySQL 从单库的架构升至主从架构实现读写分离；如果仍然存在瓶颈那就要考虑分库分表。

索引优化

• 看看能否对文本用上前缀索引，比如用邮箱登录是一个常见的问题，如果对 email 整个字段建立索引，会让索引变得大且慢，这时我们可以索引开始的部分字符，这样可以大大节约索引空间，从而提高索引效率。

• 看看是否能够使用上覆盖索引，从而减少回表所消耗的时间：比如查询的时候只查需要的字段，而不查全部，并将需要的字段加上索引，从而使用到了覆盖索引，减少回表操作。

• 如果使用了联合索引，应该考虑到最左匹配原则，将区分度比较高的索引放在左边。

• 再者就是要注意索引的失效问题以及 MySQL 优化器可能选错索引的问题。可以通过 explain 命令查看其中的 key 字段来做判断，并进行相应的矫正。
• 如果是索引失效，则要检查 SQL 语句条件字段中的索引是否参与了函数运算。

• 如果是选错了索引，则应判断是什么原因导致的。如果是因为频繁的增加和删除数据导致索引统计不准确，可以使用 analyze table + 表名 的操作来重新统计索引；也可以直接使用 force index(索引) 来强制选择一个我们需要的索引。

查询优化

关键是重构查询方式

切分大查询

一个大查询如果一次性执行的话，可能一次锁住很多数据、占满整个事务日志、耗尽系统资源、阻塞很多小的但重要的查询。

所以可以将大查询切分成许多小查询，避免一次性执行。

分解大连接查询

将一个大连接查询分解成对每一个表进行一次单表查询，然后在应用程序中进行关联，这样做的好处有：

让缓存更高效。对于大连接查询，如果其中一个表发生变化，那么整个查询缓存就会一并失效，无法使用。而分解后的多个单表查询，即使其中一个表发生变化，其它表的查询缓存依然可以使用。

在应用层进行连接，可以更容易对数据库进行拆分，从而更容易做到高性能和可伸缩。

优化磁盘 I/O

如果 MySQL 出现了性能瓶颈，而且瓶颈在磁盘 IO 上，可以考虑以下三种方法：

1. 设置 binlog_group_commit_sync_delay 和 binlog_group_commit_sync_no_delay_count 参数，减少 binlog 的写盘次数，使组提交效果更明显，可以大幅度降低磁盘的 IOPS 消耗。这个方法是基于“额外的故意等待”来实现的，因此可能会增加语句的响应时间，但没有丢失数据的风险。

1. 将 sync_binlog 设置为大于 1 的值，比较常见是 100~1000，让多个事务写到 binlog 文件后再一并持久化到磁盘。但这样做的风险是：主机掉电后会丢失较多的 binlog 日志。

1. 将 innodb_flush_log_at_trx_commit 设置为 2，让多个 redo log 先写到文件系统的 page cache 中，再通过每秒的轮询刷盘被动的持久化到磁盘。但这样做的风险是：主机掉电的时候会丢数据。

2和3统称为 MySQL 的“非双1”模式。

使用 Redis 缓存与搜索引擎 Elasticsearch

如果索引和查询都优化了，仍然遇到了读性能上的瓶颈，那可以考虑在查询数据库之前走一层缓存，可以用 Redis。

然后再看看是不是有字符串检索的场景从而导致查询低效，如果是的话，可以考虑把表的数据导入到搜索引擎，可以用 Elasticsearch，一般就是监听 MySQL 的 binlog，解析 binlog 然后导入到 Elasticsearch，后续的线上查询就直接走搜索引擎了。

升至主从架构实现读写分离 + 分库分表

如果上面都优化了之后，读写仍然遇到了瓶颈，那就可以考虑把 MySQL 的架构从单库升至主从架构：主库负责写操作以及一些实时性比较高的读操作；从库全部负责写操作。

如果在主从架构下读写性能仍然有瓶颈，那就要考虑分库分表了。