📘 MySQL 索引失效机理、创建规范与实战指南

在微服务与高并发场景中，索引是性能优化的第一道防线。但“索引失效”往往不是数据库的 Bug，而是 SQL 写法破坏了 B+ 树有序性 或 优化器（CBO）基于成本模型主动放弃索引。下面从底层原理、工程规范到生产监控，系统梳理。

---

一、 导致索引失效的典型 SQL 写法及底层原因

SQL 写法	示例	失效原因	底层机制（B+树 / CBO）
🔹 前置/模糊匹配	WHERE name LIKE '%张%'	B+ 树按前缀有序，无法定位起始节点	必须全表扫描；LIKE '张%' 可走索引
🔹 索引列参与运算/函数	WHERE YEAR(create_time)=2024 WHERE amount + 100 > 500	索引存储的是原始值，函数/运算后值无序	等价于全表逐行计算；应改为范围查询 create_time BETWEEN '2024-01-01' AND '2024-12-31'
🔹 隐式类型转换	WHERE phone = 13800138000（phone 为 VARCHAR）	MySQL 会将列数据隐式转为数字再比较	等价于 WHERE CAST(phone AS SIGNED) = ...，破坏索引有序性。参数类型必须与字段严格一致
🔹 违反联合索引最左前缀	索引 (a,b,c)，查询 WHERE b=1 或 WHERE a>1 AND b=2	B+ 树按 (a,b,c) 联合排序，跳过 a 直接查 b 无序	范围条件（>,<,LIKE, BETWEEN）右侧列无法用于索引定位，仅能用于 ICP 过滤
🔹 OR 条件未全覆盖索引	WHERE status=1 OR type=2（仅 status 有索引）	优化器为保证结果完整，常放弃索引	MySQL 5.6+ 可能走 Index Merge，但若估算成本高仍会全表扫描。建议拆分为 UNION ALL 或建联合索引
🔹 否定类操作符	WHERE status != 1 WHERE id NOT IN (1,2,3) WHERE name IS NOT NULL	过滤后数据量仍较大，回表成本 > 全表扫描	CBO 成本模型判定全表扫描更优；IS NULL 在低基数时可能走索引
🔹 ORDER BY / GROUP BY 方向不一致	ORDER BY create_time DESC, update_time ASC	索引单向有序，混用方向无法利用索引排序	触发 filesort；应统一方向或建复合排序索引
🔹 SELECT * 破坏覆盖索引	SELECT * FROM t WHERE idx_col = ?	需回表查所有字段，优化器可能跳过索引	明确指定字段，凑成覆盖索引可彻底消除回表

💡 核心认知：所谓“索引失效”，80% 是 优化器基于代价评估主动选择全表扫描。索引不是“建了就一定用”，而是“用不用取决于数据分布、统计信息与成本模型”。

---

二、 索引创建的核心原则与工程规范

✅ 1. 查询驱动，反推设计

• 先有 SQL，后有索引：基于慢查询日志、EXPLAIN 执行计划反推，禁止“凭空建索引”。
• 覆盖高频场景：优先为 Top 20% 的查询模式建索引，而非为所有字段建索引。

✅ 2. 区分度（Selectivity）优先

-- 区分度公式：COUNT(DISTINCT col) / COUNT(*)
-- > 0.8 优秀，0.5~0.8 良好，< 0.1 不建议单独建索引

• 高区分度：用户 ID、订单号、手机号 → ✅ 强烈建议
• 低区分度：状态码、性别、布尔值 → ❌ 单独建无效，需联合其他字段

✅ 3. 联合索引严格守左序

查询模式	推荐索引设计
WHERE a=? AND b=?	(a, b)
WHERE a=? ORDER BY b DESC	(a, b)
WHERE a>? AND b=?	(b, a)（等值左，范围右）
SELECT id, name, status FROM t WHERE type=?	(type, status, name, id)（覆盖索引）

✅ 4. 控制数量、长度与维护成本

