美团点评SQL优化工具SQLAdvisor开源

标签: 美团 sql 优化 | 发表时间:2017-03-10 02:07 | 作者:美团点评技术团队
出处:http://tech.meituan.com/

介绍

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

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

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

现在,我们非常高兴地将 SQLAdvisor 开源,项目 GitHub 地址: https://github.com/Meituan-Dianping/SQLAdvisor 。我们已经把相关开发工作全面转到 GitHub 上,开源版本和内部使用版本保持完全一致。希望与业内有类似需求的团队,一起打造一款优秀的 SQL 优化产品。

SQLAdvisor架构流程图
SQLAdvisor处理流程

SQLAdvisor使用举例

  sql: SELECT id FROM crm_loan WHERE id_card = '1234567'
cmd: ./sqladvisor -h xx -P xx -u xx -pxx -d xx -q "SELECT id FROM crm_loan WHERE id_card = '1234567'"
SQLAdvisor输出: alter table crm_loan add index idx_id_card(id_card)

SQLAdvisor快速入门教程

SQLAdvisor的优点

  • 基于 MySQL 原生词法解析,充分保证词法解析的性能、准确定以及稳定性;
  • 支持常见的 SQL(Insert/Delete/Update/Select);
  • 支持多表 Join 并自动逻辑选定驱动表;
  • 支持聚合条件 Order by 和 Group by;
  • 过滤表中已存在的索引。

SQLAdvisor原理介绍

Join 处理

  1. Join语法分为两种:Join on 和 Join using,并且 Join on 有时会存在 where 条件中。
  2. 分析 Join 条件首先会得到一个 nested_join 的 table list,通过判断它的 join_using_fields 字段是否为空来区分 Join on 与 Join using。
  3. 生成的 table list 以二叉树的形式进行存储,以后序遍历的方式对二叉树进行遍历。
  4. 生成内部解析树时,right Join 会转换成 left Join。
  5. Join 条件会存在当层的叶子节点上,如果左右节点都是叶子节点,会存在右叶子节点。
  6. 每一个非叶子节点代表一次 Join 的结果。

上述实现时,涉及的函数为:mysql_sql_parse_join(TABLE_LIST join_table) mysql_sql_parse_join(Item join_condition) ,主要流程图如下:
join流程

where 处理

  1. 主要是提取 SQL 语句的 where 条件。where 条件中一般由 AND 和 OR 连接符进行连接,因为 OR 比较难以处理,所以忽略,只处理 AND 连接符。
  2. 由于 where 条件中可以存在 Join 条件,因此需要进行区分。
  3. 依次获取 where 条件,当条件中的操作符是 like,如果不是前缀匹配则丢弃这个条件。
  4. 根据条件计算字段的区分度按照高低进行倒序排,如果小于30则丢弃。同时使用最左原则将 where 条件进行有序排列。

计算区分度

  1. 通过 “show table status like” 获得表的总行数 table_count。
  2. 通过计算选择表中已存在的区分度最高的索引 best_index,同时Primary key > Unique key > 一般索引。
  3. 通过计算获取数据采样的起始值offset与采样范围rand_rows:
    • offset = (table_count / 2) > 10W ? 10W : (table_count / 2)
    • rand_rows =(table_count / 2) > 1W ? 1W : (table_count / 2)
    • 使用select count(1) from (select field from table force index(best_index) order by cl.. desc limit rand_rows) where field_print 得到满足条件的rows。
    • cardinality = rows == 0 ? rand_rows : rand_rows / rows;
    • 计算完成选择度后,会根据选择度大小,将该条件添加到该表中的备选索引中。

主要涉及的函数为:mysql_sql_parse_field_cardinality_new() 计算选择度。
计算区分度流程

添加备选索引

  1. mysql_sql_parse_index()将条件按照选择度添加到备选索引链表中。
  2. 上述两函数的流程图如下所示:
    添加备选索引

