mysql的limit分页优化

推荐关注

顶级架构师后台回复 1024 有特别礼包

作者：sunpy

来源：https://www.jianshu.com/p/c5fb2dadbfe4

上一篇：太卷了， 某公司把自家运营多年的核心系统（智慧系统）完全开源了....

大家好，我是顶级架构师。

准备工作

---

# 总记录数为500000
mysql> select count(id) from edu_test;
+-----------+
| count(id) |
+-----------+
|    500000 |
+-----------+
1 row in set (0.05 sec)

分析过程

从0开始查询10条：

mysql> select * from edu_test limit 0, 10;

10 rows in set (0.05 sec)

从20万开始查询10条：

mysql> select * from edu_test limit 200000, 10;

10 rows in set (0.14 sec)

从50万开始查询10条：

mysql> select * from edu_test limit 499000, 10;

10 rows in set (0.21 sec)

• 现象：随着分页越深入，查询的时间也越来越长。

mysql> explain select * from edu_test limit 200000, 10;
+----+-------------+----------+------------+------+---------------+------+---------+------+--------+----------+-------+
| id | select_type | table    | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra |
+----+-------------+----------+------------+------+---------------+------+---------+------+--------+----------+-------+
|  1 | SIMPLE      | edu_test | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 499483 |   100.00 | NULL  |
+----+-------------+----------+------------+------+---------------+------+---------+------+--------+----------+-------+
1 row in set (0.09 sec)

• 思考：limit分页做了一个全表扫描，扫描后将从200000开始往后取10条记录返回。

优化

---

思路：

• 快速定位到要访问的数据行，缩小扫描范围。
  方案1

• 延迟查询（先定位再查询）.
  方案2、方案3

方案1：通过有序唯一索引缩小扫描范围
前提必须要id有序，要不然结果会漏掉一部分数据的。

mysql> select * from edu_test where id > 499000 order by id asc limit 10;

10 rows in set (0.14 sec)

mysql> explain select * from edu_test where id > 499000 order by id asc limit 10;
+----+-------------+----------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table    | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+----------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | edu_test | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL | 1000 |   100.00 | Using where |
+----+-------------+----------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set (0.16 sec)
# 再缩小扫描范围
mysql> select * from edu_test where id between 499000 and 499020 order by id asc limit 10;

10 rows in set (0.09 sec)

mysql> explain select * from edu_test where id between 499000 and 499020 order by id asc limit 10;
+----+-------------+----------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table    | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+----------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | edu_test | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL |   21 |   100.00 | Using where |
+----+-------------+----------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set (0.08 sec)

方案2：子查询

mysql> SELECT * FROM edu_test WHERE id >=  (SELECT id FROM edu_test ORDER BY id LIMIT 499000, 1) LIMIT 10;

10 rows in set (0.16 sec)

mysql> explain SELECT * FROM edu_test WHERE id >=  (SELECT id FROM edu_test ORDER BY id LIMIT 499000, 1) LIMIT 10;
+----+-------------+----------+------------+-------+---------------+---------+---------+------+--------+----------+-------------+
| id | select_type | table    | partitions | type  | possible_keys | key     | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+----------+------------+-------+---------------+---------+---------+------+--------+----------+-------------+
|  1 | PRIMARY     | edu_test | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL |   1000 |   100.00 | Using where |
|  2 | SUBQUERY    | edu_test | NULL       | index | NULL          | PRIMARY | 4       | NULL | 499001 |   100.00 | Using index |
+----+-------------+----------+------------+-------+---------------+---------+---------+------+--------+----------+-------------+
2 rows in set (0.14 sec)

方案3：join查询

mysql> select * from edu_test s, (select id from edu_test order by id limit 499000, 10) t where s.id = t.id;

10 rows in set (0.16 sec)

mysql> explain select * from edu_test s, (select id from edu_test order by id limit 499000, 10) t where s.id = t.id;
+----+-------------+------------+------------+--------+---------------+---------+---------+------+--------+----------+-------------+
| id | select_type | table      | partitions | type   | possible_keys | key     | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+------------+------------+--------+---------------+---------+---------+------+--------+----------+-------------+
|  1 | PRIMARY     | <derived2> | NULL       | ALL    | NULL          | NULL    | NULL    | NULL | 499010 |   100.00 | NULL        |
|  1 | PRIMARY     | s          | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | t.id |      1 |   100.00 | NULL        |
|  2 | DERIVED     | edu_test   | NULL       | index  | NULL          | PRIMARY | 4       | NULL | 499010 |   100.00 | Using index |
+----+-------------+------------+------------+--------+---------------+---------+---------+------+--------+----------+-------------+
3 rows in set (0.10 sec)

实际业务场景

---

• 场景：
  在我们设计数据库id的时候，可能采用字符串格式、有顺序的id，带有一定的业务逻辑这样的分布式id。

• 解决：
  如果我们分页想要优化时候，根据减少扫描思路，可以通过where id like '10289%' 方式，先缩小范围再分页。

• 启示：
  在设计数据库id主键的时候，尽量保持主键唯一且有序，最好能解决热点业务问题（如果依赖很多的非主键值，那么我们可能还需要回表操作），而且主键本身就是一种唯一索引，这种唯一有序特性可以便于帮助我们后期优化，减少扫描记录范围。

欢迎大家进行观点的探讨和碰撞，各抒己见。如果你有疑问，也可以找我沟通和交流。扩展：接私活儿

最后给读者整理了一份BAT大厂面试真题，需要的可扫码回复“面试题”即可获取。

公众号后台回复 架构 或者 架构整洁 有惊喜礼包！顶级架构师交流群

 「顶级架构师」建立了读者架构师交流群，大家可以添加小编微信进行加群。欢迎有想法、乐于分享的朋友们一起交流学习。

扫描添加好友邀你进架构师群，加我时注明【姓名+公司+职位】

版权申明：内容来源网络，版权归原作者所有。如有侵权烦请告知，我们会立即删除并表示歉意。谢谢。

猜你还想看