Mysql explain 优化解析

explain 解释

select_type 效率对比

MySQL 中 EXPLAIN 语句的 select_type 列描述了查询的类型,不同的 select_type 类型在效率上会有所差异。下面我们来比较一下各种 select_type 的效率:

1. SIMPLE:
   这是最简单的查询类型,表示查询不包含子查询或 UNION 操作。
   这种查询通常是最高效的,因为 MySQL 可以更好地优化执行计划。
   当查询只涉及一个表时,select_type 就会显示为 SIMPLE。

explain select * from user where uid=1;

2. PRIMARY:
   这种查询类型表示最外层的查询。
   与 SIMPLE 类型相比,它可能会稍微低效一些,因为可能包含子查询。
   当查询中包含子查询时,子查询的 select_type 会显示为 SUBQUERY。

explain select * from (select * from user where uid=1)b

3. SUBQUERY:
   这种查询类型表示作为独立子查询执行的查询块。
   子查询的效率通常比外层查询低,因为它需要单独执行并返回结果。
   子查询可能会在外层查询中多次使用,每次都需要重新执行,因此效率较低。

explain select * from groups where gid =(select gid from user where uid=1)

4. DERIVED:
   这种查询类型表示从 FROM 子句的结果集中派生出来的临时表。
   这种查询通常比较低效,因为需要在查询执行时动态计算临时表。
   使用临时表可能会导致 MySQL 使用 Using temporary; Using filesort 策略,从而降低查询效率。

explain select * from (select * from user where uid=1)b

5. UNION:
   这种查询类型表示 UNION 操作,用于合并多个查询结果集。
   UNION 操作通常比较低效,因为需要合并多个结果集。
   如果 UNION 中的子查询可以独立执行,可以考虑将它们拆分成多个查询,然后在代码中进行合并。

explain select * from user where uid=1 union select * from user where uid=2

6. DEPENDENT UNION
   依赖性(DEPENDENT): 这个子查询依赖于外层查询的结果。也就是说,子查询的执行需要依赖外层查询的结果。
   DEPENDENT UNION(从属联合)与DEPENDENT SUBQUERY(依赖子查询):
   当union作为子查询时，其中第二个union的select_type就是DEPENDENT UNION。第一个子查询的select_type则是DEPENDENT SUBQUERY。

7. UNION RESULT:
   这种查询类型表示 UNION 操作的结果。
   这种查询通常是最低效的,因为需要额外的合并操作。
   如果可以,尽量避免使用 UNION。

explain select * from user where uid=1 union select * from user where uid=2

总的来说,效率从高到低的顺序是:

SIMPLE > PRIMARY > SUBQUERY > DERIVED > UNION > DEPENDENT UNION > UNION RESULT

当然,实际的效率还受到其他因素的影响,如表的大小、索引情况、查询条件等。因此在实际使用中,我们还需要通过 EXPLAIN 语句分析具体的查询计划,并根据结果进行针对性的优化。

同时,也要注意查询的语义和可读性。有时为了提高效率,可能需要牺牲一些查询的可读性,这需要权衡取舍。

总之,在优化 MySQL 查询时,不仅要关注 select_type 的效率,还要综合考虑其他因素,并进行适当的优化。

type 列

TYPE含义解释

TYPE效率对比

MySQL 中 EXPLAIN 语句的 type 列描述了表访问的类型,这个列的值可以反映查询的效率。以下是 type 列各个值的含义:
好的,那我们再深入探讨一下 MySQL 中 EXPLAIN 语句的 type 列各个值的更多细节:

1. system:
   这是一种特殊的 const 类型,表示表中只有一条记录。
   这种类型的访问速度是最快的,因为只需要读取一条记录。
   通常出现在使用常量表的查询中,比如使用 LIMIT 1 的查询。
2. const:
   当查询能够在查询一次后就确定结果时,表示"constant"。
   典型的例子是当查询的 WHERE 子句使用主键或唯一索引时,MySQL 能在查询一次之后就确定结果。
   这种访问类型的速度非常快,因为它只需要读取一次记录。
3. eq_ref:
   当查询使用主键或唯一索引时,对于每个索引键,表中只有一条记录与之匹配。
   这种访问类型的效率仅次于 const,是一种非常高效的访问方式。
   常见于多表连接中根据主键或唯一索引列进行关联的情况。
4. ref:
   当查询使用非唯一索引或者触发了部分索引列(比如最左前缀)时,返回匹配某个单值的所有行。
   这种访问类型的效率略低于 eq_ref,但仍然较为高效。
   常见于使用非唯一索引进行关联查询的情况。
5. range:
   当使用索引来检索某个范围的记录时,该访问类型就会被使用。
   比如 WHERE col BETWEEN 10 AND 20 或 WHERE col IN (10, 20, 30)。
   这种访问方式需要检索索引中的部分键值,因此效率比 ref 稍低。
6. index:
   当 MySQL 决定全表扫描要比使用等值或范围索引快时, 并且索引覆盖所需要的列（包括在查询和条件中）时，使用索引树来遍历数据。
   这种方式虽然比全表扫描快,但比使用传统的索引扫描慢。
   通常出现在查询的 WHERE 子句未能有效利用索引的情况。
