欢迎来到JIA的博客

执行计划(EXPLAIN)

 

一、about

MySQL5.7.20
初始数据：world.sql

现在，让我们回想一下SQL的执行过程：

  1. 客户端将SQL发送到mysqld。
  2. 经过连接层。
  3. 在SQL层，会经过、语法、语意检查、权限检查后经过解析器预处理后，产生计划(可能产生多个执行计划)，优化器根据解析器得出的多种执行计划，选择一个最优执行计划，然后执行器执行SQL产生执行结果。
  4. 经过存储引擎层一顿操作后通过专用线程将结果返回给客户端。

而本篇就来研究执行计划，通过执行计划可以了解MySQL选择了什么执行计划来执行SQL，并且SQL的执行过程到此结束，即并不会真正的往下交给执行器去执行；最终的目的还是优化MySQL的性能。

 

而我们这里重点关注查询时的执行计划

-- 基本用法
EXPLAIN SQL语句;

-- 示例
EXPLAIN SELECT * FROM city WHERE id<3;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | city  | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL |    2 |   100.00 | Using where |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

返回中的重要参数：

字段描述备注
id 该SELECT标识符  
select_type 该SELECT类型  
table 输出结果的表  
partitions 匹配的分区  
type 表的连接类型  
possible_keys 查询时可能的索引选择 只是有可能选择的索引，但是也能最后选择的索引不在该字段中
key 实际选择的索引 需要重点了解的
key_len 所选KEY的长度  
ref 列与索引的比较  
rows 表示MySQL认为执行查询必须检查的行数 innodb中是个估算值
filtered 按表条件过滤的行百分比  
Extra 执行情况的附加信息 需要重点了解的

 

这里我们重点掌握KEY和Extra字段。其他的就参考：EXPLAIN Output Format

 

其次，EXPLAIN为SELECT语句中使用到的每个表都返回一行信息，并且按照MySQL在处理语句时读取它们的顺序列出了输出中的表：

 

DESC SELECT city.name,city.population,country.code,country.name
FROM city INNER JOIN country
ON city.countrycode = country.code;
+----+-------------+---------+------------+------+---------------------+-------------+---------+--------------------+------+----------+-------+
| id | select_type | table   | partitions | type | possible_keys       | key         | key_len | ref                | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------------+-------------+---------+--------------------+------+----------+-------+
|  1 | SIMPLE      | country | NULL       | ALL  | PRIMARY             | NULL        | NULL    | NULL               |  239 |   100.00 | NULL  |
|  1 | SIMPLE      | city    | NULL       | ref  | CountryCode,inx_c_p | CountryCode | 3       | world.country.Code |   18 |   100.00 | NULL  |
+----+-------------+---------+------------+------+---------------------+-------------+---------+--------------------+------+----------+-------+
2 rows in set, 1 warning (0.00 sec)

MySQL使用嵌套循环连接方法解析所有连接。这意味着MySQL从第一个表中读取一行，然后在第二个表，第三个表中找到匹配的行，依此类推。处理完所有表后，MySQL通过表列表输出选定的列和回溯，直到找到一个表，其中存在更多匹配的行。从该表中读取下一行，然后继续下一个表。

另外，DESCRIBE和EXPLAIN是同义词，同样可以查看语句的执行计划：

 

-- 下面三条语句是等价的
EXPLAIN SELECT * FROM city WHERE id<3;
DESCRIBE SELECT * FROM city WHERE id<3;
DESC SELECT * FROM city WHERE id<3;

 

 

 二、type

type 的官方全称是join type，意思是"连接类型"，这容易让人误会是联表的连接类型，其实不然，这里的join type事实上是数据库引擎查找表的一种方式，我们这里可以像在《高性能MySQL》中作者称呼join type为访问类型更贴切些。

该type列输出介绍如何联接表，接下来列举常见联接类型，性能从最佳到最差排序：

我们挑重点的说！为了便于理解，这里从性能最差到最佳排序一一说明

 2.1 ALL

all便是所谓的全表扫描了，如果出现了all类型，通常意味着你的SQL处于一种最原始的状态，还有很大的优化空间！

我们来看常见出现all的情况

1.查询条件字段是非索引字段

EXPLAIN SELECT * FROM city WHERE district='henan';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | city  | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 4188 |    10.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

 2. 查询条件中，包含 !=、not in、like

 

-- 注意，以下情况适用于辅助索引
EXPLAIN SELECT * FROM city WHERE countrycode NOT IN ('CHN','USA');
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | city  | NULL       | ALL  | CountryCode   | NULL | NULL    | NULL | 4188 |    82.19 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

