MySQL执行计划explain的key_len解析

末蓝、 2022-08-21 11:46 204阅读 0赞

作者 ：沃趣科技高级[数据库][Link 1]专家 邱文辉

**前言**

当用Explain查看SQL的执行计划时，里面有列显示了 key\_len 的值，根据这个值可以判断索引的长度，在组合索引里面可以更清楚的了解到了哪部分字段使用到了索引。下面演示中，表结构的合理性这边暂且不说，只是证明一下索引长度的计算方法。目前大部分博文是字符类型的索引长度计算方法，下面列举几个类型的索引长度计算方法。

**1、整数类型**

![复制代码][copycode.gif]

Query OK, 0 rows affected (0.06 sec)

Records: 0  Duplicates: 0  Warnings: 0

(dg1)root@127.0.0.1 \[mytest\]> desc select \* from table\_key where sid=6;

\+----+-------------+-----------+------+---------------+-----------+---------+-------+------+-------+

\+----+-------------+-----------+------+---------------+-----------+---------+-------+------+-------+

\+----+-------------+-----------+------+---------------+-----------+---------+-------+------+-------+

1 row in set (0.00 sec)

(dg1)root@127.0.0.1 \[mytest\]>

整数类型索引长度跟字段长度有关，如果允许为空，需要额外一个字节去标记为空

![复制代码][copycode.gif]

**2.浮点数类型**

![复制代码][copycode.gif]

![复制代码][copycode.gif]

**3、看看时间类型**

![复制代码][copycode.gif]

