mysql - 添加带有 LIMIT 的 ORDER BY 时，简单的 MySQL 查询被阻塞

Question

我有一张表tb_profilecomments:

4,3 GiB -- 总计 800 万行 -- InnoDB

有 3 个索引:

我运行的查询很简单:

SELECT *
FROM tb_profilecomments
WHERE profilecomment_user_id=6430
ORDER BY profilecomment_id DESC

在不到 1 秒的时间内获得结果(16.000 多行)。当我现在将 LIMIT 1, 5 添加到查询中时，我必须等待超过 2 分钟才能获得结果。

不知道 mysql 后台发生了什么，为什么它会如此严重地减慢查询速度。

当我从查询中删除 ORDER BY 或 LIMIT 时，一切正常。

按非索引 profilecomment_date 列排序时，速度很慢(7 秒)，但不像使用索引主键 profilecomment_id 排序和限制时那样需要 2 分钟.

你知道哪里出了问题吗？可能是坏索引？如何查明？怎么修？ ANALYZE TABLE 显示消息“ok”。

解释 tb_profilecomments:

显示创建表tb_profilecomments:

结果 EXPLAIN SELECT * FROM tb_profilecomments WHERE profilecomment_user_id=6430 ORDER BY profilecomment_id DESC LIMIT 1, 5:

Answer 1

索引中的行是非常明确地排序的。仅限 ASC。即使是 5.7 手册页也说

An index_col_name specification can end with ASC or DESC. These keywords are permitted for future extensions for specifying ascending or descending index value storage. Currently, they are parsed but ignored; index values are always stored in ascending order.

MySQL 一直都有这个限制。 (升级无济于事。)

但是...这不会阻止代码以 DESC 顺序交付结果。

也就是说，MySQL 没有 DESC 索引的不足并不重要。这是一个重要的例子:ORDER BY x ASC, y DESC。无法构建(在 MySQL 中)INDEX 来有效处理该问题。

您的查询的最佳索引是

INDEX(user_id, comment_id);

user_id 将用于WHERE user_id = _constant_，然后 comment_id 将以相反的顺序扫描以获得您想要的行。有或没有 LIMIT。

但是...索引必须处理所有的WHERE、GROUP BY和 LIMIT 之前的 ORDER BY 会短路执行。 (否则，在可以应用 LIMIT 之前，存在文件排序、tmp 表等。)

对于超慢查询，请提供 EXPLAIN SELECT ... 和(如果可能)EXPLAIN FORMAT=JSON SELECT ...。

不要将 PRIMARY KEY 放在 InnoDB 表上!这是非常需要的。 (好吧，如果你要更换它，就把它扔掉。)