-
解决方案|数据库查询时间过长
2020-05-19 13:06:55解决方案|数据库查询时间过长前言分析解决 前言 一次线上故障,数据库查询时间过长,而前端设置了超时时间,结果不仅该服务的访问受到了影响,其他服务的访问的流畅度也下降了。 分析 查询语句并不复杂,只涉及单...
前言
一次线上故障,数据库查询时间过长,导致前端页面频频报错,结果不仅该服务的访问受到了影响,其他服务的访问的流畅度也下降了。
分析
- 查询语句并不复杂,只涉及单表查询
- 查询已经设置了分页,也有加索引
- 查看该表的数据量,已经有两千万
解决
阶段一
看到数据量已经有两千万,是不是有人觉得我立刻就会讲分表、分区等操作。哈哈,当然不是了,线上问题当然应该尽快解决。
为了保证其他服务正常执行,并结合该服务的特点(访问量不会太多),直接设置该服务的最大查询时间,查询时间超过限制,则把错误日志打印出来,然后返回,保证其他服务的可用性。
设置的最大查询时间应该只局限于该服务,即粒度要小,别影响到其他服务。所以采用如下方式:
Future<List<Repor>> result = asyncService.get(reportDetailedsExample); List<ReportDetailed> reportDetailed = null; try { reportDetailed = result.get(5, TimeUnit.SECONDS); } catch (Exception e) { throw new BusinessException("慢查询异常"); } if (reportDetailed == null || reportDetailed.isEmpty()) { return null; } // ...
采用Future的形式,异步获取结果,get方法设置等待时间为5秒。异步服务类AsyncService ,采用AsyncResult包裹查询结果。
/*** * * @Author:fsn * @Date: 2020/4/16 17:14 * @Description */ @Service public class AsyncService { @Autowired private ReportDetailedsMapper reportDetailedsMapper; @Async public Future<List<ReportDetaileds>> get(ReportDetailedsExample reportDetailedsExample) { return new AsyncResult<>(reportDetailedsMapper.selectByExampleWithBLOBs( reportDetailedsExample)); } }
阶段二
分表or分区,最终方案是分区。先进行一波操作,再说说缘由~
这里采用比较常见的RANGE分区,注意!!!分区键(这里是date)必须是主键的一部分!
ALTER TABLE report_detaileds_copy1 PARTITION BY RANGE (YEAR(`date`)) ( PARTITION p2019 VALUES less than (2019), PARTITION p2020 VALUES less than (2020) );
上述这种方式需要服务访问量比较低的情况下才做比较好,一般来说,可以再新建一张表,然后分区,再导数据到新表(导数据的时候千万小心!!!特别是表的更新、插入都很频繁的时候,还得注意是否有走索引(不是说加了索引就一定会走索引),避免锁整张表的情况发生)。
CREATE TABLE `report_detaileds_2020` ( // 此次省略一大波字段 PRIMARY KEY (`id`, `date`) USING BTREE, KEY `rule_id` (`rule_id`) USING BTREE ) PARTITION BY RANGE (YEAR(`date`))( PARTITION p2019 VALUES less than (2019), PARTITION p2020 VALUES less than (2020) );
为什么我这里采用分区呢?(1)由于业务特点,显示的信息是根据时间显示的;(2)这些信息不会全部对用户公开,只显示了一段时间内的数据;(3)对于一张大数据量的表进行分表工作量还是挺大的,还得涉及代码层面的修改,而采用merge的分表形式虽然比较简单但受限于存储引擎(需要MyISAM存储引擎,如果能在代码设计的时候,可以预估到数据量未来的大概增长情况,还是早做分表稍微好点)
注意!!
分区方案也不是随便划分的,它的缺点如下:(1)对分区表进行DDL操作难度更大风险高。(DDL操作需要锁定所有分区,导致所有分区上操作都被阻塞)(2)分区不当,导致扫描全部分区,可能导致IO次数反而更多了。关于第二点的解释:
以Innodb存储引擎文件,我们的一个表的数据和索引保存的地方是在一个idb为后缀名的文件里头,对于分区表来说,原来的一个idb文件现在是有多个的,对应多个分区。而我们知道,InnoDb采用B+树作为索引结构,一般2次IO左右的次数就可以扫描所有数据了。而如果扫描所有分区的话,一个分区2次IO,10个分区那就是20次IO。。。。
拓展
其实还有一个更加骚的办法,文中说过该业务的特点之一是这些信息不会全部对用户公开,只显示了一段时间内的数据,我们可以写个脚本,每天晚上或者凌晨,把这段时间的数据捞出来,存到一个表中(存之前truncate一下),然后只针对该表进行查询。
-
解决navicat中使用SQL语句操作数据库查询时间过长的问题
2020-11-09 10:01:52解决navicat中使用SQL语句操作数据库查询时间过长的问题 解决方法的内容由以下链接转载得来:https://blog.csdn.net/weixin_44296929/article/details/106813103 Navicat -右键点击设置的数据库- 编辑连接 - 高级 - ...解决navicat中使用SQL语句操作数据库查询时间过长的问题
解决方法的内容由以下链接转载得来:https://blog.csdn.net/weixin_44296929/article/details/106813103
Navicat -右键点击设置的数据库- 编辑连接 - 高级 - 勾选保持连接间隔(系统默认240) - 输入框设置10 - 点击确定
-
数据库查询时间过长原因
2019-11-20 15:21:12今天新遇到的问题,2000万行的数据库,靠主键在程序里查询的时候需要5秒,但是在SQL中查询只要0.05秒。 最后发现原因是在数据库里的数据类型为VarChar,在程序里面传递的Parameter为String类型,不对应 VarChar对应...今天新遇到的问题,2000万行的数据库,靠主键在程序里查询的时候需要5秒,但是在SQL中查询只要0.05秒。
最后发现原因是在数据库里的数据类型为VarChar,在程序里面传递的Parameter为String类型,不对应
VarChar对应的是AnsiString类型,如果是String类型的数据需要进行转换才能执行。
同样的,NVarchar对应的是String类型。错误的数据类型也会导致
-
数据库按时间查询,当月和七月份数据,查询时长过长
2017-08-21 05:36:21在做一个关联查询时,按照时间查询今年六月份或六月份之前的数据,查询速度正常,但是查询七月份和现在八月份的数据时异常的慢,导致程序超时,当时想是不是后台代码的问题,测试sql语句时发现在数据库中查询速度也... -
前端遍历导致查询数据时间过长_「干货」一文搞懂为什么图数据库比关系型数据库查询更快...
2020-12-06 16:17:43随着数据形式的变化,面向不同场景的数据库也...在NoSQL中,以Neo4j为代表的图数据库解决了海量关联数据存储和查询的问题。都说在处理关联型数据上,图数据库表现得比传统关系型数据库优异,但优异在哪里呢?...随着数据形式的变化,面向不同场景的数据库也应运而生,主要包括关系型数据库(如Oracle、Mysql、SQLite)和非关系型数据库NoSQL(如内存数据库Redis、文档数据库MogoDB、列式数据库HBase、图数据库Neo4j)两大类。在NoSQL中,以Neo4j为代表的图数据库解决了海量关联数据存储和查询的问题。
都说在处理关联型数据上,图数据库表现得比传统关系型数据库优异,但优异在哪里呢?特别是,为什么图数据库查询关系数据更快呢?本文将以一个具体的例子进行解读。
图数据库VS关系型数据库
相对于关系型数据库来说,图数据库因其存储结构的特点相较于关系型数据库有天然的优势,总结起来有如下几点:
- 更好的性能表现:对于高度关联的数据来说,会产生较多的join操作,当join层次过多的时候,关系型数据库的性能会显著低于图数据库。
- 灵活性:图数据库具有更好的灵活性,不强制把图数据存储到结构一定的数据表中,属性值的新增和删除都很方便,对于NULL值很多的数据集、非结构化数据等非常有用。
- 便于数据模型:因为没有预先设定的结构,使得数据建模非常方便,可按需调整。
- SQL查询痛点:随着Join的操作,SQL语句会变得非常复杂。
对于上述四点优势,2-4理解起来没什么难度,第一点虽然字面意思能理解,但一直搞不懂是为什么?join的层数变多会让关系型数据库查询性能下降能够理解,但为什么图数据库不会呢?
案例场景
场景:有三张表分别是部门表D(部门ID、部门名称)、员工表E(员工编号、员工姓名、所属部门)、支出表P(支出编号、员工、金额),现在想查询的是部门d1的总支出金额。
关系型数据库
先来看一下传统关系型数据库是怎么解决场景中提出的问题,通过对需求的分析,SQL可以写成如下形式:
select sum(P.money)from Dleft join Eon D.id = E.didleft join Pon E.id = P.eidwhere D.id = 'd1'
上述查询语句会怎么执行呢,大致可分为如下几步:
- 遍历D表,定位部门编号id为d1的部门,即要把D表的每一个值都访问一遍,时间复杂度是|D|。
- 遍历E表,判断每一行数据的部门编号did字段是否为d1,即定位所属部门编号为d1的员工,假设最终定位到d1部门中有m位员工,时间复杂度是|E|。
- 遍历P表,判断每一行数据的员工编号是否与第2步中得到的m位员工编号相同,进而得到支出金额,时间复杂度为5*|P|。
- 汇总支出金额。
上述的按步分析可知,传统关系型数据库在解决这样一个问题时会多次遍历多张表,总的时间消耗是|D|+|E|+m*|P|。这还是在只探查一个部门的简化情况下,如果分析所有部门,时间复杂度可达到|D|*|E|*|P|,一般D表数据量不大,但E和P表的数据量(特别是P表)的数据量都很大,这样消耗的时间就很长了。
可能会有人说为什么不做索引,确实通过索引能够改善查询性能,假设D、E、P表的索引分别建在部门编号、员工编号、支出编号上,那么上面场景的时间消耗是多少呢?
- 因为D表中的部门编号有索引,所以定位部门d1的时间为常数时间,记为1。
- 员工表E的索引不在部门编号上,因此查询部门员工的操作仍然需要遍历全表,耗时为|E|。
- 支出表P的员工编号也没有做索引,查询部门员工支出仍然需要遍历全表,耗时为|P|。
- 那么,总耗时是1+|E|+m*|P|。在E表和P表都很大的情况下,耗时仍然过长。
随着D表、E表、P表规模的扩大,上述场景的耗时会越来越大。如果再增加一个支出明细表PI(产品编号、产品名称、产品数量、产品单价、支出编号),想要查询每个部门的具体支出明细,上面的SQL代码就更复杂了,耗时也就更长,最坏情况下是|D|*|E|*|P|*|PI|。
也就是说,即使使用索引,传统关系型数据库还是会随着查询层次的加深、数据量的加大,查询性能会逐渐恶化,这还不算索引本身的维护成本。
图数据库
相较于传统的关系型数据库,图数据库(以Neo4j为例)并不是把数据存在一张一张表里,然后通过外键进行关联,而是在存储结构上直接把节点及其关联节点连接在一起。这样,当需要查询一个节点的关联节点时,直接从节点循着链接出发去寻找就可以了,而不需要遍历所有节点,大大节省了查询时间。
对于上述场景中的部门、员工、支出,图数据库将其视为实体,员工和部门之间通过“属于”的关系链接在一起,支出和员工通过“实施”关系链接在一起,如下图所示:
那么,如何查询部门d1的支出情况呢,步骤如下:
- 通过索引技术快速定位编号为d1的部门,耗时为1。
- d1为图数据库中的一个节点,直接遍历通过“属于”关系与该节点联结的节点(员工),耗时为m,m为部门d1的员工数。
- 围绕m个员工节点,遍历通过“实施”关系与这些节点联结的节点(支出)。假设有n个支出事件与部门d1的m个员工相关,那么耗时为n。
- 这样,总的耗时时间为1+m+n。
这里,m和n都远小于|E|和|P|,因此耗时远小于传统关系型数据库的耗时。
即使是查询所有部门的支出情况,耗时为|D|+|E|+|P|,耗时也远比|D|*|E|*|P|少。
还有就是上面提到的,增加一个支出详情表PI,其实对于图数据库来说,并不会有多大影响。单一部门的时间变成1+m+n+c,远小于|D|+|E|+|P|+|PI|;全部部门的时间是|D|+|E|+|P|+|PI|,耗时也远比|D|*|E|*|P|*|PI|少。
总结一下
通过上面的例子可以看出,使用图数据库查询时,查询工作量仅与被查询的节点关系数有关(即一个子图),而与全局节点数无关,这样当全局节点数变多、图变大时,子图的查询工作量不会有太大变化。另外,即使是查询层次变深,对于图数据库来说,只是多查了一层子图,逻辑比较清晰,不会像传统关系型数据库那样增加SQL的复杂度和表的遍历工作量。
这就是为什么在关联数据查询的场景下,图数据库的效率要比传统关系型数据库高的原因。
PS:本文内容多翻译自《Why Graph Databases Outperform RDBMS on Connected Data》,原文有更多解读,如果您感兴趣,可自行阅读。
如果有更好的理解,欢迎评论区留言讨论。
我是会说科技,关注我,一起聊聊数据、科技、IT、安全、金融那些琐事。
-
如何查询MySQL数据库IO过高和用户压力#Olivia丶长歌#
2018-01-18 09:52:14看一下时间长的sql,主要是update,insert,delete等查看具体的磁盘消耗DBA可以根据该指标查询具体的IO消耗在哪个表上。然后针对于特定的数据库和表进行优化mysql>select file,avg_read+avg_write as avg_io from io... -
SQL语句查询时间过长的优化
2017-08-02 06:46:35下面这段sql ``` SELECT TT1.COUNT - TT2.COUNT COUNT ...所用查询时间需要8秒多,请问有什么方法能够优化这个查询~~~  -
mysql下监测数据库语句creating sort index时间过长的问题
2017-06-23 17:33:00在一张单表5000W数据上进行数据查询时传入两个单列索引条件,进行组合索引查询时,如果最后...1, limit a,b 在a值过大时,也会导致性能严重下降,解决方案是获取到一批数据之后拿到最大的ID,然后在查询条件中加入&g... -
mysql 执行时间过长_mysql查询执行时间太长
2021-01-19 06:24:30大家好我正在使用phpmyadmin数据库。...mysql查询执行时间太长查询是SELECT ib.*, b.brand_name, m.model_name,s.id as sale_id, br.branch_code,br.branch_name,r.rentry_date,r.id as ridfrom ... -
项目中记录影响性能的缓慢数据库查询
2018-03-07 19:13:00如果程序性能随着时间推移不断降低,那很有可能是因为数据库查询变慢了,随着数据库规模的增长,这一情况还会变得更糟。优化数据库有时很简单,需要在程序和数据库之间加入缓存。大多数数据库查询语言都提供了... -
增加自增列 耗时长_如何通过自动增加索引,实现数据库查询耗时降低50%
2021-01-13 14:07:44作者 | 利开园责编 | Carol封图 | ...但是过一段时间后程序响应越来越慢,这个时候一般都要花很大精力去排查原因,最后发现是数据库查询没有索引导致的。流量大或数据量增加后会导致请求变慢,加上索引就正常了。... -
增加自增列 耗时长_如何通过自动增加索引,实现数据库查询耗时降低50%?
2021-01-12 04:38:15作者 | 利开园责编 | Carol...但是过一段时间后程序响应越来越慢,这个时候一般都要花很大精力去排查原因,最后发现是数据库查询没有索引导致的。流量大或数据量增加后会导致请求变慢,加上索引就正常了。在小程序云... -
微信小程序mysql查询_微信小程序云开发数据库查询指南
2021-01-27 05:38:54写在前面做小程序的开发已经有很长一段时间了,虽然是在写,但是一直没有去沉淀过,没有去记录过踩过的坑,这篇博客作为一个导航,记录一下我所遇到的坑(主要还是云函数的数据库查询)随着不断的更新,小程序的功能也... -
poi excel导出多次循环导致查询时间过长的解决方案
2017-09-13 21:18:31最近开发的项目中要求把信息通过excel导出,由于excel中标题父节点数量不固定,父节点下子节点也不固定,且父节点、子节点都需排序,还需2表联查,所以根据for循环来多次操作数据库,1千条的数据量需要几千次查询,... -
C# 如何异步查询数据库
2019-06-03 08:10:43我们平时默认使用的查询是同步的,也就是说一方不等待另一方做好准备,当查询时间过长时,客户端会被一直阻塞在这里而不能做其他事情。而当我们使用异步时,程序并不会阻塞或挂起线程,它会通过一个代理的回调方法... -
数据库的标识符可以有多长
2019-03-14 23:28:14前言 ...一时间很好奇为什么要限制列别名的长度,查阅过资料才明白,原来数据库的名字、表名、表别名、列名、列别名和函数名等,这些都属于标识符,不同数据库对于标识符会限定各种的长度最大值... -
sql优化(查询大数据量时sql执行时间过长)
2015-11-29 18:01:15问题:Oracle数据库 sql查询的优化(成交额统计表的sql查询时间过长进行的优化) 解决办法:对sql语句中使用视图的部分替换为子查询,对查询表条件字段建立索引 引发的问题:在什么情况下建立索引,及建立索引后...