知识点整理（五）——mysql分页问题

前言

mysql在大数据量的情况下使用limit分页，会随着页码增大而查询效率低下。该怎么优化呢？

先看是不是，再看为什么，最后看怎么办

准备

mysql

本次操作使用mysql8

表

为了验证大数据量，我们先准备一张5000万条记录的表。

1
2
3
4
5
6
7
8
9
10
11
CREATE TABLE `demo_5000` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `a` int NOT NULL,
  `b` int NOT NULL,
  `c` int NOT NULL,
  `d` varchar(45) NOT NULL,
  `f` varchar(45) NOT NULL,
  `g` varchar(45) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_combo` (`a`,`b`,`c`)
) ENGINE=InnoDB AUTO_INCREMENT=50010007 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

一张简单的表，主键自增，3个int字段和3个varchar字段，并建立一个联合索引。

sql语句

1
set profiling = 1;

临时开启性能分析。

1
show profile CPU,SWAPS,BLOCK IO,MEMORY,SOURCE for query [querID]

查看性能。

explain （查看执行计划） 和 SQL_NO_CACHE （跳过缓存）

limit offset,size 分页

根据a字段降序，进行分页查询

  • 查询1万左右的
1
select SQL_NO_CACHE * from demo_5000  order by id desc  limit 10000,100;

使用 show profile 查看性能。

  • 查询10万左右的
1
select SQL_NO_CACHE * from demo_5000  order by id desc  limit 100000,100;

  • 查询100万左右的
1
select SQL_NO_CACHE * from demo_5000  order by id desc  limit 1000000,100;

到这里我们其实就可以看出结果了，随着页码变大，效率会急剧下降。虽然每一次查询只取100条数据，但查询前1万和前10万左右的记录时间是差不多的，但到100万时性能就相差非常大。（当然这和表结构有关）

为什么？

那为什么会这样呢？我们可以使用执行计划来看一下。

1
2
3
explain select * from demo_5000  order by a desc  limit 10000,100;
explain select * from demo_5000  order by a desc  limit 100000,100;
explain select * from demo_5000  order by a desc  limit 1000000,100;



可以看到实际上mysql会扫描offset数量，然后再取100条。尤其是第三条语句，mysql甚至认为预计需要扫描4000w条以至于直接放弃使用索引了。

这就是为什么页码越大，性能越差的原因。

怎么优化

先查询id，然后通过id来查询其余的数据。

1
select SQL_NO_CACHE id from demo_5000  order by a desc  limit 1000000,100;

再看看执行计划

可以看到，如果只是查询主键id，那么mysql不会回表查询，而且会使用到索引，这样效率会非常高。即使查询id的时候依然会扫描100万条索引，但扫描索引比扫描数据要高效的多。

利用上一页的查询结果

在有些场景下，既然查询到第1万页了，那也应该查询过第9999页。我们可以利用上一页的最大id，或者排序字段来作为where条件，这样就能避免offset太大了。

1
select SQL_NO_CACHE id from demo_5000  where a<=4900450   order by a desc  limit 0,100;

这样查询效率是最高的，并且扫描行数也是最少的。