mysql 使用ORDER BY和LIMIT的表进行查询时运行缓慢

nkhmeac6 于 2023-08-02 发布在 Mysql

关注(0)|答案(3)|浏览(140)

我有以下查询：

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;

字符串
在比赛表具有大约1 m行并且锦标赛表具有大约30 k行的情况下，运行大约需要4秒。如果我删除ORDER_BY，那么它将在0.002秒内运行。
下面是每个表的CREATE TABLE语句：

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                                  |
+----+-------------+-------+------------+-------+-------------------------------------------------------------------------------------------------+----------------------------------------+---------+-------------------------------+-------+----------+----------------------------------------------+

型
为什么当ORDER_BY存在时，我的性能会变得相当差？我该怎么解决这个问题？
注意：值得一提的是，我知道没有tournament_version.tournament_id外键链接到tournament.id_是不理想的，但可悲的是，我正在使用atm！

mysql

来源：https://stackoverflow.com/questions/76682806/slow-running-query-with-table-with-order-by-and-limit

3条答案

按热度按时间

13z8s7eq1#

您的查询似乎遇到了mysql order by limit slow rate look up的问题。不幸的是，这是mysql中缺少的优化。
请参阅此处的其他详细信息以了解此查询为何缓慢。在页面的底部，它显示了一些如何加速查询的策略，如下所示：https://explainextended.com/2009/10/23/mysql-order-by-limit-performance-late-row-lookups/
如果这不起作用，您可以尝试重新构造查询，使其不同时包含ORDER BY和LIMIT。正如你可能注意到从玩弄你的查询，如果你可以删除其中任何一个关键字，它会很快了。

赞(0）回复(0）举报 2023-08-02

z9smfwbn2#

感谢@tay_inc提供的link。以下代码将在0.04秒内执行：

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
        JOIN
    (
        SELECT 
            mv.id_ AS match_version_id
        FROM
            test.match_version AS mv
        ORDER BY mv.id_ DESC
        LIMIT 100
    ) AS o ON o.match_version_id = mv.id_
ORDER BY mv.id_ DESC;

字符串
我能看到的唯一问题是，这会获取mv中的最后100条记录，而不管它们是否可以连接到m和t。在这种情况下对我来说不是问题，但值得记住...

赞(0）回复(0）举报 2023-08-02

fjnneemd3#

我认为你的索引是好的，但有一些额外的你应该摆脱。
因为id_是PRIAMRY KEY，所以用id_开始任何其他索引是不必要的，也是低效的。
没有WHERE过滤，所以优化器可能会决定从ORDER BY的索引开始，这是存在的。这也意味着它将在100行后停止。EXPLAIN不太可能显示这种停止。
FOREIGN KEY是两个东西：

一个索引（可以通过INDEX构造--同样好）
在另一个表中动态检查是否存在链接。

如果ENGINE=InnoDB，则PRIMARY KEY将被静默地附加在每个辅助索引的末尾。因此，在上述索引 * 的末尾添加id_ * 是多余的（可选）。
提示。这实际上可能有助于提高性能：
当你有INDEX(a,b)（或UNIQUE(a,b)）时，不要也有INDEX(a)。它是冗余的，浪费空间，并且可能欺骗优化器使用较短的索引而不是复合索引。（我在每个表定义中都看到一个这样的情况。）
如果EXPLAIN未能将mv列为第一个表，则有多种方法可以强制首先使用它。

赞(0）回复(0）举报 2023-08-02

我来回答

mysql 使用ORDER BY和LIMIT的表进行查询时运行缓慢

3条答案

相关问题

热门标签

最新问答