使用ORDER BY和LIMIT时查询速度较慢

Question

我有一个非常慢的查询，因为使用ORDER BY。现在我知道为什么它很慢了，但我不知道如何让它更快。

这张表有21.000条记录。(速度慢的原因)

SELECT 
 id_registre, 
 section, 
 numero_naissance, 
 annee_naissance, 
 TYPE, 
 prenom_fr, 
 nom_fr, 
 prenom_ar, 
 nom_ar, 
 date_naissance_equivalent, 
 date_redaction_equivalent
FROM registres
ORDER BY annee_naissance, numero_naissance
LIMIT 20010 , 30

我使用limit是因为我使用分页。

这个查询大概需要119秒，这太长了。如果我删除order by子句，这个查询大约需要0.92秒。

我在numero_naissance列的annee_naissance上有索引。

"annee_naissance“和"numero_naissance”列的类型为int(11)

Answer 1

此查询的最佳索引是registres(annee_naissance, numero_naissance)上的复合索引。

请注意，这两列需要在同一索引中。

Answer 2

您可以尝试强制使用复合索引(以防MySQL优化器选择使用文件排序)

CREATE INDEX naissance_idx ON registres(annee_naissance, numero_naissance);

然后

SELECT * 
FROM registres 
FORCE INDEX(naissance_idx) 
ORDER BY annee_naissance, numero_naissance 
LIMIT 20010,30

但我不确定这是否能将查询时间从119秒减少到1秒。

更多按优化排序的技巧：https://dev.mysql.com/doc/refman/5.0/en/order-by-optimization.html

Answer 3

我测试了EXPLAIN查询

EXPLAIN SELECT 
         id_registre, 
         section, 
         numero_naissance, 
         annee_naissance, 
         TYPE, 
         prenom_fr, 
         nom_fr, 
         prenom_ar, 
         nom_ar, 
         date_naissance_equivalent, 
         date_redaction_equivalent
        FROM registres
        ORDER BY annee_naissance, numero_naissance
        LIMIT 20010 , 30

这就是结果：

​

​