SQL优化工具SQLAdvisor使用

忘是亡心i 2024-02-18 19:52 10阅读 0赞

#### 一、简介 ####

在数据库运维过程中，优化SQL是业务团队与DBA团队的日常任务。例行SQL优化，不仅可以提升程序性能，还能够降低线上故障的概率。

目前常用的SQL优化方式包括但不限于：业务层优化、SQL逻辑优化、索引优化等。其中索引优化通常通过调整索引或新增索引从而达到SQL优化的目的。索引优化往往可以在短时间内产生非常巨大的效果。如果能够将索引优化转化成工具化、标准化的流程，减少人工介入的工作量，无疑会大大提高DBA的工作效率

SQLAdvisor是由美团点评公司DBA团队(北京)开发维护的SQL优化工具：**输入SQL，输出索引优化建议**。 它基于MySQL原生词法解析，再结合SQL中的where条件以及字段选择度、聚合条件、多表Join关系等最终输出最优的索引优化建议。目前SQLAdvisor在公司内部大量使用，较为成熟、稳定。

美团点评致力于将SQLAdvisor打造成一款高智能化SQL优化工具，选择将已经在公司内部使用较为成熟的、稳定的SQLAdvisor项目开源，github地址。希望与业内有类似需求的团队，一起打造一款优秀的SQL优化产品。

**目前SQLAdvisor在美团点评内部广泛应用，公司内部对SQLAdvisor的开发全面转到github上，开源和内部使用保持一致**。

**主要功能：输出SQL索引优化建议**

