歡迎轉載，請註明作者、出處。

作者：劉春雷

blog：http://blog.itpub.net/28823725/
如有疑問，歡迎留言~

-------------------------------------------------------------------------------

SQLAdvisor調研與應用

--2017-05-23 劉春雷

一、需求

1.1、降低慢查詢量與影響

[1]、為降低線上慢查詢的數量與影響

[2]、為減少DBA日常優化的繁雜工作

[3]、為方便、快速、自動優化慢SQL

[4]、為直觀展示慢SQL結果等

二、SQLAdvisor

2.1、SQLAdvisor簡介

在資料庫運維過程中，優化SQL是業務團隊與DBA團隊的日常任務。例行SQL優化，不僅可以提升程式效能，還能夠降低線上故障的概率。

目前常用的SQL優化方式包括但不限於：業務層優化、SQL邏輯優化、索引優化等。其中索引優化通常通過調整索引或新增索引從而達到SQL優化的目的。索引優化往往可以在短時間內產生非常巨大的效果。如果能夠將索引優化轉化成工具化、標準化的流程，減少人工介入的工作量，無疑會大大提高DBA的工作效率

SQLAdvisor是由美團點評公司DBA團隊(北京)開發維護的SQL優化工具：輸入SQL，輸出索引優化建議。它基於MySQL原生詞法解析，再結合SQL中的where條件以及欄位選擇度、聚合條件、多表Join關係等最終輸出最優的索引優化建議。目前SQLAdvisor在公司內部大量使用，較為成熟、穩定。

美團點評致力於將SQLAdvisor打造成一款高智慧化SQL優化工具，選擇將已經在公司內部使用較為成熟的、穩定的SQLAdvisor專案開源，github地址。希望與業內有類似需求的團隊，一起打造一款優秀的SQL優化產品。

2.2、SQLAdvisor結構流程圖

2.3、SQLAdvisor支援SQL

insert、update、delete、select、insert select 、select join、update t1 t2 等常見SQL有支援

2.4、SQLAdvisor注意

SQL中的子查詢、or條件、使用函式的條件會忽略不處理

命令列傳入sql引數時，注意sql中的雙引號、反引號都需要用\轉義。建議使用配置檔案形式呼叫

2.5、SQLAdvisor安裝

2.5.1、下載包

SQLAdvisor-master.zip

Percona-Server-shared-56-5.6.35-rel81.0.el6.x86_64.rpm

2.5.2、安裝

安裝依賴包：

yum install -y cmake libaio-devel libffi-devel glib2 glib2-devel

yum -y install Percona-Server-shared-56-5.6.35-rel81.0.el6.x86_64.rpm

注：

cd /usr/lib64

ln -s libperconaserverclient_r.so.18 libperconaserverclient_r.so

安裝SQLAdvisor

解壓：unzip SQLAdvisor-master.zip

cmake -DBUILD_CONFIG=mysql_release -DCMAKE_BUILD_TYPE=debug -DCMAKE_INSTALL_PREFIX=/usr/local/sqlparser ./

make && make install

cd SQLAdvisor/sqladvisor

cmake -DCMAKE_BUILD_TYPE=debug ./

make

出現sqladvisor 命令檔案且無報錯為安裝正確

cp sqladvisor /usr/bin/sqladvisor

2.5.3、SQLAdvisor使用

sqladvisor --help

Usage:
SQL Advisor Summary
Help Options:

-?, --help Show help options
Application Options:
-f, --defaults-file sqls file
-u, --username username
-p, --password password
-P, --port port
-h, --host host
-d, --dbname database name
-q, --sqls sqls
-v, --verbose 1:output logs 0:output nothing

2.5.4、測試使用

[root@dbmon SQLAdvisor]# sqladvisor -u *** -p *** -h *** -P 6001 -d my -q "select * from my_db where port=6001" -v 1

2017-05-04 11:42:03 27943 [Note] 第1步: 對SQL解析優化之後得到的SQL:select `*` AS `*` from `my`.`my_db` where (`port` = 6001)

