当前位置：首页 > news >正文

MySQL索引优化实战：排序、批量IN、范围查询一网打尽

news 2026/6/4 17:28:48

title: “MySQL索引优化实战：排序、批量IN、范围查询一网打尽”
tags:

MySQL
索引优化
数据库
SQL优化
categories:
数据库
description: “结合真实业务场景，深入讲解MySQL复合索引在ORDER BY排序、批量IN查询、范围查询中的设计与优化策略，包含EXPLAIN分析、索引列顺序设计、覆盖索引实战案例”

导读：线上慢查询里最常见的就是三类——排序慢、IN 查询慢、范围查询慢。这篇文章我用一个真实的订单表做例子，把这三类问题串起来讲，告诉你索引该怎么建、列顺序怎么排、EXPLAIN 怎么看。

一、先交代下背景

我之前做过一个电商系统，订单表到了千万级之后，很多查询开始飘红。慢查询日志里反复出现的几个模式：

按状态 + 时间排序分页
批量根据用户 ID 查订单
按时间范围 + 状态筛选

这三个场景看着简单，但索引设计稍有不慎，EXPLAIN 里就会出现Using filesort、Using temporary、Using where这些让人头疼的字眼。

下面我用一张简化版的订单表来演示：

CREATETABLEt_order(idBIGINTPRIMARYKEYAUTO_INCREMENT,user_idBIGINTNOTNULL,order_noVARCHAR(64)NOTNULL,statusVARCHAR(20)NOTNULLCOMMENT'PAID,SHIPPED,COMPLETED,CANCELLED',total_amountDECIMAL(12,2)NOTNULL,created_atDATETIMENOTNULL,updated_atDATETIMENOTNULLDEFAULTCURRENT_TIMESTAMPONUPDATECURRENT_TIMESTAMP,INDEXidx_user_id(user_id),INDEXidx_status(status),INDEXidx_created_at(created_at))ENGINE=InnoDBDEFAULTCHARSET=utf8mb4;

这是很多项目初期的索引方案——每个查询字段建一个单列索引。数据量小的时候没问题，但数据一大，这种方案就扛不住了。

适用版本：本文基于 MySQL 8.0+（InnoDB），8.0 的Descending Index 功能在某些场景下会有帮助。

二、排序优化：ORDER BY 和索引的关系

2.1 一个典型的慢排序

-- 查询某个用户的订单，按创建时间倒序分页SELECTid,order_no,status,total_amount,created_atFROMt_orderWHEREuser_id=10086ORDERBYcreated_atDESCLIMIT20;

这条 SQL 看着人畜无害，但在只有idx_user_id索引的情况下，MySQL 的执行过程是：

通过idx_user_id找到 user_id=10086 的所有行（假设有 3000 条）
这 3000 条数据没有按 created_at 排好序，所以 MySQL 要做一个 filesort
排序完成后取前 20 条

来看 EXPLAIN：

type: ref key: idx_user_id rows: 3000 Extra: Using where; Using filesort

Using filesort意味着额外的排序开销。数据少的时候感受不到，但如果你有 10 万条符合条件的记录，这个排序就会很吃力。

2.2 用复合索引消灭 filesort

CREATEINDEXidx_user_createdONt_order(user_id,created_at);

这个索引的 B+Tree 结构是先按 user_id 排，同一个 user_id 内再按 created_at 排。MySQL 可以直接沿着索引倒着读，前 20 条就是结果。

type: ref key: idx_user_created rows: 20 Extra: Using where

注意rows从 3000 降到了 20，Using filesort没了。因为索引本身就是排好序的，MySQL 读到 20 条就停了，根本不需要排序。

💡关键点：ORDER BY 的列放在复合索引的后面，等值筛选的列放在前面。这样索引既能过滤，又能排序。

2.3 排序方向不一致怎么办

-- 按状态正序、创建时间倒序排SELECT*FROMt_orderWHEREuser_id=10086ORDERBYstatusASC,created_atDESCLIMIT20;

这种情况，索引(user_id, status, created_at)能帮上忙吗？

在 MySQL 8.0 之前，如果 ORDER BY 的列排序方向不一致（一个 ASC 一个 DESC），索引没法直接用，还是会走 filesort。MySQL 8.0 引入了Descending Index，可以显式声明：

CREATEINDEXidx_user_status_created_descONt_order(user_id,statusASC,created_atDESC);

这样索引本身就按status ASC, created_at DESC排好了，查询可以直接用。

