不是！你不会还不明白MySQL的EXPLAIN用法吧！

超爱吃士力架
189 阅读19分钟

1. 分析查询语句：EXPLAIN

1.1 概述

定位了查询慢的SQL之后，我们就可以使用EXPLAIN或DESCRIBE工具做针对性的分析查询语句。 DESCRIBE语句的使用方法与EXPLAIN语句是一样的，并且分析结果也是一样的。

MySQL中有专门负责优化SELECT语句的优化器模块，主要功能: 通过计算分析系统中收集到的统计信息，为客户端请求的Query提供它认为最优的执行计划（他认为最优的数据检索方式，但不见得是DBA认为是最优的，这部分最耗费时间)。

这个执行计划展示了接下来具体执行查询的方式，比如多表连接的顺序是什么，对于每个表采用什么访问方法来具体执行查询等等。MySQL为我们提供了EXPLAIN语句来帮助我们查看某个查询语句的具体执行计划，大家看懂EXPLAIN语句的各个输出项，可以有针对性的提升我们查询语句的性能。

1.能做什么?

  • 表的读取顺序
  • 数据读取操作的操作类型。
  • 哪些索引可以使用
  • 哪些索引被实际使用
  • 表之间的引用
  • 每张表有多少行被优化器查询

官网介绍

dev.mysql.com/doc/refman/… dev.mysql.com/doc/refman/…

image-20220325194823764

版本情况

  • MySQL 5.6.3以前只能 EXPLAIN SELECT ；MYSQL 5.6.3以后就可以 EXPLAIN SELECT，UPDATE，DELETE
  • 在5.7以前的版本中，想要显示 partitions 需要使用 explain partitions 命令；想要显示filtered 需要使用 explain extended 命令。在5.7版本后，默认explain直接显示partitions和filtered中的信息。

image-20220325195048342

1.2 基本语法

EXPLAIN 或 DESCRIBE语句的语法形式如下：

EXPLAIN SELECT select_options
# 或者 两个是一样的
DESCRIBE SELECT select_options

如果我们想看看某个查询的执行计划的话，可以在具体的查询语句前边加一个 EXPLAIN ，就像这样：

mysql> EXPLAIN SELECT 1;

image-20220326113218648

EXPLAIN 语句输出的各个列的作用如下

列名描述
id在一个大的查询语句中每个SELECT关键字都对应一个 唯一的id
select_typeSELECT关键字对应的那个查询的类型
table表名
partitions匹配的分区信息
type针对单表的访问方法（重要）
possible_keys可能用到的索引
key实际上使用的索引
key_len实际使用到的索引长度
ref当使用索引列等值查询时，与索引列进行等值匹配的对象信息
rows预估的需要读取的记录条数
filtered某个表经过搜索条件过滤后剩余记录条数的百分比
Extra一些额外的信息

1.3 数据准备

1.建表

CREATE TABLE s1 (
    id INT AUTO_INCREMENT,
    key1 VARCHAR(100), 
    key2 INT, 
    key3 VARCHAR(100), 
    key_part1 VARCHAR(100),
    key_part2 VARCHAR(100),
    key_part3 VARCHAR(100),
    common_field VARCHAR(100),
    PRIMARY KEY (id),
    INDEX idx_key1 (key1),
    UNIQUE INDEX idx_key2 (key2),
    INDEX idx_key3 (key3),
    INDEX idx_key_part(key_part1, key_part2, key_part3)
) ENGINE=INNODB CHARSET=utf8;

CREATE TABLE s2 (
    id INT AUTO_INCREMENT,
    key1 VARCHAR(100),
    key2 INT,
    key3 VARCHAR(100),
    key_part1 VARCHAR(100),
    key_part2 VARCHAR(100),
    key_part3 VARCHAR(100),
    common_field VARCHAR(100),
    PRIMARY KEY (id),
    INDEX idx_key1 (key1),
    UNIQUE INDEX idx_key2 (key2),
    INDEX idx_key3 (key3),
    INDEX idx_key_part(key_part1, key_part2, key_part3)
) ENGINE=INNODB CHARSET=utf8;

