meituan-dianping/sqladvisor

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

sqladvisor's Issues

/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

谢谢。

只能对简单的单表或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结束!

优化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 *也是很奇怪的

最后一步编译失败

[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

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

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

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]
待求大神解决

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

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

Error getting repository data for Percona56, repository not found

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可执行文件，这即是我们想要的。

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)

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

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

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

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。

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

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

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

命令返回结果抓取不了？

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

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

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

编译出错

安装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

mac OS Sierra 下如何安装？

支持percona server 5.7 吗

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

编译时出现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

能否离线使用

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

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 * 结果报错，想请您帮忙看下，谢谢

优化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

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.
有谁知道什么问题？

-d 参数错误

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

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

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

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

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;

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

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

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

当索引名称不规范，包含中横线（-），在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

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

优化建立索引有问题

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

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

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

Invalid parameter number

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

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

第一步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 ((

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

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 $

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

有编译好的包吗

编译依赖项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

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

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结束！表中没有任何索引

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

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

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

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

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，会不会跟这个有问题，求助，先写了

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

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

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

数据库支持

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

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

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

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

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

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