⚠️ 但说实话，我实际项目中遇到这种多列不同方向排序的场景不算多。大部分情况是单列排序，或者同方向的多列排序。如果真遇到了，先问问产品经理这个排序需求是不是必须的——有时候换一种排序方式比加索引更划算。

三、批量 IN 查询优化

3.1 IN 列表查询的索引行为

-- 查询一批用户的最近订单SELECTid,user_id,order_no,created_atFROMt_orderWHEREuser_idIN(1001,1002,1003,1004,1005)ORDERBYcreated_atDESCLIMIT50;

IN 查询在 MySQL 里本质上是多个等值查询的合并。对于上面的 SQL，优化器会把 IN 列表展开，相当于对每个 user_id 做一次索引查找，然后合并结果。

用idx_user_created(user_id, created_at)这个索引，EXPLAIN 是这样的：

type: range key: idx_user_created rows: 150 Extra: Using where; Using filesort

注意这里又出现了Using filesort。因为虽然每个 user_id 内部的 created_at 是有序的，但 5 个 user_id 之间的结果合并后，整体顺序没法保证，所以还是要排序。

3.2 IN 查询 + 排序的取舍

对于这种"IN + ORDER BY"的场景，说实话没有完美的索引方案。我的做法是根据数据量做取舍：

方案一：数据量小，让 MySQL 排

如果 IN 列表里的值不多（比如十几个 user_id），每个 user_id 下的记录也不多，filesort 的开销其实很小，不用特别优化。

方案二：IN 列表很大，考虑改写查询

-- 如果是分页展示，可以改成多次查询后应用层合并SELECTid,user_id,order_no,created_atFROMt_orderWHEREuser_id=1001ORDERBYcreated_atDESCLIMIT10;SELECTid,user_id,order_no,created_atFROMt_orderWHEREuser_id=1002ORDERBYcreated_atDESCLIMIT10;-- ... 在应用层合并排序取 top 50

这种方式每个子查询都能完美命中索引(user_id, created_at)，没有 filesort。代价是网络往返变多了，但在 IN 列表特别大（几百个值）的情况下，往往比一个大 IN 查询更快。

方案三：用临时表 JOIN

-- 先把 IN 列表存到临时表CREATETEMPORARYTABLEtmp_users(user_idBIGINTPRIMARYKEY);INSERTINTOtmp_usersVALUES(1001),(1002),(1003),(1004),(1005);SELECTo.id,o.user_id,o.order_no,o.created_atFROMt_order oJOINtmp_users tONo.user_id=t.user_idORDERBYo.created_atDESCLIMIT50;

这在某些场景下比直接 IN 更高效，尤其是 IN 列表需要从别的表关联获取的时候。

3.3 IN 列表不是越长越好

MySQL 对 IN 列表有个优化叫range_optimizer_max_mem_size，默认 8MB。IN 列表太长会占用大量内存，甚至被优化器放弃走索引。我的经验是：

IN 列表控制在500 个以内，性能通常没问题
超过 500 个，考虑分批查询或者用临时表 JOIN
超过 1000 个，必须拆分，否则不仅查询慢，SQL 解析本身都会卡

四、范围查询优化

4.1 范围查询对复合索引的影响

这是我觉得最容易踩坑的地方。先看一条 SQL：

-- 查询某时间段内的已付款订单，按创建时间排序SELECTid,order_no,status,total_amount,created_atFROMt_orderWHEREstatus='PAID'ANDcreated_at>='2025-01-01'ANDcreated_at<'2025-02-01'ORDERBYcreated_atDESC;

如果索引是(status, created_at)：

type: range key: idx_status_created rows: 15000 Extra: Using where

这个索引用得很好——先通过 status 等值过滤到 PAID 状态的记录，然后在索引内部按 created_at 范围扫描，而且 ORDER BY created_at 也直接利用了索引顺序。

但如果反过来，索引是(created_at, status)：

type: range key: idx_created_status rows: 80000 Extra: Using where

MySQL 先按 created_at 范围扫描，扫了 80000 行，然后逐行检查 status 是否为 PAID。扫描行数多了好几倍。

4.2 核心规则：等值列在范围列前面

复合索引的列顺序有一个黄金法则：

位置	条件类型	说明
第1位	等值条件（=, IN）	精准定位，过滤掉最多数据
第2位	范围条件（>, <, BETWEEN）	在等值过滤基础上做范围扫描
第3位	排序/分组（ORDER BY, GROUP BY）	利用索引有序性，避免 filesort

范围条件后面的列，索引还能用吗？