2017-05-04 11:42:03 27943 [Note] 第2步：開始解析where中的條件:(`port` = 6001)

2017-05-04 11:42:03 27943 [Note] show index from my_db

2017-05-04 11:42:03 27943 [Note] show table status like 'my_db'

2017-05-04 11:42:03 27943 [Note] select count(*) from ( select `port` from `my_db` FORCE INDEX( ux_hostportservicename ) order by host DESC,port DESC limit 353) `my_db` where (`port` = 6001)

2017-05-04 11:42:03 27943 [Note] 第3步：表my_db的行數:707,limit行數:353,得到where條件中(`port` = 6001)的選擇度:353

2017-05-04 11:42:03 27943 [Note] 第4步：開始驗證欄位port是不是主鍵。表名:my_db

2017-05-04 11:42:03 27943 [Note] show index from my_db where Key_name = 'PRIMARY' and Column_name ='port' and Seq_in_index = 1

2017-05-04 11:42:03 27943 [Note] 第5步：欄位port不是主鍵。表名:my_db

2017-05-04 11:42:03 27943 [Note] 第6步：開始驗證欄位port是不是主鍵。表名:my_db

2017-05-04 11:42:03 27943 [Note] show index from my_db where Key_name = 'PRIMARY' and Column_name ='port' and Seq_in_index = 1

2017-05-04 11:42:03 27943 [Note] 第7步：欄位port不是主鍵。表名:my_db

2017-05-04 11:42:03 27943 [Note] 第8步：開始驗證表中是否已存在相關索引。表名:my_db, 欄位名:port, 在索引中的位置:1

2017-05-04 11:42:03 27943 [Note] show index from my_db where Column_name ='port' and Seq_in_index =1

2017-05-04 11:42:03 27943 [Note] 第9步：開始輸出表my_db索引優化建議:

2017-05-04 11:42:03 27943 [Note] Create_Index_SQL：alter table my_db add index idx_port(port)

2017-05-04 11:42:03 27943 [Note] 第10步: SQLAdvisor結束!

三、SQLAdvisor自動化、平臺化

3.1、架構

注：

利用django， bootstrap，html 來平臺化

利用python指令碼等來實現自動化

3.2、慢日誌線上展示分析平臺具體實現

3.2.1、平臺介面

3.2.2、具體實現

根據輸入庫名，獲取例項資訊

選取例項

選擇時間，預設為當前時間前5小時至當前時間

選擇資料條數預設50條

利用pt-query-digest 分析慢日誌入庫表：my_query_review_once，my_query_review_history_once

SQLAdvisor分析結果，給出建議，入庫表：my_auto_tuning_once

3.2.3、使用

輸入庫名，選擇提交按鈕

選擇例項

選擇排序方式

選擇時間（預設5小時前至當前時間）

條數（預設50）

提交，結果如下：

3.3、慢日誌統計分析平臺具體實現

3.3.1、介面

3.3.2、具體實現

利用pt-query-digest 分析慢日誌入庫，利用SQLAdvisor分析，
將結果儲存在表：my_auto_tuning，my_auto_tuning_history

利用django 前端展示結果

3.3.3、使用

登入django測試平臺

點選平臺 --> [6]自動優化分析結果

3.4、線上SQL優化分析平臺具體實現

3.4.1、介面

3.4.2、具體實現

輸入庫名，根據庫名獲取例項資訊

根據輸入SQL，與叢集號

利用SQLAdvisor分析，結果入庫

利用django前端展示結果

3.4.3、使用

輸入庫名，檢查庫名

輸入SQL，叢集號

點選提交

輸出結果如下：

四、優點
<1>不影響當前架構，快速部署上線

<2>方便DBA優化，直觀輸出

<3>自動分析，報表展示，郵件發給開發

<4>平臺操作，方便快捷

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/28823725/viewspace-2140970/，如需轉載，請註明出處，否則將追究法律責任。

SQLAdvisor調研與應用