联合索引 (A, B) 的范围查询详解

以 (A, B) 为联合索引，分析不同查询条件（=, >, >=, LIKE）下的索引使用行为。通过具体场景和执行计划（EXPLAIN）解析索引的生效范围，并给出优化建议。

场景1：等值查询A + 范围查询B

SELECT * FROM table WHERE A = 1 AND B > 5;

  • 索引使用：

    • A = 1 → 定位到所有A为1的索引项。
    • B > 5 → 在A=1的子树中，按B的范围扫描（range类型）。
    • B列仍能使用索引（因为A是等值查询，B在连续范围内）。

  • 执行计划：

    • type: range
    • key_len: 8（假设A和B均为INT类型，各占4字节，总长度8字节）
    • Extra: Using index condition（可能触发ICP优化）。

场景2：范围查询A + 等值查询B

SELECT * FROM table WHERE A > 1 AND B = 2;

  • 索引使用：

    • A > 1 → 对A进行范围扫描，索引仅使用到A列。
    • B = 2 → 无法通过索引过滤（A的范围查询中断了B的索引使用）。
    • ICP优化：在存储引擎层过滤B=2的行，减少回表次数。

  • 执行计划：

    • type: range（仅A列参与范围扫描）
    • key_len: 4（仅A列被使用）
    • Extra: Using index condition（ICP生效）。

场景3：等值查询A + LIKE查询B（前缀匹配）

SELECT * FROM table WHERE A = 1 AND B LIKE 'abc%';

  • 索引使用：

    • A = 1 → 定位到A=1的索引项。
    • B LIKE 'abc%' → 在A=1的子树中，按B的前缀范围扫描（视为range类型）。
    • B列使用索引（前缀匹配等价于 B >= 'abc' AND B < 'abd'）。

  • 执行计划：

    • type: range
    • key_len: 8（A和B均被使用）
    • Extra: Using index condition（可能触发ICP）。

场景4：LIKE查询A（前缀匹配） + 等值查询B

SELECT * FROM table WHERE A LIKE 'a%' AND B = 2;

  • 索引使用：

    • A LIKE 'a%' → 触发索引范围扫描（等价于 A >= 'a' AND A < 'b'）。
    • B = 2 → 无法通过索引过滤（A的范围查询中断了B的索引使用）。
    • ICP优化：在存储引擎层过滤B=2的行。

  • 执行计划：

    • type: range（仅A列参与范围扫描）
    • key_len: 4（假设A为VARCHAR，前缀匹配长度足够）
    • Extra: Using index condition。

场景5：范围查询A + 范围查询B

SELECT * FROM table WHERE A >= 1 AND A <= 10 AND B > 5;

  • 索引使用：

    • A BETWEEN 1 AND 10 → 对A进行范围扫描。
    • B > 5 → 无法通过索引过滤（A的范围查询中断B的索引使用）。
    • ICP优化：在存储引擎层过滤B>5的行。

  • 执行计划：

    • type: range（仅A列参与范围扫描）
    • key_len: 4
    • Extra: Using index condition.

场景6：仅范围查询A

SELECT * FROM table WHERE A > 100;

  • 索引使用：

    • A > 100 → 索引范围扫描。
    • B列未被查询条件使用，但索引仍能覆盖A的范围。

  • 执行计划：

    • type: range
    • key_len: 4
    • Extra: Using where（若需回表，否则为Using index）。

关键规则总结

  1. 最左前缀原则：

    • 只有等值查询的列可延续使用后续索引列的范围过滤。
    • 范围查询列会中断后续列的索引使用（但ICP可优化过滤）。

  2. LIKE的索引使用：

    • LIKE 'prefix%' 视为范围查询，可使用索引。
    • LIKE '%suffix' 无法使用索引。

  3. 索引下推（ICP）：

    • 在存储引擎层提前过滤不满足条件的行，减少回表次数（MySQL 5.6+）。

