MySQL执行计划explain的key_len解析

红太狼 2021-09-19 14:46 317阅读 0赞

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

1、整数类型

2.浮点数类型

3、看看时间类型

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

4.字符类型

总结

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

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

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

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

2.字符类型

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让统计更加复杂，并且需要额外的存储空间。这个结论在此得到了证实，复合索引有最左前缀的特性，如果复合索引能全部使用上，则是复合索引字段的索引长度之和，这也可以用来判定复合索引是否部分使用，还是全部使用。

**本文来自云栖社区合作伙伴“DBGEEK”**