慢查询及其优化

原创于 2026-04-20 16:49:10 发布 · 497 阅读

6 ·

本内容遵循CC 4.0 BY-SA版权协议

GEO检测

什么是慢查询？原因是什么？可以怎么优化？

数据库查询的执行时间超过指定的超时时间时，就被称为慢查询。

原因： 两个

等待时间过长：
-并发冲突：当多个查询同时访问相同的资源时，可能发生并发冲突（锁竞争、事务等待），导致查询变慢。
执行时间过长
- 语句复杂：查询涉及多个表，包含复杂的连接和子查询，可能导致执行时间较长。
- 缺少索引：如果查询的表没有合适的索引或索引失效的时候，需要遍历整张表才能找到结果，查询速度较慢。
- 数据库设计不合理：数据库表设计庞大，查询时可能需要较多时间。
- 查询数据量大：当查询的数据量庞大时，即使查询本身并不复杂，也可能导致较长的执行时间。
- 硬件资源不足：如果 MySQL 服务器上同时运行了太多的查询，会导致服务器负载过高，从而导致查询变慢。

优化：

开启慢查询日志，定位具体SQL：开启慢查询日志（slow_query_log），分析日志文件，找出执行频率高、耗时长的 SQL。
使用EXPLAIN分析执行计划：查看SQL执行细节：
type字段出现 ALL（全表扫描），必须优化。
key（实际使用索引）字段，如果为 NULL，说明未用索引，需要检查索引创建或 SQL 写法。
rows（估算扫描行数）数值越大，性能越差，应尽量通过索引减少扫描行数。
针对性优化：
- 索引优化：1、为 WHERE、ORDER BY的字段建立索引 2、选择区分度高的字段做索引（唯一值多的列，避免索引后再次大量查询）
- 改写SQL：1、避免使用select* 2、分解复杂查询：将大连接拆分为多次简单查询，利用应用层（应用程序执行，防止服务器由于多表连接耗时）做关联。
- 架构升级：升级Mysql服务器的硬件

undo log、redo log、binlog 有什么用？

undo log（回滚日志）是 Innodb 存储引擎层生成的日志。
在事务没提交之前，MySQL 会先记录更新前的数据到 undo log 日志文件里面，当事务回滚时，可以利用 undo log 来进行回滚。
1. 实现事务回滚，保障事务的原子性。
  事务处理过程中，如果出现了错误或者用户执行了 ROLLBACK 语句，MySQL 可以利用 undo log 中的历史数据将数据恢复到事务开始之前的状态
2. 实现 MVCC（多版本并发控制）关键因素之一。
  MVCC 是通过 ReadView + undo log 实现的。undo log 为每条记录保存多份历史数据，MySQL 在执行快照读（普通 select 语句）的时候，会根据事务的 Read View 里的信息，顺着 undo log 的版本链找到满足其可见性的记录。
具体的回滚：
在插入一条记录时，要把这条记录的主键值记下来，这样之后回滚时只需要把这个主键值对应的记录删掉就好了；
在删除一条记录时，要把这条记录中的内容都记下来，这样之后回滚时再把由这些内容组成的记录插入到表中就好了；
在更新一条记录时，要把被更新的列的旧值记下来，这样之后回滚时再把这些列更新为旧值就好了。
redo log（重做日志）是 Innodb 存储引擎层生成的日志，实现了事务中的持久性，主要用于掉电等故障恢复。
Mysql 使用了缓存池（Buffer Pool）来提高读写效率，但是，缓冲池是基于内存的，一旦断电重启，没来得及写入磁盘的脏数据就会丢失。
为了防止断电导致数据丢失的问题，当有一条记录需要更新的时候，InnoDB 引擎就会先更新内存（同时标记为脏页），然后将本次对这个页的修改以 redo log 的形式记录下来（WAL技术），
事务提交时候，只要将redo log 持久化到磁盘就行，Mysql重启后，就会根据redo log的内容，把所有数据恢复到最新的状态。

WAL 技术指的是， MySQL 的写操作并不是立刻写到磁盘上，而是先写日志，然后在合适的时间再写到磁盘上。