mysql - 两个不稳定的查询性能

Question

我对这两个查询真的很困惑，它们的查询速度不稳定。这是我的两表方案；

帖子表:(id、标题、日期等...)[日期索引]

关系表:(news_id, relation_id) [both and per row have indexes]

查询 A:

SELECT *
FROM posts
WHERE id IN (
    SELECT news_id
    FROM relationships
    WHERE relation_id IN (?)
)
AND status = 1
ORDER BY `date` DESC

查询 B:

SELECT *
FROM posts AS p
INNER JOIN relationships AS r ON r.news_id = n.id
WHERE r.relation_id IN (?)
AND n.status = 1
ORDER BY n.date DESC

现在奇怪的部分是测试结果；首先尝试一个有 30 行的 relation_id；

查询 A:总共 30 个，查询耗时 5.56 秒

查询 B:总共 30 个，查询耗时 0.03 秒

A 在较少的行上速度较慢，B 在较少的行上速度较快。接下来尝试一个有 3k 行的 relation_id；

查询 A:总共 3,850，查询耗时 0.05 秒

查询 B:总共 3,850，查询耗时 0.70 秒

所以这让我很困惑，现在数据越多，A 越快。最后一个，尝试使用 +10k 行的 multi relation_id；例子; relation_id IN (1, 2, 3, 4)

查询 A:总共 18,906，查询耗时 0.01 秒

查询 B:总共 18,906，查询耗时 3.34 秒

那我该怎么办呢？查询 A 在如此多的行上速度很快，但在行数较少的情况下速度很慢。这个查询还有其他真正的建议吗？ (抱歉我的英语不好或语法错误)

编辑

这里是 SQL EXPLAIN ;

查询 A 有 30 行

具有 30 行的查询 B

查询 A 有 18k 行

具有 18k 行的查询 B

Answer 1

很奇怪，但这就是 MySQL ;-)

优化器认为status = 1 是一个强限制。如果成立，你就过去了

select status=1, count(*) as num from posts group by status=1

如果您的表中只有一小部分status=1，那么从 mysql 的角度来看，30 行的解决方案仍然相当不错。

如果很大一部分有 status = 1 那么您应该尝试使用

进行查询

from posts ignore index (status) ...

或

from posts force index (id) ...

强制执行 where-in-solution。

您可以通过以下方式获得更多信息

explain select count(*) from relationships where relation_id in (1)

这显示了优化器根据其索引统计信息期望的大小。

固定索引提示的问题在于它们是固定的。这意味着他们申请好的和坏的场景。

有时临时表有助于避免这种情况，您可以将相关关系存储到其中。