采集内容管理平台(Oracle模块划分总结一下（二）：数据采集、规则解析)

　　采集内容管理平台(Oracle模块划分总结一下（二）：数据采集、规则解析)

　　模块划分

　　

　　综上所述，平台主要由以上四个模块组成：数据采集、规则分析、系统管理、结果展示。后面会详细介绍不同模块的实现。

　　5、数据采集

　　采集内容

　　

　　我们先来看看 data采集 模块。从表中可以看出，两种数据库的采集的内容是不同的。

　　Oracle提供了丰富的资料，基本上需要的都可以采集；MySQL函数信息相对较少，可以采集。

　　表中的“复选标记+星号”表示非计划作业已完成，但稍后会实时检索。下面对各部分的采集内容进行简要说明。

　　这些信息将作为后续审核的基础。

　　采集原理

　　

　　下面简单介绍一下采集的原理和原理：

　　6、规则分析

　　概述

　　下面介绍整个系统的核心部分——规则解析模块。它的作用是根据定义的规则对采集的数据进行审核，过滤掉违反规则的数据。对过滤后的数据进行评分和记录，以供后续生成审计报告。同时，还会记录一些额外的信息，以辅助一些判断工作。

　　这里有一个核心概念——“规则”。后面可以看到内置规则的定义，大家会比较清楚。从分类上来看，大致可以分为以下几类。

　　规则定义

　　

　　这是规则体的声明对象。我将解释每个字段的含义，以便大家对规则有一个清晰的认识。

　　规则定义（对象级别）

　　

　　我们先来看第一类规则——对象规则。这是一组针对数据库对象设置的规则。上表显示了一些示例。常见的对象，如表、分区、索引、字段、函数、存储过程、触发器、约束、序列等，都是审计对象。以表格为例，内置了很多规则。

　　例如：第一个“大表太多”。表示数据库中的大表数量超过了规则定义的阈值。这里的大表是由规则的输入参数决定的。参数包括表记录数和表的物理大小。该规则的总体描述是“数据库中超过指定大小的表数或指定记录数超过指定阈值，触发审计规则”。其他对象的规则类似。

　　规则实现（对象级别）

　　

　　对象规则的实现部分比较简单。除个别规则外，基本都是查询数据字典信息，然后根据规则的定义进行判断。上面的例子是一个索引的规则实现，查询数据字典信息。

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

　　

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

　　以最常见的访问路径类为例进行说明。比如最常见的规则“大表扫描”。表示在执行SQL语句时，对大表进行访问，访问路径为全表扫描的形式。该规则的输入参数包括大表的定义（物理大小或记录数）；输出部分包括表名、表大小和附加信息（包括整个执行计划、指定大表的统计信息等）。

　　这些规则所针对的数据源是从在线数据库中获取的。Oracle部分是按时间段直接从AWR中提取出来的，MySQL部分是通过explain命令查数据库得到的。

　　信息存储格式

　　

　　这里特别说明，保存执行计划时，使用的是MongoDB等文档数据库。目的是利用其无模式特性，方便兼容不同数据库和版本的执行计划差异。都可以存储在一个集合中，后续的规则审核也是使用mongo中的查询语句来实现的。这也是一开始引入mongo的初衷，其他类型的信息后面会放到库中。现在整个审计平台，除了连接pt工具的部分使用MySQL，其余都在MongoDB中。另外，MySQL库可以直接输出json格式的执行计划，存储非常方便；Oracle部分也形成了json格式的存储。

　　规则执行（执行计划）

　　

　　左侧是 Oracle 执行计划在 MongoDB 中的存储方式。其实就是将sqlplan字典数据插入到mongo中。右边是一个规则实现的例子，是一个基于mongo的查询语句。稍后我们将看到一个详细的示例。

　　7、平台实现

　　规则执行

　　

　　这是“大表全表扫描”规则的示例。以上是Oracle中数据字典保存的执行计划，以下是Mongo中保存的。可以看出是完全抄袭的。

　　

　　基于这种结构，如何实现规则过滤？其实是通过mongo中的find语句实现的。以下是该语句的执行步骤的详细说明。

　　规则执行（执行计划）

　　这部分是一个在 MySQL 中实现分层结果存储的例子。

　　

　　第一个图显示了原创的执行计划。

　　

　　第二张图是代码实现的总结。

　　

　　第三张图片是它实际保存在库中的样子。核心部分是item_level的生成。

