备案控制台
探索云世界
开发者社区 开发与运维 文章 正文

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

2017-11-07 1589
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
简介：

前言

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






 本文转自 dbapower 51CTO博客，原文链接：http://blog.51cto.com/suifu/1909169，如需转载请自行联系原作者


文章标签：
云解析DNS
SQL
索引
开发工具
git
关键词：
SQL优化
SQL索引
SQL工具
SQL索引优化
技术小胖子
目录
相关文章
changwoo
|
27天前
|
SQL 存储 关系型数据库
一文搞懂SQL优化——如何高效添加数据
**SQL优化关键点：** 1. **批量插入**提高效率，一次性建议不超过500条。 2. **手动事务**减少开销，多条插入语句用一个事务。 3. **主键顺序插入**避免页分裂，提升性能。 4. **使用`LOAD DATA INFILE`**大批量导入快速。 5. **避免主键乱序**，减少不必要的磁盘操作。 6. **选择合适主键类型**，避免UUID或长主键导致的性能问题。 7. **避免主键修改**，保持索引稳定。 这些技巧能优化数据库操作，提升系统性能。
changwoo
226 4
一文搞懂SQL优化——如何高效添加数据
穆雄雄.
|
1月前
|
SQL 存储 数据库
sql事务、视图和索引
sql事务、视图和索引
穆雄雄.
14 0
1030024444162706
|
1月前
|
SQL 存储 数据库连接
日活3kw下，如何应对实际业务场景中SQL过慢的优化挑战？
在面试中，SQL调优是一个常见的问题，通过这个问题可以考察应聘者对于提升SQL性能的理解和掌握程度。通常来说，SQL调优需要按照以下步骤展开。
1030024444162706
136 3
Sereinn
|
1月前
|
SQL 存储 弹性计算
GaussDB SQL调优：建立合适的索引
GaussDB SQL调优：建立合适的索引
Sereinn
12 0
1024小神
|
1月前
|
存储 关系型数据库 MySQL
最全MySQL面试60题（含答案）：存储引擎+数据库锁+索引+SQL优化等
最全MySQL面试60题（含答案）：存储引擎+数据库锁+索引+SQL优化等
1024小神
178 0
阿里开发者
|
4天前
|
SQL 分布式计算 资源调度
一文解析 ODPS SQL 任务优化方法原理
本文重点尝试从ODPS SQL的逻辑执行计划和Logview中的执行计划出发，分析日常数据研发过程中各种优化方法背后的原理，覆盖了部分调优方法的分析，从知道怎么优化，到为什么这样优化，以及还能怎样优化。
阿里开发者
41 0
LightOf
|
12天前
|
SQL 关系型数据库 数据库
【后端面经】【数据库与MySQL】SQL优化：如何发现SQL中的问题？
【4月更文挑战第12天】数据库优化涉及硬件升级、操作系统调整、服务器/引擎优化和SQL优化。SQL优化目标是减少磁盘IO和内存/CPU消耗。`EXPLAIN`命令用于检查SQL执行计划，关注`type`、`possible_keys`、`key`、`rows`和`filtered`字段。设计索引时考虑外键、频繁出现在`where`、`order by`和关联查询中的列，以及区分度高的列。大数据表改结构需谨慎，可能需要停机、低峰期变更或新建表。面试中应准备SQL优化案例，如覆盖索引、优化`order by`、`count`和索引提示。优化分页查询时避免大偏移量，可利用上一批的最大ID进行限制。
LightOf
38 3
极客李华
|
17天前
|
SQL 数据库 索引
SQL索引失效原因分析与解决方案
SQL索引失效原因分析与解决方案
极客李华
22 0
小强聊it
|
21天前
|
SQL 运维 数据管理
sql管理工具archery简介
Archery是一个多公司采用的SQL管理工具，提供权限管理、工作流配置、实例管理、SQL审核、查询、优化及通知功能。它支持多级审批和不同云环境的数据管理。尝试Archery的SaaS版本可访问[ArcheryDMS.com](https://archerydms.com/home/)。此外，NineData是一个综合平台，包含SQL开发、数据复制等功能，适应混合云和多云环境，由叶正盛创建，详情见[Ninedata.cloud](https://www.ninedata.cloud/aboutus)。
小强聊it
37 0
洛神灬殇
|
28天前
|
SQL 关系型数据库 MySQL
【MySQL技术之旅】（7）总结和盘点优化方案系列之常用SQL的优化
【MySQL技术之旅】（7）总结和盘点优化方案系列之常用SQL的优化
洛神灬殇
42 1

热门文章

最新文章

  • 1
    一文搞懂SQL优化——如何高效添加数据
  • 2
    【SQL server】玩转SQL server数据库：第三章 关系数据库标准语言SQL（二）数据查询
  • 3
    【SQL server】玩转SQL server数据库：第二章 关系数据库
  • 4
    SQL常见面试题总结2
  • 5
    NL2SQL实践系列(2)：2024最新模型实战效果(Chat2DB-GLM、书生·浦语2、InternLM2-SQL等)以及工业级案例教学
  • 6
    NL2SQL进阶系列(5)：论文解读业界前沿方案（DIN-SQL、C3-SQL、DAIL-SQL）、新一代数据集BIRD-SQL解读
  • 7
    数据库管理与电脑监控软件：SQL代码优化与实践
  • 8
    【MySQL技术之旅】（7）总结和盘点优化方案系列之常用SQL的优化
  • 9
    大模型与数据分析：探索Text-to-SQL（上）
  • 10
    SQL，Group By 真扎心，原来是这样
  • 1
    [MySQL]SQL优化之索引的使用规则
    35
  • 2
    [MySQL] SQL优化之性能分析
    30
  • 3
    sql文件批处理程序-java桌面应用
    25
  • 4
    《牛客笔试选择题》sql错题集
    26
  • 5
    AI代码生成器——AI2sql
    64
  • 6
    10个高级的 SQL 查询技巧
    48
  • 7
    【问题】Cause: java.sql.SQLException: 不支持的字符集 (在类路径中添加 orai18n.jar): ZHS16GBK
    30
  • 8
    解决SQL报错：索引中丢失IN或OUT參数
    21
  • 9
    Mybatis拦截器实现带参数SQL语句打印
    41
  • 10
    Flink CDC产品常见问题之sql运行中查看日志任务失败如何解决
    34

    • 相关课程

    更多
  • 如何在 PolarDB-X 中优化慢 SQL
  • SQL完全自学手册
  • SQL Server on Linux入门教程
  • SQL入门与实践
  • 数据库及SQL/MySQL基础
  • SQL进阶及查询

    • 相关电子书

    更多
  • SQL Server在电子商务中的应用与实践
  • GeoMesa on Spark SQL
  • 原生SQL on Hadoop引擎- Apache HAWQ 2.x最新技术解密malili

    • 相关实验场景

    更多
  • PolarDB NL2SQL：自然语言驱动的数据智能
  • MySQL引擎及架构优化
  • PolarDB for AI：在数据库中通过SQL实现AI能力
  • 玩转MaxCompute SQL! 30分钟搞定数据分析挖掘
  • 使用SQL语句实现数据查询操作
  • 使用SQL语句管理索引
    • 下一篇
    部署LAMP环境（Alibaba Cloud Linux 3）