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

最新推荐文章于 2024-07-22 18:11:20 发布

灵颖桥人

最新推荐文章于 2024-07-22 18:11:20 发布

阅读量479

点赞数 3

分类专栏： MySQL 文章标签： mysql 索引 sql调优

本文链接：https://blog.csdn.net/qq_22076345/article/details/109662413

版权

MySQL 专栏收录该内容

8 篇文章 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;

执行计划：

从 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 输出执行计划：

可以看到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就慢了，这个时候系统就不可用了，又要着急去建索引了，而且这时如果表数据量很大，在原表新建索引往往很慢，反而一开始在建表时就考虑设计创建索引，才是一种良好的做法。