加了索引查询还是慢：复合索引最左前缀的实战排查

  1.  Home
  2. Text  
    3. Share to：

为什么你加了索引，查询还是慢：复合索引的最左前缀到底怎么算

几乎每个个人站长都经历过这个阶段：看到慢查询日志里有条 SQL 跑了 2 秒，第一反应是「这列没索引」，于是加上 ALTER TABLE ... ADD INDEX(col)，再跑一遍，发现还是 2 秒。然后就开始怀疑 MySQL，怀疑服务器，怀疑人生。

问题通常不在「有没有索引」，而在「索引的顺序对不对」。本文只讲一件事：复合索引的列顺序如何决定这条索引能不能被用上。这是 MySQL 索引设计里最容易被误解、也最容易造成「索引建了等于没建」的知识点。

先建立一个心智模型：索引是一本按列排序的字典

理解最左前缀，最好的类比是电话簿。一本电话簿按「省份 → 城市 → 姓名」排序。你可以用它快速找到「广东省内所有城市」的所有人，也可以找到「广东省深圳市」的所有人。但你没办法用它快速找到「所有叫张伟的人」——因为姓名是第三级排序，而前两级完全未知。

复合索引 INDEX(a, b, c) 的物理结构就是这个电话簿：先按 a 排序，a 相同时按 b 排序，b 也相同时按 c 排序。所以：

  • WHERE a = 1 → 能用（定位到 a=1 的连续区间）
  • WHERE a = 1 AND b = 2 → 能用（区间进一步收窄）
  • WHERE a = 1 AND b = 2 AND c = 3 → 完全能用
  • WHERE b = 2 → 不能用（没有 a，定位不到起点）
  • WHERE b = 2 AND c = 3 → 不能用
  • WHERE c = 3 → 不能用

这六种情况里，前三种是「最左前缀」的正面例子，后三种是反面例子。记住一句话就够了：索引从最左列开始，遇到第一个缺失或「不可用于定位」的列就中断。

用 EXPLAIN 亲眼看到「用不上」和「用得上」的区别

光看理论不够，一定要会用 EXPLAIN 验证。假设有一张文章访问统计表：

