数据库篇：MySQL explain

灰太狼 2021-09-22 00:44 228阅读 0赞

> 首发：[https://www.elltor.com/archives/124.html][https_www.elltor.com_archives_124.html]

为了知道SQL的效率执行的怎么样，经常使用 explain 查看SQL的具体执行过程，以加快SQL的执行效率。

explain 语句官方说明文档：https://dev.mysql.com/doc/refman/8.0/en/explain-output.html

### explain 使用示例 ###

使用 explain 对SQL进行分析，会得到一个或多个结果。

![image.png][]

### explain 字段含义 ###

![image.png][image.png 1]

*  id
    
     *  id相同表示再同一组，同一组中的查询从上往下执行
     *  id不同，可能存在子查询，值越大优先级越高，则先执行
 *  select\_type 用来查看查询的类型，是普通查询、联合查询或子查询

![image.png][image.png 2]

*  table 当前查询正在访问的表，表名或别名
 *  partitions 分区信息
 *  **type** 指明查询访问的类型。
 *  possible\_keys 显示可能应用在这张表中的索引，一个或多个，查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用
 *  **key** 实际使用的索引，如果为null，则没有使用索引，查询中若使用了覆盖索引，则该索引和查询的select字段重叠
 *  key\_len 表示索引中使用的字节数，可以通过key\_len计算查询中使用的索引长度，在不损失精度的情况下长度越短越好
 *  ref 显示索引的哪一列被使用了，如果可能的话，是一个常数
 *  **rows** 根据表的统计信息及索引使用情况，大致估算出找出所需记录需要读取的行数，此参数很重要，直接反应的sql找了多少数据，在完成目的的情况下越少越好
 *  filtered 过滤比例，使用distinct关键字，会进行过滤，filterd =（过滤后的记录数/过滤前的记录数）\* 100
 *  Extra 包含额外的信息，指明当前查询使用索引的情况

### type 字段 ###

type显示查询访问的类型，它有许多取值，在开发中应该尽量使查询类型处在**ref**，否则至少保持在**range**。

system > const > eq\_ref > **ref** > fulltext > ref\_or\_null > index\_merge > unique\_subquery > index\_subquery > **range** > index > ALL

*  system 只有一条记录的表（等于系统表）
 *  const 通过主键查询出一条记录

-- 通过主键查
    SELECT * FROM tbl_name WHERE primary_key=1;
    
    -- 通过联合主键查，须满足左匹配原则
    SELECT * FROM tbl_name
      WHERE primary_key_part1=1 AND primary_key_part2=2;

*  eq\_ref
    
     *  查询使用的所有列覆盖到PRIMARY KEY或UNIQUE非空字段
     *  where中索引列使用 = 进行比较

SELECT * FROM ref_table,other_table
      WHERE ref_table.key_column=other_table.column;
    
    SELECT * FROM ref_table,other_table
      WHERE ref_table.key_column_part1=other_table.column
      AND ref_table.key_column_part2=1;

*  ref 表示使用的是非主键和unique索引，一般是普通索引进行查询
 *  fulltext 使用全文索引
 *  ref\_or\_null 索引覆盖，普通索引做 IS NULL 判断（普通索引可以为NULL）

SELECT * FROM ref_table
      WHERE key_column=expr OR key_column IS NULL;

*  index\_merge 在查询过程中多个索引组合使用，仍然满足索引覆盖

SELECT * FROM tbl_name WHERE key1 = 10 OR key2 = 20;
    
    SELECT * FROM tbl_name
      WHERE (key1 = 10 OR key2 = 20) AND non_key = 30;
    
    SELECT * FROM t1, t2
      WHERE (t1.key1 IN (1,2) OR t1.key2 LIKE 'value%')
      AND t2.key1 = t1.some_col;
    
    SELECT * FROM t1, t2
      WHERE t1.key1 = 1
      AND (t2.key1 = t1.some_col OR t2.key2 = t1.some_col2);

*  unique\_subquery 该连接类型类似与index\_subquery,使用的是唯一索引
 *  index\_subquery 利用索引来关联子查询，不再扫描全表

-- student表中的s_id为主键，score中的索引为普通索引
    select s_name from student WHERE s_id in (SELECT s_id FROM score);

*  range 表示利用索引查询的时候限制了范围，在指定范围内进行查询，这样避免了index的全索引扫描，适用的操作符： =, <>, >, >=, <, <=, IS NULL, BETWEEN, LIKE, or IN()

-- 使用以下
    SELECT * FROM tbl_name
      WHERE key_column BETWEEN 10 and 20;
    
    SELECT * FROM tbl_name
      WHERE key_column IN (10,20,30);
    
    SELECT * FROM tbl_name
      WHERE key_part1 = 10 AND key_part2 IN (10,20,30);

*  index 索引覆盖且对整个索引进行扫描
    
     *  不满足左匹配原则
 *  ALL 全表扫描
    
     *  索引不覆盖

SELECT * FROM table_name

### 总结 ###

*  通过了解explain可以知道SQL的执行情况，如果查询类型（select\_type), 索引使用情况（key、type、possible\_key），查询的记录数（rows）等
 *  知道了索引索引在什么时候被选择和使用对SQL优化和表设计有很多帮助

编写SQL：

1.  尽量不适用子查询（subquery)，含有子查询的查询多生成一次查询，下面是一个例子，它们相同的功能

![image.png][image.png 3]

![image.png][image.png 4]

1.  查询是尽量保持索引覆盖
2.  不用时 select \*, 如果使用，一定会全部扫描

表设计：

1.  两个表相关联的字段，一般都要加索引
2.  表中常查询的字段和条件字段一般加索引
3.  经常房在一块查询的字段可以考虑组合索引（combination index）

（完）

[https_www.elltor.com_archives_124.html]: https://www.elltor.com/archives/124.html
[image.png]: /images/20210920/67bc8f81a54945f598ec715f2af4d041.png
[image.png 1]: /images/20210920/3196cca7b55f45f690e9d4897d2538ea.png
[image.png 2]: /images/20210920/a74ba78726924313be770a1ff7315eee.png
[image.png 3]: /images/20210920/9a696ea5d9ec42ebacb273e174dcfee3.png
[image.png 4]: /images/20210920/9ec55ba4def64c21a1d0edd90039dfc3.png