背景:

事故频发

•每年都会看到 SQL 问题引发的线上问题

不易发觉

•对于 SQL 性能问题测试在预发环境不易发现

•saas 系统隔离字段在 SQL 条件中遗漏，造成越权风险

•业务初期 SQL 没问题，业务增长容易出现事故

•DBS 慢 SQL 不支持实时报警，无法及时发现

•靠大家 review 代码总会出现遗漏

事后处理

•每次都是线上接口性能、数据库报警才意识到问题，再去优化 SQL，此刻有可能引发线上的严重事故

思考：

通过人为去发现总是不靠谱的，而且更希望问题在测试和预发环境提前暴漏出来，尽量避免带到线上，是否可以通过技术手段提前发现问题？研发新工具来自动检测有问题的 SQL？

流程设计：

行动：

通过开发 SQL 巡检检插件查实现问题 SQL 自动预警

1.利用 SQL 拦截器，拦截系统执行的 SQL

2.开启异步线程池，不阻碍业务流程的执行，解析 SQL，忽略具体入参数据和格式，MD5 加密 SQL 语句，为了防止重复 SQL 执行，将之前拦截过的 MD5 值缓存，可以自定义缓存时间，这段时间内容不会解析相同的 SQL

3.为了保障业务系统的稳定性，接入插件的时候支持手动数据源的注入，可以选择主或者从，来执行后续的 explain/show create table 操作

4.通过 explain/show create table 执行的结果，以及 SQL 语句通过 http/MQ 发送给 SQL 巡检平台

5.SQL 巡检平台接受信息进行内容拆分，获取表名和条件；

6.首先通过执行计划分析:如：[possible_keys][key]分析索引是否使用，如未使用会及时预警通知,并记录到巡检平台；

7.其次进行表和查询条件分析，通过读取平台的配置，设置某一个表的查询条件的校验规则（支持正则表达），如:xxx_info 表条件必须使用 xxx_code,如不符合规则也会及时预警通知，并记录到巡检平台；

SQL风险预警

【描 述】SQL安全检测-table_name(表名)不符合条件规则:.*org_no.* (正则表达式) 【traceId】wewrerew234234242342 (请求ID) 【执行方法】com.XXX.XXX.XX.FINDBYID(mapper方法) 【SQL内容】select * from table_name where xxx=1 and yyy=2 【系统名称】所属系统

SQL风险预警SQL 事前巡检插件【描 述】SQL索引检测-table_name(表名)未使用索引; 【traceId】aa6ac6c89bec4f7dfdfdf74719ae583 (请求 ID) 【执行方法】XXXXXMapper.selectResult (mapper 方法) 【SQL内容】select * from table_name where xxx=1 and yyy=2 【系统名称】所属系统

1.巡检平台提供了一些报警阈值管理、校验规则管理等，来满足不同系统的不同表的不同要求

2.巡检平台同时会把有问题的 SQL 进行展示，支持一键分析，因为之前咱们已经获取到执行计划结果和建表语句，把这些信息交给 chatgpt，通过大模型分析，并返回响应的建议，辅助用户进行治理

接入：

引入 SQL 巡检 jar 包，在数据源注册拦截器

</property>        <property name="plugins">            <list>                <bean class="com.yzt.plugin.MysqlExplainInterceptor">                    <property name="sysName" value="yzt-refund"/>                    <property name="monitorSqlService" ref="monitorSqlServiceImpl"/>                </bean>            </list>        </property>

指定重复 SQL 拦截时间段

@Overridepublic boolean warnFLag(String id) {//缓存实现指定时间重复SQL上报拦截    return false;}............

在我们的巡检平台根据配置的系统名称来自定义报警人和报警规则；

通过自动巡检、及时预警能提前在测试预发环境发现 SQL 存在的问题，进行修复，避免带到线上，同时可以给出问题 SQL 的优化建议，帮助研发快速修复；