SQL语句LIMIT在MySQL中的性能分析

limit语句的执行时间

我的MySQL中有一个表(table)，里面有450000条数据。下面这条SQL语句是将id排序后，查询从第0位（偏移量）开始，往后的10条数据。

SELECT * FROM table order by id desc limit 0, 10

注意这条语句是先执行order by，再按limit要求来查询。

根据limit偏移量的不同，SQL语句执行时间的统计数据如下：

SQL	Time Cost
limit 0, 8000	0.73s
limit 1000, 8000	0.92s
limit 10000, 8000	2s
limit 100000, 8000	3.3s

为什么偏移量越大，查询越费时？

• 每条数据的实际存储长度不一样（所以必须要依次遍历，不能直接跳过前面的一部分）
• 哪怕是每条数据存储长度一样，如果之前有过delete操作，那索引上的排列就有gap
• 所以数据不是定长存储，不能像数组那样用index来访问，只能依次遍历，就导致偏移量越大查询越费时

归根结底，这个问题跟MySQL的数据存储结构有关。

参考

1. http://blog.csdn.net/fangwei1235/article/details/8621655
2. http://stackoverflow.com/questions/1612957/mysql-index-configuration
3. https://explainextended.com/2009/10/23/mysql-order-by-limit-performance-late-row-lookups/
4. http://stackoverflow.com/questions/4481388/why-does-mysql-higher-limit-offset-slow-the-query-down
5. order by是如何执行的，http://stackoverflow.com/questions/6954981/how-does-mysql-order-by-implemented-internally
6. mysql 索引的原理，http://www.uml.org.cn/sjjm/201107145.asp
7. MySQL存储引擎MyISAM与InnoDB的优劣, https://www.pureweber.com/article/myisam-vs-innodb/
8. 存储引擎，http://coolshell.cn/articles/1846.html