数据库慢查询日志分析及SQL重写指南

数据库慢查询日志是排查性能瓶颈最直接、最有效的手段。打开慢查询日志（slow query log），你会看到每条执行超过阈值时间的SQL语句、执行耗时、扫描行数、返回行数等关键信息。拿到这些数据后，核心工作就是三步：定位慢SQL、分析执行计划、重写SQL语句。下面我会从日志配置、日志解读、执行计划分析到SQL重写技巧，把整套流程讲透。

一、慢查询日志的开启与配置

MySQL默认可能没有开启慢查询日志，需要手动配置。在my.cnf或my.ini中添加以下参数：

slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 2
log_queries_not_using_indexes = 1
log_throttle_queries_not_using_indexes = 60

long_query_time设为2秒，意味着超过2秒的SQL都会被记录。log_queries_not_using_indexes设为1，会把没有使用索引的查询也记录下来，这对发现隐式全表扫描非常有用。生产环境建议把阈值设为1秒甚至更低，避免漏掉中等程度的慢查询。PostgreSQL的做法类似，在postgresql.conf中设置：

log_min_duration_statement = 1000
log_checkpoints = on
log_lock_waits = on

PostgreSQL用毫秒为单位，1000即1秒。配置好后重启数据库服务，慢查询日志就开始工作了。

二、慢查询日志的关键字段解读

一条典型的MySQL慢查询日志长这样：

# Time: 2024-01-15T10:23:45.123456Z
# User@Host: app_user[app_user] @ localhost []
# Query_time: 3.523456  Lock_time: 0.000123  Rows_sent: 10  Rows_examined: 158234
SET timestamp=1705313025;
SELECT * FROM orders WHERE customer_name LIKE '%张%' AND status = 1 ORDER BY create_time DESC;

重点看四个字段：Query_time是执行耗时，Rows_examined是扫描行数，Rows_sent是返回行数，Lock_time是锁等待时间。如果Rows_examined远大于Rows_sent，说明大量无效扫描，索引大概率没用上。Lock_time高则说明存在锁竞争问题。时间戳帮你定位问题发生的时段，结合业务高峰判断是否是并发导致。

三、用EXPLAIN分析执行计划

找到慢SQL后，第一件事不是急着改，而是用EXPLAIN看执行计划。在SQL前面加EXPLAIN关键字执行：

EXPLAIN SELECT * FROM orders WHERE customer_name LIKE '%张%' AND status = 1 ORDER BY create_time DESC;

输出结果重点关注这几列：type列显示访问类型，从好到差依次是system > const > eq_ref > ref > range > index > ALL，出现ALL就是全表扫描，必须优化。key列显示实际使用的索引，如果是NULL说明没走索引。rows列是预估扫描行数，数字越大越慢。Extra列会出现"Using filesort"或"Using temporary"，这两个都是性能杀手，意味着额外的排序或临时表操作。

MySQL 8.0以上还可以用EXPLAIN ANALYZE，它会真正执行SQL并给出实际运行数据，比EXPLAIN的估算更准确：

EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_name LIKE '%张%' AND status = 1 ORDER BY create_time DESC;

四、SQL重写的核心原则与实战技巧

1. 避免SELECT *，只查需要的列

SELECT *会导致无法利用覆盖索引，还会增加网络传输和内存消耗。改成明确的字段列表：

-- 慢
SELECT * FROM orders WHERE status = 1;
-- 快
SELECT order_id, customer_id, amount, create_time FROM orders WHERE status = 1;

2. 优化LIKE模糊查询

前面带通配符的LIKE '%xxx%'会导致索引失效。如果业务允许，改成后缀匹配LIKE 'xxx%'，这样可以走索引。或者使用全文索引（FULLTEXT）替代：

-- 慢：全表扫描
SELECT * FROM articles WHERE content LIKE '%数据库优化%';
-- 快：使用全文索引
SELECT * FROM articles WHERE MATCH(content) AGAINST('数据库优化' IN BOOLEAN MODE);

3. 用EXISTS替代IN子查询

当子查询结果集较大时，IN的性能会急剧下降，改成EXISTS通常更高效：

