一句话总结
评估索引是否生效需“三看”：看执行计划是否命中索引（EXPLAIN）、看扫描行数是否骤降（rows）、看查询耗时是否锐减（性能监控）；失效常因“破坏索引结构”或“优化器弃用”（如函数计算、隐式转换、前导通配符等）
详细解析
一、判断方式
执行计划分析（EXPLAIN）——数据库的“导航路线图”
通过 EXPLAIN 命令解析查询路径，重点关注四列：
EXPLAIN SELECT * FROM users WHERE name = 'Alice';字段 | 索引生效标志 | 索引失效标志 | 原理比喻 |
type | ref（普通索引）range（范围扫描） | ALL（全表扫描） | 快递员按门牌号精准投递（索引） vs 挨家挨户敲门（全表扫描） |
key | 显示索引名（如 idx_name） | NULL | 导航仪显示正确路线 vs 迷路乱转 |
rows | 数值远小于表总量（如从10万→100行） | 接近表总行数 | 只需检查100户 vs 查遍整个小区 |
Extra | Using index（覆盖索引） | Using filesort（额外排序） | 包裹直接送到手 vs 中途转运站处理 |
案例：
若 type=ALL 且 key=NULL，则索引失效；若 type=ref 且 rows=50（表总量10万），则索引高效生效。
统计信息监控——索引的“健康体检报告”
通过数据库统计指标动态验证：
SHOW STATUS LIKE 'Handler_read%'; -- MySQL指标 | 健康值 | 异常风险 |
Handler_read_key | 持续高位（>90%） | 索引未被使用 |
Handler_read_rnd_next | 低位 | 随机扫描暴增（预示全表扫描） |
解读：
Handler_read_key 高 = 精准投递成功率高；Handler_read_rnd_next 高 = 快递员在小区里乱转（随机I/O）。
性能对比验证——索引的“速度测试仪”
- 有索引时：查询耗时毫秒级（如 5ms）
- 无索引时：查询耗时秒级（如 2s）
技巧：
对比 SELECT 语句在索引创建前后的执行时间，若速度提升10倍以上则索引生效。
二、常见索引失效场景及修复方案
场景1：破坏索引结构
失效原因 | 示例 | 修复方案 | 原理比喻 |
索引列用函数 | SELECT ... WHERE UPPER(name)='ALICE' | 函数移出列：name = UPPER('alice') | 修改钥匙形状（函数扭曲数据）→ 无法开锁 |
隐式类型转换 | phone（VARCHAR）与 WHERE phone=13812345678（数字） | 类型统一：phone='13812345678' | 英文书用中文目录查找 → 无法匹配 |
索引列参与计算 | WHERE salary*1.1 > 5000 | 计算移出列：salary > 5000/1.1 | 破坏索引数值连续性 → 导航失效 |
场景2：违反索引使用规则
失效原因 | 示例 | 修复方案 | 原理比喻 |
联合索引跳过最左列 | 索引 (age,name)，查询 WHERE name='Alice' | 补全最左列 或 新建单列索引 | 查字典跳过拼音首字母 → 只能整本翻 |
LIKE 前导通配符 | WHERE name LIKE '%Alice' | 改用后缀索引 或 全文检索 | 模糊查找书名中间字 → 无法用目录 |
OR 连接非索引列 | WHERE age=25 OR address='Beijing'（address无索引） | 拆为 UNION 或 为address建索引 | 两把钥匙一把生锈 → 放弃开锁 |
场景3：优化器主动弃用索引
失效原因 | 示例 | 修复方案 | 原理比喻 |
数据量过少 | 100行小表查询 | 无需修复，全表扫描更快 | 找小区3户人 → 问保安比查地图快 |
低选择性列 | gender 列（值只有'M'/'F'）建索引 | 改用复合索引（如 (gender,age)） | 90%住户姓“张”→ 按姓氏查无意义 |
统计信息过期 | 索引创建后未更新统计信息 | 定期执行 ANALYZE TABLE | 地图未更新新道路 → 导航绕远路 |
索引设计黄金法则
- 最左前缀原则：联合索引按查询频率降序排列（如 (a,b,c) 需按 a → a,b → a,b,c 顺序使用）
- 覆盖索引优先：索引包含查询所有字段，避免回表（如 SELECT id,name FROM users → 建 (id,name)）
- 高选择性优先：索引列唯一值比例 > 10%（如 user_id 比 gender 更适合索引）
诊断流程图
总结：索引健康度自检清单
检查项 | 通过标准 | 工具命令 |
执行计划类型 type | 非 ALL | EXPLAIN |
索引命中 key | 显示索引名 | EXPLAIN |
扫描行数 rows | < 总行数×1% | EXPLAIN |
隐式转换/函数 | 索引列无计算或转换 | 代码审查 |
联合索引顺序 | 查询条件匹配最左前缀 | 索引设计文档 |
统计信息更新 | 最近1周内执行过 ANALYZE | SHOW TABLE STATUS |