📚 课程目录第4章索引设计原则

索引设计原则：选择合适的索引列

很多开发者在设计索引时，喜欢凭感觉选列。觉得“这个字段经常查询就建上去”。我见过不少系统，索引建了一大堆，查询依然慢得像蜗牛。为什么会这样？说白了，索引列没选对。

我自己刚做DBA那会儿，也犯过类似错误。有一次给一张千万级流水表加索引，加完后查询没快多少，写入反而变慢了。后来才明白，选择索引列是个技术活，不是拍脑袋就能决定的。

高选择性列是索引的首选

什么是选择性？简单说就是字段里不同值的比例。选择性越高，索引越有效。你想想看，性别字段只有男和女，选择性很低。身份证号几乎每个值都不同，选择性很高。

  选择性计算公式： COUNT(DISTINCT col) / COUNT(*)

  选择性越接近1，索引效果越好。

举个例子，如果表有100万行，某个字段有90万个不同值，选择性0.9。另一个字段只有2个不同值，选择性0.000002。选哪个？不用我说了吧。

我在项目中遇到过一张订单表，查询经常用订单号、用户ID和支付时间。当时有人建议在“是否已支付”这个字段上建索引（只有0和1两个值）。我坚持没建。结果呢？全表扫描时用这个字段过滤效率反而更高，因为MySQL优化器觉得索引扫描还不如直接扫表。

字段	不同值数量	选择性	建索引建议
id (主键)	100万	1.0	必须
order_no	90万	0.9	强烈推荐
status	5	0.000005	不推荐
is_deleted	2	0.000002	千万别建

小技巧： 用这个SQL快速查看字段的选择性：

SELECT COUNT(DISTINCT col) / COUNT(*) AS selectivity FROM your_table;

选择性低于0.1的字段，我一般不考虑单列索引。

覆盖索引：避免回表的利器

你建了索引，查询却要走回表，这就像你找到了书的目录，还得翻到正文去查具体内容。慢就慢在这里。覆盖索引直接把需要的字段都包含在索引里，不用回表。

举个实际场景。查询订单的ID和金额，where条件是订单号：

-- 普通索引，会回表
CREATE INDEX idx_order_no ON orders(order_no);

-- 覆盖索引，不回表
CREATE INDEX idx_order_no_cover ON orders(order_no, amount, id);

第二个索引，直接在索引里就能拿到ID和金额，省了一次IO。差异有多大？我在项目中遇到过，某个查询原来跑3秒，改成覆盖索引后降到3毫秒。差了1000倍。你想想看，差异就在“回表”这两个字上。

注意： 覆盖索引不是字段越多越好。字段太多，B+树的叶子节点就变大，每个数据页能存的索引行就变少。IO次数反而可能增加。我一般控制在3-5个字段以内。

怎么判断当前查询是否用了覆盖索引？看执行计划里的Extra列：

-- 出现 "Using index" 说明是覆盖索引
EXPLAIN SELECT id, amount FROM orders WHERE order_no = 'xxx';

-- 出现 "Using index condition" 说明只走了索引下推，还是会回表
EXPLAIN SELECT * FROM orders WHERE order_no = 'xxx';

复合索引的顺序：最左前缀法则

创建复合索引时，列的顺序直接决定了它能服务多少查询。最左前缀法则，说白了就是查询条件必须从索引最左边的列开始匹配。跳过了第一列，索引可能就废了。

我举个例子。索引建在 (a, b, c) 上：

查询条件	能否用到索引	用到哪部分
WHERE a = 1	能	a列
WHERE a = 1 AND b = 2	能	a、b列
WHERE a = 1 AND b = 2 AND c = 3	能	a、b、c列
WHERE b = 2	不能	无
WHERE a = 1 AND c = 3	部分	仅a列（c未跳过a、b，但跳过了b，所以只能用到a）

所以，顺序怎么定？我个人习惯遵循两个原则：

把等值条件的列放左边。比如 WHERE user_id = 1 AND create_time > '2024-01-01'，user_id 放左边。
选择性高的列尽量放左边。这个跟第一条冲突时，用实际查询模式决定。

  我的经验法则： 把最常出现在 WHERE 条件里的列放最左边。如果两个列都经常出现，选择性高的优先。这样能最大化索引的覆盖范围。

为什么等值条件优先？因为范围条件（>, <, BETWEEN）之后的列，索引就没法继续匹配了。你想想看，索引是排好序的，一旦某个列走了范围，后面的列顺序就没法利用。

避免过度索引：写操作为王

索引不是越多越好。每个索引都是一棵B+树，插入一行数据，所有索引都要更新。你建了10个索引，每次INSERT就同时维护10棵树。写入能不慢吗？

我见过最夸张的一个案例，一张表只有几百万行，有人建了12个索引。结果每天的数据导入要跑2小时。砍掉一半索引后，降到20分钟。

检查索引使用情况： MySQL 8.0可以用 sys.schema_unused_indexes 找出从未用过的索引，果断删掉。

避坑指南：我曾经接手过一个OLTP系统，订单表上光复合索引就有6个。排查后发现，其中3个索引的重复度极高，只是列顺序调了调。砍掉无用索引后，读写都顺畅了。记住，写操作密集型系统，索引数量建议控制在5个以内。

警告： 建索引前先想清楚：这个索引能覆盖哪些查询？是不是已经有类似的索引了？如果只是为了偶尔跑一次报表建索引，不如用临时表。

另外，注意索引中的冗余。比如已经有索引 (a, b)，再建索引 (a) 就重复了。MySQL的优化器会自动利用复合索引的左前缀，不需要单独建。

总结一下，选择合适的索引列，核心就这几点：高选择性、覆盖索引、合理顺序、控制数量。每个原则背后都有血泪教训。嗯，下次建索引前，花5分钟思考一下这几个问题，能帮你省下几小时的排查时间。