7. ALL:
   这是最差的访问类型,表示需要进行完整的表扫描。
   通常情况下,应该尽量避免查询出现这种访问类型。
   如果出现这种情况,通常意味着需要为相关列创建索引来优化查询。

总的来说,type 列的取值越往后,查询的效率就越低。提高查询效率的一个重要方法,就是尽量使用更高效的访问类型,如const、eq_ref、ref等。一般来说，至少保证查询达到range级别，最好达到ref。这需要为相关列建立合适的索引,并根据查询的条件进行针对性的优化。

实例分析

调优思路

• 拆分sql，并发查询出符合标签的group_id, 效果不理想
• 干掉多余的subquery，有效果
• in转换成join，效果不理想，跟数据量、数据分布、索引情况都有关系
  • 当数据量巨大（百万以上）、且数据散列分布均匀时，此时应该采用join
  • 大数据量不大或者数据分布聚集时，此时in效率更好
• 减少子查询，减少派生临时表，效果立竿见影

优化前后对比

优化前:

SELECT t1.*,
       t2.*
FROM
  (SELECT a.*
   FROM syyy_dest a
   WHERE a.del_flag = 0
     AND a.id IN
       (SELECT t3.group_id
        FROM
          (SELECT group_id,
                  group_concat(tag_id)AS tag_ids
           FROM syyy_group_tag
           WHERE delete_flag = 0
             AND tag_id IN ('123')
           GROUP BY group_id
           HAVING find_in_set('123', tag_ids)) t3) ) t1
LEFT JOIN
  (SELECT group_id,
          group_concat(category_name, ':', tag_name) AS tagName
   FROM syyy_group_tag gt
   LEFT JOIN syyy_tag c ON gt.tag_id = c.id
   LEFT JOIN syyy_tag_category stc ON c.tag_category_id = stc.id
   WHERE gt.delete_flag = 0
   GROUP BY group_id) t2 ON t1.id = t2.group_id
ORDER BY t1.id DESC
LIMIT 10;

优化前查询结果：

优化后:

SELECT
          a.*,
          gt.group_id,
          group_concat(stc.category_name, ':', c.tag_name) as tagName
        FROM  syyy_dest a
        LEFT JOIN (SELECT group_id, tag_id FROM syyy_group_tag WHERE delete_flag = 0) gt
        ON a.id = gt.group_id
        LEFT JOIN syyy_tag c
        ON gt.tag_id = c.id
        LEFT JOIN syyy_tag_category stc
        ON c.tag_category_id  = stc.id
        WHERE a.del_flag = 0         
            and a.id in
            (
            select t3.group_id from
            (select group_id , group_concat(tag_id)as tag_ids from syyy_group_tag
            where delete_flag = 0
            and tag_id in
             (  
                123
             ) 
            group by group_id
            having
                find_in_set( 123, tag_ids)
            ) t3 where 1=1
            ) 
        GROUP BY a.id
        order by a.id desc LIMIT 10

优化后查询结果：

减少派生临时表字查询的优化分析

因为需要在查询执行时动态计算临时表，因此这种查询通常比较低效

优化前

explain
SELECT t1.*,
       t2.tag_name
FROM
  (SELECT a.*
   FROM syyy_dest a
   WHERE a.del_flag = 0 ) t1
LEFT JOIN
  (SELECT group_id,
          group_concat(category_name, ':', tag_name) AS tag_name
   FROM syyy_group_tag gt
   LEFT JOIN syyy_tag c ON gt.tag_id = c.id
   LEFT JOIN syyy_tag_category stc ON c.tag_category_id = stc.id
   WHERE gt.delete_flag = 0
   GROUP BY group_id) t2 ON t1.id = t2.group_id
ORDER BY t1.id DESC

执行计划：

优化后

explain
SELECT
             a.*
            ,group_concat(stc.category_name, ':', c.tag_name) as tag_name
        FROM  syyy_dest a
        LEFT JOIN (SELECT group_id, tag_id FROM syyy_group_tag WHERE delete_flag = 0) gt
        ON a.id = gt.group_id
        LEFT JOIN syyy_tag c
        ON gt.tag_id = c.id
        LEFT JOIN syyy_tag_category stc
        ON c.tag_category_id  = stc.id
        WHERE a.del_flag = 0
GROUP BY a.id
ORDER BY a.id desc

执行计划：

优化分析：

1. 优化后减少了一次子查询，减少了派生临时表的生成

2. select_type优化后为smiple,性能最优

3. 优化后连接类型type，c和stc表为eq_ref，因为使用了主键连接；syyy_group_tag为ref，因为虽然使用了唯一建，但是只是触发了部分索引列(最左前缀)，因此连接方式不是eq_ref, 如下：
   

4. 优化后a表的连接类型依旧为index，扫描整个索引树，这种访问方式比全表扫描快,但相比使用其他索引访问方式(如 ref、eq_ref 等)仍然较慢。是因为连接条件a.del_flag = 0的数据离散度较小，数据分布极不均匀（只有0和1），所以mysql引擎优化的结果是不使用索引的等值查找（ref），即使存在del_flag字段的索引，如下：
   

参考

MySQL Explain（执行计划）详解