EXPLAIN SELECT * FROM city WHERE countrycode != 'CHN';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | city  | NULL       | ALL  | CountryCode   | NULL | NULL    | NULL | 4188 |    88.73 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)


-- 而聚集索引来说，还是会走索引
EXPLAIN SELECT * FROM city WHERE id != 10;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | city  | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL | 2103 |   100.00 | Using where |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)


-- 而针对于like情况，% 放在首位不会走索引，放在后面会走索引
EXPLAIN SELECT * FROM city WHERE countrycode LIKE 'CH%';   -- 走索引
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type  | possible_keys | key         | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | city  | NULL       | range | CountryCode   | CountryCode | 3       | NULL |  397 |   100.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

EXPLAIN SELECT * FROM city WHERE countrycode LIKE '%H%';    -- 不走索引
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | city  | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 4188 |    11.11 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.01 sec)

 

 

2.2 index

index是另一种形式上的all类型，只不过index是全索引扫描(但索引也是建立在全表记录上的)，index根据条件扫描索引然后回表取数据。index和all相比，它们都扫描了全表的数据，而且index要先扫索引再回表取数据，这么一说，还不如all快呢(在同样的工况下)！但是为啥官方说index比all效率高呢？原因(我经过严格的逻辑推理和分析得来的)在于索引是有序的，所以index效率高些，来看排序示例：

 

EXPLAIN SELECT * FROM city ORDER BY id;        -- id 是主键
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------+
|  1 | SIMPLE      | city  | NULL       | index | NULL          | PRIMARY | 4       | NULL | 4188 |   100.00 | NULL  |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

EXPLAIN SELECT * FROM city ORDER BY population;    -- 普通字段
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra          |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
|  1 | SIMPLE      | city  | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 4188 |   100.00 | Using filesort |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
1 row in set, 1 warning (0.00 sec)

 

 

可以看到根据population排序走的是ALL访问类型，但Extra字段说使用Using filesort;而根据主键id排序的访问类型是index，并且Extra字段是NULL，即没有额外的排序，所以这可能就是官方说index比all性能好的原因。

还有一种情况需要我们注意，那就是Extra字段是Using index，并且type是index：

 

EXPLAIN SELECT id FROM city;
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key         | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | city  | NULL       | index | NULL          | CountryCode | 3       | NULL | 4188 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

 

、

如上这种情况我们称为"索引覆盖"，那什么是"索引覆盖"？举个例子：我们从字典(一张表)的目录(索引)中找到所有的字，而无需查每个字的实际位置，因为我们想要的数据都在索引中，这就是"索引覆盖"，稍微正式点的解释：只需要在一棵索引树上就能获取SQL所需的所有列数据，无需回表，速度更快

2.3 range

range是基于索引的范围扫描，包含>,<,>=,<=,!=,like,in，not in,or,!=，not in的情况会走range；

出现range的条件是：查询条件列是非PRIMARY KEY和UNIUQE KEY的索引列，也就是说条件列使用了索引，但该索引列的值并不是唯一的，这样的话，即使很快的找到了第一条数据，但仍然不能停止的在指定的范围内继续找。

range的优点是不需要扫描全表，因为索引是有序的，即使有重复值，但也被限定在指定的范围内。

-- 首先为 population 字段建立一个普通索引，现在 population 和 countrycode 是普通索引，而 id 是主键
ALTER TABLE city ADD INDEX idx_p(population);

-- 示例
EXPLAIN SELECT * FROM city WHERE population < 100000;    -- 走索引的范围查找
EXPLAIN SELECT * FROM city WHERE countrycode LIKE 'CH%';  -- 因为索引是有序的，也走索引的范围查找

 

 

注意，上面前两个例子可以享受到B+树带来的查询优势(查询连续)；而下面的例子是无法享受的(查询不连续)：

EXPLAIN SELECT * FROM city WHERE countrycode IN ('CHN','USA');
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type  | possible_keys | key         | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | city  | NULL       | range | CountryCode   | CountryCode | 3       | NULL |  637 |   100.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.01 sec)

注意，上面前两个例子可以享受到B+树带来的查询优势(查询连续)；而下面的例子是无法享受的(查询不连续)：

EXPLAIN SELECT * FROM city WHERE countrycode IN ('CHN','USA');
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type  | possible_keys | key         | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | city  | NULL       | range | CountryCode   | CountryCode | 3       | NULL |  637 |   100.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.01 sec)

对此，我们可以做些优化：

 

