基于开源SQLE的SQL质量管控方案

基于开源SQLE的 SQL质量管控方案 演讲人：张沈波 开源生态 分布式数据库项目DBLEhttps://github.com/actiontech/dble 数据传输项目DTLEhttps://github.com/actiontech/dtle 金融级分布式事务TXLEhttps://github.com/actiontech/txle SQL审核项目SQLEhttps://github.com/actiontech/sqle 2 更多信息请访问https://opensource.actionsky.com/ SQLE基础功能1：SQL质量数字化展示，SQL质量可视化 实时工单，SQL质量量化查看 每张审核工单质量（通过率和评分）量化展示 历史工单，SQL质量分析 工单历史质量分析，可按时间、业务、开发人员等维度查看，开发质量提升情况一目了然 报表统计，多维报表展示 工单审核时间/上线时间/审核通过率/上线成功率/业务或业务用户工单情况/不同数据源工单情况 SQLE基础2：规则集成专家经验、丰富灵活 为每个业务自定义规则模版每条规则可禁用/启用 特殊SQL可设置白名单 规则参数和等级可按需调整 SQLE基础3：高效审核，无锁发布（onlineDDL） 自动审核 平台自动审核后反馈审核结果，DBA复审即可，审核效率高 审核结果分级 error(必须整改)、notice（建议整改）、warn（开发建议），减少跨部门沟通成本，整改效率高 审核结果去重 SQLE根据SQL指纹去重，同类SQL去重展示，复 审效率高 审核报告下载 审核报告、SQL语句均可下载，内部流转效率高 无锁发布 手动上线/自动上线/定时上线 大表自动调用gh-ost工具实现无锁上线 6 SQLE基础4：审核上线流程可编排自定义，无缝衔接内部流程 审核与上线流程根据实际场景按需自定义，满足企业内部不同流程管理要求 7 DDL优化建议 •createdatabase：必须加ifnotexists •createtable：库必须存在，字段/索引/约束等不能重复，表必须加ifnotexists •altertable：库表必须存在，字段（新增/ 修改）不能重复，主键只能有一个 DML优化建议 索引优化建议 • • insert语句必须指定columndelete/update语句必须有limit条件 delete/update语句不能有orderby 禁止使用全模糊搜索或左模糊搜索 • • • 索引个数建议不超过阈值 禁止将blob类型的列加入索引 建议为某列增加索引 • • SQLE基础5：SQL优化，反馈优化建议，提高优化能力 索引优化流程 SQL解析—语法语义分析—执行计划分析—优化分析—子查询优化分析—索引优化建议—建议核对—形成优化建议分析：t1表200万行，c2无索引，需要回表，影响性能。建议：为c1，c2建立索引 SQLE基础6：SQL分析下钻分析，提升优化效率 8 SQLE基础功能7:SQL工作台方便业务开发快速数据检索及了解数据库 业务开发人员安全、快速、高效数据检索 SQLE与CloudBeaver集成，用户权限体系集成，双系统统一登录 所有的SQL操作通过SQLE审核管控，自动拦截不合规SQL，提供SQL审核建议 9 开发人员 一般意义上的SQL审核流程 提交SQL SQLE审核平台 系统自动给 出分析结果 以上常见功能： 报表视图 审核规则 工单流程 上线/onlineDDL 是否修改 否 是否通过 是 是否一致 是 是否通过 DBA执行上线 是 是 DBA审批 否 SQL复查 开发人员进行对比 OA流程审批 否 否 修改SQL OA流程提交的上线单 取消上线 SQL调优 下钻分析 统一访问 落地的痛点： 1.开发配合度差 提升了大量的显示工作量 没有看到显示收益 2.SQL审核不够全面 一般只能在生产变更的时候被用到 3.SQL审核是个定制化非常强的工具 需要嵌入到内部系统/流程 审核规则和规范有特殊性 10 SQL治理需要考虑SQL全生命周期 三方统计数据显示：49.4%的数据库故障是由于SQL不规范、效率低等问题引起 SQL生命周期 开发测试人员 运维人员 需求创 生产 建 代码开 发 持续集 成 创建镜 像 测试环境部署测试 UAT环境部署测试 生产部署 上线 生产运行 开发 测试 代码开发代码测试发布上线 生产运维 SQL 整改效率 几小时，不影响生产 几天，不影响生产 几天或几周，影响项目周期 几周或几个月，影响生产 问题越早解决成本越低！ SQL全生命周期质量管理是解决在开发、测试、发布上线、生产环境中低效SQL发现及优化遇到的问题，达到提升SQL质量的目的。 需求创建 代码开发 代码开发 SQL文件 SQL语句Mybatis/iBatis文件 IDE集成 SQLE-Jetbrains-Plugin SQLE 审核 审核结果/优化建议 创建镜像 …… 审核上线 赋能开发：脚本/IDE审核，开发随写随审，提升开发效率 脚本审核：支持多种SQL文件获取方式IIDE集成审核 提升效率：2个工作日SQL审核等待分钟级完成12 赋能开发，拉齐开发能力，推动企业开发规范落地 持续集成系统能力增强：CI/CD持续集成流程自动审核 ——可适配所有CI/CD持续集成系统 CI/CD持续集成（Jenkins集成） 需求创 建 代码 代码开提交 发 代码仓库git 自动 线 Build SQL 推送 SQLE 审核 审核通过 生成制品 ……审核上 CI/CD持续集成系统能力增强 SQL提取问题SQL/优化建议 提早发现问题SQL 全量、增量审核，提升审核效率 •jenkins集成无缝对接现有流程，增强持续集成系统能力，不改变使用习惯，减低推广难度 •开发阶段及早发现问题SQL，及时整改，避 免问题SQL流向生产 •支持扫描配置路径下的全量SQL与变化SQL 审核 14 测试审核优化：抓取java应用程序运行SQL，审核分析，发现问题SQL SQL治理流程 SQL实时获取与审核 审核结果 （质量报 告） 预警 SQL优化 优化建议反馈开发协同优化 客户端工具 SQL获取 分优先级反馈 SQLE 质量 审核 数据库管理员 SQL获取插件 java应用程序采集审核 1.SQLE通过其客户端程序实时获取java APP SQL获取插件 应用程序中运行的SQL语句 2.定期审核：使用语法解析器解析SQL语句，获取SQL执行计划 3.基于规则和执行计划判断SQL是否存在风险。 测试数据库 RoadMap：自动阻断 AP业务和客户端中不符合标准规范的SQL 自动阻断，降低对生产/测试库的影响 生产审核优化：数据库审核获取运行SQL，审核分析，定位问题SQL 生产库SQL获取与审核审核结果 （质量报告） APP 预警 （发给相关人 员） 问题SQL优化 优化建议反馈开发协同优化 生产数据库 运行SQL采集获取 SQLE 分优先级反馈 数据库管理员 TOPSQL采集审核 1.根据采集周期SQLE定期获取数据库TOPSQL语句 2.使用语法解析器解析SQL语 句，获取SQL执行计划 3.基于规则和执行计划判断SQL是否存在风险。 慢日志采集审核 1.SQLE通过其客户端程序实时获取数据库慢日志SQL语句 2.定期审核：使用语法解析器解析SQL语句，获取SQL执行计划 3.基于规则和执行计划判断SQL是否存在风险。 审计日志采集审核 1.SQLE通过客户端程序实时获取数据库慢日志SQL语句 2.定期审核：使用语法解析器解析SQL语句，获取SQL执行计划3.基于规则和执行计划判断SQL是否存在风险。 库表元数据审核 1.根据采集周期SQLE定期获取数据库库表元数据信息 2.使用语法解析器解析SQL语句，获取SQL执行计划 3.基于规则和执行计划判断SQL是否存在风险。 云数据库审核 1.SQLE调用RDS的慢日志、 审计日志等API获取运行SQL信息2.SQLE使用语法解析器解析SQL语句，获取SQL执行计划 3.基于规则和执行计划判断SQL是否存在风险。 标准发布：上线工单，变更发布标准化，杜绝问题SQL流向生产 变更SQL上线发布流程 SQLE 预审 是是SQLE 否通上线工单 过 上线审核流程 是否通过 否 变更 取消 SQLE 上线 SQL执行 变更SQL否 审核结果、审批 回滚语句通过 手动/自动执行 执行失败执行 回滚 16 开发人员运维人员 发布效率提升，发布留痕 变更SQL一键执行上线，提升发布效率手动上线/定时上线 发布留痕，可追溯 建立标准化发布流程 所有发布必须通过平台执行，线下人情上线减少100%，问题SQL引起的性能问题减少60% SQLE 审核 赋能开发 •IDE审核 •脚本审核 （SQL语句、文本、 MyBatis） • 测试审核 CI/CD持续集成审核 慢日志审核 TOPSQL审核 审计日志审核 API审核 java应用审核 • • • • 标准发布 自动上线发布手动上线发布对接发布流程无锁发布 多库发布 多区域发布多环境发布 生产审核优化 审核结果可视化 系统集成对接 • • 慢日志审核 TOPSQL审核 审计日志审核 API审核 java应用审核 • • SQL风险展示 SQL分析优化SQL下钻分析图表展示 报表展示 • • • • • • • • • • • • • • • • • • openAPI 用户系统对接 资产管理系统对接 监控告警系统 对接 智能运维平台 • SQLE能干更多 SQLERoadMap https://github.com/actiontech/sqle Feature: •更多类型的数据库 •更多审核规则 •更多的审核场景 V2.0 版本 •审核规则引擎（自定义规则） •项目管理 •集群模式 THANKYOU!