SQLAdvisor调研与应用

发布时间：2020-08-05 00:04:10 来源：ITPUB博客阅读：158 作者：o烟雨潇潇o 栏目：MySQL数据库

欢迎转载，请注明作者、出处。

作者：刘春雷

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，集群号

点击提交

输出结果如下：
SQLAdvisor调研与应用

四、优点
<1>不影响当前架构，快速部署上线

<2>方便DBA优化，直观输出

<3>自动分析，报表展示，邮件发给开发

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

向AI问一下细节