🌺The Begin🌺点点关注，收藏不迷路🌺 ⬇ ⬇ 底部 ⬇ ⬇

📌 前言

很多人认为"只要创建了索引，查询就会变快"，这是一个常见的误区。实际上，索引并不总是生效的，不当的SQL写法、不合理的数据分布或错误的索引设计都可能导致索引失效，让MySQL放弃使用索引而选择全表扫描。本文将系统梳理索引失效的常见场景，并通过EXPLAIN工具教你如何精准排查索引使用效果。

---

一、索引失效的常见场景

---

二、索引失效场景详解

2.1 对索引列使用函数

-- 建表
CREATE TABLE orders (
    id INT PRIMARY KEY,
    order_date DATETIME,
    amount DECIMAL(10,2),
    INDEX idx_date (order_date)
);

-- ❌ 索引失效：使用函数
EXPLAIN SELECT * FROM orders WHERE YEAR(order_date) = 2024;
-- type: ALL（全表扫描）

-- ✅ 正确写法：范围查询
EXPLAIN SELECT * FROM orders 
WHERE order_date >= '2024-01-01' AND order_date < '2025-01-01';
-- type: range（使用索引）

2.2 隐式类型转换

-- 电话号码使用VARCHAR存储
CREATE TABLE user (
    id INT PRIMARY KEY,
    phone VARCHAR(20),
    INDEX idx_phone (phone)
);

-- ❌ 索引失效：INT类型比较
EXPLAIN SELECT * FROM user WHERE phone = 13800138000;
-- 等价于：WHERE CAST(phone AS INT) = 13800138000
-- type: ALL

-- ✅ 正确写法：字符串类型
EXPLAIN SELECT * FROM user WHERE phone = '13800138000';
-- type: ref（使用索引）

类型转换规则：

• VARCHAR → INT：失效
• INT → VARCHAR：可能失效（取决于优化器）

2.3 LIKE通配符在前

-- 索引有效
EXPLAIN SELECT * FROM user WHERE name LIKE '张%';
-- type: range

-- ❌ 索引失效
EXPLAIN SELECT * FROM user WHERE name LIKE '%三';
EXPLAIN SELECT * FROM user WHERE name LIKE '%三%';
-- type: ALL

2.4 OR条件

-- 创建两个独立索引
CREATE INDEX idx_name ON user(name);
CREATE INDEX idx_age ON user(age);

-- ❌ 部分失效：只有name有索引，age无索引
-- 假设age没有索引
EXPLAIN SELECT * FROM user WHERE name = '张三' OR age = 25;
-- type: ALL（全表扫描）

-- ✅ 正确：确保OR两边都有索引
-- 或者使用UNION替代
SELECT * FROM user WHERE name = '张三'
UNION
SELECT * FROM user WHERE age = 25;

OR条件的规则：

• 两边都有索引 → 可能使用索引合并
• 一边无索引 → 全表扫描

2.5 不满足最左前缀

-- 联合索引
CREATE INDEX idx_name_age_city ON user(name, age, city);

-- ❌ 索引失效：跳过最左列
EXPLAIN SELECT * FROM user WHERE age = 25;
-- type: ALL

-- ✅ 索引有效
EXPLAIN SELECT * FROM user WHERE name = '张三';
-- type: ref，使用索引的name部分

-- ⚠️ 部分失效：跳过age列
EXPLAIN SELECT * FROM user WHERE name = '张三' AND city = '北京';
-- 只使用name列，city列不生效

2.6 范围查询右侧失效

-- 联合索引 (name, age, city)

-- ✅ name等值，age范围，city失效
EXPLAIN SELECT * FROM user 
WHERE name = '张三' AND age > 25 AND city = '北京';
-- 使用 name 和 age，city不生效

-- ❌ 范围查询在最左列，完全失效
EXPLAIN SELECT * FROM user WHERE name > '张' AND age = 25;
-- 只有name生效（范围查询），age不生效

2.7 NOT条件

-- ❌ 索引失效（大多数情况）
EXPLAIN SELECT * FROM user WHERE name != '张三';
EXPLAIN SELECT * FROM user WHERE name <> '张三';
EXPLAIN SELECT * FROM user WHERE NOT name = '张三';
EXPLAIN SELECT * FROM user WHERE name NOT IN ('张三', '李四');

-- ⚠️ IN条件（优化器可能选择索引）
-- 如果IN值较少且选择性好，可能使用索引
EXPLAIN SELECT * FROM user WHERE name IN ('张三', '李四');

2.8 IS NULL / IS NOT NULL

-- ✅ IS NULL 通常可以使用索引
EXPLAIN SELECT * FROM user WHERE name IS NULL;
-- type: ref

-- ⚠️ IS NOT NULL 取决于NULL值比例
-- 如果大部分值是NULL，可能使用索引
-- 如果大部分值非NULL，可能全表扫描
EXPLAIN SELECT * FROM user WHERE name IS NOT NULL;

2.9 数据分布导致优化器放弃索引

-- 假设gender只有两个值：'M'和'F'
CREATE INDEX idx_gender ON user(gender);

