开源|性能优化神器：数据库初审平台Themis的选型与实践

　　开源|性能优化神器：数据库初审平台Themis的选型与实践

　　模块界定

　　

　　总结一下，平台主要是由上述四个模块组成：数据采集、规则解析、系统管理、结果展示。后面将针对不同模块的实现，进行详尽说明。

　　5、数据采集

　　采集内容

　　

　　先来瞧瞧数据采集模块。从表格可见，两种类型数据库的采集内容不同。

　　Oracle提供了较为丰富的信息，需要的基本都可采集到；MySQL功能相对能采集到的信息较少。

　　表格中的“对号+星号”，表示非定时作业完成，而是前面实时回库抓取的。下面简单说下，各部份的采集内容。

　　这些信息都将作为前面初审的根据。

　　采集原理

　　

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

　　6、规则解析

　　概要说明

　　下面介绍整个系统最为核心的部份—规则解析模块，它所完成的功能是根据定义规则，审核采集的数据，筛选出违背规则的数据。对筛选出的数据进行计分，并记录出来供后续生成初审报告使用。同时都会记录附加信息，用于辅助进行一些判定工作。

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

　　规则定义

　　

　　这是一个规则体的申明对象，我说明一下各数组涵义，大家也可对规则有个清晰的认识。

　　规则定义（对象级）

　　

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

　　例如：第一个的“大表过多”。表示一个数据库中的大表个数超过规则定义阈值。这里的大表又是通过规则输入参数来确定，参数包括表记录数、表化学规格。整体描述这个规则就是“数据库中超过指定规格或指定记录数的表的个数超过规定阈值，则触发初审规则”。其它对象的规则也类似。

　　规则实现（对象级）

　　

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

　　规则定义（执行计划级）

　　

　　第二类规则是执行计划类的规则，它也界定为若干类别。例如访问路径类、表间关联类、类型转换类、绑定变量类等。

　　以最为常见的的访问路径类为例，进行说明下。如最为常见的一个规则“大表扫描”。它表示的是SQL句子的执行中，执行了对大表的访问，并且访问的路径是采用全表扫描的方法。这个规则的输入参数，收录了对大表的定义（物理大小或记录数）；输出部份则包括了表名、表大小及附加信息（包括整个执行计划、指定大表的统计信息等内容）。

　　这类规则针对的数据源，是从线上数据库中抓取的。Oracle部份是直接从AWR中按时间段提取的，MySQL部份是使用explain命令返查数据库得到的。

　　信息储存格式

　　

　　在这里非常说明一下，在保存执行计划的时侯，使用了MongoDB这些文档性数据库。目的就是借助其schemaless特点，方便兼容不同数据库、不同版本执行计划的差别。都可以保存在一个集合中，后续的规则初审也是借助的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可从这种信息就可以做一些初步的优化判定工作。

　　此外，平台也提供了导入功能。可导入为excel文件，供用户下载查看。这里就展示了。

　　10、我们遇见的坑

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

　　MySQL在解析json格式执行计划中曝露出的问题…

　　【会话步入sleep状态，假死】

　　解决方式：执行会话之前设置wait_timtout=3,这个时间依据实际情况进行调整。

　　【数据量过大，长时间没有结果】

　　会话处于query状态，但是数据量很大或由于数据库对format=json支持不是挺好，长时间解析不下来，会影响其他会话。

　　解决方式：使用pt-kill工具杀掉会话。为了避免误伤，可打个标示“eXplAin format=json”，然后使用pt-kill辨识eXplAin关键字。

　　11、推进流程

　　

　　此平台在宜信公司运行以来，为好多系统提供了初审报告，大大推动了数据库结构、SQL优化的速率，减轻了DBA的日常工作压力。在工作施行过程中，我们也摸索了一套实行方式。该平台已开源后，如有同学使用，可参考施行。

　　采集信息阶段

　　海量搜集公司的数据库系统的运行情况，掌握第一手资料。快速了解各业务系统的质量，做好试点选择工作。

　　人工剖析阶段

　　重点系统，人工介入剖析。根据规则初审中曝露出的核心问题，“以点带面”，有针对性的给出剖析及优化报告。

　　交流培训阶段

　　主动*敏*感*词*，跟开发团队沟通交流报告情况。借剖析报告的机会，可对开发团队进行必要的培训工作，结合她们身边的案例，更具有劝说作用。

　　反馈改进阶段

　　落实交流的成果，督促其改进。通过初审平台定期反馈改进质量。有一定基础的团队，可开发平台，供开发人员自己使用。使SQL质量问题，不再仅仅是DBA的问题，而和项目中的每位人都有关系。