6 Star 44 Fork 8

guanguans / soar-php

加入 Gitee
与超过 1200万 开发者一起发现、参与优秀开源项目,私有仓库也完全免费 :)
免费加入
克隆/下载
贡献代码
同步代码
取消
提示: 由于 Git 不支持空文件夾,创建文件夹后会生成空的 .keep 文件
Loading...
README
MIT

soar-php

简体中文 | ENGLISH

SQL 优化器、重写器。

soar-php 是一个基于小米公司开源的 soar 开发的 SQL 优化器、重写器(辅助 SQL 调优)。

tests check & fix styling codecov Total Downloads Latest Stable Version License

环境要求

  • PHP >= 7.1
  • ext-json
  • ext-mbstring
  • ext-pdo

框架中使用

安装

$ composer require guanguans/soar-php -vvv

使用

创建 soar 实例
<?php

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

use Guanguans\SoarPHP\Soar;

$soar = Soar::create();

/** 配置选项参考 @see soar.config.example.php */
// $soar->setSoarPath('自定义的 soar 路径')
//     ->setOptions([
//         // 测试环境配置
//         '-test-dsn'    => [
//             'host'     => '127.0.0.1',
//             'port'     => '3306',
//             'dbname'   => 'database',
//             'username' => 'root',
//             'password' => '123456',
//             'disable'  => false,
//         ],
//         // 日志输出文件
//         '-log-output'  => __DIR__ . '/logs/soar.log',
//         // 报告输出格式: [markdown, html, json, ...]
//         '-report-type' => 'html',
//     ]);
SQL 评分
$sql ="SELECT * FROM `fa_user` `user` LEFT JOIN `fa_user_group` `group` ON `user`.`group_id`=`group`.`id`;";
echo $soar->score($sql);

$sql = 'SELECT * FROM users LEFT JOIN post ON users.id=post.user_id; SELECT * FROM post;';
echo $soar->jsonScore($sql);
echo $soar->arrayScore($sql);
echo $soar->htmlScore($sql);
echo $soar->mdScore($sql);

[
    {
        "ID": "628CC297F69EB186",
        "Fingerprint": "select * from users left join post on users.id=post.user_id",
        "Score": 85,
        "Sample": "SELECT * FROM users LEFT JOIN post ON users.id=post.user_id",
        "Explain": [
            {
                "Item": "EXP.000",
                "Severity": "L0",
                "Summary": "Explain信息",
                "Content": "| id | select\\_type | table | partitions | type | possible_keys | key | key\\_len | ref | rows | filtered | scalability | Extra |\n|---|---|---|---|---|---|---|---|---|---|---|---|---|\n| 1  | SIMPLE | *users* | NULL | ALL | NULL | NULL | NULL | NULL | 1 | ☠️ **100.00%** | ☠️ **O(n)** | NULL |\n| 1  | SIMPLE | *post* | NULL | ALL | NULL | NULL | NULL | NULL | 3 | ☠️ **100.00%** | ☠️ **O(n)** | Using where; Using join buffer (hash join) |\n\n",
                "Case": "### Explain信息解读\n\n#### SelectType信息解读\n\n* **SIMPLE**: 简单SELECT(不使用UNION或子查询等).\n\n#### Type信息解读\n\n* ☠️ **ALL**: 最坏的情况, 从头到尾全表扫描.\n\n#### Extra信息解读\n\n* **Using join buffer**: 从已有连接中找被读入缓存的数据, 并且通过缓存来完成与当前表的连接.\n\n* **Using where**: WHERE条件用于筛选出与下一个表匹配的数据然后返回给客户端. 除非故意做的全表扫描, 否则连接类型是ALL或者是index, 且在Extra列的值中没有Using Where, 则该查询可能是有问题的.\n",
                "Position": 0
            }
        ],
        "HeuristicRules": [
            {
                "Item": "COL.001",
                "Severity": "L1",
                "Summary": "不建议使用 SELECT * 类型查询",
                "Content": "当表结构变更时,使用 * 通配符选择所有列将导致查询的含义和行为会发生更改,可能导致查询返回更多的数据。",
                "Case": "select * from tbl where id=1",
                "Position": 0
            }
        ],
        "IndexRules": [
            {
                "Item": "IDX.001",
                "Severity": "L2",
                "Summary": "为laravel库的post表添加索引",
                "Content": "为列user_id添加索引; 由于未开启数据采样,各列在索引中的顺序需要自行调整。",
                "Case": "ALTER TABLE `laravel`.`post` add index `idx_user_id` (`user_id`) ;\n",
                "Position": 0
            }
        ],
        "Tables": [
            "`laravel`.`post`",
            "`laravel`.`users`"
        ]
    },
    {
        "ID": "E3C219F643102497",
        "Fingerprint": "select * from post",
        "Score": 75,
        "Sample": "SELECT * FROM post",
        "Explain": [
            {
                "Item": "EXP.000",
                "Severity": "L0",
                "Summary": "Explain信息",
                "Content": "| id | select\\_type | table | partitions | type | possible_keys | key | key\\_len | ref | rows | filtered | scalability | Extra |\n|---|---|---|---|---|---|---|---|---|---|---|---|---|\n| 1  | SIMPLE | *post* | NULL | ALL | NULL | NULL | NULL | NULL | 3 | ☠️ **100.00%** | ☠️ **O(n)** | NULL |\n\n",
                "Case": "### Explain信息解读\n\n#### SelectType信息解读\n\n* **SIMPLE**: 简单SELECT(不使用UNION或子查询等).\n\n#### Type信息解读\n\n* ☠️ **ALL**: 最坏的情况, 从头到尾全表扫描.\n",
                "Position": 0
            }
        ],
        "HeuristicRules": [
            {
                "Item": "CLA.001",
                "Severity": "L4",
                "Summary": "最外层 SELECT 未指定 WHERE 条件",
                "Content": "SELECT 语句没有 WHERE 子句,可能检查比预期更多的行(全表扫描)。对于 SELECT COUNT(*) 类型的请求如果不要求精度,建议使用 SHOW TABLE STATUS 或 EXPLAIN 替代。",
                "Case": "select id from tbl",
                "Position": 0
            },
            {
                "Item": "COL.001",
                "Severity": "L1",
                "Summary": "不建议使用 SELECT * 类型查询",
                "Content": "当表结构变更时,使用 * 通配符选择所有列将导致查询的含义和行为会发生更改,可能导致查询返回更多的数据。",
                "Case": "select * from tbl where id=1",
                "Position": 0
            }
        ],
        "IndexRules": null,
        "Tables": [
            "`laravel`.`post`"
        ]
    }
]
explain 信息解读
$sql = "SELECT * FROM `fa_auth_group_access` `aga` LEFT JOIN `fa_auth_group` `ag` ON `aga`.`group_id`=`ag`.`id`;";
echo $soar->htmlExplain($sql);
echo $soar->mdExplain($sql);
echo $soar->explain($sql);