• 单表二级索引 ≤ 5~8 个，过多会导致 INSERT/UPDATE/DELETE 写放大。
• 优先使用前缀索引：INDEX(name(10)) 节省空间，但需测试区分度。
• 索引列尽量不可变（频繁 UPDATE 索引列会导致页分裂、碎片化）。

✅ 5. 规范命名与元数据管理

• 普通索引：idx_表名_字段（如 idx_order_status_create）
• 唯一索引：uk_表名_字段
• 定期执行 ANALYZE TABLE 更新统计信息，确保优化器决策准确。

---

三、 索引使用时的注意事项与关键关注点

🔍 1. 警惕隐式转换（生产最常见坑）

字段类型	错误写法	正确写法
VARCHAR	WHERE phone = 13800138000	WHERE phone = '13800138000'
DECIMAL	WHERE amount = 100.0（传 Float）	传 BigDecimal 或字符串
DATETIME	WHERE create_time = '2024-05-09'	WHERE create_time BETWEEN '2024-05-09 00:00:00' AND '2024-05-09 23:59:59'

⚙️ 2. 慎用 Optimizer Hint

• FORCE INDEX、USE INDEX 仅作为临时兜底，优先通过改写 SQL、更新统计信息、调整索引结构解决。
• hint 会锁定执行计划，数据量增长后可能反而变慢。

3. 数据量变化会改变执行计划

• 小表（< 10 万行）：全表扫描可能比索引更快（避免回表开销）
• 大表（> 500 万行）：索引收益显著，但深度分页仍需游标/延迟关联
• 对策：定期 ANALYZE TABLE，压测覆盖生产数据量级

🔄 4. 索引合并（Index Merge）陷阱

• 多个单列索引 WHERE a=? OR b=? 可能触发 Index Merge Union，但性能通常不如一个联合索引 (a,b)。
• 优先建联合索引，减少优化器决策复杂度。

5. NULL 值处理策略

• IS NULL 在低基数时可走索引，IS NOT NULL 通常不走。
• 规范：业务允许时，字段声明 NOT NULL DEFAULT '' 或 0，避免 NULL 带来的索引与逻辑复杂性。

6. 配合分页优化

• 索引无法解决 LIMIT 1000000, 10 的性能问题（扫描成本仍在）。
• 必须配合 游标分页 或 延迟关联，索引仅加速定位起始点。

📡 7. 监控与治理

监控维度	工具/命令	告警阈值
未使用索引	sys.schema_unused_indexes	连续 30 天无使用
冗余/重复索引	pt-duplicate-key-checker	发现立即清理
索引碎片率	SHOW TABLE STATUS LIKE 'table_name'	Data_free / Data_length > 0.3
慢查询关联索引	slow_query_log + EXPLAIN	rows 扫描 > 10 万

---

四、 总结：索引设计的“道”与“术”

索引不是银弹，而是用空间换时间、用写入换读取的工程权衡。

维度	核心原则
🧠 认知层	索引失效 ≠ Bug，是 CBO 成本模型的选择；理解 B+ 树有序性 & 回表代价
🛠️ 设计层	查询驱动、区分度优先、最左前缀、覆盖索引、控制数量
⚡ 使用层	防隐式转换、拒函数运算、明字段列表、定期 ANALYZE
📈 治理层	监控未使用索引、清理冗余、压测验证、架构演进（分库分表/ES）

🚀 实战检查清单（上线前必过）

• EXPLAIN 中 type 是否为 ref/range/const，而非 ALL？
• Extra 是否出现 Using index（覆盖）或 Using where（合理过滤）？
• 是否避免 SELECT *、隐式转换、函数运算？
• 联合索引顺序是否匹配 WHERE/ORDER BY 模式？
• 单表索引数是否 ≤ 8，写性能是否达标？
• 是否配置慢查询日志 + 索引使用监控？

如果你提供具体的表结构、高频 SQL 或 EXPLAIN 输出，我可以为你输出针对性的索引改造方案与执行计划解读。