优化建议

  1. 调整索引顺序：

    • 高频等值查询的列应放在范围查询列之前。例如，若查询多为 A = ? AND B > ?，保持索引 (A, B)。

  2. 覆盖索引优化：

    • 若查询仅需索引列，避免回表：

      SELECT A, B FROM table WHERE A > 1 AND B = 2; -- 直接通过索引返回数据

  3. 避免中断性范围查询：

    • 若需频繁使用B列过滤，考虑将B列前置（需权衡业务场景）。

  4. 重写查询：

    • 将大范围拆分为多个小范围，结合 UNION ALL：

      SELECT * FROM table WHERE A BETWEEN 1 AND 1000 AND B = 2
UNION ALL
SELECT * FROM table WHERE A BETWEEN 1001 AND 2000 AND B = 2;

验证工具

通过 EXPLAIN 分析执行计划：

  • type字段：

    • ref → 等值查询（最优）。
    • range → 范围扫描（次优）。
    • index → 全索引扫描（需优化）。

  • key_len字段：

    • 表示实际使用的索引字节数，可判断哪些列被使用。

总结

联合索引 (A, B) 的范围查询行为严格遵循最左前缀原则，范围查询列会中断后续列的索引使用，但通过ICP和合理设计仍可优化性能。实际开发中需结合执行计划分析，针对性调整索引和查询逻辑。

select * from order where status = 1 order by create_time asc

不建索引时的执行过程

当表中没有索引时，这条SQL的执行步骤如下：

  1. 全表扫描（Full Table Scan）：

    • 逐行读取整个order表，过滤出status = 1的所有行。

  2. 临时表与文件排序（Filesort）：

    • 将所有满足条件的行加载到内存临时表（或磁盘临时表，若数据量较大），然后对create_time进行排序。

  3. 返回结果：

    • 将排序后的结果返回给客户端。

性能问题：

  • I/O开销高：全表扫描需要读取大量数据，尤其是大表。
  • 排序成本高：若满足status=1的行数较多，排序操作会消耗大量内存和CPU，甚至触发磁盘临时表（Using filesort）。

执行计划示例（EXPLAIN）：

+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                       |
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------+
| 1  | SIMPLE      | order | ALL  | NULL          | NULL | NULL    | NULL | 1M   | Using where; Using filesort |
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------+

建立联合索引 (status, create_time) 后的执行过程

若对(status, create_time)建立联合索引，执行流程优化为：

  1. 索引范围扫描（Index Range Scan）：

    • 直接在索引树中定位status=1的条目（索引按status排序，相同status下按create_time排序）。

  2. 顺序读取有序数据：

    • 由于create_time在索引中已按升序排列，数据库可以直接按索引顺序读取数据，无需额外排序。

  3. 回表查询（若需要）：

    • 若索引未覆盖所有查询列（如SELECT *），需根据索引中的行指针回表获取完整数据。

性能提升：

  • 减少I/O：仅需读取status=1的索引项和对应的数据页，避免全表扫描。
  • 消除排序：索引天然按create_time排序，无需filesort。

执行计划示例（EXPLAIN）：

+----+-------------+-------+------+---------------+-------------+---------+-------+-------+-----------------------+
| id | select_type | table | type | possible_keys | key         | key_len | ref   | rows  | Extra                 |
+----+-------------+-------+------+---------------+-------------+---------+-------+-------+-----------------------+
| 1  | SIMPLE      | order | ref  | idx_status    | idx_status  | 4       | const | 1000  | Using index condition |
+----+-------------+-------+------+---------------+-------------+---------+-------+-------+-----------------------+

关键差异对比

对比项无索引联合索引 (status, create_time)
扫描方式全表扫描（type: ALL）索引范围扫描（type: ref）
排序开销需要内存/磁盘排序（Using filesort）无需排序（索引已有序）
I/O量高（读取全表数据）低（仅读取索引和少量数据页）
适用场景小表或过滤条件无索引大表且需高效过滤和排序
回表成本无（直接读取数据行）可能有（若需SELECT *）

