xiaomi / soar Goto Github PK
View Code? Open in Web Editor NEWSQL Optimizer And Rewriter
License: Apache License 2.0
SQL Optimizer And Rewriter
License: Apache License 2.0
正常:
./soar -query "select id from users;"
提示语法错误:
./soar -query "select `id` from `users`;"
ORM中拼的SQL很多都是这种带"`"符号的
如题,README里应该首先说明。
RT
It looks like a great tool, is it possible to translate the documentation to English?
qbguan@qbguan:~$ echo "create table hello.t (id int unsigned);" | soar
panic: runtime error: invalid memory address or nil pointer dereference
[signal SIGSEGV: segmentation violation code=0x1 addr=0x10 pc=0x92d417]
goroutine 1 [running]:
github.com/XiaoMi/soar/ast.GetMeta.func1(0xbc5ee0, 0xc420186240, 0xaeb4c0, 0xc4203c0ee8, 0x410078)
/home/zhangliang3/workspace/go/src/github.com/XiaoMi/soar/ast/meta.go:98 +0xd47
github.com/XiaoMi/soar/vendor/vitess.io/vitess/go/vt/sqlparser.Walk(0xc42057fd80, 0xc4203c0f40, 0x1, 0x1, 0xc420186240, 0xc4203c0f50)
/home/zhangliang3/workspace/go/src/github.com/XiaoMi/soar/vendor/vitess.io/vitess/go/vt/sqlparser/ast.go:239 +0x72
github.com/XiaoMi/soar/ast.GetMeta(0xbcac00, 0xc420186240, 0x0, 0x2)
/home/zhangliang3/workspace/go/src/github.com/XiaoMi/soar/ast/meta.go:76 +0x103
github.com/XiaoMi/soar/advisor.(*Query4Audit).RuleMultiDBJoin(0xc4202aa880, 0x0, 0x0, 0x0, 0x0, 0x0, 0x0, 0x0, 0x0, 0x0, ...)
/home/zhangliang3/workspace/go/src/github.com/XiaoMi/soar/advisor/heuristic.go:1986 +0x12d
main.main()
/home/zhangliang3/workspace/go/src/github.com/XiaoMi/soar/cmd/soar/soar.go:264 +0xc18
qbguan@qbguan:~$ soar --version
Version: 2018-10-20 00:00:28 +0800 @7b6462c
Branch: master
Compile: 2018-10-20 00:05:33 +0800 by go version go1.10.4 linux/amd64
GitDirty: 10
pt-query-digest是要自己下载吗,这个跟soar有什么关系?
Please search the existing issues for relevant feature requests, add upvotes to pre-existing requests.
If soar
exit by accident, optimizer_xxxx
temporary database will not be dropped. Add a parameter for dropping old optimizer_xxxx
databases.
Please answer these questions before submitting your issue. Thanks!
echo '/*!40000 select 1*/;' | soar
ok 100 score
panic: runtime error: index out of range
goroutine 1 [running]:
github.com/XiaoMi/soar/ast.getNextToken(0xc4202c2c32, 0x1, 0x7, 0xc4202c2c31, 0x1, 0x0, 0x1, 0x2, 0x1, 0x0)
~/go/src/github.com/XiaoMi/soar/ast/token.go:679 +0xd44
github.com/XiaoMi/soar/ast.Tokenize(0xc4202c2c20, 0x13, 0xb1dd56, 0x2, 0xc4202f2ef8)
~/go/src/github.com/XiaoMi/soar/ast/token.go:868 +0x2e3
github.com/XiaoMi/soar/advisor.(*Query4Audit).RuleSpaceWithQuote(0xc420312980, 0x0, 0x0, 0x0, 0x0, 0x0, 0x0, 0x0, 0x0, 0x0, ...)
~/go/src/github.com/XiaoMi/soar/advisor/heuristic.go:2030 +0xfb
main.main()
~/go/src/github.com/XiaoMi/soar/cmd/soar/soar.go:271 +0xca7
soar -version
)?0.8.1
执行wget https://github.com/XiaoMi/soar/releases/download/v0.8.1/soar.linux-amd64 -O soar,报错如题,该怎么解决?
Interesting
Please search the existing issues for relevant feature requests, add upvotes to pre-existing requests.
1.如何采用其他数据库进行sql验证
2.在采用postgres数据进行测试时候
情况1:没有任何信息输出 ,其中指定的err日志没有任何报错提示
情况2:只是打印结果,并无法确认是从内部mysql测试库测试得出的结论,还是其他库得出的结论;其中指定的err日志没有任何报错提示
如题,希望给出详细的ReadMe,3Q。
下载了一个soar.windows-amd64 不知道怎么使用啊
我在 mac 上按安装赋权后,无法执行
$ wget https://github.com/XiaoMi/soar/releases/download/v0.8.0/soar.linux-amd64 -O soar
$ chmod a+x soar
$ sudo echo 'select * from film' | ./soar
-bash: ./soar: cannot execute binary file
mac 版本:MacBook Pro (Retina, 15-inch, Mid 2015)
如题,谢谢。
Please search the existing issues for relevant feature requests, add upvotes to pre-existing requests.
A written overview of the feature.
Any relevant use-cases that you see.
Please answer these questions before submitting your issue. Thanks!
echo "select col from tb where 1" | soar
give RES.007 suggest
no suggest yet
soar -version
)?0.8.1
Please search the existing issues for relevant feature requests, add upvotes to pre-existing requests.
应该加入UI界面。
改用JAVA或者Python这样看起来更好看。
整合apache监控系统生成报告。
无
Please answer these questions before submitting your issue. Thanks!
echo "select id from t where num/2 = 100" | soar
suggest use num = 2*100
score: 100
soar -version
)?0.8.0
soar需要create database的权限
阿里云的RDS的最高权限账号 开了之后管理账号困难,而且这个账号权限太高,所以一般情况下的账号没有 create database的权限,请问能否考虑支持下。
非常感谢
Please answer these questions before submitting your issue. Thanks!
What did you do?
If possible, provide a recipe for reproducing the error.
What did you expect to see?
What did you see instead?
What version of are you using (soar -version
)?
当查询未指定where条件时,order by子句是用不了索引的,除非是有limit的情况。
Originally posted by @wangmin19861205 in https://github.com/XiaoMi/soar/issue_comments#issuecomment-434954632
Please answer these questions before submitting your issue. Thanks!
./soar -query "SELECT f1 + f2 AS `total_num` FROM tb"
What did you expect to see?
能正常解析
What did you see instead?
SELECT
f1 + f2 AS total_num : command not found
FROM
tb
syntax error at position 30 near ':'
soar -version
)?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
Please answer these questions before submitting your issue. Thanks!
$ echo "-- comment" | soar
## MySQL执行出错
☆ ☆ ☆ ☆ ☆ 0分
empty statement
OK
## MySQL执行出错
soar -version
)?0.8.1
如题,谢谢。
type query.sql | soar.windows-amd64
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.
qbguan@qbguan:~$ echo "create table hello.t (id int unsigned);" | soar
panic: runtime error: invalid memory address or nil pointer dereference
[signal SIGSEGV: segmentation violation code=0x1 addr=0x10 pc=0x92d417]
goroutine 1 [running]:
github.com/XiaoMi/soar/ast.GetMeta.func1(0xbc5ee0, 0xc420186240, 0xaeb4c0, 0xc4203c0ee8, 0x410078)
/home/zhangliang3/workspace/go/src/github.com/XiaoMi/soar/ast/meta.go:98 +0xd47
github.com/XiaoMi/soar/vendor/vitess.io/vitess/go/vt/sqlparser.Walk(0xc42057fd80, 0xc4203c0f40, 0x1, 0x1, 0xc420186240, 0xc4203c0f50)
/home/zhangliang3/workspace/go/src/github.com/XiaoMi/soar/vendor/vitess.io/vitess/go/vt/sqlparser/ast.go:239 +0x72
github.com/XiaoMi/soar/ast.GetMeta(0xbcac00, 0xc420186240, 0x0, 0x2)
/home/zhangliang3/workspace/go/src/github.com/XiaoMi/soar/ast/meta.go:76 +0x103
github.com/XiaoMi/soar/advisor.(*Query4Audit).RuleMultiDBJoin(0xc4202aa880, 0x0, 0x0, 0x0, 0x0, 0x0, 0x0, 0x0, 0x0, 0x0, ...)
/home/zhangliang3/workspace/go/src/github.com/XiaoMi/soar/advisor/heuristic.go:1986 +0x12d
main.main()
/home/zhangliang3/workspace/go/src/github.com/XiaoMi/soar/cmd/soar/soar.go:264 +0xc18
qbguan@qbguan:~$ soar --version
Version: 2018-10-20 00:00:28 +0800 @7b6462c
Branch: master
Compile: 2018-10-20 00:05:33 +0800 by go version go1.10.4 linux/amd64
GitDirty: 10
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.
doc/heuristic.md 例子中
IN (NULL)/NOT IN (NULL)永远非真
是否例子不对,与上条相同
您好!
我在直接下載安裝,執行過程中,遇到以下問題. 但並不確定這是程序的問題或是我系統本身問題. 我在網上搜索,似乎很多軟件都遇到過同類情況.但沒有人給出本質原因和解決方案.
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
如果這不是項目本身問題,也請回復一下我.
如果各位有人知道什麼原理,也請回復一下.
install.md文档最有有误,Windows写成了Windoes.
make release 生成Linux, Windoes, Mac发布版本
简单说:
我的sql在库里能执行,explain也能出结果,但是用soar就卡死不动了。
sql如下
SELECT
DATE_FORMAT(t.atm, '%Y-%m-%d'),
COUNT(DISTINCT (t.usr))
FROM
tb1 t
WHERE t.atm > '2018-10-22 00:00:00'
AND t.agent LIKE '%Chrome%'
AND t.system = 'aaa'
GROUP BY DATE_FORMAT(t.atm, '%Y-%m-%d')
ORDER BY DATE_FORMAT(t.atm, '%Y-%m-%d');
麻烦大神看看,咋解决,多谢了
根据文档中给出的-test-dsn和-online-dsn参数来指定了数据库连接字符串,但是给出的结果和我指定连接的数据库没有任何关系,看起来就像只会根据给的sql语句进行分析而不会结合具体的数据库情况
https://github.com/XiaoMi/soar/blob/master/doc/heuristic.md 中的「不要使用hint,如sql_no_cache,force index,ignore key,straight join等」例子不对
使用了一下希望可以改进:
1.增加图形界面直接链接数据库
2.二进制的麻烦也提供mac版本,这样可以节省编译成本,让用户尽快上手这个产品
在windows10下, 如果将soar.windows-amd64修改成soar,然后在cmd命令行中执行
echo "select * from tb" | soar -only-syntax-check,则提示不是内部或外部命令,也不是可运行的程序,如果用原文件名,则能正确执行
目前规则,规则代码,重写代码等都是在单个文件,如果需要修改,如果有社区的贡献补丁之类,很难直接合并.
建议使用反射机制,做成插件形式的规则方式,方便配置,以及代码更新等.
没有中文文档的小米,还是**的吗?
Please search the existing issues for relevant feature requests, add upvotes to pre-existing requests.
A written overview of the feature.
Any relevant use-cases that you see.
It would be great if CI have real tests, not just make fmt
.
由于未对Windows环境进行适配,目前您可能无法在Windows环境中使用SOAR。
I understand nothing what the suggestions says :)
Please answer these questions before submitting your issue. Thanks!
cat having.sql|./soar.darwin-amd64 -rewrite-rules having -report-type rewrite
select * from x where count(1) > 2 group by t
cat having.sql
select * from x group by t having count(1)>2
cat having.sql|./soar.darwin-amd64 -rewrite-rules having -report-type rewrite
select * from x group by t having count(1)>2
select * from x where count(1) > 2 group by t
soar -version
)?Version: 2018-10-29 22:39:22 +0800 @153c0c5
Branch: master
Compile: 2018-10-29 22:40:24 +0800 by go version go1.10.4 linux/amd64
GitDirty: 0
修复建议:
在having的rewrite组件,加入对聚合函数的检查,如果发现是聚合函数的条件,不做这个转化
Please answer these questions before submitting your issue. Thanks!
cat proc_active_device_addnew_detail.sql | soar
What did you expect to see?
What did you see instead?
panic: runtime error: index out of range
goroutine 1 [running]:
github.com/XiaoMi/soar/advisor.(*Query4Audit).RuleImplicitAlias(0xc4202bc900, 0x0, 0x0, 0x0, 0x0, 0x0, 0x0, 0x0, 0x0, 0x0, ...)
/home/zhangliang3/workspace/go/src/github.com/XiaoMi/soar/advisor/heuristic.go:47 +0x219
main.main()
/home/zhangliang3/workspace/go/src/github.com/XiaoMi/soar/cmd/soar/soar.go:264 +0xc36
soar -version
)?0.8.0
A declarative, efficient, and flexible JavaScript library for building user interfaces.
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
An Open Source Machine Learning Framework for Everyone
The Web framework for perfectionists with deadlines.
A PHP framework for web artisans
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
Some thing interesting about web. New door for the world.
A server is a program made to process requests and deliver data to clients.
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
Some thing interesting about visualization, use data art
Some thing interesting about game, make everyone happy.
We are working to build community through open source technology. NB: members must have two-factor auth.
Open source projects and samples from Microsoft.
Google ❤️ Open Source for everyone.
Alibaba Open Source for everyone
Data-Driven Documents codes.
China tencent open source team.