　　规则定义（文本级别）

　　

　　第三种规则是基于文本的规则，它是描述 SQL 语句的文本特征的规则，与数据库类型无关。在实现上，通过常规的文本匹配或程序进行处理。其主要目的是规范开发者的 SQL 编写方式，避免复杂、性能差、不规范的 SQL 编写方式。

　　规则实现（文本级别）

　　

　　本节介绍如何实现文本规则。第一个示例 bad_join 是通过常规文本匹配实现的简单规则。第二个例子，sub_query，是通过程序判断括号的嵌套来判断子查询（或多级子查询）。

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

　　

　　最后一类规则是要素类的实现。这部分与数据库密切相关，过滤掉符合一定执行特征的语句。这些语句不一定是低效的，它们可能只是未来优化的重点，或者是一些优化收益最高的语句。主要有一些资源消耗等等。

　　8、系统管理

　　规则管理

　　

　　后来通过一些界面展示，介绍了平台的功能。

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

　　

　　对于已定义的规则，您可以在此处完成规则修改。主要配置规则状态、阈值、扣减项。

　　任务管理

　　

　　规则配置好后，任务发布的工作就可以在这里完成了。

　　以上是发布规则任务的界面。选择好数据源（ip、port、schema）后，选择审计类型和审计日期。目前审计数据源的计时策略仍然是以天为单位，所以不能选择今天的日期。

　　任务发布后，您可以在任务结果查看界面观察执行状态。根据审计类型、数据源对象的数量和语句的数量，审计的持续时间是可变的，一般在 5 分钟内。当审计工作状态为“成功”时，表示审计工作完成，可以查看或导出审计结果。

　　9、结果展示

　　对象审计结果概述

　　

　　上图是对象审计报告的示例。报告的开头是概览页面。在审计报告中集中展示各项规则和扣减；并通过饼图显示它们的比例。这使我们能够首先关注核心问题。

　　在顶部，您还可以观察到规则总分的显示。这是按照百分制将规则扣分换算得到的分数。分数越高，违规越少，审计对象的质量越高。“规则总分”的引入在设计之初就有些争议。我担心有这个指标会打击开发者的积极性，不利于平台的推广和使用。这里有几点可以说明。

　　对象审核结果详细信息

　　

　　这部分是对象审计的详细部分，对应每条规则的详细信息，可以在左侧的链接中进一步查看对象信息。由于篇幅限制，这里就不展示了。

　　执行计划评审结果概览

　　

　　这部分执行计划的概览显示，类似于对象的情况。也是每条规则的扣分。

　　执行计划审核结果详情

　　

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

　　

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

　　以上是一些通用的解决方案说明。此处描述了可能触发此类规则的情况和解决方案。相当于一个小知识库，方便开发者优化。之后在平台二期，我们会对引擎部分做更精准的优化，并且会扩充这部分。

　　下面是每条违规语句的状态，我们可以看到语句文本、执行计划、关联信息（比如这条规则的大表名）等，您也可以进一步点击语句展开信息。

　　

　　

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

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

　　10、我们遇到的坑

　　在实际开发过程中，遇到了很多问题。我们这里简单介绍两个，例如：

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

　　【Session进入睡眠状态，假死】

　　解决方法：在执行会话前设置wait_timtout=3，这个时间根据实际情况调整。

　　【数据量太大，很久没有结果】

　　session处于查询状态，但是数据量大或者因为数据库对format=json的支持不是很好，长时间解析不出来，会影响其他session。

　　解决方法：使用 pt-kill 工具终止会话。为了防止误杀，可以标记“eXplAin format=json”，然后使用pt-kill识别eXplAin关键字。

　　11、推进进程

　　

　　该平台在宜信运营以来，已经为多个系统提供了审计报告，大大加快了数据库结构和SQL优化的速度，缓解了DBA的日常工作压力。在工作实施过程中，我们也探索了一套实施方法。平台开源后，如果朋友使用，可以参考实现。

　　信息采集阶段

　　数据库系统的操作，掌握第一手资料。快速了解各业务系统质量，做好试点选型工作。

　　人工分析阶段

　　关键系统，人工干预分析。针对规则审核中暴露的核心问题，“点对面”，有针对性的分析优化报告。

　　沟通训练阶段

　　主动*敏*感*词*与开发团队沟通汇报情况。以分析报告为契机，可以根据需要对开发团队进行培训，身边的案例可以更有说服力。