索引设计原则：选择性、复合索引、覆盖索引

索引这东西，说起来好像就是建个B+树，但真要设计好，门道不少。我个人习惯先把选择性摸清楚，再考虑要不要叠复合索引，最后看能不能用覆盖索引把回表省掉。这三步走下来，90%的慢查询都能救回来。

选择性：为什么有些索引建了等于白建

选择性，说白了就是字段的区分度。你想想看，性别字段只有男女两种值，那索引分层就两层，扫描一半数据跟全表扫描差别不大。我遇到过一位同事，给订单表的is_deleted字段（0/1）建了索引，结果查询还是慢。为啥？因为90%的数据都是0，索引根本没法快速定位。这种低选择性的索引，MySQL优化器会直接忽略，还不如不建。

选择性的计算公式很简单：COUNT(DISTINCT col) / COUNT(*)。比值越接近1，索引效果越好。我个人习惯要求选择性至少大于0.1，否则索引收益太小。

举个实际例子：用户表里有email和gender两个字段。email几乎每条记录都不同，选择性接近1；gender只有2个值，选择性0.0000x。那我肯定只在email上建索引，gender加进去反而增加维护成本。

再说个避坑点。我曾经接手一个系统，DBA把“状态+创建时间”建了复合索引，但状态字段的选择性只有0.0002（就3种状态）。结果查询时间范围时，优化器依然走全表扫描。后来我把状态移到索引最后一位，时间放第一位，效果立竿见影。

复合索引：最左前缀究竟怎么用

复合索引是解决多条件查询的利器。但它的核心规则——最左前缀原则，很多人栽跟头。我习惯把复合索引比喻成一本电话簿：先按姓氏排序，再按名字排序。如果你只查名字，这本电话簿就废了。

来看一个典型的错误写法：

-- 建索引
CREATE INDEX idx_a_b_c ON t (a, b, c);

-- 能用上索引的查询
SELECT * FROM t WHERE a = 1 AND b = 2;         -- ✅
SELECT * FROM t WHERE a = 1 AND c = 3;         -- ✅ 只用a
SELECT * FROM t WHERE a = 1 AND b = 2 AND c = 3; -- ✅

-- 用不上索引的查询
SELECT * FROM t WHERE b = 2;                    -- ❌ 第一列没了
SELECT * FROM t WHERE c = 3;                    -- ❌ 第一列没了
SELECT * FROM t WHERE b = 2 AND c = 3;          -- ❌ 缺a

为什么会这样？因为复合索引是B+树，它先按a排序，a相同再按b，b相同再按c。没有a，后面的b和c的排序就没意义了。我在项目中经常遇到开发同事问：“我建了(a,b,c)索引，为啥查b字段还是慢？” —— 就是因为忽略了最左前缀。

注意：MySQL 8.0支持跳跃索引（Skip Scan），在特定情况下可以跳过第一列使用后面的列。但触发条件苛刻（第一列选择性低、查询条件选择性强等），千万别依赖这个特性。老老实实把查询条件里最常用的字段放最左边。

复合索引的字段顺序，我总结了一个原则：等值条件放前面，范围条件放后面，排序字段收尾。比如查询WHERE status=1 AND create_time BETWEEN '2024-01-01' AND '2024-01-31' ORDER BY id，我会建索引(status, create_time, id)。因为status是等值，直接定位；create_time是范围，B+树能快速定位区间；id用在排序，避免文件排序。

字段类型建议位置原因
等值条件（WHERE col = ?）最左精确定位，过滤最多数据
范围条件（WHERE col > ?）中间范围后其他列无法使用索引
排序字段（ORDER BY col）靠右利用索引顺序避免filesort

覆盖索引：让查询不回表的魔法

覆盖索引指的是：索引里已经包含了查询需要的所有字段。这样MySQL只需扫描索引，不需要再回表查聚簇索引（主键索引）。我之前优化过一个报表查询，原来执行要3秒，加了覆盖索引后降到10毫秒——就是因为它把回表的IO开销全省了。

判断一个查询是否用上了覆盖索引，可以从执行计划的Extra列看：出现Using index就说明是覆盖索引，只有Using where需要回表。

核心思想：把查询涉及的列尽量塞进索引里，避免回表。但不要无脑加列，索引列越多，写入和更新越重。

举个例子：有个订单表经常查SELECT order_no, amount, status FROM orders WHERE user_id = ?。如果只建(user_id)索引，查到主键后还得回表读order_no、amount、status。改成复合索引(user_id, order_no, amount, status)，那这个查询就完全在索引树里完成，不回表了。

注意：覆盖索引只对InnoDB有效。MyISAM的索引存储的是行指针，没有主键概念，覆盖索引的定义略有不同。另外，SELECT *几乎不可能用覆盖索引，因为你不可能把所有列都塞进一个索引里。所以我建议：业务查询尽量只取需要的字段，不要SELECT *。

我曾经优化过一个“用户最近订单”列表：SELECT * FROM orders WHERE user_id=123 ORDER BY create_time DESC LIMIT 10。原索引是(user_id, create_time)，但Extra显示Using filesort。为什么？因为SELECT *需要回表读所有字段，而回表后数据并不是按时间顺序返回的，所以MySQL还得把结果集排序。改成SELECT id, order_no, create_time，并把这三个字段加到索引最后，就变成了Using index; Using filesort——虽然还有filesort，但回表次数少了10倍，性能提升明显。

综合设计框架：从业务到索引，三步走

下面这张SVG图是我自己做索引设计时常用的决策流程。你可以把它打印出来贴在工位上。

索引设计三步决策流程 分析查询SQL 步骤1：评估字段选择性 COUNT(DISTINCT col)/COUNT(*) ≥ 0.1？ 否：放弃 是：继续 步骤2：复合索引字段顺序 等值→范围→排序 步骤3：检查覆盖索引 SELECT需要的列是否都在索引中？ 否：加列 验证执行计划 避坑提示 复合索引不要超过5列 低选择性字段放最后

实战避坑清单

  • 索引不要建在频繁更新的字段上。我曾经在一个计数器的update_time上建索引，每次更新都要修改B+树，性能反而下降。
  • 复合索引列数控制在4-5列以内。超过5列，B+树变宽，查询和更新都变慢，得不偿失。
  • 主键尽量用自增ID。UUID作为主键，插入时页分裂严重，索引碎片多。我从一个云服务日志表（UUID主键）迁移到自增ID后，插入性能提升了3倍。
  • 定期检查未使用的索引。MySQL的sys.schema_unused_indexes视图可以帮你找出哪些索引建了但从未被使用，果断删掉，释放写入压力和存储空间。

最后说一句：没有银弹。索引调优的核心就是根据业务查询模式，在选择性、复合顺序、覆盖范围之间找到平衡。你动手去做几个优化，慢慢就有感觉了。

← 上一章 📖 返回目录 下一章 →