供应信息和文章都能优化的采集软件(Oracle模块划分总结一下（二）：数据采集、规则解析)

　　供应信息和文章都能优化的采集软件(Oracle模块划分总结一下（二）：数据采集、规则解析)

　　模块划分

　　

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

　　5、数据采集

　　采集内容

　　

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

　　Oracle 提供了丰富的信息。基本上可以采集搞定；MySQL 的功能相对较少，可以采集。

　　表格中的“对号+星号”表示非定时作业已完成，但稍后会实时取回图书馆。下面简单说一下采集各部分的内容。

　　此信息将用作后续审核的基础。

　　采集原理

　　

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

　　6、规则分析

　　概要说明

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

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

　　规则定义

　　

　　这是规则体的声明对象。让我解释一下每个字段的含义。您也可以清楚地了解规则。

　　规则定义（对象级别）

　　

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

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

　　规则实现（对象级）

　　

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

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

　　

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

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

　　这些规则针对的数据源是从在线数据库中获取的。Oracle部分直接按时间段从AWR中提取，MySQL部分使用explain命令返回数据库获取。

　　信息存储格式

　　

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

　　规则实现（执行计划）

　　

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

　　7、平台实现

　　规则实现

　　

　　以“大表全表扫描”规则为例进行说明。上面是保存在Oracle中数据字典中的执行计划，下面是存放在Mongo中。可以看出，它被完全复制了。

　　

　　基于这种结构，如何实现规则过滤？其实是通过mongo中的find语句实现的。下*敏*感*词*体解读一下这个语句的执行步骤。

　　规则实现（执行计划）

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

　　

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

　　

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

　　

　　第三张图是图书馆里的实际样子。核心部分是item_level的生成。

　　规则定义（文本级别）

　　

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

　　规则实现（文本级）

　　

　　这部分描述了文本规则的实现。第一个示例 bad_join 是一个简单的规则，通过常规文本匹配实现。第二个例子，sub_query，就是通过程序判断括号的嵌套来完成对子查询（或多级子查询）的判断。

　　规则定义（执行特征级别）

　　

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

　　8、系统管理

　　规则管理

　　

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

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

　　

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

　　任务管理

　　

　　配置好规则后，就可以在这里完成任务下达工作。

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

　　任务发布后，可以在任务结果查看界面观察执行情况。根据审计类型、数据源对象数、句子数等不同，审计时长不定，一般在5分钟以内。当审核作业状态为“成功”时，表示审核作业完成，可以查看或导出审核结果。

　　9、结果展示

　　对象审查结果概览

　　

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

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

　　对象审计结果详情

　　

　　这部分是对象审计的详细部分，对应每个规则的详细信息，可以在左边的链接中进一步查看对象信息。由于篇幅所限，我们不再展示。

　　实施计划审查结果概览

　　

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

　　实施计划审查结果详情

　　

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

　　

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

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

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

　　

　　

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

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

　　10、我们遇到的坑

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

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

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

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

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

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

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

　　11、 推广流程

　　

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

　　信息采集阶段

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

　　人工分析阶段

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

　　沟通训练阶段

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

　　反馈改进阶段

　　落实交流成果，督促改进。通过审核平台定期反馈以提高质量。有一定基础的团队可以开发一个平台供开发者使用。SQL 质量问题不再只是 DBA 的问题，而是与项目中的每个人都有关系。