SQLAdvisor美团SQL索引优化建议工具-低调大师

SQLAdvisor美团SQL索引优化建议工具

2017-03-22 638

SQLAdvisor美团SQL索引优化建议工具

前言

Part1:写在最前

SQLAdvisor是美团开源的一款SQL索引优化建议工具，是由美团点评公司技术工程部DBA团队（北京）开发维护的一个分析SQL给出索引优化建议的工具。它基于MySQL原生态词法解析，结合分析SQL中的where条件、聚合条件、多表Join关系给出索引优化建议。目前SQLAdvisor在美团点评内部广泛应用，公司内部对SQLAdvisor的开发全面转到github上，开源和内部使用保持一致。本文记录对该工具的初步安装和基本使用。

安装

Part1:构建安装环境

[root@HE3 ~]# yum install git

[root@HE3 ~]# git clone https://github.com/Meituan-Dianping/SQLAdvisor.git

[root@HE3 ~]# yum install cmake libaio-devel libffi-devel glib2 glib2-devel

[root@HE3 ~]# yum install http://www.percona.com/downloads/percona-release/redhat/0.1-3/percona-release-0.1-3.noarch.rpm

[root@HE3 ~]# yum install Percona-Server-shared-56

[root@HE3 lib64]# cd /usr/lib64/

[root@HE3 ~]# ln -s libperconaserverclient_r.so.18 libperconaserverclient_r.so

Warning:警告1

这里该命令一直过不去yum install --enablerepo=Percona56 Percona-Server-shared-56，后直接安装的Percona-Server-shared-56通过

Warning:警告2

跟据glib安装的路径，修改SQLAdvisor/sqladvisor/CMakeLists.txt中的两处include_directories针对glib设置的path。本文采用yum安装的git，故glib yum 安装默认不需要修改路径

Warning:警告3

编译sqladvisor时依赖perconaserverclient_r, 因此需要安装Percona-Server-shared-56。有可能需要配置软链接例如:1. cd /usr/lib64/ 2. ln -s libperconaserverclient_r.so.18 libperconaserverclient_r.so

Warning:警告4

有可能需要配置percona56 yum源: yum install http://www.percona.com/downloads/percona-release/redhat/0.1-3/percona-release-0.1-3.noarch.rpm

[root@HE3 ~]#cmake -DBUILD_CONFIG=mysql_release -DCMAKE_BUILD_TYPE=debug -DCMAKE_INSTALL_PREFIX=/usr/local/sqlparser ./

[root@HE3 ~]# make && make install

[root@HE3 ~]# cd SQLAdvisor/sqladvisor

[root@HE3 sqladvisor]# cmake -DCMAKE_BUILD_TYPE=debug ./

[root@HE3 sqladvisor]# make

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

使用

Part1:对小表进行测试

[root@HE3 sqladvisor]# ./sqladvisor -h 127.0.0.1 -P 3306 -u root -p "MANAGER" -d helei1 -q "select * from helei1" -v 1

2017-03-21 20:37:53 8581 [Note] 第1步: 对SQL解析优化之后得到的SQL:select `*` AS `*` from `helei1`.`helei1`

2017-03-21 20:37:53 8581 [Note] 第2步：表helei1 的SQL太逆天,没有优化建议

2017-03-21 20:37:53 8581 [Note] 第3步: SQLAdvisor结束!

Part2:对大表进行测试（有索引）

这里我们对表helei进行分析，改表在c1列上存在索引

[root@HE3 sqladvisor]# ./sqladvisor -h 127.0.0.1 -P 3306 -u root -p "MANAGER" -d helei1 -q "explain select * from helei where c1=88501;" -v 1

2017-03-21 21:19:23 8624 [Note] 第1步: 对SQL解析优化之后得到的SQL:select `*` AS `*` from `helei1`.`helei` where (`c1` = 88501)

2017-03-21 21:19:23 8624 [Note] 第2步：开始解析where中的条件:(`c1` = 88501)

2017-03-21 21:19:23 8624 [Note] show index from helei

2017-03-21 21:19:23 8624 [Note] show table status like 'helei'

2017-03-21 21:19:23 8624 [Note] select count(*) from ( select `c1` from `helei` FORCE INDEX( PRIMARY ) order by id DESC limit 10000) `helei` where (`c1` = 88501)

2017-03-21 21:19:23 8624 [Note] 第3步：表helei的行数:200380,limit行数:10000,得到where条件中(`c1` = 88501)的选择度:10000

