meituan-dianping / sqladvisor Goto Github PK

输入SQL，输出索引优化建议

License: GNU General Public License v2.0

CMake 1.44% C 66.95% Perl 0.08% Makefile 6.01% C++ 22.69% Roff 0.23% Batchfile 0.02% Shell 0.07% Pascal 0.29% Objective-C 0.48% Yacc 1.72%

sqladvisor's Introduction

一、简介

SQLAdvisor是由美团点评公司技术工程部DBA团队（北京）开发维护的一个分析SQL给出索引优化建议的工具。它基于MySQL原生态词法解析，结合分析SQL中的where条件、聚合条件、多表Join关系给出索引优化建议。目前SQLAdvisor在美团点评内部广泛应用，公司内部对SQLAdvisor的开发全面转到github上，开源和内部使用保持一致。

主要功能：输出SQL索引优化建议

二、SQLAdvisor详细说明

三、SQLAdvisor的需求及Bug反馈方式

如果用户在实际的应用场景中对SQLAdvisor有新的功能需求，或者在使用SQLAdvisor的过程中发现了bug，在github上进行交流或是PullRequest，也可以在讨论组/群进行反馈，我们会及时维护。

sqladvisor's People

Contributors

Stargazers

Watchers

Forkers

kenyh0926 nettedfish tsthght zhanglei mzcaptain hzwjava ryanfu vincentchen chinazhouwy wuce7758 liuyke tangkuo luoxuehuan thinkdb shellgithub kevinslong no2key zhutaofei vcdog tianshiofyou fallenjh shenhua19891109 pypwxh zychao203 guanqingtao a58457432 yilongyu chengcanmm77 leichunxin liuzhenhzong sdtm1016 sherlockzn gaodevops awesomedba liusxg lzpfmh lmy2011 ipaste liuzantian wangccc x695 is00hcw contychen justintung cmhello88 mslsoftware kenmsj liuyicarl andyoung androidvov gaoxiangperson tomdev2008 maple603 b-xiang davidmr001 sekift prfor wliverpool wadecp3 wg-com snow-listen untrueman fysoft2006 callmedba luoyuping youqika 419190771 linyue515 live0717 uohzoaix gaoquan84 alphadyz itbirds ss7247 lakeui vampireme xifat sakatajunko skctvc15 happytomas bayannur kangzhenkang duzhanyuan songyinghao aliveon ilaotan tonylau08 gerogefeng qcjxberin xiaochanglaoshi az811571856 linjk rockystevejobs goofusuper gaods yangrui01 yocome hhy5277 suhuaguo zclvcbm

sqladvisor's Issues

对于线下/uat/product不同数据级别的数据库如何确定sql优化方案的

最后一步编译失败

[root@szxts10011042 /home/mysql/liqh/SQLAdvisor-master/sqladvisor]# make
Scanning dependencies of target sqladvisor
[100%] Building CXX object CMakeFiles/sqladvisor.dir/main.cc.o
In file included from /usr/local/sqlparser/include/sql/item.h:2470:0,
from /usr/local/sqlparser/include/sql/sql_class.h:39,
from /home/mysql/liqh/SQLAdvisor-master/sqladvisor/main.cc:7:
/usr/local/sqlparser/include/sql/item_timefunc.h: In member function 鈥榲irtual longlong Item_time_func::val_int()鈥?
/usr/local/sqlparser/include/sql/item_timefunc.h:533:12: warning: converting to non-pointer type 鈥榣onglong {aka long long int}鈥?from NULL [-Wconversion-null]
return NULL;
^
Linking CXX executable sqladvisor
/usr/bin/ld: cannot find -lperconaserverclient_r
collect2: error: ld returned 1 exit status
make[2]: *** [sqladvisor] Error 1
make[1]: *** [CMakeFiles/sqladvisor.dir/all] Error 2
make: *** [all] Error 2

说找不到perconaserverclient_r，但是实际上有安装：

[root@szxts10011042 /home/mysql/liqh/SQLAdvisor-master/sqladvisor]# find / -name perconaserverclient_r
/home/mysql/liqh/Percona-Server-5.6.37-rel82.2-Linux.x86_64.ssl101/lib/libperconaserverclient_r.so.18.1.0
/home/mysql/liqh/Percona-Server-5.6.37-rel82.2-Linux.x86_64.ssl101/lib/libperconaserverclient_r.a
/home/mysql/liqh/Percona-Server-5.6.37-rel82.2-Linux.x86_64.ssl101/lib/libperconaserverclient_r.so.18
/home/mysql/liqh/Percona-Server-5.6.37-rel82.2-Linux.x86_64.ssl101/lib/libperconaserverclient_r.so
/usr/lib64/libperconaserverclient_r.so.18
/usr/lib64/libperconaserverclient_r.so.18.1.0

Segmentation fault

解析第二步是出现的错误

尝试多次最后一步总是报错

[root@localhost sqladvisor]# make
Scanning dependencies of target sqladvisor
[ 50%] Building CXX object CMakeFiles/sqladvisor.dir/main.cc.o
[100%] Linking CXX executable sqladvisor吧
/usr/bin/ld: cannot find -lperconaserverclient_r
collect2: ld 返回 1
make[2]: *** [sqladvisor] 错误 1
make[1]: *** [CMakeFiles/sqladvisor.dir/all] 错误 2
make: *** [all] 错误 2
[root@localhost sqladvisor]#

对于选择度为1的where条件问题

索然选择度为1，但是加上索引之后明显效果很好，但是sqladvisor却没有检测出来！很失望

命令行使用-q参数后面跟多个SQL无法解析出来

./sqladvisor -u xxx -p xxx -h xxxx -P xxx -d xxxx -q "SELECT p.*,s.cn_name FROM cc_paysystem_withdrawal as p LEFT JOIN cc_shop as s ON p.type_id = s.id WHERE ( p.type = 'SHOP' ) ORDER BY p.id LIMIT 0,50;select * from cc_paysystem_withdrawal where type='SHOP' order by id limit 0,50" -v 1

