【MySQL性能优化系列】百万数据limit分页优化

背景

众所周知，在使用limit分页过程中，随着前端传过来的PageSize越来越大，查询速度会越来越慢。那有什么优化的办法呢？
本文将通过百万数据表进行演示和优化， 欲知详情，请看下文分解。

limit简介

语法：

select column1,column2 from table [where Clause] [limit N][offset M]
1

LIMIT 接受一个或两个数字参数。参数必须是一个整数常量。
如果给定两个参数，第一个参数指定第一个返回记录行的偏移量，第二个参数指定返回记录行的最大数目。
初始记录行的偏移量是 0(而不是 1)： 为了与 PostgreSQL 兼容，MySQL 也支持句法： LIMIT # OFFSET #。

用法示例：

// 检索记录行 6-666
SELECT * FROM table LIMIT 5,666; 
1
2

//检索前6个记录行
SELECT * FROM table LIMIT 6; 
LIMIT n 等价于 LIMIT 0,n。
1
2
3

百万数据表测试

mysql> select count(*) from test;
+----------+
| count(*) |
+----------+
|  1306725 |
+----------+
1 row in set (0.19 sec)
1
2
3
4
5
6
7

mysql> 
select * from test limit 1000, 10; 
select * from test limit 10000, 10; 
select * from test limit 100000, 10; 
select * from test limit 1000000,10;
1
2
3
4
5

耗时如下：

查询偏移量1000： 0.032s

查询偏移量10000： 0.146s

查询偏移量100000： 0.219s

查询偏移量1000000：11.463s

优化

注:以下优化是建立在已添加id的二级索引。相关参考如下：

【MySQL性能优化系列】select count(*)走二级索引比主键索引快几百倍，你敢信？

子查询的分页优化](http://t.csdn.cn/x3Wwf)

SELECT * FROM test WHERE  id in
(select t.id from ( SELECT id FROM test LIMIT 1000000, 10) as t);

//耗时: 21.803s
1
2
3
4

为什么比显示输入id还慢呢 ?查看一波执行计划。

使用到了物化子查询：不直接将不相关子查询的结果集当作外层查询的参数，而是将该结果集写入一个临时表里 (称为物化)。 MySQL 从外到内执行查询，认为子查询执行一次，然后将其结果传递给外部查询的 WHERE 表达式。但实际循环了很多次。
MySQL引入了Materialization(物化)这一关键特性用于子查询(比如在IN/NOT IN子查询以及 FROM 子查询)优化。实现方式是：在SQL执行过程中，第一次需要子查询结果时执行子查询并将子查询的结果保存为临时表 ，后续对子查询结果集的访问将直接通过临时表获得。物化子查询优化SQL执行的关键点在于对子查询只需要执行一次。与之相对的执行方式是对外表的每一行都对子查询进行调用，其执行计划中的查询类型为“DEPENDENT SUBQUERY”。

SELECT * FROM test WHERE  id >=
(SELECT id FROM test LIMIT 1000000, 1) LIMIT 10
//耗时：0.159s
1
2
3

SELECT * FROM test WHERE  id in (select id from ( SELECT id FROM test LIMIT 1000000, 10) t)
//耗时：0.162s
1
2

连接查询分页优化

隐式内连接

SELECT a.* FROM test  a, (select id from test  LIMIT 1000000,10 ) b where a.id=b.id
//在没有条件语句的情况下返回笛卡尔积（排列组合）
//时间: 0.156s
1
2
3

显式内连接

SELECT * FROM test AS a    
JOIN (SELECT id FROM test limit 1000000, 10) AS b on a.id=b.id
//时间: 0.166s
1
2
3

点赞 收藏 关注
踔厉奋发，勇毅前行