Mysql 分页优化

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

mysql开启表锁备份数据

1、全局读锁定: FLUSH TABLES WITH READ LOCK ; 执行了命令之后所有库所有表都被锁定只读,一般用在数据库联机备份,这个时候数据库的写操作将被阻塞,读操...

阅读全文

Mysql事务的四种隔离级别(转)

转载地址(click me) 数据库事务的隔离级别有4种,由低到高分别为Read uncommitted 、Read committed 、Repeatable read 、Serializable 。而且,在事务的并...

阅读全文

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...

阅读全文

欢迎留言