进一步优化建议

  1. 覆盖索引优化：

    • 若查询仅需status和create_time字段，可将索引设计为(status, create_time, other_cols)，避免回表（Extra: Using index）。
    SELECT status, create_time FROM order WHERE status = 1 ORDER BY create_time ASC;

  2. 数据量过大时的分页优化：

    • 若需分页查询（如LIMIT 1000,10），联合索引可以显著减少排序和I/O开销。

  3. 索引选择性评估：

    • 若status=1的数据占比过高（如超过20%），全表扫描可能比索引扫描更快，需结合业务场景权衡。

总结

  • 无索引时：全表扫描 + 文件排序，适合小表或低频率查询。
  • 联合索引时：索引范围扫描 + 天然排序，适合大表和高性能需求场景。
    核心优化点：通过索引减少数据扫描量和消除排序操作。

上述sql中，不建立索引如何排序

在 MySQL 中，如果未对 create_time 建立索引，当执行 SELECT * FROM order WHERE status = 1 ORDER BY create_time ASC 时，排序操作会在 服务层（Server Layer） 完成，具体通过 filesort 算法实现。以下是详细过程：

1. 排序的位置：服务层（Server Layer）

MySQL 的架构分为 存储引擎层（InnoDB/MyISAM） 和 服务层（Server Layer）：

  • 存储引擎层：负责数据存储和索引查询。
  • 服务层：负责解析 SQL、优化查询、执行排序等逻辑。

当没有索引支持排序时，服务层需要自行处理排序逻辑。

2. 排序的步骤

阶段1：全表扫描 + 过滤数据

  1. 存储引擎层：

    • 执行全表扫描（Full Table Scan），逐行读取 order 表中的所有数据。
    • 根据 WHERE status = 1 条件过滤出符合条件的行，并将这些行的数据传递给服务层。

阶段2：收集排序数据

  1. 服务层：

    • 将过滤后的所有行的 create_time 值和对应的行数据（或行指针）加载到内存的 排序缓冲区（sort_buffer） 中。
    • 如果数据量较小（小于 sort_buffer_size），直接在内存中完成排序。
    • 如果数据量较大，内存不足时，会将数据分块排序，并将中间结果写入 磁盘临时文件。

阶段3：排序（filesort）

  1. 排序算法：

    • 单路排序（Single-Pass）：
      将 create_time 和需要返回的所有列（SELECT *）一起加载到内存排序。适用于数据量较小的场景。
    • 双路排序（Two-Pass）：
      仅将 create_time 和主键（或行指针）加载到内存排序，排序后再根据主键回表获取完整数据。适用于数据量较大的场景。

阶段4：返回结果

  1. 服务层：

    • 按排序后的顺序（create_time ASC）返回结果。

3. 性能问题

  • I/O 开销：

    • 全表扫描需要读取所有数据页，即使大部分数据不满足 status = 1。
    • 若排序数据超过内存容量，需多次读写磁盘临时文件，进一步增加延迟。

  • CPU 开销：

    • 排序复杂度为 O(N log N)，数据量越大，耗时越长。

4. 如何验证排序行为？

通过 EXPLAIN 查看执行计划，关注 Extra 字段：

+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------+
| id | select_type | table | type | possible_keys | key  | rows    | Extra                       |
+----+-------------+-------+------+---------------+------+---------+-----------------------------+
| 1  | SIMPLE      | order | ALL  | NULL          | NULL | 1000000 | Using where; Using filesort |
+----+-------------+-------+------+---------------+------+---------+-----------------------------+
  • Using filesort：明确表示使用了服务层的排序逻辑。

5. 对比联合索引的优化效果

若对 (status, create_time) 建立联合索引：

  • 存储引擎层：
    直接按索引顺序（status=1 且 create_time 有序）返回数据，无需服务层排序。
  • 执行计划：
    Extra 字段中不再出现 Using filesort。

总结

  • 无索引时的排序：

    • 发生在服务层，通过 filesort 完成。
    • 需要全表扫描 + 内存/磁盘排序，性能较差。

  • 优化方向：

    • 通过索引（如 (status, create_time)）将排序下推到存储引擎层，避免服务层排序。
    • 减少数据扫描量（WHERE 过滤）和排序成本（ORDER BY 天然有序）。
最后修改：2025 年 03 月 24 日
© 允许规范转载
如果觉得我的文章对你有用，请随意赞赏