CREATE TABLE article_view (
  id         BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  cid        INT UNSIGNED NOT NULL,
  view_date  DATE NOT NULL,
  referer    VARCHAR(255) DEFAULT '',
  ip         VARBINARY(16) NOT NULL,
  PRIMARY KEY (id),
  KEY idx_cid_date (cid, view_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

现在跑两条查询，只看 key 和 rows 两列：

EXPLAIN SELECT COUNT(*) FROM article_view
WHERE cid = 42 AND view_date = '2026-09-20';
-- key: idx_cid_date   rows: 约 30    全走索引
EXPLAIN SELECT COUNT(*) FROM article_view
WHERE view_date = '2026-09-20';
-- key: NULL           rows: 约 480000  全表扫描

第二条查询虽然 view_date 是索引的第二列，但因为没给 cid，MySQL 无法定位起点，只能放弃索引。注意 key: NULL 这一项——只要 EXPLAIN 的 key 是 NULL，就说明这条索引对当前查询毫无贡献，这是判断索引是否生效的第一眼依据。

误区一：范围查询之后，索引列「失效」

这是第二个高频坑。复合索引 (a, b, c)，如果 SQL 写成：

SELECT * FROM t WHERE a = 1 AND b > 100 AND c = 5;

很多人以为三列都用上了。实际上：a 用于定位（等值），b 用于范围扫描，而 c 不能被用于索引查找——因为 b > 100 匹配的是一条条「b 的连续但不唯一的记录」，在这个区间里边，c 并不是全局有序的。

这不代表 c 完全白写。在 MySQL 5.6 之后引入了 索引条件下推（Index Condition Pushdown, ICP），c = 5 这个条件可以在存储引擎层就过滤掉，减少回表次数。你会在 EXPLAIN 的 Extra 列看到 Using index condition。但要把话说清楚：

  • 索引条件下推 ≠ 减少了索引扫描的行数，它减少的是「回表」的次数
  • 它不会让 c = 5 变成精确的索引定位，扫描量依然由 b 的范围决定

所以实战建议：等值条件放在复合索引的前面，范围条件放在最后。如果你经常写 WHERE a = 1 AND c = 5 AND b > 100，正确的索引是 (a, c, b)，而不是 (a, b, c)。

误区二：函数、类型转换和隐式转换都会让索引失效

最左前缀只是索引失效的其中一类原因。下面这几种情况同样会让一个「本该能用」的索引直接作废：

-- 1. 列上套函数：索引用不上
SELECT * FROM t WHERE YEAR(view_date) = 2026;

-- 改成范围写法，索引就能用了
SELECT * FROM t WHERE view_date >= '2026-01-01' AND view_date < '2027-01-01';

-- 2. 隐式类型转换：varchar 列传了数字
-- ip_str 是 VARCHAR，下面这种写法会导致全表扫描
SELECT * FROM t WHERE ip_str = 3232235777;

第二种情况尤其隐蔽，因为结果看起来是「对的」——MySQL 会把 varchar 列逐行转成数字再比较，所以能查出你以为的结果，只是慢。判断依据依然是 EXPLAIN：如果 key 显示了索引名但 rows 接近全表行数，或者 key 直接是 NULL，那基本就是隐式转换在作怪。

另一种常见写法是 LIKE：

-- 能用索引（前缀匹配）
SELECT * FROM t WHERE title LIKE 'Nginx%';

-- 不能用索引（以通配符开头）
SELECT * FROM t WHERE title LIKE '%Nginx%';

原因是索引按字符串前缀排序，只有从左边开始匹配才能在 B+ 树里定位区间。%Nginx% 无法确定起点，只能退化为全表扫描。如果你的搜索需求确实需要中间匹配，那就该考虑全文索引（FULLTEXT）或者外部搜索方案，而不是硬扛 LIKE。

误区三：索引不是越多越好，写放大是真实成本

知道索引能加速查询之后，很多站长会走向另一个极端：给每个常用列都单独建一个索引。这会带来两个后果。

第一，每次写入都要维护所有索引。一张表有 6 个二级索引，插入一行数据就要更新 6 棵 B+ 树。对个人站来说，文章发布、评论写入都会因此变慢，而评论表的写入频率远高于查询频率。

第二，磁盘和内存被无谓消耗。之前那篇讲大表瘦身的文章里提到过一个判据：如果 index_length 超过 data_length，说明索引建多了。索引常驻内存才有效，索引越多，Buffer Pool 越挤，最终连最该缓存的页都被挤出去了。

更聪明的做法是让一个复合索引覆盖多个查询。比如你同时有这两个查询场景：

WHERE cid = 42 AND view_date = '2026-09-20'
WHERE cid = 42 ORDER BY view_date DESC LIMIT 10

一个 (cid, view_date) 复合索引就能同时服务两者，不需要再单独建 (cid) 索引——因为 (cid) 正好是 (cid, view_date) 的最左前缀。这叫做「用最左前缀复用索引」，是减少索引数量的核心技巧。

覆盖索引：让查询完全不回表

既然讲到了复合索引，就必须提「覆盖索引」。如果一条查询需要的所有列都在索引里，MySQL 根本不需要回表读主键索引，直接从索引返回数据。EXPLAIN 的 Extra 列会显示 Using index。

-- (cid, view_date) 覆盖了这个查询
SELECT view_date FROM article_view WHERE cid = 42;

-- 但下面这个带 * 的查询覆盖不了，必须回表
SELECT * FROM article_view WHERE cid = 42;

所以「不要无脑 SELECT *」在性能上是有真实依据的：少读几列，就可能从「需要回表」变成「覆盖索引」。对于列表页这种只展示几个字段的场景，把需要的列显式写出来，往往比加索引更有效。

怎么把索引顺序「推」出来：三步实操法

不要凭感觉决定列顺序，按下面的步骤来：

  1. 打开慢查询日志，找出真实的高频慢 SQL。没有慢查询日志的优化都是猜。
  2. 拆解 WHERE 子句：把每条 SQL 的条件分成「等值条件」和「范围条件」两类。等值排在前面，范围排在最后。多列等值条件里，把选择性最高（不重复值最多）的那列放最前。
  3. 用 EXPLAIN 验证，再验证，再验证。改完索引顺序后，重跑 EXPLAIN，看 key 是否命中、rows 是否显著下降、Extra 是否出现 Using index 或 Using index condition。没有 EXPLAIN 证据的「优化」不要提交生产。

关于「选择性」补充一句计算方法：SELECT COUNT(DISTINCT col) / COUNT(*) FROM t。这个比值越接近 1，选择性越高，越适合放在复合索引的最左。像 status（只有几个取值）、is_deleted（0/1）这种低选择性列，单独建索引几乎没有意义——区分度太低，优化器宁可全表扫。

索引失效排查清单

文章最后给一份可以直接对照的清单。当你发现索引没生效，按顺序检查这几点：

  • 是不是用了索引的非最左列作为查询条件？
  • 是不是在索引列上套了函数或做了运算（YEAR()、col+1、DATE()）？
  • 是不是 varchar 列被传了整数（隐式类型转换）？
  • 是不是 LIKE 以 % 开头？
  • 是不是 OR 连接了不同列，导致优化器只能用一遍索引？
  • 是不是范围条件后面还跟着等值条件（列顺序反了）？
  • 是不是这张表数据量太小，优化器算完代价决定全表扫反而更快？这最后一条不是 bug，是真的更快。

最后一点值得单独强调：优化器是有代价模型的，它选全表扫描未必是错的。一张只有 500 行的配置表，全表扫描比走索引再回表还要快。判断「索引有没有用」的唯一标准不是「key 是不是 NULL」，而是「实际耗时有没有下降」。先用 EXPLAIN 看执行计划，再用实际执行时间做最终判断，两者结合才可靠。

索引设计的核心思想其实很朴素：你在查询里写的条件顺序，和你在索引里定义的列顺序，必须能对上。对不上的时候，MySQL 不会报错，它只是安静地把索引丢在一边，然后老老实实扫全表。慢查询日志里那些「明明加了索引还是慢」的 SQL，八成都是这个原因。

Last modification：September 22nd, 2026 at 12:23 pm
© 允许规范转载

相关文章

Leave a Comment