欢迎您光临本站,如有问题请及时联系我们。

智能运维:美团SQL Advisor的自动化SQL优化实现

  2017年10月19日,互联网+生活服务平台美团点评宣布完成新一轮40亿美元融资,投后估值300亿美元。


  在技术上,美团点评技术团队以及成为业界一流的研发组织,形成了比较完整的技术体系。在2017第七届数据技术嘉年华大会上,我们有幸邀请了美团点评的技术专家龙雪刚进行主题分享,将全面介绍和解读他们独立研发的SQL优化利器之SQLAdvisor的技术细节和底层架构。


  为了让大家更好地了解该产品,今天拣选此文分享。更多的技术细则,欢迎来数据技术嘉年华,目前限时免费报名中。


  介绍

  在数据库运维过程中,优化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:SELECTidFROMcrm_loanWHEREid_card=‘1234567’


  cmd:./sqladvisor-hxx-Pxx-uxx-pxx-dxx-q“SELECTidFROMcrm_loanWHEREid_card=‘1234567’”


  SQLAdvisor输出:altertablecrm_loanaddindexidx_id_card(id_card)


  SQLAdvisor的优点

  基于MySQL原生词法解析,充分保证词法解析的性能、准确定以及稳定性;

  支持常见的SQL(Insert/Delete/Update/Select);

  支持多表Join并自动逻辑选定驱动表;

  支持聚合条件Orderby和Groupby;

  过滤表中已存在的索引。

  SQLAdvisor原理介绍

  Join处理

  Join语法分为两种:Joinon和Joinusing,并且Joinon有时会存在where条件中。

  分析Join条件首先会得到一个nested_join的tablelist,通过判断它的join_using_fields字段是否为空来区分Joinon与Joinusing。

  生成的tablelist以二叉树的形式进行存储,以后序遍历的方式对二叉树进行遍历。

  生成内部解析树时,rightJoin会转换成leftJoin。

  Join条件会存在当层的叶子节点上,如果左右节点都是叶子节点,会存在右叶子节点。

  每一个非叶子节点代表一次Join的结果。

  上述实现时,涉及的函数为:mysql_sql_parse_join(TABLE_LISTjoin_table)mysql_sql_parse_join(Itemjoin_condition),主要流程图如下:


  MySQL


  where处理

  主要是提取SQL语句的where条件。where条件中一般由AND和OR连接符进行连接,因为OR比较难以处理,所以忽略,只处理AND连接符。

  由于where条件中可以存在Join条件,因此需要进行区分。

  依次获取where条件,当条件中的操作符是like,如果不是前缀匹配则丢弃这个条件。

  根据条件计算字段的区分度按照高低进行倒序排,如果小于30则丢弃。同时使用最左原则将where条件进行有序排列。

  计算区分度

  通过“showtablestatuslike”获得表的总行数table_count。

  通过计算选择表中已存在的区分度最高的索引best_index,同时Primarykey>Uniquekey>一般索引。

  通过计算获取数据采样的起始值offset与采样范围rand_rows:

  offset=(table_count/2)>10W?10W:(table_count/2)

  rand_rows=(table_count/2)>1W?1W:(table_count/2)

  使用selectcount(1)from(selectfieldfromtableforceindex(best_index)orderbycl..desclimitrand_rows)wherefield_print得到满足条件的rows。

  cardinality=rows==0?rand_rows:rand_rows/rows;

  计算完成选择度后,会根据选择度大小,将该条件添加到该表中的备选索引中。

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

  函数


  添加备选索引

  mysql_sql_parse_index()将条件按照选择度添加到备选索引链表中。

  上述两函数的流程图如下所示:

  函数


  Group与Order处理

  Group字段与Order字段能否用上索引,需要满足如下条件:

  涉及到的字段必须来自于同一张表,并且这张表必须是确定下来的驱动表。

  Groupby优于Orderby,两者只能同时存在一个。

  Orderby字段的排序方向必须完全一致,否则丢弃整个Orderby字段列。

  当Orderby条件中包含主键时,如果主键字段为Orderby。字段列末尾,忽略该主键,否则丢弃整个Orderby字段列。

  整个索引列排序优先级:等值>(groupby|orderby)>非等值。

  该过程中设计的函数主要有:

  mysql_sql_parse_group()判断Group后的字段是否均来自于同一张表。

  mysql_sql_parse_order()判断Order后的条件是否可以使用。

  mysql_sql_parse_group_order_add()将字段依次按照规则添加到备选索引链表中。

  索引


  驱动表选择

  经过前期的where解析、Join解析,已经将SQL中表关联关系存储起来,并且按照一定逻辑将候选驱动表确定下来。

  在侯选驱动表中,按照每一张表的侯选索引字段中第一个字段进行计算表中结果集大小。

  使用explainselect*fromtablewherefield来计算表中结果集。

  结果集小最小的被确为驱动表。

  步骤中涉及的函数为:final_table_drived(),在该函数中,调用了函数get_join_table_result_set()来获取每张驱动候选表的行数。

  添加被驱动表备选索引

  通过上述过程,已经选择了驱动表,也通过解析保存了语句中的条件。

  由于选定了驱动表,因此需要对被驱动表的索引,根据Join条件进行添加。

  该过程涉及的函数主要是:mysql_index_add_condition_field(),流程如下:


  输出建议

  通过上述步骤,已经将每张表的备选索引键全部保存。此时,只要判断每张表中的候选索引键是否在实际表中已存在。没有索引,则给出建议增加对应的索引。

  该步骤涉及的函数是:print_index(),主要的流程图为:


  SQLAdvisor版本更新

  FunctionalityAddedorChanged

  调整架构将SQLParser与SQLAdvisor模块隔离,方便调试。

  重新架构多表Join关系的find_join_elements()函数,思路更加清晰。

  修改选定驱动表的策略,确保驱动表为小结果集。

  添加where条件中的like处理。

  优化Orderby逻辑,忽略Orderbyprimarykey场景。

  输出索引建议前,增加判断索引是否已存在。

  BugsFixed

  修复SQL无法处理中文问题。

  修复字段多次出现在where条件中从而导致多次出现在索引列中问题。

  修复在find_best_index()函数中,对MySQLAPI中的result对象提前free,导致指针失效问题。


来源:本文由E8运维原创撰写,欢迎分享本文,转载请保留出处和链接!