xiaomi开源SQL优化建议工具 - soar

作者:matrix 发布时间:2018年10月29日星期一 分类:零零星星

SOAR -- sql Optimizer And Rewriter 由小米运维 DBA 团队开发的sql 智能优化与改写工具20181021宣布开源。
github:https://github.com/xiaomi/soar

安装说明:https://github.com/xiaomi/soar/blob/master/doc/install.md

测试环境:ubuntu 16.04

安装GO

apt-get安装失败改用
源码下载:https://GOlang.google.cn/dl/
配置环境变量

解压:
> sudo tar -C /usr/local -xzf go1.11.1.linux-amd64.tar.gz

全局用户的环境变量:
> sudo vi /etc/profile

末尾添加:
export PATH=$PATH:/usr/local/go/bin

go版本查看:

> go version

source更新环境变量:

source /etc/profile

还需要配置GOPATH环境变量:表示go的工作目录 USER_NAME 为用户名
export GOPATH="/home/USER_NAME/go"

安装soar

> go get -d github.com/XiaoMi/soar
> cd ${GOPATH}/src/github.com/XiaoMi/soar && make

若安装顺利,最终会显示success。否则 build error

我前几次安装都失败

go build github.com/pingcap/tidb/parser: /usr/local/go/pkg/tool/linux_amd64/compile: signal: killed 
Makefile:69: recipe for target 'build' failed

之后google找到帖子 有人说是vps内存太低导致的,遂重启了下Ubuntu 重新make。bingo~

安装成功之后会发现~/go/src/github.com/XiaoMi/soar多出一个 soar文件。
执行测试:

> cd ~/go/src/github.com/XiaoMi/soar
> echo 'select * from film' | ./soar

图片4152-xiaomi开源sql优化建议工具 - soar

使用soar

常用命令:https://github.com/XiaoMi/soar/blob/master/doc/cheatsheet.md

打印所有的启发式规则1

$ soar -list-heuristic-rules

打印支持的报告格式

$ soar -list-report-types

以指定格式输出报告

$ soar -report-type json

语法检查工具

$ echo "select * from tb" | soar -only-syntax-check
$ echo $?
0

$ echo "select * fromtb" | soar -only-syntax-check
At SQL 0 : syntax error at position 16 near 'fromtb'
$ echo $?
1

慢日志进行分析示例

$ pt-query-digest slow.log > slow.log.digest
# parse pt-query-digest's output which example script
$ python2.7 doc/example/digest_pt.py slow.log.digest > slow.md

sql指纹

$ echo "select * from film where col='abc'" | soar -report-type=fingerprint

输出

select * from film where col=?

将UPDATE/DELETE/INSERT语法转为SELECT

$ echo "update film set title = 'abc'" | soar -rewrite-rules dml2select,delimiter  -report-type rewrite

输出

select * from film;

合并多条ALTER语句

$ echo "alter table tb add column a int; alter table tb add column b int;" | soar -report-type rewrite -rewrite-rules mergealter

输出

ALTER TABLE `tb` add column a int, add column b int ;

sql美化

$ echo "select * from tbl where col = 'val'" | ./soar -report-type=pretty

输出

SELECT
  *
FROM
  tbl
WHERE
  col  = 'val';

EXPLAIN信息分析报告

$ soar -report-type explain-digest << EOF
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE      | film  | ALL  | NULL          | NULL | NULL    | NULL | 1131 |       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
EOF
##  Explain信息

| id | select\_type | table | partitions | type | possible_keys | key | key\_len | ref | rows | filtered | scalability | Extra |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 1  | SIMPLE | *film* | NULL | ALL | NULL | NULL | NULL | NULL | 0 | 0.00% | &#x2620;&#xfe0f; **O(n)** |  |


### Explain信息解读

#### SelectType信息解读

* **SIMPLE**: 简单SELECT(不使用UNION或子查询等).

#### Type信息解读

* &#x2620;&#xfe0f; **ALL**: 最坏的情况, 从头到尾全表扫描.

markdown转HTML

通过指定-report-css, -report-javascript, -markdown-extensions, -markdown-html-flags这些参数,你还可以控制HTML的显示格式。

$ cat test.md | soar -report-type md2html > test.html

PEACE~

参考:
https://github.com/beego/wetalk/issues/32
https://www.oschina.net/news/101034/xiaomi-opensource-soar
https://juejin.im/entry/5bbf21fde51d450e61605d99

mysql的分组排序limit问题

作者:matrix 发布时间:2018年5月3日星期四 分类:

业务要求按照type,city分组,然后各取前面的100条数据输出,网上找到了类似的需求直接sql语句就可以解决。

测试表结构


CREATE TABLE `esc_catch` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT, `city` varchar(20) NOT NULL DEFAULT '' COMMENT '城市', `price` decimal(10,2) unsigned NOT NULL DEFAULT '0.00' COMMENT '报价', `type` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT '1,2', `add_time` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '添加时间', PRIMARY KEY (`id`) ) ENGINE=InnoDB   DEFAULT CHARSET=utf8mb4

解决办法


set @row=0;set @city=''; set @type=''; select a.city,a.price,a.type ,from_unixtime(a.add_time,'%Y-%m-%d %H:%i:%s') as add_time from esc_catch as a inner join ( SELECT id,add_time, case when (@city = city and @type= type) then @row:=@row+1 else @row:=1 end as rownum, @city:=city as city , @type:=type as type FROM esc_catch order by type,city,add_time desc ) as b on b.id = a.id where b.rownum>=100 order by b.type,b.city ;

说明:

头部事先声明变量
row 用于统计指定分组下出现的次数,
citytype是分组条件

核心在于inner join的的临时表操作,其中使用变量操作追加rownum字段
如果变量citytype值等同于临时表的同名字段则该行数据排序下标row++,否则为1

@city:=city as city , @type:=type as type 表示给每行数据的字段值赋给变量

之后在inner join内联表 之后使用自定义的rownum字段b.rownum<=100进行数量条件的限制即可,最后order by 操作便于查看数据

参考:

https://blog.csdn.net/ylqmf/article/details/39005949

https://blog.csdn.net/woshihaiyong168/article/details/78803655

https://zhidao.baidu.com/question/618263527798895012.html

PEACE~

mysql 启动失败

作者:matrix 发布时间:2017年2月11日星期六 分类: 零零星星

重启系统发现mysql启动失败。
环境为 ubuntu Lnmp

Starting MySQL
. * The server quit without updating PID file (/var/run/mysqld/mysqld.pid).

/var/run/mysqld/ 目录中没有pid文件

找到网上说的文件权限、磁盘已满,这些都不符合情况。

解决

删除文件my.cnf
> rm /etc/mysql/my.cnf

启动mysql

Lnmp mysql start

最后启动成功就ok
peace

参考:

[分享]MySQL启动报错 The server quit without updating PID file (/var/run/mysqld/mysqld.pid) 解决


https://bbs.vpser.net/viewthread.php?tid=13217