SQLAdvisor美團SQL索引優化建議工具

SQLAdvisor美團SQL索引優化建議工具

前言

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已經擁有有效索引的建議

Part2:對大表進行測試（無索引）

這里我們對表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結束!

可以看到，最后給出了創建索引的建議

Part3:多條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中的雙引號、反引號 都需要用\轉義。建議使用配置文件形式調用

——總結——

可以看出，SQLAdvisor對查詢SQL給出了索引創建的優化和建議，還是不錯的，后續會進一步進行深度測試。由于筆者的水平有限，編寫時間也很倉促，文中難免會出現一些錯誤或者不準確的地方，不妥之處懇請讀者批評指正。

參考：https://github.com/Meituan-Dianping/SQLAdvisor/blob/master/doc/QUICK_START.md