MySQL 索引失效的 10 大实战场景与 EXPLAIN 验证

问题背景

"索引建了，SQL 也能跑，但就是慢"，这是DBA和开发同学日常对话里最常见的场景之一。明明字段上有索引，WHERE 条件也用了这个字段，可 EXPLAIN 一看，执行计划里却是 type=ALL（全表扫描），或者明明有更优的索引却没有被选中。

MySQL 索引失效的原因五花八门：有些是 SQL 写法问题，有些是数据特征问题，有些是优化器自己做的决策。这篇文章整理了 10 个最常碰到的索引失效场景，每个都给出现象描述、原理分析、复现 SQL、修复方案和验证方法。

环境准备：建表和造数据

为了方便理解，所有场景都用同一套表结构来演示。

-- 示例表：订单表
CREATE TABLE `t_order_demo` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `order_no` varchar(32) NOT NULL,
  `user_id` bigint NOT NULL,
  `status` tinyint NOT NULL DEFAULT 1,
  `amount` decimal(10,2) NOT NULL DEFAULT 0.00,
  `create_time` datetime NOT NULL,
  `update_time` datetime NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_order_no` (`order_no`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_status` (`status`),
  KEY `idx_user_status` (`user_id`, `status`),
  KEY `idx_create_time` (`create_time`),
  KEY `idx_amount` (`amount`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 插入测试数据（存储过程造 10 万条）
DELIMITER $$
CREATE PROCEDURE generate_test_data()
BEGIN
  DECLARE i INT DEFAULT 1;
  DECLARE user_ids INT DEFAULT 100;
  DECLARE statuses INT DEFAULT 5;

  WHILE i <= 100000 DO
    INSERT INTO t_order_demo (order_no, user_id, status, amount, create_time, update_time)
    VALUES (
      CONCAT('ORD', LPAD(i, 8, '0')),
      FLOOR(1 + RAND() * user_ids),
      FLOOR(1 + RAND() * statuses),
      ROUND(10 + RAND() * 990, 2),
      DATE_SUB(NOW(), INTERVAL FLOOR(RAND() * 365) DAY),
      NOW()
    );
    SET i = i + 1;
  END WHILE;
END$$
DELIMITER ;

-- 执行存储过程（10 万条数据，耗时约 30-60 秒）
CALL generate_test_data();

-- 收集统计信息（关键！）
ANALYZE TABLE t_order_demo;

建好表之后，务必确认统计信息准确：

-- MySQL 8.0 可以直接查 table_statistics（Percona Server）
-- MySQL 官方版用 EXPLAIN 验证基数
EXPLAIN SELECT * FROM t_order_demo WHERE user_id = 1;
-- rows 列应该反映实际行数

场景 1：函数或运算操作导致索引失效

现象
WHERE 条件里对索引字段用了函数（如 YEAR()、DATE()、LEFT()、SUBSTRING()）或者算术运算（+、-、*、/），导致执行计划走全表扫描。

原理
索引树是按照字段的原始值构建的。如果在 WHERE 中对字段用函数或运算，MySQL 需要把函数作用到每一行的索引列上才能判断是否匹配——这就无法利用 B+ 树的有序性，只能逐行扫描。

复现 SQL

-- 索引字段：create_time
-- 慢：函数运算
SELECT * FROM t_order_demo
WHERE YEAR(create_time) = 2026;

-- 慢：算术运算
SELECT * FROM t_order_demo
WHERE amount + 100 > 500;

-- 慢：字符串拼接
SELECT * FROM t_order_demo
WHERE CONCAT('ORD_', order_no) = 'ORD_00012345';

-- 慢：隐式函数（MySQL 内部行为）
SELECT * FROM t_order_demo
WHERE order_no = 12345;  -- order_no 是 varchar，传入 int

-- 验证执行计划
EXPLAIN SELECT * FROM t_order_demo WHERE YEAR(create_time) = 2026;

-- 输出示例（应该是 type=ALL，全表扫描）：
-- +----+-------------+-------------+------+---------------+------+---------+------+--------+-------------+
-- | id | select_type | table       | type | key           | rows | filtered| Extra                 |
-- +----+-------------+-------------+------+---------------+------+---------+------+----------+-------------+
-- |  1 | SIMPLE      | t_order_demo| ALL  | NULL          | 100000|   10.00| Using where          |
-- +----+-------------+-------------+------+---------------+------+---------+------+----------+-------------+
-- key 为 NULL，说明没有索引被使用

修复方案
核心思路是把函数和运算挪到等号右边，让索引列以原始形态参与比较。

-- 方案1：改成范围查询（针对日期）
SELECT * FROM t_order_demo
WHERE create_time >= '2026-01-01 00:00:00'
AND create_time < '2027-01-01 00:00:00';

-- 方案2：用 DATE() 但保留范围（更精确）
SELECT * FROM t_order_demo
WHERE DATE(create_time) >= '2026-01-01'
AND DATE(create_time) < '2027-01-01';
-- 注意：DATE() 仍然会使索引失效，上层DATE()函数仍会逐行计算
-- 最好改写为范围查询

-- 方案3：amount + 100 > 500 改成 amount > 400
SELECT * FROM t_order_demo
WHERE amount > 400;

-- 方案4：类型不一致时，确保类型匹配
-- order_no 是 varchar，查询时用字符串
SELECT * FROM t_order_demo
WHERE order_no = '00012345';  -- 加引号，字符串字面量

-- 方案5：如果是模糊查询开头，用 LIKE 替代函数
-- 慢：WHERE LEFT(order_no, 3) = 'ORD'
-- 快：WHERE order_no LIKE 'ORD%'

验证

-- 改写后验证执行计划
EXPLAIN SELECT * FROM t_order_demo
WHERE create_time >= '2026-01-01 00:00:00'
AND create_time < '2027-01-01 00:00:00';

-- 期望输出：
-- type = range（范围扫描）
-- key = idx_create_time（使用索引）
-- rows 大幅减少

场景 2：隐式类型转换

现象
字段定义是 varchar，但查询时传了 int；或者字段定义是 bigint，查询时传了 '123'（字符串）。MySQL 在比较前会做类型转换，导致索引失效。

原理
MySQL 在比较不同类型的值时，会按一定规则做隐式类型转换，方向取决于字段类型。如果 varchar 字段和 int 比较，会把所有 varchar 值转成数字——这个转换发生在每一行上，无法使用索引。

复现 SQL

-- user_id 是 bigint NOT NULL
-- 慢：传入字符串
SELECT * FROM t_order_demo WHERE user_id = '12345';

-- 快：传入数字
SELECT * FROM t_order_demo WHERE user_id = 12345;

-- status 是 tinyint
-- 慢
SELECT * FROM t_order_demo WHERE status = '2';

-- 快
SELECT * FROM t_order_demo WHERE status = 2;

-- 验证隐式转换
EXPLAIN SELECT * FROM t_order_demo WHERE user_id = '12345';
-- type = ALL，全表扫描

修复方案
应用层确保传入参数的类型和数据库字段类型一致：

-- Java 层：确保参数类型是 Long 而非 String
// 慢：PreparedStatement.setString(1, "12345")
// 快：PreparedStatement.setLong(1, 12345L)

-- Python 层：用整型而非字符串
# 慢：cursor.execute("SELECT * FROM t_order WHERE user_id = %s", "12345")
# 快：cursor.execute("SELECT * FROM t_order WHERE user_id = %s", 12345)

-- 强制类型转换（在 SQL 层补救，不推荐作为主要方案）
SELECT * FROM t_order_demo WHERE user_id = CAST('12345' AS UNSIGNED);

验证

EXPLAIN SELECT * FROM t_order_demo WHERE user_id = 12345;
-- 期望：type = ref 或 eq_ref，key = idx_user_id

场景 3：LIKE 前缀通配符

现象
用 LIKE '%关键字' 或 LIKE '%关键字%' 查询时，索引完全失效。

原理
B+ 树索引按照从左到右的顺序组织数据。'%abc' 和 '%abc%' 这类条件在索引最左边就是通配符，无法确定有序的起点，只能扫描整个索引（等价于全表扫描）。'abc%' 则可以利用索引，因为它有确定的前缀。

复现 SQL

-- order_no 字段有索引：KEY `idx_order_no` (`order_no`)
-- 慢：前缀通配符
SELECT * FROM t_order_demo WHERE order_no LIKE '%00012345%';
SELECT * FROM t_order_demo WHERE order_no LIKE '%00012345';

-- 快：后缀通配符（可用索引）
SELECT * FROM t_order_demo WHERE order_no LIKE 'ORD0001%';

EXPLAIN SELECT * FROM t_order_demo WHERE order_no LIKE '%00012345%';
-- type = index（全索引扫描，不是 ALL）
-- key = uk_order_no（扫描整个索引）
-- rows = 100000

修复方案  

根据业务场景选择：

-- 方案1：如果是后缀匹配（订单号通常有规律），反转存储
-- 新增一列 order_no_reversed 存储反转后的订单号
ALTER TABLE t_order_demo ADD COLUMN order_no_rev VARCHAR(32);
UPDATE t_order_demo SET order_no_rev = REVERSE(order_no);
CREATE INDEX idx_order_rev ON t_order_demo(order_no_rev);

-- 查询时反转关键字
SELECT * FROM t_order_demo WHERE order_no_rev LIKE REVERSE('%12345');

-- 方案2：使用全文索引（MySQL 5.6+ InnoDB 支持）
ALTER TABLE t_order_demo ADD FULLTEXT INDEX ft_order_no (order_no);
SELECT * FROM t_order_demo WHERE MATCH(order_no) AGAINST('+00012345' IN BOOLEAN MODE);

-- 方案3：应用层先模糊查 Redis/ES，再精确查 MySQL
-- 不适用于纯数据库方案

-- 方案4：调整前缀匹配为后缀匹配（如果业务允许）
-- 例如存储时保证订单号有固定前缀

注意：如果数据量不大（几万条以内），LIKE 全表扫描的性能可能也在可接受范围内，不必过度优化。

验证

-- 验证全文索引
EXPLAIN SELECT * FROM t_order_demo WHERE MATCH(order_no) AGAINST('+00012345' IN BOOLEAN MODE);
-- 期望：type = fulltext，key = ft_order_no

场景 4：OR 连接条件中缺少索引列

现象
WHERE 条件里用了 OR，但 OR 两边的字段不是都有索引，导致查询退化为全表扫描。

原理
A OR B 的语义是：满足 A 条件或者满足 B 条件。如果 A 有索引但 B 没有，MySQL 需要扫描有索引的 A 部分，再扫描没有索引的 B 部分（即整个表），然后把两部分结果取并集。优化器认为这种情况下全表扫描比走索引再加全表扫描更快（因为索引扫描后还要回表取其他字段），所以直接选了全表扫描。

复现 SQL

-- user_id 有索引，status 没有索引
-- 慢：OR 一边有索引，一边没有
SELECT * FROM t_order_demo WHERE user_id = 1 OR status = 2;

-- 快：拆分成两个查询 UNION
SELECT * FROM t_order_demo WHERE user_id = 1
UNION ALL
SELECT * FROM t_order_demo WHERE status = 2 AND user_id != 1;

-- 快2：给 status 加索引
ALTER TABLE t_order_demo ADD INDEX idx_status (status);

-- 再验证
EXPLAIN SELECT * FROM t_order_demo WHERE user_id = 1 OR status = 2;

修复方案

-- 方案1：给 OR 两边的字段都加索引
ALTER TABLE t_order_demo ADD INDEX idx_status (status);

-- 方案2：拆分为 UNION 查询
SELECT * FROM t_order_demo WHERE user_id = 1
UNION ALL
SELECT * FROM t_order_demo WHERE status = 2 AND user_id != 1;
-- 注意：第二个条件要加上 user_id != 1，否则会重复

-- 方案3：改用 IN（逻辑等价于 OR，但行为不同）
SELECT * FROM t_order_demo WHERE user_id IN (1, 2, 3);
-- IN 的每个值都会独立使用索引（MySQL 5.7+）

-- 方案4：业务上合并条件，减少 OR 使用

验证

-- 给 status 加索引后
EXPLAIN SELECT * FROM t_order_demo WHERE user_id = 1 OR status = 2;
-- 期望：type = index_merge，key = idx_user_id, idx_status
-- Extra = Using union(idx_user_id, idx_status); Using where

场景 5：违反联合索引最左前缀原则

现象
创建了联合索引 (user_id, status, create_time)，但查询条件只用了 status 或只用了 create_time，导致索引失效。

原理
联合索引的 B+ 树按从左到右的顺序组织多列。索引 (A, B, C) 相当于同时建立了 (A)、(A, B)、(A, B, C) 三个索引。查询条件必须从最左边的列开始，连续使用前缀列，才能利用索引。跳过 A 直接用 B，或者只用 C，都无法利用这个联合索引。

复现 SQL

-- 联合索引：idx_user_status_create (user_id, status, create_time)
-- 慢：跳过第一列
SELECT * FROM t_order_demo WHERE status = 2;

-- 慢：跳过前两列
SELECT * FROM t_order_demo WHERE create_time > '2026-01-01';

-- 快：使用最左前缀
SELECT * FROM t_order_demo WHERE user_id = 1;
SELECT * FROM t_order_demo WHERE user_id = 1 AND status = 2;
SELECT * FROM t_order_demo WHERE user_id = 1 AND status = 2 AND create_time > '2026-01-01';

EXPLAIN SELECT * FROM t_order_demo WHERE status = 2;
-- type = ALL，全表扫描，key = NULL

修复方案

-- 方案1：调整 SQL 使用最左前缀
-- 如果业务需要按 status 单独查询，给 status 加单独索引
ALTER TABLE t_order_demo ADD INDEX idx_status (status);

-- 方案2：创建满足查询模式的联合索引
-- 如果经常按 (user_id, create_time) 查询但不用 status
ALTER TABLE t_order_demo ADD INDEX idx_user_create (user_id, create_time);

-- 方案3：组合多个常用查询场景，平衡索引数量
-- 一般来说，索引数量控制在 5-6 个以内

最左前缀匹配示意：

查询条件	是否使用 idx(user_id, status, create_time)
user_id = 1	是（只用第一列）
user_id = 1 AND status = 2	是（连续使用前两列）
user_id = 1 AND status = 2 AND create_time > '2026-01-01'	是（使用全部三列）
status = 2	否（跳过第一列）
create_time > '2026-01-01'	否（跳过前两列）
user_id = 1 AND create_time > '2026-01-01'	部分使用（用到第一列，但中间断了）

验证

-- 验证联合索引的使用情况
EXPLAIN SELECT * FROM t_order_demo
WHERE user_id = 1 AND create_time > '2026-01-01';

-- 如果 Extra 显示 Using index condition（ICP），说明部分用到了索引
-- 如果 Extra 显示 Using where; Using index，说明用到了覆盖索引

场景 6：数据量过小时优化器不走索引

现象
表里只有几百或几千条数据，SQL 查询条件也匹配索引，可 EXPLAIN 显示 type=ALL（全表扫描），而不是 type=ref（索引查找）。

原理
MySQL 优化器基于成本估算选择执行计划。当表很小时，全表扫描的成本可能低于索引查找（因为索引查找需要先访问索引树，再回表取数据，做了两次 I/O）。MySQL 认为直接顺序读表比走两次随机读更划算。这是优化器的正常行为，不是索引真的“失效”了。

复现 SQL

-- 表只有 500 行
-- 慢（但实际可能很快，因为数据量小）
SELECT * FROM t_order_demo WHERE user_id = 1;
-- type = ALL

-- 快（强制使用索引）
SELECT * FROM t_order_demo USE INDEX (idx_user_id) WHERE user_id = 1;
-- type = ref

修复方案
这是优化器的成本选择，一般不需要修复（小表全表扫描也很快）。但如果真想强制用索引：

-- 强制使用索引（不推荐在生产环境作为常规手段）
SELECT * FROM t_order_demo USE INDEX (idx_user_id) WHERE user_id = 1;

-- 如果表开始变大了（数据量超过 10 万），优化器会自动选择索引
-- 可以用 ANALYZE TABLE 更新统计信息，帮助优化器做更准确的决策
ANALYZE TABLE t_order_demo;

注意：生产环境中表通常不会只有几百行。如果小表全表扫描变慢了，先确认是不是数据确实在增长，而不是一味强制使用索引。

验证

EXPLAIN SELECT * FROM t_order_demo USE INDEX (idx_user_id) WHERE user_id = 1;
-- 查看 type 是否从 ALL 变成 ref

场景 7：统计信息不准确

现象
表数据量已经很大（几百万行），但 EXPLAIN 的 rows 列显示的数字与实际不符（比如显示 100 行但实际返回 10 万行），导致优化器选错索引。

原理
MySQL 优化器依赖表的统计信息（行数、基数、索引分布等）来估算成本。统计信息不准确时，优化器会做出错误的决策。统计信息不准的几个常见原因：

1. 表刚创建或刚大量写入数据，还没来得及更新统计信息
2. 表做了大量 DELETE 操作，导致统计信息过时
3. ANALYZE TABLE 未执行或执行频率不够
4. 用了 SHOW TABLE STATUS 的近似值（不是精确值）

复现 SQL

-- 先批量删除大量数据
DELETE FROM t_order_demo WHERE user_id BETWEEN 1 AND 50;
-- 删除后表还有 5 万行左右

-- 查看统计信息（不准确）
SHOW TABLE STATUS LIKE 't_order_demo';
-- Rows 显示的可能是删除前的数字

-- 此时查询 user_id = 1（实际只有 10 条）的执行计划可能有问题
EXPLAIN SELECT * FROM t_order_demo WHERE user_id = 1;
-- 优化器可能认为 user_id = 1 的数据很多，选择了全表扫描而不是索引

修复方案

-- 方案1：执行 ANALYZE TABLE 更新统计信息
ANALYZE TABLE t_order_demo;

-- 验证统计信息已更新
SHOW TABLE STATUS LIKE 't_order_demo';
-- Rows 列应该反映实际行数

-- 方案2：如果统计信息持续不准确，检查 innodb_stats_auto_recalc
SHOW VARIABLES LIKE 'innodb_stats_auto_recalc';
-- 默认 ON，表中超过 10% 数据变化时会自动触发统计信息更新

-- 方案3：手动设置统计信息采样率（MySQL 5.7+）
SET GLOBAL innodb_stats_persistent_sample_pages = 200;
-- innodb_stats_persistent = ON 时，采样页数越多，统计越准确，但 ANALYZE 更慢

-- 方案4：强制使用特定索引（应急，不推荐长期使用）
SELECT * FROM t_order_demo FORCE INDEX (idx_user_id) WHERE user_id = 1;
-- FORCE INDEX 会强制优化器使用指定索引，但可能不如优化器选择的更好

验证

-- 执行 ANALYZE TABLE 后
ANALYZE TABLE t_order_demo;
EXPLAIN SELECT * FROM t_order_demo WHERE user_id = 1;
-- rows 列应该接近实际行数

场景 8：WHERE 子句使用 IS NULL / IS NOT NULL

现象
字段有索引，但用了 IS NULL 或 IS NOT NULL 后，索引失效。

原理
B+ 树索引不存储 NULL 值的行（对于允许 NULL 的列）。如果 IS NULL 要返回大量数据（很多行都有 NULL），MySQL 可能认为直接全表扫描更快。对于 IS NOT NULL，MySQL 需要找到所有非 NULL 的值，但索引的有序性在这里优势不明显（NULL 值在索引中的位置不固定），也可能选择全表扫描。

复现 SQL

-- amount 字段有索引 KEY `idx_amount` (`amount`)
-- 假设大部分订单 amount > 0，只有少量 0 或 NULL

-- 慢
SELECT * FROM t_order_demo WHERE amount IS NULL;

-- 慢
SELECT * FROM t_order_demo WHERE amount IS NOT NULL;

-- 快：用具体的值范围替代
SELECT * FROM t_order_demo WHERE amount IS NOT NULL AND amount != 0;

EXPLAIN SELECT * FROM t_order_demo WHERE amount IS NOT NULL;
-- type 可能变成 ALL 或 index（全索引扫描）

修复方案

-- 方案1：用具体范围替代 IS NULL/IS NOT NULL
SELECT * FROM t_order_demo WHERE amount >= 0;

-- 方案2：把 NULL 转成具体默认值
UPDATE t_order_demo SET amount = 0 WHERE amount IS NULL;
-- 改为 NOT NULL DEFAULT 0
ALTER TABLE t_order_demo MODIFY COLUMN amount DECIMAL(10,2) NOT NULL DEFAULT 0.00;

-- 方案3：利用覆盖索引
-- 如果 amount 有索引，但查 SELECT * 会回表
-- 改成只查 amount 字段
SELECT amount FROM t_order_demo WHERE amount IS NOT NULL;
-- 这样可以利用覆盖索引，避免回表

-- 方案4：COUNT(*) 配合 IS NOT NULL（不走索引但也有优化空间）
SELECT COUNT(*) FROM t_order_demo WHERE amount IS NOT NULL;
-- 可以用覆盖索引加速
SELECT COUNT(amount) FROM t_order_demo;
-- COUNT(列名) 不统计 NULL 值，结果相同

验证

EXPLAIN SELECT * FROM t_order_demo WHERE amount >= 0;
-- type = range，key = idx_amount

场景 9：DISTINCT 和 GROUP BY 的索引使用

现象
对某个字段用 DISTINCT 或 GROUP BY 时，查询很慢，EXPLAIN 显示 Using temporary; Using filesort。

原理
GROUP BY 的默认行为是先分组（排序），再输出。如果 GROUP BY 的字段有索引，MySQL 可以利用索引的有序性直接分组，不需要额外的排序和临时表。如果字段没有索引，就需要先排序（Using filesort）；如果结果集很大，还需要临时表（Using temporary）。

复现 SQL

-- status 字段有索引
-- 快：GROUP BY 有索引的字段
SELECT status, COUNT(*) FROM t_order_demo GROUP BY status;
-- type = index（扫描整个索引，比全表扫描快）

-- user_id 字段有索引
-- 快：GROUP BY 有索引的字段
SELECT user_id, COUNT(*) FROM t_order_demo GROUP BY user_id;
-- type = index

-- order_no 字段有唯一索引
-- 快：DISTINCT 有索引的字段（MySQL 会自动去重）
SELECT DISTINCT order_no FROM t_order_demo;
-- type = index

-- 慢：DISTINCT 字段没有索引
-- 如果没有针对 order_no 的高效索引
SELECT DISTINCT LEFT(order_no, 3) FROM t_order_demo;
-- 可能走全表扫描

-- 验证
EXPLAIN SELECT status, COUNT(*) FROM t_order_demo GROUP BY status;
-- Extra = Using index（使用索引完成分组，不需要临时表和排序）

修复方案

-- 方案1：确保 GROUP BY 和 DISTINCT 的字段有索引
ALTER TABLE t_order_demo ADD INDEX idx_status (status);

-- 方案2：使用索引覆盖 GROUP BY 和 SELECT
-- 如果 SELECT 中只有 GROUP BY 字段和聚合函数，MySQL 可以只扫描索引
SELECT user_id, COUNT(*) FROM t_order_demo GROUP BY user_id;
-- 扫描 idx_user_id 即可，不需要回表

-- 方案3：用 HAVING 替代 WHERE 过滤后分组
-- 这是正常的，先过滤再分组：
SELECT user_id, COUNT(*) AS cnt
FROM t_order_demo
WHERE status = 2
GROUP BY user_id
HAVING cnt > 10;
-- 如果 status = 2 的数据不多，可以建联合索引 (status, user_id)

-- 方案4：强制使用索引
SELECT user_id, COUNT(*) FROM t_order_demo FORCE INDEX (idx_user_id) GROUP BY user_id;

验证

EXPLAIN SELECT status, COUNT(*) FROM t_order_demo GROUP BY status;
-- Extra = Using index（最优）
-- type = index（扫描整个索引）

EXPLAIN SELECT user_id, COUNT(*) FROM t_order_demo GROUP BY user_id;
-- Extra = Using index（最优，user_id 有索引）

场景 10：排序 Order By 与索引关系

现象
SQL 有 ORDER BY，但 EXPLAIN 显示 Using filesort，ORDER BY 没有利用索引有序性。

原理
ORDER BY 要利用索引，需要满足：

1. ORDER BY 的字段在索引中且顺序正确（遵循最左前缀原则）
2. 索引排序方向一致：如果 ORDER BY ASC，但索引是 DESC，或者混合了 ASC/DESC，无法利用索引有序性
3. 无法利用索引排序的情况：ORDER BY 的字段不在 WHERE 中作为等值条件，而索引的其他列被用于等值过滤

复现 SQL

-- 联合索引：idx_user_status (user_id, status)
-- 最常见错误：ORDER BY 字段不在 WHERE 等值条件中

-- 慢：ORDER BY 字段 status 不在等值条件中
SELECT * FROM t_order_demo
WHERE user_id = 1
ORDER BY status;
-- user_id = 1 是等值条件，status 是排序条件
-- 这种情况在 MySQL 5.7+ 有 ICP 优化，但不保证不排序

-- 快：用覆盖索引
ALTER TABLE t_order_demo ADD INDEX idx_user_status_covering (user_id, status, id, order_no, amount, create_time);
SELECT * FROM t_order_demo WHERE user_id = 1 ORDER BY status;
-- 此时不需要 filesort

-- 另一种慢：排序方向不一致
SELECT * FROM t_order_demo
WHERE user_id = 1
ORDER BY create_time DESC, update_time ASC;
-- 混用 ASC 和 DESC 无法利用索引

-- 快：所有排序方向一致
SELECT * FROM t_order_demo
WHERE user_id = 1
ORDER BY create_time DESC, update_time DESC;

EXPLAIN SELECT * FROM t_order_demo WHERE user_id = 1 ORDER BY status;
-- Extra = Using where; Using index（如果用了覆盖索引，无排序）
-- Extra = Using where; Using filesort（如果没覆盖，需要额外排序）

排序无法利用索引的 5 种情况

场景	SQL 示例	原因
排序字段不在索引中	WHERE user_id = 1 ORDER BY status	status 不在索引中
排序方向不一致	ORDER BY id DESC, status ASC	索引 ASC/DESC 固定
OR 条件	WHERE user_id = 1 OR status = 2 ORDER BY create_time	OR 无法确定索引顺序
非等值 WHERE	WHERE user_id > 1 ORDER BY status	user_id 是范围，status 无法利用索引顺序
多表 JOIN	SELECT * FROM a JOIN b ON a.id = b.id ORDER BY a.id	JOIN 顺序影响排序

修复方案

-- 方案1：调整索引结构，让 ORDER BY 字段在索引中且连续
-- 如果经常按 user_id 过滤，按 status 排序：
ALTER TABLE t_order_demo ADD INDEX idx_user_status (user_id, status);

-- 方案2：覆盖索引，避免回表
ALTER TABLE t_order_demo ADD INDEX idx_user_status_cov (user_id, status, id, order_no, amount, create_time);

-- 方案3：确保排序方向一致
-- 所有 DESC 排序都用 DESC，不要混用

-- 方案4：减少 SELECT 字段，让 MySQL 更倾向于用覆盖索引
SELECT user_id, status FROM t_order_demo WHERE user_id = 1 ORDER BY status;
-- 只查两个字段，更容易命中覆盖索引

-- 方案5：用 SQL_BIG_RESULT 提示（针对大结果集排序）
SELECT SQL_BIG_RESULT user_id, COUNT(*)
FROM t_order_demo
GROUP BY user_id
ORDER BY COUNT(*) DESC;
-- 告诉 MySQL 结果集很大，直接用磁盘排序（filesort），不要尝试用索引

验证

-- 创建覆盖索引后
ALTER TABLE t_order_demo ADD INDEX idx_user_status_cov (user_id, status, id, order_no, amount, create_time);

EXPLAIN SELECT * FROM t_order_demo WHERE user_id = 1 ORDER BY status;
-- Extra = Using where; Using index（不需要 filesort）
-- type = ref（使用索引查找）

补充场景 11：多表 JOIN 时的索引失效

现象
多表关联查询中，有些表走了全表扫描，明明关联字段有索引却没用上。

原理
多表 JOIN 时，MySQL 优化器根据数据量、索引效率、驱动表的选择等因素决定 JOIN 顺序和每个表使用的索引。如果驱动表选错了（比如大表驱动小表），性能就会急剧下降。另外，如果 JOIN 条件中有函数或运算，同样会导致索引失效。

复现 SQL

-- 假设 t_order 有 800 万行，t_order_item 有 3200 万行
-- t_order_item.order_id 和 t_order.id 都有索引

-- 慢：驱动表选择错误（MySQL 自动选了 t_order_item 做驱动表）
SELECT o.id, o.order_no, oi.goods_name
FROM t_order_item oi
INNER JOIN t_order o ON oi.order_id = o.id
WHERE oi.user_id = 1;
-- MySQL 看到 oi.user_id 有索引，可能选择从 oi 表出发
-- 结果：扫描 3200 万行的 t_order_item，找到了 user_id=1 的 1000 行
-- 再关联 t_order，用这 1000 个 order_id 逐个查找

-- 快：强制指定驱动表（小表驱动大表）
SELECT STRAIGHT_JOIN o.id, o.order_no, oi.goods_name
FROM t_order o
INNER JOIN t_order_item oi ON o.id = oi.order_id
WHERE oi.user_id = 1;
-- 强制从 t_order（800 万行）出发
-- 但实际上，WHERE 条件在 oi 上，优化器可能认为从 oi 出发更优
-- 关键：把 WHERE 条件放到能被索引过滤的表上

修复方案

-- 方案1：调整 SQL，让 WHERE 条件在驱动表上
SELECT o.id, o.order_no, oi.goods_name
FROM t_order o
INNER JOIN t_order_item oi ON o.id = oi.order_id
WHERE o.user_id = 1;
-- 从 t_order（800 万行）用 user_id=1 过滤，得到 500 行
-- 再关联 t_order_item，500 个 order_id 查 4 行/单 = 2000 行
-- 性能差异巨大

-- 方案2：给 JOIN 字段也加上覆盖索引
ALTER TABLE t_order_item ADD INDEX idx_order_user (order_id, user_id, goods_name);

-- 方案3：用 STRAIGHT_JOIN 强制指定驱动表（保守做法）
SELECT STRAIGHT_JOIN o.id, o.order_no, oi.goods_name
FROM t_order o
INNER JOIN t_order_item oi ON o.id = oi.order_id
WHERE o.user_id = 1;

JOIN 的索引设计原则

1. 驱动表的选择：让过滤后数据量最小的表做驱动表
2. ON 条件的字段要有索引：特别是被驱动表（right join 的右表，left join 的左表）的关联字段
3. 被驱动表的索引要包含 SELECT 的字段：实现覆盖索引，减少回表
4. 避免在 ON 条件中使用函数：会导致索引失效

验证

-- 查看 JOIN 顺序
EXPLAIN SELECT STRAIGHT_JOIN o.id, o.order_no, oi.goods_name
FROM t_order o
INNER JOIN t_order_item oi ON o.id = oi.order_id
WHERE o.user_id = 1;

-- 输出中注意：
-- id 列：id 大的表先执行（MySQL 的 EXPLAIN 输出中 id 越大优先级越高）
-- type 列：应该是 ref 或 eq_ref，不应该是 ALL
-- key 列：应该显示被使用的索引名
-- rows 列：驱动表的 rows 应该远小于被驱动表

综合排查清单

当发现 SQL 疑似索引失效时，按以下顺序检查：

1. EXPLAIN 看 type 列
   - type = ALL → 全表扫描，通常意味着索引失效
   - type = ref/eq_ref/range → 至少用到了索引

2. EXPLAIN 看 key 列
   - key = NULL → 没有索引被使用
   - key = xxx → 使用了某个索引

3. EXPLAIN 看 rows 列
   - rows 远大于实际返回行数 → 统计信息可能不准，执行 ANALYZE TABLE

4. 检查 WHERE 条件
   - 是否有函数或运算？（场景 1）
   - 是否有类型转换？（场景 2）
   - LIKE 是否前缀通配符？（场景 3）
   - OR 两边是否都有索引？（场景 4）
   - 是否违反最左前缀原则？（场景 5）

5. 检查 ORDER BY 和 GROUP BY
   - 是否利用了索引的有序性？（场景 10）
   - 是否有 filesort 或 temporary？（场景 9）

6. 检查表数据量
   - 数据量太小可能导致优化器选择全表扫描（场景 6）

7. 检查统计信息
   - SHOW TABLE STATUS 的 Rows 是否准确？（场景 7）

8. 检查 NULL 处理
   - IS NULL / IS NOT NULL 可能影响索引使用（场景 8）

补充场景：优化器提示（Optimizer Hints）的正确使用

什么时候需要手动指定索引
正常情况下，MySQL 优化器会选择最优索引。但以下情况可能选错：

1. 统计信息不准（场景 7），导致选错索引
2. 多表 JOIN 时，JOIN 顺序影响性能，优化器不一定选对
3. 复杂查询中，多个索引都可以用，优化器可能选错
4. 数据分布倾斜，某个值占 99%，优化器误判

常用优化器提示

-- 强制使用某个索引（比 FORCE INDEX 更轻量）
SELECT * FROM t_order USE INDEX (idx_user_id) WHERE user_id = 1;

-- 强制优化器使用指定索引
SELECT * FROM t_order FORCE INDEX (idx_user_id) WHERE user_id = 1;

-- 忽略某个索引（测试禁用某索引的影响）
SELECT * FROM t_order IGNORE INDEX (idx_user_id) WHERE user_id = 1;

-- 指定 JOIN 顺序（STRAIGHT_JOIN）
SELECT STRAIGHT_JOIN
    o.id, oi.goods_name
FROM t_order o
INNER JOIN t_order_item oi ON o.id = oi.order_id
WHERE o.user_id = 1;
-- STRAIGHT_JOIN 强制按写的顺序 JOIN，左边的表是驱动表

-- 指定优化器策略
SELECT /*+ SET_VAR(optimizer_switch='index_merge=off') */ *
FROM t_order WHERE user_id = 1 OR status = 2;
-- 关闭 index_merge 优化，强制分别走两个索引再合并

-- 指定最大执行时间（MySQL 8.0+）
SELECT /*+ MAX_EXECUTION_TIME(5000) */ *
FROM t_order WHERE user_id = 1;
-- 最多执行 5 秒，超时自动终止

-- 禁止某个优化（MySQL 8.0+）
SELECT /*+ NO_RANGE_OPTIMIZATION(t_order idx_status) */ *
FROM t_order WHERE status > 5;
-- 禁止对 idx_status 使用范围优化，强制全表扫描或走其他索引

优化器提示的风险
不要滥用优化器提示。优化器提示会绕过优化器的成本估算，如果数据分布发生变化（比如表从 10 万行增长到 1000 万行），手动指定的索引可能从最优变成最差。

正确的做法：

1. 先用 EXPLAIN 分析优化器的选择是否正确
2. 用 EXPLAIN ANALYZE（MySQL 8.0+）验证实际执行成本
3. 用 optimizer_trace 理解优化器的决策过程
4. 只有在确认优化器选错时，才使用优化器提示作为临时修复
5. 同时分析为什么优化器会选错，调整索引或统计信息，从根本上解决问题

索引设计原则：避免失效于未然

索引失效的根本原因是索引设计与查询语义不匹配。遵循以下原则，可以从源头避免大部分索引失效问题。

原则 1：查询驱动建索引
不要先建一堆索引，然后祈祷能覆盖查询。要反过来：根据实际查询来设计索引。

-- 分析慢查询日志，找出最常用的查询模式
-- 然后针对每个查询设计最优索引

-- 查询模式 1：WHERE user_id = ? AND create_time > ?
ALTER TABLE t_order ADD INDEX idx_1 (user_id, create_time);

-- 查询模式 2：WHERE status = ? AND amount > ?
ALTER TABLE t_order ADD INDEX idx_2 (status, amount);

-- 查询模式 3：WHERE order_no = ?
ALTER TABLE t_order ADD INDEX idx_3 (order_no);

原则 2：控制索引数量
每个索引都要占用磁盘空间，并在数据写入时维护（B+ 树更新）。索引过多会导致写入性能下降、存储空间膨胀，以及优化器选择困难。经验值是单表索引数量控制在 5-6 个以内。

-- MySQL 8.0：查看索引使用情况
SELECT * FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE OBJECT_SCHEMA = 'your_database'
AND TABLE_NAME = 't_order'
ORDER BY COUNT_STAR DESC;

原则 3：索引字段选择
区分度高的字段放前面（user_id 比 status 区分度高）；频繁等值查询的字段放前面（等值在前，范围在后）；字段类型要匹配（用 INT 而不是 VARCHAR 存储数字 ID）。

原则 4：覆盖索引优先
能一次索引扫描完成的事情，不要回表。

-- 覆盖索引：SELECT 的字段都在索引中
ALTER TABLE t_order ADD INDEX idx_cov (user_id, create_time, status, id, order_no, amount);

-- 查询只查这些字段，不需要回表
SELECT id, order_no, status, amount, create_time
FROM t_order
WHERE user_id = 1
AND create_time >= '2026-01-01';
-- Extra = Using index（完全在索引中完成）

原则 5：避免冗余索引  

-- 假设已有索引
INDEX idx_a (A)
INDEX idx_b (A, B)

-- 这里 idx_b 是 idx_a 的冗余索引（前者包含后者）
-- 删除 idx_a，保留 idx_b
-- 因为 idx_b 在满足 idx_a 的所有查询同时，还能额外支持 A+B 的组合查询

-- 查找冗余索引（MySQL 8.0+）
SELECT * FROM sys.schema_redundant_indexes;

生产环境索引管理规范

上线前检查

-- 1. 在测试环境验证 EXPLAIN
EXPLAIN SELECT ... 你的慢查询

-- 2. 确认没有意外的全表扫描
-- type 应该是 ref、eq_ref、range、index 之一，不应该是 ALL

-- 3. 确认使用了预期的索引
-- key 列应该是你期望的那个索引名

-- 4. 确认没有 filesort
-- Extra 不应该包含 Using filesort

-- 5. 对比优化前后执行时间
SET profiling = 1;
SELECT ... 你的查询
SHOW PROFILES;

监控索引使用情况

-- MySQL 8.0：每分钟运行一次，监控索引使用率
SELECT
    OBJECT_SCHEMA AS db,
    OBJECT_NAME AS tbl,
    INDEX_NAME AS idx,
    COUNT_FETCH AS fetch,
    COUNT_INSERT AS ins,
    COUNT_UPDATE AS upd,
    COUNT_DELETE AS del,
    SUM_TIMER_WAIT / 1000000000000 AS total_time_s
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE OBJECT_SCHEMA = 'your_database'
ORDER BY SUM_TIMER_WAIT DESC;

安全删除索引的流程

-- 步骤1：确认没有慢查询使用这个索引
-- 在慢查询日志中搜索索引名

-- 步骤2：改为不可见，观察 24-48 小时
ALTER TABLE t_order ALTER INDEX idx_old INVISIBLE;

-- 步骤3：确认业务正常，没有性能下降
-- 监控慢查询日志和 P99 延迟

-- 步骤4：确认应用日志没有异常

-- 步骤5：确认没有其他查询使用该索引
-- 再次检查慢查询日志

-- 步骤6：确认无误后，删除索引
ALTER TABLE t_order DROP INDEX idx_old;

常见面试问题与回答

Q：为什么索引失效了？
索引失效的原因是查询条件无法利用索引树的有序性。具体可能是：查询中对索引列使用了函数或运算（场景 1）；字段类型和参数类型不一致导致隐式转换（场景 2）；LIKE 查询用了前缀通配符（场景 3）；OR 条件中有字段没有索引（场景 4）；违反了最左前缀原则（场景 5）。用 EXPLAIN 查看执行计划，就能找到具体原因。

Q：如何判断是索引问题还是数据库配置问题？
如果是索引问题，优化 SQL 或添加索引后性能会显著提升。如果是配置问题（比如 buffer pool 太小、磁盘 I/O 慢），即使优化了 SQL，性能提升也很有限。区分方法：用 EXPLAIN 看是否走全表扫描（索引问题），还是即使走索引也很慢（配置问题）。

Q：什么时候该用索引覆盖？
当 SELECT 的字段都能在索引中找到时，用索引覆盖可以避免回表，性能提升最显著。判断方法：在 EXPLAIN 的 Extra 列看到 Using index，就说明用到了覆盖索引。

Q：为什么 MySQL 选了错误的索引？
MySQL 优化器基于统计信息选择索引。如果统计信息不准（场景 7），或者数据分布极端（某个值占 99%），优化器可能选错。解决方法：执行 ANALYZE TABLE 更新统计信息；或者用 USE INDEX / FORCE INDEX 强制使用正确的索引（临时方案）；最终方案是调整索引结构。

总结

索引失效不是“索引坏了”，而是查询条件和索引结构不匹配。10 个场景的核心原理总结如下：

场景	根因	核心修复
函数/运算	索引列被函数包裹，无法定位	改写为范围查询，保留原始字段
隐式类型转换	字符串和数字比较触发 CAST	确保参数类型和字段类型一致
LIKE 前缀通配符	% 在最左，无法确定起点	改用后缀匹配、全文索引或反转存储
OR 缺索引	OR 有一边无索引时全表扫描更快	OR 两边都加索引，或拆成 UNION
违反最左前缀	跳过了索引的前缀列	调整 SQL 顺序或建新索引
数据量太小	全表扫描比索引回表更快	不处理，或强制 USE INDEX
统计信息不准	优化器决策依据错误	ANALYZE TABLE 更新统计
IS NULL / NOT NULL	NULL 在索引中不存储	改用具体值范围，或设默认值
GROUP BY / DISTINCT	无索引时需要排序和临时表	加索引或利用覆盖索引
ORDER BY	排序字段不在索引中或方向不一致	调整索引结构，统一排序方向

索引优化的本质是让查询条件和索引结构对齐。不是索引越多越好，而是越准越好。每一张表、每一个常用查询，都需要根据实际的 WHERE 条件、ORDER BY 字段、数据分布来设计索引，而不是凭感觉建一堆索引然后指望优化器能选对。

如果把前面提到的那些坑都避开，你的 MySQL 查询性能通常会有一个质的提升。排查索引问题没有捷径——多跑几次 EXPLAIN，多看看执行计划，慢慢就能培养出对优化器行为的直觉。