【MySQL八股文】慢SQL是什么？怎么处理？

vocal
523 阅读3分钟

定义

慢SQL指的是执行时间较长，影响数据库性能的SQL语句。在MySQL中，通常认为执行时间超过1秒（或自定义阈值）的查询可以被视为慢查询。这类SQL语句可能会导致数据库响应缓慢、资源占用过高等问题，严重时甚至会拖垮整个数据库服务。

通常慢SQL统计的时间为：锁等待时间、I/O等待时间、CPU计算时间、内存访问时间等所有在数据库服务器内部发生的操作时间。

但一般不包括：客户端与服务器之间的网络传输时间。即数据从MySQL服务器发送结果集到客户端所花费的时间，并不会被计算在慢查询的日志记录时间内。

提取

  1. 启动慢查询日志

    设置配置文件my.cnf

    [mysqld] 
slow_query_log=1 
long_query_time=10

  2. 分析日志，提取耗时的查询
    可以使用mysqldumpslow来分析日志 或者 人工审查日志，来获取慢SQL

处理

从业务角度

如果慢SQL本身是简单查询，但扫描返回的数据本身是十几万或者上百万行，这个是符合预期的，需要从业务上判断是否真的需要返回那么多条数据；或者考虑分页展示(也要避免深分页)。

从系统角度

  1. 观察执行SQL时，系统资源(CPU、内存等)是否存在耗尽的问题。如果存在资源耗尽的情况，出现慢SQL即为正常现象，需要考虑是否扩容服务器，或者对一些高资源消耗的应用进行优化。不过，慢SQL日志无法记录由于网络波动而导致的“慢SQL”，如果要检测这一点，需要在代码中手动计算

查询资源消耗

  1. top 查询资源占用(P 按CPU排序，M 按Mem排序)
  2. top -H -p {pid} # -H 为线程模式，-p 为指定pid
  3. jstack -l {pid} > output.txt
  4. cat output.txt | grep -i {Ootid}

从数据库角度

  1. EXPLAIN 分析SQL
    对于从日志中提取出来的慢查询，使用EXPLAIN关键字来查看MySQL是如何执行这些查询的。通过EXPLAIN输出的信息，可以了解到MySQL为执行查询选择了哪些索引、是否进行了全表扫描、连接顺序等重要细节。，是否走了索引（type=ref/range/index），是否有全表扫描（type=ALL） 
    +----+-------------+----------+------------+-------+---------------+---------+---------+-----+------+----------+-------------+
| id | select_type | table    | partitions | type  | possible_keys | key     | key_len | ref | rows | filtered | Extra       |
+----+-------------+----------+------------+-------+---------------+---------+---------+-----+------+----------+-------------+
| 1  | SIMPLE      | lineitem | NULL       | index | NULL          | PRIMARY | 8       | NULL | 1    | 100      | Using index |
+----+-------------+----------+------------+-------+---------------+---------+---------+-----+------+----------+-------------+
  2. 合理添加索引：在频繁查询的列（如主键、外键、条件列）上建立索引。
  3. 避免重复索引：例如对 (a, b) 和 (a) 同时建索引是多余的。避免过度索引：每个索引都会影响写入性能（INSERT/UPDATE/DELETE）。
  4. 复合索引最左匹配原则：联合索引 (col1, col2, col3) 可以命中 col1, col1+col2，但不能命中 col2 或 col3。
  5. 覆盖索引：如果查询字段都在索引中，则不需要回表，效率更高。
  6. 避免 SELECT *，只查询需要的字段。
  7. 减少子查询嵌套，改用 JOIN 操作。
  8. 控制分页深度，避免 LIMIT offset, size 中 offset 过大(先使用WHERE过滤)。
  9. 避免在 WHERE 条件中对字段进行函数操作，会导致索引失效。
  10. SHOW ENGINE INNODB STATUS;查看是否存在锁等待、死锁等问题：
  11. 避免长事务，减少行锁持有时间。
  12. 使用合适的事务隔离级别，降低锁竞争。

总结对比

维度关键点
业务角度判断是否真的需要返回大量数据；分页展示或异步导出
系统角度监控服务器资源，排查高负载、线程阻塞、网络延迟等问题
数据库角度优化执行计划、索引、SQL语句、表结构、锁机制等
avatar
文章
阅读
粉丝