2. 设置参数 log_bin_trust_function_creators

创建函数，假如报错，需开启如下命令：允许创建函数设置：

set global log_bin_trust_function_creators=1; # 不加global只是当前窗口有效。

3. 创建函数

DELIMITER //
CREATE FUNCTION rand_string1 ( n INT ) 
    RETURNS VARCHAR ( 255 ) #该函数会返回一个字符串
BEGIN
    DECLARE
        chars_str VARCHAR ( 100 ) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';
    DECLARE
        return_str VARCHAR ( 255 ) DEFAULT '';
    DECLARE
        i INT DEFAULT 0;
    WHILE
            i < n DO
            
            SET return_str = CONCAT(
                return_str,
            SUBSTRING( chars_str, FLOOR( 1+RAND ()* 52 ), 1 ));
        
        SET i = i + 1;
        
    END WHILE;
    RETURN return_str;
    
END // 
DELIMITER ;

4. 创建存储过程

创建往s1表中插入数据的存储过程：

DELIMITER //
CREATE PROCEDURE insert_s1 (IN min_num INT (10),IN max_num INT (10))
BEGIN
    DECLARE i INT DEFAULT 0;
    SET autocommit = 0;
    REPEAT
    SET i = i + 1;
    INSERT INTO s1 VALUES(
    (min_num + i),
    rand_string1(6),
    (min_num + 30 * i + 5),
    rand_string1(6),
    rand_string1(10),
    rand_string1(5),
    rand_string1(10),
    rand_string1(10));
    UNTIL i = max_num
    END REPEAT;
    COMMIT;
END //
DELIMITER ;

创建往s2表中插入数据的存储过程：

DELIMITER //
CREATE PROCEDURE insert_s2 (IN min_num INT ( 10 ),IN max_num INT ( 10 )) 
BEGIN
    DECLARE i INT DEFAULT 0;
    SET autocommit = 0;
    REPEAT
    SET i = i + 1;
        INSERT INTO s2 VALUES(
                ( min_num + i ),
                rand_string1 ( 6 ),
                ( min_num + 30 * i + 5 ),
                rand_string1 ( 6 ),
                rand_string1 ( 10 ),
                rand_string1 ( 5 ),
                rand_string1 ( 10 ),
                rand_string1 ( 10 ));
        UNTIL i = max_num 
    END REPEAT;
    COMMIT;
    
END // 
DELIMITER ;

5. 调用存储过程

s1表数据的添加：加入1万条记录：

CALL insert_s1(10001,10000); # id 10002~20001

s2表数据的添加：加入1万条记录：

CALL insert_s2(10001,10000);# id 10002~20001

1.4 EXPLAIN各列作用

为了让大家有比较好的体验，我们调整了下 EXPLAIN 输出列的顺序。

1. table

表名

不论我们的查询语句有多复杂，里边儿 包含了多少个表 ，到最后也是需要对每个表进行 单表访问 的，所 以MySQL规定EXPLAIN语句输出的每条记录都对应着某个单表的访问方法，该条记录的table列代表着该 表的表名（有时不是真实的表名字，可能是简称）

#1. table：表名
#查询的每一行记录都对应着一个单表
explain select count(*) from s1;

image-20220326120805996

#s1:驱动表  s2:被驱动表
EXPLAIN SELECT * FROM s1 INNER JOIN s2;
# 驱动表和被驱动表是 优化器决定的，他认为哪个比较好久用哪个

image-20220326121611806

用到多少个表，就会有多少条记录

2. id

正常来说一个select 一个id ，也有例外的可能，查询优化器做了优化

mysql> EXPLAIN SELECT * FROM s1 WHERE key1 = 'a';

image-20220326122616487

mysql> EXPLAIN SELECT * FROM s1 INNER JOIN s2;

image-20220326122717663

mysql> EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key1 FROM s2) OR key3 = 'a';

image-20220326122751920

查询优化器优化

 ######查询优化器可能对涉及子查询的查询语句进行重写,转变为多表查询的操作########
 EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key2 FROM s2 WHERE common_field = 'a');

