MySQL 5.7关联查询ORDER BY为何用filesort而非索引排序？

问题背景 #

在MySQL 5.7中执行关联查询时，执行计划触发了filesort，但排序列DOUBLE_VALUE DESC和TEXTUAL_VALUE明明包含在A_LARGE_TABLE的复合索引中，且执行计划显示已使用该索引，排序阶段却未利用索引。测试数据集仅10行（无需调整资源参数），实际场景中A_LARGE_TABLE约有6000万行，而MySQL 8.0中该查询可正常使用索引排序。

原因分析 #

MySQL 5.7的查询优化器在处理JOIN+ORDER BY场景时，索引利用逻辑与8.0存在差异：

• 当前A_LARGE_TABLE的复合索引为(INTEGER_VALUE, DOUBLE_VALUE DESC, TEXTUAL_VALUE)，虽然排序列包含在索引中，但优化器选择的执行路径是先扫描A表的索引，再关联LOOKUP_TABLE。此时关联后的结果集顺序是按照A表的INTEGER_VALUE排序的，与查询要求的DOUBLE_VALUE DESC, TEXTUAL_VALUE顺序不符，因此必须额外执行filesort来重新排序。
• 简单来说，5.7的优化器无法在JOIN操作后直接复用A表索引中的排序顺序，JOIN过程打乱了索引原本的排序逻辑，导致无法跳过filesort步骤。

解决方法 #

方案1：强制指定驱动表，调整执行路径 #

使用STRAIGHT_JOIN强制优化器先扫描LOOKUP_TABLE（小表），再通过A_LARGE_TABLE的索引关联匹配行。此时A表索引中DOUBLE_VALUE DESC, TEXTUAL_VALUE的排序顺序可以直接满足ORDER BY要求，无需filesort：

SELECT *
FROM LOOKUP_TABLE B
STRAIGHT_JOIN A_LARGE_TABLE A ON A.INTEGER_VALUE = B.ID
ORDER BY A.DOUBLE_VALUE DESC, A.TEXTUAL_VALUE;

方案2：升级至MySQL 8.0 #

MySQL 8.0对查询优化器进行了大幅改进，针对此类JOIN+ORDER BY场景的索引利用逻辑做了优化，可以自动识别并复用索引排序，无需额外调整即可避免filesort。

方案3：调整索引（需权衡业务场景） #

若排序优先级远高于关联效率，可尝试创建以DOUBLE_VALUE DESC, TEXTUAL_VALUE, INTEGER_VALUE为顺序的复合索引，但会降低关联查询的效率，仅适用于排序为主的场景。

附：测试用DDL、数据及执行计划 #

DDL与测试数据 #

# 创建A_LARGE_TABLE表
CREATE TABLE A_LARGE_TABLE
(
    ID            BIGINT PRIMARY KEY AUTO_INCREMENT,
    INTEGER_VALUE BIGINT       NOT NULL,
    TEXTUAL_VALUE VARCHAR(255) NOT NULL,
    DOUBLE_VALUE  DOUBLE,
    INDEX (INTEGER_VALUE, DOUBLE_VALUE DESC, TEXTUAL_VALUE)
);

# 创建LOOKUP_TABLE表
CREATE TABLE LOOKUP_TABLE
(
    ID        BIGINT PRIMARY KEY AUTO_INCREMENT,
    SOME_DATA BIGINT NOT NULL
);

# 插入A_LARGE_TABLE测试数据
INSERT INTO A_LARGE_TABLE(INTEGER_VALUE, TEXTUAL_VALUE, DOUBLE_VALUE)
VALUES (1, '1111111111', 2.2),
       (2, '2222222222', 5.2),
       (3, '3333333333', 2.2),
       (4, '4444444444', 56.2),
       (5, '5555555555', 25.5),
       (6, '6666666666', 21.2),
       (7, '7777777777', 25.2),
       (8, '8888888888', 342.2),
       (9, '9999999999', 29.2),
       (10, '1010101010', 32.2);

# 插入LOOKUP_TABLE测试数据
INSERT INTO LOOKUP_TABLE(SOME_DATA)
VALUES (1),
       (2),
       (3),
       (4),
       (5),
       (6),
       (7),
       (8),
       (9),
       (10);

# 触发filesort的查询语句
SELECT *
FROM A_LARGE_TABLE A
         JOIN LOOKUP_TABLE B ON A.INTEGER_VALUE = B.ID
ORDER BY A.DOUBLE_VALUE DESC, A.TEXTUAL_VALUE;

执行计划 #

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "25.00"
    },
    "ordering_operation": {
      "using_filesort": true,
      "cost_info": {
        "sort_cost": "10.00"
      },
      "nested_loop": [
        {
          "table": {
            "table_name": "A",
            "access_type": "index",
            "possible_keys": [
              "INTEGER_VALUE"
            ],
            "key": "INTEGER_VALUE",
            "used_key_parts": [
              "INTEGER_VALUE",
              "DOUBLE_VALUE",
              "TEXTUAL_VALUE"
            ],
            "key_length": "1039",
            "rows_examined_per_scan": 10,
            "rows_produced_per_join": 10,
            "filtered": "100.00",
            "using_index": true,
            "cost_info": {
              "read_cost": "1.00",
              "eval_cost": "2.00",
              "prefix_cost": "3.00",
              "data_read_per_join": "10K"
            },
            "used_columns": [
              "ID",
              "INTEGER_VALUE",
              "TEXTUAL_VALUE",
              "DOUBLE_VALUE"
            ]
          }
        },
        {
          "table": {
            "table_name": "B",
            "access_type": "eq_ref",
            "possible_keys": [
              "PRIMARY"
            ],
            "key": "PRIMARY",
            "used_key_parts": [
              "ID"
            ],
            "key_length": "8",
            "ref": [
              "nfw_prod_db.A.INTEGER_VALUE"
            ],
            "rows_examined_per_scan": 1,
            "rows_produced_per_join": 10,
            "filtered": "100.00",
            "cost_info": {
              "read_cost": "10.00",
              "eval_cost": "2.00",
              "prefix_cost": "15.00",
              "data_read_per_join": "240"
            },
            "used_columns": [
              "ID",
              "SOME_DATA"
            ]
          }
        }
      ]
    }
  }
}

内容的提问来源于stack exchange，提问作者Aneeb Khawar