Group 与 Order 处理

  1. Group 字段与 Order 字段能否用上索引,需要满足如下条件:
    • 涉及到的字段必须来自于同一张表,并且这张表必须是确定下来的驱动表。
    • Group by 优于 Order by, 两者只能同时存在一个。
    • Order by 字段的排序方向必须完全一致,否则丢弃整个 Order by 字段列。
    • 当 Order by 条件中包含主键时,如果主键字段为 Order by。 字段列末尾,忽略该主键,否则丢弃整个 Order by 字段列。
  2. 整个索引列排序优先级:等值>(group by | order by )> 非等值。
  3. 该过程中设计的函数主要有:
    • mysql_sql_parse_group() 判断 Group 后的字段是否均来自于同一张表。
    • mysql_sql_parse_order() 判断 Order 后的条件是否可以使用。
    • mysql_sql_parse_group_order_add() 将字段依次按照规则添加到备选索引链表中。
      添加group
      处理group

驱动表选择

  1. 经过前期的 where 解析、Join 解析,已经将 SQL 中表关联关系存储起来,并且按照一定逻辑将候选驱动表确定下来。
  2. 在侯选驱动表中,按照每一张表的侯选索引字段中第一个字段进行计算表中结果集大小。
  3. 使用 explain select * from table where field 来计算表中结果集。
  4. 结果集小最小的被确为驱动表。
  5. 步骤中涉及的函数为:final_table_drived(),在该函数中,调用了函数 get_join_table_result_set() 来获取每张驱动候选表的行数。

添加被驱动表备选索引

  1. 通过上述过程,已经选择了驱动表,也通过解析保存了语句中的条件。
  2. 由于选定了驱动表,因此需要对被驱动表的索引,根据 Join 条件进行添加。
  3. 该过程涉及的函数主要是:mysql_index_add_condition_field(),流程如下:
    驱动表选择

输出建议

  1. 通过上述步骤,已经将每张表的备选索引键全部保存。此时,只要判断每张表中的候选索引键是否在实际表中已存在。没有索引,则给出建议增加对应的索引。
  2. 该步骤涉及的函数是:print_index() ,主要的流程图为:
    驱动表选择

SQLAdvisor版本更新

  • Functionality Added or Changed
    • 调整架构将 SQLParser 与 SQLAdvisor 模块隔离,方便调试。
    • 重新架构多表 Join 关系的 find_join_elements() 函数,思路更加清晰。
    • 修改选定驱动表的策略,确保驱动表为小结果集。
    • 添加 where 条件中的 like 处理。
    • 优化 Order by 逻辑,忽略 Order by primary key 场景。
    • 输出索引建议前,增加判断索引是否已存在。
  • Bugs Fixed
    • 修复 SQL 无法处理中文问题。
    • 修复字段多次出现在 where 条件中从而导致多次出现在索引列中问题。
    • 修复在 find_best_index() 函数中,对 MySQL API 中的 result 对象提前 free,导致指针失效问题。

愿景

和各位同行共同打造一款企业级优秀的 SQL 优化产品,希望大家能够积极参与。
欢迎大家将需求或发现的 Bug 在 Github 上提交 issue,帮助 SQLAdvisor 逐渐壮大;也欢迎大家在 SQLAdvisor 用户交流群(QQ: 231434335)相互交流,共同学习。

SQLAdvisor手册

  1. SQLAdvisor快速入门教程.
  2. SQLAdvisor原理和架构.
  3. SQLAdvisor release notes.
  4. SQLAdvisor开发规范.
  5. FAQ.

相关 [美团 sql 优化] 推荐:

美团点评SQL优化工具SQLAdvisor开源

- - 美团点评技术团队
在数据库运维过程中,优化 SQL 是 DBA 团队的日常任务. 例行 SQL 优化,不仅可以提升程序性能,还能够降低线上故障的概率. 目前常用的 SQL 优化方式包括但不限于:业务层优化、SQL逻辑优化、索引优化等. 其中索引优化通常通过调整索引或新增索引从而达到 SQL 优化的目的. 索引优化往往可以在短时间内产生非常巨大的效果.

sql优化

- - 数据库 - ITeye博客
是对数据库(数据)进行操作的惟一途径;. 消耗了70%~90%的数据库资源;独立于程序设计逻辑,相对于对程序源代码的优化,对SQL语句的优化在时间成本和风险上的代价都很低;. 可以有不同的写法;易学,难精通. 固定的SQL书写习惯,相同的查询尽量保持相同,存储过程的效率较高. 应该编写与其格式一致的语句,包括字母的大小写、标点符号、换行的位置等都要一致.

