溫馨提示×

溫馨提示×

您好，登錄后才能下訂單哦！

密碼登錄×
登錄注冊×
其他方式登錄
點擊 登錄注冊 即表示同意《億速云用戶服務(wù)條款》

SQLAdvisor調(diào)研與應(yīng)用

發(fā)布時間：2020-08-05 00:04:10 來源：ITPUB博客 閱讀：151 作者：o煙雨瀟瀟o 欄目：MySQL數(shù)據(jù)庫

歡迎轉(zhuǎn)載，請注明作者、出處。

作者：劉春雷
blog：http://blog.itpub.net/28823725/
如有疑問，歡迎留言~
-------------------------------------------------------------------------------
                                              SQLAdvisor調(diào)研與應(yīng)用
                                                                 --2017-05-23 劉春雷

一、需求

1.1、降低慢查詢量與影響

[1]、為降低線上慢查詢的數(shù)量與影響
[2]、為減少DBA日常優(yōu)化的繁雜工作
[3]、為方便、快速、自動優(yōu)化慢SQL
[4]、為直觀展示慢SQL結(jié)果等

二、SQLAdvisor

2.1、SQLAdvisor簡介

在數(shù)據(jù)庫運維過程中，優(yōu)化SQL是業(yè)務(wù)團隊與DBA團隊的日常任務(wù)。例行SQL優(yōu)化，不僅可以提升程序性能，還能夠降低線上故障的概率。

目前常用的SQL優(yōu)化方式包括但不限于：業(yè)務(wù)層優(yōu)化、SQL邏輯優(yōu)化、索引優(yōu)化等。其中索引優(yōu)化通常通過調(diào)整索引或新增索引從而達到SQL優(yōu)化的目的。索引優(yōu)化往往可以在短時間內(nèi)產(chǎn)生非常巨大的效果。如果能夠?qū)⑺饕齼?yōu)化轉(zhuǎn)化成工具化、標準化的流程，減少人工介入的工作量，無疑會大大提高DBA的工作效率

SQLAdvisor是由美團點評公司DBA團隊(北京)開發(fā)維護的SQL優(yōu)化工具：輸入SQL，輸出索引優(yōu)化建議。 它基于MySQL原生詞法解析，再結(jié)合SQL中的where條件以及字段選擇度、聚合條件、多表Join關(guān)系等最終輸出最優(yōu)的索引優(yōu)化建議。目前SQLAdvisor在公司內(nèi)部大量使用，較為成熟、穩(wěn)定。

美團點評致力于將SQLAdvisor打造成一款高智能化SQL優(yōu)化工具，選擇將已經(jīng)在公司內(nèi)部使用較為成熟的、穩(wěn)定的SQLAdvisor項目開源，github地址。希望與業(yè)內(nèi)有類似需求的團隊，一起打造一款優(yōu)秀的SQL優(yōu)化產(chǎn)品。


2.2、SQLAdvisor結(jié)構(gòu)流程圖

SQLAdvisor調(diào)研與應(yīng)用

2.3、SQLAdvisor支持SQL

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

2.4、SQLAdvisor注意

SQL中的子查詢、or條件、使用函數(shù)的條件 會忽略不處理
命令行傳入sql參數(shù)時，注意sql中的雙引號、反引號 都需要用\轉(zhuǎn)義。建議使用配置文件形式調(diào)用


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
出現(xiàn)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解析優(yōu)化之后得到的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的行數(shù):707,limit行數(shù):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步：開始驗證表中是否已存在相關(guān)索引。表名: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索引優(yōu)化建議: 


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結(jié)束! 


三、SQLAdvisor自動化、平臺化

3.1、架構(gòu)

SQLAdvisor調(diào)研與應(yīng)用

注：

利用django， bootstrap，html 來平臺化

利用python腳本等來實現(xiàn)自動化



3.2、慢日志在線展示分析平臺具體實現(xiàn)

3.2.1、平臺界面

SQLAdvisor調(diào)研與應(yīng)用

 3.2.2、具體實現(xiàn)

根據(jù)輸入庫名，獲取實例信息
選取實例
選擇時間，默認為當前時間前5小時  至  當前時間
選擇數(shù)據(jù)條數(shù) 默認50條
利用pt-query-digest 分析慢日志入庫表：my_query_review_once，my_query_review_history_once
SQLAdvisor分析結(jié)果，給出建議，入庫表：my_auto_tuning_once

3.2.3、使用

輸入庫名，選擇提交按鈕
選擇實例
選擇排序方式
選擇時間（默認5小時前至當前時間）
條數(shù)（默認50）
提交，結(jié)果如下：
SQLAdvisor調(diào)研與應(yīng)用

3.3、慢日志統(tǒng)計分析平臺具體實現(xiàn)

3.3.1、界面

SQLAdvisor調(diào)研與應(yīng)用

3.3.2、具體實現(xiàn)

利用pt-query-digest 分析慢日志入庫，利用SQLAdvisor分析，
將結(jié)果存儲在表：my_auto_tuning，my_auto_tuning_history
利用django 前端展示結(jié)果

3.3.3、使用

登錄django測試平臺
點擊平臺 --> [6]自動優(yōu)化分析結(jié)果

3.4、在線SQL優(yōu)化分析平臺具體實現(xiàn)

3.4.1、界面

SQLAdvisor調(diào)研與應(yīng)用

3.4.2、具體實現(xiàn)

輸入庫名，根據(jù)庫名獲取實例信息
根據(jù)輸入SQL，與集群號
利用SQLAdvisor分析，結(jié)果入庫
利用django前端展示結(jié)果

3.4.3、使用

輸入庫名，檢查庫名
輸入SQL，集群號
點擊提交
SQLAdvisor調(diào)研與應(yīng)用
輸出結(jié)果如下：
SQLAdvisor調(diào)研與應(yīng)用

 四、優(yōu)點

<1>不影響當前架構(gòu)，快速部署上線
<2>方便DBA優(yōu)化，直觀輸出
<3>自動分析，報表展示，郵件發(fā)給開發(fā)
<4>平臺操作，方便快捷

向AI問一下細節(jié)

免責聲明：本站發(fā)布的內(nèi)容（圖片、視頻和文字）以原創(chuàng)、轉(zhuǎn)載和分享為主，文章觀點不代表本網(wǎng)站立場，如果涉及侵權(quán)請聯(lián)系站長郵箱：is@yisu.com進行舉報，并提供相關(guān)證據(jù)，一經(jīng)查實，將立刻刪除涉嫌侵權(quán)內(nèi)容。

AI
助
手