📘 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）优先
- 高区分度：用户 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） |
🚀 实战检查清单（上线前必过）
如果你提供具体的表结构、高频 SQL 或 EXPLAIN 输出，我可以为你输出针对性的索引改造方案与执行计划解读。
扫一扫
439
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
