破解LIMIT和OFFSET分页性能瓶颈

06 数据库 评论

1. 分页方法分类

  1. LIMIT X 
    1
    2
    -- LIMIT X 表示: 读取 X 条数据
    select * from user limit 20
  2. LIMIT Y OFFSET X 
    1
    2
    3
    -- LIMIT Y OFFSET X 表示: 跳过 X 条数据，读取 Y 条数据
    select * from user limit 20 OFFSET 10
    -- 从第10+1 行开始读取20条数据
  3. LIMIT X, Y 
    1
    2
    -- 跳过 X 条数据，读取 Y 条数据
    select * from user limit 20 , 10

对于简单的小型应用程序和数据量不是很大的场景，这种方式还是没有问题的，但是一旦数据量过大，这种分页方式存在瓶颈。

2. LIMIT和OFFSET 的问题

OFFSET 和 LIMIT 对于数据量少的项目来说是没有问题的，但是，当数据库里的数据量超过服务器内存能够存储的能力，并且需要对所有数据进行分页，问题就会出现，为了实现分页，每次收到分页请求时，数据库都需要进行低效的全表遍历

全表遍历就是一个全表扫描的过程，就是根据双向链表把磁盘上的数据页加载到磁盘的缓存页里去，然后在缓存页内部查找那条数据，这个过程是非常满的，所以说当数据量大的时候，全表遍历的性能非常低，时间特别长，应该尽量避免全表遍历

为了获取一页的数据：10万行中的第50000行到第50020行需要先获取 5 万行，这么做非常低效！

3. 初探LIMIT查询效率

3.1 建表

测试数据库采用的是（存储引擎采用InnoDB）

表结构如下：

1
2
3
4
5
6
CREATE TABLE `user` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(100) DEFAULT NULL,
  `age` int DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

3.2 插入数据

1
2
3
4
5
6
7
8
9
10
11
12
13
-- 创建存储过程， 参数param1 为int 类型
CREATE DEFINER=`root`@`localhost` PROCEDURE `insert_limit_test`(param1 int)
BEGIN
 -- for循环遍历 插入 350万条数据
    WHILE param1 < 3500000 DO
          -- 插入表数据
            INSERT INTO `user` ( `name`, `age` ) VALUES (CONCAT('name_',param1)  , (param1 % 4)+10 );
          SET param1 = param1 + 1;
    END WHILE;
END;

-- 调用存储过程
CALL insert_limit_test(1);
1
2
3
4
5
6
7
mysql> select count(*) from user;
+----------+
| count(*) |
+----------+
|  3499999 |
+----------+
1 row in set (0.11 sec)

3.3 开始测试

首先偏移量设置为0，取20条数据(中间输出省略)

1
2
3
4
5
6
7
8
9
10
11
mysql> select * from user limit 0,20;
+----+---------+------+
| id | name    | age  |
+----+---------+------+
|  1 | name_1  |   11 |
#...中间输出省略
| 18 | name_18 |   12 |
| 19 | name_19 |   13 |
| 20 | name_20 |   10 |
+----+---------+------+
20 rows in set (0.00 sec)

可以看到查询时间基本忽略不计，于是我们要一步一步的加大这个偏移量然后进行测试，先将偏移量改为10000(中间输出省略)：

1
2
3
4
5
6
7
8
9
10
11
12
mysql> select * from user limit 10000,20;
+-------+------------+------+
| id    | name       | age  |
+-------+------------+------+
| 10001 | name_10001 |   11 |
| 10002 | name_10002 |   12 |
 #...中间输出省略
| 10018 | name_10018 |   12 |
| 10019 | name_10019 |   13 |
| 10020 | name_10020 |   10 |
+-------+------------+------+
20 rows in set (0.00 sec)

可以看到查询时间还是非常短的，几乎可以忽略不计，于是我们将偏移量直接上到340W(中间输出省略)：

1
2
3
4
5
6
7
8
9
10
11
mysql> select * from user limit 3400000,20;
+---------+--------------+------+
| id      | name         | age  |
+---------+--------------+------+
| 3400001 | name_3400001 |   11 |
#...中间输出省略
| 3400018 | name_3400018 |   12 |
| 3400019 | name_3400019 |   13 |
| 3400020 | name_3400020 |   10 |
+---------+--------------+------+
20 rows in set (0.48 sec)

这个时候就可以看到非常明显的变化了，查询时间增到了0.48s。

3.4 分析原因

根据下面的结果可以看到三条查询语句都进行了全表扫描：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
mysql> explain select * from user limit 0,20;
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows    | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------+
|  1 | SIMPLE      | user  | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 3493299 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from user limit 10000, 20;
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows    | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------+
|  1 | SIMPLE      | user  | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 3493299 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------+
1 row in set, 1 warning (0.01 sec)

