Mysql 分页优化

4-14 413 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》第三版

centos yum安装mysql5.7 或 mysql8.0

参考:http://www.koukousky.com/back/1296.html 参考:https://dev.mysql.com/doc/mysql-yum-repo-quick-guide/en/ $ wget https://dev.mysql.com/get/mysql...

阅读全文

MySQL 5.7以上 root用户默认密码问题【转】

转载地址:https://www.yanning.wang/archives/379.html 最近有点小问题,需要在本地虚拟机里搭建一个MySQL服务器.但是按着以前的日志一步步操作,最后总是...

阅读全文

mysql根据汉字首字母分组排序

最终要实现效果,如下: "mysql查询语句" SELECT t1.id, t1.name, t2.f_PY FROM table_name t1, table_coslers t2 WHERE CONV(HEX(LEFT(CONV...

阅读全文

欢迎留言