2017-03-21 21:19:23 8624 [Note] 第4步：开始验证字段c1是不是主键。表名:helei

2017-03-21 21:19:23 8624 [Note] show index from helei where Key_name = 'PRIMARY' and Column_name ='c1' and Seq_in_index = 1

2017-03-21 21:19:23 8624 [Note] 第5步：字段c1不是主键。表名:helei

2017-03-21 21:19:23 8624 [Note] 第6步：开始验证字段c1是不是主键。表名:helei

2017-03-21 21:19:23 8624 [Note] show index from helei where Key_name = 'PRIMARY' and Column_name ='c1' and Seq_in_index = 1

2017-03-21 21:19:23 8624 [Note] 第7步：字段c1不是主键。表名:helei

2017-03-21 21:19:23 8624 [Note] 第8步：开始验证表中是否已存在相关索引。表名:helei, 字段名:c1, 在索引中的位置:1

2017-03-21 21:19:23 8624 [Note] show index from helei where Column_name ='c1' and Seq_in_index =1

2017-03-21 21:19:23 8624 [Note] 第9步：索引(c1)已存在

2017-03-21 21:19:23 8624 [Note] 第10步: SQLAdvisor结束!

可以看到，最后给出了该条SQL已经拥有有效索引的建议

Part2:对大表进行测试（无索引）

这里我们对表helei进行分析，改表在c5列上没有索引

[root@HE3 sqladvisor]# ./sqladvisor -h 127.0.0.1 -P 3306 -u root -p "MANAGER" -d helei1 -q "explain select * from helei where c5=74685;" -v 1

2017-03-21 21:20:53 8628 [Note] 第1步: 对SQL解析优化之后得到的SQL:select `*` AS `*` from `helei1`.`helei` where (`c5` = 74685)

2017-03-21 21:20:53 8628 [Note] 第2步：开始解析where中的条件:(`c5` = 74685)

2017-03-21 21:20:53 8628 [Note] show index from helei

2017-03-21 21:20:53 8628 [Note] show table status like 'helei'

2017-03-21 21:20:53 8628 [Note] select count(*) from ( select `c5` from `helei` FORCE INDEX( PRIMARY ) order by id DESC limit 10000) `helei` where (`c5` = 74685)

2017-03-21 21:20:53 8628 [Note] 第3步：表helei的行数:201361,limit行数:10000,得到where条件中(`c5` = 74685)的选择度:10000

2017-03-21 21:20:53 8628 [Note] 第4步：开始验证字段c5是不是主键。表名:helei

2017-03-21 21:20:53 8628 [Note] show index from helei where Key_name = 'PRIMARY' and Column_name ='c5' and Seq_in_index = 1

2017-03-21 21:20:53 8628 [Note] 第5步：字段c5不是主键。表名:helei

2017-03-21 21:20:53 8628 [Note] 第6步：开始验证字段c5是不是主键。表名:helei

2017-03-21 21:20:53 8628 [Note] show index from helei where Key_name = 'PRIMARY' and Column_name ='c5' and Seq_in_index = 1

2017-03-21 21:20:53 8628 [Note] 第7步：字段c5不是主键。表名:helei

2017-03-21 21:20:53 8628 [Note] 第8步：开始验证表中是否已存在相关索引。表名:helei, 字段名:c5, 在索引中的位置:1

2017-03-21 21:20:53 8628 [Note] show index from helei where Column_name ='c5' and Seq_in_index =1

2017-03-21 21:20:53 8628 [Note] 第9步：开始输出表helei索引优化建议:

2017-03-21 21:20:53 8628 [Note] Create_Index_SQL：alter table helei add index idx_c5(c5)

2017-03-21 21:20:53 8628 [Note] 第10步: SQLAdvisor结束!

可以看到，最后给出了创建索引的建议

Part3:多条SQL同时分析

可以创建任意名的参数文件，这里叫helei.cnf，输入常规的数据库连接信息和SQL，SQL之间用分号隔开。

[root@HE3 sqladvisor]# cat helei.cnf

[sqladvisor]

username=root

password=MANAGER

host=127.0.0.1

port=3306

dbname=helei1

sqls=select * from helei where c1=88501;select * from helei where c5=74685;

这里使用-f命令来载入helei.cnf中的配置

[root@HE3 sqladvisor]# ./sqladvisor -f helei.cnf -v 1

2017-03-21 21:27:35 8640 [Note] 第1步: 对SQL解析优化之后得到的SQL:select `*` AS `*` from `helei1`.`helei` where (`c1` = 88501)