mysql> explain select * from user limit 3400000, 20;
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows    | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------+
|  1 | SIMPLE      | user  | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 3493299 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------+
1 row in set, 1 warning (0.00 sec)

此时就可以知道的是，在偏移量非常大的时候，就像案例中的LIMIT 3400000,20这样的查询。
此时MySQL就需要查询3400020行数据，然后在返回最后20条数据。
前边查询的340W数据都将被抛弃，这样的执行结果可不是我们想要的。
接下来就是优化大偏移量的性能问题

4. 优化

1
SELECT * FROM user WHERE id>10 limit 20

这是一种基于指针的分页。你要在本地保存上一次接收到的主键 (通常是一个 ID) 和 LIMIT，而不是 OFFSET 和 LIMIT，那么每一次的查询可能都与此类似。
为什么？因为通过显式告知数据库最新行，数据库就确切地知道从哪里开始搜索（基于有效的索引），而不需要考虑目标范围之外的记录。
我们再来一次测试(中间输出省略)：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
mysql> select * from user  where id > 3400000 limit 20;
+---------+--------------+------+
| id      | name         | age  |
+---------+--------------+------+
| 3400001 | name_3400001 |   11 |
#....中间输出省略
| 3400019 | name_3400019 |   13 |
| 3400020 | name_3400020 |   10 |
+---------+--------------+------+
20 rows in set (0.00 sec)

mysql> EXPLAIN SELECT * FROM user WHERE id>3400000 LIMIT 20;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | user  | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL | 198326 |   100.00 | Using where |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+--------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

返回同样的结果，第一个查询使用了0.48 sec，而第二个仅用了0.00 sec。

注意：如果我们的表没有主键，比如是具有多对多关系的表，那么就使用传统的 OFFSET/LIMIT 方式，只是这样做存在潜在的慢查询问题。所以建议在需要分页的表中使用自动递增的主键，即使只是为了分页。

继续优化

类似于查询 SELECT * FROM table_name WHERE id > 3400000 LIMIT 20; 这样的效率非常快,因为主键上是有索引的,但是这样有个缺点,就是ID必须是连续的,并且查询不能有WHERE语句,因为WHERE语句会造成过滤数据。那使用场景就非常的局限了，于是我们可以这样

使用覆盖索引优化

mysql的查询完全命中索引的时候，称为覆盖索引，是非常快的，因为查询只需要在索引上进行查找，之后就可以直接返回，而不用再回数据表那数据，因此我们可以先查处索引的ID，然后根据ID取数据

1
2
3
4
5
6
7
8
9
10
11
12
-- user 为表名
SELECT * FROM (SELECT id FROM user LIMIT 3400000,20) a LEFT JOIN user b ON a.id = b.id;

mysql> explain SELECT * FROM (SELECT id FROM user LIMIT 3400000,20) a LEFT JOIN user b ON a.id = b.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 | 3400020 |   100.00 | NULL        |
|  1 | PRIMARY     | b          | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | a.id |       1 |   100.00 | NULL        |
|  2 | DERIVED     | user       | NULL       | index  | NULL          | PRIMARY | 4       | NULL | 3493299 |   100.00 | Using index |
+----+-------------+------------+------------+--------+---------------+---------+---------+------+---------+----------+-------------+
3 rows in set, 1 warning (0.00 sec)

或者是

1
2
3
4
5
6
7
8
9
10
11
SELECT * FROM user a INNER JOIN (SELECT id FROM user LIMIT 3400000,20) b USING (id);

mysql> explain SELECT * FROM user a INNER JOIN (SELECT id FROM user LIMIT 3400000,20) b USING (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 | 3400020 |   100.00 | NULL        |
|  1 | PRIMARY     | a          | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | b.id |       1 |   100.00 | NULL        |
|  2 | DERIVED     | user       | NULL       | index  | NULL          | PRIMARY | 4       | NULL | 3493299 |   100.00 | Using index |
+----+-------------+------------+------------+--------+---------------+---------+---------+------+---------+----------+-------------+
3 rows in set, 1 warning (0.00 sec)

5. 总结

  1. 数据量大的时候不能使用OFFSET/LIMIT来进行分页，因为OFFSET越大，查询时间越久。
  2. 当然不能说所有的分页都不可以，如果你的数据就那么几千、几万条，那就很无所谓，随便使用。
  3. 如果我们的表没有主键，比如是具有多对多关系的表，那么就使用传统的 OFFSET/LIMIT 方式。
  4. 这种方法适用于要求ID为数值类型，并且查出的数据ID连续的场景且不能有其他字段的排序。

小余哥xiaoyouge

分享一些技术教程与实践，以及日常踩坑记录。