开源|性能优化工具: Themis数据库审计平台的选择与实践

　　模块划分

　　

　　总而言之，该平台主要由以下四个模块组成: 数据采集，规则分析，系统管理和结果显示. 稍后将详细描述不同模块的实现.

　　5. 数据采集

　　采集内容

　　

　　让我们首先看一下数据采集模块. 从表中可以看出，两种数据库的集合内容是不同的.

　　Oracle提供了大量信息，这些信息基本上可以采集； MySQL的信息相对较少.

　　该表格中的“复选标记+星号”表示该非定时作业已完成，但稍后将被实时检索. 让我们简单地谈谈每个部分的采集内容.

　　这些信息将用作后续审核的基础.

　　采集原则

　　

　　以下简要介绍了集合和原理:

　　6. 规则分析

　　摘要说明

　　下面介绍了整个系统的核心部分，即规则解析模块. 它的功能是根据定义的规则检查采集的数据，并过滤出违反规则的数据. 对过滤后的数据进行评分并记录下来，以供后续生成审核报告. 同时，还将记录其他信息，以帮助进行某些判断工作.

　　这里有一个核心概念-“规则”. 稍后您将看到内置规则的定义，每个人都会更加清楚. 从分类的角度来看，可以将其大致分为以下几类.

　　规则定义

　　

　　这是规则主体的声明对象. 让我解释每个字段的含义. 您还可以对规则有清楚的了解.

　　规则定义（对象级别）

　　

　　首先让我们看看第一种规则-对象规则. 这是为数据库对象设置的一组规则. 上表显示了一些示例. 常见对象（如表，分区，索引，字段，函数，存储过程，触发器，约束，序列等）都是已审计的对象. 以表格为例，有很多内置规则.

　　例如: 第一个“太大的表”. 表示数据库中大表的数量超过了规则定义的阈值. 这里的大表由常规输入参数确定，包括表记录的数量和表的物理大小. 该规则的整体描述是“表的数量超过指定的大小或数据库中的记录的指定数量超过指定的阈值，然后触发审核规则. ”其他对象的规则相似.

　　规则实施（对象级别）

　　

　　对象规则的实现部分相对简单. 除了个别规则外，基本上先查询数据字典信息，然后根据规则定义进行判断. 上面的示例是在执行索引规则时查询数据字典信息.

　　规则定义（执行计划级别）

　　

　　第二种规则是执行计划的规则，它也分为几类. 例如，访问路径类，表关联类，类型转换类，绑定变量类等.

　　以最常见的访问路径类为例进行说明. 例如最常见的规则“大表扫描”. 这意味着在执行SQL语句期间，将执行对大表的访问，并且访问路径将使用全表扫描. 此规则的输入参数包括大表的定义（物理大小或记录数）；输出部分包括表名，表大小和其他信息（包括整个执行计划，指定的大表的统计信息等）.

　　此类规则所针对的数据源是从联机数据库中获取的. 按时间段直接从AWR中提取Oracle部分，并使用explain命令返回数据库来获得MySQL部分.

　　信息存储格式

　　

　　特别是，让我在这里解释一下，在保存执行计划时，将使用诸如MongoDB之类的文档数据库. 目的是使用其无模式功能来促进与不同数据库和版本的执行计划中的差异的兼容性. 所有这些都可以存储在一个集中，并且后续的规则审核也可以使用mongo中的查询语句来实现. 这也是最初引入mongo的初衷，其他类型的信息将在以后放入图书馆. 现在，除了pt工具访问的部分之外，整个审核平台都使用MySQL，其余部分在MongoDB中. 另外，MySQL库可以json格式直接输出执行计划，非常方便存储； Oracle部分也以json格式存储.

　　规则实现（执行计划）

　　

　　左侧是存储在MongoDB中的Oracle执行计划. 实际上，sqlplan词典数据已插入mongo. 右边是规则实现的样本，它是基于mongo的查询语句. 稍后我们将看到详细的示例.

　　7. 平台实施

　　规则实施

　　

　　这是“大表全表扫描”规则的示例. 上面是保存在Oracle数据字典中的执行计划，下面是存储在Mongo中的执行计划. 可以看出它已被完全复制.

　　

　　基于此结构，如何实现规则过滤？实际上，它是通过mongo中的find语句实现的. 以下是对该语句执行步骤的具体解释.

　　规则实现（执行计划）

　　这部分是在MySQL中实现分层结果存储的示例.

　　

　　第一张图片显示了原创执行计划.

　　

　　第二个图是代码实现的摘要.

　　

　　第三张图片实际上是库中的外观. 核心部分是item_level的生成.

　　规则定义（文本级别）

　　

　　第三种规则是基于文本的规则，与数据库的类型无关，它描述了SQL语句的文本特征. 在实现中，它是通过文本常规匹配或程序来处理的. 其主要目的是使开发人员的SQL编写标准化，避免复杂，性能低下和非标准的SQL编写.

　　规则实施（文本级）

　　

　　本节描述文本规则的实现. 第一个示例bad_join是通过常规文本匹配实现的简单规则. 第二个示例sub_query是通过程序判断子查询（或多级子查询）以判断括号的嵌套.

　　规则定义（执行功能级别）

　　

