亡羊补牢or防患未然？在数百套多种数据库中找出风险点（附PPT下载）

最新推荐文章于 2023-10-18 13:58:05 发布

数据和云

最新推荐文章于 2023-10-18 13:58:05 发布

阅读量265

点赞数

文章标签：数据库大数据编程语言人工智能 java

本文链接：https://blog.csdn.net/enmotech/article/details/112000695

版权

本文探讨在企业级运维中如何对多种数据库进行SQL质量分析，以预防性能和安全问题。通过采集、分析和展示TopSQL，利用专家经验和机器学习模型识别风险，建立知识库以提高SQL优化效率。分享了针对Oracle、MySQL、DB2、SQL Server、PostgreSQL、OceanBase获取TopSQL的方法，并提出实现大规模SQL质量分析的平台架构。

摘要由CSDN通过智能技术生成

墨墨导读：2020数据技术嘉年华于11月21日落下帷幕，大会历时两天，来自全国各地的数据领域学术精英、领袖人物、技术专家、从业者和技术爱好者相聚北京，见证了个人的快速成长、技术的迭代进步、行业的蓬勃发展、生态的融合共赢，以及市场的风云变迁。

2020数据技术嘉年华近50个PPT下载、视频回放已上传墨天轮平台，可在“数据和云”公众号回复关键词“2020DTC”获得！

本文根据巩飞老师在 2020数据技术嘉年华分享的主题《大规模多种数据库热点SQL质量分析》整理而成。

巩飞：云和恩墨应用架构产品部总经理，近20年工作经验，围绕数据库领域从事过开发、架构设计、运维等，如今专注产品设计，擅长Oracle数据库、内存数据库、GoldenGate等。经历了两层架构时代关系型数据库技术的蓬勃发展、三层架构时代关系型数据库技术的砥砺前行、一直到现在互联网+时代关系型数据库技术面临的诸多挑战，作为数据领域的老兵，继续奋战在一线，不断学习成长，乐在其中。

摘要

在企业级运维工作中，我们面对的往往是多种数据库，数十甚至数百套数据库，需要在有异动时能快速响应，更需要能提前感知，在问题产生前就找出风险点并消除。在数据库运维中，大多数问题都是由SQL引发的，今天和大家分享的内容就是一种能够同时对数百套Oracle、MySQL、DB2、SQL Server、PostgreSQL、OceanBase数据库抓取TopSQL，分析SQL质量，找出风险点的方法。

为什么要做？

一直以来，大部分数据库性能问题都是由应用开发产生的不良SQL、不良表设计(低效、不规范)所致，并且在测试、开发过程中缺乏有效的审核管理和流程规范，导致SQL问题不能及时有效解决。当应用发布上线以后，爆发各种性能问题和安全问题，DBA陷于各种救火工作。

质量管理的核心思想是提前把事情做对，通过一定的质量管理投入来分析SQL质量，优化问题SQL，将SQL问题消灭在萌芽阶段，避免影响业务高效稳定运行。

做哪些？

既然SQL质量分析是很值得做的事情，那要做哪些？是将所有运行的SQL均进行分析？这样量太大，成本太高。是随机采样SQL进行分析？这样覆盖率有限，很容易错过严重风险。

按照我的经验，想把SQL质控做好，需要合理地分配时间和精力，面面俱到不如重点突破，把80%的资源花在能出关键效益的20%的方面。哪如何确定这20%的重点SQL呢？可以从TopSQL入手。

从执行次数、执行时长、消耗资源等不同维度，提取TopSQL作为分析对象。提取Top 10，Top 20还是Top 50？每小时，每天还是每业务周期提取一次？分析时采用专家模型还是机器学习模型？展示时列表式，报表式，图形式还是告警式？这些都可以随着需求场景，灵活设定。

如何做？

通过采集、分析、展示三大环节，就可以实现大规模多种数据库热点SQL质量分析的目标。

采集：实现按需拿到要分析的SQL。需要包含采集任务管控，按频率调度，合适的采集脚本。

分析：实现对SQL进行全面分析，判断其有无风险，具体的风险情况。需要包含SQL的词法解析，语义识别，提取支撑分析的元数据、统计信息、执行计划等，以专家经验模型，机器学习模型，或二者结合进行风险分析。