EXPLAIN SELECT * FROM city WHERE countrycode='CHN'
UNION ALL
SELECT * FROM city WHERE countrycode='USA';
+----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key         | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
|  1 | PRIMARY     | city  | NULL       | ref  | CountryCode   | CountryCode | 3       | const |  363 |   100.00 | NULL  |
|  2 | UNION       | city  | NULL       | ref  | CountryCode   | CountryCode | 3       | const |  274 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
2 rows in set, 1 warning (0.00 sec)

 

 

2.4 ref

ref出现的条件是： 查找条件列使用了索引但不是PRIMARY KEY和UNIQUE KEY。其意思就是虽然使用了索引，但该索引列的值并不唯一，有重复。这样即使使用索引快速查找到了第一条数据，仍然不能停止，要进行目标值附近的小范围扫描。但它的好处是它并不需要扫全表，因为索引是有序的，即便有重复值，也是在一个非常小的范围内扫描。

EXPLAIN SELECT * FROM city WHERE countrycode='CHN';
+----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key         | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | city  | NULL       | ref  | CountryCode   | CountryCode | 3       | const |  363 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.01 sec)

2.6 eq_ref

在多表连接时，连接条件(ON)使用了唯一索引(UNIQUE NOT NULL,PRIMARY KEY)时，走eq_ref

-- 查询世界上人口小于100人的城市名字
EXPLAIN SELECT city.name,city.population,country.code,country.name
FROM city INNER JOIN country
ON city.countrycode = country.code  -- country表的code字段是 pk
WHERE city.population<100;
+----+-------------+---------+------------+--------+---------------+---------+---------+------------------------+------+----------+-------------+
| id | select_type | table   | partitions | type   | possible_keys | key     | key_len | ref                    | rows | filtered | Extra       |
+----+-------------+---------+------------+--------+---------------+---------+---------+------------------------+------+----------+-------------+
|  1 | SIMPLE      | city    | NULL       | ALL    | CountryCode   | NULL    | NULL    | NULL                   | 4188 |    33.33 | Using where |
|  1 | SIMPLE      | country | NULL       | eq_ref | PRIMARY       | PRIMARY | 3       | world.city.CountryCode |    1 |   100.00 | NULL        |
+----+-------------+---------+------------+--------+---------------+---------+---------+------------------------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)

 

 

这里如果使用的单表来测试eq_ref，经常会出现const，后来想想是对的！UNIQUE NOT NULL和PRIMARY KEY都具有唯一性，而匹配结果是唯一的可不就是const么！所以eq_ref多出现在联表查询中，因为联接条件通常都是具有唯一性或者主键！

除了 system和 const类型，eq_ref是最好的联接类型。 

2.7 const,system

首先，system是最优的，它的意思是表只有一行(但效果我没演示出来)，是const类型的一种特例，所以就把这两个列一块了

EXPLAIN SELECT * FROM city WHERE id=3;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | city  | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

 

const表示：该表最多具有一个匹配行，该行在查询开始时读取。因为又只有一行，所以优化器的其余部分可以将这一行中的列的值视为一个常量。

我们将const看作是最快的，因为只有一行匹配结果。

三、key_len

执行计划中的key_len字段计算的是使用到的索引的长度，通过ken_len可以帮助我们进一步确定索引的使用情况。
这里只列出我们最常用的int、char、varchar三种数据类型，先来复习一些基础知识：

  • char和varchar是日常使用最多的字符类型。char(N)用于保存固定长度的字符串，长度最大为255，比指定长度大的值将被截短，而比指定长度小的值将会用空格进行填补。
  • varchar(N)用于保存可以变长的字符串，长度最大为65535，只存储字符串实际实际需要的长度（它会增加一个额外字节来存储字符串本身的长度），varchar使用额外的1~2字节来存储值的的长度，如果列的最大长度小于或者等于255，则用1字节，否则用2字节。

char和varchar跟字符编码也有密切的联系，latin1占用1个字节，gbk占用2个字节，utf8占用3个字节。
不同字符编码占用的存储空间不同：

Latinl编码(一个字符1个字节)：

 

值char(4)存储的字节varchar(4)存储的字节
'' ' ' 4 '' 1
'ab' 'ab ' 4 'ab' 3
'abcd' 'abcd' 4 'abcd' 5
'abcdefgh' 'abcd' 4 'abcd' 5

 

GBK编码(一个字符2个字节)：

值char(4)存储的字节varchar(4)存储的字节
'' ' ' 8 '' 1
'ab' 'ab ' 8 'ab' 5
'abcd' 'abcd' 8 'abcd' 9
'abcdefgh' 'abcd' 8 'abcd' 9

UTF8编码(一个字符3个字节)：