这是一个经典问题。答案是：范围条件后面的列，索引无法用于过滤，但可能用于排序。

-- 索引：(status, created_at, total_amount)SELECT*FROMt_orderWHEREstatus='PAID'ANDcreated_at>'2025-01-01'ORDERBYtotal_amountDESC;

这里created_at是范围条件，它后面的total_amount无法用于过滤，也无法用于排序（因为 created_at 范围内的数据 total_amount 不是有序的）。EXPLAIN 会显示Using filesort。

如果这个查询真的很频繁，你可能需要另一个索引：

CREATEINDEXidx_status_amountONt_order(status,total_amount);

这就是索引设计的权衡——你不可能用一个索引覆盖所有查询模式。

4.3 BETWEEN 和 IN 的选择

-- 这两种写法在优化器层面是等价的WHEREcreated_atBETWEEN'2025-01-01'AND'2025-01-31'WHEREcreated_at>='2025-01-01'ANDcreated_at<='2025-01-31'

对于离散值，有人喜欢用 IN 代替范围：

-- 用 IN 替代范围WHEREstatusIN('PAID','SHIPPED')

关键区别：IN 是等值匹配，BETWEEN 是范围匹配。在复合索引中，IN 后面的列索引还能继续用，BETWEEN 后面的列就不行了。

-- 索引：(status, created_at)-- IN 写法：created_at 的索引可以继续用WHEREstatusIN('PAID','SHIPPED')ANDcreated_at>'2025-01-01'-- 等号写法：created_at 的索引也可以用WHEREstatus='PAID'ANDcreated_at>'2025-01-01'

但如果 status 是范围条件：

-- 假设 status 有数值含义，用范围查询WHEREstatus>'C'ANDcreated_at>'2025-01-01'-- created_at 的索引就断了

所以，能用 IN/等值表达的条件，就别用范围，这在复合索引设计里特别重要。

五、综合实战：三种场景合并

实际业务中，一个查询往往同时包含排序、IN 和范围条件。来看一个真实的例子：

-- 需求：查询多个用户的已付款订单，按时间范围筛选，按创建时间排序分页SELECTid,user_id,order_no,status,total_amount,created_atFROMt_orderWHEREuser_idIN(1001,1002,1003,1004,1005)ANDstatus='PAID'ANDcreated_at>='2025-01-01'ANDcreated_at<'2025-02-01'ORDERBYcreated_atDESCLIMIT20;

5.1 索引设计分析

按照"等值 → 范围 → 排序"的原则，理想的索引列顺序是：

等值列（user_id, status） → 范围/排序列（created_at）

但因为 user_id 是 IN（多个等值），不是单值等值，优化器的处理会复杂一些。

-- 推荐索引CREATEINDEXidx_user_status_createdONt_order(user_id,status,created_at);

EXPLAIN 结果：

type: range key: idx_user_status_created rows: 85 Extra: Using where; Using filesort

Using filesort还是出现了。原因是 user_id IN (…) 展开后，每个 (user_id, status) 组合内部的 created_at 是有序的，但多个 user_id 之间的结果合并后需要重新排序。

5.2 能不能消灭 filesort？

如果排序字段是索引的一部分，而且 WHERE 条件能精确到单个 user_id，那可以。比如：

-- 单个用户的查询，filesort 可以消除SELECTid,user_id,order_no,status,total_amount,created_atFROMt_orderWHEREuser_id=1001ANDstatus='PAID'ANDcreated_at>='2025-01-01'ORDERBYcreated_atDESCLIMIT20;

type: range key: idx_user_status_created rows: 12 Extra: Using where

没有 filesort，完美。

但一旦 user_id 变成 IN 列表，filesort 就不可避免。这是 MySQL 优化器的限制，不是索引设计的问题。

5.3 实际方案

对于这种"多用户 + 状态 + 时间范围 + 排序"的综合查询，我的做法是：

IN 列表短（< 20个）：直接用idx_user_status_created，接受小规模的 filesort
IN 列表长（20-500个）：分批查询，应用层合并
超大量 IN：用临时表 + JOIN

还有一个容易被忽略的优化——覆盖索引：

-- 如果查询只需要索引列，加上 id（主键在二级索引中自动包含）SELECTid,user_id,status,created_atFROMt_orderWHEREuser_id=1001ANDstatus='PAID'ANDcreated_at>='2025-01-01'ORDERBYcreated_atDESCLIMIT20;

type: range key: idx_user_status_created rows: 12 Extra: Using where; Using index