运行结果： id 只有一个，原因是查询优化器做了优化

image-20220326122857145

Union去重

原本想的1个select 一个 id , 预计两个。

 #Union去重
# union 去重，union all 不去重
EXPLAIN SELECT * FROM s1 UNION SELECT * FROM s2;

image-20220326123056983

# union all 不去重  所以不需要放在临时表里面
mysql> EXPLAIN SELECT * FROM s1 UNION ALL SELECT * FROM s2;

image-20220326123147690

小结:

  • id如果相同，可以认为是一组，从上往下顺序执行
  • 在所有组中，id值越大，优先级越高，越先执行
  • 关注点：id号每个号码，表示一趟独立的查询, 一个sql的查询趟数越少越好

3. select_type

一条大的查询语句里边可以包含若干个SELECT关键字，每个SELECT关键字代表着一个小的查询语句，而每个SELECT关键字的FROM子句中都可以包含若干张表(这些表用来做连接查询)，每一张表都对应着执行计划输出中的一条记录，对于在同一个SELECT关键字中的表来说，它们的id值是相同的。

MySQL为每一个SELECT关键字代表的小查询都定义了一个称之为select_type的属性，意思是我们只要知道了某个小查询的select_type属性，就知道了这个小查询在整个大查询中扮演了一个什么角色，我们看一下 select_type都能取哪些值，请看官方文档:

名称描述
SIMPLESimple SELECT (not using UNION or subqueries)
PRIMARYOutermost SELECT
UNIONSecond or later SELECT statement in a UNION
UNION RESULTResult of a UNION
SUBQUERYFirst SELECT in subquery
DEPENDENT SUBQUERYFirst SELECT in subquery, dependent on outer query
DEPENDENT UNIONSecond or later SELECT statement in a UNION, dependent on outer query
DERIVEDDerived table
MATERIALIZEDMaterialized subquery
UNCACHEABLE SUBQUERYA subquery for which the result cannot be cached and must be re-evaluated for each row of the outer query
UNCACHEABLE UNIONThe second or later select in a UNION that belongs to an uncacheable subquery (see UNCACHEABLE SUBQUERY)

  • SIMPLE

     # 查询语句中不包含`UNION`或者子查询的查询都算作是`SIMPLE`类型
 EXPLAIN SELECT * FROM s1;
 
  #连接查询也算是`SIMPLE`类型
 EXPLAIN SELECT * FROM s1 INNER JOIN s2;

  • PRIMARY 与 UNION与 UNION RESULT

    • UNION RESULT

      MySQL选择使用临时表来完成UNION查询的去重工作，针对该临时表的查询的select_type就是UNION RESULT，例子上边有。

    #对于包含`UNION`或者`UNION ALL`或者子查询的大查询来说，它是由几个小查询组成的，其中最左边的那个
#查询的`select_type`值就是`PRIMARY`
 
