MySQL慢查询优化的方法论和实战案例（襄阳DBA手记）

2026-08-07 阅读 20 技术博客

发现慢查询

首先你要知道哪些查询慢。在my.cnf中开启慢查询日志：slow_query_log = 1 slow_query_log_file = /var/log/mysql/slow.log long_query_time = 1 (记录执行时间超过1秒的查询)

然后用pt-query-digest或mysqldumpslow分析日志，找出TOP N慢查询。

我们在{CITY}一个客户的系统中开启了慢查询日志后发现，排名前十的慢查询占了总查询时间的87%。也就是说只要优化这10条SQL，整体性能就能提升一个数量级。

EXPLAIN是MySQL自带的SQL分析工具。重点关注以下几列：

type列：表示访问类型。从好到差的顺序是 system > const > eq_ref > ref > range > index > ALL。如果看到ALL(全表扫描)，通常意味着需要加索引。

key列：实际使用的索引。如果是NULL说明没有用到索引。

rows列：预估扫描的行数。这个值越大越慢。

Extra列：额外信息。如果出现"Using temporary"(使用临时表)或"Using filesort"(文件排序)，通常需要优化SQL写法或调整索引。

原始SQL：SELECT * FROM orders WHERE user_id = 123 AND status = 1 ORDER BY create_time DESC LIMIT 20;

EXPLAIN显示type=ALL，rows=150000(全表扫描15万行)。执行时间2.3秒。

优化方案：创建联合索引 idx_user_status_time(user_id, status, create_time)。

优化后EXPLAIN显示type=ref，rows=48。执行时间0.008秒。提速近300倍。

关键点：WHERE条件列放在索引前面，ORDER BY列放在后面。这就是"最左前缀原则"的实际应用。

原始SQL：SELECT COUNT(*) FROM products WHERE category_id IN (SELECT id FROM categories WHERE parent_id = 5);

子查询导致每次都要扫描categories表再关联products表。执行时间1.8秒。

优化方案：改为JOIN方式。SELECT COUNT(p.*) FROM products p INNER JOIN categories c ON p.category_id = c.id WHERE c.parent_id = 5;

或者进一步优化：如果category_id本身就知道(比如parent_id=5下面的子分类ID是固定的几个)，直接用IN(1,3,7,9)代替子查询。

优化后执行时间0.015秒。

原始SQL：SELECT * FROM articles ORDER BY id DESC LIMIT 100000, 20;

OFFSET越大越慢——MySQL需要扫描前面100000行然后丢弃。执行时间3.5秒。

优化方案：使用游标分页(基于上一页最后一条记录的ID)。SELECT * FROM articles WHERE id < last_id ORDER BY id DESC LIMIT 20;

优化后执行时间0.002秒。无论翻到第几页都是毫秒级。

索引优先：大多数慢查询问题加合适的索引就能解决。避免SELECT *：只查需要的列减少IO和内存消耗。小心IN子查询：尽量改写为JOIN。分页深翻用游标：LIMIT offset太大时一定有问题。定期Review慢查询日志：每周看一次，积少成多的优化效果显著。