写点什么

心态炸了!我的 join 查询多加了个过滤条件性能就崩了

用户头像
林一
关注
发布于: 1 小时前
心态炸了!我的join查询多加了个过滤条件性能就崩了

起因

由于功能的迭代,为某个用了很久的查询接口多加了一个过滤条件,线下功能测试正常,但是上线后发现出现了慢查询。项目用的是 spring-data-jpa,通过线下打印查到慢查询语句为:


SELECT  count( discountru0_.id ) AS col_0_0_ FROM  discountrules discountru0_  CROSS JOIN discountmaps discountma1_ WHERE  discountma1_.type = 3   AND discountma1_.map_key = discountru0_.id   AND discountma1_.map_value = '350100'   AND discountru0_.is_deleted = 0   AND discountru0_.limit_area_status = 1   AND discountru0_.limit_product = 1   -- 注意这里最后一句过滤条件 discount_type =0  AND discountru0_.discount_type =0
复制代码


功能上线前原本的查询语句为


SELECT  count( discountru0_.id ) AS col_0_0_ FROM  discountrules discountru0_  CROSS JOIN discountmaps discountma1_ WHERE  discountma1_.type = 3   AND discountma1_.map_key = discountru0_.id   AND discountma1_.map_value = '350100'   AND discountru0_.is_deleted = 0   AND discountru0_.limit_area_status = 1   AND discountru0_.limit_product = 1 
复制代码


这两个语句的差别在最新的语句比原本语句多了一句 AND discountru0_.discount_type =0,两句的线上查询执行时间如下:

可以看到,加了 AND discountru0_.discount_type =0 之后查询时间多了两分钟。分析两张表的索引得出上述查询语句可能用到的索引如下:

  • discountrules.id(主键索引)

  • discountmaps.map_key(普通二级索引,idx_key)

  • discountmaps.map_value(普通二级索引,idx_value)


其余上述查询涉及到的字段都没有加索引。那么现在的问题就是:为什么多加了一个非索引字段过滤会导致查询变慢

复现

将两句不同 SQL 在线下进行执行,发现无法复现,得到如下相同的 EXPLAIN 结果:

查询性能没有啥区别。分析无法复现的原因为:线上与线下数据量不同,并且数据也不同,导致相同 SQL 执行性能产生不同。无奈,只能跑到线上分析执行计划,在执行加了 AND discountru0_.discount_type =0 的语句之后得到如下结果:

以上结果可以描述为:在执行 discountrules 和 discountmaps 连表查询的时候,先通过 discountrules 其它过滤条件进行过滤生成临时结果(注意此时是没有走任何索引的,执行全表扫描),然后将生成的结果带入 discountmaps 表进行过滤并生成最终连表的结果(此时有 idx_key 和 idx_value 两个索引可以走,最终走的是 idx_value 索引)。


下面再来看看原来没加 AND discountru0_.discount_type =0 的时候执行计划:

以上结果可以描述为:在执行 discountrules 和 discountmaps 连表查询的时候先通过 discountmaps 表的过滤条件生成临时结果(此时有 idx_key 和 idx_value 两个索引可以走,最终走的是 idx_value 索引),然后将生成的结果带入 discountrules 进行过滤并生成最终的链表结果(此时走主键索引)。


到此可以得出的结论是:两句性能有差别的原因是执行计划的不同导致走的索引不同,最终才产生巨大的性能差异。难道是 MySQL 在选择执行计划的时候选择错误了吗?是的,MySQL 在优化查询的时候是有可能选择错执行计划导致性能更差的!

解决方案

在讲解决方案之前先讲讲 MySQL 连接查询的查询成本计算公式:


查询总成本 = 单次访问驱动表的成本 + 访问驱动表后过滤出来的结果 x 单次访问被驱动表的成本


对于我们上面的例子:由于语句是 cross join,是一种内连接,对于内连接来说,驱动表和被驱动表的位置是可以互换的,MySQL 会计算不同表作为驱动表的查询总成本,然后选择一个成本最低的来执行。那么导致上面的原因就是 MySQL 用上面的公式计算查询总成本的时候认为使用 discountrules 表做驱动表的成本更低。(这里要注意的是,实际计算起来并不像上面公司那么简单,在预估访问驱动表后过滤出来的结果的时候,各个过滤条件都是参与计算的,并不是只有索引字段才参与计算,所以会出现算不准的情况,这里会用到表的统计数据啥的)。


如果你遇到上面的问题可以考虑如下解决方案:

  • 如果某列未编入索引,请将其编入索引,以便优化器了解有关列值分布的一些信息,并可以改进其行估计值。

  • 禁用会话的条件过滤:SET optimizer_switch = 'condition_fanout_filter=off';

用户头像

林一

关注

公众号:林一后端笔记 2020.02.13 加入

刚开始学写作,喜欢的同学欢迎关注一波~

评论

发布
暂无评论
心态炸了!我的join查询多加了个过滤条件性能就崩了