MySql-千万级数据 分页优化

在数据量比较大时，如果进行limit分页查询，在查询时，越往后，分页查询效率越低。

1.首先复习下limit用法：

LIMIT 起始索引, 查询记录数 ;

注意事项:

• 起始索引从0开始，起始索引 = （查询页码 - 1）* 每页显示记录数
• 分页查询是数据库的方言，不同的数据库有不同的实现，MySQL中是LIMIT。
• 如果查询的是第一页数据，起始索引可以省略，直接简写为 limit 10。

2.优化开始：

测试环境： MySQL 数据库5.7.23 服务器4核8G 带宽5M 3836000数据 为防止缓存影响，测试前已经将缓存关闭
服务器内存有限制，不能创建千万级的，百万级的也可以看出效果！

1. 先查看本次测试数据量：

mysql> select count(*) from user_operation_log;
+----------+
| count(*) |
+----------+
|  3836000 |
+----------+

2. 我们一起来看看执行limit分页查询耗时对比：

1）相同索引，不同数据量

SELECT * FROM `user_operation_log` LIMIT 100000, 10;
SELECT * FROM `user_operation_log` LIMIT 100000, 10000;
SELECT * FROM `user_operation_log` LIMIT 100000, 100000;

数量（条）	第一次（s）	第二次 （s）	第三次（s）
10	3.2	3.3	3.1
10000	3.8	3.4	3.9
100000	12.36	12.33	11.66

测试可知：相同索引，不同数据量，数据量越大花费时间越多

2）不相同索引，相同数据量

SELECT * FROM `user_operation_log` LIMIT 0, 10;
SELECT * FROM `user_operation_log` LIMIT 100000, 10;
SELECT * FROM `user_operation_log` LIMIT 2000000, 10;

数量（页）	第一次（s）	第二次 （s）	第三次（s）
0	0.024	0.021	0.025
10000	2.7	2.5	2.2
200000	93	92	93

测试可知：不相同索引，相同数据量，索引越大花费时间越多

3. 优化思路：

1）问题分析：

通过测试我们会看到，越往后，分页查询效率越低，这就是分页查询的问题所在。因为，当在进行分页查询时，如果执行 limit 200000, 10;，此时需要MySQL排序前200010 记录，仅仅返回 200000- 200010 的记录，其他记录丢弃，查询排序的代价非常大 。

2）解决方式：

• ID自增的情况：
  在主键自增的情况下MySql的数据结构有一颗主键B+树，如果单独查询一条数据即使在大数据量的情况下也可以非常快的查询到结果。 所以 LIMIT 2000000, 10;，我们可以先查询到2000000页的第一条数据并获取到对应的id，在此id的基础上获取10条，查询速度就会很快。
  上代码：

查询到2000000页的第一条数据并获取到对应的id：

mysql> SELECT id FROM `user_operation_log` LIMIT 2000000, 1 ;
+---------+
| id      |
+---------+
| 1000001 |
+---------+
1 row in set (0.38 sec)

在此id的基础上获取10条：

//第一种写法 耗时0.38秒
SELECT * FROM `user_operation_log` WHERE id >= (SELECT id FROM `user_operation_log` LIMIT 2000000, 1) LIMIT 10;
//第二种写法 耗时 0.39秒
SELECT a.* FROM `user_operation_log`  a, ((SELECT id FROM `user_operation_log` LIMIT 2000000, 10)) b where a.id=b.id;

那两种写法时间差不多，应该用那种写法呢？我们通过EXPLAIN分析一下：


通过分析可知：第一种写法更好

• id 采用的非递增

SELECT * FROM `user_operation_log` WHERE id IN (SELECT t.id FROM (SELECT id FROM `user_operation_log` LIMIT 2000000, 10) AS t)

第一次（s）	第二次 （s）	第三次（s）
2.56	2.62	1.87

这里强调一点，数据库主键建议设置为自增id，对查询速度有帮助。SQL查询中同样不建议使用in

3）select 不同字段对比

SELECT * FROM `user_operation_log` LIMIT 2000000, 10 ;
SELECT id FROM `user_operation_log` LIMIT 2000000, 10 ;
SELECT id, user_id, ip, op_data, attr1, attr2, attr3, attr4, attr5, attr6, attr7, attr8, attr9, attr10, attr11, attr12 FROM `user_operation_log` LIMIT 2000000, 10 ;

SQL	耗时（秒）	字段
第一条	90+	全部
第二条	0.4	主键
第三条	120+	部分

可以看出，不同字段耗时也是不一样。查询的时候建议使用覆盖索引。

4.总结：

• 分页查询每页的数据量越大查询效果越差，所以分页的时候应该控制每页的数据量
• 查询的时候建议使用覆盖索引，非必要字段不返回同样有利于查询速度提升，在写SQL的时候要合理利用索引去优化