我是如何将一条SQL语句从50秒的执行速度优化到0.11秒的

╰半橙微兮° 2022-01-10 13:31 169阅读 0赞

在给项目添加新功能的时候，需要union5张数据表，join2张字典表，由于一开始没有啥mysql优化经验，导致这7张表查询足足用了50秒左右的时间。

最开始写的SQL语句如下：

SELECT * FROM(
    SELECT A.*, (SELECT CONCAT(F.info, F.content) FROM F WHERE A.type = F.type AND F.id = 2222) descr
    FROM ATable A INNER JOIN DTable D ON A.key = D.key
    UNION ALL
    SELECT B.*, (SELECT CONCAT(F.info, F.content) FROM F WHERE B.type = F.type AND F.id = 2222) descr
    FROM BTable B INNER JOIN DTable D ON B.key = D.key
    UNION ALL
    SELECT C.*, (SELECT CONCAT(F.info, F.content) FROM F WHERE C.type = F.type AND F.id = 2222) descr
    FROM CTable C INNER JOIN DTable D ON C.key = D.key
    UNION ALL
    SELECT D.*, (SELECT CONCAT(F.info, F.content) FROM F WHERE D.type = F.type AND F.id = 2222) descr
    FROM DTable D INNER JOIN DTable D ON D.key = D.key
    UNION ALL
    SELECT E.*, (SELECT CONCAT(F.info, F.content) FROM F WHERE E.type = F.type AND F.id = 2222) descr
    FROM ETable E INNER JOIN DTable D ON E.key = D.key
    ) O INNER JOIN GTable G ON O.id = G.keyid WHERE O.name LIKE '%小明%';

即查询A表字段，以及F表字段信息，JOIN D表得出每个UNION的结果，然后UNION5个记录结果。

EXPLAIN后发现，descr字段查询了5次，每次都从几万数据中以ALL的方式查询，因此需要修改descr字段查询顺序；

由于D表每次都跟其他5张表做INNER JOIN，又会导致重复耗时，于是D表JOIN 查询又可以抽出来，只做一次查询，最后SQL语句为：

SELECT * FROM(
    SELECT A.*, CONCAT(F.info, F.content) descr
    FROM ATable
    UNION ALL
    SELECT B.*, CONCAT(F.info, F.content) descr
    FROM BTable
    UNION ALL
    SELECT C.*,CONCAT(F.info, F.content) descr
    FROM CTable C
    UNION ALL
    SELECT D.*, CONCAT(F.info, F.content) descr
    FROM DTable D
    UNION ALL
    SELECT E.*, CONCAT(F.info, F.content) descr
    FROM ETable E
    ) O INNER JOIN GTable G ON O.id = G.keyid
    INNER JOIN F ON O.type = F.type
    INNER JOIN D ON D.key = O.key
    WHERE O.name LIKE '%小明%' AND F.id = 2222;

表的错误请自动忽略。此时查询出的数据所需要时间从50秒减到19秒，但是依然不能接受。

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM0MjkxNTcw_size_16_color_FFFFFF_t_70][]

由于EXPLAIN的TYPE是ALL，所以我在联立的字段上增加了普通索引，结果查询后时间竟然神奇的减到了0.117秒

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM0MjkxNTcw_size_16_color_FFFFFF_t_70 1][]

主要还是要提供一种SQL优化思路，即：

尽量不要让同一张表和其他表重复UNION或者JOIN 多次

尽量不在字段中使用子查询，使select\_type向PRIMARY查询类型靠

尽量别使用范围或者模糊查询比如like, or等，可以使用in替代or

尽量添加合适的索引作为主要的条件字段

。。。当然优化SQL还有很多办法，需要大家发掘，哈哈~

做SQL优化一定要了解SQL执行顺序，执行的流程，是否在主要字段上又索引，是否使用的条件不支持索引等等！

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM0MjkxNTcw_size_16_color_FFFFFF_t_70]: /images/20220110/a9f2fdf426b945d69f905271c251d11f.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM0MjkxNTcw_size_16_color_FFFFFF_t_70 1]: /images/20220110/1d4d84c7e4c345b987bb713ce363965d.png