ORDER BY LIMIT 查询的 MySQL 索引用法

Question

我正在使用包含超过 200 万条记录的“用户”表。查询是:

SELECT * FROM users WHERE 1 ORDER BY firstname LIMIT $start,30

“名字”列已编入索引。获取第一页非常快，而获取最后一页非常慢。

我使用了 EXPLAIN，结果如下:

对于

EXPLAIN SELECT * FROM `users` WHERE 1 ORDER BY `firstname` LIMIT 10000 , 30

我得到:

id  select_type     table   type    possible_keys   key     key_len     ref     rows    Extra
1   SIMPLE  users   index   NULL    firstname   194     NULL    10030 

但是为了

EXPLAIN SELECT * FROM `users` WHERE 1 ORDER BY `firstname` LIMIT 100000 , 30

我得到了

id  select_type     table   type    possible_keys   key     key_len     ref     rows    Extra
1   SIMPLE  users   ALL     NULL    NULL    NULL    NULL    2292912     Using filesort

有什么问题？

Answer 1

您不应该使用 limit 将分页到您的数据集中那么远。

通过使用范围查询，您将获得很多更好的结果。

SELECT * FROM users 
WHERE firstname >= last_used_name 
ORDER BY firstname 
LIMIT 30

其中 last_used_name 是您已经看到的(我假设您进行某种批处理)。如果您对具有唯一索引的列进行范围查询，您将获得更准确的结果。这样您就不会两次获得相同的记录。

当你做的时候

LIMIT 100000 , 30

MySQL 本质上与中的相同

LIMIT 100030

只是它不返回前 10 万。但它会对它们进行排序和读取。