当结果小于限制时mysql查询速度变慢

Question

我有一个包含 550.000 条记录的表

SELECT * FROM logs WHERE user = 'user1' ORDER BY date DESC LIMIT 0, 25

此查询需要 0.0171 秒。没有 LIMIT，有 3537 个结果

SELECT * FROM logs WHERE user = 'user2' ORDER BY date DESC LIMIT 0, 25

此查询需要 3.0868 秒。如果没有 LIMIT，则有 13 个结果

表键是:

PRIMARY KEY  (`id`),
KEY `date` (`date`)

使用“LIMIT 0,25”时，如果记录少于 25 条，查询速度会变慢。我该如何解决这个问题？

Answer 1

使用limit 25允许查询在找到 25 行时停止。

如果 550.000 行中有 3537 条匹配行，则假设均匀分布，在检查 550.000/3537*25 rows = 3887 rows 后平均会找到 25 行在按 date 排序的列表中( date 上的索引)或根本没有排序的列表。

如果 550.000 行中有 13 个匹配行，limit 25将必须检查所有 550.000 行(这是行数的 141 倍)，因此我们期望 0.0171 sec * 141 = 2.4s 。显然还有其他因素决定运行时间，但数量级是合适的。

还有一个额外的效果。不幸的是索引为date不包含 user 的值，因此 MySQL 必须通过在原始表中来回跳转来查找该值(因为数据本身是按主键排序的)。这比直接读取无序表要慢。

实际上，如果您有很多行需要读取，那么根本不使用索引可能比使用索引更快。您可以通过使用例如强制 MySQL 不使用它FROM logs IGNORE INDEX (date) ，但这会产生这样的效果:它现在必须在每种情况下读取整个表:最后一行可能是最新的，因此必须位于结果集中，因为您按 date 进行了排序。 。因此，它可能会减慢您的第一个查询 - 快速读取完整的 550.000 行可能比通过来回跳转缓慢读取 3887 行慢。 (MySQL 事先也不知道这一点，所以它做出了选择 - 对于你的第二个查询显然是错误的)。

那么如何更快地获得结果呢？

有一个按 user 排序的索引。然后查询'user2'可以在 13 行后停止，因为它知道没有更多行了。现在这将比查询 'user1' 更快。 ，必须查看 3537 行，然后按 date 对它们进行排序.

因此，您的查询的最佳索引将是 user, date ，因为它知道何时停止查找更多行，并且列表已经按照您想要的方式排序(并且在所有情况下都超过了 0.0171 秒)。

索引也需要一些资源(例如，更新表时更新索引的硬盘空间和时间)，因此为每个查询添加完美的索引有时可能会对整个系统产生反作用。