MySQL范围查询与联合索引优化实战详解

问题排查：隐式的文件排序

假设有一张订单表 order，包含主键 order_no、状态 status 和提交时间 create_time。若分别创建了 status 列索引和 create_time 列索引，执行以下查询：

SELECT order_no FROM order WHERE status = 1 ORDER BY create_time DESC;

该语句的实际执行过程如下：
1. 优化器使用 status 索引过滤数据。
2. 过滤后的数据返回至 Server 层。
3. 由于缺乏对 create_time 的全局有序性支持，Server 层需调用快排算法进行排序。
4. 执行计划中的 Extra 列会出现 filesort。

风险说明：Filesort 会在内存或磁盘文件中临时排序数据。当数据量较大导致内存不足时，会引入昂贵的磁盘IO操作，显著降低查询性能。

解决思路：将 (status, create_time) 建立为联合索引。这样既利用 status 进行了过滤，又利用了联合索引内部按第二列自然有序的特性，直接在存储引擎层完成排序，彻底消除 Filesort。

阿里巴巴 MySQL 规范推荐：如果有 ORDER BY 的场景，请注意利用索引的有序性。ORDER BY 最后的字段是组合索引的一部分，并且放在索引组合顺序的最后，避免出现 FileSort 的情况。

正例：WHERE a=? AND b=? ORDER BY c; 对应索引：a_b_c
反例：存在范围查询时，如 WHERE a > 10 ORDER BY b;，索引 a_b 无法用于排序，因为范围查询破坏了索引的有序连续性。

核心理论：最左匹配原则

理解范围查询优化的前提，是掌握联合索引的“最左前缀”匹配规则。
所谓最左匹配，即 SQL 查询必须从联合索引的最左侧列开始匹配。一旦遇到范围查询操作符（如 >, <, BETWEEN, LIMIT 非分页限制，以及特定的 LIKE），后续列的索引匹配即刻停止。

以联合索引 (a, b, c, d) 为例：

• WHERE a=1 AND b=2 AND c>3 AND d=4：能命中 a, b, c 三列，但 d 列失效，因为 c>3 是范围查询。
• 注意：Mysql 优化器具备智能调整能力。若索引为 (a, b)，执行 b=2 AND a=1 同样能命中索引，优化器会自动重写条件顺序。

实战场景：联合索引的差异化建法

第1步：纯等值查询（场景一）

SQL示例：SELECT * FROM table WHERE a = 1 AND b = 2 AND c = 3;
分析：所有条件均为等值，理论上任意排列的联合索引 (a,b,c), (c,b,a), (b,a,c) 均生效。优化器会自动调整条件顺序。

最佳实践：高区分度字段置于前端。
例如区分度：性别低，地区中等，城市高。应建 (city, region, gender) 索引。对于字符串类型，指定合适的索引长度也是提升区分度的关键，可通过公式计算：count(distinct left(列名, 长度))/count(*)。

第2步：单范围查询（场景二）

SQL示例：SELECT * FROM table WHERE a > 1 AND b = 2;
分析：若建 (a,b) 索引，仅 a 命中，b 因被 > 阻断而失效。若建 (b,a) 索引，则 b（等值）在前，完美避开 a 的范围干扰，两列均可利用。

结论：等值条件放前面，范围条件放后面。

第3步：多范围与混合查询（场景三与拓展）

SQL示例A：WHERE a > 1 AND b = 2 AND c > 3;
此处有两个范围查询。无论是 (b,a) 还是 (b,c) 均可满足基本过滤需求，具体取决于哪个范围的基数更小（Cardinality）以减少回表或扫描行数。

SQL示例B（拓展）：WHERE a = 1 AND b = 2 AND c > 3;
此时应建立索引 (a, b, c)。前两项等值精准定位，第三项范围扫描，完全符合最左匹配且无阻断。

第4步：涉及 Order By 的排序（场景四）

SQL示例：WHERE a = 1 ORDER BY b;
索引选择：直接建立 (a, b) 索引即可。当 a=1 固定时，b 天然有序，无需额外排序。

SQL陷阱：WHERE a > 1 ORDER BY b;
分析：a 的范围导致其覆盖区间内的 b 是无序的。此时索引只能用作过滤 (a)，无法用于 ORDER BY b。不建议建立 (a,b) 来试图同时满足，只会增加维护成本。

综合排序案例：WHERE a=1 AND b=2 AND c>3 ORDER BY c;
建立索引 (a, b, c)，既能过滤又能利用 c 的索引顺序完成排序。

第5步：特殊算符 IN 的处理（场景五）

SQL示例：WHERE a IN (1,2,3) AND b > 1;
分析：在 MySQL 索引逻辑中，IN 被视为一系列离散的等值查询，它不会中止索引匹配。因此，仍然可以建立 (a, b) 联合索引。

局限扩展：WHERE a=1 AND b IN (1,2,3) AND c>3 ORDER BY c;
此时 c 的排序依然无法依赖索引，因为 IN 造成的逻辑分支复杂性使得引擎难以保证整体有序。

总结与核心建议

• 转换思维：尽可能将范围查询转换为“等值”查询。例如 a > 1 AND a < 5 可优化为 a IN (2,3,4)（视业务允许精度而定），从而保住后续字段的索引可用性。
• 顺序铁律：将等值条件（Where/In/Order By）放置在联合索引的前面，范围条件（>/</Between）置后。
• 规避Filesort：永远不要忽视执行计划中的 Extra 信息。利用联合索引的局部有序性代替 Server 层的排序操作，是 SQL 调优的核心手段之一。

以上就是MySQL范围查询优化的详细内容，更多关于MySQL联合索引设计与性能调优的资料请关注本站其它相关文章！