SQL索引并非建了就生效，失效主因是破坏有序性、确定性或原始值匹配；函数操作、类型不匹配、OR含非索引字段及联合索引顺序不当均会导致全表扫描。
SQL索引不是“建了就一定生效”，很多看似合理的查询，实际执行时却绕过索引走全表扫描。根本原因在于：索引（尤其是B+树）依赖有序性、确定性、原始值匹配——一旦破坏这三点，优化器就无法安全、高效地使用它。
函数或表达式操作索引列
对索引字段使用函数（如 YEAR()、SUBSTR()、UPPER()）、运算（如 age + 1 = 25）或类型转换（如 CAST()），会导致索引失效。
- ❌ 错误写法：
WHERE YEAR(create_time) = 2023或WHERE name LIKE CONCAT('%', ?) - ✅ 正确写法：
WHERE create_time >= '2023-01-01' AND create_time - ? 提示：若必须用函数查询，可考虑函数索引（MySQL 8.0+ 支持）或冗余生成列+索引
模糊查询位置不当
LIKE 匹配是否走索引，取决于通配符位置。B+树按字符串前缀排序，只能高效支持“左对齐”查找。
- ❌ 不走索引：
LIKE '%张'、LIKE '%三%'（首部含%） - ✅ 可走索引：
LIKE '张%'（前缀匹配）、LIKE '张_明'（固定长度通配） - ⚠️ 注意：
LIKE '张%' ESCAPE '\'中转义符不影响索引使用，但需确保语义清晰
联合索引未遵守最左前缀原则
复合索引 (a, b, c) 的 B+ 树叶子节点按 a → b → c 排序。跳过左侧列，后续列无法定位。
MySQL8 CRM Schema Design Expert
下载
面向 CRM 系统的 MySQL 8 数据库模式设计。当用户需要为客户关系管理（CRM）系统设计、评审、优化或生成数据库模式时，应使用此项技能。
- ❌ 失效场景：
WHERE b = 2 AND c = 3（没用a）、WHERE a > 10 AND c = 'x'（c在范围列右侧） - ✅ 有效场景：
WHERE a = 1、WHERE a = 1 AND b > 5、WHERE a = 1 AND b = 2 AND c LIKE 'y%' - ? 建议：高频等值查询列前置，范围/排序/模糊列靠后；必要时拆分或新增覆盖索引
数据类型不匹配引发隐式转换
当字段类型与查询值类型不一致，MySQL 自动做类型转换，导致索引列被“包装”成临时表达式，无法比对原始索引值。
- ❌ 常见陷阱：
VARCHAR字段查数字：WHERE user_no = 12345（实际字段是字符串） - ✅ 统一类型：
WHERE user_no = '12345'；INT字段加引号一般仍可用索引（MySQL 会反向转为整型） - ? 验证方法：用
EXPLAIN查看type是否为ref/range，key是否显示索引名
OR 条件中存在非索引字段
优化器面对 OR 时，只要任一条件无法利用索引，往往放弃整个索引路径，选择全表扫描更“稳妥”。
- ❌ 危险写法：
WHERE id = 100 OR username = 'tom'（仅id有索引） - ✅ 替代方案：
– 为username加索引
– 改用UNION ALL拆分：(SELECT ... WHERE id = 100) UNION ALL (SELECT ... WHERE username = 'tom') - ⚠️ 补充：即使两边都有索引，若含范围比较（如
id > 10 OR id ），也可能因优化器判断低效而弃用索引
其他易忽略的失效点
除上述高频场景外，以下情况也常导致索引“静默失效”：
-
NULL 值处理：索引默认不存 NULL（除非显式声明
INCLUDE NULL），IS NULL可能走索引，但IS NOT NULL在高 NULL 率下常被优化器跳过 -
否定条件：
!=、、NOT IN、NOT EXISTS因结果集分散，优化器倾向全表扫描 - 小表自动放弃索引：数据量极小（如
-
统计信息陈旧：表数据大量变更后未
ANALYZE TABLE，优化器基于错误基数选错执行计划
