41. 索引设计原则与最左前缀

41.1 最左前缀原则

最左前缀原则（Leftmost Prefix Rule） 是联合索引最重要的规则：联合索引 (a, b, c) 可以用于 a、(a, b)、(a, b, c) 的查询，但不能跳过 a 直接用 b 或 c。

因为 B+ 树是按索引列从左到右排序的。先按 a 排序，a 相同再按 b 排序，b 相同再按 c 排序。跳过 a 就无法利用索引的有序性。

-- 创建联合索引
CREATE INDEX idx_abc ON users(age, status, username);

能用索引的查询：

SELECT * FROM users WHERE age = 25;                          -- 用到 age
SELECT * FROM users WHERE age = 25 AND status = 'active';    -- 用到 age, status
SELECT * FROM users WHERE age = 25 AND status = 'active'
    AND username = 'zhangsan';                               -- 用到 age, status, username

不能用索引的查询：

SELECT * FROM users WHERE status = 'active';                 -- 跳过 age，不行
SELECT * FROM users WHERE username = 'zhangsan';             -- 跳过 age, status，不行
SELECT * FROM users WHERE age = 25 AND username = 'zhangsan'; -- 用到 age，但 username 用不到（中间跳过了 status）

范围查询会中断后续列的索引使用

-- age 用了范围查询，status 和 username 就用不到索引了
SELECT * FROM users WHERE age > 20 AND status = 'active' AND username = 'zhangsan';
-- 只有 age 走索引，status 和 username 是在回表后逐行判断的

MySQL 优化器的自动调整

MySQL 优化器会自动调整 WHERE 条件的顺序，让它匹配最左前缀：

-- 以下两条等价，都能用上 (age, status, username) 索引
SELECT * FROM users WHERE status = 'active' AND age = 25 AND username = 'zhangsan';
SELECT * FROM users WHERE age = 25 AND status = 'active' AND username = 'zhangsan';

41.2 索引下推（Index Condition Pushdown）

索引下推（ICP） 是 MySQL 5.6 引入的优化，在存储引擎层就过滤数据，减少回表次数。

-- 联合索引 (username, age)
CREATE INDEX idx_user_age ON users(username, age);

-- 查询：username 用 LIKE，age 用等值
SELECT * FROM users WHERE username LIKE 'zhang%' AND age = 25;

没有 ICP 时：

1. 存储引擎根据 username LIKE 'zhang%' 在索引中找到所有匹配的主键；
2. 逐个回表查完整行；
3. Server 层再过滤 age = 25。

有 ICP 时：

1. 存储引擎根据 username LIKE 'zhang%' 找到匹配的索引记录；
2. 在索引中直接检查 age = 25，不满足的直接跳过；
3. 只对满足条件的记录回表。

-- 查看 ICP 是否开启
SELECT @@optimizer_switch LIKE '%index_condition_pushdown%';
-- 默认开启

41.3 覆盖索引

覆盖索引（Covering Index） 指查询所需的所有列都在索引中，不需要回表。

-- 联合索引 (user_id, status)
CREATE INDEX idx_uid_status ON orders(user_id, status);

-- 查询只需要 user_id 和 status
SELECT user_id, status FROM orders WHERE user_id = 1;
-- 索引中已经有 user_id 和 status，直接返回，不需要回表

如果查询还需要索引中没有的列（如 amount），就必须回表：

SELECT user_id, status, amount FROM orders WHERE user_id = 1;
-- amount 不在索引中，需要回表

41.4 索引失效场景

建了索引不代表一定会用。以下情况会导致索引失效，MySQL 退化为全表扫描。

1. 对索引列使用函数或表达式

-- 索引失效：用了函数
SELECT * FROM users WHERE YEAR(created_at) = 2026;
SELECT * FROM users WHERE LEFT(username, 5) = 'zhang';

-- 改写为等值/范围查询，能用索引
SELECT * FROM users WHERE created_at >= '2026-01-01' AND created_at < '2027-01-01';
SELECT * FROM users WHERE username LIKE 'zhang%';

2. 隐式类型转换

-- 假设 user_id 是 INT 类型
-- 索引失效：传了字符串，MySQL 隐式转换
SELECT * FROM orders WHERE user_id = '1';

-- 正确：传数字
SELECT * FROM orders WHERE user_id = 1;

3. LIKE 以 % 开头

-- 索引失效：% 开头无法利用 B+ 树的有序性
SELECT * FROM users WHERE username LIKE '%zhang';

