前言

线上遇到慢 SQL 时，有时候我们会发现原来是查询索引失效了，所以导致查询时间特别长。那么针对于索引失效的情况，本文就以MySQL 8.0 和 InnoDB为例，准备一张 10 万行的订单表，复现 6个常见场景。每个场景都包含问题 SQL、执行计划判断方法和可落地的改写方案。

一、准备可复现的实验数据

1、建表

DROP TABLE IF EXISTS orders; CREATE TABLE orders ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, user_id BIGINT UNSIGNED NOT NULL, order_no VARCHAR(32) NOT NULL, phone VARCHAR(20) NOT NULL, status TINYINT NOT NULL, created_at DATETIME NOT NULL, amount DECIMAL(12, 2) NOT NULL, remark VARCHAR(255) NOT NULL DEFAULT '', PRIMARY KEY (id), UNIQUE KEY uk_order_no (order_no), KEY idx_phone (phone), KEY idx_user_time_status (user_id, created_at, status) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

2、插入10万行数据

当前测试使用Python批量插入数据，大家也可以通过Java程序、SQL语句等进行订单数据的批量插入。如果你已有Python环境的话，可以使用下面的程序进行数据插入。（注意⚠️：需要安装pymysql库，用于MySQL数据库的连接及操作）

#!/usr/bin/env python3 """向 MySQL orders 表批量插入测试数据。""" import argparse import getpass import random import time import uuid from datetime import datetime, timedelta from decimal import Decimal import pymysql INSERT_SQL = """ INSERT INTO orders (user_id, order_no, phone, status, created_at, amount, remark) VALUES (%s, %s, %s, %s, %s, %s, %s) """ def parse_args(): parser = argparse.ArgumentParser(description="批量生成并插入 orders 测试数据") parser.add_argument("--count", type=int, default=100_000, help="插入条数，默认 100000") parser.add_argument("--batch-size", type=int, default=1_000, help="每批条数，默认 1000") parser.add_argument("--host", default="127.0.0.1", help="数据库地址") parser.add_argument("--port", type=int, default=3306, help="数据库端口") parser.add_argument("--user", default="root", help="数据库用户") parser.add_argument("--database", help="数据库名；未提供时由程序提示输入") return parser.parse_args() def build_row(sequence: int, now: datetime): # 31 位订单号，可在脚本重复执行时继续保持极低的冲突概率。 order_no = "O" + uuid.uuid4().hex[:30] phone = "1" + str(random.choice((3, 5, 7, 8, 9))) + f"{random.randint(0, 999_999_999):09d}" created_at = now - timedelta(seconds=random.randint(0, 365 * 24 * 60 * 60)) amount = Decimal(random.randint(1, 10_000_000)) / Decimal("100") return ( random.randint(1, 20_000), order_no, phone, random.randint(0, 4), created_at, amount, f"批量测试数据-{sequence}", ) def main(): args = parse_args() if not args.database: args.database = input("请输入数据库名：").strip() if not args.database: raise SystemExit("数据库名不能为空") password = getpass.getpass("请输入 MySQL 密码（无密码直接回车）：") if args.count <= 0 or args.batch_size <= 0: raise SystemExit("--count 和 --batch-size 必须大于 0") connection = pymysql.connect( host=args.host, port=args.port, user=args.user, password=password, database=args.database, charset="utf8mb4", autocommit=False, ) started_at = time.perf_counter() inserted = 0 now = datetime.now() try: with connection.cursor() as cursor: while inserted < args.count: current_batch_size = min(args.batch_size, args.count - inserted) rows = [build_row(inserted + index + 1, now) for index in range(current_batch_size)] try: cursor.executemany(INSERT_SQL, rows) connection.commit() except Exception: connection.rollback() raise inserted += current_batch_size elapsed = time.perf_counter() - started_at print( f"已插入 {inserted:,}/{args.count:,} 条，" f"平均 {inserted / max(elapsed, 0.001):,.0f} 条/秒" ) finally: connection.close() elapsed = time.perf_counter() - started_at print(f"完成：共插入 {inserted:,} 条，耗时 {elapsed:.2f} 秒") if __name__ == "__main__": main()

二、看懂EXPLAIN中的关键信息

先执行一个正常使用唯一索引的查询：

EXPLAIN SELECT * FROM orders WHERE order_no = 'O116770401e134636b6db774679f3d7';

传统表格格式的执行计划中，排查索引问题主要看下面几个字段。

