背景介绍
有个数字化转型项目采用Mysql数据库，表中大概有两千万数据，做深度分页后查询比较慢，需要优化
需求分析
对于B端项目，需要查询全量数量，查询条件包括起止日期，没有采用水平分表方案，如根据用户id水平分表，根据时间水平分表等 采用es、Hive+Impala,ClichHouse等OLAP方案需要引入其它技术栈，开发资源，进度等无法满足要求
讨论主题
-
分析大表深度分页为什么这么慢
-
如何优化
-
优化原理
准备表结构
# MYSQL 5.7.42
CREATE TABLE person (
`id` bigint(20) NOT NULL,
`name` varchar(50) DEFAULT NULL,
`sex` varchar(10) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
`email` varchar(100) DEFAULT NULL,
`tel` varchar(50) DEFAULT NULL,
`address` varchar(100) DEFAULT NULL,
`ccreate_time` datetime DEFAULT NULL,
`update_time` datetime DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
默认查询
SELECT * FROM person limit 100 # 7ms
问题复现
实际业务场景中一般需要排序，以创建时间逆序为例，耗时7秒
SELECT * FROM person p order by p.create_time desc limit 100
增加创建时间逆序索引后，再执行一次耗时为3ms
ALERT TABLE person ADD INDEX idx_create_time(create_time desc) # 3ms
如果从第100万行获取100行，耗时明显增加
在100万行获取100行，耗时15s
SELECT * FROM person p ORDER BY p.create_time desc limit 1000000,100 # 15s
在1000万行获取100行，耗时80s
SELECT * FROM person p ORDER BY p.create_time desc limit 1000000,100 # 80s
一般我们查询数据的最大时间容忍度是3s内
分析原因
查看深度分页执行计划，显示没有使用到索引
EXPLAIN SELECT * FROM person p ORDER BY p.create_time desc limit 1000000,100
TYPE: ALL 全表扫描
KEY: NULL 没有使用索引，但是索引在少量查询的情况下是可以使用的
limit m, n 查询过程是先回表查询m+n条记录，然后丢掉前面m条，取后面n条返回
目前表中默认有一个主键id组成的聚集索引，所有的分支节点存储id,叶子节点存储数据字段
create_time二级索引：时间作为分支节点，叶子节点存储表的主键
create_time二级B+tree索引保留主键id,如果使用索引则返回m+n条id,再通过聚集索引B+tree根据id查询数据，次过程称之为回表
当m+n数量非常大时，回表的查询两也很大，Mysql查询优化器认为全表扫描的性能优于使用索引
如我们根据二级索引返回了100万个id,然后返回聚集索引查询id，此时回表数据量巨大
我们优化的方案也是针对二级索引进行优化，如何让二级索引尽可能小地返回数据量，从而提高查询效率
解决方案1 子查询
通过子查询优化，100万行100条耗时242ms,提升61倍 通过子查询优化，1000万行100条耗时242ms,提升61倍
SELECT p.*
FROM person p
WHERE p.create_time <= (
SELECT create_time FROM person t ORDER BY t.create_time DESC
LIMIT 1000000, 1
)
ORDER BY p.create_time DESC LIMIT 100
通过create_time索引B+tree得到create_time偏移量，此时不需要回表; 再一次通过create_time索引B+tree计算得到100个id,然后回表读取记录，从而有效减少回表记录数，提高查询效率
分析
该过程实际是执行了两次，第一次是子查询，通过子查询查询最大时间，这个过程是通过二级索引来的，是不需要回表的，因为返回的时间就是节点数据
p.create_time <=查询出来的时间，实际走的还是create_time二级索引,该过程依然不需要回表， 然后获取100条，这个过程是需要回表的。再去聚集索引去查全部数据
索引使用了两次，性能提升几十倍
EXPLAIN SELECT p.*
FROM person p
WHERE p.create_time <= (
SELECT create_time FROM person t ORDER BY t.create_time DESC
LIMIT 1000000, 1
)
ORDER BY p.create_time DESC LIMIT 100
1
select_type PRIMARY
table p
type range
possible_keys idx_create_time
key idx_create_time
key_len 6
extra using where
2
select_type SUBQUERY
table t
type index
key idx_create_time
key_len 6
extra using index
解决方案2 inner join
将表按照create_time进行子查询，并返回id通过id进行关联，返回数据
查询100万条数据，236ms 查询1000万条数据，2.363s
SELECT p.*
FROM person p,
(SELECT id FROM person t ORDER BY t.create_time DESC LIMIT 1000000, 100) p2
WHERE p.id = p2.id
ORDER BY p.create_time DESC
t表使用到了id,create_time两个字段，都存储在create_time索引B+tree中了，不需要回表; 通过t表临时与p表关联过滤得到100条记录，回表读取100条记录，有效减少回表记录数
EXPLAIN SELECT p.*
FROM person p,
(SELECT id FROM person t ORDER BY t.create_time DESC LIMIT 1000000, 100) p2
WHERE p.id = p2.id
ORDER BY p.create_time DESC
1
select_type PRIMARY
table <derived2>
type ALL
possible_keys NULL
key NULL
key_len NULL
extra using temproary,using filesort
2
select_type PRIMARY
table p
type eq_ref
possible_keys PRIMARY
key PRIMARY
key_len 6
extra NULL
3
select_type DERIVED
table t
type index
possible_keys NULL
key idx_create_time
key_len 6
extra using index
解决方案3
每次保存上一次最小的create_time, 第一次查询比上一页create_time小一页的数据 查询第100万行耗时18ms，比80s快4000多倍
SELECT * FROM person p
WHERE p.create_time < '2023-01-09 17:21:03'
ORDER BY p.create_time DESC LIMIT 10
分析
SELECT * FROM person p
WHERE p.create_time < '2023-01-09 17:21:03'
ORDER BY p.create_time DESC LIMIT 10
1.
select_type: SIMPLE
table p
type range
possible_keys idx_create_time
key idx_create_time
rows 9907503
extra using index condition
缺点：只能连续分页，移动端常用，web B端使用较少，需要客户认可才可以
总结
深度分页解决方案
-
需求层面，如果允许最多查询xx页，则避免了深度分页问题
-
翻页体验层面，如果可以保留上一页，下一页，每次传参上一页的最大最小值，可使用方案能3
-
通过子查询，inner join减少回表次数，提高查询效率
-
水平分表
-
ES,HIVE+IMPALA,CLICKHOUSE等OLAP方案