#对于包含`UNION`或者`UNION ALL`的大查询来说，它是由几个小查询组成的，其中除了最左边的那个小查询
#以外，其余的小查询的`select_type`值就是`UNION`
​
#`MySQL`选择使用临时表来完成`UNION`查询的去重工作，针对该临时表的查询的`select_type`就是`UNION RESULT`

    测试sql:

     EXPLAIN SELECT * FROM s1 UNION SELECT * FROM s2;

    image-20220326125611904

    EXPLAIN SELECT * FROM s1 UNION ALL SELECT * FROM s2;

    image-20220326125627303

  • SUBQUERY

    如果包含子查询的查询语句不能够转为对应的semi-join的形式，并且该子查询是不相关子查询，并且查询优化器决定采用将该子查询物化的方案来执行该子查询时，该子查询的第个SELECT 关键字代表的那个查询 的select_type就是 SUBQUERY，比如下边这个查询:

     #子查询：
 #如果包含子查询的查询语句不能够转为对应的`semi-join`的形式，并且该子查询是不相关子查询。
 #该子查询的第一个`SELECT`关键字代表的那个查询的`select_type`就是`SUBQUERY`
 EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key1 FROM s2) OR key3 = 'a';

    image-20220326122751920

  • DEPENDENT SUBQUERY

    dependent subquery

     #如果包含子查询的查询语句不能够转为对应的`semi-join`的形式，并且该子查询是相关子查询，
 #则该子查询的第一个`SELECT`关键字代表的那个查询的`select_type`就是`DEPENDENT SUBQUERY`
 EXPLAIN SELECT * FROM s1 
 WHERE key1 IN (SELECT key1 FROM s2 WHERE s1.key2 = s2.key2) OR key3 = 'a';
 #注意的是，select_type为`DEPENDENT SUBQUERY`的查询可能会被执行多次。

    image-20220326130111650

  • DEPENDENT UNION

     #在包含`UNION`或者`UNION ALL`的大查询中，如果各个小查询都依赖于外层查询的话，那除了
 #最左边的那个小查询之外，其余的小查询的`select_type`的值就是`DEPENDENT UNION`。
 EXPLAIN SELECT * FROM s1 
 WHERE key1 IN (SELECT key1 FROM s2 WHERE key1 = 'a' UNION SELECT key1 FROM s1 WHERE key1 = 'b');
 
 # 这里优化器会重构成exist

    image-20220326130433291

  • DERIVED

    derived : 衍生，派生

     #对于包含`派生表`的查询，该派生表对应的子查询的`select_type`就是`DERIVED`
 EXPLAIN SELECT * 
 FROM (SELECT key1, COUNT(*) AS c FROM s1 GROUP BY key1) AS derived_s1 WHERE c > 1;

    image-20220326141653065

  • MATERIALIZED

    materialized: 英 [məˈtɪəri:əˌlaɪzd] 具体化

    #当查询优化器在执行包含子查询的语句时，选择将子查询物化之后与外层查询进行连接查询时，
#该子查询对应的`select_type`属性就是`MATERIALIZED`
EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key1 FROM s2); #子查询被转为了物化表 
​

    image-20220326142034981

    不理解： 为啥上面的子查询，没有雾化

    EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key1 FROM s2) OR key3 = 'a';
# 这个怎么不物化

  • UNCACHEABLE SUBQUERY

    uncacheable

  • UNCACHEABLE UNION

4. partitions (可略)

  • 代表分区表中的命中情况，非分区表，该项为NULL。一般情况下我们的查询语句的执行计划的partitions列的值都是NULL。
  • dev.mysql.com/doc/refman/…
  • 如果想详细了解，可以如下方式测试。创建分区表：
-- 创建分区表，
-- 按照id分区，id<100 p0分区，其他p1分区
CREATE TABLE user_partitions (
    id INT auto_increment,
    NAME VARCHAR(12),PRIMARY KEY(id))
    PARTITION BY RANGE(id)(
    PARTITION p0 VALUES less than(100),
    PARTITION p1 VALUES less than MAXVALUE
);

DESC SELECT * FROM user_partitions WHERE id>200;

查询id大于200（200>100，p1分区）的记录，查看执行计划，partitions是p1，符合我们的分区规则

image-20220325201510359

5. type ☆

执行计划的一条记录就代表着MySQL对某个表的执行查询时的访问方法，又称"访问类型”，其中的type列就表明了这个访问方法是啥，是较为重要的一个指标。比如，看到type列的值是ref，表明MySQL即将使用ref访问方法来执行对s1表的查询。

完整的访问方法如下： system ， const ， eq_ref ， ref ， fulltext ， ref_or_null ，index_merge ， unique_subquery ， index_subquery ， range ， index ， ALL 。

我们详细解释一下：

  • system

    当表中只有一条记录并且该表使用的存储引擎的统计数据是精确的，比如MyISAM、Memory，那么对该表的访问方法就是system。比方说我们新建一个MyISAM表，并为其插入一条记录:

    mysql> CREATE TABLE t(i int) Engine=MyISAM;
Query OK, 0 rows affected (0.05 sec)
​
mysql> INSERT INTO t VALUES(1);
Query OK, 1 row affected (0.01 sec)

    然后我们看一下查询这个表的执行计划：

    mysql> EXPLAIN SELECT * FROM t;
