MySQL索引优化实战：从慢查询定位到索引设计

MySQL 的索引是提升查询性能最直接的手段，但很多人在实际项目中会遇到"明明加了索引却依然很慢"的情况。排查这类问题，光靠 EXPLAIN 看执行计划往往还不够，需要理解 B+ 树结构、索引失效的多种场景，以及最左前缀原则到底怎么用。

一、为什么索引能加速

InnoDB 默认使用 B+ 树索引。叶子节点存放完整的行数据，内部节点只存索引键和指针，这样的结构有三个关键优势：

• 树高低：三层 B+ 树就能存下千万级数据，查询最多三次磁盘 I/O
• 有序：叶子节点通过双向链表串联，范围查询可以顺序扫描
• 聚簇：主键索引的叶子节点就是数据本身，不需要回表

反过来说，索引不是越多越好。每个索引都会占用磁盘空间，而且写入时需要维护，增加拖慢 INSERT 和 UPDATE。

二、创建索引的正确姿势

最基础的建索引语法：

CREATE INDEX idx_user_name ON user (name);

但实际建索引前，应该先确认这张表的查询场景。假设有一张订单表，典型查询是"按用户 ID 查该用户的订单列表，按时间倒序"，那么联合索引的顺序就很关键：

CREATE INDEX idx_user_id_create_time ON orders (user_id, create_time DESC);

注意 MySQL 8.0 支持降序索引，之前版本需要用 8.0 以下的兼容写法。

三、联合索引与最左前缀原则

联合索引 (a, b, c) 相当于建立了三个索引：(a)、(a,b)、(a,b,c)。查询条件必须从最左列开始连续匹配才能命中：

-- 命中 idx(a, b, c)
SELECT * FROM t WHERE a = 1 AND b = 2 AND c = 3;
SELECT * FROM t WHERE a = 1 AND b = 2;
SELECT * FROM t WHERE a = 1;

-- 不命中（跳过了 a）
SELECT * FROM t WHERE b = 2 AND c = 3;

排序方向同样受最左前缀约束：索引是 (a, b, c) 时，ORDER BY a, c 能利用索引，而 ORDER BY b, c 不能。

四、索引失效的常见场景

以下几种情况会让索引直接失效，是线上慢查询最常见的元凶。

1. 对索引列做函数或运算

-- 索引失效
SELECT * FROM orders WHERE DATE(create_time) = '2026-01-01';
SELECT * FROM orders WHERE amount + 100 > 500;

-- 索引有效
SELECT * FROM orders WHERE create_time >= '2026-01-01' AND create_time < '2026-01-02';
SELECT * FROM orders WHERE amount > 400;

2. 隐式类型转换

字段是 varchar 而传入数字，或反之，都会触发隐式转换导致索引失效：

-- phone 是 varchar，这样写会失效
SELECT * FROM users WHERE phone = 13800138000;

-- 改成字符串就正常
SELECT * FROM users WHERE phone = '13800138000';

3. 前导模糊匹配

-- 索引失效
SELECT * FROM users WHERE name LIKE '%张';
-- 索引有效
SELECT * FROM users WHERE name LIKE '张%';

4. OR 连接非索引列

WHERE indexed_col = 1 OR unindexed_col = 2 整个查询都会退化成全表扫描，通常改成 UNION ALL 更快。

5. !=、NOT IN、IS NOT NULL

这些否定条件通常选择性太低，优化器会认为走全表扫描更划算。

五、用 EXPLAIN 定位问题

EXPLAIN 输出里最该关注的是 type、key、rows 和 Extra 四列：

Extra 列里如果出现 Using filesort 或 Using temporary，说明额外进行了排序或临时表，通常需要调整索引。

EXPLAIN FORMAT=JSON
SELECT * FROM orders
WHERE user_id = 1001
  AND create_time >= '2026-01-01'
ORDER BY amount DESC
LIMIT 100;

六、线上排查工具

MySQL 自带的慢查询日志是最直接的手段：

SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1;

配合 pt-query-digest 可以把慢查询按耗时聚合，快速找出罪魁祸首。生产环境更推荐用 Performance Schema 或 APM 工具做实时监控。

七、几条实用建议

1. 区分度低的列不适合单独建索引，比如性别、状态这种只有两三个值的列
2. 字符串字段超长时用前缀索引：CREATE INDEX idx_title ON articles (title(20))
3. 覆盖索引能避免回表，把需要查询的列都放进索引里，EXPLAIN 的 Extra 会显示 Using index
4. 定期用 ANALYZE TABLE 更新统计信息，否则优化器可能选错索引
5. 索引不是越多越好，写入频繁的表上每多一个索引，INSERT 就要多做一次索引维护

总结

索引优化的核心思路是：先定位慢查询，再用 EXPLAIN 看清执行计划，然后对照最左前缀原则和索引失效场景逐条排查，最后才动手改索引或优化 SQL 顺序。盲目给每一列都加索引，往往只会让写入变慢、让优化器更迷惑。