-- 慢
SELECT * FROM orders WHERE customer_id IN (SELECT id FROM customers WHERE level = 'VIP');
-- 快
SELECT * FROM orders o WHERE EXISTS (SELECT 1 FROM customers c WHERE c.id = o.customer_id AND c.level = 'VIP');

4. 避免在WHERE条件中对字段做函数运算

对索引列使用函数会导致索引失效，必须把运算移到等号右边：

-- 慢：索引失效
SELECT * FROM orders WHERE YEAR(create_time) = 2024;
-- 快：走范围索引
SELECT * FROM orders WHERE create_time >= '2024-01-01' AND create_time < '2025-01-01';

5. JOIN优化：小表驱动大表

JOIN时确保驱动表（小表）在前，被驱动表（大表）的关联字段有索引。同时避免过多表JOIN，一般控制在3-4张表以内。如果必须多表关联，考虑先用子查询把大表过滤后再JOIN：

-- 慢：直接大表JOIN
SELECT * FROM orders o JOIN order_items i ON o.id = i.order_id JOIN products p ON i.product_id = p.id WHERE o.status = 1;
-- 快：先过滤再JOIN
SELECT * FROM (SELECT id, customer_id, amount FROM orders WHERE status = 1) o 
JOIN order_items i ON o.id = i.order_id 
JOIN products p ON i.product_id = p.id;

6. 分页查询的深分页问题

LIMIT 100000, 10这种深分页会扫描前10万条再丢弃，非常慢。用游标分页或延迟关联替代：

-- 慢：深分页
SELECT * FROM orders ORDER BY id LIMIT 100000, 10;
-- 快：延迟关联
SELECT * FROM orders o INNER JOIN (SELECT id FROM orders ORDER BY id LIMIT 100000, 10) t ON o.id = t.id;
-- 或者用游标
SELECT * FROM orders WHERE id > 100000 ORDER BY id LIMIT 10;

7. 合理使用索引覆盖和联合索引

建立联合索引时遵循最左前缀原则。比如WHERE a = 1 AND b = 2 AND c = 3，建索引(a, b, c)三个条件都能用到。如果查询只涉及a和c，索引只能用到a。另外，把选择性高的列放在联合索引前面，选择性 = 不同值数量 / 总行数，越接近1越好。

五、日志分析工具推荐

手动看日志效率太低，推荐几个工具。mysqldumpslow是MySQL自带的慢查询日志分析工具，可以按查询时间、锁时间、扫描行数排序：

mysqldumpslow -s t -t 10 /var/log/mysql/slow.log

pt-query-digest是Percona Toolkit里的工具，功能更强大，能生成详细的分析报告。开源的还有pganalyze（针对PostgreSQL）、JetProfiler等。企业级可以考虑New Relic、Datadog这类APM工具，能自动抓取慢查询并给出优化建议。

六、建立长效监控机制

慢查询优化不是一次性工作。建议建立定期巡检机制：每天自动分析慢查询日志，周报汇总TOP 10慢SQL，月度做索引使用率评估。同时设置告警阈值，当慢查询数量突增或单条SQL耗时超过5秒时自动通知DBA。数据库版本升级后也要重新审视执行计划，因为优化器策略可能变化，原来走索引的SQL可能变成全表扫描。

七、容易被忽视的细节

很多人只关注SQL本身，忽略了数据量增长带来的问题。一条SQL在10万数据时很快，到1000万就可能变成灾难。所以优化要有前瞻性，提前评估数据增长曲线。另外，字符集不一致也会导致索引失效，比如一个字段是utf8mb4，另一个是utf8，JOIN时索引就用不上。还有，批量插入时不要逐条INSERT，用批量语句或LOAD DATA，能提升几个数量级的写入速度。

总结一下，数据库慢查询优化是一个系统工程：先开日志、再看执行计划、然后针对性重写SQL、最后建立监控闭环。掌握EXPLAIN分析、索引设计原则、SQL重写技巧这三板斧，能解决80%以上的慢查询问题。剩下的20%，往往需要从架构层面考虑分库分表、读写分离、缓存降级等方案。

数据库慢查询日志分析及SQL重写指南

服务指南

帮助中心