2017-03-21 21:27:35 8640 [Note] 第2步：开始解析where中的条件:(`c1` = 88501)

2017-03-21 21:27:35 8640 [Note] show index from helei

2017-03-21 21:27:35 8640 [Note] show table status like 'helei'

2017-03-21 21:27:35 8640 [Note] select count(*) from ( select `c1` from `helei` FORCE INDEX( PRIMARY ) order by id DESC limit 10000) `helei` where (`c1` = 88501)

2017-03-21 21:27:35 8640 [Note] 第3步：表helei的行数:200674,limit行数:10000,得到where条件中(`c1` = 88501)的选择度:10000

2017-03-21 21:27:35 8640 [Note] 第4步：开始验证字段c1是不是主键。表名:helei

2017-03-21 21:27:35 8640 [Note] show index from helei where Key_name = 'PRIMARY' and Column_name ='c1' and Seq_in_index = 1

2017-03-21 21:27:35 8640 [Note] 第5步：字段c1不是主键。表名:helei

2017-03-21 21:27:35 8640 [Note] 第6步：开始验证字段c1是不是主键。表名:helei

2017-03-21 21:27:35 8640 [Note] show index from helei where Key_name = 'PRIMARY' and Column_name ='c1' and Seq_in_index = 1

2017-03-21 21:27:35 8640 [Note] 第7步：字段c1不是主键。表名:helei

2017-03-21 21:27:35 8640 [Note] 第8步：开始验证表中是否已存在相关索引。表名:helei, 字段名:c1, 在索引中的位置:1

2017-03-21 21:27:35 8640 [Note] show index from helei where Column_name ='c1' and Seq_in_index =1

2017-03-21 21:27:35 8640 [Note] 第9步：索引(c1)已存在

2017-03-21 21:27:35 8640 [Note] 第10步: SQLAdvisor结束!

2017-03-21 21:27:35 8640 [Note] 第1步: 对SQL解析优化之后得到的SQL:select `*` AS `*` from `helei1`.`helei` where (`c5` = 74685)

2017-03-21 21:27:35 8640 [Note] 第2步：开始解析where中的条件:(`c5` = 74685)

2017-03-21 21:27:35 8640 [Note] show index from helei

2017-03-21 21:27:35 8640 [Note] show table status like 'helei'

2017-03-21 21:27:35 8640 [Note] select count(*) from ( select `c5` from `helei` FORCE INDEX( PRIMARY ) order by id DESC limit 10000) `helei` where (`c5` = 74685)

2017-03-21 21:27:35 8640 [Note] 第3步：表helei的行数:201067,limit行数:10000,得到where条件中(`c5` = 74685)的选择度:10000

2017-03-21 21:27:35 8640 [Note] 第4步：开始验证字段c5是不是主键。表名:helei

2017-03-21 21:27:35 8640 [Note] show index from helei where Key_name = 'PRIMARY' and Column_name ='c5' and Seq_in_index = 1

2017-03-21 21:27:35 8640 [Note] 第5步：字段c5不是主键。表名:helei

2017-03-21 21:27:35 8640 [Note] 第6步：开始验证字段c5是不是主键。表名:helei

2017-03-21 21:27:35 8640 [Note] show index from helei where Key_name = 'PRIMARY' and Column_name ='c5' and Seq_in_index = 1

2017-03-21 21:27:35 8640 [Note] 第7步：字段c5不是主键。表名:helei

2017-03-21 21:27:35 8640 [Note] 第8步：开始验证表中是否已存在相关索引。表名:helei, 字段名:c5, 在索引中的位置:1

2017-03-21 21:27:35 8640 [Note] show index from helei where Column_name ='c5' and Seq_in_index =1

2017-03-21 21:27:35 8640 [Note] 第9步：开始输出表helei索引优化建议:

2017-03-21 21:27:35 8640 [Note] Create_Index_SQL：alter table helei add index idx_c5(c5)

2017-03-21 21:27:35 8640 [Note] 第10步: SQLAdvisor结束!

可以看到这里对helei.cnf中的两个SQL进行了一次性建议输出。

Warning:警告5

SQL中的子查询、or条件、使用函数的条件会忽略不处理。
命令行传入sql参数时，注意sql中的双引号、反引号都需要用\转义。建议使用配置文件形式调用

——总结——