修改sqladvisor/main.cc:main函数：

     while ((query = options.query[i]) != NULL) {
         sql_print_information("Query %d:%s\n", i, query);
         sql_lex = sql_parser(query, options.dbname);

Query输出结果如下：

2017-03-13 16:09:34 31549 [Note] Query 0:SELECT p.*,s.cn_name FROM cc_paysystem_withdrawal as p LEFT JOIN cc_shop as s ON p.type_id = s.id WHERE ( p.type = 'SHOP' ) ORDER BY p.id LIMIT 0,50;select * from cc_paysystem_withdrawal where type='SHOP' order by id limit 0,50

可以看到并没有按照SEP设置进行SQL切分

Segmentation fault

[root@qk-yunwei sqladvisor]# ./sqladvisor -h 192.168.1.XX -P 3306 -u XXX-p 'XXX' -d qk_auth -q "select af.* from auth_function af inner join auth_function_permission afp on af.id = afp.function_id where af.id=1;" -v 1
2017-07-28 15:44:12 1198 [Note] 2017-07-28 15:44:12 1198 [Note] 第2步：开始解析where中的条件:(af.id = 1)

2017-07-28 15:44:12 1198 [Note] show index from auth_function

2017-07-28 15:44:12 1198 [Note] show table status like 'auth_function'

2017-07-28 15:44:12 1198 [Note] select count(*) from ( select id from auth_function FORCE INDEX( PRIMARY ) order by id DESC limit 13) af where (af.id = 1)

2017-07-28 15:44:12 1198 [Note] 第3步：表auth_function的行数:27,limit行数:13,得到where条件中(af.id = 1)的选择度:13

2017-07-28 15:44:12 1198 [Note] 第4步：开始解析join on条件:af.id=afp.function_id

2017-07-28 15:44:12 1198 [Note] 第5步：开始选择驱动表,一共有2个候选驱动表

2017-07-28 15:44:12 1198 [Note] explain select * from auth_function

Segmentation fault

centos7 安装最后一步报错了，怎么解决？

报错如下：

make

Linking CXX executable sqladvisor
/usr/bin/ld: cannot find -lperconaserverclient_r
collect2: error: ld returned 1 exit status
make[2]: *** [sqladvisor] Error 1
make[1]: *** [CMakeFiles/sqladvisor.dir/all] Error 2
make: *** [all] Error 2

建议代码中添加.gitignore文件，以免使用IDE工具查看项目时自动编译生成的临时文件影响git status

.gitignore文件可以参考mysql-server, mariadb-server提供的ignore文件

单条语句没有给出优化建议

单条语句没有给出优化建议
sql
select * from temp_student where tempstu_code='学号278641' and tempstu_name_cn like '姓名35264%'

ddl
CREATE TABLE temp_student (
tempstu_id varchar(32) NOT NULL DEFAULT '' COMMENT '主键',
tempstu_grade_id varchar(32) NOT NULL DEFAULT '' COMMENT '班级ID',
tempstu_code varchar(32) NOT NULL DEFAULT '' COMMENT '学号',
tempstu_name_cn varchar(16) NOT NULL DEFAULT '' COMMENT '姓名',
tempstu_sex tinyint(1) NOT NULL DEFAULT '0' COMMENT '性别',
tempstu_mobile varchar(11) NOT NULL DEFAULT '' COMMENT '联系电话',
tempstu_qq varchar(12) DEFAULT '' COMMENT 'QQ',
tempstu_email varchar(64) NOT NULL DEFAULT '' COMMENT 'Email',
tempstu_interest varchar(256) DEFAULT NULL COMMENT '兴趣爱好、特长',
tempstu_info text NOT NULL COMMENT '个人简历',
tempstu_birth_time datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '出生时间',
tempstu_coming_time datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入学时间',
tempstu_create_user varchar(32) DEFAULT NULL COMMENT '创建人',
tempstu_create_time datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '发送时间',
PRIMARY KEY (tempstu_id),
KEY idx_ctime (tempstu_create_time)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='学生';

执行计划

[Note] 第1步: 对SQL解析优化之后得到的SQL:select * AS * from youren_test.temp_student where ((tempstu_code = '学号278641') and (tempstu_name_cn like '姓名35264%'))

2017-03-10 17:00:23 8770 [Note] 第2步：开始解析where中的条件:(tempstu_code = '学号278641')

2017-03-10 17:00:23 8770 [Note] show index from temp_student

2017-03-10 17:00:23 8770 [Note] show table status like 'temp_student'

2017-03-10 17:00:23 8770 [Note] select count(*) from ( select tempstu_code from temp_student FORCE INDEX( PRIMARY ) order by tempstu_id DESC limit 0) temp_student where (tempstu_code = '学号278641')

2017-03-10 17:00:23 8770 [Note] 第3步：表temp_student的行数:0,limit行数:0,得到where条件中(tempstu_code = '学号278641')的选择度:0

2017-03-10 17:00:23 8770 [Note] 第4步：开始解析where中的条件:(tempstu_name_cn like '姓名35264%')

2017-03-10 17:00:23 8770 [Note] show index from temp_student

2017-03-10 17:00:23 8770 [Note] show table status like 'temp_student'

2017-03-10 17:00:23 8770 [Note] select count(*) from ( select tempstu_name_cn from temp_student FORCE INDEX( PRIMARY ) order by tempstu_id DESC limit 0) temp_student where (tempstu_name_cn like '姓名35264%')

2017-03-10 17:00:23 8770 [Note] 第5步：表temp_student的行数:0,limit行数:0,得到where条件中(tempstu_name_cn like '姓名35264%')的选择度:0

2017-03-10 17:00:23 8770 [Note] 第6步：表temp_student 的SQL太逆天,没有优化建议

2017-03-10 17:00:23 8770 [Note] 第7步: SQLAdvisor结束!

[root@db1 sqladvisor]# ./sqladvisor -f sql.cnf -v 1
2017-03-10 17:00:43 9092 [Note] 第1步: 对SQL解析优化之后得到的SQL:select * AS * from youren_test.temp_student where ((tempstu_code = '学号278641') and (tempstu_name_cn like '姓名35264%'))

2017-03-10 17:00:43 9092 [Note] 第2步：开始解析where中的条件:(tempstu_code = '学号278641')

2017-03-10 17:00:43 9092 [Note] show index from temp_student

2017-03-10 17:00:43 9092 [Note] show table status like 'temp_student'

2017-03-10 17:00:43 9092 [Note] select count(*) from ( select tempstu_code from temp_student FORCE INDEX( PRIMARY ) order by tempstu_id DESC limit 0) temp_student where (tempstu_code = '学号278641')

2017-03-10 17:00:43 9092 [Note] 第3步：表temp_student的行数:0,limit行数:0,得到where条件中(tempstu_code = '学号278641')的选择度:0

2017-03-10 17:00:43 9092 [Note] 第4步：开始解析where中的条件:(tempstu_name_cn like '姓名35264%')

2017-03-10 17:00:43 9092 [Note] show index from temp_student

2017-03-10 17:00:43 9092 [Note] show table status like 'temp_student'

2017-03-10 17:00:43 9092 [Note] select count(*) from ( select tempstu_name_cn from temp_student FORCE INDEX( PRIMARY ) order by tempstu_id DESC limit 0) temp_student where (tempstu_name_cn like '姓名35264%')

2017-03-10 17:00:43 9092 [Note] 第5步：表temp_student的行数:0,limit行数:0,得到where条件中(tempstu_name_cn like '姓名35264%')的选择度:0

2017-03-10 17:00:43 9092 [Note] 第6步：表temp_student 的SQL太逆天,没有优化建议

2017-03-10 17:00:43 9092 [Note] 第7步: SQLAdvisor结束!

只能对简单的单表或2个表 join 做分析，对于复杂SQL冗余或复杂的基本上分析不了

[root@localhost ~]# sqladvisor -u mpup -p mpup -P 3306 -h 127.0.0.1 -d mpup -q "select log_id,user_name,log_type,log_createtime,log_level,log_formatter from (select log_id,user_name,log_type,log_createtime,log_level,log_formatter from (select log_id,user_name,log_type,log_createtime,log_level,log_formatter from LOG order by log_createtime desc,log_id asc ) orderedLog ) logs limit 0, 10;" -v 1
2017-11-09 17:29:17 5726 [Note] 第1步: 对SQL解析优化之后得到的SQL:select log_id AS log_id,user_name AS user_name,log_type AS log_type,log_createtime AS log_createtime,log_level AS log_level,log_formatter AS log_formatter from (select log_id AS log_id,user_name AS user_name,log_type AS log_type,log_createtime AS log_createtime,log_level AS log_level,log_formatter AS log_formatter from (select log_id AS log_id,user_name AS user_name,log_type AS log_type,log_createtime AS log_createtime,log_level AS log_level,log_formatter AS log_formatter from mpup.LOG order by log_createtime desc,log_id) orderedLog) logs limit 0,10

2017-11-09 17:29:17 5726 [Note] 第2步：表* 是临时表,不进行处理

2017-11-09 17:29:17 5726 [Note] 第3步：表* 是临时表,不进行处理

2017-11-09 17:29:17 5726 [Note] 第4步: SQLAdvisor结束!

安装SQLAdvisor源码时出错

按照https://github.com/Meituan-Dianping/SQLAdvisor/blob/master/doc/QUICK_START.md ，执行到1.4中的第三步make时报错，内容如下：

Linking CXX executable sqladvisor
/usr/bin/ld: cannot find -lperconaserverclient_r
collect2: 错误：ld 返回 1
make[2]: *** [sqladvisor] 错误 1
make[1]: *** [CMakeFiles/sqladvisor.dir/all] 错误 2
make: *** [all] 错误 2

系统信息：

LSB Version:	:core-4.1-amd64:core-4.1-noarch
Distributor ID:	CentOS
Description:	CentOS Linux release 7.0.1406 (Core)
Release:	7.0.1406
Codename:	Core

能否离线使用

不连接数据库，单纯的解析SQL

Invalid parameter number

安装过程能不能再简单一点

安装过程能不能再简单一点，依赖的东西有点麻烦，有时候会装不上什么的。

命令返回结果抓取不了？

    请问sqladvisor是不是写死输出到console了？为什么我重定向的时候没有日志输出。类似

sqladvisor -h xx -u xx -p xx -P 3306 -d xx -q "xx" > log。发现直接就能展示结果出来，但是log中并没有结果。

编译依赖项sqlparser时报错

root@ubuntu:~/SQLAdvisor/sqladvisor# make&&make install
Scanning dependencies of target sqladvisor
[100%] Building CXX object CMakeFiles/sqladvisor.dir/main.cc.o
/root/SQLAdvisor/sqladvisor/main.cc:6:24: fatal error: sql/mysqld.h: No such file or directory
#include "sql/mysqld.h"
^
compilation terminated.
make[2]: *** [CMakeFiles/sqladvisor.dir/main.cc.o] Error 1
make[1]: *** [CMakeFiles/sqladvisor.dir/all] Error 2
make: *** [all] Error 2

优化sql出现截断啥情况？

[root@f6-web-test opt]# sqladvisor -f sqladvisor.cnf -v 1
2017-05-24 17:01:25 15179 [Note] 第1步: 对SQL解析优化之后得到的SQL:select * AS * from (select m.pk_id AS pk_id,ifnull(m.service_subtotal,0) AS mService,ifnull(m.partinfo_subtotal,0) AS mPart,ifnull(s.servicePrice,0) AS service,ifnull(p.partPrice,0) AS part,ifnull(m.vip_expense,0) AS vip_expense,ifnull(m.czk_expense,0) AS czk_expense,m.modifiedtime AS modifiedtime from ((f6dms_1116_prod_backup.ts_maintain m left join (select id_maintain AS id_maintain,sum(subtotal) AS servicePrice from f6dms_1116_prod_backup.ts_maintain_service_detial group by id_maintain) s on((s.id_maintain = m.pk_id))) left join (select id_maintain AS id_maintain,sum(subtotal) AS partPrice from f6dms_1116_prod_backup.ts_maintain_part_detail where ((is_bring = 0) or isnull(is_bring)) group by id_maintain) p on((p.id_maintain = m.pk_id))) where ((m.is_migration = 0) and (m.is_del = 0))) a where ((abs((((a.mService + a.mpart) + a.vip_ 2017-05-24 17:01:25 15179 [Note] 第2步：开始解析where中的条件:(a.modifiedtime` > '2017-04-15 00:00:00')

2017-05-24 17:01:25 15179 [Note] 第3步：表* 是临时表,不进行处理

2017-05-24 17:01:25 15179 [Note] 第4步：表* 是临时表,不进行处理

2017-05-24 17:01:25 15179 [Note] 第5步：表* 是临时表,不进行处理

2017-05-24 17:01:25 15179 [Note] 第6步: SQLAdvisor结束!

配置文件
[sqladvisor] username=root password=root host=192.168.1.7 port=3306 dbname=f6dms_1116_prod_backup sqls=SELECT * FROM(SELECT m.pk_id, IFNULL(m.service_subtotal, 0) AS mService, IFNULL(m.partinfo_subtotal, 0) AS mPart, IFNULL(s.servicePrice, 0) AS service, IFNULL(p.partPrice, 0) AS part, ifNULL(m.vip_expense, 0) AS vip_expense, ifNULL(m.czk_expense, 0) AS czk_expense, m.modifiedtime FROMts_maintainm LEFT JOIN ( SELECT id_maintain, SUM(subtotal) AS servicePrice FROM ts_maintain_service_detial GROUP BY id_maintain ) s ON s.id_maintain = m.pk_idLEFT JOIN ( SELECT id_maintain, SUM(subtotal) AS partPrice FROM ts_maintain_part_detail WHERE is_bring = 0 OR is_bring IS NULL GROUP BY id_maintain ) p ON p.id_maintain = m.pk_id WHERE m.is_migration = 0 AND m.is_del = 0) a WHERE( abs( a.mService + a.mpart + a.vip_expense - (a.service + a.part) ) > 0.01 AND a.modifiedtime > '2017-04-15 00:00:00')
select * as *也是很奇怪的

ubuntu编译找不到包，有编译好的包吗

有编译好的包吗

最后一步安装报错，请问是什么原因呢？

20:47 $ uname -a
Linux ip-172-31-11-168 4.4.0-101-generic #124~14.04.1-Ubuntu SMP Fri Nov 10 19:05:36 UTC 2017 x86_64 x86_64 x86_64 GNU/Linux

✔ ~/SQLAdvisor/sqladvisor [master|✚ 3…60]
20:47 $ sudo cmake -DCMAKE_BUILD_TYPE=debug ./
-- Configuring done
-- Generating done
-- Build files have been written to: /home/ubuntu/SQLAdvisor/sqladvisor
✔ ~/SQLAdvisor/sqladvisor [master|✚ 3…60]
20:47 $ ll
total 108
drwxrwxr-x 3 ubuntu ubuntu 4096 Dec 5 20:47 ./
drwxrwxr-x 18 ubuntu ubuntu 4096 Dec 5 19:51 ../
-rw-r--r-- 1 root root 11988 Dec 4 13:47 CMakeCache.txt
drwxr-xr-x 6 root root 4096 Dec 5 20:47 CMakeFiles/
-rw-r--r-- 1 root root 1617 Dec 4 13:47 cmake_install.cmake
-rw-rw-r-- 1 ubuntu ubuntu 694 Dec 4 11:44 CMakeLists.txt
-rw-r--r-- 1 ubuntu ubuntu 12288 Dec 5 19:57 .CMakeLists.txt.swp
-rw-rw-r-- 1 ubuntu ubuntu 53195 Dec 4 11:44 main.cc
-rw-r--r-- 1 root root 4673 Dec 5 20:47 Makefile
-rw-rw-r-- 1 ubuntu ubuntu 257 Dec 4 11:44 README
✔ ~/SQLAdvisor/sqladvisor [master|✚ 3…60]
20:47 $ sudo make
[100%] Building CXX object CMakeFiles/sqladvisor.dir/main.cc.o
/home/ubuntu/SQLAdvisor/sqladvisor/main.cc:6:24: fatal error: sql/mysqld.h: No such file or directory
#include "sql/mysqld.h"
^
compilation terminated.
make[2]: *** [CMakeFiles/sqladvisor.dir/main.cc.o] Error 1
make[1]: *** [CMakeFiles/sqladvisor.dir/all] Error 2
make: *** [all] Error 2
✘-2 ~/SQLAdvisor/sqladvisor [master|✚ 3…60]
20:47 $

优化建立索引有问题

有a/b/c三张表
create table a (id int auto_increment,seller_id bigint,seller_name varchar(100) collate utf8_bin ,gmt_create varchar(30),primary key(id));
create table b (id int auto_increment,seller_name varchar(100),user_id varchar(50),user_name varchar(100),sales bigint,gmt_create varchar(30),primary key(id));
create table c (id int auto_increment,user_id varchar(50),order_id varchar(100),state bigint,gmt_create varchar(30),primary key(id));

使用sql
select a.seller_id,a.seller_name,b.user_name,c.state
from a,b,c
where a.seller_name=b.seller_name
and b.user_id=c.user_id
and c.user_id=17
and a.gmt_create BETWEEN DATE_ADD(NOW(), INTERVAL - 600 MINUTE)
AND DATE_ADD(NOW(), INTERVAL 600 MINUTE)
order by a.gmt_create

然后在建议c表时出现如下优化语句：
2017-07-19 11:15:53 4364 [Note] 第31步：开始验证字段user_id是不是主键。表名:c
2017-07-19 11:15:53 4364 [Note] show index from c where Key_name = 'PRIMARY' and Column_name ='user_id' and Seq_in_index = 1
2017-07-19 11:15:53 4364 [Note] 第32步：字段user_id不是主键。表名:c
2017-07-19 11:15:53 4364 [Note] 第33步：开始验证字段user_id是不是主键。表名:c
2017-07-19 11:15:53 4364 [Note] show index from c where Key_name = 'PRIMARY' and Column_name ='user_id' and Seq_in_index = 1
2017-07-19 11:15:53 4364 [Note] 第34步：字段user_id不是主键。表名:c
2017-07-19 11:15:53 4364 [Note] 第35步：开始验证表中是否已存在相关索引。表名:c, 字段名:user_id, 在索引中的位置:1
2017-07-19 11:15:53 4364 [Note] show index from c where Column_name ='user_id' and Seq_in_index =1
2017-07-19 11:15:53 4364 [Note] 第36步：开始验证字段user_id是不是主键。表名:c
2017-07-19 11:15:53 4364 [Note] show index from c where Key_name = 'PRIMARY' and Column_name ='user_id' and Seq_in_index = 1
2017-07-19 11:15:53 4364 [Note] 第37步：字段user_id不是主键。表名:c
2017-07-19 11:15:53 4364 [Note] 第38步：开始验证表中是否已存在相关索引。表名:c, 字段名:user_id, 在索引中的位置:2
2017-07-19 11:15:53 4364 [Note] show index from c where Column_name ='user_id' and Seq_in_index =2
2017-07-19 11:15:53 4364 [Note] 第39步：开始输出表c索引优化建议:
2017-07-19 11:15:53 4364 [Note] Create_Index_SQL：alter table c add index idx_user_id_user_id(user_id,user_id)

这个建议有两个user_id，但是语句执行错误的
[Err] 1060 - Duplicate column name 'user_id' 。
麻烦看一下。

-d 参数错误

为何-d错误，去掉引号也不行

sqladvisor-web 抛错 /bin/sh: ./app/native/sqladvisor: cannot execute binary file

sqladvisor-web 抛错 /bin/sh: ./app/native/sqladvisor: cannot execute binary file
什么情况？截图地址：error.png

优化出现一个报错

sql是可以查询的，但是优化过程出现了select * from * 结果报错，想请您帮忙看下，谢谢

优化sql：SELECT e.id, e.target_id, e.target_num, e.client_name, e.business_name, e.collection_name, e.client_order_num, e.region, e.pick_time, e.late_age, e.households, e.amount, e.BALANCEMONEY, ( e.amount - e.BALANCEMONEY - e.apply_money ) chargeMoney, e.agent_term, e.rateStr, ( SELECT f.KEY_VALUE FROM t_data_dictionary f WHERE f.KEY_NAME = 'targetState' AND f.KEY_PROP = e.audit_status ) audit_status, e.CREATE_TIME, e.UPDATE_TIME, e.target_type, e.signTarget, e.collection_commission, e.platform_rate, m.debt_type_1 debtType1, e.come_from, e.CREATE_TIME, e.due_time, m.balance_type balanceType, m.typeBank5, m.typeCount1, m.typeCount2, m.typeCount3, m.typeCount4, m.typeCount5, m.typeCountName1, m.typeCountName2, m.typeCountName5, e.audit_status auditStatusFalg, e.apply_money, e.collection_info_id FROM e_client_target e, ( SELECT m.balance_type, m.typeBank5, m.typeCount1, m.typeCount2, m.typeCount3, m.typeCount4, m.typeCount5, m.typeCountName1, m.typeCountName2, m.typeCountName5, m.debt_type_1, m.client_target_id FROM d_debtor_info m GROUP BY m.client_target_id ) m WHERE e.target_id = m.client_target_id GROUP BY m.client_target_id ORDER BY e.pick_time DESC

优化输出
2017-05-03 11:10:01 2341 [Note] 第1步: 对SQL解析优化之后得到的SQL:select e.id AS id,e.target_id AS target_id,e.target_num AS target_num,e.client_name AS client_name,e.business_name AS business_name,e.collection_name AS collection_name,e.client_order_num AS client_order_num,e.region AS region,e.pick_time AS pick_time,e.late_age AS late_age,e.households AS households,e.amount AS amount,e.BALANCEMONEY AS BALANCEMONEY,((e.amount - e.BALANCEMONEY) - e.apply_money) AS chargeMoney,e.agent_term AS agent_term,e.rateStr AS rateStr,(...) AS audit_status,e.CREATE_TIME AS CREATE_TIME,e.UPDATE_TIME AS UPDATE_TIME,e.target_type AS target_type,e.signTarget AS signTarget,e.collection_commission AS collection_commission,e.platform_rate AS platform_rate,m.debt_type_1 AS debtType1,e.come_from AS come_from,e.CREATE_TIME AS CREATE_TIME,e.due_time AS due_time,m.balance_type AS `balan
2017-05-03 11:10:01 2341 [Note] 第2步：开始解析join on条件:e.target_id=m.client_target_id

2017-05-03 11:10:01 2341 [Note] 第3步：表* 是临时表,不进行处理

2017-05-03 11:10:01 2341 [Note] 第4步：开始选择驱动表,一共有2个候选驱动表

2017-05-03 11:10:01 2341 [Note] explain select * from e_client_target

2017-05-03 11:10:01 2341 [Note] 第5步：候选驱动表e_client_target的结果集行数为:629

2017-05-03 11:10:01 2341 [Note] explain select * from *

2017-05-03 11:10:01 2341 [Note] 第6步：SQLAdvisor结束！错误日志: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 '*' at line 1

优化建议能否增加表或者视图判断

优化建议能否增加表或者视图判断，视图无法添加索引

可以支持视图和子查询吗？

可以支持视图和子查询吗？谢谢！

Segmentation fault (core dumped)

初次安装SQLAdvisor费了老大劲了，终于安装成功。准备验证奇迹的时刻，在sql语句的分析中简单的sql分析执行的都没问题，但是到了一些复杂的sql时偶尔会报错，报错信息如下：
Segmentation fault (core dumped)
查看linux系统日志发现了如下内容：
kernel: sqladvisor[24453]: segfault at 0 ip 000000344da361a2 sp 00007fff68c32a90 error 4 in libc-2.14.so[344da00000+183000]
待求大神解决

sqladvisor-web SQLAdvisor结束！错误日志:Invalid parameter number

2017-07-28 13:56:41 58531 [Note] 第1步: 对SQL解析优化之后得到的SQL:select * AS * from qk_auth.auth_function where (id = 100)

2017-07-28 13:56:41 58531 [Note] 第2步：开始解析where中的条件:(id = 100)

2017-07-28 13:56:41 58531 [Note] 第3步：SQLAdvisor结束！错误日志:Invalid parameter number

针对无where条件的SQL，不能给出优化建议

select max(num) from tbname;
select distinct A from tbname;
select title,count(*) from tbname group by title;

类似的没有where条件的sql，不能给出优化建议，SQL也不逆天：）

/usr/bin/ld: cannot find -lperconaserverclient_r

安装 SQLAdvisor 源码这一步，当执行 make 时提示如下错误信息：

Linking CXX executable sqladvisor
/usr/bin/ld: cannot find -lperconaserverclient_r
collect2: ld returned 1 exit status
make[2]: *** [sqladvisor] Error 1
make[1]: *** [CMakeFiles/sqladvisor.dir/all] Error 2
make: *** [all] Error 2

版本信息如下：

cmake: cmake version 2.8.12.2
glibc: glibc-2.12-1.166.el6_7.7.x86_64

注：已通过 rpm 包安装 Percona-Server-shared-56-5.6.25-rel73.1.el6.x86_64.rpm

谢谢。

relocation R_X86_64_32 against `std::bad_typeid::~bad_typeid()'

[ 93%] Building CXX object sql/CMakeFiles/sqlparser.dir/sql_list.cc.o
[ 93%] Building CXX object sql/CMakeFiles/sqlparser.dir/sql_plugin.cc.o
[ 94%] Building CXX object sql/CMakeFiles/sqlparser.dir/sql_show.cc.o
/root/SQLAdvisor-master/sql/sql_show.cc:92: warning: ‘sys_privileges’ defined but not used
[ 94%] Building CXX object sql/CMakeFiles/sqlparser.dir/sql_signal.cc.o
[ 94%] Building C object sql/CMakeFiles/sqlparser.dir/sql_state.c.o
[ 95%] Building CXX object sql/CMakeFiles/sqlparser.dir/sql_string.cc.o
[ 95%] Building CXX object sql/CMakeFiles/sqlparser.dir/sql_table.cc.o
[ 96%] Building CXX object sql/CMakeFiles/sqlparser.dir/sql_time.cc.o
[ 96%] Building CXX object sql/CMakeFiles/sqlparser.dir/strfunc.cc.o
[ 96%] Building CXX object sql/CMakeFiles/sqlparser.dir/sys_vars.cc.o
[ 97%] Building CXX object sql/CMakeFiles/sqlparser.dir/table.cc.o
[ 97%] Building CXX object sql/CMakeFiles/sqlparser.dir/thr_malloc.cc.o
[ 97%] Building C object sql/CMakeFiles/sqlparser.dir//sql-common/my_time.c.o
[ 98%] Building C object sql/CMakeFiles/sqlparser.dir//sql-common/pack.c.o
[ 98%] Building CXX object sql/CMakeFiles/sqlparser.dir/event_parse_data.cc.o
[ 99%] Building CXX object sql/CMakeFiles/sqlparser.dir/mysqld.cc.o
/root/SQLAdvisor-master/sql/mysqld.cc:273: warning: ‘tc_heuristic_recover_typelib’ defined but not used
/root/SQLAdvisor-master/sql/mysqld.cc:283: warning: ‘opt_autocommit’ defined but not used
/root/SQLAdvisor-master/sql/mysqld.cc:288: warning: ‘opt_help’ defined but not used
/root/SQLAdvisor-master/sql/mysqld.cc:288: warning: ‘opt_verbose’ defined but not used
/root/SQLAdvisor-master/sql/mysqld.cc:330: warning: ‘lower_case_table_names_used’ defined but not used
/root/SQLAdvisor-master/sql/mysqld.cc:333: warning: ‘opt_debugging’ defined but not used
/root/SQLAdvisor-master/sql/mysqld.cc:333: warning: ‘opt_external_locking’ defined but not used
/root/SQLAdvisor-master/sql/mysqld.cc:333: warning: ‘opt_console’ defined but not used
/root/SQLAdvisor-master/sql/mysqld.cc:334: warning: ‘opt_short_log_format’ defined but not used
/root/SQLAdvisor-master/sql/mysqld.cc:705: warning: ‘remaining_argc’ defined but not used
/root/SQLAdvisor-master/sql/mysqld.cc:707: warning: ‘remaining_argv’ defined but not used
/root/SQLAdvisor-master/sql/mysqld.cc:962: warning: ‘void buffered_option_error_reporter(loglevel, const char*, ...)’ defined but not used
/root/SQLAdvisor-master/sql/mysqld.cc:1037: warning: ‘plugins_are_initialized’ defined but not used
/root/SQLAdvisor-master/sql/mysqld.cc:2349: warning: ‘int test_if_case_insensitive(const char*)’ defined but not used
/root/SQLAdvisor-master/sql/mysqld.cc:1103: warning: ‘void usage()’ declared ‘static’ but never defined
/root/SQLAdvisor-master/sql/mysqld.cc:1105: warning: ‘void wait_for_signal_thread_to_end()’ declared ‘static’ but never defined
/root/SQLAdvisor-master/sql/mysqld.cc:1309: warning: ‘void set_root(const char*)’ defined but not used
/root/SQLAdvisor-master/sql/mysqld.cc:1462: warning: ‘bool init_global_datetime_format(timestamp_type, DATE_TIME_FORMAT*)’ defined but not used
/root/SQLAdvisor-master/sql/mysqld.cc:2052: warning: ‘void print_version()’ defined but not used
Linking CXX shared library libsqlparser-debug.so
/usr/bin/ld: /usr/lib/gcc/x86_64-redhat-linux/4.4.7/libstdc++.a(functexcept.o): relocation R_X86_64_32 against `std::bad_typeid::~bad_typeid()' can not be used when making a shared object; recompile with -fPIC
/usr/lib/gcc/x86_64-redhat-linux/4.4.7/libstdc++.a: could not read symbols: Bad value
collect2: ld returned 1 exit status
make[2]: *** [sql/libsqlparser-debug.so] Error 1
make[1]: *** [sql/CMakeFiles/sqlparser.dir/all] Error 2
make: *** [all] Error 2

编译出错

insert语句在执行过程中是否会向数据库中真正插入数据？

insert语句在结果上是最终没有插入数据的，但是在执行过程中是否是“先插入数据到数据库，测试后删除”呢？

Percona-Server-shared-56装不上去？麻烦看一下，谢谢~~~

安装的时候报错如图：

配置percona56 yum源已经操作过了。（ yum install http://www.percona.com/downloads/percona-release/redhat/0.1-3/percona-release-0.1-3.noarch.rpm）

我的系统是阿里云上的centos7。

yum install --enablerepo=Percona56 Percona-Server-shared-56 执行出错

返回信息：
已加载插件：fastestmirror, presto

Error getting repository data for Percona56, repository not found

mysql_init 返回 NULL

问题是：在执行完命令./sqladvisor -f sql.cnf -v 1后，错误日志什么都没有。
以下是输出日志：
2017-03-31 15:50:01 15432 [Note] 第1步: 对SQL解析优化之后得到的SQL:select count(1) AS count(1) from im_server.message where (msgsender = '10006')
2017-03-31 15:50:01 15432 [Note] 第2步：开始解析where中的条件:(msgsender = '10006')
2017-03-31 15:50:01 15432 [Note] show index from message
2017-03-31 15:50:01 15432 [Note] 第3步：SQLAdvisor结束！错误日志:

后来在sqladvisor里面的main.cc添加一些日志，发现在mysql_sql_parse_field_cardinality_new的MYSQL *con = mysql_init(NULL);方法返回的con为NULL，后来单独写了个demo，验证mysql_init是否好使，确定返回非NULL,mysql环境是percona5.5，看官网推荐是5.6，会不会跟这个有问题，求助，先写了

编译时出现converting to non-pointer Warning

/usr/local/sqlparser/include/sql/item_timefunc.h: In member function ‘virtual longlong Item_time_func::val_int()’: /usr/local/sqlparser/include/sql/item_timefunc.h:533:12: warning: converting to non-pointer type ‘longlong {aka long long int}’ from NULL [-Wconversion-null] return NULL;
版本：
g++ (GCC) 4.8.5 20150623 (Red Hat 4.8.5-11)
glibc：2.17

Ubuntu下的安装成功的步骤梳理，给大家参考

1.拉取代码
git clone https://github.com/Meituan-Dianping/SQLAdvisor.git

2.安装依赖项

apt-get install cmake libaio-dev libffi-dev
apt-get install libglib2.0-dev
apt-get install libperconaserverclient18.1-dev
//g++不知道有没有起作用，中间各种出错后尝试安装了他
apt-get install g++

percona的安装参考 https://www.percona.com/doc/percona-server/5.6/installation/apt_repo.html

3.编译依赖项sqlparser

cmake -DBUILD_CONFIG=mysql_release -DCMAKE_BUILD_TYPE=debug -DCMAKE_INSTALL_PREFIX=/usr/local/sqlparser ./
make && make install

4.安装SQLAdvisor源码

cd SQLAdvisor/sqladvisor/
cmake -DCMAKE_BUILD_TYPE=debug ./
make
在本路径下生成一个sqladvisor可执行文件，这即是我们想要的。

Segmentation fault

1.OS version:CentOS release 6.5 (Final) MySQL version:5.7.16-log

2.error
2017-03-10 14:41:26 18622 [Note] 第1步: 对SQL解析优化之后得到的SQL:select count(1) AS COUNT(1) from (mydb.archive a left join mydb.archive_detail ad on((a.id = ad.archive_id))) where (a.user_id = 231)
2017-03-10 14:41:26 18622 [Note] 第2步：开始解析where中的条件:(a.user_id = 231)
2017-03-10 14:41:26 18622 [Note] show index from archive
2017-03-10 14:41:26 18622 [Note] show table status like 'archive'
2017-03-10 14:41:26 18622 [Note] select count(*) from ( select user_id from archive FORCE INDEX( PRIMARY ) order by id DESC limit 4) a where (a.user_id = 231)
2017-03-10 14:41:26 18622 [Note] 第3步：表archive的行数:9,limit行数:4,得到where条件中(a.user_id = 231)的选择度:4
2017-03-10 14:41:26 18622 [Note] 第4步：开始解析join on条件:a.id=ad.archive_id
2017-03-10 14:41:26 18622 [Note] 第5步：开始选择驱动表,一共有1个候选驱动表
2017-03-10 14:41:26 18622 [Note] explain select * from archive
Segmentation fault

3.table ddl
CREATE TABLE archive (
id bigint(20) NOT NULL AUTO_INCREMENT,
signed_summary_id bigint(20) DEFAULT NULL,
user_id bigint(20) DEFAULT NULL,
tilte varchar(200) DEFAULT NULL,
archive_type int(11) DEFAULT NULL,
archive_date date DEFAULT NULL,
created1 bigint(20) DEFAULT NULL,
created2 varchar(50) DEFAULT NULL,
created_date datetime DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=270 DEFAULT CHARSET=utf8mb4;

CREATE TABLE archive_detail (
id bigint(20) NOT NULL AUTO_INCREMENT,
archive_id bigint(20) DEFAULT NULL,
archive_type int(11) DEFAULT NULL,
archive_type_name varchar(20) DEFAULT NULL,
content varchar(2000) DEFAULT NULL,
pic varchar(200) DEFAULT NULL,
voice varchar(2000) DEFAULT NULL,
created_date datetime DEFAULT NULL,
service_remark int(5) DEFAULT '0',
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=420 DEFAULT CHARSET=utf8mb4;

debian下libaio-devel libffi-devel glib2 glib2-devel这几个包找不到

似乎对应的是这几个包：libaio-dev libffi-dev libglib2.0-0 libglib2.0-dev

数据库支持

请问这个工具有没有原理介绍，只支持MySQL数据库吗，其它数据库也支持吗

docker里安装完后，执行SQL，报错New state of 'nil' is invalid.

[root@044d01672267 sqladvisor]# sqladvisor -h 192.168.13.17 -u root -p root -P 3
306 -d hb -q "SELECT loanterm,ApproveTerm,inputorgid,businesstype FROM business_
apply WHERE serialno='B00501003011610130001';" -v 1
New state of 'nil' is invalid.
有谁知道什么问题？

命令行参数出现中文会报错: Invalid byte sequence in conversion input

完成安装测试时提示信息无法解决

提示无法处理 -P 所用的整数值“xx”

mac OS Sierra 下如何安装？

SQLAdvisor 安装 aws环境

#SQLAdvisor 安装

1. SQLAdvisor安装

环境：Linux 86_64 GNU/Linux

1.1 拉取最新代码

git clone https://github.com/Meituan-Dianping/SQLAdvisor.git

1.2 安装依赖项

 1. yum | apt-get  install cmake libaio-devel libffi-devel glib2 glib2-devel
 2. yum | apt-get  install --enablerepo=Percona56 Percona-Server-shared-56

第2步安装失败，调整：

cd /d2platform/
wget https://www.percona.com/downloads/Percona-Server-5.6/Percona-Server-5.6.25-73.1/binary/redhat/6/x86_64/Percona-Server-5.6.25-73.1-r07b797f-el6-x86_64-bundle.tar
tar -xvf Percona-Server-5.6.25-73.1-r07b797f-el6-x86_64-bundle.tar
rpm -ivh Percona-Server-shared-56-5.6.25-rel73.1.el6.x86_64.rpm
或者
yum install http://www.percona.com/downloads/percona-release/redhat/0.1-3/percona-release-0.1-3.noarch.rpm

上面两步是在issue里面看到的。我都装了。。

注意

跟据glib安装的路径，修改SQLAdvisor/sqladvisor/CMakeLists.txt中的两处include_directories针对glib设置的path。glib yum 安装默认不需要修改路径
编译sqladvisor时依赖perconaserverclient_r, 因此需要安装Percona-Server-shared-56。有可能需要配置软链接例如:1. cd /usr/lib64/ 2. ln -s libperconaserverclient_r.so.18 libperconaserverclient_r.so
有可能需要配置percona56 yum源: yum install http://www.percona.com/downloads/percona-release/redhat/0.1-3/percona-release-0.1-3.noarch.rpm

1.3 编译依赖项sqlparser

cd SQLAdvisor

1. cmake -DBUILD_CONFIG=mysql_release -DCMAKE_BUILD_TYPE=debug -DCMAKE_INSTALL_PREFIX=/usr/local/sqlparser ./
2. make && make install

注意

DCMAKE_INSTALL_PREFIX为sqlparser库文件和头文件的安装目录，其中lib目录包含库文件libsqlparser.so，include目录包含所需的所有头文件。
DCMAKE_INSTALL_PREFIX值尽量不要修改，后面安装依赖这个目录。

1.4 安装SQLAdvisor源码

1. cd SQLAdvisor/sqladvisor/
2. cmake -DCMAKE_BUILD_TYPE=debug ./
3. make
4. 在本路径下生成一个sqladvisor可执行文件，这即是我们想要的。

eg:

./sqladvisor -h .com -P 3306 -u *** -p '' -d db_billing -q "select * from tb_cashier limit 10" -v 1

第一步sql优化长度达到1000以上会被截断

[Note] 第1步: 对SQL解析优化之后得到的SQL:select product_orgz.product_id AS product_id,products.code AS product_code,products.name AS product_name,products.product_base_code AS product_base_code,products.min_spec_unit AS sales_unit,min_spec.name AS unit_name,products.order_spec_unit AS order_spec_unit,products.order_spec_num AS spec_num,order_spec.name AS spec_unit,products.weight_type AS weight_type,products.c_code AS c_code from (((test.product_orgz join test.products on((products.id = product_orgz.product_id))) join test.product_units min_spec on((min_spec.id = products.min_spec_unit))) join test.product_units order_spec on((order_spec.id = products.order_spec_unit))) where ((product_orgz.hq_code = '000001') and (products.code <> 123) and (product_orgz.orgz_id = 12) and (product_orgz.is_manager = 1) and (product_orgz.is_require = 0) and (product_orgz.status = 1) and ((

socket不能指定？

我的socket位置不是标准的，就报错了，难道一定要位于/var/lib/mysql/下？

2017-06-12 18:27:06 19007 [Note] 第3步：SQLAdvisor结束！错误日志:Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)

支持percona server 5.7 吗

编译的时候percona-server-shared-57 的libperconaserverclient.so是没有的，但是我将libmysqlclient.so.20做软连接后编译是可以的，不知这样对使用有问题吗

测试了下，结果不对啊，并没有给出合适的索引建议，是我使用不对吗？

mysql> show create table test\G
*************************** 1. row ***************************
Table: test
Create Table: CREATE TABLE test (
id bigint(20) DEFAULT NULL,
name char(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

用下面的存储过程插入100000条数据

create procedure test()
begin
declare i bigint;
set i=0;
while i<100000 do
insert into test values(i,cast(i as char(10)));
set i=i+1;
end while;
end;//

mysql> SHOW TABLE STATUS\G
*************************** 1. row ***************************
Name: test
Engine: InnoDB
Version: 10
Row_format: Dynamic
Rows: 99937
Avg_row_length: 47
Data_length: 4734976
Max_data_length: 0
Index_length: 0
Data_free: 4194304
Auto_increment: NULL
Create_time: 2017-11-06 09:52:16
Update_time: 2017-11-06 11:53:32
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)

[root@szxts10011040 sqladvisor]# cat sql.cnf
[sqladvisor]
username=graTemp
password=*****
host=*****
port=50006
dbname=lych
sqls=select * from lych.test where id=1000;

[root@szxts10011040 sqladvisor]# ./sqladvisor -f sql.cnf -v 1
2017-11-06 13:51:35 66459 [Note] 2017-11-06 13:51:35 66459 [Note] 第2步：开始解析where中的条件:(id = 1000)

2017-11-06 13:51:35 66459 [Note] show index from test

2017-11-06 13:51:35 66459 [Note] 第3步：SQLAdvisor结束！表中没有任何索引

不规范索引名称导致优化过程出现语法错误

当索引名称不规范，包含中横线（-），在force index时会出现语法报错，可用``包括。

2017-04-28 09:25:27 98697 [Note] show table status like 'play'

2017-04-28 09:25:27 98697 [Note] select count(*) from ( select start_time from play FORCE INDEX( cinema_play-id ) order by cinema_id DESC,cine_play_id DESC limit 10000) play where (start_time > '2017-04-19 01:02:54')

2017-04-28 09:25:27 98697 [Note] 第5步：SQLAdvisor结束！错误日志: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 '-id ) order by cinema_id DESC,cine_play_id DESC limit 10000) play where (`star' at line 1

meituan-dianping / sqladvisor Goto Github PK

sqladvisor's Introduction

一、简介

二、SQLAdvisor详细说明

三、SQLAdvisor的需求及Bug反馈方式

sqladvisor's People

Contributors

Stargazers

Watchers

Forkers

sqladvisor's Issues

make

1. SQLAdvisor安装

1.1 拉取最新代码

1.2 安装依赖项

1.3 编译依赖项sqlparser

1.4 安装SQLAdvisor源码

Recommend Projects

Recommend Topics

Recommend Org