心态炸了！我的 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';