展示：以适合的方式将SQL及其风险点，对象信息、统计信息、执行计划进行展示。

1. 关键点是什么？
在整个实现路径中，要做的事情很多，但有三个最为关键的要点，即精准捕获TopSQL，全面深入的分析模型，丰富的信息及知识库。我们对这些深入讨论。

2. Oracle中如何获取TopSQL？
可以从AWR中提取，可以从ASH中提取，也可以从shared pool（v$sql等视图）中提取，还可以通过日志分析提取等，方式较多，考虑实现成本和效果，推荐从AWR中提取。

3. MySQL中如何获取TopSQL？
可以分析慢日志提取，可以分析通用日志提取，也可以从performance schema中提取等，方式较多，考虑实现成本和效果，推荐从performance schema中提取。

4. DB2中如何获取TopSQL？
可以从sysibmadm下的视图中提取，可以从MON_GET系列表函数中提取，也可以通过日志解析提取等，方式较多，考虑实现成本和效果，推荐从sysibmadm下的视图或MON_GET系列表函数中提取。

5. SQL Server中如何获取TopSQL？
可以从dm_exec系列函数中提取，也可以通过日志解析提取等，方式较多，考虑实现成本和效果，推荐从dm_exec系列函数中提取。

6. PostgreSQL中如何获取TopSQL？
可以从pg_stat_statements中提取，也可以通过日志解析提取等，方式较多，考虑实现成本和效果，推荐从pg_stat_statements中提取。

7. OceanBase中如何获取TopSQL？
OceanBase因为目前公开的资料有限，方式较少，已知的可以通过Plan Cache 系列视图中提取。

8. 如何做SQL解析？
在拿到SQL后，首先要进行SQL解析，可以自研，也可以基于开源组件，比如Antlr、JSqlParser、Apache Calcite等二次开发。要达成的目标，都是对各种数据库的SQL进行词法分析、语法分析，生成语法树。

上图为Calcite架构

无论哪种方式，均需兼容Oracle、MySQL、DB2、SQL Server、PostgreSQL、OceanBase等各种数据库的SQL语法区别，增强元数据特征，提取源生执行计划。

这里举个例子，比如SQL语句：

SELECT * FORM  CUSTOMER
 WHERE 
IDCARDNO='320113198001003226'

在经过解析后，生成的语法树是下面的样子。

9. 如何做SQL分析？

在SQL分析时，主流有两种做法，专家经验方式，机器学习方式。

专家经验方式，如上图，主要就是根据专家经验，将SQL可能存在的风险提炼出来，总结出每种风险的特征、危害、检测算法，即规则，最后形成规则库，比如：

规则，是有效经验的浓缩，越丰富可以发现的风险越多。应支持用户自定义规则。

在分析时，主要是用规则库中每个规则算法对SQL进行比对，确定出其风险点。还可以再根据风险点清单给SQL生成质量评分。

机器学习方式，如上图，主要就是通过Agent与环境不断试错的过程的方式进行交互，寻找最优决策策略，以最大化与环境交互获得的累计奖励。在数学上可建模为马尔科夫决策过程（MDP，Markov Decision Process），用于在系统状态具有马尔可夫性质的环境中模拟智能体可实现的随机策略与回报，要素包括状态、动作、策略和奖励。

实际执行时间作为Reward进行训练，即执行策略的调整。目标是找到查询执行时间最小的执行计划。

10. 为何要建立知识库？

如果你长期进行SQL质量分析、SQL优化相关工作，就会发现，SQL虽然可以千奇百怪，写成各种各样，但其底层原理，高效的原则却是相同的。所以建立单位内部SQL相关的知识库，不仅能总结过去，还可以指导未来。在分析出风险SQL后，给不擅长优化的同事提供资源支撑。建议知识库至少涵盖SQL知识背景、SQL优化原则、SQL优化原理、SQL优化示例、SQL编写规范等，并随着业务发展不断积累更新。

11. 怎么样支撑大规模多种数据库的并SQL质量分析？
为了实现大规模多种数据库并行的SQL质量的评估和管控，要做到外部架构松耦合，内部架构高性能、高可用、随需扩展。比如可以考虑整个平台部署架构示意如下：