可以看出，SQLAdvisor对查询SQL给出了索引创建的优化和建议，还是不错的，后续会进一步进行深度测试。由于笔者的水平有限，编写时间也很仓促，文中难免会出现一些错误或者不准确的地方，不妥之处恳请读者批评指正。

参考：https://github.com/Meituan-Dianping/SQLAdvisor/blob/master/doc/QUICK_START.md

微信关注我们

原文链接：https://blog.51cto.com/suifu/1909169

转载内容版权归作者及来源网站所有！

低调大师中文资讯倾力打造互联网数据资讯、行业资源、电子商务、移动互联网、网络营销平台。持续更新报道IT业界、互联网、市场资讯、驱动更新,是最及时权威的产业资讯及硬件资讯报道平台。

Nginx反代MySQL案例

案例：一个朋友要用Nginx代理MySQL（MySQL局域网），不用×××，不用NAT映射等，好吧，做个笔记。 Nginx版本：1.9.x（持tcp的负载均衡，nginx_tcp_proxy_module（姚伟斌阿里团队也可以实现）） Nginx官方模块：ngx_stream_core_module --with-stream_ssl_module（ssl协议支持，比如MySQL ssl）官网：http://nginx.org/en/docs/stream/ngx_stream_core_module.html 1、查看现有编译 --user=nginx--group=nginx--prefix=/usr/local/nginx--with-http_stub_status_module--with-http_ssl_module--with-http_realip_module--with-http_flv_module--with-http_mp4_module--with-http_gzip_static_module 2、重新编译： http://nginx.org/down...

2017-03-22

807

前面两篇文章我们和大家一起讨论到了SharePoint IIS的应用池回收，W3WP进程数和重置回收等方面的优化，今天来和大家讲讲后端SQL服务器的优化。对于SQL的优化，今天主要介绍的就是两点，CPU的优化和内存的优化。很多同学在装好SQL之后，其实并没有对内存优化进行设置，导致SQL的内存分配很不合理，针对于SharePoint，建议设置SQL的使用内存最少为 8192 MB，最多为 20480 MB 这个临界值。如上设置，注意，这里的配置值和运行值一定要配置两次，并且要保证其一直，否则不会生效，如果不匹配，多点击几次即可。默认情况下，这两个值的设置是不一样的，需要我们点击配置项，点击确定保存，再输入值，点击运行项目，再点击确定。多设置几次，两个地方反复点OK，多试几次。 OK，说完内存，现在我们来说下CPU，在一个SharePoint环境里面，或者私有云环境里面，正常情况下，SQL的CPU应该至少要跑在 40% ，伴随着硬盘会有频繁的读写IO。如果CPU占用不高，磁盘IO读写也不高，那就是SQL拖了后腿，SQL一旦拖后腿了，前端web服务器再怎么优化和牛X，用户访问也...

2017-03-23

765

资源下载

更多资源

腾讯云软件源

为解决软件依赖安装时官方源访问速度慢的问题，腾讯云为一些软件搭建了缓存服务。您可以通过使用腾讯云软件源站来提升依赖包的安装速度。为了方便用户自由搭建服务架构，目前腾讯云软件源站支持公网访问和内网访问。

Spring

Spring框架（Spring Framework）是由Rod Johnson于2002年提出的开源Java企业级应用框架，旨在通过使用JavaBean替代传统EJB实现方式降低企业级编程开发的复杂性。该框架基于简单性、可测试性和松耦合性设计理念，提供核心容器、应用上下文、数据访问集成等模块，支持整合Hibernate、Struts等第三方框架，其适用范围不仅限于服务器端开发，绝大多数Java应用均可从中受益。

Rocky Linux

Rocky Linux（中文名：洛基）是由Gregory Kurtzer于2020年12月发起的企业级Linux发行版，作为CentOS稳定版停止维护后与RHEL（Red Hat Enterprise Linux）完全兼容的开源替代方案，由社区拥有并管理，支持x86_64、aarch64等架构。其通过重新编译RHEL源代码提供长期稳定性，采用模块化包装和SELinux安全架构，默认包含GNOME桌面环境及XFS文件系统，支持十年生命周期更新。

Sublime Text

Sublime Text具有漂亮的用户界面和强大的功能，例如代码缩略图，Python的插件，代码段等。还可自定义键绑定，菜单和工具栏。Sublime Text 的主要功能包括：拼写检查，书签，完整的 Python API ， Goto 功能，即时项目切换，多选择，多窗口等等。Sublime Text 是一个跨平台的编辑器，同时支持Windows、Linux、Mac OS X等操作系统。