详谈 | 将一个千万级多表关联SQL执行时间从几十秒降到毫秒生产案例

yumo6661个月前 (03-23)技术文章21

前言

写在前面的话,不论再小的业务系统,随着上线时间的推移,数据也会随之日渐增长,对各个业务场景的架构设计,时刻保持敬畏之心。尤其是在遇到 SQL 慢查询这类问题,不要“无脑”的看待它,要学会分析根因,从某个接口响应耗时,导致页面直接挂掉,要看其后台具体产生此现象的根本原因!



场景

基于数据存储的业务类型-OLTP,OLAP:

1、其中一种是企业知识库,权限系统,数据由本系统产生,数据量不是很大,但是数据增删改较多;

2、其另一种是统计分析类型,数据不由本系统产生,来自各业务生产系统,数据集规模极其庞大,并且数据查询较多。



现状

数据每天在源源不断产生,音视频,影像图片,文本......

1、海量数据存储出现瓶颈,单台机器无法负载大规模数据集;

2、单台机器 IO 读写请求,成为海量数据存储时高并发-大规模请求的瓶颈。


生产接入&成功案例

这里,着重列举俩个指标:

其一,数据集规模-千万级

其二,查询检索时效-毫秒级


接下来,以 DBA 与 架构 的视角,具体看看整个生产过程:

时间轴实录





DBA


在某个似乎平静的夜晚,通过监控得知某个接口响应超时极其严重,监测到数据库出现慢 SQL...

架构


查询对应 SQL 的执行计划,并对当前 SQL 的 where 条件中的字段选择性-进行分析...




DBA


在导入数据集之前的很长一段时间都是正常的,索引失效了?

架构


根据数据集的规模分析,考虑到现有业务既有跨时间区间检索的分页需求,还有对多个属性字段进行模糊检索的诉求,研发童鞋写的原始业务逻辑显然满足不了当下的数据量需求了,暂时对部分字段像按 name 查询的增加了普通索引,并沟通产品展示默认最近半个月的数据,模糊匹配效果经沟通也可改造为 like '测试%'。




DBA


在经过短时间内的快速定位及处理,发现页面展示数据至少能够正常显示了,点了出自一名DBA的

架构


箭在弦上,刻不容缓...

随着数据集持续增长,肯定需要采取相应的应急策略才行。



...... ...... ...... after a period of time ...... ...... ......



DBA


根据 SQL 执行计划,除了时间区间字段之外,选择性的确都不高...

架构


随着检索数据越来越多,这时优化器认为索引的选择性已经不高了,反而走全表扫描更快,当然也可强制索引 force index...


同时,根据业务 SQL 发现,通过测试-查询数据越往后翻,检索效率越低,立即作出了如下改造:


对时间区间检索再次优化,使其走以id字段为主键的聚簇索引(无需回表),其他类似 name 属性字段为非聚簇索引。当然,我们需要根据实际场景而作出应对,比如查询名称字段和其他字段,也可以创建联合索引(索引存储的内容即为需要查询的内容-覆盖索引)。

id>=(select max(id) from table where create_time > 'yyyy-MM-dd HH:mm:ss')


同时,对深度分页按采用自增ID方式解决:





...... ...... ...... a few minutes later ...... ...... ......



DBA


接口响应耗时降下去了,降下去了,降下去了...

架构


但考虑到这里是通过多表关联,部分表的数据量并不大,而其中一张明细表规模在1300w 条左右,而产品侧需要的正是明细数据展示,当前提供给业务侧童鞋的应急策略确实可行,但长此以往,必须提供一个更为全面、妥善、且可接纳的设计方案才是真正解决这类问题。

考虑到当前业务情况,暂无需作分库分表。思索过后,恰巧联想到了索引+宽表模型。


将业务 SQL 出来的数据汇聚成宽表模型,将其 json 属性字段进行非关系型数据库异构,这样哪些需要分词的字段则设置为可分词,不需要分词的字段则设置为不可再分,解决在海量数据模糊查询再适合不过了,唯一需要保障的就是数据同步。


为此,自研了前面提到的接入方案,性能指标也得到了大家的认可,使得用户体验更上一个级别,得到了一致好评!


*温馨提示*

感兴趣的小伙伴,了解详情可关注分享+私信互相交流探讨><



梳理概括

为考虑到各个业务中实际的应对情况,做出了如下的优化&改造:

1、产品需求侧沟通;

2、业务研发侧SQL改造;

3、系统架构设计侧升级。



思考延伸

那如何从架构与DBA的视角来辨证地看待这件事情?这里,我们来深度考量一下,针对上文中出现的这个典型场景的层与次,主要从以下俩个视角来分析看待:



业务系统视角


在实际项目中,当数据量一上来,就发现索引其实是多么的重要,比如怎么排查视图中的表有没有建立索引,其中索引有没有失效,哪些索引没有利用上,又是如何失效的,耗时分析等,或许这能给我们在实际项目中作 SQL 优化,在整体思路上有着及其重要的启发!




系统技术视角


在传统DBMS关系型数据库表中,其数据可通过一系列方案处理(数据建模、视图、临时表、外部表等),而传统DBMS关系型数据库分库分表组合查询相当麻烦,并且在全文搜索查询方面需要具备强悍的性能,那么设计一款组合且灵活-自动路由(开发者无需在业务层作过多干涉),保留的历史数据能够按时间分片-可溯源,这的确是一件极其有意义且重要的事情!



引发猜想

当千万乃至更大数据量,需要像传统DBMS关系型数据库一样,实现在海量数据中作模糊搜索,全文搜索,然又需要有一定程度的检索效率,突破传统DBMS性能瓶颈,那么如何与关系型数据库形成互补?

相关文章

mariadb数据库给数据配置快捷键-视图和索引

1.视图1.1 什么是视图?视图是基于SQL查询结果的虚拟表。视图本身不存储数据,而是存储查询逻辑。通过视图,可以简化复杂查询、隐藏数据细节、提供数据安全性。场景:你家的电视有几百个频道,但每次你只想...

MyBatis实现一对一有几种方式?具体怎么操作的?

MyBatis 实现一对一关系主要有两种方式,这两种方式都依赖于 标签在 resultMap 中的配置。 核心区别在于如何获取关联对象的数据:1. 嵌套查询 (Nested Select/Lazy...