Giter Site home page Giter Site logo

meituan-dianping / sqladvisor Goto Github PK

View Code? Open in Web Editor NEW
5.5K 315.0 1.2K 19.93 MB

输入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详细说明

  1. SQLAdvisor快速入门教程
  2. SQLAdvisor架构和实践
  3. SQLAdvisor release notes
  4. SQLAdvisor开发规范
  5. FAQ

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

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

QQ

sqladvisor's People

Contributors

longxuegang avatar tsthght avatar

Stargazers

 avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar

Watchers

 avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar

sqladvisor's Issues

最后一步编译失败

[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

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

[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]#

命令行使用-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

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

单条语句没有给出优化建议
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

命令返回结果抓取不了?

    请问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 *也是很奇怪的

image

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

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' 。
麻烦看一下。

优化出现一个报错

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

/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

编译出错

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;

数据库支持

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

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步安装失败,调整:

  1. cd /d2platform/
  2. 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
  3. tar -xvf Percona-Server-5.6.25-73.1-r07b797f-el6-x86_64-bundle.tar
  4. rpm -ivh Percona-Server-shared-56-5.6.25-rel73.1.el6.x86_64.rpm
    或者
  5. yum install http://www.percona.com/downloads/percona-release/redhat/0.1-3/percona-release-0.1-3.noarch.rpm

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

注意

  1. 跟据glib安装的路径,修改SQLAdvisor/sqladvisor/CMakeLists.txt中的两处include_directories针对glib设置的path。glib yum 安装默认不需要修改路径
  2. 编译sqladvisor时依赖perconaserverclient_r, 因此需要安装Percona-Server-shared-56。有可能需要配置软链接例如:1. cd /usr/lib64/ 2. ln -s libperconaserverclient_r.so.18 libperconaserverclient_r.so
  3. 有可能需要配置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

注意

  1. DCMAKE_INSTALL_PREFIX为sqlparser库文件和头文件的安装目录,其中lib目录包含库文件libsqlparser.so,include目录包含所需的所有头文件。
  2. 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

Recommend Projects

  • React photo React

    A declarative, efficient, and flexible JavaScript library for building user interfaces.

  • Vue.js photo Vue.js

    🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.

  • Typescript photo Typescript

    TypeScript is a superset of JavaScript that compiles to clean JavaScript output.

  • TensorFlow photo TensorFlow

    An Open Source Machine Learning Framework for Everyone

  • Django photo Django

    The Web framework for perfectionists with deadlines.

  • D3 photo D3

    Bring data to life with SVG, Canvas and HTML. 📊📈🎉

Recommend Topics

  • javascript

    JavaScript (JS) is a lightweight interpreted programming language with first-class functions.

  • web

    Some thing interesting about web. New door for the world.

  • server

    A server is a program made to process requests and deliver data to clients.

  • Machine learning

    Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.

  • Game

    Some thing interesting about game, make everyone happy.

Recommend Org

  • Facebook photo Facebook

    We are working to build community through open source technology. NB: members must have two-factor auth.

  • Microsoft photo Microsoft

    Open source projects and samples from Microsoft.

  • Google photo Google

    Google ❤️ Open Source for everyone.

  • D3 photo D3

    Data-Driven Documents codes.