mysql - MySQL不使用LIMIT来优化查询选择功能吗？

Question

我有一个必须在应用程序中运行的复杂查询，这给我带来了一些性能问题。我这里已经简化了。数据库是CentOS上的MySQL 5.6.35。

SELECT a.`po_num`, 
       Count(*)          AS item_count, 
       Sum(b.`quantity`) AS total_quantity, 
       Group_concat(`web_sku` SEPARATOR ' ') AS web_skus 
FROM   `order` a 
       INNER JOIN `order_item` b 
               ON a.`order_id` = b.`order_key` 
WHERE  `store` LIKE '%foobar%'
LIMIT  200 offset 0; 

此查询的关键部分是我放置“foobar”作为占位符的位置。如果该值类似于 big_store，则查询所需的时间比该值是small_store(在此处提供的查询中大约 0.4 秒，在我实际使用的查询中大约需要 0.1 秒)要长得多。如果没有限制，big_store 将返回更多的结果。

但是有一个限制，这就是让我惊讶的地方。两个数据集都超过了 LIMIT，即 200。在我看来，MySQL 执行选择函数 COUNT、SUM、GROUP_CONCAT 对于所有 big_store/small_store 行，然后追溯应用 LIMIT。我想当你达到 200 时最好停止。

在抓取 200 行之后，它不能执行选择函数 COUNT、SUM、GROUP_CONCAT 操作吗？会使用，使我的查询更快吗？这对我来说似乎是可行的，除非其中一行上有 ORDER BY 。

MySQL不使用LIMIT来优化查询选择函数吗？如果没有，有充分的理由吗？如果是的话，我上面的想法是否有错误？

Answer 1

由于LIMIT，它可能会短暂停止，但这不是一个合理的查询，因为没有ORDER BY。

如果没有 ORDER BY，它会选择任意 200 行，然后停止。

使用ORDER BY，它将必须扫描包含store的整个表(请使用它们来自的表来限定列!)。这是因为前导通配符。只有这样才能修剪到 200 行。

另一个问题——如果没有GROUP BY，聚合(SUM等)就会在整个表(或者至少是过滤后剩下的表)上执行。 LIMIT 在此之后才应用。

也许您要问的是 MariaDB 5.5.21 的“LIMIT_ROWS_EXAMINED”。

这样想...SELECT 的所有组件都是按照语法指定的顺序完成的。由于 LIMIT 是最后一个，因此只有在执行其他操作之后它才适用。

(有几个异常(exception):(1) SELECT col... 必须在 FROM ... 之后完成，因为它不知道哪个表( s); (2) 优化器很容易对 WHERE ... AND ... 中的 JOINed 表和子句进行重新排序。)

有关该查询的更多详细信息。

1. 优化器向前查看，发现 WHERE 正在对 order 进行过滤(这就是 store 所在的位置，是吗？)，因此它决定从表order开始。
2. 它从 order 中获取与 %foobar% 匹配的所有行。
3. 对于每个这样的行，在 order_item 中查找行。现在它有一定数量的行(可能超过 200 行)用于进行聚合。
4. 执行聚合 - COUNT、SUM、GROUP_CONCAT。 (实际上，这可能会在收集行时完成——另一种优化。)
5. 现在有 1 行(a.po_num 的值不可预测)。
6. 对于 LIMIT 的 OFFSET 部分跳过 0 行。 (好吧，又是一个乱七八糟的事情。)
7. 最多传送 200 行。 (只有 1 个。)

添加ORDER BY(但没有GROUP BY)——大不了，对第1行进行排序。

添加GROUP BY(但没有ORDER BY)，现在可能会出现超过200行，并且可能会短暂停止。

添加GROUP BY 和 ORDER BY 和它们是相同的，那么它可能 必须对分组进行排序，但不需要对排序进行排序，并且它可能停在 200。

添加GROUP BY 和 ORDER BY 并且它们不相同，那么它可能必须对分组进行排序，将必须重新排序以进行排序，并且不能在200处停止，直到之后> ORDER BY。也就是说，几乎所有工作都是针对所有数据执行的。

哦，如果没有最佳索引，所有这一切都会变得更糟。哦，我是不是没有坚持提供SHOW CREATE TABLE？

我为我的语气道歉。我已经向你提出了很多建议；请向他们学习。