MySQL中10多张表关联要做优化，怎么理解逻辑幂等

最近优化了一条MySQL的慢查询SQL,还是蛮有感触，小结一下。

首先问题的背景是一个业务做压力测试，排除了很多的前期问题，使用的最有效手段就是索引，在最后一个环节，问题开始陷入焦灼状态，因为这一条SQL的相关表有16张，而且是在业务环节中频繁调用和引用的逻辑。

一般碰到问题都会有一个疑问，说这是谁写的SQL,应该快速重构，但是大部分优化场景都是：优化可以做，但业务不能停。 所以重构需要，但是不是现在。

在一种很复杂的心情下开始了优化，当然在查看了执行计划后让我除了绝望还有一种惊喜。那就是里面有一个明显全表扫描的逻辑，也就意味着尽管这么多表关联，但是数据量也可以接受，在优化器解析时大部分逻辑是走了索引，优化好最后一个全表扫描，整个问题就迎刃而解了。

当然我不用把整个SQL粘贴处理，全文超过5000字符，所以我做了简化，在做了一些对比测试之后，把问题的逻辑简化为下面的SQL形式，也就意味着这个SQL优化成功，则整个优化就意味着成功。

目标看起来很简单，但是让人开始纠结的是里面的都是left join,怎么破？

SQL语句如下：

SELECT 
    prod_id, prod_name, tag_url
FROM
    product sku
        LEFT JOIN
    (SELECT 
        jt.tag_url, jts.prod_id
    FROM
        tag jt
    LEFT JOIN prod_tag jts ON jt.tag_id = jts.tag_id
    WHERE
        jts.sku_id IN (1 , 2, 3, 4)
            AND NOW() >= jt.start_time
            AND jt.store_id = 0
            AND jt.end_time >= NOW()) AS tag_new ON sku.sku_id = tag_new.sku_id

整个SQL的逻辑是输出其中product表的数据（字段prod_id,prod_name）和tag表的数据（tag_url）,其中表tag和表tag_product)他们通过字段(tag_id)进行关联，然后和外部的表使用prod_id进行关联，为了体现出是left join(左连接)，我把表product的位置及往上放了放。

整个逻辑其实从上面的图看起来还是有点别扭，tag_product的数据还得反向和外部的表进行关联。

所以对于上面的逻辑，其实数据表product和表tag要联合输出数据，需要借助一个中间表tag_product,那么tag_product应该是连接数据的纽带，一个相对比较合理的方式就是其实基于表product,tag_product和tag这样的顺序来进行过滤。

所以我补充了如下的图来说明这个逻辑。

从通常的设计来说，这样是最合理的方式，可以使得逻辑关系更加清晰。

看起来这应该是比较合理的方式了。

SELECT prod_id,prod_name,tag_url
FROM product sku
 LEFT JOIN tag_product jts on  jts.sku_id =sku.sku_id
    left join (select tag_url,tag_id 
            from tag jt where jt.start_time <= NOW()
            AND jt.store_id = 0
            AND jt.end_time >= NOW()) tag_new 
            on tag_new.tag_id=jts.tag_id

在经过测试之后，感觉已经很接近问题的真相了。

但是在进一步和业务沟通，了解了业务的实现细节，发现整个逻辑似乎和我们理解的不大一样。

比如tag表的数据

tag_id:1,tag_status:ACTIVE,

tag_id:2,tag_status:INACTIVE

tag_product的数据

tag_id:1,prod_id:100

tag_id:1,prod_id:200

tag_id:2,prod_id:100

按照业务逻辑，如果tag表中的做过滤后的数据为

tag_id:1,tag_status:ACTIVE,

则根据SQL的逻辑，left join会和表tag_product再做一次连接，数据以tag表中的tag_id为准，输出就是：

tag_id:1,prod_id:100

tag_id:1,prod_id:200

而如果采用上述的连接方式，其实就会出现意料之外的数据。

比如，按照tag_product进行过滤

tag_id:1,prod_id:100

tag_id:1,prod_id:200

tag_id:2,prod_id:100

然后和tag做关联，tag输出数据为:

tag_id:1,tag_status:ACTIVE,

tag_id:2,tag_status:INACTIVE

这样一来就失去了过滤的意义。

当然沟通的过程中，也进一步理解了需求，其实我们所谓的逻辑幂等，不是真正意义上的业务逻辑幂等。 从业务逻辑幂等上，是按照表tag的输出为标准。所以整个tag和tag_product的关联可以降维为普通的表关联，而非left join.

整个改进的逻辑如下图所示：

在业务层明确之后，而且输出结果和预期一致的情况下，整个改动的部分就是删除了left join中的left,整个 SQL的执行效率又变得更加流畅。