-- 即使有索引，也可能全表扫描
-- 因为优化器认为全表扫描更快
EXPLAIN SELECT * FROM user WHERE gender = 'M';
-- 如果M占50%数据，type可能是ALL

优化器决策因素：

• 预估扫描行数
• 回表成本
• 是否可以使用覆盖索引

---

三、EXPLAIN工具深度解读

3.1 EXPLAIN输出格式

EXPLAIN SELECT * FROM user WHERE name = '张三'\G

-- 输出示例
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: user
   partitions: NULL
         type: ref
possible_keys: idx_name
          key: idx_name
      key_len: 202
          ref: const
         rows: 10
     filtered: 100.00
        Extra: Using index condition

3.2 关键字段解读

3.3 type类型详解（从好到差）

type	含义	示例	性能
system	系统表，只有一行	系统表	⭐⭐⭐⭐⭐
const	主键或唯一索引等值查询	WHERE id = 1	⭐⭐⭐⭐⭐
eq_ref	联表查询，使用主键或唯一索引	JOIN ON t1.id = t2.id	⭐⭐⭐⭐
ref	非唯一索引等值查询	WHERE name = '张三'	⭐⭐⭐⭐
range	范围查询	WHERE age > 18	⭐⭐⭐
index	索引全扫描	SELECT age FROM user	⭐⭐
ALL	全表扫描	WHERE name LIKE '%张'	⭐

3.4 Extra信息解读

Extra	含义	建议
Using index	覆盖索引，无需回表	✅ 最佳
Using index condition	索引下推优化	✅ 良好
Using where	需要回表后过滤	⚠️ 可优化
Using filesort	需要额外排序	❌ 需优化
Using temporary	使用临时表	❌ 需优化
Using join buffer	无索引连接	❌ 添加索引

---

四、实战排查流程

4.1 启用慢查询日志

-- 开启慢查询日志
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 2;  -- 超过2秒记录
SET GLOBAL log_queries_not_using_indexes = ON;  -- 记录未使用索引的查询

-- 查看慢查询日志位置
SHOW VARIABLES LIKE 'slow_query_log_file';

-- 使用mysqldumpslow分析
-- mysqldumpslow -s t /var/log/mysql/slow.log

4.2 使用OPTIMIZER_TRACE

-- 开启优化器跟踪
SET SESSION optimizer_trace = 'enabled=on';

-- 执行SQL
SELECT * FROM user WHERE name = '张三';

-- 查看优化器决策过程
SELECT * FROM information_schema.OPTIMIZER_TRACE\G

-- 关闭
SET SESSION optimizer_trace = 'enabled=off';

4.3 实际案例排查

-- 案例1：慢查询排查
-- 问题SQL
SELECT * FROM orders WHERE DATE(create_time) = '2024-01-01';
-- 创建时间2分钟

-- 执行EXPLAIN
EXPLAIN SELECT * FROM orders WHERE DATE(create_time) = '2024-01-01';
-- type: ALL, rows: 100万

-- 优化方案
ALTER TABLE orders ADD INDEX idx_create_time(create_time);
-- 改写SQL
SELECT * FROM orders 
WHERE create_time >= '2024-01-01' AND create_time < '2024-01-02';
-- type: range, rows: 5000

---

五、索引优化 Checklist

优化要点总结

问题	错误示例	正确示例
函数操作	WHERE YEAR(date)=2024	WHERE date BETWEEN '2024-01-01' AND '2024-12-31'
类型转换	WHERE phone=13800138000	WHERE phone='13800138000'
LIKE前置	WHERE name LIKE '%三'	WHERE name LIKE '张%'
OR条件	WHERE a=1 OR b=2 (b无索引)	UNION 或两边都加索引
范围查询	WHERE a>1 AND b=2	把范围查询放最后

---

六、面试高频问题

Q1：为什么明明有索引，MySQL还是选择全表扫描？

优化器基于成本估算，如果全表扫描的成本低于使用索引（例如：数据量小、回表成本高、选择性差），优化器会选择全表扫描。

Q2：如何判断索引是否有效？

通过EXPLAIN分析：

• type 是否为 ALL 或 index
• key 是否为 NULL
• rows 预估扫描行数

Q3：索引下推（ICP）是什么？

MySQL 5.6+ 引入，在索引遍历过程中直接过滤不满足条件的记录，减少回表次数。

Q4：force index 强制使用索引好吗？

不推荐。FORCE INDEX 强制使用指定索引，但可能优化器选择的索引更优。一般只在优化器选择错误时临时使用。

-- 不推荐
SELECT * FROM user FORCE INDEX (idx_name) WHERE name = '张三';

-- 推荐：优化SQL，让优化器正确选择

---

七、总结

排查步骤	命令/方法
分析执行计划	EXPLAIN
查看慢查询	慢查询日志
查看索引使用情况	SHOW INDEX FROM table
分析索引选择性	COUNT(DISTINCT col)/COUNT(*)
查看优化器决策	OPTIMIZER_TRACE

---

如果觉得本文对你有帮助，欢迎点赞、收藏、评论三连支持！

🌺The End🌺点点关注，收藏不迷路🌺 ⬆ ⬆ 顶部 ⬆ ⬆