???????
????Part1: д???
????SQLAdvisor?????????????SQL??????????鶴??? ???????????????????????DBA??????????????????????????SQL??????????????????????????MySQL???????????????????SQL?е?where??????????????????Join??? ??????????????顣 ??SQLAdvisor??????????????????????????SQLAdvisor???????????github???????????????????? ??????????ù?????????????????á?
???????
????Part1: ???????????
????[root@HE3 ~]# yum install git
????[root@HE3 ~]# git clone https://github.com/Meituan-Dianping/SQLAdvisor.git
????[root@HE3 ~]# yum install cmake libaio-devel libffi-devel glib2 glib2-devel
????[root@HE3 ~]# yum install http://www.percona.com/downloads/percona-release/redhat/0.1-3/percona-release-0.1-3.noarch.rpm
????[root@HE3 ~]# yum install Percona-Server-shared-56
????[root@HE3 lib64]# cd /usr/lib64/
????[root@HE3 ~]# ln -s libperconaserverclient_r.so.18 libperconaserverclient_r.so
????Warning: ?? ??1
????????????????????yum install --enablerepo=Percona56 Percona-Server-shared-56???????????Percona-Server-shared-56???
????Warning: ?? ??2
????????glib?????·???????SQLAdvisor/sqladvisor/CMakeLists.txt?е?????include_directories???glib?????path?????????yum?????git????glib yum ?????????????·??
????Warning: ?? ??3
????????sqladvisor?????perconaserverclient_r?? ?????????Percona-Server-shared-56???п????????????????????:1. cd /usr/lib64/ 2. ln -s libperconaserverclient_r.so.18 libperconaserverclient_r.so
????Warning: ?? ??4
?????п??????????percona56 yum?: yum install http://www.percona.com/downloads/percona-release/redhat/0.1-3/percona-release-0.1-3.noarch.rpm
????[root@HE3 ~]#cmake -DBUILD_CONFIG=mysql_release -DCMAKE_BUILD_TYPE=debug -DCMAKE_INSTALL_PREFIX=/usr/local/sqlparser ./
????[root@HE3 ~]# make && make install
????[root@HE3 ~]# cd SQLAdvisor/sqladvisor
????[root@HE3 sqladvisor]# cmake -DCMAKE_BUILD_TYPE=debug ./
????[root@HE3 sqladvisor]# make
???????·???????????sqladvisor????????????????????????
???????
????Part1: ??С????в???
????[root@HE3 sqladvisor]# ./sqladvisor -h 127.0.0.1 -P 3306 -u root -p "MANAGER" -d helei1 -q "select * from helei1" -v 1
????2017-03-21 20:37:53 8581 [Note] ??1??: ??SQL??????????????SQL:select `*` AS `*` from `helei1`.`helei1`
????2017-03-21 20:37:53 8581 [Note] ??2??????helei1 ??SQL???????????????
????2017-03-21 20:37:53 8581 [Note] ??3??: SQLAdvisor????!
????Part2: ??????в????????????
??????????????helei???з??????????c1???????????
????[root@HE3 sqladvisor]# ./sqladvisor -h 127.0.0.1 -P 3306 -u root -p "MANAGER" -d helei1 -q "explain select * from helei where c1=88501;" -v 1
????2017-03-21 21:19:23 8624 [Note] ??1??: ??SQL??????????????SQL:select `*` AS `*` from `helei1`.`helei` where (`c1` = 88501)
????2017-03-21 21:19:23 8624 [Note] ??2???????????where?е?????:(`c1` = 88501)
????2017-03-21 21:19:23 8624 [Note] show index from helei
????2017-03-21 21:19:23 8624 [Note] show table status like 'helei'
????2017-03-21 21:19:23 8624 [Note] select count(*) from ( select `c1` from `helei` FORCE INDEX( PRIMARY ) order by id DESC limit 10000) `helei` where (`c1` = 88501)
????2017-03-21 21:19:23 8624 [Note] ??3??????helei??????:200380??limit????:10000?????where??????(`c1` = 88501)??????:10000
????2017-03-21 21:19:23 8624 [Note] ??4?????????? ???c1???????????????:helei
????2017-03-21 21:19:23 8624 [Note] show index from helei where Key_name = 'PRIMARY' and Column_name ='c1' and Seq_in_index = 1
????2017-03-21 21:19:23 8624 [Note] ??5???????c1??????????????:helei
????2017-03-21 21:19:23 8624 [Note] ??6?????????? ???c1???????????????:helei
????2017-03-21 21:19:23 8624 [Note] show index from helei where Key_name = 'PRIMARY' and Column_name ='c1' and Seq_in_index = 1
????2017-03-21 21:19:23 8624 [Note] ??7???????c1??????????????:helei
????2017-03-21 21:19:23 8624 [Note] ??8???????????????????????????????????:helei?? ?????:c1?? ???????е?λ??:1
????2017-03-21 21:19:23 8624 [Note] show index from helei where Column_name ='c1' and Seq_in_index =1
????2017-03-21 21:19:23 8624 [Note] ??9????????(c1)?????
????2017-03-21 21:19:23 8624 [Note] ??10??: SQLAdvisor????!
???????????????????????SQL????????Ч?????????
????Part3: ??????в????????????
??????????????helei???з??????????c5???????????
????[root@HE3 sqladvisor]# ./sqladvisor -h 127.0.0.1 -P 3306 -u root -p "MANAGER" -d helei1 -q "explain select * from helei where c5=74685;" -v 1
????2017-03-21 21:20:53 8628 [Note] ??1??: ??SQL??????????????SQL:select `*` AS `*` from `helei1`.`helei` where (`c5` = 74685)
????2017-03-21 21:20:53 8628 [Note] ??2???????????where?е?????:(`c5` = 74685)
????2017-03-21 21:20:53 8628 [Note] show index from helei
????2017-03-21 21:20:53 8628 [Note] show table status like 'helei'
????2017-03-21 21:20:53 8628 [Note] select count(*) from ( select `c5` from `helei` FORCE INDEX( PRIMARY ) order by id DESC limit 10000) `helei` where (`c5` = 74685)
????2017-03-21 21:20:53 8628 [Note] ??3??????helei??????:201361??limit????:10000?????where??????(`c5` = 74685)??????:10000
????2017-03-21 21:20:53 8628 [Note] ??4?????????? ???c5???????????????:helei
????2017-03-21 21:20:53 8628 [Note] show index from helei where Key_name = 'PRIMARY' and Column_name ='c5' and Seq_in_index = 1
????2017-03-21 21:20:53 8628 [Note] ??5???????c5??????????????:helei
????2017-03-21 21:20:53 8628 [Note] ??6?????????? ???c5???????????????:helei
????2017-03-21 21:20:53 8628 [Note] show index from helei where Key_name = 'PRIMARY' and Column_name ='c5' and Seq_in_index = 1
????2017-03-21 21:20:53 8628 [Note] ??7???????c5??????????????:helei
????2017-03-21 21:20:53 8628 [Note] ??8???????????????????????????????????:helei?? ?????:c5?? ???????е?λ??:1
????2017-03-21 21:20:53 8628 [Note] show index from helei where Column_name ='c5' and Seq_in_index =1
????2017-03-21 21:20:53 8628 [Note] ??9????????????helei???????????:
????2017-03-21 21:20:53 8628 [Note] Create_Index_SQL??alter table helei add index idx_c5(c5)
????2017-03-21 21:20:53 8628 [Note] ??10??: SQLAdvisor????!
????????????????????????????????
????Part4: ????SQL??????
????????????????????????????????helei.cnf???????????????????????SQL??SQL????÷??????
????[root@HE3 sqladvisor]# cat helei.cnf
????[sqladvisor]
????username=root
????password=MANAGER
????host=127.0.0.1
????port=3306
????dbname=helei1
????sqls=select * from helei where c1=88501;select * from helei where c5=74685;
???????????-f??????????helei.cnf?е?????
????[root@HE3 sqladvisor]# ./sqladvisor -f helei.cnf -v 1
????2017-03-21 21:27:35 8640 [Note] ??1??: ??SQL??????????????SQL:select `*` AS `*` from `helei1`.`helei` where (`c1` = 88501)
????2017-03-21 21:27:35 8640 [Note] ??2???????????where?е?????:(`c1` = 88501)
????2017-03-21 21:27:35 8640 [Note] show index from helei
????2017-03-21 21:27:35 8640 [Note] show table status like 'helei'
????2017-03-21 21:27:35 8640 [Note] select count(*) from ( select `c1` from `helei` FORCE INDEX( PRIMARY ) order by id DESC limit 10000) `helei` where (`c1` = 88501)
????2017-03-21 21:27:35 8640 [Note] ??3??????helei??????:200674??limit????:10000?????where??????(`c1` = 88501)??????:10000
????2017-03-21 21:27:35 8640 [Note] ??4?????????? ???c1???????????????:helei
????2017-03-21 21:27:35 8640 [Note] show index from helei where Key_name = 'PRIMARY' and Column_name ='c1' and Seq_in_index = 1
????2017-03-21 21:27:35 8640 [Note] ??5???????c1??????????????:helei
????2017-03-21 21:27:35 8640 [Note] ??6?????????? ???c1???????????????:helei
????2017-03-21 21:27:35 8640 [Note] show index from helei where Key_name = 'PRIMARY' and Column_name ='c1' and Seq_in_index = 1
????2017-03-21 21:27:35 8640 [Note] ??7???????c1??????????????:helei
????2017-03-21 21:27:35 8640 [Note] ??8???????????????????????????????????:helei?? ?????:c1?? ???????е?λ??:1
????2017-03-21 21:27:35 8640 [Note] show index from helei where Column_name ='c1' and Seq_in_index =1
????2017-03-21 21:27:35 8640 [Note] ??9????????(c1)?????
????2017-03-21 21:27:35 8640 [Note] ??10??: SQLAdvisor????!
????2017-03-21 21:27:35 8640 [Note] ??1??: ??SQL??????????????SQL:select `*` AS `*` from `helei1`.`helei` where (`c5` = 74685)
????2017-03-21 21:27:35 8640 [Note] ??2???????????where?е?????:(`c5` = 74685)
????2017-03-21 21:27:35 8640 [Note] show index from helei
????2017-03-21 21:27:35 8640 [Note] show table status like 'helei'
????2017-03-21 21:27:35 8640 [Note] select count(*) from ( select `c5` from `helei` FORCE INDEX( PRIMARY ) order by id DESC limit 10000) `helei` where (`c5` = 74685)
????2017-03-21 21:27:35 8640 [Note] ??3??????helei??????:201067??limit????:10000?????where??????(`c5` = 74685)??????:10000
????2017-03-21 21:27:35 8640 [Note] ??4?????????? ???c5???????????????:helei
????2017-03-21 21:27:35 8640 [Note] show index from helei where Key_name = 'PRIMARY' and Column_name ='c5' and Seq_in_index = 1
????2017-03-21 21:27:35 8640 [Note] ??5???????c5??????????????:helei
????2017-03-21 21:27:35 8640 [Note] ??6?????????? ???c5???????????????:helei
????2017-03-21 21:27:35 8640 [Note] show index from helei where Key_name = 'PRIMARY' and Column_name ='c5' and Seq_in_index = 1
????2017-03-21 21:27:35 8640 [Note] ??7???????c5??????????????:helei
????2017-03-21 21:27:35 8640 [Note] ??8???????????????????????????????????:helei?? ?????:c5?? ???????е?λ??:1
????2017-03-21 21:27:35 8640 [Note] show index from helei where Column_name ='c5' and Seq_in_index =1
????2017-03-21 21:27:35 8640 [Note] ??9????????????helei???????????:
????2017-03-21 21:27:35 8640 [Note] Create_Index_SQL??alter table helei add index idx_c5(c5)
????2017-03-21 21:27:35 8640 [Note] ??10??: SQLAdvisor????!
????????????????helei.cnf?е?????SQL???????????????????
????Warning: ?? ??5
????SQL?е???????or????????ú????????? ???????????
?????????д???sql??????????sql?е????????????? ?????????塣?????????????????????
?????D?D ??? ?D?D
?????????????SQLAdvisor????SQL???????????????????????飬?????????????????????????????? ?????????????????д??????????????????????Щ?????????????????????????????? ?????????