避免索引失效需遵循：1. 不对索引列使用函数或表达式；2. 联合索引遵守最左前缀原则；3. 避免使用!=、NOT IN等否定操作；4. LIKE查询不以%开头；5. 防止隐式类型转换；6. 合理利用覆盖索引；7. 使用EXPLAIN分析执行计划。
在MySQL中，索引失效会导致查询性能大幅下降。要避免索引失效，关键在于理解索引的工作机制以及常见的“陷阱”。以下是几个实用建议和注意事项。
1. 避免对索引列进行表达式或函数操作
如果在WHERE条件中对索引列使用函数或表达式，MySQL将无法使用该列的索引。
错误示例：
SELECT * FROM users WHERE YEAR(create_time) = 2023;
即使create_time有索引，YEAR()函数会让索引失效。
正确做法：
SELECT * FROM users WHERE create_time >= '2023-01-01' AND create_time
这样可以利用create_time的范围索引。
2. 遵循最左前缀原则（适用于联合索引）
联合索引（复合索引）要求查询条件从最左边的列开始，并连续使用，否则可能导致部分或全部索引失效。
例如：有联合索引 idx_name_age (name, age)
- ✅
WHERE name = '张三' AND age = 25— 可用索引 - ✅
WHERE name = '张三'— 可用索引（只用到name） - ❌
WHERE age = 25— 索引失效（跳过name）
若必须按age查询，应单独为age建索引，或调整联合索引顺序。
3. 避免在索引列上使用不等于（!= 或 ）和NOT IN
这类否定操作通常会导致全表扫描，因为索引更适合查找“存在”的值。
例如：
SELECT * FROM users WHERE status != 'inactive';
可能不会走索引，尤其是当大部分数据满足条件时。
优化思路：
- 改写为明确的正向条件，如：
status = 'active' - 或使用覆盖索引减少回表成本
4. 尽量避免在索引列上使用LIKE以通配符开头
以%开头的模糊查询会使索引失效。
错误示例：
-
WHERE name LIKE '%三'— 无法使用索引
正确方式：
-
WHERE name LIKE '张%'— 可以使用索引
如果必须做前后模糊匹配，可考虑使用全文索引（FULLTEXT）或Elasticsearch等外部工具。
5. 注意隐式类型转换导致索引失效
当索引列是字符串类型，而查询条件传入数字时，MySQL会尝试自动转换，导致索引失效。
示例：
-
SELECT * FROM users WHERE user_no = 123;（user_no 是 VARCHAR 类型）
MySQL会把每行的user_no转成数字比较，索引失效。
解决方法：
- 保持类型一致：
user_no = '123'
6. 合理使用覆盖索引
如果查询的所有字段都在索引中，MySQL无需回表，能显著提升性能。
示例：
- 索引：
(name, age, city) -
SELECT name, age FROM users WHERE name = '张三';— 覆盖索引，无需回表
尽量让常用查询能命中覆盖索引。
7. 不要过度依赖索引，定期分析执行计划
使用EXPLAIN命令查看SQL执行计划，确认是否真正使用了索引。
检查点：
-
type：避免ALL（全表扫描） -
key：确认使用的索引名称 -
Extra：避免Using filesort或Using temporary
基本上就这些。只要写SQL时多留意条件写法、数据类型和索引结构，大多数索引失效问题都能提前规避。
