SQLAdvisor調研與應用

o煙雨瀟瀟o發表於2017-06-19
SQL

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

作者：劉春雷
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/，如需轉載，請註明出處，否則將追究法律責任。

相關文章