索引失效的 8 个真实场景——一个老 DBA 的排查笔记

原创

数据工匠老o

发布于 2026-06-17 09:59:32

260

十五年数据库做下来，我翻过很多文档，也踩过很多坑。最常被开发同事问到的一句话就是：

“我明明建了索引，为什么查询还是慢？”

这个问题听起来简单，但背后的坑能写一本书。今天我整理了 8 个索引失效的典型场景，每个都附上建表语句、查询 SQL、EXPLAIN 输出和修复方案。

不一定最全，但在我的项目里最实用。

先搞清楚一件事：怎么看索引有没有生效

在讲具体场景之前，先统一一个基础动作——用 EXPLAIN 看执行计划。

EXPLAIN SELECT * FROM users WHERE name = '张三';

重点关注这几个字段：

字段	含义	关键值
type	访问类型	const/ref/range = 好，ALL/index = 全表扫描 = 差
key	实际使用的索引	NULL = 没走索引
rows	预估扫描行数	越小越好
Extra	额外信息	Using index = 覆盖索引（好），Using filesort = 额外排序（差）

记住这张表，后面每个场景我们都会用 EXPLAIN 来验证。

场景 1：对索引列使用函数或表达式

坑点：在 WHERE 条件中对索引列套函数，索引直接失效。

先建一张测试表：

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;

错误写法：

EXPLAIN SELECT * FROM orders WHERE YEAR(created_at) = 2025;

+----+-------------+--------+------+---------------+------+---------+------+--------+-------------+
| 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，全表扫描，索引完全没用上。

正确写法（范围查询替代函数）：

EXPLAIN SELECT * FROM orders 
WHERE created_at >= '2025-01-01' AND created_at < '2026-01-01';

+----+-------------+--------+-------+----------------+----------------+---------+------+-------+-----------------------+
| 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 会把字符串转成数字来比较，索引失效。

CREATE TABLE users (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    phone VARCHAR(20) NOT NULL,
    name VARCHAR(50),
    INDEX idx_phone (phone)
) ENGINE=InnoDB;

错误写法：

-- phone 是 VARCHAR，但条件用了数字
EXPLAIN SELECT * FROM users WHERE phone = 13800138000;

+----+-------------+-------+------+---------------+------+---------+------+-------+-------------+
| 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 列的每一行都转成数字再比较，相当于对索引列套了函数。

正确写法：

-- 加引号，类型匹配
EXPLAIN SELECT * FROM users WHERE phone = '13800138000';

+----+-------------+-------+------+---------------+-----------+---------+-------+------+-------------+
| 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) 必须从最左列开始使用，跳过中间列会导致后面的列无法走索引。

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;

各种查询的索引命中情况：

-- 走索引 ✅ 最左列 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;

最佳实践：设计联合索引时，按照「等值查询列在前，范围查询列在后」的原则排列。

-- 如果业务上经常 category = '手机' AND price > 3000
-- 应该把 category 放在 price 前面（已经是了），brand 可以不加

-- 如果业务上经常 brand = '华为' AND price > 3000
-- 应该单独建一个索引：
CREATE INDEX idx_brand_price ON products (brand, price);

场景 4：LIKE 以通配符开头

坑点：LIKE '%关键词' 无法走索引，因为 B+ 树是按前缀排序的，不知道前缀就没法定位。

-- 不走索引 ❌
EXPLAIN SELECT * FROM users WHERE name LIKE '%张';

-- 走索引 ✅
EXPLAIN SELECT * FROM users WHERE name LIKE '张%';

如果业务确实需要模糊搜索，有三个替代方案：

-- 方案 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 两边的列如果索引不一致，可能导致整个查询无法走索引。

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;

-- 两边列都有单独索引，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：数据分布倾斜导致优化器"弃用"索引

坑点：当某个值的数据占比过高时，优化器会认为全表扫描比走索引更快。

-- 假设 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

修复方案：

-- 方案 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 时两个表的列字符集不同，会导致隐式转换，索引失效。

-- 表 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;

-- JOIN 时字符集不一致，索引失效
EXPLAIN SELECT * FROM orders_utf8 o
JOIN customers_utf8mb3 c ON o.customer_code = c.code;
-- Extra 中会出现 "Using where"，且其中一个表的 type = ALL

修复方案：

-- 方案 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 优化器依赖统计信息来估算行数和选择执行计划。如果统计信息过期，优化器可能做出错误判断。

-- 查看表的统计信息
SHOW INDEX FROM orders;

-- 手动更新统计信息
ANALYZE TABLE orders;

-- 查看索引基数（Cardinality）
-- Cardinality 越高，索引区分度越好

什么时候需要手动更新统计信息：

场景	说明
大批量数据导入后	统计信息可能还是旧的
分区表合并后	分区统计可能不准确
执行计划突然变差	先 ANALYZE TABLE 试试
MySQL 重启后	InnoDB 统计信息默认持久化到磁盘，但某些配置下不持久化

配置统计信息持久化（MySQL 5.6.6+）：

-- 查看当前配置
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，十五年数据库领域老兵。关注我，一起把数据库这件事搞明白。

原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。

如有侵权，请联系 cloudcommunity@tencent.com 删除。

dba

mysql优化

原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。

如有侵权，请联系 cloudcommunity@tencent.com 删除。

dba

mysql优化

登录后参与评论

0 条评论

热度