GitHup地址：[https://github.com/Meituan-Dianping/SQLAdvisor][https_github.com_Meituan-Dianping_SQLAdvisor]

#### 二、SQLAdvisor安装 ####

**2.1 拉取最新代码**

<table style="margin-left:-20px;"> 
 <tbody> 
  <tr> 
   <td style="vertical-align:top;"> 
    <div style="margin-left:0px;"> 
     <div style="margin-left:0px;">
       1 
     </div> 
    </div> </td> 
   <td style="vertical-align:top;"> 
    <div style="margin-left:0px;"> 
     <div style="margin-left:0px;"> 
      <span style="color:#c9d2d1;">$</span> 
      <span style="color:#c29734;">git </span> 
      <span style="color:#92afc1;">clone</span> https://github 
      <span style="color:#c29734;">.com</span>/Meituan-Dianping/SQLAdvisor 
      <span style="color:#c29734;">.git</span> 
     </div> 
    </div> </td> 
  </tr> 
 </tbody> 
</table>

**2.2 安装依赖项**

<table style="margin-left:-20px;"> 
 <tbody> 
  <tr> 
   <td style="vertical-align:top;"> 
    <div style="margin-left:0px;"> 
     <div style="margin-left:0px;">
       1 
     </div> 
    </div> </td> 
   <td style="vertical-align:top;"> 
    <div style="margin-left:0px;"> 
     <div style="margin-left:0px;"> 
      <span style="color:#c9d2d1;">$</span> 
      <span style="color:#c29734;">yum </span> 
      <span style="color:#c29734;">install </span> 
      <span style="color:#c29734;">cmake </span>libaio- 
      <span style="color:#c29734;">devel </span>libffi- 
      <span style="color:#c29734;">devel </span> 
      <span style="color:#c29734;">glib2 </span>glib2-devel 
     </div> 
    </div> </td> 
  </tr> 
 </tbody> 
</table>

跟据glib安装的路径，修改SQLAdvisor/sqladvisor/CMakeLists.txt中的两处include\_directories针对glib设置的path。glib yum安装默认不需要修改路径。

另外，编译sqladvisor时依赖perconaserverclient\_r, 因此需要安装Percona-Server-shared-56。

如果yum安装不行，可以采用rpm包手动安装。参考：https://github.com/Meituan-Dianping/SQLAdvisor/issues/12

\#\#\#\#\#\#\#\#\#\#\#采用rpm包手动安装\#\#\#\#\#\#\#\#\#\#

下载 tar 包  
wget [https://www.percona.com/downloads/Percona-Server-5.6/Percona-Server-5.6.25-73.1/binary/redhat/6/x86\_64/Percona-Server-5.6.25-73.1-r07b797f-el6-x86\_64-bundle.tar][https_www.percona.com_downloads_Percona-Server-5.6_Percona-Server-5.6.25-73.1_binary_redhat_6_x86_64_Percona-Server-5.6.25-73.1-r07b797f-el6-x86_64-bundle.tar]

或者

[https://www.percona.com/downloads/Percona-Server-5.6/Percona-Server-5.6.48-88.0/binary/redhat/7/x86\_64/Percona-Server-5.6.48-88.0-r66735bc-el7-x86\_64-bundle.tar][https_www.percona.com_downloads_Percona-Server-5.6_Percona-Server-5.6.48-88.0_binary_redhat_7_x86_64_Percona-Server-5.6.48-88.0-r66735bc-el7-x86_64-bundle.tar]

解压  
tar -zxvf Percona-Server-5.6.25-73.1-r07b797f-el6-x86\_64-bundle.tar  
找到对应包，安装即可。  
rpm -ivh Percona-Server-shared-56-5.6.25-rel73.1.el6.x86\_64.rpm

我使用这个方案：  
解压的时候报错了：  
tar -zxvf Percona-Server-5.6.25-73.1-r07b797f-el6-x86\_64-bundle.tar

gzip: stdin: not in gzip format  
tar: Child returned status 1  
tar: Error is not recoverable: exiting now

然后换成：tar -xvf Percona-Server-5.6.25-73.1-r07b797f-el6-x86\_64-bundle.tar（去掉z参数）

之后执行安装：  
warning: Percona-Server-shared-56-5.6.25-rel73.1.el6.x86\_64.rpm: Header V4 DSA/SHA1 Signature, key ID cd2efd2a: NOKEY

\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#

需要配置软链接：

**2.3 编译依赖项sqlparser**

**注意**

*  DCMAKE\_INSTALL\_PREFIX为sqlparser库文件和头文件的安装目录，其中lib目录包含库文件libsqlparser.so，include目录包含所需的所有头文件。
 *  DCMAKE\_INSTALL\_PREFIX值尽量不要修改，后面安装依赖这个目录。

**2.4 安装SQLAdvisor源码**

安装完成后，在本路径下生成一个sqladvisor可执行文件，这即是我们想要的。

可以把可执行文件sqladvisor复制到PATH路径中。

#### 三、SQLAdvisor使用 ####

**2.1 帮助输出**

<table style="margin-left:-28px;"> 
 <tbody> 
  <tr> 
   <td style="vertical-align:top;"> 
    <div style="margin-left:0px;"> 
     <div style="margin-left:0px;">
       1 
     </div> 
     <div style="margin-left:0px;">
       2 
     </div> 
     <div style="margin-left:0px;">
       3 
     </div> 
     <div style="margin-left:0px;">
       4 
     </div> 
     <div style="margin-left:0px;">
       5 
     </div> 
     <div style="margin-left:0px;">
       6 
     </div> 
     <div style="margin-left:0px;">
       7 
     </div> 
     <div style="margin-left:0px;">
       8 
     </div> 
     <div style="margin-left:0px;">
       9 
     </div> 
     <div style="margin-left:0px;">
       10 
     </div> 
     <div style="margin-left:0px;">
       11 
     </div> 
     <div style="margin-left:0px;">
       12 
     </div> 
     <div style="margin-left:0px;">
       13 
     </div> 
     <div style="margin-left:0px;">
       14 
     </div> 
     <div style="margin-left:0px;">
       15 
     </div> 
     <div style="margin-left:0px;">
       16 
     </div> 
     <div style="margin-left:0px;">
       17 
     </div> 
     <div style="margin-left:0px;">
       18 
     </div> 
    </div> </td> 
   <td style="vertical-align:top;"> 
    <div style="margin-left:0px;"> 
     <div style="margin-left:0px;"> 
      <span style="color:#c9d2d1;">$</span> sqladvisor -- 
      <span style="color:#c29734;">help</span> 
     </div> 
     <div style="margin-left:0px;">
       Usage: 
     </div> 
     <div style="margin-left:0px;">
       &nbsp;&nbsp; 
      <span style="color:#c7c7c7;">sqladvisor</span> 
      <span style="color:#c9d2d1;">[</span>OPTION 
      <span style="color:#c9d2d1;">.</span> 
      <span style="color:#c9d2d1;">.</span> 
      <span style="color:#c9d2d1;">.</span> 
      <span style="color:#c9d2d1;">]</span> 
      <span style="color:#c29734;">sqladvisor</span> 
     </div> 
     <div style="margin-left:0px;">
       &nbsp; 
     </div> 
     <div style="margin-left:0px;"> 
      <span style="color:#c29734;">SQL </span> 
      <span style="color:#c29734;">Advisor </span> 
      <span style="color:#c29734;">Summary</span> 
     </div> 
     <div style="margin-left:0px;">
       &nbsp; 
     </div> 
     <div style="margin-left:0px;"> 
      <span style="color:#c29734;">Help </span>Options: 
     </div> 
     <div style="margin-left:0px;">
       &nbsp;&nbsp;- 
      <span style="color:#c9d2d1;">?</span> 
      <span style="color:#c9d2d1;">,</span> -- 
      <span style="color:#c29734;">help&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</span> 
      <span style="color:#c29734;">Show </span> 
      <span style="color:#c29734;">help </span> 
      <span style="color:#c29734;">options</span> 
     </div> 
     <div style="margin-left:0px;">
       &nbsp; 
     </div> 
     <div style="margin-left:0px;"> 
      <span style="color:#c29734;">Application </span>Options: 
     </div> 
     <div style="margin-left:0px;">
       &nbsp;&nbsp;-f 
      <span style="color:#c9d2d1;">,</span> --defaults- 
      <span style="color:#92afc1;">file</span>&nbsp;&nbsp;&nbsp;&nbsp; 
      <span style="color:#c29734;">sqls </span> 
      <span style="color:#92afc1;">file</span> 
     </div> 
     <div style="margin-left:0px;">
       &nbsp;&nbsp;-u 
      <span style="color:#c9d2d1;">,</span> -- 
      <span style="color:#c29734;">username&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</span>username 
     </div> 
     <div style="margin-left:0px;">
       &nbsp;&nbsp;-p 
      <span style="color:#c9d2d1;">,</span> -- 
      <span style="color:#c29734;">password&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</span>password 
     </div> 
     <div style="margin-left:0px;">
       &nbsp;&nbsp;-P 
      <span style="color:#c9d2d1;">,</span> -- 
      <span style="color:#c29734;">port&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</span>port 
     </div> 
     <div style="margin-left:0px;">
       &nbsp;&nbsp;-h 
      <span style="color:#c9d2d1;">,</span> -- 
      <span style="color:#c29734;">host&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</span>host 
     </div> 
     <div style="margin-left:0px;">
       &nbsp;&nbsp;-d 
      <span style="color:#c9d2d1;">,</span> -- 
      <span style="color:#c29734;">dbname&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</span> 
      <span style="color:#c29734;">database </span>name 
     </div> 
     <div style="margin-left:0px;">
       &nbsp;&nbsp;-q 
      <span style="color:#c9d2d1;">,</span> -- 
      <span style="color:#c29734;">sqls&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</span>sqls 
     </div> 
     <div style="margin-left:0px;">
       &nbsp;&nbsp;-v 
      <span style="color:#c9d2d1;">,</span> -- 
      <span style="color:#c7c7c7;">verbose</span>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1: 
      <span style="color:#c29734;">output </span> 
      <span style="color:#c7c7c7;">logs</span> 0: 
      <span style="color:#c29734;">output </span>nothing 
     </div> 
    </div> </td> 
  </tr> 
 </tbody> 
</table>

**2.2 命令行传参调用**

<table style="margin-left:-20px;"> 
 <tbody> 
  <tr> 
   <td style="vertical-align:top;"> 
    <div style="margin-left:0px;"> 
     <div style="margin-left:0px;">
       1 
     </div> 
    </div> </td> 
   <td style="vertical-align:top;"> 
    <div style="margin-left:0px;"> 
     <div style="margin-left:0px;"> 
      <span style="color:#c9d2d1;">$</span> sqladvisor - 
      <span style="color:#c7c7c7;">h</span> xx&nbsp;&nbsp;- 
      <span style="color:#c7c7c7;">P</span> xx&nbsp;&nbsp;- 
      <span style="color:#c7c7c7;">u</span> xx - 
      <span style="color:#c7c7c7;">p</span> 
      <span style="color:#b2af75;">'xx'</span> - 
      <span style="color:#c7c7c7;">d</span> xx - 
      <span style="color:#c7c7c7;">q</span> 
      <span style="color:#b2af75;">"sql"</span> - 
      <span style="color:#c7c7c7;">v</span> 1 
     </div> 
    </div> </td> 
  </tr> 
 </tbody> 
</table>

2.3 配置文件传参调用

**2.4 测试使用**

<table style="margin-left:-20px;"> 
 <tbody> 
  <tr> 
   <td style="vertical-align:top;"> 
    <div style="margin-left:0px;"> 
     <div style="margin-left:0px;">
       1 
     </div> 
     <div style="margin-left:0px;">
       2 
     </div> 
     <div style="margin-left:0px;">
       3 
     </div> 
     <div style="margin-left:0px;">
       4 
     </div> 
     <div style="margin-left:0px;">
       5 
     </div> 
     <div style="margin-left:0px;">
       6 
     </div> 
     <div style="margin-left:0px;">
       7 
     </div> 
     <div style="margin-left:0px;">
       8 
     </div> 
     <div style="margin-left:0px;">
       9 
     </div> 
    </div> </td> 
   <td style="vertical-align:top;"> 
    <div style="margin-left:0px;"> 
     <div style="margin-left:0px;"> 
      <span style="color:#c9d2d1;">$</span> sqladvisor - 
      <span style="color:#c7c7c7;">f</span> sqladvisor 
      <span style="color:#c29734;">.cnf</span> - 
      <span style="color:#c7c7c7;">v</span> 1 
     </div> 
     <div style="margin-left:0px;">
       2017-04-13 17:39:59 23848 
      <span style="color:#c9d2d1;">[</span>Note 
      <span style="color:#c9d2d1;">]</span> 第1步: 对 
      <span style="color:#c7c7c7;">SQL</span>解析优化之后得到的SQL: 
      <span style="color:#c7c7c7;">select</span> 
      <span style="color:#c9d2d1;">`</span>* 
      <span style="color:#c9d2d1;">`</span> AS 
      <span style="color:#c9d2d1;">`</span>* 
      <span style="color:#c9d2d1;">`</span> 
      <span style="color:#c7c7c7;">from</span> 
      <span style="color:#c9d2d1;">`</span>blog 
      <span style="color:#c9d2d1;">`</span> 
      <span style="color:#c9d2d1;">.</span> 
      <span style="color:#c9d2d1;">`</span>wp_posts 
      <span style="color:#c9d2d1;">`</span> 
      <span style="color:#c29734;">where</span> 
      <span style="color:#c9d2d1;">(</span> 
      <span style="color:#c9d2d1;">`</span>post_date 
      <span style="color:#c9d2d1;">`</span> &gt; 
      <span style="color:#b2af75;">'2015-07-16 18:44:27'</span> 
      <span style="color:#c9d2d1;">)</span> 
     </div> 
     <div style="margin-left:0px;">
       2017-04-13 17:39:59 23848 
      <span style="color:#c9d2d1;">[</span>Note 
      <span style="color:#c9d2d1;">]</span> 第2步：开始解析 
      <span style="color:#c7c7c7;">where</span>中的条件: 
      <span style="color:#c9d2d1;">(</span> 
      <span style="color:#c9d2d1;">`</span>post_date 
      <span style="color:#c9d2d1;">`</span> &gt; 
      <span style="color:#b2af75;">'2015-07-16 18:44:27'</span> 
      <span style="color:#c9d2d1;">)</span> 
     </div> 
     <div style="margin-left:0px;">
       2017-04-13 17:39:59 23848 
      <span style="color:#c9d2d1;">[</span>Note 
      <span style="color:#c9d2d1;">]</span> 
      <span style="color:#c29734;">show </span> 
      <span style="color:#c29734;">index </span> 
      <span style="color:#c29734;">from </span>wp 
      <span style="color:#c9d2d1;">_</span>posts 
     </div> 
     <div style="margin-left:0px;">
       2017-04-13 17:39:59 23848 
      <span style="color:#c9d2d1;">[</span>Note 
      <span style="color:#c9d2d1;">]</span> 
      <span style="color:#c29734;">show </span> 
      <span style="color:#c29734;">table </span> 
      <span style="color:#c29734;">status </span> 
      <span style="color:#c7c7c7;">like</span> 
      <span style="color:#b2af75;">'wp_posts'</span> 
     </div> 
     <div style="margin-left:0px;">
       2017-04-13 17:39:59 23848 
      <span style="color:#c9d2d1;">[</span>Note 
      <span style="color:#c9d2d1;">]</span> 
      <span style="color:#c29734;">select </span> 
      <span style="color:#c29734;">count</span> 
      <span style="color:#c9d2d1;">(</span>* 
      <span style="color:#c9d2d1;">)</span> 
      <span style="color:#c29734;">from</span> 
      <span style="color:#c9d2d1;">(</span> 
      <span style="color:#c7c7c7;">select</span> 
      <span style="color:#c9d2d1;">`</span>post_date 
      <span style="color:#c9d2d1;">`</span> 
      <span style="color:#c7c7c7;">from</span> 
      <span style="color:#c9d2d1;">`</span>wp_posts 
      <span style="color:#c9d2d1;">`</span> 
      <span style="color:#c29734;">FORCE </span> 
      <span style="color:#c29734;">INDEX</span> 
      <span style="color:#c9d2d1;">(</span> 
      <span style="color:#c7c7c7;">PRIMARY</span> 
      <span style="color:#c9d2d1;">)</span> 
      <span style="color:#c29734;">order </span> 
      <span style="color:#c29734;">by </span> 
      <span style="color:#92afc1;">ID</span> 
      <span style="color:#c29734;">DESC </span> 
      <span style="color:#c7c7c7;">limit</span> 3763 
      <span style="color:#c9d2d1;">)</span> 
      <span style="color:#c9d2d1;">`</span>wp_posts 
      <span style="color:#c9d2d1;">`</span> 
      <span style="color:#c29734;">where</span> 
      <span style="color:#c9d2d1;">(</span> 
      <span style="color:#c9d2d1;">`</span>post_date 
      <span style="color:#c9d2d1;">`</span> &gt; 
      <span style="color:#b2af75;">'2015-07-16 18:44:27'</span> 
      <span style="color:#c9d2d1;">)</span> 
     </div> 
     <div style="margin-left:0px;">
       2017-04-13 17:39:59 23848 
      <span style="color:#c9d2d1;">[</span>Note 
      <span style="color:#c9d2d1;">]</span> 第3步：表wp 
      <span style="color:#c9d2d1;">_</span>posts的行数:7527 
      <span style="color:#c9d2d1;">,</span> 
      <span style="color:#c7c7c7;">limit</span>行数:3763 
      <span style="color:#c9d2d1;">,</span>得到 
      <span style="color:#c7c7c7;">where</span>条件中 
      <span style="color:#c9d2d1;">(</span> 
      <span style="color:#c9d2d1;">`</span>post_date 
      <span style="color:#c9d2d1;">`</span> &gt; 
      <span style="color:#b2af75;">'2015-07-16 18:44:27'</span> 
      <span style="color:#c9d2d1;">)</span>的选择度:1 
     </div> 
     <div style="margin-left:0px;">
       2017-04-13 17:39:59 23848 
      <span style="color:#c9d2d1;">[</span>Note 
      <span style="color:#c9d2d1;">]</span> 第4步：表wp 
      <span style="color:#c9d2d1;">_</span>posts 的 
      <span style="color:#c7c7c7;">SQL</span>太逆天 
      <span style="color:#c9d2d1;">,</span>没有优化建议 
     </div> 
     <div style="margin-left:0px;">
       2017-04-13 17:39:59 23848 
      <span style="color:#c9d2d1;">[</span>Note 
      <span style="color:#c9d2d1;">]</span> 第5步: 
      <span style="color:#c7c7c7;">SQLAdvisor</span>结束! 
     </div> 
    </div> </td> 
  </tr> 
 </tbody> 
</table>

#### 四、SQLAdvisor工作原理 ####

详情看：[SQLAdvisor架构和实践][SQLAdvisor]

#### 五、SQLAdvisor Web ####

美团开源出来的SQLAdvisor SQL优化建议工具只有命令行，所以有网友就针对这个开发出了Web版本，告别命令行。

GitHub地址：[https://github.com/zyw/sqladvisor-web][https_github.com_zyw_sqladvisor-web]

*  项目中使用的美团SQL分析工具是在CentOS上编译的，所以建议部署到CentOS上。
 *  该项目是使用Python的Flask框架开发的。
 *  使用CentOS自带的Python版本,版本号是2.7.5。

### 安装sqladvisor-web ###

下载依赖：

yum install python-devel mysql-community-devel -y

*  1

下载源码：

git clone https://github.com/zyw/sqladvisor-web.git

*  1

安装依赖：

cd sqladvisor-web

pip3 install django_admin_bootstrapped

pip install -r requirements.txt

修改数据库配置

vim config.py
    SQLALCHEMY_DATABASE_URI = os.environ.get('DEV_DATABASE_URL') or 'mysql://root:root@localhost/sqladvisor'

其中把第二个root修改成你安装Mysql时设置的密码  
初始化数据库：

python manage.py db init
    python manage.py db migrate
    python manage.py db upgrade

启动系统：  
解压sqlparser.tar.gz到/usr/local

python manage.py runserver --host 0.0.0.0 &

默认监听5000端口，此时安装完成

[https_github.com_Meituan-Dianping_SQLAdvisor]: https://github.com/Meituan-Dianping/SQLAdvisor
[https_www.percona.com_downloads_Percona-Server-5.6_Percona-Server-5.6.25-73.1_binary_redhat_6_x86_64_Percona-Server-5.6.25-73.1-r07b797f-el6-x86_64-bundle.tar]: https://www.percona.com/downloads/Percona-Server-5.6/Percona-Server-5.6.25-73.1/binary/redhat/6/x86_64/Percona-Server-5.6.25-73.1-r07b797f-el6-x86_64-bundle.tar
[https_www.percona.com_downloads_Percona-Server-5.6_Percona-Server-5.6.48-88.0_binary_redhat_7_x86_64_Percona-Server-5.6.48-88.0-r66735bc-el7-x86_64-bundle.tar]: https://www.percona.com/downloads/Percona-Server-5.6/Percona-Server-5.6.48-88.0/binary/redhat/7/x86_64/Percona-Server-5.6.48-88.0-r66735bc-el7-x86_64-bundle.tar
[SQLAdvisor]: https://github.com/Meituan-Dianping/SQLAdvisor/blob/master/doc/THEORY_PRACTICES.md
[https_github.com_zyw_sqladvisor-web]: https://github.com/zyw/sqladvisor-web