MySQL explain Type总结

不念不忘少年蓝@ 2022-09-10 08:30 128阅读 0赞

## explain ##

在调试优化MySQL中，大家都需要使用explain查看SQL执行计划，其中的字段信息在本文中将逐一详述。

## 基本信息 ##

DB： shopping.shool\_info

CREATE TABLE `school_info` (
      `id` bigint NOT NULL AUTO_INCREMENT,
      `name` varchar(30) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '学院名称',
      `student_num` int DEFAULT NULL COMMENT '学生数量',
      `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
      `created_by` varchar(30) COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '创建人',
      `updated_by` varchar(30) COLLATE utf8_unicode_ci NOT NULL COMMENT '更新人',
      `updated_at` datetime NOT NULL COMMENT '更新时间',
      `valid` smallint NOT NULL DEFAULT '0',
      PRIMARY KEY (`id`),
      UNIQUE KEY `idnew_table_UNIQUE` (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

常见的扫描方式:

*  system：系统表，少量数据，往往不需要进行磁盘IO
 *  const：常量连接
 *  eq\_ref：主键索引(primary key)或者非空唯一索引(unique not null)等值扫描
 *  ref：非主键非唯一索引等值扫描
 *  range：范围扫描
 *  index：索引树扫描
 *  ALL：全表扫描(full table scan)

type扫描方式由快到慢

> system > const > eq\_ref > ref > range > index > ALL

## 示例 ##

### const ###

![在这里插入图片描述][a490299176c74ad8bb573810447d4691.png]  
基于主键直接命中，一次就找到数据，其查询效果为const。  
应用场景： 命中PK 或者 unique Key，其type和ref为const

### eq\_ref ###

> explain select \* from school\_info school left join class\_info cls on school.id = cls.school\_id where school.id = cls.school\_id  
> ![在这里插入图片描述][watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBAYmxhZGVzdG9uZQ_size_20_color_FFFFFF_t_70_g_se_x_16]上例中对于前表school\_info表中的每一行（row），对应后class\_info表有2行被扫描.  
> 场景：

1.  联表(join)查询
2.  命中主键(primary key)或者非空唯一索引(unique not null)
3.  等值连接

### eq\_ref ###

新建表：

CREATE TABLE `class_info_index` (
      `id` bigint NOT NULL,
      `school_id` bigint NOT NULL,
      `name` varchar(45) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
      `course_num` int DEFAULT '0',
      `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
      `created_by` varchar(45) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT '',
      `updated_at` datetime DEFAULT CURRENT_TIMESTAMP,
      `updated_by` varchar(45) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT '',
      KEY `id` (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

表只有单独index，没主键。

> explain select \* from school\_info school left join class\_info\_index cls on school.id = cls.school\_id where school.id = cls.school\_id;  
> ![在这里插入图片描述][watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBAYmxhZGVzdG9uZQ_size_20_color_FFFFFF_t_70_g_se_x_16 1]由于后表使用了普通非唯一索引，对于前表的每一行(row)，后表可能有多于一行的数据被扫描.

单表查询：

> explain select \* from class\_info\_index where id = 1;  
> ![在这里插入图片描述][530e20cfbc3c4b1ea4f0a365b6188a8c.png]  
> 当id改为普通非唯一索引后，常量的连接查询，也由const降级为了ref，因为非唯一索引所以有多于一行的数据被可能被扫描

ref每一次匹配可能有多行数据返回，虽然它比eq\_ref要慢，但它仍然是一个很快的join类型

场景： 联表查询 普通非唯一索引

### range ###

between:

> explain select \* from school\_info where id between 3 and 9;  
> ![在这里插入图片描述][0e3efcfbc2ad440bac50c60d29175d0e.png]如果是用非索引字段，在between中，将进行全表扫描。  
> in,>,>=,<,<=  
> explain select \* from school\_info where id >4;  
> ![在这里插入图片描述][11761bbd7097450fbb95b7dad2899005.png]range比较好理解，它是索引上的范围查询，它会在索引上扫码特定范围内的值

### index ###

> explain select count(\*) from school\_info  
> ![在这里插入图片描述][e41a1a62ff0c452e9f036b86cb905b4b.png]index类型，需要扫描索引上的全部数据，它仅比全表扫描快一点

## All ##

> explain select count(\*) from school\_info where student\_num>100;  
> ![在这里插入图片描述][b97f6821f5ba41e197313fa0d8a4931b.png]如果id上不建索引,则全表扫描

## 总结 ##

> system > const > eq\_ref > ref > range > index > ALL

大家注意掌握上述的MySQL索引顺序，效率从高到低。

[a490299176c74ad8bb573810447d4691.png]: /images/20220829/d3a2989c495a4d36be5e66671b617620.png
[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBAYmxhZGVzdG9uZQ_size_20_color_FFFFFF_t_70_g_se_x_16]: /images/20220829/bab65d6d48d5471e89de1567bf236502.png
[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBAYmxhZGVzdG9uZQ_size_20_color_FFFFFF_t_70_g_se_x_16 1]: /images/20220829/bf14fd2ff67f43699a8e2c036c82a424.png
[530e20cfbc3c4b1ea4f0a365b6188a8c.png]: /images/20220829/89aaa82648da47e4a3b1bfc0a28784c0.png
[0e3efcfbc2ad440bac50c60d29175d0e.png]: /images/20220829/d7c15b01878049a3b201eb76ef0a83e8.png
[11761bbd7097450fbb95b7dad2899005.png]: /images/20220829/088b10e8cedc4d57a8bd57467a0f52b9.png
[e41a1a62ff0c452e9f036b86cb905b4b.png]: /images/20220829/11e97557ecd34e3599c415ef9ed78015.png
[b97f6821f5ba41e197313fa0d8a4931b.png]: /images/20220829/dc9e07d3a24548cfbdfeea269228ca2a.png