值char(4)存储的字节varchar(4)存储的字节
'' ' ' 12 '' 1
'ab' 'ab ' 12 'ab' 7
'abcd' 'abcd' 12 'abcd' 13
'abcdefgh' 'abcd' 12 'abcd' 13

接下来，实验开始。
有表结构如下：

 

create table k1(
    id int not null primary key auto_increment,
    a int,
    b int not null,
    c char(10),
    d char(10) not null,
    e varchar(10),
    f varchar(10) not null
)engine=innodb charset=utf8;

 

我们定义的k1表，但没有添加数据，这没关系。

首先来看int的索引长度怎么算出来的：

-- 创建索引
alter table k1 add index(a);
alter table k1 add index(b);

-- key_len的长度是5，对于字段a来说，int类型的索引本身占4个字节，且a字段又允许为空，所以再加1个字节的存储标志位，是否允许为空，加一起，正好是5个字节
explain select * from k1 where a=1;
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | k1    | NULL       | ref  | a             | a    | 5       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+

-- 对于字段b来说，int类型的索引本身占4个字节，但a字段不允许为空，也就不用加1个字节的标志位了，所以ken_len是4
explain select * from k1 where b=1;
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | k1    | NULL       | ref  | b             | b    | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+

-- 再来看在联合索引中的使用情况
alter table k1 add index mul_idx_a_b(a, b);
explain select * from k1 where a=1 and b=1;  -- 使用的是联合索引，a是5字节，b是4字节，加一起是9个字节
+----+-------------+-------+------------+------+---------------+---------+---------+-------------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key     | key_len | ref         | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+---------+---------+-------------+------+----------+-------+
|  1 | SIMPLE      | k1    | NULL       | ref  | mul_idx_a_b,a,b   | mul_idx_a_b| 9       | const,const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+---------+---------+-------------+------+----------+-------+

 

在来看char的索引长度怎么算出来的：

-- 创建索引
alter table k1 add index(c);
alter table k1 add index(d);
alter table k1 add index mul_idx_c_d(c, d);

-- 字段c是char类型，1个字符用3个字节表示，char(10) * 3 = 30字节，且c字段又允许为空，所以再加1个字节的存储标志位，是否允许为空，加一起，正好是31个字节
explain select * from k1 where c='a';
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | k1    | NULL       | ref  | c,mul_idx_c_d | c    | 31      | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+

-- 字段d是char类型，1个字符用3个字节表示，char(10) * 3 = 30字节，但d字段不允许为空，也就不用加1个字节的标志位了，所以ken_len是30
explain select * from k1 where d='a';
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | k1    | NULL       | ref  | d             | d    | 30      | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+

-- 来看联合索引的使用情况，当然，首先要删除两个普通索引，因为MySQL对于下面的查询示例来说，会优先选择普通索引
drop index c on k1;
drop index d on k1;
explain select * from k1 where c='a' and d='a';  -- c + d = 61
+----+-------------+-------+------------+------+---------------+-------------+---------+-------------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key         | key_len | ref         | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+-------------+---------+-------------+------+----------+-------+
|  1 | SIMPLE      | k1    | NULL       | ref  | mul_idx_c_d   | mul_idx_c_d | 61      | const,const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+-------------+---------+-------------+------+----------+-------+

在来看varchar的索引长度怎么算出来的：

-- 创建索引
alter table k1 add index(e);
alter table k1 add index(f);
alter table k1 add index mul_idx_e_f(e, f);

-- 字段e是varchar类型，varchar类型需要额外的1~2个字节存储字符本身的长度，这里取2。是否允许为空又占1个字节，所以是 10 * 3 + 2 + 1 = 33
explain select * from k1 where e='a';
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | k1    | NULL       | ref  | e,mul_idx_e_f | e    | 33      | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+

-- 字段f是varchar类型，但不允许为空，所以，10 * 3 + 2 = 32
explain select * from k1 where f='a';
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | k1    | NULL       | ref  | f             | f    | 32      | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+

-- 来看联合索引的使用情况，当然，首先要删除两个普通索引，因为MySQL对于下面的查询示例来说，会优先选择普通索引
drop index e on k1;
drop index f on k1;
explain select * from k1 where e='a' and f='a';  -- 字段e的33 + 字段f的32 = 65
+----+-------------+-------+------------+------+---------------+-------------+---------+-------------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key         | key_len | ref         | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+-------------+---------+-------------+------+----------+-------+
|  1 | SIMPLE      | k1    | NULL       | ref  | mul_idx_e_f   | mul_idx_e_f | 65      | const,const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+-------------+---------+-------------+------+----------+-------+

 

posted @ 2021-05-11 23:07  讷言敏行~  阅读(518)  评论(0)  编辑  收藏  举报