千万级数据深度分页如何优化

 需求描述

报表系统：涉及订单数据全量查询，千万级数据深度分页性能优化

其他应用场景：

1、查询文章列表，一直滑动翻页，不用跳转到指定页数（移动端瀑布流效果）。

2、从数据库查询百万客户数据写入到redis。

3、访问某小程序的积分商城查看商品，一直滑动翻页，不用跳转到指定页数。

4、ETL大数据量，数据拆分。

难点分析

设计客户表结构，插入1000万数据。

CREATE TABLE `ry-cloud`.`person`  (
  `id` bigint NOT NULL  AUTO_INCREMENT,
  `name` varchar(50) NULL,
  `sex` varchar(10) NULL,
  `age` int(11) NULL,
  `email` varchar(100) NULL,
  `tel` varchar(50) NULL,
  `address` varchar(100) NULL,
  `create_time` datetime NULL,
  `update_time` datetime NULL,
  PRIMARY KEY (`id`)
);

• 批量插入一千万数据

• 随机datetime

• -- 随机日期
  select DATE_FORMAT('1970-1-1' + INTERVAL FLOOR(RAND() * 10000) DAY, '%Y-%m-%d')
  
  -- 随机时间
  SELECT TIME_FORMAT(SEC_TO_TIME(FLOOR(RAND()* 86400)), '%H:%i:%s')
  
  -- 随机datetime
  select CONCAT(DATE_FORMAT('1970-1-1' + INTERVAL FLOOR(RAND() * 10000) DAY, '%Y-%m-%d'),' ',TIME_FORMAT(SEC_TO_TIME(FLOOR(RAND()* 86400)), '%H:%i:%s'))

  批量插入一千万数据

• delimiter $
  
  create procedure batchInsert(in args int)
  begin 
  declare i int default 1;
  start transaction;
  while i<=args do
  INSERT INTO person( `name`, `sex`, `age`, `email`, `tel`, `address`, `create_time`, `update_time`) VALUES (concat('刘德华',i), '男', 66, 'ldh@163.com', '12345678999', '北京海淀区1号', CONCAT(DATE_FORMAT('1970-1-1' + INTERVAL FLOOR(RAND() * 10000) DAY, '%Y-%m-%d'),' ',TIME_FORMAT(SEC_TO_TIME(FLOOR(RAND()* 86400)), '%H:%i:%s')), NOW());
  set i = i+1;
  end while;
  commit;
  end
  $
  
  call batchInsert(10000000);
  $
  
  执行结果：
  call batchInsert(10000000);
  > OK
  > 时间: 977.277s
  
  -- 查询插入数据量
  select count(*) from person

  深度分页测试 性能灾难 325S

• -- 深度分页测试，性能灾难   
  select * from person p order by p.create_time desc limit 10000000,100; -- 325S

  问题分析

• limit m，n查询过程是先回表查询m+n条记录，然后丢掉前m条，取后面n条结果返回

  解决方案

  避免大量回表

• 子查询
• 表连接inner join
• 使用标签记录

• 拓展方案

  1、需求端解决，比如淘宝，京东，搜索时最多100页，直接避免深度分页

  2、翻页体验层面，只保留上一页，下一页，有效使用游标解决任意深度性能稳定高效

  3、通过子查询，表连接有效减少回表次数

  4、水平分库分表

  5、采用ES、Hive+Impala、ClickHouse等OLAP方案需要引入其他技术栈

• 优化实施

• 1、正常测试

select * from person p limit 100;  -- 0.020S

实际业务场景中一般需要排序，以创建时间逆序为例

select * from person p order by p.create_time desc limit 100; --7.45s

增加创建时间逆序的索引后，再执行一次耗时36ms

alter table person add index idx_create_time (create_time desc)

• 2、深度分页，性能急剧下降

  select * from person p order by p.create_time desc limit （pageNo-1）*pageSize,pageSize

• -- 10001页，在第一百万行，取100条

  select * from person p order by p.create_time desc limit （10001-1）*100,100; -- 31S

  -- 在第一千万行，取100条

  select * from person p order by p.create_time desc limit 10000000,100; -- 325S

  limit m，n查询过程是先回表查询m+n条记录，然后丢掉前m条，取后面n条结果返回

• 3、优化一【子查】

-- 利用子查询过滤掉大量数据，有效避免大量数据的回表
select  p.* from person pwhere p.create_time<=(select t.create_time t from person t order by t.create_time desc limit 1000000,1)
order by p.create_time desc limit 100;   -- 534ms

• 4、优化二【表连接inner join】

  -- 利用主键查询，缩小需要回表的数据量
  select p1.*
  from person p1 , (select p2.id from person p2 order by p2.create_time desc limit 1000000,100) p2
  where p1.id = p2.id order by create_time desc

  5、优化三【使用标签记录优化】

-- 每次查询保存上次最小create_time，下次只查询比上页create_time小的一页数据查询第1000万行耗时38ms，提升4000多倍
select * from person where create_time<'1994-08-13 21:54:37' order by create_time desc limit 100  -- 38ms

性能最高，单表查询，索引命中，回表数据少

缺点：只能连续分页，特定场景下才可以使用，需要调用端代码改造，代码耦合度较大

双新调研

ClickHouse 是联机分析 (OLAP) 的列式数据库，适用于商业智能领域，在垂直和水平方向上都可以很好地缩放，在 1 亿数据集体量的情况下，ClickHouse 的平均响应速度是MySQL 的 429 倍