SQL优化:关联子查询&派生表优化案例

2020-03-04 本文已影响0人轻松的鱼

优化过程

1. 问题SQL：

SELECT E.AREAID
FROM (
    SELECT T1.STRU_ID, T1.STRU_GRADE1
    FROM (
        SELECT F.BRANCH_ID AS STRU_ID
            , (
                SELECT T.BRANCH_ID
                FROM t_obom_subbranch T
                WHERE t.stru_grade = 'x'
                    AND t.sub_branch_id = F.BRANCH_ID
            ) AS STRU_GRADE1
        FROM t_obom_branch F
    ) T1
    WHERE T1.STRU_GRADE1 IS NOT NULL
) T2
    LEFT JOIN (T_OBOM_AREA_HEAD_REL E, T_OBOM_BRANCH O) ON T2.STRU_GRADE1 = E.STRUID
WHERE E.AREAID IS NOT NULL
    AND T2.STRU_ID = O.BRANCH_ID;

2. 执行计划：

3. slow log 输出：

# Time: 2020-02-13T05:12:22.326634+08:00
# User@Host: 
# Query_time: 13.969574 Lock_time: 0.000327 Rows_sent: 0 Rows_examined: 16234764
SET timestamp=1581541942;
UPDATE T_OBOM_BRANCH O SET O.AREAID =(SELECT E.AREAID FROM(SELECT T1.STRU_ID, T1.STRU_GRADE1 FROM(SELECT F.BRANCH_ID STRU_ID,(SELECT T.BRANCH_ID FROM t_obom_subbranch T WHERE t.stru_grade = '2' and t.sub_branch_id=F.BRANCH_ID)STRU_GRADE1 FROM t_obom_branch F)T1 WHERE T1.STRU_GRADE1 IS NOT NULL)T2 LEFT JOIN T_OBOM_AREA_HEAD_REL E ON T2.STRU_GRADE1 = E.STRUID WHERE E.AREAID IS NOT NULL AND T2.STRU_ID = O.BRANCH_ID);

4. 解剖SQL

这个SQL乍一看层层叠叠很复杂的样子，但是我们切开一看，其实变得简单了：

就是把SQL1 的结果和其他两张表做 join，执行计划中 join 部分效率很高，所以不需要优化，接下来只需要分析 SQL1 就好了。

5.分析有问题的那部分SQL

SQL1 的意思是要把 SQL2 这个子查询的结果中 STRU_GRADE 字段值为 NULL 的行去除掉；
SQL2 做了什么？SQL2 的语法很有意思，其中又包含了 SQL3 这个子查询，而且子查询结果作为选择的字段（第一次遇到这种语法），又与外表进行关联查询，所以这是一个关联子查询。

关于 SQL2 的执行结果，其实等价于一个 left join 外连接，下面是一个简单示例说明：

外连接结果会产生 NULL 值，然后SQL1 的处理就是把 SQL2 产生结果中的 NULL 值过滤掉。这意味着什么？意味着这三个子查询其实就是为了实现 inner join，多此一举。所以直接改写成 inner join：

SELECT F.BRANCH_ID AS STRU_ID, t.BRANCH_ID AS STRU_GRADE1
FROM t_obom_subbranch T, t_obom_branch F
WHERE t.stru_grade = 'x'
        AND t.sub_branch_id = F.BRANCH_ID;

优化结果

优化后的完整SQL：

SELECT E.AREAID
FROM (
    SELECT F.BRANCH_ID AS STRU_ID, t.BRANCH_ID AS STRU_GRADE1
    FROM t_obom_subbranch T, t_obom_branch F
    WHERE t.stru_grade = 'x'
        AND t.sub_branch_id = F.BRANCH_ID
) T2
    LEFT JOIN (T_OBOM_AREA_HEAD_REL E, T_OBOM_BRANCH O) ON T2.STRU_GRADE1 = E.STRUID
WHERE E.AREAID IS NOT NULL
    AND T2.STRU_ID = O.BRANCH_ID;

执行计划：

为什么子查询和 join 之间的效率差这么大？

其实我的优化思路是从查询结果入手分析的：因为3个子查询是为了达到 inner join 的结果，所以直接改写成 inner join。但是为什么子查询和 join 之间的效率差这么大？

有两个原因：

SQL2 是一个关联子查询，关联子查询的顺序是：外部查询的每行数据传递一个值给子查询，然后子查询为每一行数据执行一次并返回它的记录，之后外部查询根据返回的记录做出决策。

SELECT T1.STRU_ID, T1.STRU_GRADE1
    FROM (
        SELECT F.BRANCH_ID AS STRU_ID
            , (
                SELECT T.BRANCH_ID
                FROM t_obom_subbranch T
                WHERE t.stru_grade = 'x'
                    AND t.sub_branch_id = F.BRANCH_ID
            ) AS STRU_GRADE1
        FROM t_obom_branch F
    ) T1
    WHERE T1.STRU_GRADE1 IS NOT NULL

SQL2 的执行顺序是：
a. 先从 F 表中取出一行数据 X；
b. 从 X 中取出关联字段 BRANCH_ID 的值，去 T 表中查找，满足条件的行取出；
c. 重复 a、b，直到 F 表最后一行循环结束。

T 表 sub_branch_id 字段虽然是主键的一部分，但是是第二个字段，所以 SQL2 执行时，F、T 表都是全表扫描，所以效率低：

改写为 inner join 后，由于 F.BRANCH_ID 字段是主键，所以优化器会选择 T 表为驱动表，F 表为被驱动表，这样被驱动表 F 进行数据查找时就能使用到索引了（《MySQL优化:JOIN 优化实践》中有详细解释），效率飙升。

原 SQL 中有派生表（即 derived3 ），并且派生子查询中做了关联子查询（即 SQL2），使得 derived_merge 特性失效。优化器会将派生表物化为内部临时表，再与其他表进行联接，效率变低。