+----+-------------+-------+------------+--------+
| id | select_type | table | partitions | type   |
+----+-------------+-------+------------+--------+
|  1 | SIMPLE      | t     | NULL       | system |
+----+-------------+-------+------------+--------+
1 row in set, 1 warning (0.00 sec)

    这里如果是 innodb 会变成ALL ， 因为innodb系统不会存条数字段。。MyISAM会存储这么一个字段

  • const

     #当我们根据主键或者唯一二级索引列与常数进行等值匹配时，对单表的访问方法就是`const`
 EXPLAIN SELECT * FROM s1 WHERE id = 10005;
 
 EXPLAIN SELECT * FROM s1 WHERE key2 = '10066';

  • eq_ref

     #在连接查询时，如果被驱动表是通过主键或者唯一二级索引列等值匹配的方式进行访问的
 #（如果该主键或者唯一二级索引是联合索引的话，所有的索引列都必须进行等值比较），则
 #对该被驱动表的访问方法就是`eq_ref`
 EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.id = s2.id;
 

    mysql>  EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.id = s2.id;
+----+------------+------+--------+---------+---------+------------------+------+
| id | select_type| table| type   | key     | key_len | ref              | rows |
+----+------------+------+--------+---------+---------+------------------+------+
|  1 | SIMPLE     | s1   | ALL    | NULL    | NULL    | NULL             | 9895 |
|  1 | SIMPLE     | s2   | eq_ref | PRIMARY | 4       | atguigudb1.s1.id |    1 |
+----+------------+------+--------+---------+---------+------------------+------+
2 rows in set, 1 warning (0.00 sec)

    从执行计划的结果中可以看出，MySQL打算将s2作为驱动表，s1作为被驱动表，重点关注s1的访问 方法是 eq_ref ，表明在访问s1表的时候可以 通过主键的等值匹配 来进行访问。

  • ref

     #当通过普通的二级索引列与常量进行等值匹配时来查询某个表，那么对该表的访问方法就可能是`ref`
 EXPLAIN SELECT * FROM s1 WHERE key1 = 'a';

    执行结果：

    mysql>  EXPLAIN SELECT * FROM s1 WHERE key1 = 'a';
+----+-------+------+---------------+----------+---------+
| id | table | type | possible_keys | key      | key_len |
+----+-------+------+---------------+----------+---------+
|  1 | s1    | ref  | idx_key1      | idx_key1 | 303     |
+----+-------+------+---------------+----------+---------+
1 row in set, 1 warning (0.00 sec)

    tips: 类型相同才可以走索引

    EXPLAIN SELECT * FROM s1 WHERE key2 = 10066;
# 这个是不会走索引的 因为key2 是字符串
# 类型不一样，mysql会加函数，进行隐式转换，一旦加上函数，就不会走索引了。

  • fulltext

    全文索引

  • ref_or_null

     #当对普通二级索引进行等值匹配查询，该索引列的值也可以是`NULL`值时，那么对该表的访问方法
 #就可能是`ref_or_null`
 EXPLAIN SELECT * FROM s1 WHERE key1 = 'a' OR key1 IS NULL;

  • index_merge

     #单表访问方法时在某些场景下可以使用`Intersection`、`Union`、
 #`Sort-Union`这三种索引合并的方式来执行查询
 EXPLAIN SELECT * FROM s1 WHERE key1 = 'a' OR key3 = 'a';

    结果

    mysql>  EXPLAIN SELECT * FROM s1 WHERE key1 = 'a' OR key3 = 'a';
