跳到主要内容

慢 SQL 排查与优化

问题​

线上出现慢 SQL 告警，如何快速定位和优化？

答案​

一、排查流程​

二、慢查询日志​

-- 开启慢查询日志
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1;       -- 超过 1 秒记录
SET GLOBAL log_queries_not_using_indexes = ON;  -- 未使用索引也记录

-- 查看慢查询日志路径
SHOW VARIABLES LIKE 'slow_query_log_file';

mysqldumpslow 分析：

# 按执行次数排序 Top 10
mysqldumpslow -s c -t 10 slow.log

# 按平均耗时排序 Top 10
mysqldumpslow -s at -t 10 slow.log

三、EXPLAIN 关键字段​

EXPLAIN SELECT * FROM orders WHERE user_id = 100 ORDER BY created_at DESC;
字段关注要点目标值
type访问类型const > eq_ref > ref > range > index > ALL
key使用的索引不为 NULL
rows预估扫描行数越小越好
Extra额外信息避免 Using filesort、Using temporary
filtered过滤比例越接近 100% 越好
必须优化的信号
  • type = ALL：全表扫描
  • Extra: Using filesort：文件排序
  • Extra: Using temporary：临时表
  • rows 非常大（> 10 万）

四、高频慢 SQL 场景与优化​

场景 1：索引失效​

-- ❌ 对索引列使用函数
SELECT * FROM orders WHERE DATE(created_at) = '2024-01-01';
-- ✅ 改为范围查询
SELECT * FROM orders
WHERE created_at >= '2024-01-01' AND created_at < '2024-01-02';

-- ❌ 隐式类型转换（phone 是 VARCHAR）
SELECT * FROM users WHERE phone = 13800138000;
-- ✅ 使用正确类型
SELECT * FROM users WHERE phone = '13800138000';

-- ❌ 前导通配符
SELECT * FROM products WHERE name LIKE '%手机%';
-- ✅ 使用全文索引或搜索引擎
ALTER TABLE products ADD FULLTEXT INDEX ft_name(name);
SELECT * FROM products WHERE MATCH(name) AGAINST('手机');

-- ❌ 最左前缀不匹配（索引 a, b, c）
SELECT * FROM t WHERE b = 1;
-- ✅ 查询条件包含最左列
SELECT * FROM t WHERE a = 1 AND b = 1;

场景 2：深分页​

-- ❌ OFFSET 深分页（扫描 100 万行）
SELECT * FROM orders ORDER BY id LIMIT 1000000, 20;

-- ✅ 方案 1：延迟关联（子查询只扫描索引）
SELECT o.* FROM orders o
JOIN (SELECT id FROM orders ORDER BY id LIMIT 1000000, 20) t
ON o.id = t.id;

-- ✅ 方案 2：游标分页（记住上次的 ID）
SELECT * FROM orders WHERE id > 12345678 ORDER BY id LIMIT 20;

场景 3：大 IN 查询​

-- ❌ IN 列表过长
SELECT * FROM products WHERE id IN (1, 2, 3, ..., 10000);

-- ✅ 分批查询
SELECT * FROM products WHERE id IN (1, 2, ..., 500);
SELECT * FROM products WHERE id IN (501, 502, ..., 1000);
-- 或使用临时表 JOIN

场景 4：COUNT 优化​

-- ❌ COUNT(*) 全表扫描（InnoDB 无精确行数）
SELECT COUNT(*) FROM orders;

-- ✅ 方案 1：近似值
SELECT TABLE_ROWS FROM information_schema.TABLES
WHERE TABLE_NAME = 'orders';

-- ✅ 方案 2：单独维护计数（Redis / 计数表）
-- ✅ 方案 3：加条件限定范围
SELECT COUNT(*) FROM orders WHERE created_at > '2024-01-01';

五、索引优化工具箱​

工具用途
EXPLAIN查看执行计划
EXPLAIN ANALYZE（8.0）实际执行 + 耗时
SHOW INDEX FROM table查看表索引
SHOW STATUS LIKE 'Handler%'查看读行数
pt-query-digest分析慢查询日志
sys.schema_unused_indexes未使用的索引

常见面试问题​

Q1: 索引失效的常见场景有哪些？​

答案：

场景原因解决方案
函数操作WHERE YEAR(date) = 2024改为范围查询
隐式转换VARCHAR 列用数字比较使用正确类型
前导通配LIKE '%abc'全文索引 / ES
最左前缀组合索引跳列调整索引或查询
OR 条件WHERE a=1 OR b=2UNION 或分别建索引
NOT IN / !=无法使用索引范围扫描改用 EXISTS / IN
数据量小优化器判断全扫更快正常现象，不必优化

Q2: 如何判断一个 SQL 是否需要优化？​

答案：

  1. 执行时间 > 1 秒的查询需要关注
  2. 扫描行数远大于返回行数（rows >> 实际结果数）
  3. type 为 ALL 或 index（全表/全索引扫描）
  4. 出现 filesort 或 temporary
  5. 高频执行的 SQL：即使单次 100ms，QPS=1000 时也很严重

优化优先级：高频 SQL > 慢 SQL，因为高频 SQL 的整体影响更大。

Q3: EXPLAIN 中 type 各级别的含义？​

答案：

type含义性能
system表只有一行⭐⭐⭐⭐⭐
const主键等值查询⭐⭐⭐⭐⭐
eq_refJOIN 使用主键/唯一索引⭐⭐⭐⭐
ref非唯一索引等值查询⭐⭐⭐⭐
range索引范围扫描⭐⭐⭐
index全索引扫描⭐⭐
ALL全表扫描⭐

一般要求线上查询至少达到 range 级别。

相关链接​