526互联

Explain简记

发布时间 2023-11-05 00:13:37作者: peng_boke

EXPLAIN简记

CREATE DATABASE IF NOT EXISTS test_db DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
use test_db;
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  `age` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `udx_name` (`name`),
  UNIQUE KEY `idx_age` (`age`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- ----------------------------
-- Records of student
-- ----------------------------
BEGIN;
INSERT INTO `student` VALUES (1, 'S001', 24);
INSERT INTO `student` VALUES (2, 'S002', 23);
INSERT INTO `student` VALUES (3, 'S003', 22);
COMMIT;

EXPLAIN SELECT * FROM student where id = 1

在需要执行的SQL前加上EXPLAIN，MYSQL不会真正执行这条SQL语句，而是模拟优化器执行SQL查询语句

image-20230406165541644

1.id

ID 字段的值及其排列顺序，表明 MySQL 执行时从各表取数据的顺序。

  • ID 相同的组，其执行优先级按照其顺序由上到下。
  • ID 越大的组，其执行优先级越高。

2.select_type

select_type 字段表示该 SQL 是什么查询类型，一共有以下 6 种：

  • SIMPLE：简单查询，不包含子查询或 union 查询
  • PRIMARY：主键查询
  • SUBQUERY：在 select 或 where 中包含子查询
  • DERIVED：from 中包含子查询
  • UNION
  • UNION RESULT

3.type

type 字段表示访问情况，通常用来衡量 SQL 的查询效率。其值的查询效率从最好到最差分别为：

  • NULL：NULL 表示 MySQL 能够在优化阶段分解查询语句，在执行阶段用不着再访问表或索引。

  • system：表只有一行记录（等于系统表），这是const类型的特列。

  • const：const 表示该表最多有一个匹配记录。通常情况下是 SQL 中出现了主键索引或唯一索引。

  • eq_ref： 表示主键索引或唯一索引的所有部分被连接使用 ，最多只会返回一条符合条件的记录。与 const 类型非常相似，唯一的区别是 eq_ef 通常出现在联表的情况下，而 const 通常出现在单表情况下。

  • ref：表示使用了非唯一索引扫描，会返回匹配某个单独值的所有行。与 const 非常类似，只不过 ref 会匹配到多个记录，而 const 则只会匹配到单个记录。

  • ref_or_null：类似ref，但是可以搜索值为NULL的行。

  • index_merge：表示使用了索引合并的优化方法。索引合并指的是：对多个索引分别进行条件扫描，然后将它们各自的结果进行合并。

  • range 表示检索给定范围的行，使用一个索引来选择行，key 列显示使用了哪个索引。

    一般就是在你的 where 语句中出现 between、<>、in 等的范围查询。

  • index：表示只遍历索引树，且只从索引树中获取数据。

  • ALL：ALL 表示该查询将遍历全表以找到匹配行

4.table

表示数据来自哪张表

5.possible_keys

显示可能应用在这张表中的索引，一个或多个。

查询涉及到的字段若存在索引，则该索引将被列出，但不一定被实际使用。

6.key

实际使用到的索引，如果为NULL，则没有使用索引。

查询中若使用了覆盖索引（查询的列刚好是索引），则该索引仅出现在key列表。

7.key_len

这一列显示了 mysql 在索引里使用的字节数，通过这个值可以算出具体使用了索引中的哪些列。

比如idx_studentId_teacherId联合索引由studentId和teacherId俩个int组成，每个int是4字节。通过key_len=4可推断出查询使用了studentId列执行索引查找

key_len计算规则：

  • char(n)：n字节长度
  • varchar(n)：2n字节长度，如果是utf-8，则长度是3n+2
  • tinyint：1字节
  • smallint：2字节
  • int：4字节
  • bigint：8字节　
  • date：3字节
  • timestamp：4字节
  • datetime：8字节
  • 如果字段允许为 NULL，需要1字节记录是否为 NULL

8.ref

这一列显示了在 key 列记录的索引中，表查找值所用到的列或常量，常见的有：const（常量），func，NULL，字段名（例：film.id）。

9.rows

这一列是mysql估计要读取并检测的行数，注意这个不是结果集里的行数。

10.Extra

这一列展示的是额外信息。

  • distinct：一旦mysql找到了与行相联合匹配的行，就不再搜索了。
  • Using index：这表示查找某个表的时候，所需要的信息直接从索引就可以拿到，而不需要再访问行记录。
  • Using where：mysql 服务器将在存储引擎检索行后再进行过滤。就是先读取整行数据，再按 where 条件进行检查，符合就留下，不符合就丢弃。
  • Using temporary：mysql需要创建一张临时表来处理查询。出现这种情况一般是要进行优化的，首先是想到用索引来优化。
  • Using filesort：MySQL 中无法利用索引完成的排序操作称为「文件排序」。在MySQL中的ORDER BY有两种排序实现方式：
    • 利用有序索引获取有序数据，order by后的字段加索引。
    • 文件排序