sql 优化

- - SQL - 编程语言 - ITeye博客
转:数据库SQL优化大总结之 百万级数据库优化方案. 2014-07-18 09:33 雲霏霏 雲霏霏的博客 字号:. 网上关于SQL优化的教程很多,但是比较杂乱. 近日有空整理了一下,写出来跟大家分享一下,其中有错误和不足的地方,还请大家纠正补充. 网上关于SQL优化的教程很多,但是比较杂乱. 近日有空整理了一下,写出来跟大家分享一下,其中有错误和不足的地方,还请大家纠正补充.

索引SQL优化

- - SQL - 编程语言 - ITeye博客
(一)深入浅出理解索引SQL优化 (转).         实际上,您可以把索引理解为一种特殊的目录. 微软的SQL SERVER提供了两种索引:聚集索引(clustered index,也称聚类索引、簇集索引)和非聚集索引(nonclustered index,也称非聚类索引、非簇集索引). 下面,我们举例来说明一下聚集索引和非聚集索引的区别:  .

优化sql查询

- - 数据库 - ITeye博客
1、 首先要搞明白什么叫执行计划. 执行计划是数据库根据SQL语句和相关表的统计信息作出的一个查询方案,这个方案是由查询优化器自动分析产生的,比如一条SQL语句如果用来从一个 10万条记录的表中查1条记录,那查询优化器会选择“索引查找”方式,如果该表进行了归档,当前只剩下5000条记录了,那查询优化器就会改变方案,采用 “全表扫描”方式.

sql语句优化

- - 数据库 - ITeye博客
性能不理想的系统中除了一部分是因为应用程序的负载确实超过了服务器的实际处理能力外,更多的是因为系统存在大量的SQL语句需要优化. 为了获得稳定的执行性能,SQL语句越简单越好. 对复杂的SQL语句,要设法对之进行简化. 1)不要有超过5个以上的表连接(JOIN). 2)考虑使用临时表或表变量存放中间结果.

SQL Server优化50法

- - CSDN博客推荐文章
虽然查询速度慢的原因很多,但是如果通过一定的优化,也可以使查询问题得到一定程度的解决.   查询速度慢的原因很多,常见如下几种:没有索引或者没有用到索引(这是查询慢最常见的问题,是程序设计的缺陷).   I/O吞吐量小,形成了瓶颈效应.   没有创建计算列导致查询不优化.   内存不足网络速度慢查询出的数据量过大(可以采用多次查询,其他的方法降低数据量).

oracle sql 优化大全

- - Oracle - 数据库 - ITeye博客
最近遇到了oracle sql优化的问题,找了一下,发现这文章实在不错,跟大家分享一下,如果以后有什么新的改进也会继续补充的. 1     前言… 2 . 2     总纲… 2 . 3     降龙十八掌… 3 . 第一掌 避免对列的操作… 3 . 第二掌 避免不必要的类型转换… 4 . 第三掌 增加查询的范围限制… 4 .

Oracle SQL性能优化

- - 数据库 - ITeye博客
(1)      选择最有效率的表名顺序(只在基于规则的优化器中有效):. ORACLE的解析器按照从右到左的顺序处理FROM子句中的表名,FROM子句中写在最后的表(基础表 driving table)将被最先处理,在FROM子句中包含多个表的情况下,你必须选择记录条数最少的表作为基础表. 如果有3个以上的表连接查询, 那就需要选择交叉表(intersection table)作为基础表, 交叉表是指那个被其他表所引用的表.

SQL Server优化50法

- - CSDN博客数据库推荐文章
  虽然查询速度慢的原因很多,但是如果通过一定的优化,也可以使查询问题得到一定程度的解决.   查询速度慢的原因很多,常见如下几种:. 没有索引或者没有用到索引(这是查询慢最常见的问题,是程序设计的缺陷). I/O吞吐量小,形成了瓶颈效应. 查询出的数据量过大(可以采用多次查询,其他的方法降低数据量).