type中最需要警惕的是：

• ALL：全表扫描；
• index：扫描整棵索引，仍可能读取很多条目；
• range：按一个或多个索引区间扫描；
• ref：通过非唯一索引等值查找；
• const：通过主键或唯一索引与常量比较，最多匹配一行。

容易理解错误的点：

• Using where只表示还要应用过滤条件，不等于没有使用索引；
• Using index表示覆盖索引，即所需列可以直接从索引取得；
• Using index condition表示使用了索引条件下推（ICP），存储引擎先在索引层过滤，再决定是否回表；
• Using filesort表示需要额外排序，并不保证一定会写磁盘；
• rows是优化器估算值，想看实际行数要用EXPLAIN ANALYZE。

三、场景一：联合索引缺少最左列

当前表中有联合索引：

idx_user_time_status(user_id, created_at, status)

下面查询跳过第一列user_id：

EXPLAIN SELECT * FROM orders WHERE created_at >= '2025-10-01 00:00:00' AND status = 2;

1、执行计划分析：

type: ALL
possible_keys: NULL
key: NULL
rows: 接近总行数
Extra: Using where

虽然created_at和status都在联合索引里，但它们没有构成(user_id, created_at, status)的最左前缀，B+Tree 不能直接定位扫描起点。先按user_id排序；只有user_id相同，才继续按created_at排序。因此，不指定user_id时，全局的created_at并不是连续有序的。

2、修改建议：

如果业务经常按状态和时间查询，应建立与访问模式匹配的索引。新增索义：

ALTER TABLE orders ADD KEY idx_status_time (status, created_at);

EXPLAIN
SELECT *
FROM orders
WHERE status = 2 AND created_at >= '2026-05-01 00:00:00';

然后我们再看一下执行计划

type: range（如果查询的数据命中率比较高，有时候全表扫描反而更快，type=ALL）
possible_keys: idx_status_time

rows: 明显下降

四、场景二：索引列上做函数或运算

order_no上有唯一索引uk_order_no。直接等值查询可以快速定位：

EXPLAIN SELECT * FROM orders WHERE order_no = 'O116770401e134636b6db774679f3d7';

如果在列上调用函数：

EXPLAIN SELECT * FROM orders WHERE LOWER(order_no) = 'O116770401e134636b6db774679f3d7';

1、执行计划会退化为：

type: ALL
possible_keys: NULL
key: NULL
Extra: Using where

索引保存的是原始order_no，不是LOWER(order_no)的结果，普通 B+Tree 无法根据函数结果直接找到对应的原始键值。

2、修复建议：

（1）优先改写SQL：如果数据本身已经统一为大写订单号，应规范入参。不在列上调用LOWER函数、日期查询改成左闭右开区间。

（2）确实要按表达式查询时使用函数索引（MySQL 8.0.13 起支持函数索引）

CREATE INDEX idx_lower_order_no ON orders ((LOWER(order_no))); EXPLAIN SELECT * FROM orders WHERE LOWER(order_no) = 'O116770401e134636b6db774679f3d7';

五、场景三：隐式类型转换发生在索引列一侧

phone的类型是VARCHAR(20)，下面两条 SQL 看起来只差一对引号：

-- 参数是字符串 EXPLAIN SELECT * FROM orders WHERE phone = '13000000100'; -- 参数是数字 EXPLAIN SELECT * FROM orders WHERE phone = 13000000100;

第一条通常使用idx_phone索引，访问方式为ref。第二条很可能全表扫描。

1、为什么字符串列和数字比较会丢索引？

MySQL 比较字符串和数字时会做类型转换。问题不只是把'13000000100'转成数字这么简单，因为多个不同字符串可能转换成同一个数字，例如'1'、' 1'和'1a'。因此，MySQL 不能直接用字符串索引完成phone = 13000000100的查找，只能逐行转换和比较。所以执行计划常常是这样的：

type: ALL
key: NULL
rows: 接近总行数

2、修复建议

（1）应用参数类型要与字段类型一致，比如手机号是字符串类型，就用字符串方式查找。在JDBC中也可以使用preparedStatement.setString(1, phone);

（2）联表字段也要检查类型和字符集，比如下面例子：

SELECT * FROM orders o JOIN user_profile u ON o.user_id = u.user_id;

如果两边一个是BIGINT、另一个是VARCHAR，或者字符串列的字符集、排序规则不兼容，执行计划可能出现转换，导致某一侧索引不能用于高效查找。