+---+-------+------------+-------------------+--------+-------------------------------+
| id| table | type       | key               | key_len| Extra
+---+-------+------------+-------------------+--------+-------------------------------+
|  1| s1    | index_merge| idx_key1,idx_key3 | 303,303| Using union(idx_key1,idx_key3)|
+---+-------+------------+-------------------+--------+-------------------------------+
1 row in set, 1 warning (0.01 sec)
​

    从执行计划的 type 列的值是 index_merge 就可以看出，MySQL 打算使用索引合并的方式来执行 对 s1 表的查询。

  • unique_subquery

     #`unique_subquery`是针对在一些包含`IN`子查询的查询语句中，如果查询优化器决定将`IN`子查询
 #转换为`EXISTS`子查询，而且子查询可以使用到主键进行等值匹配的话，那么该子查询执行计划的`type`
 #列的值就是`unique_subquery`
 EXPLAIN SELECT * FROM s1 
 WHERE key2 IN (SELECT id FROM s2 WHERE s1.key1 = s2.key1) OR key3 = 'a';

  • index_subquery

    EXPLAIN SELECT * FROM s1 WHERE common_field IN (SELECT key3 FROM s2 where
s1.key1 = s2.key1) OR key3 = 'a';

  • range

    #如果使用索引获取某些`范围区间`的记录，那么就可能使用到`range`访问方法
EXPLAIN SELECT * FROM s1 WHERE key1 IN ('a', 'b', 'c');
​
#同上
EXPLAIN SELECT * FROM s1 WHERE key1 > 'a' AND key1 < 'b';

  • index

    #当我们可以使用索引覆盖，但需要扫描全部的索引记录时，该表的访问方法就是`index`
