带ORDER BY和LIMIT的MySQL慢查询问题排查与优化咨询

问题背景 #

现有一条SQL查询，添加ORDER BY mv.id_ DESC LIMIT 100后执行耗时约4秒，移除ORDER BY子句后仅需0.002秒。各表数据量：match_表约100万行，tournament表约3万行。

原查询语句 #

SELECT 
    m.id_ AS match_id,
    mv.id_ AS match_version_id,
    t.id_ AS tournament_id
FROM
    test.match_version AS mv
        JOIN
    test.match_ AS m ON m.id_ = mv.match_id
        JOIN
    test.tournament AS t ON t.orig_id = m.tournament_orig_id
        AND t.tour_id = m.tour_id
ORDER BY mv.id_ DESC
LIMIT 100;

表结构定义 #

CREATE TABLE `match_` (
  `id_` int NOT NULL AUTO_INCREMENT,
  `tour_id` tinyint NOT NULL,
  `tournament_orig_id` int NOT NULL,
  PRIMARY KEY (`id_`),
  KEY `ix__match__tour_id` (`tour_id`),
  KEY `ix__match__tour_id__tournament_orig_id` (`tour_id`,`tournament_orig_id`),
  KEY `ix__match__tournament_orig_id` (`tournament_orig_id`),
  CONSTRAINT `fk__match__tournament` FOREIGN KEY (`tour_id`, `tournament_orig_id`) REFERENCES `tournament` (`tour_id`, `orig_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1666470 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `match_version` (
  `id_` int NOT NULL AUTO_INCREMENT,
  `match_id` int NOT NULL,
  `generation_start` int NOT NULL DEFAULT '0',
  `generation_end` int NOT NULL DEFAULT '0',
  PRIMARY KEY (`id_`),
  KEY `ix__match_version__match_id` (`match_id`),
  KEY `ix__match_version__match_id__generation_end` (`match_id`,`generation_end`),
  KEY `ix__match_version__generation_end` (`generation_end`),
  CONSTRAINT `fk__match_version__match_id` FOREIGN KEY (`match_id`) REFERENCES `match_` (`id_`)
) ENGINE=InnoDB AUTO_INCREMENT=1669206 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `tournament` (
  `id_` int NOT NULL AUTO_INCREMENT,
  `tour_id` tinyint NOT NULL,
  `orig_id` int NOT NULL,
  PRIMARY KEY (`id_`),
  UNIQUE KEY `uq__tournament_oc__tour_id__orig_id` (`tour_id`,`orig_id`),
  KEY `ix__tournament_oc__tour_id` (`tour_id`),
  KEY `ix__tournament_oc__orig_id` (`orig_id`)
) ENGINE=InnoDB AUTO_INCREMENT=30962 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

EXPLAIN执行结果 #

+----+-------------+-------+------------+-------+-------------------------------------------------------------------------------------------------+----------------------------------------+---------+-------------------------------+-------+----------+----------------------------------------------+
| id | select_type | table | partitions | type  |                                          possible_keys                                          |                  key                   | key_len |              ref              | rows  | filtered |                    Extra                     |
+----+-------------+-------+------------+-------+-------------------------------------------------------------------------------------------------+----------------------------------------+---------+-------------------------------+-------+----------+----------------------------------------------+
|  1 | SIMPLE      | t     | NULL       | index | uq__tournament_oc__tour_id__orig_id,ix__tournament_oc__tour_id,ix__tournament_oc__orig_id       | uq__tournament_oc__tour_id__orig_id    |       5 | NULL                          | 30900 |      100 | Using index; Using temporary; Using filesort |
|  1 | SIMPLE      | m     | NULL       | ref   | PRIMARY,ix__match__tour_id,ix__match__tour_id__tournament_orig_id,ix__match__tournament_orig_id | ix__match__tour_id__tournament_orig_id |       5 | test.t.tour_id,test.t.orig_id |    48 |      100 | Using index                                  |
|  1 | SIMPLE      | mv    | NULL       | ref   | ix__match_version__match_id,ix__match_version__match_id__generation_end                         | ix__match_version__match_id            |       4 | test.m.id_                    |     1 |      100 | Using index                                  |
+----+-------------+-------+------------+-------+-------------------------------------------------------------------------------------------------+----------------------------------------+---------+-------------------------------+-------+----------+----------------------------------------------+

性能骤降原因 #

从EXPLAIN结果可见，MySQL优化器选择了从tournament表开始全索引扫描（扫描3万行数据），再依次关联match_和match_version表，最终需要对所有关联得到的mv.id_进行全局排序（Extra列显示Using temporary; Using filesort）。这一过程需要处理大量数据并完成排序操作，导致耗时剧增。

而移除ORDER BY后，优化器可以选择更高效的执行路径：直接从match_version表取最新数据（利用LIMIT 100），再关联其他表，仅需处理100条数据，因此速度极快。

解决方案 #

核心思路是引导优化器优先从match_version表获取数据（利用mv.id_的自增主键特性，直接取最新的100条），避免全局排序。

方案1：使用STRAIGHT_JOIN强制表关联顺序 #

修改查询语句，强制优化器先扫描match_version表，再依次关联match_和tournament：

SELECT 
    m.id_ AS match_id,
    mv.id_ AS match_version_id,
    t.id_ AS tournament_id
FROM
    test.match_version AS mv
        STRAIGHT_JOIN
    test.match_ AS m ON m.id_ = mv.match_id
        STRAIGHT_JOIN
    test.tournament AS t ON t.orig_id = m.tournament_orig_id
        AND t.tour_id = m.tour_id
ORDER BY mv.id_ DESC
LIMIT 100;

这种方式让优化器按照指定顺序执行：先从mv表取最新的100条记录（通过主键id_ DESC直接获取，无需排序），再关联m和t表，全程只需处理100条数据，性能会大幅提升。

补充说明 #

已知当前缺少tournament_version.tournament_id关联tournament.id_的外键设计并非最优，但本次方案仅基于现有环境进行优化。

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