meituan-dianping / sqladvisor Goto Github PK
View Code? Open in Web Editor NEW输入SQL,输出索引优化建议
License: GNU General Public License v2.0
输入SQL,输出索引优化建议
License: GNU General Public License v2.0
/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
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
当索引名称不规范,包含中横线(-),在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
./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切分
返回信息:
已加载插件:fastestmirror, presto
Error getting repository data for Percona56, repository not found
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 $
似乎对应的是这几个包:libaio-dev libffi-dev libglib2.0-0 libglib2.0-dev
不连接数据库,单纯的解析SQL
[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 ((
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;
[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
.gitignore文件可以参考mysql-server, mariadb-server提供的ignore文件
初次安装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 -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,会不会跟这个有问题,求助,先写了
索然选择度为1,但是加上索引之后明显效果很好,但是sqladvisor却没有检测出来!很失望
解析第二步是出现的错误
sqladvisor-web 抛错 /bin/sh: ./app/native/sqladvisor: cannot execute binary file
什么情况? 截图地址:error.png
[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 FROM
ts_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 *也是很奇怪的
[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.
有谁知道什么问题?
有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' 。
麻烦看一下。
有编译好的包吗
[ 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
编译出错
[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结束!
insert语句在结果上是最终没有插入数据的,但是在执行过程中是否是“先插入数据到数据库,测试后删除”呢?
#SQLAdvisor 安装
环境:Linux 86_64 GNU/Linux
git clone https://github.com/Meituan-Dianping/SQLAdvisor.git
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里面看到的。我都装了。。
注意
有可能需要配置软链接例如:1. cd /usr/lib64/ 2. ln -s libperconaserverclient_r.so.18 libperconaserverclient_r.so
yum install http://www.percona.com/downloads/percona-release/redhat/0.1-3/percona-release-0.1-3.noarch.rpm
cd SQLAdvisor
1. cmake -DBUILD_CONFIG=mysql_release -DCMAKE_BUILD_TYPE=debug -DCMAKE_INSTALL_PREFIX=/usr/local/sqlparser ./
2. make && make install
注意
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
优化建议能否增加表或者视图判断,视图无法添加索引
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可执行文件,这即是我们想要的。
请问这个工具有没有原理介绍,只支持MySQL数据库吗,其它数据库也支持吗
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
配置percona56 yum源已经操作过了。( yum install http://www.percona.com/downloads/percona-release/redhat/0.1-3/percona-release-0.1-3.noarch.rpm)
我的系统是阿里云上的centos7。
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
编译的时候percona-server-shared-57 的libperconaserverclient.so是没有的,但是我将libmysqlclient.so.20做软连接后编译是可以的,不知这样对使用有问题吗
[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
可以支持视图和子查询吗? 谢谢!
安装过程能不能再简单一点,依赖的东西有点麻烦,有时候会装不上什么的。
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结束!表中没有任何索引
请问sqladvisor是不是写死输出到console了?为什么我重定向的时候没有日志输出。类似
sqladvisor -h xx -u xx -p xx -P 3306 -d xx -q "xx" > log。发现直接就能展示结果出来,但是log中并没有结果。
安装 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
版本信息如下:
注:已通过 rpm 包安装 Percona-Server-shared-56-5.6.25-rel73.1.el6.x86_64.rpm
谢谢。
[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]#
报错如下:
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
select max(num) from tbname;
select distinct A from tbname;
select title,count(*) from tbname group by title;
类似的没有where条件的sql,不能给出优化建议,SQL也不逆天:)
我的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)
按照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
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结束!
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.