再次研究 MySQL EXPLAIN type列的解释和测试

原创

天将降大任于斯人也 2022-05-05 13:45:41 ©著作权

文章标签 ico 主键 mysql 文章分类 运维

©著作权归作者所有：来自51CTO博客作者天将降大任于斯人也的原创作品，请联系作者获取转载授权，否则将追究法律责任

type列其实很关键。解释如下：

type列

这一列表示关联类型或访问类型，即MySQL决定如何查找表中的行。

依次从最优到最差分别为：system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

NULL：mysql能够在优化阶段分解查询语句，在执行阶段用不着再访问表或索引。例如：在索引列中选取最小值，可以单独查找索引来完成，不需要在执行时访问表

const, system：mysql能对查询的某部分进行优化并将其转化成一个常量（可以看show warnings 的结果）。用于 primary key 或 unique key 的所有列与常数比较时，所以表最多有一个匹配行，读取1次，速度比较快。

eq_ref：primary key 或 unique key 索引的所有部分被连接使用，最多只会返回一条符合条件的记录。这可能是在 const 之外最好的联接类型了，简单的 select 查询不会出现这种 type。

ref：相比 eq_ref，不使用唯一索引，而是使用普通索引或者唯一性索引的部分前缀，索引要和某个值相比较，可能会找到多个符合条件的行。

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

index_merge：表示使用了索引合并的优化方法。例如下表：id是主键，tenant_id是普通索引。or 的时候没有用 primary key，而是使用了 primary key(id) 和 tenant_id 索引

range：范围扫描通常出现在 in(), between ,> ,<, >= 等操作中。使用一个索引来检索给定范围的行。

index：和ALL一样，不同就是mysql只需扫描索引树，这通常比ALL快一些。

测试一下：

注意： actor 有主键id，无其他索引， actor_no_key 无主键，有一个唯一索引：addresss ，无其他索引，