-- 能用索引：% 在后面
SELECT * FROM users WHERE username LIKE 'zhang%';

4. OR 条件中有列没有索引

-- 假设 age 有索引，email 没有索引
-- 索引失效：OR 两边必须都有索引才能用
SELECT * FROM users WHERE age = 25 OR email = 'test@test.com';

5. 不符合最左前缀

-- 联合索引 (age, status, username)
SELECT * FROM users WHERE status = 'active';  -- 跳过 age，索引失效

6. NOT IN、NOT EXISTS、!=

-- 不等于、NOT IN 通常导致索引失效
SELECT * FROM users WHERE age != 25;
SELECT * FROM users WHERE age NOT IN (25, 30);

索引失效汇总

场景	示例	原因
函数操作	WHERE YEAR(col) = 2026	破坏索引有序性
隐式类型转换	WHERE int_col = '1'	类型不匹配
LIKE ‘%xxx’	WHERE col LIKE '%abc'	无法利用前缀有序性
OR 有无索引列	WHERE a=1 OR b=2（b 无索引）	无法全部用索引
不符合最左前缀	跳过联合索引第一列	B+ 树无法定位
!= / NOT IN	WHERE col != 1	优化器认为全表扫描更快

41.5 复合索引顺序设计

联合索引的列顺序直接决定了哪些查询能用上索引。设计原则：

1. 把等值查询的列放前面

-- 如果查询模式是 WHERE status = 'active' AND created_at > '2026-01-01'
-- 把等值的 status 放前面，范围查询的 created_at 放后面
CREATE INDEX idx_status_time ON orders(status, created_at);

2. 把区分度高的列放前面

区分度 = 不同值的数量 / 总行数。区分度越高，过滤效果越好。

-- 查看各列区分度
SELECT
    COUNT(DISTINCT status) / COUNT(*) AS status_sel,
    COUNT(DISTINCT user_id) / COUNT(*) AS user_id_sel
FROM orders;
-- user_id 区分度远高于 status，放前面
CREATE INDEX idx_uid_status ON orders(user_id, status);

3. 把排序列放最后

-- 查询模式：WHERE user_id = 1 ORDER BY created_at
CREATE INDEX idx_uid_time ON orders(user_id, created_at);
-- 既能用 user_id 过滤，又能利用 created_at 的有序性避免 filesort

4. 尽量用覆盖索引

把查询需要的列都放进索引，避免回表。但要权衡索引大小。

设计原则	理由
等值列在前	避免范围查询中断后续列
高区分度在前	过滤效果好，减少扫描行数
排序列在后	利用索引有序性避免排序
覆盖查询列	减少回表

41.6 避免过度索引

1. 重复索引

-- 重复：id 已有主键索引，又建一个普通索引
CREATE INDEX idx_id ON users(id);  -- 多余，删掉
DROP INDEX idx_id ON users;

2. 冗余索引

-- 联合索引 (a, b) 已经覆盖了 a 的单列查询
CREATE INDEX idx_a ON users(age);              -- 多余
CREATE INDEX idx_ab ON users(age, status);     -- 有了这个就够了
DROP INDEX idx_a ON users;

3. 很少查询的列不建索引

索引有维护代价。只有经常出现在 WHERE、JOIN ON、ORDER BY 中的列才值得建索引。

4. 数据量小的表不建索引

几十行的小表，全表扫描比走索引还快。MySQL 优化器在小表上会自动忽略索引。

-- 查看索引使用情况（MySQL 8.0+）
SELECT
    OBJECT_SCHEMA, OBJECT_NAME, INDEX_NAME,
    COUNT_READ, COUNT_FETCH
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE OBJECT_SCHEMA = '你的库名' AND INDEX_NAME IS NOT NULL
ORDER BY COUNT_READ DESC;

41.7 小结

• 最左前缀原则：联合索引从左到右依次匹配，跳过某列后续列用不到；
• 范围查询会中断后续列的索引使用，等值查询不会；
• 索引下推（ICP）在存储引擎层提前过滤，减少回表次数；
• 覆盖索引：查询列全在索引中，无需回表，EXPLAIN 显示 Using index；
• 常见索引失效：函数操作、隐式类型转换、LIKE '%xxx'、OR 有无索引列、不符合最左前缀、!=/NOT IN；
• 复合索引设计：等值在前、高区分度在前、范围和排序在后；
• 避免重复索引和冗余索引，定期检查索引使用情况。

下一节学 EXPLAIN 执行计划，看 MySQL 到底有没有走索引。