专15-1-金海锋：平安数据库SQL审核平台(DTCC)

平安数据库SQL审核平台金海锋定的应用开发经验，掌握多一次生产故障引发的思考前台大量业务功能无法使用，主机告警，CPU使用一个高频SQL因隐式转换导致没有使用到高效索引，紧急固化执行计划后故障解除。为何开发没有按照我们要求的规范来编写SQL？为何测试阶段没有发现问题？如何再次避免这样的问题发生？DevOps=Development+Operations+QA人是不可靠的环境存在差异效率低下人是不可靠的环境存在差异效率低下容易有漏网之鱼石器时代进化借助工具，进入铁器时代消除人的不可靠因消除人的不可靠因素消消除环境的影响提提升效率大幅不易产生遗漏不易产生遗漏2011年CodeXpert2012年DBAudit2015年SQLAuditMySQL自助扫描平MySQL自助扫描数数据库审计平台Oracle生产审计自助扫描自动报告移交审核自助扫描测试审核自助扫描月度审计自动报告OracleOracle月度审计自动报告MySQLMySQL移交审核自助扫描SQLAuditDBAuditCodeXpert为什么要对应用SQL做审计SQL的来说还是偏差。Ø部分高压力系统因SQL代码质量问题出现生产故障。Ø提升开发人员的责任意识。绩效报告PROD代码库绩效报告PROD代码库ITIL文档规则集SSQLAUDITSQL扫描WEB展示审计报告数据库SQL捕获器调度器审核结果集SQL基线基线分析SQL整改数据库SQL捕获器调度器审核结果集SQL基线基线分析SQL整改解解析引擎规则引规则引擎基线日报基线月报ØSQLAudit工具核心模块为解析引擎和规则引擎。Ø解析引擎用于识别并解析各类的SQL语句，输出SQL树形解析结果。SQL的必改项违规扫描。DML-U002DML-U002DML-D001ØSQLAuditforOracle的审计规则集如下，并设置了必改项规则(黄色高亮所示)，并已将必改项报告添加到开发、测试、生产的审计过程中。类型分类规则号检查点说明是否必改项执行计划扫描索引PLAN-DML-I001索引全扫执行计划里有INDEXFULLSCAN操作，每次扣NPLAN-DML-I002索引跳跃执行计划里有INDEXSKIPSCAN操作，每次扣NPLAN-DML-I003全表扫描对于大于200M的表全表扫描，执行计划里有TABLEACCESSFULL操作，每次扣0.5分。Y连接PLAN-DML-J001笛卡尔积缺少连接条件，导致SQL使用笛卡尔积的方式进行连接，每句SQL扣1分。YPLAN-DML-J002嵌套连接过深执行计划中嵌套连接深度超过6层，每句SQL扣1N绑定变量PLANDML-B001绑定变量未使用绑定变量的SQL，每句SQL扣0.2分。NPLAN-DML-B002隐式转换在查询列上发生隐式转换(包括良性的)，每次扣0.5分。YSQL扫描通用SQLA-DML-G001禁止使用select*禁止使用select*，必须明确选择所需的列，每句SQL扣0.5分。NSQLA-DML-G002禁止出现重复查询子句可以使用withas替换子句来提升SQL语句执行效率，每句SQL扣0.5分。NSQLA-DML-G003索引列函数在查询索引列上发生函数调用(函数索引除外)，每次扣0.5分。N更新SQLA-DML-U001update禁止出现orderby子句防止DML过程出现不必要的排序，每句SQL扣NSQLAupdate必须出现where子句防止DML过程出现全表锁，每句SQL扣1分。YSQLA-DML-U003禁止更新主键列值不允许有SQL更新主键，每次SQL扣1分。N查询SQLA-DML-S001禁止嵌套select子句防止出现select子句的嵌套子查询，避免出现性能问题，每句SQL扣0.5分。N删除SQLAdelete必须出现where子句防止DML过程出现全表锁，每句SQL扣1分。YSQLA-DML-D002delete禁止出现orderby子句防止DML过程出现不必要的排序，每句SQL扣NØSQLAuditforMySQL审计规则列表如下。类型分类规则号检查点说明SQL扫描通用SQLA-DML-G001禁止使用select*禁止使用select*，必须明确选择所需的列。SQLA-DML-G002禁止3个表以上的连接禁止3个表以上的连接，避免出现性能问题。SQLA-DML-G003禁止嵌套子查询防止出现子查询嵌套子查询，避免出现性能SQLA-DML-G004where子句必须包含主键或索引防止出现DML操作时的全表锁，防止SELECT过程出现慢查询。SQLA-DML-G005禁止where子句出现索引列函数防止索引失效。SQLA-DML-G006禁止执行计划无索引扫描禁止执行计划无索引扫描，避免出现性能问查询SQLA-DML-S001禁止select子句嵌套select子句防止出现select子句的嵌套子查询，避免出现性能问题。插入SQLA-DML-I001必须指定插入列表必须是这样的格式：insertinto<tablename>(…)values(…)。SQLA-DML-I002禁止使用子查询更新SQLA-DML-U001必须出现where子句防止DML过程出现全表锁。SQLA-DML-U002禁止出现orderby子句防止DML过程出现不必要的排序。SQLA-DML-U003禁止更新主键列值不允许有SQL更新主键。删除必须出现where子句防止DML过程出现全表锁。禁止出现limit子句防止DML过程出现不必要的排序。禁止出现orderby子句防止DML过程出现不必要的排序。SQLAudit在开发测试环境如何保证准确性？PRPRODSSQLAUDITSQLAudit的贡献uSQL。u实现生产0故障的一大重要手段。SQLAuditDBAuditCodeXpertVIEW等数据库对象的创建、删除、修改。DBAUDIT代码上传代码扫描WEB展示DBAUDIT代码上传代码扫描WEB展示 开发测试演练代码库PRODDBAUDIT代码上传代码扫描WEB展示DBAUDIT代码上传代码扫描WEB展示 开发测试演练代码库PRODu文档化建模规范，根据规范文档生成相关规则集，并注入到DBAUDIT工具。uDBAUDIT工具贯彻开发、测试、上线三个阶段。开发测试阶段，违规项和整改建议将返回再次开发或者申请例外处理；上线阶段，生成当月绩效报告，分析讨论后反馈KPI绩效报告。ITIL文档建模规范ITIL文档规则集绩效报告DBAUDIT绩效报告代码提取自动扫描WEB展示在有DBAudit之前，DB版本的审核工作是DBA的一大负担。Ø非常规范的DB规范(意味着规范的数量很多)。有DBAudit后Ø节省大量的人力，把有限的人力投人到更有意义的工作中去。Ø更好的把控DB版本质量。Ø对于历史的违规项，可以做到有选择性SQLAuditDBAuditCodeXpert代码库代码库u文档化SQL和PLSQL开发规范，根据规范文档生成相关规则集，并注入到CodeAnalysis工具。uCodeAnalysis工具主要针对线上代码质量检查，同时也开放接口给开发者自行检查，并生成当月绩效报告，分析讨论后反馈KPI绩效报告。ITIL文档开发规范ITIL文档规则集CodeCodeA