六、场景四：LIKE以通配符开头

前缀匹配可以利用 B+Tree 的有序性：

EXPLAIN SELECT * FROM orders WHERE phone LIKE '1300000%';

典型访问方式是range，key=idx_phone。但是如果把%放到最前面后，索引无法确定起始位置，常见结果如下

EXPLAIN SELECT * FROM orders WHERE phone LIKE '%00100';

type: ALL
key: NULL
Extra: Using where

修复建议：模糊查询时，使用前缀匹配。

七、场景五：OR中有一个分支没有可用索引

order_no有索引，remark没有索引：

EXPLAIN SELECT * FROM orders WHERE order_no = 'O71c3b1448217428aa5b6323c34aa87' OR remark = '批量测试数据-2';

左边可以通过uk_order_no找到一行，右边却要扫描整张表。既然右边无法通过索引取得候选主键，优化器很可能直接选择一次全表扫描：

type: ALL
possible_keys: uk_order_no
key: NULL
rows: 接近总行数
Extra: Using where

possible_keys有值但 key 为 NULL，正好说明“存在候选索引”和“最终使用索引”是两回事。

修复建议：两个分支都可索引时可能使用Index Merge

如果remark的等值查询足够常见，可以增加索引：

ALTER TABLE orders ADD KEY idx_remark (remark); ANALYZE TABLE orders; #作用是重新收集表和索引的统计信息 EXPLAIN SELECT * FROM orders WHERE order_no = 'O71c3b1448217428aa5b6323c34aa87' OR remark = '批量测试数据-2';

修改后可能看到：

type: index_merge
key: uk_order_no,idx_remark
Extra: Using union(uk_order_no,idx_remark); Using where

index_merge会分别扫描多个索引，再合并行 ID。它比全表扫描好还是差，取决于各分支返回的数据量以及合并成本。

八、场景六：WHERE走了索引，ORDER BY仍然发生额外排序

例如索引为：

idx_user_time_status(user_id, created_at, status)

查询某个用户的订单，并要求先按状态、再按时间排序：

EXPLAIN SELECT id, user_id, status, created_at FROM orders WHERE user_id = 2150 ORDER BY status, created_at LIMIT 20;

WHERE user_id=100可以使用idx_user_time_status，但索引在user_id之后的顺序是(created_at, status)，与ORDER BY status, created_at不一致。所以执行计划可能这样的：

type: ref
key: idx_user_time_status
Extra: Using where; Using index; Using filesort

这不是 WHERE 索引失效，而是索引不能同时满足排序。MySQL 先找到该用户的记录，再额外排序。

修复建议：如果这个查询频繁执行，可以建立同时匹配过滤和排序的索引：user_id是常量，后面的(status, created_at)正好提供所需顺序，Using filesort通常会消失。

CREATE INDEX idx_user_status_time ON orders (user_id, status, created_at); EXPLAIN SELECT id, user_id, status, created_at FROM orders WHERE user_id = 2150 ORDER BY status, created_at LIMIT 20;

九、生产环境加索引前还要考虑什么？

1、索引有写入成本：

每增加一个二级索引，INSERT、UPDATE、DELETE都要维护它。宽索引还会占用更多缓存和磁盘。能用一个设计合理的联合索引覆盖多个稳定查询时，通常比堆很多单列索引更好。

2、DDL要评估锁和资源：

大表执行ALTER TABLE ... ADD INDEX前，应确认当前版本支持的在线 DDL 能力，评估元数据锁、临时空间、I/O、主从延迟以及失败回滚时间。不要在业务高峰直接试。

3、先验证再删除旧索引：

MySQL 8 可以把索引设为不可见，用于观察优化器不使用该索引时的影响：不可见索引仍会被写入维护，并不会降低写入成本；它主要用于安全评估删除影响。确认没有计划回退后，再按变更流程删除。

ALTER TABLE orders ALTER INDEX idx_phone INVISIBLE; -- 验证完成后恢复 ALTER TABLE orders ALTER INDEX idx_phone VISIBLE;

十、结语

“索引失效”不是一个根因，只是执行计划表现出来的结果。

排查时先分清：索引是完全不能定位、只使用了一部分，还是被优化器基于成本放弃。接着用EXPLAIN看估算计划，用EXPLAIN ANALYZE验证实际扫描和耗时，再决定改 SQL、调列顺序还是补索引。