加载中...
知识点整理(五)——mysql分页问题
发表于:2021-09-09 | 分类: 程序人生
字数统计: 744 | 阅读时长: 3分钟 | 阅读量:

前言

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

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

准备

mysql

本次操作使用mysql8

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

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语句

set profiling = 1;

临时开启性能分析。

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

查看性能。

explain (查看执行计划) 和 SQL_NO_CACHE (跳过缓存)

limit offset,size 分页

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

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

使用 show profile 查看性能。

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

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

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

为什么?

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

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来查询其余的数据。

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

再看看执行计划

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

利用上一页的查询结果

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

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

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

上一篇:
知识点整理(六)——图解Raft协议
下一篇:
知识点整理(四)——dubbo中支持的RPC协议
本文目录
本文目录