4-14 2,569 views
在系统中需要进行分页的时候,我们通常会使用limit 加上偏移量的方法实现,再加上合适的order by 子句,如果有对应的索引,通常效率会很不错,否则mysql需要做大量的文件排序操作。
SELECT * FROM USERS ORDER BY ID LIMIT 10;
但是现在有一个问题是假如现在便宜量很大,比如是limit 1000,10,limit 1000000,10这样的查询,
这时效率就会很慢,有时候甚至要访问半个表的数据。
测试案例:现在我有一张文章表,数据量是1700W,假如我查询从100开始,取最新10条数据,大概花费了0.0005秒
正在显示第 100 - 109 行 (共 10 行, 查询花费 0.0005 秒。)
SELECT * FROM `Posts` ORDER BY ID DESC limit 100,10
测试案例:现在我有一张文章表,数据量是1700W,假如我查询从10W开始,取最新10条数据,大概花费了0.23秒
SELECT * FROM `Posts` ORDER BY ID DESC limit 100000,10
正在显示第 100000 - 100009 行 (共 10 行, 查询花费 0.2368 秒。)
所以这时候就需要优化limit语句了,
延迟关联方式
将上面的语句改为内关联的方式,同样从10W条开始取10条,这种方式可以让mysql扫描尽可能少的页面,获取需要的访问记录后再根据关联列回原表查询需要的所有列。这个技术也可以用于优化关联查询中的limit子句。
SELECT * FROM `Posts`
INNER JOIN (
SELECT Id FROM Posts
ORDER BY Id DESC LIMIT 100000,10
) AS lim USING(Id);
正在显示第 0 - 9 行 (共 10 行, 查询花费 0.0994 秒。)
每次翻页记录最大ID
这种方式适合只有上一页,下一页的场景,不适合跳页,原理是,每次翻页记录当前页的最大或最小ID,这种方式效率会非常高。这种方式很适合接口连续翻页,接口每次请求附带上一次的最大ID。
所以语句可以这么写
SELECT * FROM Posts Where Id < 当前页最大ID,或者最小ID, LIMIT 10
参考《高性能mysql》第三版