Using index意味着 MySQL 完全不需要回表查主键索引，直接从二级索引的叶子节点拿数据。性能提升非常明显，特别是在高并发场景下，能减少大量的随机 IO。

如果业务上确实需要更多字段，可以在索引后面补上：

CREATEINDEXidx_user_status_created_amountONt_order(user_id,status,created_at,total_amount);

索引变宽了，占用更多磁盘空间，但换来的是查询不需要回表。这是空间换时间的经典取舍。

六、索引设计速查表

我总结了几个常见的查询模式和对应的索引方案：

查询模式	推荐索引	说明
`WHERE a = ? ORDER BY b`	`(a, b)`	等值 + 排序，经典覆盖
`WHERE a = ? AND b > ?`	`(a, b)`	等值 + 范围
`WHERE a = ? AND b > ? ORDER BY b`	`(a, b)`	范围列和排序列相同
`WHERE a IN (...) ORDER BY b`	`(a, b)`	IN 是等值，但可能有 filesort
`WHERE a = ? AND b = ? AND c > ? ORDER BY c`	`(a, b, c)`	等值 → 等值 → 范围/排序
`WHERE a = ? AND b > ? ORDER BY c`	`(a, b)`或`(a, c)`	b 范围后 c 无法排序，需要权衡
覆盖索引（不回表）	`(a, b, c, d...)`	把 SELECT 的列也放进索引

索引列顺序口诀

等值列放前面，范围列放中间，排序列放后面。

具体来说：

先放=/IN的列（区分度高的优先）
再放范围条件>/</BETWEEN的列
最后放ORDER BY/GROUP BY的列

七、几个容易踩的坑

坑1：给低区分度字段单独建索引

-- status 只有几个值（PAID/SHIPPED/COMPLETED/CANCELLED）CREATEINDEXidx_statusONt_order(status);

这种索引几乎没用。MySQL 优化器发现通过 status 过滤后还有大量数据（比如 45% 的数据都是 COMPLETED），会直接放弃索引走全表扫描，因为全表扫描的顺序 IO 比索引的随机 IO 更快。

坑2：索引列上使用函数

-- 这样索引不生效WHEREDATE(created_at)='2025-01-01'-- 改成范围查询WHEREcreated_at>='2025-01-01'ANDcreated_at<'2025-01-02'

对索引列做任何运算（函数、计算、类型转换），都会导致索引失效。

坑3：隐式类型转换

-- user_id 是 BIGINT，但传了字符串WHEREuser_id='10086'-- 索引可能失效-- 应该传数值类型WHEREuser_id=10086

MySQL 会尝试做类型转换，转换发生在哪一侧取决于字段类型和值的类型，但不管哪种，都可能导致索引用不上。

坑4：索引越多越好？

绝对不是。每个索引都有维护成本：

INSERT/UPDATE/DELETE 需要同步更新所有索引
索引占用磁盘空间
优化器在选择索引时需要评估更多方案，可能选错

我的建议是：一张表的索引数量控制在5-8 个以内。如果一个表有十几个索引，说明查询模式太分散了，可能需要在业务层做聚合或拆分。

八、排查工具

最后分享几个我日常排查慢查询用的工具和命令。

EXPLAIN FORMAT=JSON

比普通 EXPLAIN 信息更全，能看到索引用了哪些列、过滤比例等。

EXPLAINFORMAT=JSONSELECT*FROMt_orderWHEREuser_id=1001ANDstatus='PAID'ORDERBYcreated_atDESCLIMIT20\G

EXPLAIN ANALYZE（MySQL 8.0.18+）

这个是真正执行查询并返回实际耗时的，比 EXPLAIN 的估算值更准：

EXPLAINANALYZESELECT*FROMt_orderWHEREuser_id=1001ANDstatus='PAID'ORDERBYcreated_atDESCLIMIT20\G

重点关注actual time和actual rows，如果估算值和实际值差距超过 10 倍，说明统计信息需要更新：

ANALYZETABLEt_order;

慢查询日志

-- 开启慢查询日志SETGLOBALslow_query_log='ON';SETGLOBALlong_query_time=1;-- 超过1秒记录

定期用mysqldumpslow或pt-query-digest分析 TOP N 慢查询。

sys 库

-- 查看哪些查询全表扫描最多SELECT*FROMsys.statements_with_full_table_scanORDERBYno_index_used_countDESCLIMIT10;-- 查看哪些表没有用好索引SELECT*FROMsys.schema_unused_indexes;