　　规则的最后一种类型是实现特征类型. 这部分与数据库密切相关，并过滤出符合某些执行特征的语句. 这些语句不一定是效率低下的，但可能是将来优化的重点，或者是某些具有最高优化收益的语句. 这主要是关于资源的消耗.

　　8. 系统管理

　　规则管理

　　

　　在显示一些界面之后，我们将介绍该平台的功能.

　　第一部分是系统管理模块的规则管理部分. 在这一部分中，您可以添加自己的规则. 它的核心是规则实现部分，它以SQL语句，Mongo查询语句和自定义Python文件的形式定义规则实现主体. 定制规则的基础是当前正在爬网的数据源，并且定义者需要熟悉现有的数据结构和含义. 当前，不支持自定义爬网数据源.

　　

　　对于已定义的规则，您可以在此处修改规则. 主要配置规则状态，阈值，扣除项等.

　　任务管理

　　

　　配置规则后，您可以在此处完成任务发布工作.

　　上面是发布规则任务的界面. 选择数据源（ip，端口，架构）后，选择审核类型和审核日期. 当前的审核数据源的计时策略仍基于天，因此不能选择日期作为天.

　　任务下达后，您可以在任务结果视图界面上观察执行情况. 根据审核类型，数据源对象的数量，句子的数量等，审核持续时间通常在5分钟内是可变的. 当审核作业的状态为“成功”时，表示审核作业已完成，可以查看或导出审核结果.

　　9. 结果显示

　　对象检查结果概述

　　

　　上图是对象审核报告的示例. 报告的开头是概述页面. 它着重于显示审计报告中的各种规则和推论；并通过饼图显示它们的比例. 这使我们能够首先关注核心问题.

　　在顶部，您还可以看到规则总分的显示. 这是我们根据百分制转换规则推导点后获得的点. 分数越高，违规越少，审计对象的质量也越高. 在设计之初，“规则总分”项目的引入就引起了一些争议. 我担心拥有这个指标会更挫败开发人员的热情，并且不利于平台的推广. 这里有几点要解释.

　　对象审核结果的详细信息

　　

　　这部分是对象审核的详细部分，与每个规则的细节相对应，您可以在左侧的链接中进一步查看对象信息. 由于篇幅所限，我们将不再显示.

　　执行计划审查结果概述

　　

　　执行计划的此部分的概览显示与主题相似. 这也是每条规则的推论.

　　详细的执行计划审核结果

　　

　　这是执行计划的详细部分.

　　

　　展开后，您可以查看每个违反规则的详细信息. 上图是违反全表扫描的规则的详细部分.

　　以上是一些常规解决方案说明. 这里说明了可能触发此类规则的情况和解决方案. 它相当于一个很小的知识库，便于开发人员进行优化. 稍后，在平台的第二阶段，将制作更精确的优化引擎部分，并且该部分将继续.

　　以下是每个违反语句的情况，我们可以看到语句文本，执行计划，相关信息（例如此规则的大表的名称）等. 您可以进一步单击该语句以展开信息.

　　

　　

　　这部分是针对每条SQL信息的，包括语句文本，执行计划，执行特征，关联的对象统计信息等. DBA可以根据此信息进行一些初步的优化判断工作.

　　此外，该平台还提供了导出功能. 可以将其导出为ex​​cel文件，以供用户下载和查看. 显示在这里.

　　10. 我们遇到的坑

　　在实际的开发过程中，遇到了许多问题. 我们在这里简要介绍两个，例如:

　　MySQL在解析json格式执行计划时暴露的问题...

　　[会话进入睡眠状态，已暂停*敏*感*词*]

　　解决方案: 在执行会话之前设置wait_timtout = 3，并根据实际情况调整此时间.

　　[数据量太大，很长一段时间都没有结果]

　　会话处于查询状态，但是数据量很大，或者由于数据库不能很好地支持format = json，因此无法长时间解析它，这会影响其他会话.

　　解决方案: 使用pt-kill工具杀死会话. 为了防止误杀，请标记“ eXplAin format = json”，然后使用pt-kill标识eXplAin关键字.

　　11. 促销过程

　　

　　自CreditEase上该平台运行以来，它已为许多系统提供了审计报告，从而极大地加快了数据库结构和SQL优化的速度，并减轻了DBA的日常工作压力. 在工作实施过程中，我们还探索了一套实施方法. 该平台开源后，如果有朋友使用，请参考实现.

　　信息采集阶段

　　大量采集公司数据库系统的运行状态并掌握第一手信息. 快速了解每个业务系统的质量，并在飞行员选择方面做得很好.

　　手动分析阶段

　　关键系统，手动干预分析. 根据规则审查中暴露的核心问题，“点对点”，给出有针对性的分析和优化报告.

　　交流训练阶段

　　主动出门与开发团队就报告进行沟通. 借助分析和报告的机会，可以为开发团队提供必要的培训工作，并结合他们周围的案例，更具说服力.

　　反馈改进阶段

　　落实交流的成果，并敦促其改善. 定期反馈，以通过审核平台提高质量. 具有一定基础的团队可以开发供开发人员使用的平台. SQL质量问题不再只是DBA问题，而与项目中的每个人都息息相关.