“This is the 21st day of my participation in the August Text Challenge.

Xiaomi’s open source SOAR(SQL Optimizer And Rewriter) is an automatic tool for SQL optimization And rewriting. Developed and maintained by xiaomi artificial intelligence and cloud platform database team.

1. Functional features

Cross-platform support (support Linux, Mac environment, Windows environment theoretically also supported, SQL optimizations that currently support only MySQL syntax family protocols support statement optimizations based on heuristic algorithms support multi-column index optimizations for complex queries (UPDATE, INSERT, DELETE, SELECT support EXPLAIN information rich interpretation support SQL fingerprint, compression and beautification support the same table multiple ALTER requests merge support custom rules SQL rewriting

2. Comparison of other excellent products in the industry

SOAR sqlcheck pt-query-advisor SQL Advisor Inception sqlautoreview
Heuristic suggestion ✔ ️ ✔ ️ ✔ ️ ❌ ✔ ️ ✔ ️
Index recommendations ✔ ️ ❌ ❌ ✔ ️ ❌ ✔ ️
Query rewriting ✔ ️ ❌ ❌ ❌ ❌ ❌
Execution plan Display ✔ ️ ❌ ❌ ❌ ❌ ❌
Profiling ✔ ️ ❌ ❌ ❌ ❌ ❌
Trace ✔ ️ ❌ ❌ ❌ ❌ ❌
SQL Online Execution ❌ ❌ ❌ ❌ ✔ ️ ❌
The data backup ❌ ❌ ❌ ❌ ✔ ️ ❌

3. Installation and use

3.1 download soar execution

Github.com/XiaoMi/soar…

Download the SOAR binary installation package:

Wget https://github.com/XiaoMi/soar/releases/download/${tag} / soar execution. ${} OS - amd64 -o soar execution chmod + x a soar execution such as ` 0.11.0 ` download version: The wget https://github.com/XiaoMi/soar/releases/download/0.11.0/soar.linux-amd64 - O soar execution#Add executable permissions
chmod +x soar
Copy the code

If the download is slow, you can visit github.com/XiaoMi/soar… Download it and copy it

3.2 Installing the extension Library

Soar – PHP is a PHP extension package developed based on Xiaomi’s open source SOAR, which facilitates SQL statement tuning in the framework.

composer require guanguans/soar-php --dev
Copy the code

3.3 configuration

For more details, see Soar Config

Method 1. Initial configuration at runtime


      

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

use Guanguans\SoarPHP\Soar;

$config = [
    // The path to download soar
    '-soar-path'= >'/Users/yaozm/Documents/wwwroot/soar-php/soar.darwin-amd64'.// Test environment configuration
    '-test-dsn'= > ['host'= >'127.0.0.1'.'port'= >'3306'.'dbname'= >'database'.'username'= >'root'.'password'= >'123456',].// Log output file
    '-log-output'= >'./soar.log'.// Report output format: default markdown [markdown, HTML, json]
    '-report-type'= >'html',];$soar = new Soar($config);
Copy the code

Method 2. Initialize the configuration file

Create. Soar. Dist or. Soar in the vendor directory.


      
return [
    // The path to download soar
    '-soar-path'= >'/Users/yaozm/Documents/wwwroot/soar-php/soar.darwin-amd64'.// Test environment configuration
    '-test-dsn'= > ['host'= >'127.0.0.1'.'port'= >'3306'.'dbname'= >'database'.'username'= >'root'.'password'= >'123456',].// Log output file
    '-log-output'= >'./soar.log'.// Report output format: default markdown [markdown, HTML, json]
    '-report-type'= >'html',];Copy the code

Runtime initial configuration >.soar >.soar. Dist

Then initialize:


      

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

3.4 test

3.4.1 track SQL score

Method call:

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

Output result:

3.4.2 Explain information interpretation

Method call:

$sql = "SELECT * FROM `fa_auth_group_access` `aga` LEFT JOIN `fa_auth_group` `ag` ON `aga`.`group_id`=`ag`.`id`;";
// Output HTML format
echo $soar->htmlExplain($sql);
// Output md format
echo $soar->mdExplain($sql);
// Output HTML format
echo $soar->explain($sql.'html');
// Output md format
echo $soar->explain($sql.'md');
Copy the code

More references: github.com/guanguans/s…

3.4.3 ThinkPHP 6 framework

Consider wrapping it as function so that you can call the function directly

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

if(! function_exists('soar')) {

    function soar()
    {
        // sosem.php is the configuration file
        return \think\Facade::make(Soar::class, [config('soar')]); }}/** * SQL score */
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 information */
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 information */
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)); }}/** * syntax check */
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 fingerprint */
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 beautify */
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 converts to 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); }}/** * Execute any soar command */
if(! function_exists('soar_help')) {
    function soar_help()
    {
        returnsoar()->help(); }}Copy the code