mysql 索引失效的场景

我给你整理了10个高频、可直接跑的索引失效练习用例，覆盖了最常见的坑，每个都有「场景 + SQL + EXPLAIN 结果 + 修正方案」，直接复制就能测试。

先建测试环境（一次性跑）

CREATE TABLE user_info (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(32),
    phone VARCHAR(11),
    age INT,
    gender TINYINT,
    create_time DATETIME,
    INDEX idx_phone(phone),
    INDEX idx_create_time(create_time),
    INDEX idx_name_age(name, age)
);

-- 插入几条测试数据
INSERT INTO user_info(name, phone, age, gender, create_time)
VALUES 
('user1', '13800000001', 19, 1, '2026-05-13 10:00:00'),
('user2', '13800000002', 20, 1, '2026-05-13 11:00:00'),
('user3', '13800000003', 21, 0, '2026-05-14 10:00:00');

练习用例（10个，每个都能验证）

1. 索引列上使用函数（最经典）

失效写法：

-- create_time 有索引，但用了 DATE() 函数
EXPLAIN SELECT * FROM user_info WHERE DATE(create_time) = '2026-05-13';
  • 结果：type: ALL，key: NULL（全表扫描）
  • 原因：索引列被函数包裹，优化器无法使用索引
  • 修正：
EXPLAIN SELECT * FROM user_info 
WHERE create_time >= '2026-05-13' AND create_time < '2026-05-14';

2. 字符串索引传数字（隐式类型转换）

失效写法：

-- phone 是 VARCHAR，却传了数字
EXPLAIN SELECT * FROM user_info WHERE phone = 13800000001;
  • 结果：type: ALL，key: NULL
  • 原因：MySQL 会把 phone 转为数字比较，导致索引失效
  • 修正：
EXPLAIN SELECT * FROM user_info WHERE phone = '13800000001';

3. 复合索引不满足最左前缀

失效写法：

-- 索引是 (name, age)，只查 age
EXPLAIN SELECT * FROM user_info WHERE age = 19;
  • 结果：type: ALL，key: NULL
  • 原因：不满足最左匹配原则，无法使用复合索引
  • 修正：
EXPLAIN SELECT * FROM user_info WHERE name = 'user1' AND age = 19;

4. 使用 OR 连接条件（部分场景失效）

失效写法：

-- 只有 phone 有索引，gender 没有
EXPLAIN SELECT * FROM user_info WHERE phone = '13800000001' OR gender = 1;
  • 结果：type: ALL，key: NULL
  • 原因：OR 后面的条件没有索引，导致整个索引失效
  • 修正：给 gender 建索引，或者拆成 UNION：
EXPLAIN SELECT * FROM user_info WHERE phone = '13800000001'
UNION
SELECT * FROM user_info WHERE gender = 1;

5. 使用 NOT、!=、<> 等不等条件

失效写法：

EXPLAIN SELECT * FROM user_info WHERE phone != '13800000001';
  • 结果：type: ALL，key: NULL（数据量大时优化器会放弃索引）
  • 原因：不等条件通常会过滤掉大部分数据，优化器认为全表扫描更快
  • 修正：如果数据占比低，可以用 FORCE INDEX 验证，或业务上尽量用等值/范围条件替代

6. 使用 LIKE '%xxx' 前缀模糊匹配

失效写法：

EXPLAIN SELECT * FROM user_info WHERE phone LIKE '%00000001';
  • 结果：type: ALL，key: NULL
  • 原因：前缀模糊匹配无法使用 B+ 树索引
  • 修正：
EXPLAIN SELECT * FROM user_info WHERE phone LIKE '1380000000%'; -- 前缀固定

7. 使用 IN / NOT IN 数据量过大

失效写法：

-- 假设表中有大量数据，IN 里的值占比很高
EXPLAIN SELECT * FROM user_info WHERE phone IN ('13800000001', '13800000002', '13800000003', ...);
  • 结果：type: ALL，key: NULL
  • 原因：IN 列表过长，优化器放弃索引
  • 修正：控制 IN 列表长度，或用 JOIN 替代

8. 索引列参与运算

失效写法：

EXPLAIN SELECT * FROM user_info WHERE age + 1 = 20;
  • 结果：type: ALL，key: NULL
  • 原因：索引列参与了运算，无法使用索引
  • 修正：
EXPLAIN SELECT * FROM user_info WHERE age = 19;

9. 范围条件导致复合索引后续列失效

失效写法：

-- 索引是 (name, age)，age 用了范围条件
EXPLAIN SELECT * FROM user_info WHERE name = 'user1' AND age > 18;
  • 结果：只能用到 name 部分的索引，age 无法使用
  • 原因：复合索引中，范围条件后面的列无法使用索引
  • 修正：如果需要 age 也走索引，调整索引顺序为 (age, name)

10. 数据分布不均导致优化器放弃索引

失效场景：

-- 假设表中 90% 的数据 create_time 都是 '2026-05-13'
EXPLAIN SELECT * FROM user_info WHERE create_time = '2026-05-13';
  • 结果：type: ALL，key: NULL
  • 原因：优化器认为索引查询需要回表，不如全表扫描快
  • 验证：可以用 FORCE INDEX 强制走索引
EXPLAIN SELECT * FROM user_info FORCE INDEX(idx_create_time) WHERE create_time = '2026-05-13';

练习方式

  1. 把上面的失效 SQL 挨个跑，看 EXPLAIN 的 type 和 key 字段
  2. 对比修正前后的执行计划差异
  3. 把每个场景的原因和修正方法记下来，面试直接用
posted @ 2026-05-13 14:14  wuyingchun1987  阅读(10)  评论(0)    收藏  举报