一次生产环境MySQL join 语句调优

爱被打了一巴掌 2021-09-21 13:04 269阅读 0赞

**前提：在MySQL使用 join 语句时，一定要让小表作为驱动表，而且join的列在被驱动表最好要有索引，否则很容易出现慢SQL。**

“小表作为驱动表”，MySQL查询优化器会对SQL评估选择最优的执行计划，所以即使你把大表写在前面作为驱动表，MySQL也会优化SQL，实际执行仍然用较小的表作为驱动表。【当然，你可以使用 straight join 强制指定驱动表，这时查询优化器就不会自主选择驱动表了】。

来看一下生产环境的一条SQL（这里把表名做了一下处理）：

<select id="pageByCondition4Operation" resultMap="simpleMap">
            select
            <include refid="OPERATOR_SIMPLE_COLUMNS"/>
            from (
            select * from ef_aa
            <include refid="PRODUCT_PAGE_WHERE"/>
            ) a left join ef_bb b on a.id = b.finance_product_id
            left join ef_cc c on a.id= c.finance_product_id
            <where>
                <if test="brandCode != null">
                    and b.brand_code = #{brandCode}
                </if>
                <if test="seriesCode != null">
                    and b.series_code = #{seriesCode}
                </if>
                <if test="modelCode != null">
                    and b.model_code = #{modelCode}
                </if>
                <if test="dealerIds != null">
                    and c.dealer_id in
                    <foreach collection="dealerIds" item="item" separator="," open="(" close=")">
                        #{item}
                    </foreach>
                </if>
                <if test="enable != null">
                    and a.enable = #{enable}
                </if>
            </where>
            group by a.id
            order by a.enable desc ,a.date_create desc
        </select>

这是一个列表页面的查询SQL，列表页面查询条件很多，导致了三张业务表（这里用 ef\_aa、ef\_bb、ef\_cc 表示）join 连接查询。我们要注意一点：当打开进入一个列表页时，往往是没有筛选条件的，这个时候所有的where条件可能都匹配不到，所以索引往往也用不上，这个时候往往是列表查询SQL执行效率最慢的时候。本次SQL就是出现在打开列表页，SQL执行超时，执行时间在3秒～4秒之间，页面直接超时报错了。

这个时候，钉钉线上告警群一报警，就感觉不对，因为这是一个新起的业务，业务表的数据量都不是很大。看了一下，ef\_aa 表200条记录，ef\_bb表告警当天业务有小的突破，但数据量也刚超过20000条，ef\_cc表记录1000条而已，这么小的数据量就导致了3～4秒的慢SQL。首先，使用 explain 命令查看了一下这种情况下的SQL执行计划：

explain select 
            <include refid="OPERATOR_SIMPLE_COLUMNS"/>
            from (
            select * from ef_aa
            ) a left join ef_bb b on a.id = b.finance_product_id
            left join ef_cc c on a.id= c.finance_product_id
            group by a.id
            order by a.enable desc ,a.date_create desc;

执行计划：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzIyMDc2MzQ1_size_16_color_FFFFFF_t_70][]

从 type 一列信息，我们可以看到三张表的关联，全部是 “ALL”级别，这种情况下就是三张都走了全表扫描做笛卡尔积，虽然三张表各自的行数都不大，ef\_aa join ef\_bb 就要嵌套循环 200 \* 20000 = 4000000，四百万次；得到结果后还要接着和ef\_cc做嵌套循环，这样一来查询效率就慢了。这是因为列 finance\_product\_id 在ef\_bb、ef\_cc表中都不是索引，导致嵌套循环从驱动表ef \_aa 中读取一条记录，ef\_bb表中没有索引，只能全表扫描ef\_bb表中记录匹配。如果在ef\_bb、ef\_cc表中加上列 finance\_product\_id的索引，这条SQL就会快很多。加上索引之后的explain 输出执行计划：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzIyMDc2MzQ1_size_16_color_FFFFFF_t_70 1][]

可以看到type 级别从没有索引的三个全表扫描“ALL”变成了“index”、“ref”、“ref”，key显示用到了相关索引，ken\_len 是8个字节（因为finance\_product\_id 是bigint类型）。加上索引之后的SQL执行时间缩短为：0.05秒左右。仅仅在被驱动表加上索引，这个分页列表SQL 执行时间便从 3～4秒下降到了0.05秒。

**所以，在join时，join的列一定要尽量使用被驱动表的索引。**

这里面还有一点值得个人思考的，以前自己受一些影响觉得建表时不用过多考虑索引，因为觉得索引数据量大的时候才有用，前期小数据量，mysql 往往全表扫描，而且有索引更新数据时还要更新索引文件数据，增加锁的开销，前期表建索引收益不大。但是这种想法，往往会埋坑，指不定哪天业务数据量到了一定地步，没有索引SQL就慢了，这个时候系统就不可用了，又要着急去建索引了，而且这时如果表数据量很大，在原表新建索引往往很慢，反而一开始在建表时就考虑设计创建索引，才是一种良好的做法。

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzIyMDc2MzQ1_size_16_color_FFFFFF_t_70]: /images/20210920/52c30e53ef2d4ba482be88e0178b51bb.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzIyMDc2MzQ1_size_16_color_FFFFFF_t_70 1]: /images/20210920/9250cc18d12343b4918ace08c0d82ded.png