基于SQL优化的数据库运行控制方法、系统及存储介质转让专利
申请号 : CN202110841430.6
文献号 : CN113282574B
文献日 : 2021-10-22
发明人 : 闵建兵 , 熊军
申请人 : 云和恩墨(北京)信息技术有限公司
摘要 :
权利要求 :
1.一种基于SQL优化的数据库运行控制方法,其特征在于,包括:接收并解析告警信息以得到告警目标机器,并获取所述告警目标机器的数据库的运行参数;
若所述数据库的所述运行参数超过预设参数阈值,采集正在执行的SQL的执行参数和执行信息;
根据所述执行参数、所述执行信息和预设筛选规则分析出造成故障的SQL以得到影响SQL;
对所述影响SQL进行解析以得到第一解析数据,根据所述第一解析数据以预设改写规则对所述影响SQL改写以得到优化SQL;
对优化SQL进行解析以得到第二解析数据,向所述第二解析数据添加对应的索引;
所述对所述影响SQL进行解析以得到第一解析数据,根据所述第一解析数据以预设改写规则对所述影响SQL改写以得到优化SQL,包括:对所述影响SQL进行词法解析以得到词法解析数据;
对所述词法解析数据转换为抽象语法树,并对所述抽象语法树进行语法解析以得到所述第一解析数据;
根据所述预设改写规则将所述第一解析数据中的函数列过滤条件、表达式列过滤条件和不等于过滤条件去除以得到所述优化SQL。
2.根据权利要求1所述的基于SQL优化的数据库运行控制方法,其特征在于,所述运行参数包括:CPU占用率、响应时间;所述预设参数阈值包括:预设占用率阈值、预设时间阈值。
3.根据权利要求2所述的基于SQL优化的数据库运行控制方法,其特征在于,所述若所述数据库的所述运行参数超过预设参数阈值,采集正在执行的SQL的执行参数和执行信息,包括:
若所述数据库的所述CPU占用率超过预设占用率阈值、和/或所述数据库的所述响应时间超过所述预设时间阈值,采集正在执行的SQL的所述执行参数和所述执行信息。
4.根据权利要求1所述的基于SQL优化的数据库运行控制方法,其特征在于,预设筛选规则包括:预设筛选参数和预设筛选条件;所述根据所述执行参数、执行信息和预设筛选规则分析出造成故障的SQL以得到影响SQL,包括:根据所述执行信息以所述预设筛选条件筛选以得到多个预处理SQL;
获取所述多个预处理SQL的所述执行参数与所述预设筛选参数之间差值最大的所述SQL为所述影响SQL。
5.根据权利要求4所述的基于SQL优化的数据库运行控制方法,其特征在于,所述执行信息包括:表的扫描行数、索引使用状况、全表扫描的存在状态、查询返回行数,所述根据所述执行信息以所述预设筛选条件筛选以得到多个预处理SQL,包括:获取所述表的扫描行数大于预设第一行数、和/或所述索引使用状况为使用、和/或所述全表扫描的存在状态为存在、和/或所述查询返回行数大于预设第二行数的SQL以得到多个预处理SQL。
6.根据权利要求4所述的基于SQL优化的数据库运行控制方法,其特征在于,所述执行参数包括:执行时间、执行时长、执行次数,所述预设筛选参数包括:预设时间阈值、预设时长阈值、预设次数,所述获取所述多个预处理SQL的所述执行参数与所述预设筛选参数之间差值最大的所述SQL为所述影响SQL,包括:获取所述多个预处理SQL的所述执行时间和所述预设时间阈值的差值以得第一差值;
获取所述多个预处理SQL的所述执行时长和所述预设时长阈值的差值以得到第二差值;
获取所述多个预处理SQL的所述执行次数和所述预设次数的差值以得到第三差值;
获取所述多个预处理SQL中所述第一差值、所述第二差值与所述第三差值之和最大的所述SQL为所述影响SQL。
7.根据权利要求1至5任一项所述的基于SQL优化的数据库运行控制方法,其特征在于,所述对优化SQL进行解析以得到第二解析数据,向所述第二解析数据添加对应的索引,包括:
解析所述优化SQL使用的表、过滤条件使用的列、关联条件使用的列、关联操作符、过滤操作符以得到所述第二解析数据;
采集所述第二解析数据中表的行数、列的索引基数以得到统计信息;
根据所述优化SQL的过滤条件和所述统计信息对索引基数高的列添加索引。
8.一种基于SQL优化的数据库运行控制系统,包括:至少一个处理器,以及,
与所述至少一个处理器通信连接的存储器;其中,所述存储器存储有可被所述至少一个处理器执行的指令,所述指令被所述至少一个处理器执行,以使所述至少一个处理器能够执行如权利要求1至7任一项所述的基于SQL优化的数据库运行控制方法。
9.一种计算机可读存储介质,其特征在于,所述计算机可读存储介质存储有计算机可执行指令,所述计算机可执行指令用于使计算机执行如权利要求1至7任一项所述的基于SQL优化的数据库运行控制方法。
说明书 :
基于SQL优化的数据库运行控制方法、系统及存储介质
技术领域
背景技术
了解很少,且多数的数据库的SQL语句性能调优也是参差不齐,所以数据库经常出现由于开
发人员编写低性能的SQL导致数据库死机,进而导致业务系统响应缓慢。
JOIN条件缺少索引、where条件确少索引则会出现严重的性能下降,造成整个服务器负载升
高、造成业务系统响应时间变长或者整个业务系统出现中断。因此,相关人员采用DBA手动
添加索引、停止业务系统,等待DBA和开发人员优化SQL后再恢复业务系统,但是由于人工参
与无法准确预测优化SQL的时间,导致业务系统无法快速恢复正常使用。
发明内容
的效率。
件,并得到优化SQL自动确定索引,因此在检测数据库故障时,对SQL进行自动优化,无需数
据库管理人员进行SQL优化,则数据库管理人员可以解脱出来投入更多精力参与业务系统
的分析和技能提升,且自动进行数据库故障分析,以自动查找到导致数据库故障的影响
SQL,以节省数据库人员自动排查的时间,且提高了SQL优化的效率。
括:
分析出造成故障的SQL以得到影响SQL,包括:
执行信息以所述预设筛选条件筛选以得到多个预处理SQL,包括:
到多个预处理SQL。
阈值、预设次数,所述获取所述多个预处理SQL的所述执行参数与所述预设筛选参数之间差
值最大的所述SQL为所述影响SQL,包括:
响SQL改写以得到优化SQL,包括:
库运行控制方法。
述的基于SQL优化的数据库运行控制方法。
附图中所特别指出的结构来实现和获得。
附图说明
具体实施方式
例,而不是全部实施例,基于本发明的实施例,本领域的技术人员在不付出创造性劳动的前
提下所获得的其他实施例,均属于本发明保护的范围。
简化描述,而不是指示或暗示所指的装置或元件必须具有特定的方位、以特定的方位构造
和操作,因此不能理解为对本发明的限制。如果某一特征被称为“设置”、“固定”、“连接”、
“安装”在另一个特征,它可以直接设置、固定、连接在另一个特征上,也可以间接地设置、固
定、连接、安装在另一个特征上。
如果涉及到“以上”、“以下”、“以内”,均应理解为包括本数。如果涉及到“第一”、“第二”,应
当理解为用于区分技术特征,而不能理解为指示或暗示相对重要性或者隐含指明所指示的
技术特征的数量或者隐含指明所指示的技术特征的先后关系。
较熟悉,对于数据库的了解较少,且多数的数据库的SQL语句性能调优也是参差不齐的,数
据库经常出现由于开发人员编写低性能SQL造成数据库死机,从而导致业务系统响应缓慢
甚至中断。
据库对于表连接的算法只支持嵌套JION,如果出现JION条件缺少索引,where条件缺少索引
则会出现严重的性能下降,造成整个服务器负载升高,造成业务相应时间变长或者整个业
务系统出现中断。针对MySQL数据库所存在的问题,大部分企业采用的解决手段则是依赖
DBA手动添加索引,停止业务系统等待DBA和开发人员优化SQL后再恢复业务系统,因此导致
业务系统无法快速恢复业务系统,降低了用户使用的体验感。
投入更多精力参与业务系统的分析和技能提升,从而提高用户使用业务系统的体验感。
据访问控制。
(Relational Database Management System,关系数据库管理系统)应用软件之一。
业务数据库从设计、测试到部署交付的全生命周期管理。
通过监控告警目标机器的数据库的运行参数。若运行参数超过预设参数阈值,则表示数据
库的负载过高存在死机的隐患。因此采集正在执行的SQL的执行参数和执行信息,然后根据
预设筛选规则将正在执行的SQL进行筛选以确定造成数据库故障的SQL以得到影响SQL,确
定了影响SQL后对影响SQL进行解析以得到第一解析数据,因此根据解析的第一解析数据以
预设改写规则去除第一解析数据中的条件以得到改写后的优化SQL,然后对优化SQL再进行
解析以得到第二解析数据,并根据第二解析数据确定对应的索引。因此当检测数据库存在
故障隐患时,获取导致数据库故障的影响SQL,然后解析影响SQL后删除条件,并得到优化
SQL自动确定索引,因此在检测数据库故障时,对SQL进行自动优化,无需数据库管理人员进
行SQL优化,则数据库管理人员可以解脱出来投入更多精力参与业务系统的分析和技能提
升,且自动进行数据库故障分析,以自动查找到导致数据库故障的影响SQL,以节省数据库
人员自动排查的时间,且提高了SQL优化的效率。
方案。zabbix能监视各种网络参数,保证服务器系统的安全运营;并提供灵活的通知机制以
让系统管理员快速定位/解决存在的各种问题。通过zabbix系统对服务器进行监控,若存在
故障情况则接收来自zabbix系统的告警信息。在本实施例中接收来自zabbix系统发送的告
警信息,其他实施例可以采用其他告警信息对服务器进行故障监控以接收来自其他告警系
统发送的告警信息。通过对告警信息进行解析以得到告警类型和告警目标机器,则可以对
告警目标机器的数据库的运行参数采集,以根据运行参数和预设参数阈值来判断告警目标
机器的数据库是否存在故障。
库的CPU占用率、响应时间和预设占用率阈值、预设时间阈值来判断数据库的性能是否故
障,若检测数据库存在故障隐患则采集正在执行的SQL的执行参数和执行信息。
SQL的执行参数和执行信息,以判断正在执行的SQL哪一个导致数据库故障。因此,通过判断
数据库的CPU占用率和响应时间确定数据库是否故障,实现自动进行数据库故障监测。
符合预设筛选条件的SQL,由于符合预设筛选条件的SQL也存在多个,因此需要进一步对多
个预处理SQL进行筛选。通过计算多个预处理SQL的执行参数和预设筛选参数的差值,以获
取差值最大的SQL以确定该SQL为导致数据库故障的影响SQL。因此通过对多个预处理SQL的
执行信息以预设筛选条件筛选后,再通过执行参数和预设筛选参数进一步确定影响SQL,以
实现精确查找到影响数据库故障的影响SQL。
SQL。
预设筛选条件筛选出执行信息满足预设筛选条件的SQL,以得到多个预处理SQL,因此可以
大概率地筛选出影响数据库故障的SQL。
断,以获取执行时间、执行时长和执行次数最为异常的SQL,以得到大概率造成数据库故障
的影响SQL。因此通过计算第一差值、第二差值和第三差值之和确定最为异常的SQL以得到
影响SQL,以自动且准确地查找出影响数据库故障的影响SQL,无需人工排查,节省数据库管
理人员的SQL排查,提高了影响SQL查找的效率和准确性。
设次数的第三差值为a3;2‑SQL的执行时间和预设时间阈值的第一差值为b1、执行时长和预
设时长阈值的第二差值为b2,执行次数和预设次数的第三差值为c3;3‑SQL的执行时间和预
设时间阈值的第一差值为c1、执行时长和预设时长阈值的第二差值为c2,执行次数和预设
次数的第三差值为c3。若a1+a2+a3
影响SQL,因此可以快速查找出大概率导致数据库故障的影响SQL。
据,其中词法解析主要用于将SQL拆解成不可再分的原子符号,并根据数据库提供的数据字
典将SQL拆分成关键词、表达式、字母量和操作符,然后再使用语法解析将词法解析数据转
换为抽象语法树,且转换为抽象语法树后即可根据抽象语法树确定SQL使用的表、过滤条件
使用的列、SQL中关联条件使用的列、关联条件的操作符、过滤条件的操作符、过滤值以得到
第一解析数据。因此需要进行SQL改写前需要解析到第一解析数据,由于第一解析数据为
SQL使用的表、过滤条件使用的列、SQL中关联条件使用的列、关联条件的操作符、过滤条件
的操作符、过滤值,因此根据第一解析数据去掉函数列过滤条件、表达式过滤条件和不等于
过滤条件,且将去掉各种过滤条件的第一解析数据以得到优化SQL,因此去除第一解析数据
中会影响数据库性能的过滤条件,则可以根据优化SQL再确定对应的索引,以恢复数据库的
正常运行,从而稳定业务系统运行。
如:SELECT id, name,login_time FROM t_user WHERE status = 'ACTIVE' AND age >
18 AND date(login_time)>'2020‑10‑01' AND 1=1。那么需要对SQL进行改写,则是去掉函
数列过滤条件、表达式列过滤条件和不等于过滤条件,则改写得到的数据如下:SELECT id,
name,login_time FROM t_user WHERE status = 'ACTIVE' AND age > 18。因此对于SQL
改写操作自动化,无需数据库管理人员进行SQL优化操作,使得SQL优化操作更加高效。
符,也即去除过滤条件的第一解析数据以得到第二解析数据。然后根据第二解析数据对表
的行数、列的索引基数进行采集以得到统计信息,则可以根据统计信息和优化SQL的过滤条
件对索引基数高的列添加索引,以实现自动给索引优化信息并自动执行添加索引,无需停
止业务系统后等待数据库管理人员和开发人员优化SQL再恢复业务系统,则业务系统可以
快速恢复正常运行,节省数据库管理人员和开发人员的人力,且提高数据库的稳定性,进而
提高业务系统的稳定性。
动执行添加索引,提高业务系统运行的稳定性、数据库的治愈效率。
限制。
过预设占用率阈值、和/或数据库的响应时间超过预设时间阈值以判断数据库存在性能故
障,获取正在运行的SQL的执行参数和执行信息。获取多个预处理SQL中表的扫描行数大于
预设第一行数、和/或索引使用状况为使用、和/或全表扫描的存在状态为存在、和/或查询
返回行数大于预设第二行数的SQL以得到多个预处理SQL,再通过对预处理SQL的执行时间、
执行时长和执行次数进行判断,以获取执行时间、执行时长和执行次数最为异常的SQL,以
得到大概率造成数据库故障的影响SQL。通过对影响SQL进行词法解析以得到词法解析数
据,再使用语法解析将词法解析数据转换为抽象语法树,且转换为抽象语法树后即可根据
抽象语法树确定SQL使用的表、过滤条件使用的列、SQL中关联条件使用的列、关联条件的操
作符、过滤条件的操作符、过滤值以得到第一解析数据,将第一解析数据去掉函数列过滤条
件、表达式过滤条件和不等于过滤条件以得到优化SQL。再对优化SQL进行解析,以解析优化
SQL使用的表、过滤条件使用的列、关联条件使用的列、关联操作符、过滤操作符以得到第二
解析数据,根据第二解析数据对表的行数、列的索引基数进行采集以得到统计信息,根据统
计信息和优化SQL的过滤条件对索引基数高的列添加索引。因此实现了自动查询影响SQL,
并自动优化影响SQL以得到优化SQL,则节省数据库管理人员和开发人员的人力,且提高数
据库的稳定性,进而提高业务系统的稳定性。
其中,所述存储器200存储有可被所述至少一个处理器100执行的指令,所述指令被所述至
少一个处理器100执行,以使所述至少一个处理器100能够执行如第一方面的基于SQL优化
的数据库运行控制方法。
的基于SQL优化的数据库运行控制方法。
可以根据实际的需要选择其中的部分或者全部模块来实现本实施例方案的目的。
由处理器,如中央处理器、数字信号处理器或微处理器执行的软件,或者被实施为硬件,或
者被实施为集成电路,如专用集成电路。这样的软件可以分布在计算机可读介质上,计算机
可读介质可以包括计算机存储介质(或非暂时性介质)和通信介质(或暂时性介质)。如本领
域普通技术人员公知的,术语计算机存储介质包括在用于存储信息(诸如计算机可读指令、
数据结构、程序模块或其他数据)的任何方法或技术中实施的易失性和非易失性、可移除和
不可移除介质。计算机存储介质包括但不限于RAM、ROM、EEPROM、闪存或其他存储器技术、
CD‑ROM、数字多功能盘(DVD)或其他光盘存储、磁盒、磁带、磁盘存储或其他磁存储装置、或
者可以用于存储期望的信息并且可以被计算机访问的任何其他的介质。此外,本领域普通
技术人员公知的是,通信介质通常包含计算机可读指令、数据结构、程序模块或者诸如载波
或其他传输机制之类的调制数据信号中的其他数据,并且可包括任何信息递送介质。
出各种变化。此外,在不冲突的情况下,本发明的实施例及实施例中的特征可以相互组合。