MySQL 以 DESC 顺序查询比 ASC 顺序更快

Question

我做了一个简单的数据库(innodb 版本 5.7.9)，有 2 个表，post 和 post_tag。

Post 将单个字段 ID(大整数)设置为主键(大约 120,000 个条目)。post_tag有2个字段，post_id(big int)和tag_id(int)，主键在[post_id, tag_id]上。

以下查询在 ~1 毫秒内运行:

SELECT 
   SQL_NO_CACHE p.id 
FROM 
   post as p 
STRAIGHT_JOIN 
   post_tag t
WHERE  
   t.post_id = p.id AND t.tag_id = 25 
ORDER BY 
   p.id DESC
LIMIT 0, 100

但如果我将 ORDER BY 更改为 ASC，它的运行速度会慢大约 100 倍!我感兴趣的那种...

知道为什么吗？

最初，我希望 ID 按 DESC 排序，但我发现它比 ASC 慢。我读到索引的自然排序是 ASC，所以我恢复了所有 ID(通过执行 ID = SOMETHING BIG - ID)，但它没有改变任何东西，因为它现在在 ASC 中变慢了。

我上传了数据库here以防有用。

非常感谢任何可以提供帮助的人。

这里是解释:

Answer 1

如果有“其他限制”，那么所有的赌注都会被取消。

与此同时，看看你有什么......

STRAIGHT_JOIN、USE INDEX 等是以下情况的拐杖:(a) 您没有“正确”的索引，或 (b) 优化器可以不要想出“正确”的事情要做。 也就是说，寻找其他解决方案。

在您的示例中，您最好使用普通的 JOIN 和 INDEX(tag_id, post_id)。这会让它转到 post_tag first 因为有一个 WHERE 子句让它在那里过滤。优化器可能会发现 t.post_id 和 p.id 是相同的，所以从 的结尾开始(对于 DESC) (25, post_id) 在索引中，然后扫描。然后检查是否有 post 条目(这是 post 的唯一明显用途——同样，如果有“其他限制”，则所有赌注都关闭) .

所以，回到最初的问题。 STRAIGHT_JOIN 强制首先查看帖子。但是25岁的人在哪里？显然接近 post_tag 的结尾。因此，与在另一端开始扫描相比，ASC 找到 100 个(参见 LIMIT)所需的时间更长!

假设这是一个多对多的映射表，这样做:

CREATE TABLE post_tag (
    post_id ...,
    tag_id ...,
    PRIMARY KEY(post_id, tag_id),
    INDEX      (tag_id, post_id)
) ENGINE=InnoDB;

我在 my blog 中讨论了很多原因.

如果像建议的那样添加 (tag_id, post_id DESC)，请不要误以为 DESC 有任何意义——它是公认的，但被忽略了。 两个部分都将被存储为ASC。将会发生的是优化器足够聪明，可以在 25 秒结束时开始并向后扫描。这是“证明”:

美国有INDEX(state, population):

mysql> FLUSH STATUS;
mysql> SELECT city, population FROM US
          WHERE state = 'OH'
          ORDER BY population DESC LIMIT 5;
+------------+------------+
| city       | population |
+------------+------------+
| Columbus   |     736836 |
| Cleveland  |     449514 |
| Toledo     |     306974 |
| Cincinnati |     306382 |
| Akron      |     208414 |
+------------+------------+
mysql> SHOW SESSION STATUS LIKE 'Handler%';
| Handler_read_key           | 1     |  -- get started at end of Ohio
| Handler_read_prev          | 4     |  -- read (5-1) more, scanning backwards

MySQL 在 INDEX 声明中忽略 DESC 的唯一情况是:ORDER BY a ASC, b DESC cannot使用 INDEX(a,b)。