soar-php SQL语句优化与重写的自动化工具

“这是我参与8月更文挑战的第21天，活动详情查看：8月更文挑战”

小米公司开源的 SOAR(SQL Optimizer And Rewriter) 是一个对 SQL 进行优化和改写的自动化工具。由小米人工智能与云平台的数据库团队开发与维护。

1、功能特点

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

2、业内其他优秀产品对比

	SOAR	sqlcheck	pt-query-advisor	SQL Advisor	Inception	sqlautoreview
启发式建议	✔️	✔️	✔️	❌	✔️	✔️
索引建议	✔️	❌	❌	✔️	❌	✔️
查询重写	✔️	❌	❌	❌	❌	❌
执行计划展示	✔️	❌	❌	❌	❌	❌
Profiling	✔️	❌	❌	❌	❌	❌
Trace	✔️	❌	❌	❌	❌	❌
SQL在线执行	❌	❌	❌	❌	✔️	❌
数据备份	❌	❌	❌	❌	✔️	❌

3、安装与使用

3.1 下载soar

github.com/XiaoMi/soar…

下载soar二进制安装包：

wget https://github.com/XiaoMi/soar/releases/download/${tag}/soar.${OS}-amd64 -O soar
chmod a+x soar

如`0.11.0`版本的下载方式：
wget https://github.com/XiaoMi/soar/releases/download/0.11.0/soar.linux-amd64 -O soar
# 添加可执行权限
chmod +x soar

如果下载慢，可以通过浏览器访问github.com/XiaoMi/soar…直接下载后，再拷贝过去

3.2 安装扩展库

soar-php 是一个基于小米公司开源的 soar 开发的 PHP 扩展包，方便框架中 SQL 语句调优。

composer require guanguans/soar-php --dev

3.3 配置

更多详细配置请参考 soar config

方法一、运行时初始化配置

<?php

require_once __DIR__.'/vendor/autoload.php';

use Guanguans\SoarPHP\Soar;

$config = [
    // 下载的 soar 的路径
    '-soar-path' => '/Users/yaozm/Documents/wwwroot/soar-php/soar.darwin-amd64',
    // 测试环境配置
    '-test-dsn' => [
        'host' => '127.0.0.1',
        'port' => '3306',
        'dbname' => 'database',
        'username' => 'root',
        'password' => '123456',
    ],
    // 日志输出文件
    '-log-output' => './soar.log',
    // 报告输出格式: 默认  markdown [markdown, html, json]
    '-report-type' => 'html',
];
$soar = new Soar($config);

方法二、配置文件初始化配置

vendor 同级目录下新建 .soar.dist 或者 .soar，内容参考 .soar.example，例如：

<?php
return [
    // 下载的 soar 的路径
    '-soar-path' => '/Users/yaozm/Documents/wwwroot/soar-php/soar.darwin-amd64',
    // 测试环境配置
    '-test-dsn' => [
        'host' => '127.0.0.1',
        'port' => '3306',
        'dbname' => 'database',
        'username' => 'root',
        'password' => '123456',
    ],
    // 日志输出文件
    '-log-output' => './soar.log',
    // 报告输出格式: 默认  markdown [markdown, html, json]
    '-report-type' => 'html',
];

运行时初始化配置 > .soar > .soar.dist

然后初始化：

<?php

require_once __DIR__.'/vendor/autoload.php';
use Guanguans\SoarPHP\Soar;
$soar = new Soar();

3.4 测试

3.4.1 SQL 评分

方法调用：

$sql ="SELECT * FROM `fa_user` `user` LEFT JOIN `fa_user_group` `group` ON `user`.`group_id`=`group`.`id`;";
echo $soar->score($sql);

输出结果：

3.4.2 explain 信息解读

方法调用：

$sql = "SELECT * FROM `fa_auth_group_access` `aga` LEFT JOIN `fa_auth_group` `ag` ON `aga`.`group_id`=`ag`.`id`;";
// 输出 html 格式
echo $soar->htmlExplain($sql);
// 输出 md 格式
echo $soar->mdExplain($sql);
// 输出 html 格式
echo $soar->explain($sql, 'html');
// 输出 md 格式
echo $soar->explain($sql, 'md');

更多参考：github.com/guanguans/s…

3.4.3 thinkphp 6 框架

可以考虑封装成 function，这样直接调用函数就行

use think\facade\Db;
use Guanguans\SoarPHP\Soar;

if (!function_exists('soar')) {

    function soar()
    {
        //soar.php 为配置文件
        return \think\Facade::make(Soar::class, [config('soar')]);
    }
}

/**
 * SQL 评分
 */
if (!function_exists('soar_score')) {
    function soar_score($sql = null)
    {
        return null === $sql ? soar()->score(str_replace('`', '', Db::getLastSql())) :
            soar()->score(str_replace('`', '', $sql));
    }
}

/**
 * explain 信息解读
 */
if (!function_exists('soar_md_explain')) {
    function soar_md_explain($sql = null)
    {
        return null === $sql ? soar()->mdExplain(str_replace('`', '', Db::getLastSql())) :
            soar()->mdExplain(str_replace('`', '', $sql));
    }
}

/**
 * explain 信息解读
 */
if (!function_exists('soar_html_explain')) {
    function soar_html_explain($sql = null)
    {
        return null === $sql ? soar()->htmlExplain(str_replace('`', '', Db::getLastSql())) :
            soar()->htmlExplain(str_replace('`', '', $sql));
    }
}

/**
 * 语法检查
 */
if (!function_exists('soar_syntax_check')) {
    function soar_syntax_check($sql = null)
    {
        return null === $sql ? soar()->syntaxCheck(str_replace('`', '', Db::getLastSql())) :
            soar()->syntaxCheck(str_replace('`', '', $sql));
    }
}

/**
 * SQL 指纹
 */
if (!function_exists('soar_finger_print')) {
    function soar_finger_print($sql = null)
    {
        return null === $sql ? soar()->fingerPrint(str_replace('`', '', Db::getLastSql())) :
            soar()->fingerPrint(str_replace('`', '', $sql));
    }
}

/**
 * SQL 美化
 */
if (!function_exists('soar_pretty')) {
    function soar_pretty($sql = null)
    {
        return null === $sql ? soar()->pretty(str_replace('`', '', Db::getLastSql())) :
            soar()->pretty(str_replace('`', '', $sql));
    }
}

/**
 * markdown 转化为 html
 */
if (!function_exists('soar_md2html')) {
    function soar_md2html($markdown)
    {
        return  soar()->md2html($markdown);
    }
}

/**
 * soar 帮助
 */
if (!function_exists('soar_exec')) {
    function soar_exec($command)
    {
        return soar()->exec($command);
    }
}

/**
 * 执行任意 soar 命令
 */
if (!function_exists('soar_help')) {
    function soar_help()
    {
        return soar()->help();
    }
}