SQL优化三步曲

有一天开发同学反馈线上业务库中有一条SQL执行很满,每次几乎要跑1分钟才结束,希望我们帮忙优化一下,具体SQL如下:

SQL优化三步曲

SQL优化第一步 - 查看执行计划

对于一个SQL的优化,我们的第一步也是最重要的一步就是查看SQL执行计划,SQL执行计划一方面告诉我们SQL具体的处理行为,另外一方面也可以体现每个执行步骤下大致的资源消耗点。所以我们拿到问题SQL以及对应数据库环境后,登录该环境只读实例进行SQL分析测试。

SQL优化三步曲

从以上的SQL执行计划我们可以获取到哪些有效信息呢?

  • SQL先对t表扫描查询生产派生表,brand通过索引过滤作为表关联的驱动表,与vender、product、shop_product、spu、进行关联查询,表关联均有效利用索引。
  • 从type字段上看,SQL基本上都有效利用到了索引,但是index其实是全索引扫描,该方式的索引扫描执行效率并不会很好
  • 对t表的index全索引扫描数据量高达480w,在当前SQL中执行消耗最大,这也将是我们SQL优化的切入点
  • t表在源SQL中并未出现,再次仔细观察SQL可以发现SQL引用了view_prod_store_sum的视图

SQL优化第二步 -

在MySQL中对于视图使用我们需要知道的是虽然MySQL对视图的查询做了一些优化,但是对于复杂视图查询其优化支持仍然不是很好,所以业务上我们要尽量避免对复杂视图的使用。在本SQL中视图其实是对单表的查询,且目前SQL资源消耗的瓶颈点也在视图查询这部分,所以我们将视图的定义通过子查询代替原视图,整体的来看SQL。

视图定义:

SQL优化三步曲

完整SQL:

SQL优化三步曲

SQL优化第三步 - 适当改写

结合我们获取到的SQL执行计划以及恢复出来的完整SQL,我们再次理解当前SQL的处理行为:

1)对t表进行全索引扫描,生产派生表2

2)brand表通过brand_id IN (252)条件进行索引过滤,后续与其他表以及派生表2进行关联查询

  • 资源消耗点分析:

从SQL真正需要查询的数据来看,我们只需要先通过where子句中表过滤条件过滤获取初步满足条件记录,然后对这些记录判断 sum(store) > 0是否满足,满足则返回。但是该SQL实际处理却是先将t表中所有记录的store进行分组计算,将结果保存在派生表中。通过where子句中表过滤条件后的记录再与派生表关联判断sum(store)。

SQL在处理的过程似乎扫描了很多不必要的数据,我们为何不仅仅对已满足where子句条件过滤的记录做sum(store)判断呢?

基于以上的分析,我们尝试使用exists相关子查询进行改写测试。为什么使用相关子查询呢,这是因为exists在处理SQL时的核心思想是先对where 前的主查询询进行查询,然后用主查询的结果一个一个的代入exists的查询进行判断。 因此我们可以有效的利用exists避免的避免掉优先对t表的派生表产生,保证SQL优先通过where子句中选择性最佳的条件做驱动表,然后对sum(store)通过相关子查询进行判断。

  • 具体改写如下:
    SQL优化三步曲
  • 改写后的执行计划:

SQL优化三步曲

  • 执行效率对比

优化前:

SQL优化三步曲

优化后:

SQL优化三步曲

在一般业务SQL编写中,我们都推荐开发同学使用join而不是exists,这是因为exists本身处理SQL的方式下如果where条件处理后外表记录仍然很大的情况下,再次将外表中每条记录代入exists子查询中判断,其资源消耗代价是很大的。所以我们更偏向使用JOIN,在满足必要的索引情况下MySQL优化器优先选择小表进行驱动。无论具体选择什么方式,其实减少扫描函数才是王道!

上一篇:Hive常见语句及函数


下一篇:某物流客户Elasticsearch集群性能优化案例