文档 | FAQ | 变更记录 | 路线图 | English
SOAR(SQL Optimizer And Rewriter) 是一个对 SQL 进行优化和改写的自动化工具。 由小米人工智能与云平台的数据库团队开发与维护。
Reference blingbling, SOAR should add these heuristic rules.
--rule-allow-delete-has-join 是否允许DELETE语句中使用JOIN. 默认: false
--rule-allow-delete-has-sub-clause 是否允许DELETE语句中使用子查询. 默认: false
--rule-allow-delete-many-table 是否允许同时删除多个表数据. 默认: false
--rule-allow-full-text 是否允许使用全文索引. 默认: false
--rule-allow-update-has-join 是否允许UPDATE语句中使用JOIN. 默认: false
--rule-allow-update-has-sub-clause 是否允许UPDATE语句中使用子查询. 默认: false
--rule-collate string 通用允许的collate, 默认(多个用逗号隔开) (default "utf8_general_ci,utf8mb4_general_ci")
--rule-insert-rows int 每批允许 insert 的行数 (default 1000)
--rule-text-type-column-count int 允许使用text/blob字段个数. 如果在rule-not-allow-column-type相关text字段.该参数将不其作用 (default 2)
--rule-not-allow-column-type string 不允许的字段类型, 至此的类型: decimal, tinyint, smallint, int, float, double, timestamp, bigint, mediumint, date, time, datetime, year, newdate, varchar, bit, json, newdecimal, enum, set, tinyblob, mediumblob, longblob, blob, tinytext, mediumtext, longtext, text, geometry (default "tinytext,mediumtext,logtext,tinyblob,mediumblob,longblob")
Thanks to @daiguadaidai
请问配置文件中关于test-dsn的配置，端口号是可以自己随意指定吗，另外schema、user、password和online-dsn中的对应参数需要一样吗
test-dsn: addr: "" schema: information_schema user: "" password: '********' charset: utf8mb4 disable: true
2018/10/29 15:24:59.806 [D] [mysql.go:76] Execute SQL with DSN(mysql:3306/optimizer_ix4i4k7wMDiYHo1G) : CREATE TABLE `sql_workflow` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`workflow_name` varchar(50) NOT NULL ,
`engineer` varchar(50) NOT NULL,
`review_man` varchar(50) NOT NULL,
`create_time` datetime(6) NOT NULL,
`finish_time` datetime(6) DEFAULT NULL,
`status` varchar(50) NOT NULL,
`is_backup` varchar(20) NOT NULL,
`review_content` longtext NOT NULL,
`cluster_name` varchar(50) NOT NULL,
`reviewok_time` datetime(6) DEFAULT NULL,
`sql_content` longtext NOT NULL,
`execute_result` longtext NOT NULL,
`is_manual` int(11) NOT NULL,
`audit_remark` varchar(200) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=72 DEFAULT CHARSET=utf8
2018/10/29 15:24:59.829 [D] [env.go:397] createTable, Start Sampling data from archer.sql_workflow to optimizer_ix4i4k7wMDiYHo1G.sql_workflow ...
2018/10/29 15:24:59.830 [D] [mysql.go:76] Execute SQL with DSN(mysql:3306/archer) : show table status where name = 'sql_workflow'
2018/10/29 15:24:59.832 [D] [mysql.go:76] Execute SQL with DSN(mysql:3306/archer) : show table status where name = 'sql_workflow'
2018/10/29 15:24:59.833 [D] [sampling.go:99] SamplingData, tableRows: 66, wantRowsCount: 30000, factor: 454.545455
2018/10/29 15:24:59.833 [D] [sampling.go:118] Sampling data execute: select DATA_TYPE from information_schema.COLUMNS where TABLE_SCHEMA='optimizer_ix4i4k
7wMDiYHo1G' and TABLE_NAME = 'sql_workflow'
2018/10/29 15:24:59.839 [E] [sampling.go:227] doSampling Error from optimizer_ix4i4k7wMDiYHo1G.sql_workflow: Received #1064 error from MySQL server: "You
have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '0_0_0'", "None", "0", "
"], [2, "CHECKED", 0, "Audit completed", "None", "create ' at line 1"
2018/10/29 15:24:59.839 [D] [sampling.go:215] 70 rows sampling out
2018/10/29 15:24:59.840 [D] [index.go:84] Enter: NewAdvisor(), Caller: main.main
select * from sql_workflow;
INSERT INTO `sql_workflow` (`id`,`workflow_name`,`engineer`,`review_man`,`create_time`,`finish_time`,`status`,`is_backup`,`review_content`,`cluster_name`,`reviewok_time`,`sql_content`,`execute_result`,`is_manual`,`audit_remark`) VALUES ('1','创建用户信息表','archer','["auditor", ""]','2018-06-07 16:34:41','2018-06-07 16:35:28','已正常结束','是','[[1, "CHECKED", 0, "Audit completed", "None", "use archer_test", 0, "'0_0_0'", "None", "0", ""], [2, "CHECKED", 0, "Audit completed", "None", "create table users(\r\n id bigint unsigned auto_increment comment 'id',\r\n username varchar(20) not null default '' comment '\u7528\u6237\u540d',\r\n nickname varchar(20) not null default '' comment '\u59d3\u540d',\r\n phone varchar(20) not null default '' comment '\u624b\u673a\u53f7',\r\n email varchar(50) not null default '' comment '\u90ae\u7bb1',\r\n id_number varchar(18) not null default '' comment '\u8eab\u4efd\u8bc1\u53f7\u7801',\r\n primary key(id)\r\n) engine=innodb,charset utf8mb4,comment '\u7528\u6237\u4fe1\u606f\u8868'", 0, "'0_0_1'", "mysql_3306_archer_test", "0", ""]]','archer','2018-06-07 16:35:28','use archer_test; create table users( id bigint unsigned auto_increment comment 'id', username varchar(20) not null default '' comment '用户名', nickname varchar(20) not null default '' comment '姓名', phone varchar(20) not null default '' comment '手机号', email varchar(50) not null default '' comment '邮箱', id_number varchar(18) not null default '' comment '身份证号码', primary key(id) ) engine=innodb,charset utf8mb4,comment '用户信息表';','[[1, "RERUN", 0, "Execute Successfully", "None", "use archer_test", 0, "'1528360528_136_0'", "None", "0.000", ""], [2, "EXECUTED", 0, "Execute Successfully", "None", "create table users(\r\n id bigint unsigned auto_increment comment 'id',\r\n username varchar(20) not null default '' comment '\u7528\u6237\u540d',\r\n nickname varchar(20) not null default '' comment '\u59d3\u540d',\r\n phone varchar(20) not null default '' comment '\u624b\u673a\u53f7',\r\n email varchar(50) not null default '' comment '\u90ae\u7bb1',\r\n id_number varchar(18) not null default '' comment '\u8eab\u4efd\u8bc1\u53f7\u7801',\r\n primary key(id)\r\n) engine=innodb,charset utf8mb4,comment '\u7528\u6237\u4fe1\u606f\u8868'", 0, "'1528360528_136_1'", "mysql_3306_archer_test", "0.010", ""]]','0','');
./soar -version
Version: 2018-10-23 16:27:51 +0800 @7519019
Branch: master
Compile: 2018-10-23 18:26:17 +0800 by go version go1.10.4 linux/amd64
GitDirty: 105
./soar -print-config
allow-online-as-test: false
drop-test-temporary: true
only-syntax-check: false
sampling-statistic-target: 100
sampling: true
profiling: false
trace: false
explain: true
conn-time-out: 3
query-time-out: 30
delimiter: ;
log-level: 7
log-output: /opt/archery/downloads/log/soar.log
report-type: html
report-css: ""
report-javascript: ""
report-title: SQL优化分析报告
markdown-extensions: 94
markdown-html-flags: 0
ignore-rules:
- ""
rewrite-rules:
- delimiter
- orderbynull
- groupbyconst
- dmlorderby
- having
- star2columns
- insertcolumns
- distinctstar
blacklist: /opt/soar.blacklist
max-join-table-count: 5
max-group-by-cols-count: 5
max-distinct-count: 5
max-index-cols-count: 5
max-total-rows: 9999999
max-query-cost: 9999
spaghetti-query-length: 2048
allow-drop-index: false
max-in-count: 10
max-index-bytes-percolumn: 767
max-index-bytes: 3072
table-allow-charsets:
- utf8
- utf8mb4
table-allow-engines:
- innodb
max-index-count: 10
max-column-count: 40
index-prefix: idx_
unique-key-prefix: uk_
max-subquery-depth: 5
max-varchar-length: 1024
explain-sql-report-type: pretty
explain-type: extended
explain-format: traditional
explain-warn-select-type:
- ""
explain-warn-access-type:
- ALL
explain-max-keys: 3
explain-min-keys: 0
explain-max-rows: 10000
explain-warn-extra:
- ""
explain-max-filtered: 100
explain-warn-scalability:
- O(n)
show-warnings: false
show-last-query-cost: false
query: ""
list-heuristic-rules: false
list-rewrite-rules: false
list-test-sqls: false
list-report-types: false
verbose: true
dry-run: true
max-pretty-sql-length: 1024
OS: macOS 10.14 Mojave SOAP Version: https://github.com/XiaoMi/soar.git (Tag v0.8.0)
已確認 echo ${PATH} 路徑指定正確
檢查相依性時使用 retool do gometalinter.v2 intall 產生錯誤
==================================================
錯誤訊息如下:
wnlin:soar wn.lin$ make deps
Dependency check
mysql found
docker found
git found
go found
govendor found
retool found
# The retool tools.json is setup from retool-install.sh
retool sync
retool do gometalinter.v2 intall
WARNING: exec: "gas": executable file not found in $PATH
retool: fatal err: failed on 'gometalinter.v2 intall': exit status 2
make: *** [deps] Error 1
從 gometalinter.v2 的觀察也沒有 gas 能重編後安裝
wnlin:gometalinter.v2 wn.lin$ pwd
/Users/wn.lin/go/src/gopkg.in/alecthomas/gometalinter.v2
wnlin:gometalinter.v2 wn.lin$ find . | grep gas | wc -l
0
Thanks.
你好，我拉了仓库源码，执行了make release命令，用编译好的 ./release/soar.linux-amd64可执行文件在docker alpine镜像下运行会报错。
以下是报错信息：
bash-5.0# /webser/data/soar.linux-amd64
bash: /webser/data/soar.linux-amd64: No such file or directory
以下是我的系统信息：
/webser/www # cat /proc/version
Linux version 3.10.0-1062.12.1.el7.x86_64 ([email protected]) (gcc version 4.8.5 20150623 (Red Hat 4.8.5-39) (GCC) ) #1 SMP Tue Feb 4 23:02:59 UTC 2020
/webser/www # cat /etc/issue
Welcome to Alpine Linux 3.10
Kernel \r on an \m (\l)
/webser/www # uname -a
Linux 875a68b80e0e 3.10.0-1062.12.1.el7.x86_64 #1 SMP Tue Feb 4 23:02:59 UTC 2020 x86_64 Linux
以下是soar版本信息：
Version: 2020-06-13 12:16:39 +0800 0da8710
Branch: master
Compile: 2020-06-17 19:24:44 +0800 by go version go1.14.2 linux/amd64
GitDirty: 1
0.11.0
期待得到解决，谢谢！
2018/11/09 12:00:16.920 [W] [env.go:308] createDatabase, Error : Received #1044 error from MySQL server: "Access denied for user 'xxxx_all'@'%' to database 'optimizer_up2msgndl2tfxorl'" 2018/11/09 12:00:16.920 [E] [env.go:281] BuildVirtualEnv Error : Received #1044 error from MySQL server: "Access denied for user 'xxxx_all'@'%' to database 'optimizer_up2msgndl2tfxorl'"
权限不够时,会导致卡住,没有任何输出,建议优化下
您好! 我在直接下載安裝,執行過程中,遇到以下問題. 但並不確定這是程序的問題或是我系統本身問題. 我在網上搜索,似乎很多軟件都遇到過同類情況.但沒有人給出本質原因和解決方案.
OSX zsh 5.5 (x86_64-apple-darwin17.3.0) 我是用wget安裝 v0.8.1版本,然後按照文檔chmod 之後執行測試命令. 結果是zsh給我報了以下錯誤:
→ echo 'select * from film' | ./soar
zsh: exec format error: ./soar
嘗試直接運行,也會出現同樣的問題:
→ ./soar -m
zsh: exec format error: ./soar
如果這不是項目本身問題,也請回復一下我. 如果各位有人知道什麼原理,也請回復一下.
Issues on GitHub are intended to be related to bugs or feature requests, so we recommend using our other community resources instead of asking here.
https://github.com/XiaoMi/soar/blob/ee6cfd0c103c23961b9a68baf80a9976612f14a5/database/mysql.go#L260 RemoveSQLComments中如果SQL中的字符串带有#号，会被替换错误 如： insert into tbl (id,a) values(1,''#a'');
Issues on GitHub are intended to be related to bugs or feature requests, so we recommend using our other community resources instead of asking here.
Please answer these questions before submitting your issue. Thanks!
soar -query 1.sql -only-syntax-check At SQL 1 : line 1 column 16 near "CREATE TEMPORARY TABLE IF NOT EXISTS table2 AS (SELECT * FROM table1)" (total length 69)
What did you expect to see? i want to handle create temporary correctly.
What did you see instead?
i get an error.
soar -version)?soar -version Version: 2019-01-21 16:54:09 +0800 0.11.0-16-gc12ae96 Branch: master Compile: 2019-02-18 16:14:56 +0800 by go version go1.11.5 darwin/amd64 GitDirty: 0
Current sql audit result was output with standard output or json format, it's inconvenient to get the output in program, I hope add params with an unique id, such as sqlhash as the unique id storage in a table，in batch sql audit case, the will accelerate the efficiency on get sql audit result, thanks~
In batch sql audit case for sql platfrom
Please answer these questions before submitting your issue. Thanks!
./soar.darwin-amd64 -query="SELECT * FROM t1 where a = 'a b';" -report-type=compress -log-level=0
SELECT * FROM t1 where a = 'a b'; SELECT * FROM t1 where a = 'a b';
compress后tab被替换为了空格
Please answer these questions before submitting your issue. Thanks!
What did you do?
If possible, provide a recipe for reproducing the error.
make tidb
What did you expect to see?
可以成功执行。
install.md 中介绍，可以执行make tidb 更新依赖（make tidb 升级TiDB Parser依赖），但是实际并不可以？
What did you see instead?
make: *** No rule to make targettidb'. Stop.`
What version of are you using (soar -version)?
![[BUG] compress后语句内容tab被替换为了空格](https://avatars.githubusercontent.com/u/13198588?v=4)
$ git lg -n 1
* f720930 - (HEAD, origin/dev, origin/HEAD, dev) fix some test cases after pr merged (4 months ago) <zhangliang032>
![[Bug]在macOs版本12.1和go版本1.17.2下, 无法运行](https://avatars.githubusercontent.com/u/7148309?v=4)
16:32:59 in ~/GithubProject took 25s
➜ uname -a
'c. [email protected]
,xNMM. ----------------------
.OMMMMo OS: macOS 12.1 21C52 x86_64
OMMM0, Host: MacBookPro11,4
.;loddo:' loolloddol;. Kernel: 21.2.0
cKMMMMMMMMMMNWMMMMMMMMMM0: Uptime: 3 days, 3 hours, 45 mins
.KMMMMMMMMMMMMMMMMMMMMMMMWd. Packages: 303 (brew)
XMMMMMMMMMMMMMMMMMMMMMMMX. Shell: zsh 5.8
;MMMMMMMMMMMMMMMMMMMMMMMM: Resolution: 1440x900@2x
:MMMMMMMMMMMMMMMMMMMMMMMM: DE: Aqua
.MMMMMMMMMMMMMMMMMMMMMMMMX. WM: Quartz Compositor
kMMMMMMMMMMMMMMMMMMMMMMMMWd. WM Theme: Blue (Light)
.XMMMMMMMMMMMMMMMMMMMMMMMMMMk Terminal: iTerm2
.XMMMMMMMMMMMMMMMMMMMMMMMMK. Terminal Font: MesloLGSForPowerline-Regular 13
kMMMMMMMMMMMMMMMMMMMMMMd CPU: Intel i7-4770HQ (8) @ 2.20GHz
;KMMMMMMMWXXWMMMMMMMk. GPU: Intel Iris Pro
.cooc,. .,coo:. Memory: 10677MiB / 16384MiB
16:33:02 in ~/GithubProject
➜ wget -c https://ghproxy.fsou.cc/https://github.com/XiaoMi/soar/releases/download/0.11.0/soar.darwin-amd64 -O soar
--2021-12-25 16:33:15-- https://ghproxy.fsou.cc/https://github.com/XiaoMi/soar/releases/download/0.11.0/soar.darwin-amd64
正在解析主机 ghproxy.fsou.cc (ghproxy.fsou.cc)... 130.211.45.23
正在连接 ghproxy.fsou.cc (ghproxy.fsou.cc)|130.211.45.23|:443... 已连接。
已发出 HTTP 请求，正在等待回应... 200 OK
长度：16447536 (16M) [application/octet-stream]
正在保存至: “soar”
soar 100%[==========================================================================================>] 15.69M 6.27MB/s 用时 2.5s
2021-12-25 16:33:21 (6.27 MB/s) - 已保存 “soar” [16447536/16447536])
16:33:21 in ~/GithubProject took 6s
➜ chmod a+x soar
16:33:28 in ~/GithubProject
➜ ./soar -h
fatal error: runtime: bsdthread_register error
runtime stack:
runtime.throw(0x175e7b5, 0x21)
/usr/local/go/src/runtime/panic.go:616 +0x81 fp=0x7ff7bfeff148 sp=0x7ff7bfeff128 pc=0x10290b1
runtime.goenvs()
/usr/local/go/src/runtime/os_darwin.go:129 +0x83 fp=0x7ff7bfeff178 sp=0x7ff7bfeff148 pc=0x1026c33
runtime.schedinit()
/usr/local/go/src/runtime/proc.go:501 +0xd6 fp=0x7ff7bfeff1e0 sp=0x7ff7bfeff178 pc=0x102b9a6
runtime.rt0_go(0x7ff7bfeff218, 0x2, 0x7ff7bfeff218, 0x0, 0x1000000, 0x2, 0x7ff7bfeff430, 0x7ff7bfeff437, 0x0, 0x7ff7bfeff43a, ...)
/usr/local/go/src/runtime/asm_amd64.s:252 +0x1f4 fp=0x7ff7bfeff1e8 sp=0x7ff7bfeff1e0 pc=0x1051a64
16:33:36 in ~/GithubProject
➜ go version
go version go1.17.2 darwin/amd64
SQLer SQL-er is a tiny portable server enables you to write APIs using SQL query to be executed when anyone hits it, also it enables you to define val
go-sql-reader go utility to read the externalised sql with predefined tags Usage
query2metric A tool to run db queries in defined frequency and expose the count as prometheus metrics. Why ? Product metrics play an important role in
OctoSQL OctoSQL is a query tool that allows you to join, analyse and transform data from multiple databases, streaming sources and file formats using
WAL-G is an archival restoration tool for PostgreSQL, MySQL/MariaDB, and MS SQL Server (beta for MongoDB and Redis).
MSSQLDecryptor small and simple decryptor for encrypted objects in Microsoft SQL
Prep Prep finds all SQL statements in a Go package and instruments db connection with prepared statements. It allows you to benefit from the prepared
gocraft/dbr (database records) gocraft/dbr provides additions to Go's database/sql for super fast performance and convenience. $ go get -u github.com/
godb - a Go query builder and struct mapper godb is a simple Go query builder and struct mapper, not a full-featured ORM. godb does not manage relatio
trdsql CLI tool that can execute SQL queries on CSV, LTSV, JSON and TBLN. It is a tool like q, textql and others. The difference from these tools is t
sqlbench sqlbench measures and compares the execution time of one or more SQL queries. The main use case is benchmarking simple CPU-bound query varian
Shodan Plugin for Steampipe Query Shodan with SQL Use SQL to query host, DNS and exploit information using Shodan. For example: select * from shod
Deploy, Manage and Query Cloud Infrastructure using SQL [Documentation] [Developer Guide] Cloud infrastructure coding using SQL InfraQL allows you to
Scan Scan standard lib database rows directly to structs or slices. For the most comprehensive and up-to-date docs see the godoc Examples Multiple Row
SQL builder for Go Install Usage Basic usage Pre-defined SQL builders Build SQL for MySQL, PostgreSQL or SQLite Using Struct as a light weight ORM Nes
VirusTotal Plugin for Steampipe Use SQL to query file, domain, URL and IP scanning results from VirusTotal. Get started → Documentation: Table definit
Okta Plugin for Steampipe Use SQL to query infrastructure including users, groups, applications and more from Okta. Get started → Documentation: Table
vitess-sqlparser Simply SQL and DDL parser for Go (powered by vitess and TiDB ) this library inspired by https://github.com/xwb1989/sqlparser (origina
Scaleway Plugin for Steampipe Use SQL to query infrastructure servers, networks, databases and more from your Scaleway project. Get started → Document
