跳到主要内容

慢 SQL 排查与优化

问题​

线上接口响应慢，定位到是数据库 SQL 执行慢，如何排查和优化？

答案​

排查流程​

开启慢查询日志​

-- 查看慢查询配置
SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time';

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

EXPLAIN 分析​

EXPLAIN 关键字段
EXPLAIN SELECT * FROM orders WHERE user_id = 100 AND status = 1 ORDER BY create_time DESC LIMIT 10;
字段关注点
typeALL（全表扫描）→ index → range → ref → const，越靠后越好
key实际使用的索引，NULL = 没走索引
rows预估扫描行数，越少越好
ExtraUsing filesort（排序）、Using temporary（临时表）需优化

常见索引失效场景​

场景示例解决
对索引列用函数WHERE YEAR(create_time) = 2024改为范围查询
隐式类型转换WHERE phone = 13800000000（phone 是 varchar）加引号
LIKE 左模糊WHERE name LIKE '%张'改为前缀匹配或全文索引
OR 条件WHERE a = 1 OR b = 2确保 a 和 b 都有索引，或用 UNION
不满足最左前缀联合索引 (a,b,c)，查询只用了 b调整索引或查询
NOT IN / !=WHERE status != 1改为 IN 正向条件
索引失效示例
-- ❌ 函数导致索引失效
SELECT * FROM orders WHERE DATE(create_time) = '2024-01-15';

-- ✅ 改为范围查询
SELECT * FROM orders 
WHERE create_time >= '2024-01-15 00:00:00' 
  AND create_time < '2024-01-16 00:00:00';

深分页优化​

❌ 深分页问题
-- offset 越大越慢，因为要扫描 100 万 + 10 行然后丢弃前 100 万行
SELECT * FROM orders ORDER BY id LIMIT 1000000, 10;
✅ 游标分页（推荐）
-- 基于上一页最后的 ID，直接定位
SELECT * FROM orders WHERE id > 1000000 ORDER BY id LIMIT 10;
✅ 延迟关联
-- 子查询只查 ID（走覆盖索引），再回表
SELECT o.* FROM orders o
INNER JOIN (SELECT id FROM orders ORDER BY id LIMIT 1000000, 10) t
ON o.id = t.id;

大表优化策略​

优化说明
读写分离查询走从库
垂直拆分大字段拆到扩展表
水平分表按时间/用户 ID 分片
归档历史数据迁移到归档表
缓存热点查询结果缓存到 Redis

常见面试问题​

Q1: EXPLAIN 输出的 type 有哪些级别？​

答案：

从差到好：ALL → index → range → ref → eq_ref → const → system

  • ALL：全表扫描
  • range：索引范围扫描（BETWEEN、>、IN）
  • ref：非唯一索引等值查找
  • const：主键/唯一索引等值查找

详见 EXPLAIN 详解。

Q2: 联合索引的最左前缀原则？​

答案：

联合索引 (a, b, c) 可以用于：a、a,b、a,b,c 的查询。不能只用 b、c、b,c。

详见 索引原理。

Q3: 什么是覆盖索引？​

答案：

查询的列全部在索引中，不需要回表查数据行。EXPLAIN 中 Extra 显示 Using index。

-- 联合索引 (user_id, status)
SELECT user_id, status FROM orders WHERE user_id = 100;
-- 只需索引即可返回，不用回表

Q4: 如何优化 COUNT 查询？​

答案：

  • COUNT(*) 和 COUNT(1) 性能相同，都是统计行数
  • 避免 SELECT COUNT(*) FROM 大表（全表扫描）
  • 方案：缓存计数到 Redis / 使用近似值 / 单独维护计数表

相关链接​