看看date类型的索引长度，在NOT NULL和NULL的时候，分别是3和4
    (dg1)root@127.0.0.1 [mytest]> desc select * from table_key1 where c4 = '2015-05-06';
    +----+-------------+------------+------+---------------+----------+---------+-------+------+-----------------------+
    | id | select_type | table      | type | possible_keys | key      | key_len | ref   | rows | Extra                 |
    +----+-------------+------------+------+---------------+----------+---------+-------+------+-----------------------+
    |  1 | SIMPLE      | table_key1 | ref  | c4_index      | c4_index | 3       | const |    4 | Using index condition |
    +----+-------------+------------+------+---------------+----------+---------+-------+------+-----------------------+
    1 row in set, 3 warnings (0.00 sec)
    
    (dg1)root@127.0.0.1 [mytest]> 
    
    (dg1)root@127.0.0.1 [mytest]> alter table table_key1 modify c4 date;
    Query OK, 0 rows affected (0.09 sec)
    Records: 0  Duplicates: 0  Warnings: 0
    
    (dg1)root@127.0.0.1 [mytest]> desc select * from table_key1 where c4 = '2015-05-06';
    +----+-------------+------------+------+---------------+----------+---------+-------+------+-----------------------+
    | id | select_type | table      | type | possible_keys | key      | key_len | ref   | rows | Extra                 |
    +----+-------------+------------+------+---------------+----------+---------+-------+------+-----------------------+
    |  1 | SIMPLE      | table_key1 | ref  | c4_index      | c4_index | 4       | const |    4 | Using index condition |
    +----+-------------+------------+------+---------------+----------+---------+-------+------+-----------------------+
    1 row in set, 3 warnings (0.00 sec)
    
    (dg1)root@127.0.0.1 [mytest]> 
    
    在timestamp类型的时候索引长度，在NOT NULL 和 NULL的时候，分别是4和5
    
    (dg1)root@127.0.0.1 [mytest]> desc select * from table_key1 where c5 = '2015-05-06 11:23:21' ;
    +----+-------------+------------+------+---------------+----------+---------+-------+------+-----------------------+
    | id | select_type | table      | type | possible_keys | key      | key_len | ref   | rows | Extra                 |
    +----+-------------+------------+------+---------------+----------+---------+-------+------+-----------------------+
    |  1 | SIMPLE      | table_key1 | ref  | c5_index      | c5_index | 4       | const |    5 | Using index condition |
    +----+-------------+------------+------+---------------+----------+---------+-------+------+-----------------------+
    1 row in set, 3 warnings (0.00 sec)
    
    (dg1)root@127.0.0.1 [mytest]> 
    
    dg1)root@127.0.0.1 [mytest]> alter table table_key1 modify c5 timestamp ON UPDATE CURRENT_TIMESTAMP ;
    Query OK, 0 rows affected (0.06 sec)
    Records: 0  Duplicates: 0  Warnings: 0
    
    (dg1)root@127.0.0.1 [mytest]> 
    
    
    
    (dg1)root@127.0.0.1 [mytest]> desc select * from table_key1 where c5 = '2015-05-06 110:23:21';
    +----+-------------+------------+------+---------------+----------+---------+-------+------+-----------------------+
    | id | select_type | table      | type | possible_keys | key      | key_len | ref   | rows | Extra                 |
    +----+-------------+------------+------+---------------+----------+---------+-------+------+-----------------------+
    |  1 | SIMPLE      | table_key1 | ref  | c5_index      | c5_index | 5       | const |    5 | Using index condition |
    +----+-------------+------------+------+---------------+----------+---------+-------+------+-----------------------+
    1 row in set, 3 warnings (0.00 sec)
    
    (dg1)root@127.0.0.1 [mytest]> 
    
    ##############################在大家认识里datetime是八个字节的长度，下面就来看看，是不是真的这样
    
    (dg1)root@localhost [mytest]> alter table table_key1 modify c6 datetime not null;
    Query OK, 0 rows affected (0.03 sec)
    Records: 0  Duplicates: 0  Warnings: 0
    
    (dg1)root@localhost [mytest]> desc select * from table_key1 where c6 = '2015-05-06 11:10:36';
    +----+-------------+------------+------+---------------+----------+---------+-------+------+-------+
    | id | select_type | table      | type | possible_keys | key      | key_len | ref   | rows | Extra |
    +----+-------------+------------+------+---------------+----------+---------+-------+------+-------+
    |  1 | SIMPLE      | table_key1 | ref  | c6_index      | c6_index | 5       | const |    1 | NULL  |
    +----+-------------+------------+------+---------------+----------+---------+-------+------+-------+
    1 row in set (0.00 sec)
    
    (dg1)root@localhost [mytest]> alter table table_key1 modify c6 datetime  null;
    Query OK, 0 rows affected (0.06 sec)
    Records: 0  Duplicates: 0  Warnings: 0
    
    (dg1)root@localhost [mytest]> desc select * from table_key1 where c6 = '2015-05-06 11:10:36';
    +----+-------------+------------+------+---------------+----------+---------+-------+------+-------+
    | id | select_type | table      | type | possible_keys | key      | key_len | ref   | rows | Extra |
    +----+-------------+------------+------+---------------+----------+---------+-------+------+-------+
    |  1 | SIMPLE      | table_key1 | ref  | c6_index      | c6_index | 6       | const |    1 | NULL  |
    +----+-------------+------------+------+---------------+----------+---------+-------+------+-------+
    1 row in set (0.00 sec)
    颠覆了我们认识，datetime不是8个字节么，下面来看一下MySQL的版本，没错MySQL5.6是datetime长度是5个字节
    
    (dg1)root@localhost [mytest]> \s
    --------------
    mysql  Ver 14.14 Distrib 5.6.22, for linux-glibc2.5 (x86_64) using  EditLine wrapper
    
    Connection id:        3
    Current database:    mytest
    Current user:        root@localhost
    SSL:            Not in use
    Current pager:        stdout
    Using outfile:        ''
    Using delimiter:    ;
    Server version:        5.6.22-log MySQL Community Server (GPL)
    Protocol version:    10
    Connection:        Localhost via UNIX socket
    Server characterset:    gbk
    Db     characterset:    gbk
    Client characterset:    gbk
    Conn.  characterset:    gbk
    UNIX socket:        /opt/app/mysql/mysql3307.socket
    Uptime:            4 min 47 sec
    
    Threads: 1  Questions: 19  Slow queries: 0  Opens: 75  Flush tables: 1  Open tables: 64  Queries per second avg: 0.066
    --------------
    
    (dg1)root@localhost [mytest]>

![复制代码][copycode.gif]

小结：在MySQL5.6版本，是否还得使用timestamp类型应该是仁者见仁智者见智的问题了，datetime是五个字节，timestamp范围比较窄（1970-2037年），不排除后续版本会修改其范围值

** 4.字符类型**

![复制代码][copycode.gif]

![复制代码][copycode.gif]

![复制代码][copycode.gif]