EXPLAIN SELECT key_part2 FROM s1 WHERE key_part3 = 'a';

    索引覆盖，INDEX idx_key_part(key_part1, key_part2, key_part3） 这3个构成一个复合索引

    key_part3 在复合索引里面，，查询的字段也在索引里面，干脆就直接遍历索引查出数据

    思考： 好处，索引存的数据少，数据少页就少，这样可以减少io。

  • ALL

mysql> EXPLAIN SELECT * FROM s1;

一般来说，这些访问方法中除了All这个访问方法外，其余的访问方法都能用到索引，除了index_merge访问方法外，其余的访问方法都最多只能用到一个索引。

小结:

结果值从最好到最坏依次是：

system > const > eq_ref > ref >

fulltext > ref_or_null > index_merge >unique_subquery > index_subquery > range > 

index > ALL 

SQL 性能优化的目标：至少要达到 range 级别，要求是 ref 级别，最好是 consts级别。（阿里巴巴 开发手册要求）

6. possible_keys和key

在EXPLAIN语句输出的执行计划中， possible_keys列表示在某个查询语句中，对某个表执行单表查询时可能用到的索引有哪些。一般查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询使用。key列表示实际用到的索引有哪些，如果为NULL，则没有使用索引。比方说下边这个查询:

mysql> EXPLAIN SELECT * FROM s1 WHERE key1 > 'z' AND key3 = 'a';

mysql> EXPLAIN SELECT * FROM s1 WHERE key1 > 'z' AND key3 = 'a';
+----+-------------+------+------+-------------------+----------+
| id | select_type | table| type | possible_keys     | key      |
+----+-------------+------+------+-------------------+----------+
|  1 | SIMPLE      | s1   | ref  | idx_key1,idx_key3 | idx_key3 |
+----+-------------+------+------+-------------------+----------+
1 row in set, 1 warning (0.01 sec)

上述执行计划的possible_keys列的值是idx_key1,idx_key3，表示该查询可能使用到idx_key1 , idx_key3两个索引，然后key列的值是idx_key3，表示经过查询优化器计算使用不同索引的成本后，最后决定使用idx_key3

索引只能用一个。所以他要选一个出来用。查看上面 index_merge or 的话 会走索引合并。

7. key_len ☆

  • key_len：实际使用到的索引长度(即：字节数)
  • key_len越小 索引效果越好 这是前面学到的只是，短一点效率更高
  • 但是在联合索引里面，命中一次key_len加一次长度。越长代表精度越高，效果越好
#7. 
EXPLAIN SELECT * FROM s1 WHERE id = 10005;
​
## 结果key_len =4

mysql> EXPLAIN SELECT * FROM s1 WHERE key2 = 10126;
​
## 结果key_len = 5

key2 是int 类型 unique 索引。。因为还可能有一个null值，所以 null占一个字段。4+1 = 5

mysql> EXPLAIN SELECT * FROM s1 WHERE key1 = 'a';
​
## 结果key_len = 303

原因： idx_key_part(key_part1, key_part2, key_part3） 是3个100的字段合起来的。每一个字段可以为空，所以是101*3 = 303

mysql> EXPLAIN SELECT * FROM s1 WHERE key_part1 = 'a';
+----+------------+------+---------------+--------------+---------+-------+------+
| id | select_type| type | possible_keys | key          | key_len | ref   | rows |
+----+------------+------+---------------+--------------+---------+-------+------+
|  1 | SIMPLE     | ref  | idx_key_part  | idx_key_part | 303     | const |    1 |
+----+------------+------+---------------+--------------+---------+-------+------+
1 row in set, 1 warning (0.00 sec)

结果key_len是303

mysql> EXPLAIN SELECT * FROM s1 WHERE key_part1 = 'a' AND key_part2 = 'b';
+----+------------+-----+---------------+--------------+---------+------------
| id | select_type|type | possible_keys | key          | key_len | ref        
+----+------------+-----+---------------+--------------+---------+------------
|  1 | SIMPLE     |ref  | idx_key_part  | idx_key_part | 606     | const,const
+----+------------+-----+---------------+--------------+---------+------------
1 row in set, 1 warning (0.00 sec)

结果key_606

这里命中了两次联合索引，精度更高，效果更好

练习：

key_len的长度计算公式：

varchar(10)变长字段且允许NULL = 10 * ( character set：utf8=3,gbk=2,latin1=1)+1(NULL)+2(变长字段)
​
varchar(10)变长字段且不允许NULL = 10 * ( character set：utf8=3,gbk=2,latin1=1)+2(变长字段)
​
char(10)固定字段且允许NULL = 10 * ( character set：utf8=3,gbk=2,latin1=1)+1(NULL)
​
char(10)固定字段且不允许NULL = 10 * ( character set：utf8=3,gbk=2,latin1=1)

8. ref

# 8. ref：当使用索引列等值查询时，与索引列进行等值匹配的对象信息。
#比如只是一个常数或者是某个列。
 
mysql> EXPLAIN SELECT * FROM s1 WHERE key1 = 'a';
+----+-------------+------+------+---------------+----------+---------+-------+
| id | select_type | table| type | possible_keys | key      | key_len | ref   |
+----+-------------+------+------+---------------+----------+---------+-------+
|  1 | SIMPLE      | s1   | ref  | idx_key1      | idx_key1 | 303     | const |
+----+-------------+------+------+---------------+----------+---------+-------+

类型是type =ref，与const（常量）比较

mysql> EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.id = s2.id;
+---+------+--------+---------------+--------+------------------+-----
| id| table| type   | possible_keys | key    | ref              | rows
+---+------+--------+---------------+--------+------------------+-----
|  1| s1   | ALL    | PRIMARY       | NULL   | NULL             | 9895
|  1| s2   | eq_ref | PRIMARY       | PRIMARY| atguigudb1.s1.id |    1
+---+------+--------+---------------+--------+------------------+-----

类型是type =eq_ref ， 与 atguigudb1.s1.id 比较

mysql> EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s2.key1 = UPPER(s1.key1);                         
+----+------+------+---------------+----------+---------+------+------+----------------------+
| id | table| type | possible_keys | key      | key_len | ref  | rows |Extra                 |
+----+------+------+---------------+----------+---------+------+------+----------------------+
|  1 | s1   | ALL  | NULL          | NULL     | NULL    | NULL | 9895 |NULL                  |
|  1 | s2   | ref  | idx_key1      | idx_key1 | 303     | func |    1 |Using index condition |
+----+------+------+---------------+----------+---------+------+------+----------------------+

与一个方法比较func

剩余的

9.rows 10. filtered

  1. Extra ☆

我们下回再聊！

交流学习

最后，如果这篇文章对你有所启发，请帮忙转发给更多的朋友，让更多人受益！如果你有任何疑问或想法，欢迎随时留言与我讨论，我们一起学习、共同进步。别忘了关注我，我将持续分享更多有趣且实用的技术文章，期待与你的交流！

avatar
文章
阅读
粉丝