前言
在后端开发面试中，“索引失效的场景”是必考题；在生产环境中，它更是导致接口响应缓慢（甚至拖垮数据库）的头号杀手。
很多时候，你明明给字段加了索引，但查询速度依然慢如蜗牛。这时候，你就需要检查一下：你的索引真的生效了吗？
MySQL 的索引就像字典的目录。如果你查找字的方式不对（比如只知道一个字的偏旁，却不知道它的拼音首字母），目录就没用了，只能一页页去翻（全表扫描）。
今天我们盘点7个最容易导致索引失效的“坑”。
准备工作
假设我们有一张用户表 users，并且建立了一个复合索引 idx_name_age (name, age) 和一个单列索引 idx_phone (phone)。
SQL
CREATE TABLE users ( id INT PRIMARY KEY, name VARCHAR(20), age INT, phone VARCHAR(20), create_time DATETIME, KEY idx_name_age (name, age), KEY idx_phone (phone) );
场景一：模糊查询以 % 开头
这是最经典的新手错误。B+树索引是按照顺序排列的，如果你查“以X结尾”的数据，索引无法利用排序特性。
- ❌ 失效写法：
LIKE '%刘'或LIKE '%刘%'
SQL
SELECT * FROM users WHERE name LIKE '%刘';
- 解释：全表扫描。
- ✅ 有效写法：
LIKE '刘%'
SQL
SELECT * FROM users WHERE name LIKE '刘%';
- 解释：走索引（range），因为知道了开头，利用了“最左前缀”。
场景二：在索引列上做计算
不要在“等号左边”做任何运算，否则数据库需要对每一行数据进行计算后再对比，索引直接作废。
- ❌ 失效写法：
SQL
SELECT * FROM users WHERE age + 1 = 20;
- ✅ 有效写法：
SQL
SELECT * FROM users WHERE age = 20 - 1;
- 原则：把计算交给业务代码或等号右边。
场景三：在索引列上使用函数
同理，使用函数处理索引列也会导致全表扫描。
- ❌ 失效写法：
SQL
-- 想要查询2023年的用户 SELECT * FROM users WHERE YEAR(create_time) = 2023;
- ✅ 有效写法：
SQL
SELECT * FROM users WHERE create_time BETWEEN '2023-01-01' AND '2023-12-31';
场景四：隐式类型转换（这个坑最深！）
这是很多资深开发都会栽跟头的地方。 假设 phone 字段在数据库中是 VARCHAR 类型（字符串）。
- ❌ 失效写法：
SQL
-- 注意：这里的电话号码没有加单引号，是数字类型 SELECT * FROM users WHERE phone = 13800001234;
- 解释：MySQL 遇到字段类型不匹配（字符串 vs 数字）时，会自动把字符串转成数字进行对比。这就相当于在索引列上使用了隐式函数
CAST()，导致索引失效！ - ✅ 有效写法：
SQL
-- 加上单引号，保持类型一致 SELECT * FROM users WHERE phone = '13800001234';
场景五：违背“最左前缀法则”
对于复合索引 (name, age)，索引的构建是先按 name 排，name 相同再按 age 排。如果你跳过 name 直接查 age，索引就没用了。
- ❌ 失效写法：
SQL
-- 跳过老大，直接找老二 SELECT * FROM users WHERE age = 18;
- ✅ 有效写法：
SQL
-- 带上老大 SELECT * FROM users WHERE name = 'Tom' AND age = 18; -- 或者只查老大 SELECT * FROM users WHERE name = 'Tom';
场景六：使用 OR 条件
如果 OR 两边的条件，只要有一个没有索引，那么整个查询都会变成全表扫描（因为MySQL觉得既然要扫描那部分没索引的数据，不如索性全扫了）。
- 假设
name有索引，但在address没索引。 - ❌ 失效写法：
SQL
SELECT * FROM users WHERE name = 'Tom' OR address = 'Beijing';
- 解决办法：确保 OR 两边的字段都有索引，或者使用
UNION代替。
场景七：不等号 != 或 <>
虽然这不是绝对失效（取决于数据量和优化器），但在大多数情况下，查询“不等于”某值的数据，意味着要获取表中“绝大部分”数据，优化器通常会选择全表扫描而不是回表。
- ⚠️ 风险写法：
SQL
SELECT * FROM users WHERE name != 'Tom';
如何验证索引是否生效？—— EXPLAIN
别猜，用工具看！在你的 SQL 语句前加上 EXPLAIN 关键字。
SQL
EXPLAIN SELECT * FROM users WHERE name = 'Tom';
重点看 type 和 key 这两列：
- key：显示实际使用的索引。如果是
NULL，说明没走索引。 - type（效率从好到坏）：
system>const>eq_ref>ref>range>index>ALL- 如果 type 是
ALL，说明是全表扫描，必须优化！ - 如果 type 是
range或ref，说明索引生效了。
总结
索引不是万能药，乱用索引甚至会适得其反。口诀：
全值匹配我最爱，最左前缀要遵守； 带头大哥不能死，中间兄弟不能断； 索引列上少计算，范围之后全失效； 字符引号不能丢，SQL优化有门道。