来看看复合索引的key_len,（刚才测试GBK字符集，字符集转换成GBK了）

(dg1)root@127.0.0.1 [mytest]> alter table table_key drop index name_index;
    Query OK, 0 rows affected (0.01 sec)
    Records: 0  Duplicates: 0  Warnings: 0
    
    (dg1)root@127.0.0.1 [mytest]> alter table table_key drop index address_index;
    Query OK, 0 rows affected (0.01 sec)
    Records: 0  Duplicates: 0  Warnings: 0
    (dg1)root@127.0.0.1 [mytest]> alter table table_key add index name_address_index (name,address);
    Query OK, 0 rows affected (0.02 sec)
    Records: 0  Duplicates: 0  Warnings: 0
    
    
    
    (dg1)root@127.0.0.1 [mytest]> explain extended select * from table_key where address='shanghai' and name='zhangsan';
    +----+-------------+-----------+------+--------------------+--------------------+---------+-------------+------+----------+-----------------------+
    | id | select_type | table     | type | possible_keys      | key                | key_len | ref         | rows | filtered | Extra                 |
    +----+-------------+-----------+------+--------------------+--------------------+---------+-------------+------+----------+-----------------------+
    |  1 | SIMPLE      | table_key | ref  | name_address_index | name_address_index | 42      | const,const |    1 |   100.00 | Using index condition |
    +----+-------------+-----------+------+--------------------+--------------------+---------+-------------+------+----------+-----------------------+
    1 row in set, 1 warning (0.00 sec)
    看看复合索引的长度，因为能全部使用到组合索引，所以是：2*（10）+2*（20）+2=42,下面将name字段允许为空，再来看看
    (dg1)root@127.0.0.1 [mytest]> alter table table_key modify name char(10);
    
    
    (dg1)root@127.0.0.1 [mytest]> explain extended select * from table_key where name='zhangsan' and address='shanghai';
    +----+-------------+-----------+------+--------------------+--------------------+---------+-------------+------+----------+-----------------------+
    | id | select_type | table     | type | possible_keys      | key                | key_len | ref         | rows | filtered | Extra                 |
    +----+-------------+-----------+------+--------------------+--------------------+---------+-------------+------+----------+-----------------------+
    |  1 | SIMPLE      | table_key | ref  | name_address_index | name_address_index | 43      | const,const |    1 |   100.00 | Using index condition |
    +----+-------------+-----------+------+--------------------+--------------------+---------+-------------+------+----------+-----------------------+
    
    1 row in set, 1 warning (0.00 sec)

看看复合索引的长度，因为能全部使用到组合索引，所以是：2*（10）+1+2*（20）+2=43

![复制代码][copycode.gif]

**总结**

1.整数类型，浮点数类型，时间类型的索引长度

NOT NULL=字段本身的字段长度

NULL=字段本身的字段长度+1，因为需要有是否为空的标记，这个标记需要占用1个字节

datetime类型在5.6中字段长度是5个字节

2.字符类型

![复制代码][copycode.gif]

varchr(n)变长字段且允许NULL    =  n * ( utf8=3,gbk=2,latin1=1)+1(NULL)+2
    varchr(n)变长字段且不允许NULL  =  n * ( utf8=3,gbk=2,latin1=1)+2
    
    char(n)固定字段且允许NULL      =  n * ( utf8=3,gbk=2,latin1=1)+1(NULL)
    char(n)固定字段且允许NULL      =  n * ( utf8=3,gbk=2,latin1=1)
    
    变长字段需要额外的2个字节（VARCHAR值保存时只保存需要的字符数，另加一个字节来记录长度(如果列声明的长度超过255，则使用两个字节)，所以VARCAHR索引长度计算时候要加2），固定长度字段不需要额外的字节。而null都需要1个字节的额外空间,所以索引字段最好不要为NULL，因为NULL让统计更加复杂，并且需要额外的存储空间。这个结论在此得到了证实，复合索引有最左前缀的特性，如果复合索引能全部使用上，则是复合索引字段的索引长度之和，这也可以用来判定复合索引是否部分使用，还是全部使用。

![复制代码][copycode.gif]

[Link 1]: http://lib.csdn.net/base/14
[copycode.gif]: /images/20220724/c60e7803e6ce49409b4bd5b89b181f95.png