Jenkins结合Soar实现自动化SQL审查

原创

浅时光博客

发布于 2022-09-11 11:48:33

5510

发布于 2022-09-11 11:48:33

文章被收录于专栏：云原生K8s云原生K8s

一、 Soar介绍

Soar是在2018年10月20日的开源先锋日上，由小米公布开源的项目，主要是通过智能检查SQL，并对SQL实现优化和改写的工具。

项目地址： https://github.com/XiaoMi/soar

说明：本文不会详细描述整个过程，只是把大致流程讲一下，将核心的代码贴出来，大家可自行根据自己的项目去改造。相关的 Jenkinsfile文件已经上传至本人的 GitHub仓库，可自行进行克隆下载；如果对您有帮助别忘记点个start

二、功能特点

跨平台支持（支持 Linux, Mac 环境，Windows 环境理论上也支持，不过未全面测试）
目前只支持 MySQL 语法族协议的 SQL 优化
支持基于启发式算法的语句优化
支持复杂查询的多列索引优化（UPDATE, INSERT, DELETE, SELECT）
支持 EXPLAIN 信息丰富解读
支持 SQL 指纹、压缩和美化
支持同一张表多条 ALTER 请求合并
支持自定义规则的 SQL 改写

三、安装部署

Soar的安装很简单，只需要将项目下载下来后，执行soar命令即可使用

[root@localhost ~]# wget https://github.com/XiaoMi/soar/releases/download/0.11.0/soar.linux-amd64 -O soar
 
#或者下载到本机PC再上传至服务器
[root@localhost ~]# mv soar.linux-amd64 soar
[root@localhost ~]# chmod +x soar
[root@localhost ~]# mv soar /usr/local/bin/
[root@localhost ~]# soar --version
Version: 2019-01-21 16:54:09 +0800 0.11.0-16-gc12ae96
Branch: master
Compile: 2019-01-21 16:55:46 +0800 by go version go1.10.7 linux/amd64
GitDirty: 0

四、使用测试

[root@localhost ~]# soar -query "select *  from tb;"
# Query: 09D1F5952A76399F
★ ★ ★ ☆ ☆ 75分
 
``sql
 
SELECT  
  * 
FROM  
  tb
``
 
## 最外层 SELECT 未指定 WHERE 条件
 
* **Item:**  CLA.001
 
* **Severity:**  L4
 
* **Content:**  SELECT 语句没有 WHERE 子句，可能检查比预期更多的行(全表扫描)。对于 SELECT COUNT(\*) 类型的请求如果不要求精度，建议使用 SHOW TABLE STATUS 或 EXPLAIN 替代。
 
## 不建议使用 SELECT * 类型查询
 
* **Item:**  COL.001
 
* **Severity:**  L1
 
* **Content:**  当表结构变更时，使用 \* 通配符选择所有列将导致查询的含义和行为会发生更改，可能导致查询返回更多的数据。

五、结合 Jenkins

1、安装插件

Jenkins需要安装插件 HTMLResource，这样才可以将报告通过html方式查看，安装Email Extension插件实现邮件通知

2、配置插件

Linux系统上安装Email服务

[root@localhost ~]# yum -y install sendmail
[root@localhost ~]# yum install -y mailx
[root@localhost ~]#  yum -y install jwhois

3、编写代码

//解决HTML显示问题
System.setProperty("hudson.model.DirectoryBrowserSupport.CSP","")
pipeline {
   agent { node {label "master"}}
   stages {
      stage("CreateSQLFile"){
         steps{
            script{
               sh "echo '${SQL};' > migrate.sql && cat migrate.sql"
            }
         }
      }
 
      stage("SoarSQL"){
         steps{
            script{
               sh """
                              #一般jenkins是普通用户运行的，所以把soar命令拷贝到当前jenkins运行用户的家目录下，防止权限问题导致命令无法执行
                   /home/deploy/soar -report-type html -query  v${ProjectName}-migrate.sql   > soarsql.html
                    cat soarsql.html
 
                    """
                publishHTML([allowMissing: false, alwaysLinkToLastBuild: false, keepAll: false, reportDir: '', reportFiles: ' soarsql.html', reportName: 'SQLreport', reportTitles: ''])
            
                Email("SQL审核完成 <a href='http://192.168.66.101:8080/ /view/${JOB_NAME}/job/${JOB_NAME}/SQLreport/'>审核报告</a>","${EmailUser}")  #Job传入email地址
            }
         }
      }
   }
}
 
 
 
//定义邮件内容
def Email(status,emailUser){
    emailext body: """
            <!DOCTYPE html> 
            <html> 
            <head> 
            <meta charset="UTF-8"> 
            </head> 
            <body leftmargin="8" marginwidth="0" topmargin="8" marginheight="4" offset="0"> 
                <img src="http://192.168.66.112/jenkins.jpg"> //logo地址
                <table width="95%" cellpadding="0" cellspacing="0" style="font-size: 11pt; font-family: Tahoma, Arial, Helvetica, sans-serif">   
                    <tr> 
                        <td><br /> 
                            <b><font color="#0B610B">构建信息</font></b> 
                        </td> 
                    </tr> 
                    <tr> 
                        <td> 
                            <ul> 
                                <li>项目名称：${JOB_NAME}</li>         
                                <li>构建编号：${BUILD_ID}</li> 
                                <li>构建信息: ${status} </li>                         
                                <li>项目地址：<a href="${BUILD_URL}">${BUILD_URL}</a></li>    
                                <li>构建日志：<a href="${BUILD_URL}console">${BUILD_URL}console</a></li> 
                            </ul> 
                        </td> 
                    </tr> 
                    <tr>  
                </table> 
            </body> 
            </html>  """,
            subject: "Jenkins-${JOB_NAME}项目构建信息 ",
            to: emailUser    
}