MySQL left join优化

问题描述

遇到了一个需要4个表连接查询的问题，数据量不是很大，两个表大概9000条数据，另外两个表大概几百条数据，但是每次查询时间都需要50秒左右的时间。

    SELECT
    *
    FROM
    gzgdm.gz_gd_region region
    LEFT OUTER JOIN
    gzgdm.gz_gd_htp_origin htp
    ON
    htp.regionid = region.id
    LEFT OUTER JOIN
    gzgdm.gz_gd_monitor_origin monitor
    ON
    monitor.regionid = region.id
    INNER JOIN gzgdm.gz_gd_code_county code
    ON
    code.code = '5954448117'
    AND
    region.city = code.city
    AND
    region.county = code.county

查询时间是49秒：

解决思路

打印MySQL的执行计划：（执行计划详解）

可以看到type都是最差的ALL，MySQL将遍历全表以找到匹配的行，所以这样对4个表连接的查询次数可能就是a*b*c*d，非常耗时。

MySQL的join算法

MySQL默认支持的join算法是Nested-Loop，如果参与join的内外表都没有设立索引，可能的查询次数就是a*b，非常大。MySQL的join算法与调优

方法

所以，解决这个问题的关键就是要给内表参与on的字段设置一个索引，这样就可以通过索引直接找到对应的项，而不是进行笛卡尔积。

此处，我们就设立一个外键：

它会自动创建一个索引：

再查看它的执行计划：

已经从ALL优化为了ref，查询速度可以看到只需要用0.03秒就可以查出来。

结论

在WHERE和join中出现的列需要建立索引，否则笛卡尔积的结果会导致查询的速度相当缓慢。

不过索引也不能乱建，会增加修改（insert、update、delete）的速度，并且建立索引肯定会占用磁盘的空间。

当MySQL有大数据量的表，或者有复杂的查询语句，一定要研究建立出优秀合适的索引。