十五年数据库做下来，我翻过很多文档，也踩过很多坑。最常被开发同事问到的一句话就是：
“我明明建了索引，为什么查询还是慢？”
这个问题听起来简单，但背后的坑能写一本书。今天我整理了 8 个索引失效的典型场景，每个都附上建表语句、查询 SQL、EXPLAIN 输出和修复方案。
不一定最全，但在我的项目里最实用。
先搞清楚一件事：怎么看索引有没有生效在讲具体场景之前，先统一一个基础动作——用 EXPLAIN 看执行计划。
代码语言：javascript复制EXPLAIN SELECT * FROM users WHERE name = '张三';重点关注这几个字段：
字段
含义
关键值
type
访问类型
const/ref/range = 好，ALL/index = 全表扫描 = 差
key
实际使用的索引
NULL = 没走索引
rows
预估扫描行数
越小越好
Extra
额外信息
Using index = 覆盖索引（好），Using filesort = 额外排序（差）
记住这张表，后面每个场景我们都会用 EXPLAIN 来验证。
场景 1：对索引列使用函数或表达式坑点：在 WHERE 条件中对索引列套函数，索引直接失效。
先建一张测试表：
代码语言：javascript复制CREATE TABLE orders (
id BIGINT PRIMARY KEY AUTO_INCREMENT,
order_no VARCHAR(32) NOT NULL,
amount DECIMAL(10,2),
created_at DATETIME NOT NULL,
INDEX idx_created_at (created_at)
) ENGINE=InnoDB;
-- 插入 10 万条测试数据
INSERT INTO orders (order_no, amount, created_at)
SELECT
CONCAT('ORD', LPAD(seq, 8, '0')),
RAND() * 10000,
DATE_SUB(NOW(), INTERVAL FLOOR(RAND() * 365) DAY)
FROM (
SELECT @rownum := @rownum + 1 AS seq
FROM information_schema.columns a, information_schema.columns b,
(SELECT @rownum := 0) r
LIMIT 100000
) tmp;错误写法：
代码语言：javascript复制EXPLAIN SELECT * FROM orders WHERE YEAR(created_at) = 2025;代码语言：javascript复制+----+-------------+--------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+---------------+------+---------+------+--------+-------------+
| 1 | SIMPLE | orders | ALL | NULL | NULL | NULL | NULL | 100000 | Using where |
+----+-------------+--------+------+---------------+------+---------+------+--------+-------------+type = ALL，全表扫描，索引完全没用上。
正确写法（范围查询替代函数）：
代码语言：javascript复制EXPLAIN SELECT * FROM orders
WHERE created_at >= '2025-01-01' AND created_at < '2026-01-01';代码语言：javascript复制+----+-------------+--------+-------+----------------+----------------+---------+------+-------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+-------+----------------+----------------+---------+------+-------+-----------------------+
| 1 | SIMPLE | orders | range | idx_created_at | idx_created_at | 5 | NULL | 27864 | Using index condition |
+----+-------------+--------+-------+----------------+----------------+---------+------+-------+-----------------------+type = range，索引命中，扫描行数从 10 万降到约 2.8 万。
补充：MySQL 8.0.13+ 支持函数索引（Functional Index），如果你确实需要对表达式建索引，可以用这个语法：
CREATE INDEX idx_year_created ON orders ((YEAR(created_at)));
但在老项目里大概率用的是 MySQL 5.7 或更早版本，所以"不套函数"这个原则还是得刻在脑子里。
场景 2：隐式类型转换坑点：索引列是 VARCHAR，查询条件用数字，MySQL 会把字符串转成数字来比较，索引失效。
代码语言：javascript复制CREATE TABLE users (
id BIGINT PRIMARY KEY AUTO_INCREMENT,
phone VARCHAR(20) NOT NULL,
name VARCHAR(50),
INDEX idx_phone (phone)
) ENGINE=InnoDB;错误写法：
代码语言：javascript复制-- phone 是 VARCHAR，但条件用了数字
EXPLAIN SELECT * FROM users WHERE phone = 13800138000;代码语言：javascript复制+----+-------------+-------+------+---------------+------+---------+------+-------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+-------+-------------+
| 1 | SIMPLE | users | ALL | idx_phone | NULL | NULL | NULL | 99876 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+-------+-------------+全表扫描。原因：MySQL 需要把 phone 列的每一行都转成数字再比较，相当于对索引列套了函数。
正确写法：
代码语言：javascript复制-- 加引号，类型匹配
EXPLAIN SELECT * FROM users WHERE phone = '13800138000';代码语言：javascript复制+----+-------------+-------+------+---------------+-----------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+-----------+---------+-------+------+-------------+
| 1 | SIMPLE | users | ref | idx_phone | idx_phone | 62 | const | 1 | Using where |
+----+-------------+-------+------+---------------+-----------+---------+-------+------+-------------+type = ref，精确命中，只扫描 1 行。
注意：反过来也一样——如果索引列是 INT，查询条件用字符串 '13800138000'，MySQL 会把字符串转数字，这种情况下索引不会失效（因为是对常量做转换，不是对列做转换）。但为了代码可读性和一致性，建议始终保持类型一致。
场景 3：违反联合索引的最左前缀原则坑点：联合索引 (a, b, c) 必须从最左列开始使用，跳过中间列会导致后面的列无法走索引。
代码语言：javascript复制CREATE TABLE products (
id BIGINT PRIMARY KEY AUTO_INCREMENT,
category VARCHAR(20),
brand VARCHAR(30),
price DECIMAL(10,2),
status TINYINT,
INDEX idx_cat_brand_price (category, brand, price)
) ENGINE=InnoDB;各种查询的索引命中情况：
代码语言：javascript复制-- 走索引 ✅ 最左列 category 命中
EXPLAIN SELECT * FROM products WHERE category = '手机';
-- 走索引 ✅ 最左两列命中
EXPLAIN SELECT * FROM products WHERE category = '手机' AND brand = '华为';
-- 走索引 ✅ 三列全命中
EXPLAIN SELECT * FROM products WHERE category = '手机' AND brand = '华为' AND price > 3000;
-- 不走索引 ❌ 跳过了 category
EXPLAIN SELECT * FROM products WHERE brand = '华为';
-- 部分走索引 ⚠️ 只走了 category，brand 被跳过，price 无法使用
EXPLAIN SELECT * FROM products WHERE category = '手机' AND price > 3000;最佳实践：设计联合索引时，按照「等值查询列在前，范围查询列在后」的原则排列。
代码语言：javascript复制-- 如果业务上经常 category = '手机' AND price > 3000
-- 应该把 category 放在 price 前面（已经是了），brand 可以不加
-- 如果业务上经常 brand = '华为' AND price > 3000
-- 应该单独建一个索引：
CREATE INDEX idx_brand_price ON products (brand, price);场景 4：LIKE 以通配符开头坑点：LIKE '%关键词' 无法走索引，因为 B+ 树是按前缀排序的，不知道前缀就没法定位。
代码语言：javascript复制-- 不走索引 ❌
EXPLAIN SELECT * FROM users WHERE name LIKE '%张';
-- 走索引 ✅
EXPLAIN SELECT * FROM users WHERE name LIKE '张%';如果业务确实需要模糊搜索，有三个替代方案：
代码语言：javascript复制-- 方案 1：覆盖索引（SELECT 只查索引列，避免回表）
-- 即使 LIKE '%张' 不走索引，但如果 SELECT 的列全在索引里，可以用索引扫描
EXPLAIN SELECT phone FROM users WHERE phone LIKE '%8000';
-- 方案 2：全文索引（MySQL 5.7+ InnoDB 支持）
ALTER TABLE articles ADD FULLTEXT INDEX ft_content (content) WITH PARSER ngram;
SELECT * FROM articles WHERE MATCH(content) AGAINST('数据库优化' IN BOOLEAN MODE);
-- 方案 3：上 Elasticsearch（数据量大、搜索需求复杂时的正解）我在项目里见过不少开发者用 LOCATE() 或 INSTR() 替代 LIKE，以为能绕过限制——不能。本质是一样的，函数作用在索引列上，索引就失效。
场景 5：OR 条件处理不当坑点：OR 两边的列如果索引不一致，可能导致整个查询无法走索引。
代码语言：javascript复制CREATE TABLE employees (
id BIGINT PRIMARY KEY AUTO_INCREMENT,
emp_no VARCHAR(20),
name VARCHAR(50),
department VARCHAR(30),
INDEX idx_emp_no (emp_no),
INDEX idx_name (name)
) ENGINE=InnoDB;代码语言：javascript复制-- 两边列都有单独索引，MySQL 可以用 Index Merge ✅
EXPLAIN SELECT * FROM employees WHERE emp_no = 'E001' OR name = '张三';
-- 但 Index Merge 不一定总是触发，取决于优化器判断
-- 更稳妥的写法：用 UNION 替代 OR
EXPLAIN
SELECT * FROM employees WHERE emp_no = 'E001'
UNION
SELECT * FROM employees WHERE name = '张三';对比两种写法的 EXPLAIN：
写法
type
说明
OR
index_merge 或 ALL
不确定，取决于数据分布和优化器
UNION
两次 ref
稳定走各自索引
我的经验：OR 在 MySQL 里的优化器行为不太稳定。如果你发现 OR 查询的 EXPLAIN 结果不理想，第一反应换成 UNION 试试。
场景 6：数据分布倾斜导致优化器"弃用"索引坑点：当某个值的数据占比过高时，优化器会认为全表扫描比走索引更快。
代码语言：javascript复制-- 假设 status 只有 0 和 1 两个值
-- 其中 status = 0 的占 95%，status = 1 的只占 5%
CREATE TABLE tasks (
id BIGINT PRIMARY KEY AUTO_INCREMENT,
title VARCHAR(100),
status TINYINT NOT NULL DEFAULT 0,
INDEX idx_status (status)
) ENGINE=InnoDB;
-- 插入 10 万条数据，95% 的 status = 0
-- ...（省略插入语句）
-- 查询 status = 0（占 95%），优化器可能弃用索引
EXPLAIN SELECT * FROM tasks WHERE status = 0;
-- type = ALL，全表扫描
-- 查询 status = 1（占 5%），优化器会走索引
EXPLAIN SELECT * FROM tasks WHERE status = 1;
-- type = ref修复方案：
代码语言：javascript复制-- 方案 1：强制使用索引（谨慎使用，需验证确实更快）
SELECT * FROM tasks FORCE INDEX(idx_status) WHERE status = 0;
-- 方案 2：使用覆盖索引减少回表开销
EXPLAIN SELECT status, id FROM tasks WHERE status = 0;
-- 方案 3：业务层面优化——如果经常查"未完成"的任务，
-- 换个思路建索引
ALTER TABLE tasks ADD INDEX idx_status_created (status, created_at);
-- status = 0 AND created_at > '2025-01-01' 可以缩小扫描范围这条最考验 DBA 的判断力：不是所有查询都需要走索引。当扫描大部分数据时，全表扫描确实比索引回表更快。优化器的选择有时候是对的，你要做的是理解它的逻辑，而不是盲目强制索引。
场景 7：字符集或排序规则不一致坑点：JOIN 时两个表的列字符集不同，会导致隐式转换，索引失效。
代码语言：javascript复制-- 表 A 用 utf8mb4
CREATE TABLE orders_utf8 (
id BIGINT PRIMARY KEY,
customer_code VARCHAR(32) CHARACTER SET utf8mb4,
INDEX idx_code (customer_code)
) ENGINE=InnoDB;
-- 表 B 用 utf8（注意：utf8 在 MySQL 里是 utf8mb3，不是真正的 UTF-8）
CREATE TABLE customers_utf8mb3 (
id BIGINT PRIMARY KEY,
code VARCHAR(32) CHARACTER SET utf8,
INDEX idx_code (code)
) ENGINE=InnoDB;代码语言：javascript复制-- JOIN 时字符集不一致，索引失效
EXPLAIN SELECT * FROM orders_utf8 o
JOIN customers_utf8mb3 c ON o.customer_code = c.code;
-- Extra 中会出现 "Using where"，且其中一个表的 type = ALL修复方案：
代码语言：javascript复制-- 方案 1：统一字符集（治本）
ALTER TABLE customers_utf8mb3
CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
-- 方案 2：查询时显式转换（治标）
SELECT * FROM orders_utf8 o
JOIN customers_utf8mb3 c
ON o.customer_code = CONVERT(c.code USING utf8mb4);踩坑提醒：MySQL 5.7 的默认字符集是 latin1，MySQL 8.0 改成了 utf8mb4。如果你在做数据库升级或数据迁移，这是最容易忽略的索引失效原因之一。
场景 8：统计信息过期坑点：MySQL 优化器依赖统计信息来估算行数和选择执行计划。如果统计信息过期，优化器可能做出错误判断。
代码语言：javascript复制-- 查看表的统计信息
SHOW INDEX FROM orders;
-- 手动更新统计信息
ANALYZE TABLE orders;
-- 查看索引基数（Cardinality）
-- Cardinality 越高，索引区分度越好什么时候需要手动更新统计信息：
场景
说明
大批量数据导入后
统计信息可能还是旧的
分区表合并后
分区统计可能不准确
执行计划突然变差
先 ANALYZE TABLE 试试
MySQL 重启后
InnoDB 统计信息默认持久化到磁盘，但某些配置下不持久化
配置统计信息持久化（MySQL 5.6.6+）：
代码语言：javascript复制-- 查看当前配置
SHOW VARIABLES LIKE 'innodb_stats_persistent';
-- 开启持久化（推荐）
SET GLOBAL innodb_stats_persistent = ON;
-- 设置自动更新的采样页数（默认 20，数据量大时建议调高）
ALTER TABLE orders STATS_SAMPLE_PAGES = 100;我的习惯：每次做完大批量数据操作（导入、归档、清理）之后，跑一遍 ANALYZE TABLE。花不了几秒钟，但能避免很多诡异的性能问题。这个方案不一定最酷，但在我见过的项目里最稳。
总结：一张速查表#
场景
核心原因
修复关键词
1
函数/表达式作用在索引列上
索引列被包装，B+树无法定位
范围查询替代函数
2
隐式类型转换
VARCHAR 列用数字查
类型一致
3
违反最左前缀原则
联合索引列顺序不对
等值在前，范围在后
4
LIKE 以通配符开头
B+树按前缀排序
全文索引 / ES
5
OR 条件处理不当
索引合并不确定
UNION 替代 OR
6
数据分布倾斜
优化器认为全表更快
覆盖索引 / 缩小范围
7
字符集/排序规则不一致
JOIN 时隐式转换
统一字符集
8
统计信息过期
优化器判断依据不准
ANALYZE TABLE
十五年做下来，排查索引问题没有什么银弹。核心就是三步：看 EXPLAIN、理解数据分布、根据业务场景做判断。
这个方法论不一定最酷，但在我经历过的项目里最稳。
后续我会继续分享锁等待与死锁排查、慢查询优化全流程这些话题。如果这篇文章对你有帮助，欢迎收藏，有问题可以在评论区讨论。
我是数据库工匠老O，十五年数据库领域老兵。关注我，一起把数据库这件事搞明白。