用 Jenkins 集成 Soar 做自动化 SQL 审核-星海夜航

SQL 上线前做一轮审核是常见的质量卡点。Soar 是小米开源的 SQL 优化与改写工具，把它挂到 Jenkins 流水线里，就能在建表/迁移脚本提交后自动出审核报告并邮件通知。下面记一套落地做法。

一、Soar 是什么

Soar（SQL Optimizer and Rewriter）通过分析 SQL、按内置规则给优化建议并做自动改写，帮开发和 DBA 提升查询效率、减少全表扫描这类问题。特点大致有：智能分析给建议、自动改写利用索引、查询质量检查、规则可定制扩展，支持 MySQL/MariaDB/TiDB 等，同时有命令行和图形界面。项目在 GitHub 的 XiaoMi/soar。

二、安装部署

Soar 是单个可执行文件，下载后加执行权限放进 PATH 就能用：

[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

三、先本地测一下

用 soar -query 直接审一条 SQL，看它给出的评分、命中规则和优化建议，确认工具能跑：

[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

先装两个插件：HTMLResource，用来把报告以 HTML 方式查看；Email Extension，用来做邮件通知。

邮件这块要在系统里准备好发邮件的服务：

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

然后是流水线脚本。第一步把待审 SQL 写成 migrate.sql，第二步调 Soar 以 HTML 报告输出、用 publishHTML 发布，再发一封带报告链接的邮件。注意 Jenkins 一般用普通用户跑，把 soar 命令放到该用户家目录下避免权限问题。脚本开头那句 System.setProperty("hudson.model.DirectoryBrowserSupport.CSP","") 是为了解决 HTML 报告的 CSP 显示限制：

//解决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    
}

构建时若报告样式加载不出来，是因为 Jenkins 默认不允许外部 CSS/脚本。按提示进到那一项，选择允许运行脚本即可：