SQL 索引原理与查询优化：从 EXPLAIN 开始¶
先别急着加索引¶
很多开发者遇到慢查询的第一反应是"加个索引"。索引不是越多越好——每多一个索引，写入性能就多一分损耗，磁盘也多占一分空间。真正的流程应该是：先定位慢在哪，再决定要不要索引。
一、EXPLAIN：SQL 优化从读懂它开始¶
EXPLAIN SELECT * FROM orders WHERE user_id = 123 AND status = 1;
执行结果里最值得关注的三列：
type：访问类型（性能由好到差）¶
system > const > eq_ref > ref > range > index > ALL
- const/eq_ref：主键或唯一索引等值查询，最快
- ref：非唯一索引等值查询，正常
- range：索引范围查询（>、<、BETWEEN）
- index：全索引扫描（遍历整个索引树，比全表略好）
- ALL：全表扫描，慢查询的元凶
key：实际使用的索引¶
为 NULL 说明没用索引，重点排查。
rows：预估扫描行数¶
扫描 10 万行和 100 行是两个量级。rows 明显大于实际返回行数，说明索引没选对或没走索引。
二、索引为什么能让查询变快¶
B+ 树的原理¶
InnoDB 的索引是 B+ 树结构：
- 非叶子节点只存键值，一个节点能存上千个键，树高一般 2-3 层
- 3 层 B+ 树可以支撑千万级数据，查询只做 3 次磁盘 IO
- 叶子节点按顺序串联成链表，支持范围查询
数据量 1000 万，树高 3，每次查询 3 次磁盘 IO——这就是索引快的本质。
聚簇索引 vs 二级索引¶
- 聚簇索引（主键索引）：叶子节点存整行数据
- 二级索引：叶子节点存主键值，查数据需要回表（再查一次聚簇索引）
-- 覆盖索引：查询列都在二级索引里，无需回表
EXPLAIN SELECT user_id, status FROM orders WHERE user_id = 123;
-- Extra 列出现 Using index 即命中覆盖索引
三、最左前缀原则¶
联合索引 (a, b, c) 的匹配规则：
WHERE a = 1 ✅ 走索引
WHERE a = 1 AND b = 2 ✅ 走索引
WHERE a = 1 AND c = 3 ✅ 走索引（但只用到了 a）
WHERE b = 2 ❌ 不走索引（没从最左列开始）
设计联合索引时，把选择性最高（区分度大）的列放最前面。
四、常见的索引失效场景¶
-- 1. 函数或计算作用于索引列
WHERE YEAR(create_time) = 2026 -- ❌ 应改为范围查询
WHERE create_time >= '2026-01-01' -- ✅
-- 2. 隐式类型转换
WHERE phone = 13812345678 -- phone 是 varchar，数字会转字符串比较，索引失效
-- 3. 前置通配符
WHERE name LIKE '%张%' -- ❌ 前导 % 无法走索引
WHERE name LIKE '张%' -- ✅
-- 4. OR 连接非索引列
WHERE id = 1 OR name = '张三' -- ❌ 任一条件无索引则整体失效
五、慢查询排查实战¶
1. 打开慢查询日志¶
-- 开启慢查询日志（生产环境谨慎，注意磁盘）
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1; -- 超过 1 秒记录
2. 分析典型慢 SQL¶
Q: SELECT * FROM orders WHERE status = 1 ORDER BY create_time DESC LIMIT 10;
问题分析：
status区分度低（只有几个值），索引选择性差，优化器可能放弃索引- 需要先过滤再排序，但现有索引不支持
优化方案：
-- 方案 1：联合索引 (status, create_time)，覆盖过滤+排序
ALTER TABLE orders ADD INDEX idx_status_time (status, create_time);
-- 方案 2：如果 status 分布极不均（90% 是 status=1），
-- 考虑业务侧拆分表或使用归档表
3. 分页深翻页优化¶
-- 深翻页：LIMIT 100000, 10 会扫描前 10 万行再丢弃
-- 优化：延迟关联，先取主键再回表
SELECT o.* FROM orders o
JOIN (SELECT id FROM orders ORDER BY id LIMIT 100000, 10) t
ON o.id = t.id;
六、索引维护的日常¶
- 冗余索引清理：
(a)和(a,b)共存时，前者通常冗余 - 定期 analyze：
ANALYZE TABLE更新统计信息，避免优化器用过期统计做错决策 - 碎片整理：
ALTER TABLE ... ENGINE=InnoDB或OPTIMIZE TABLE（锁表，选低峰期） - 监控索引使用率：
performance_schema.table_io_waits_summary_by_index_usage
结语¶
SQL 优化 80% 的收益来自 20% 的慢查询。正确的姿势是：EXPLAIN 定位 → 分析访问类型和扫描行数 → 针对性加索引或改写 SQL → 回放验证。不要"为了加索引而加索引"，让数据说话。