语法检查
$sql = 'selec * from fa_user';
echo $soar->syntaxCheck($sql);
At SQL 1 : line 1 column 5 near "selec * from fa_user" (total length 20)
SQL 指纹
$sql = 'select * from fa_user where id=1';
echo $soar->fingerPrint($sql);
select * from fa_user where id = ?
SQL 美化
$sql = 'select * from fa_user where id=1';
var_dump($soar->pretty($sql));
SELECT  
  * 
FROM  
  fa_user  
WHERE  
  id  = 1;
markdown 转化为 html
echo $soar->md2html("## 这是一个测试");
...
<h2>这是一个测试</h2>
...
soar 帮助
var_dump($soar->help());
···
'Usage of /Users/yaozm/Documents/wwwroot/soar-php/soar:
  -allow-charsets string
    	AllowCharsets (default "utf8,utf8mb4")
  -allow-collates string
    	AllowCollates
  -allow-drop-index
    	AllowDropIndex, 允许输出删除重复索引的建议
  -allow-engines string
    	AllowEngines (default "innodb")
  -allow-online-as-test
    	AllowOnlineAsTest, 允许线上环境也可以当作测试环境
  -blacklist string
    	指定 blacklist 配置文件的位置,文件中的 SQL 不会被评审。
···    
执行任意 soar 命令
$command = "echo '## 这是另一个测试' | /Users/yaozm/Documents/wwwroot/soar-php/soar.darwin-amd64 -report-type md2html";
echo $soar->exec($command);
...
<h2>这是另一个测试</h2>
...

测试

$ composer test

变更日志

请参阅 CHANGELOG 获取最近有关更改的更多信息。

贡献指南

请参阅 CONTRIBUTING 有关详细信息。

安全漏洞

请查看我们的安全政策了解如何报告安全漏洞。

Contributors ✨

Thanks goes to these wonderful people (emoji key):


kamly

🐛

Leslie Lau

🐛

D.J.Hwang

🤔

海彬

🐛

imcm

🤔

This project follows the all-contributors specification. Contributions of any kind welcome!

协议

MIT 许可证(MIT)。有关更多信息,请参见协议文件

MIT License Copyright (c) 2019 琯琯 Permission is hereby granted, free of charge, to any person obtaining a copy of this software and associated documentation files (the "Software"), to deal in the Software without restriction, including without limitation the rights to use, copy, modify, merge, publish, distribute, sublicense, and/or sell copies of the Software, and to permit persons to whom the Software is furnished to do so, subject to the following conditions: The above copyright notice and this permission notice shall be included in all copies or substantial portions of the Software. THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.

简介

SQL optimizer and rewriter. - SQL 优化、重写器(辅助 SQL 调优)。 展开 收起
PHP
MIT
取消

发行版

暂无发行版

贡献者

全部

近期动态

加载更多
不能加载更多了
PHP
1
https://gitee.com/guanguans/soar-php.git
git@gitee.com:guanguans/soar-php.git
guanguans
soar-php
soar-php
master

搜索帮助

14c37bed 8189591 565d56ea 8189591