进阶[数据库] Mysql千万级大表深分页优化方案
背景介绍
有个数字化转型项目采用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方案
这篇好文章是转载于:学新通技术网
- 版权申明: 本站部分内容来自互联网,仅供学习及演示用,请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系,请提供相关证据及您的身份证明,我们将在收到邮件后48小时内删除。
- 本站站名: 学新通技术网
- 本文地址: /boutique/detail/tanhgbacfh
-
photoshop保存的图片太大微信发不了怎么办
PHP中文网 06-15 -
Android 11 保存文件到外部存储,并分享文件
Luke 10-12 -
word里面弄一个表格后上面的标题会跑到下面怎么办
PHP中文网 06-20 -
《学习通》视频自动暂停处理方法
HelloWorld317 07-05 -
photoshop扩展功能面板显示灰色怎么办
PHP中文网 06-14 -
微信公众号没有声音提示怎么办
PHP中文网 03-31 -
excel下划线不显示怎么办
PHP中文网 06-23 -
excel打印预览压线压字怎么办
PHP中文网 06-22 -
怎样阻止微信小程序自动打开
PHP中文网 06-13 -
TikTok加速器哪个好免费的TK加速器推荐
TK小达人 10-01