CREATE TABLE `actor` (
  `id` int(11) NOT NULL,
  `name` varchar(45) DEFAULT NULL,
  `update_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `actor_no_key` (
  `id` int(11) NOT NULL,
  `name` varchar(45) DEFAULT NULL,
  `addresss` varchar(45) DEFAULT NULL,
  `update_time` datetime DEFAULT NULL,
  UNIQUE KEY `index_addresss` (`addresss`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

然后分别随意插入3行数据。

EXPLAIN SELECT COUNT(1) FROM actor;

EXPLAIN SELECT COUNT(*) FROM actor;

EXPLAIN SELECT COUNT(*) FROM actor WHERE id > 1;

EXPLAIN SELECT COUNT(name) FROM actor;

EXPLAIN SELECT COUNT(name) FROM actor_no_key;

EXPLAIN SELECT COUNT(1) FROM actor_no_key;

EXPLAIN SELECT COUNT(id) FROM actor_no_key;

EXPLAIN SELECT COUNT(*) FROM actor_no_key;

EXPLAIN SELECT COUNT(name) FROM actor_no_key WHERE id > 0;

EXPLAIN SELECT COUNT(addresss) FROM actor_no_key WHERE id > 1;

EXPLAIN SELECT COUNT(1) FROM actor_no_key;

结果是：

EXPLAIN SELECT COUNT(1) FROM actor; 走 PRIMARY 索引： Using index

再次研究 MySQL EXPLAIN type列的解释和测试_ico

—— 为什么 possible_keys 为空？

EXPLAIN SELECT COUNT(*) FROM actor; 同上

EXPLAIN SELECT COUNT(*) FROM actor WHERE id > 1; 基本同上，Extra 是 Using where; Using index

再次研究 MySQL EXPLAIN type列的解释和测试_mysql_02

EXPLAIN SELECT COUNT(name) FROM actor; 因为 name上面没有索引，所以是走全表扫描。

再次研究 MySQL EXPLAIN type列的解释和测试_ico_03

EXPLAIN SELECT COUNT(name) FROM actor_no_key; 同上，因为 name上面没有索引，所以是走全表扫描。

再次研究 MySQL EXPLAIN type列的解释和测试_ico_04

EXPLAIN SELECT COUNT(1) FROM actor_no_key; 因为addresss字段存在唯一索引 index_addresss，所以是 Using index，不过 key_len 是 138 , 有点长..

再次研究 MySQL EXPLAIN type列的解释和测试_mysql_05

EXPLAIN SELECT COUNT(id) FROM actor_no_key; 因为 id 上面没有索引，所以是走全表扫描。

再次研究 MySQL EXPLAIN type列的解释和测试_mysql_06

EXPLAIN SELECT COUNT(*) FROM actor_no_key; 同EXPLAIN SELECT COUNT(1) FROM actor_no_key;

再次研究 MySQL EXPLAIN type列的解释和测试_主键_07

EXPLAIN SELECT COUNT(name) FROM actor_no_key WHERE id > 0; 因为 id 上面没有索引，所以是走回表过滤（where 条件就相当于是过滤条件），（过滤和扫描是什么区别？可以认为过滤前需要扫描一下）。

再次研究 MySQL EXPLAIN type列的解释和测试_ico_08

注意 filtered 是 33.33 ，为什么其他的都是 100 ？可能是因为数据只有三行的原因，然后因为 1/3 = 33.33 % ？但是新增了几行，发行还是33.33 ..

EXPLAIN SELECT COUNT(addresss) FROM actor_no_key WHERE id > 1; 同上

EXPLAIN SELECT COUNT(1) FROM actor_no_key; 同EXPLAIN SELECT COUNT(1) FROM actor_no_key;

再次研究 MySQL EXPLAIN type列的解释和测试_mysql_09

license_record2 表有 12503260 行数据， --

实际是12696243

再次研究 MySQL EXPLAIN type列的解释和测试_ico_10

CREATE TABLE `license_record2` (

`id` bigint(20) NOT NULL COMMENT '主键',

`created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',

`created_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL COMMENT '创建人',

`updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '更新时间',

`updated_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL COMMENT '更新人',

`app_id` varchar(20) COLLATE utf8_unicode_ci NOT NULL COMMENT 'APP_ID',

`device_id` varchar(255) COLLATE utf8_unicode_ci NOT NULL COMMENT 'DEVICE_ID',

`model_id` varchar(50) COLLATE utf8_unicode_ci NOT NULL COMMENT 'model_id',

`manufacturer_id` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '制造商ID',

`user_id` varchar(60) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'user_id',

`brand_name` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '包名-',

`status` varchar(64) COLLATE utf8_unicode_ci NOT NULL COMMENT '状态',

`expire_date` datetime DEFAULT NULL COMMENT '到期时间',

`deleted` tinyint(4) NOT NULL DEFAULT '0' COMMENT '是否删除',

`status_code` tinyint(4) NOT NULL DEFAULT '1' COMMENT '授权状态，0、失败1、成功2、撤回',

`ip_address` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'ip地址',

`sign` varchar(2) COLLATE utf8_unicode_ci DEFAULT NULL,

PRIMARY KEY (`id`) USING BTREE,

KEY `app_id` (`app_id`,`device_id`,`manufacturer_id`,`model_id`,`brand_name`,`status_code`) USING BTREE

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ROW_FORMAT=DYNAMIC;

注意索引： `app_id`, `device_id`, `manufacturer_id`, `model_id`, `brand_name`, `status_code`

EXPLAIN SELECT * FROM lic_s_0.`license_record2` WHERE app_id = '20210525104140854' and device_id = '00000000-0691-d628-0000-00004394fc4f' and manufacturer_id = 'vivo' and model_id = 'PA2170' and status_code = 1 ORDER BY updated_at desc limit 1 ;

再次研究 MySQL EXPLAIN type列的解释和测试_mysql_11

可以看到const,const,const,const, 竟然有四个 const！ Extra 是Using index condition; Using filesort，为什么是Using index condition; 而不是 Using index ？因为SELECT * 并不能走覆盖索引。为什么Using filesort ？因为有 order by。。

虽然 limit 1 ，但是还是进行了排序。 why ，因为 limit 语句必须在 order by 后面执行，所以，

继续

EXPLAIN SELECT app_id FROM `license_record2` WHERE app_id = '20210525104140854' and device_id = '00000000-0691-d628-0000-00004394fc4f' and manufacturer_id = 'vivo' and model_id = 'PA2170' and brand_name = 'huawei' and status_code = 1 ;

EXPLAIN SELECT app_id FROM `license_record2` WHERE app_id = '20210525104140854' and device_id = '00000000-0691-d628-0000-00004394fc4f' and manufacturer_id = 'vivo' and model_id = 'PA2170' ;

EXPLAIN SELECT app_id FROM `license_record2` WHERE app_id = '20210525104140854' and device_id = '00000000-0691-d628-0000-00004394fc4f' and manufacturer_id = 'vivo' ;

EXPLAIN SELECT app_id FROM `license_record2` WHERE app_id = '20210525104140854' and device_id = '00000000-0691-d628-0000-00004394fc4f' ;

EXPLAIN SELECT app_id FROM `license_record2` WHERE app_id = '20210525104140854' ;

EXPLAIN SELECT app_id FROM `license_record2` where device_id = '00000000-016a-76e5-0000-000052c2195a' and status_code = 2 limit 1 ;

EXPLAIN SELECT app_id FROM `license_record2` where status_code = 2 limit 1

发现

再次研究 MySQL EXPLAIN type列的解释和测试_主键_12

竟然提示没有匹配的row 即行

EXPLAIN SELECT app_id FROM `license_record2` WHERE app_id = '20210525104140854' and device_id = '00000000-0691-d628-0000-00004394fc4f' and manufacturer_id = 'vivo' and model_id = 'PA2170' ;

再次研究 MySQL EXPLAIN type列的解释和测试_mysql_13

直接走了 index索引，ref 为4个const

EXPLAIN SELECT app_id FROM `license_record2` WHERE app_id = '20210525104140854' and device_id = '00000000-0691-d628-0000-00004394fc4f' and manufacturer_id = 'vivo' ;

再次研究 MySQL EXPLAIN type列的解释和测试_ico_14