一、问题现象

单表数据 100 万 +，简单 where 条件查询耗时 20~60 秒，字段明明创建索引却未生效，explain 查看执行计划 type=ALL 全表扫描，是数据库优化经典疑难问题。

二、6 种索引失效场景 + 优化

四、额外优化：百万表分表思路

单表超 500 万数据建议水平分表，按用户 ID 哈希拆分，配合分区表提升查询效率。

总结

索引失效优先核对 SQL 写法，规避函数、隐式转换、前置模糊匹配，日常写 SQL 养成 explain 校验习惯。

三、优化验证方法

SQL 前加 explain，查看 key 字段是否使用索引、rows 扫描行数，优化前后对比耗时。

1. 索引字段使用函数（最常见）错误 SQL：select * from user where DATE(create_time)='2025-01-01'，函数包裹索引列失效优化：改写条件，避免索引列运算

   create_time >= '2025-01-01' AND create_time<'2025-01-02'

2. 隐式类型转换索引字段 varchar 类型，查询传入数字：where phone=13800138000，自动转换导致索引失效，统一加引号phone='13800138000'。
3. or 前后字段一个无索引where name='张三' or age=20，age 无索引全表扫描，拆分 union 查询替代 or。
4. like 前置百分号 % xxlike '%测试'无法走索引，改为like '测试%'，模糊查询后置 %。
5. 联合索引违背最左前缀原则联合索引（name,age,sex），查询仅用 age、sex，跳过首列 name，索引失效，调整查询条件顺序。
6. in 数据量过大、not in、!= 全表扫描大数据量 in 改用 inner join 关联查询，!= 无法使用索引，范围查询尽量缩小筛选区间。

总结

索引失效优先核对 SQL 写法，规避函数、隐式转换、前置模糊匹配，日常写 SQL 养成 explain 校验习惯