MySQL执行计划explain的key_len解析

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

1、整数类型

(dg1)root@127.0.0.1 [mytest]> desc table_key;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| id      | int(11)     | NO   |     | NULL    |       |
| sid     | bigint(20)  | NO   |     | 0       |       |
| name    | char(10)    | YES  |     | NULL    |       |
| age     | tinyint(4)  | YES  |     | NULL    |       |
| sex     | tinyint(4)  | NO   |     | NULL    |       |
| address | varchar(10) | YES  | MUL | NULL    |       |
+---------+-------------+------+-----+---------+-------+
rows in set (0.01 sec)

(dg1)root@127.0.0.1 [mytest]>create index age_index on table_key (age);

来看看tinyint类型的索引长度，在NOT NULL 和 NULL 的时候 分别是1和2,tinyint字段长度为1，因为NULL 需要额外一个字节标记为空
(dg1)root@127.0.0.1 [mytest]> explain extended select * from table_key where age=38;
+----+-------------+-----------+------+---------------+-----------+---------+-------+------+----------+-------+
| id | select_type | table     | type | possible_keys | key       | key_len | ref   | rows | filtered | Extra |
+----+-------------+-----------+------+---------------+-----------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | table_key | ref  | age_index     | age_index | 1       | const |    1 |   100.00 | NULL  |
+----+-------------+-----------+------+---------------+-----------+---------+-------+------+----------+-------+
row in set, 1 warning (0.00 sec)

(dg1)root@127.0.0.1 [mytest]> alter table table_key modify age  tinyint(4);

(dg1)root@127.0.0.1 [mytest]> explain extended select * from table_key where age=38;
+----+-------------+-----------+------+---------------+-----------+---------+-------+------+----------+-------+
| id | select_type | table     | type | possible_keys | key       | key_len | ref   | rows | filtered | Extra |
+----+-------------+-----------+------+---------------+-----------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | table_key | ref  | age_index     | age_index | 2       | const |    1 |   100.00 | NULL  |
+----+-------------+-----------+------+---------------+-----------+---------+-------+------+----------+-------+
row in set, 1 warning (0.00 sec)

(dg1)root@127.0.0.1 [mytest]> 
看看bigint类型的索引长度，同样是 NOT NULL 和 NULL值的时候，分别是8和9,聪明的你应该知道了，bigint长度为8。
(dg1)root@127.0.0.1 [mytest]> alter table table_key add key sid_index (sid);
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

(dg1)root@127.0.0.1 [mytest]> desc select * from table_key where sid=6;
+----+-------------+-----------+------+---------------+-----------+---------+-------+------+-------+
| id | select_type | table     | type | possible_keys | key       | key_len | ref   | rows | Extra |
+----+-------------+-----------+------+---------------+-----------+---------+-------+------+-------+
|  1 | SIMPLE      | table_key | ref  | sid_index     | sid_index | 8       | const |    1 | NULL  |
+----+-------------+-----------+------+---------------+-----------+---------+-------+------+-------+
row in set (0.00 sec)

(dg1)root@127.0.0.1 [mytest]> 

(dg1)root@127.0.0.1 [mytest]> alter table table_key modify sid bigint(20);
Query OK, 0 rows affected (0.08 sec)
Records: 0  Duplicates: 0  Warnings: 0

(dg1)root@127.0.0.1 [mytest]> desc select * from table_key where sid=6;
+----+-------------+-----------+------+---------------+-----------+---------+-------+------+-------+
| id | select_type | table     | type | possible_keys | key       | key_len | ref   | rows | Extra |
+----+-------------+-----------+------+---------------+-----------+---------+-------+------+-------+
|  1 | SIMPLE      | table_key | ref  | sid_index     | sid_index | 9       | const |    1 | NULL  |
+----+-------------+-----------+------+---------------+-----------+---------+-------+------+-------+
row in set (0.00 sec)

看看smallint类型索引长度，同样是 NOT NULL 和 NULL值的时候，分别是2和3 smallint长度为2，允许为空需要一个字节标记
(dg1)root@127.0.0.1 [mytest]> alter table table_key modify sid smallint not null default 0;
Query OK, 9 rows affected (0.04 sec)
Records: 9  Duplicates: 0  Warnings: 0

(dg1)root@127.0.0.1 [mytest]> desc select * from table_key where sid=6;
+----+-------------+-----------+------+---------------+-----------+---------+-------+------+-------+
| id | select_type | table     | type | possible_keys | key       | key_len | ref   | rows | Extra |
+----+-------------+-----------+------+---------------+-----------+---------+-------+------+-------+
|  1 | SIMPLE      | table_key | ref  | sid_index     | sid_index | 2       | const |    1 | NULL  |
+----+-------------+-----------+------+---------------+-----------+---------+-------+------+-------+
row in set (0.00 sec)

(dg1)root@127.0.0.1 [mytest]> alter table table_key modify sid smallint ;
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0

(dg1)root@127.0.0.1 [mytest]> desc select * from table_key where sid=6;
+----+-------------+-----------+------+---------------+-----------+---------+-------+------+-------+
| id | select_type | table     | type | possible_keys | key       | key_len | ref   | rows | Extra |
+----+-------------+-----------+------+---------------+-----------+---------+-------+------+-------+
|  1 | SIMPLE      | table_key | ref  | sid_index     | sid_index | 3       | const |    1 | NULL  |
+----+-------------+-----------+------+---------------+-----------+---------+-------+------+-------+
row in set (0.00 sec)

看看mediumint类型索引长度，同样是 NOT NULL 和 NULL值的时候，分别是3和4

(dg1)root@127.0.0.1 [mytest]> alter table table_key modify sid mediumint NOT NULL;
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;
+----+-------------+-----------+------+---------------+-----------+---------+-------+------+-------+
| id | select_type | table     | type | possible_keys | key       | key_len | ref   | rows | Extra |
+----+-------------+-----------+------+---------------+-----------+---------+-------+------+-------+
|  1 | SIMPLE      | table_key | ref  | sid_index     | sid_index | 3       | const |    1 | NULL  |
+----+-------------+-----------+------+---------------+-----------+---------+-------+------+-------+
row in set (0.00 sec)

(dg1)root@127.0.0.1 [mytest]> 

（dg1)root@127.0.0.1 [mytest]> alter table table_key modify sid mediumint ; 
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;
+----+-------------+-----------+------+---------------+-----------+---------+-------+------+-------+ 
| id | select_type | table     | type | possible_keys | key       | key_len | ref   | rows | Extra | 
+----+-------------+-----------+------+---------------+-----------+---------+-------+------+-------+ 
|  1 | SIMPLE      | table_key | ref  | sid_index     | sid_index | 4       | const |    1 | NULL  | 
+----+-------------+-----------+------+---------------+-----------+---------+-------+------+-------+ 
row in set (0.00 sec)
(dg1)root@127.0.0.1 [mytest]> 
整数类型索引长度跟字段长度有关，如果允许为空，需要额外一个字节去标记为空

2.浮点数类型

表结构
CREATE TABLE `table_key1` (
`id`  int NOT NULL AUTO_INCREMENT ,
`c1`  float NOT NULL ,
`c2`  double NOT NULL ,
`c3`  decimal NOT NULL ,
`c4`  date NOT NULL ,
`c5`  timestamp NOT NULL ON UPDATE CURRENT_TIMESTAMP ,
`c6`  datetime NOT NULL ,
PRIMARY KEY (`id`)
)
看看float类型的索引长度，NOT NULL和NULL的时候，分别是4和5
(dg1)root@127.0.0.1 [mytest]> desc select * from table_key1 where c1 = '3.22';
+----+-------------+------------+------+---------------+----------+---------+-------+------+-----------------------+
| id | select_type | table      | type | possible_keys | key      | key_len | ref   | rows | Extra                 |
+----+-------------+------------+------+---------------+----------+---------+-------+------+-----------------------+
|  1 | SIMPLE      | table_key1 | ref  | c1_index      | c1_index | 4       | const |    8 | Using index condition |
+----+-------------+------------+------+---------------+----------+---------+-------+------+-----------------------+
row in set (0.00 sec)

(dg1)root@127.0.0.1 [mytest]> alter table table_key1 modify c1 float;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

(dg1)root@127.0.0.1 [mytest]> desc select * from table_key1 where c1 = '3.22';
+----+-------------+------------+------+---------------+----------+---------+-------+------+-----------------------+
| id | select_type | table      | type | possible_keys | key      | key_len | ref   | rows | Extra                 |
+----+-------------+------------+------+---------------+----------+---------+-------+------+-----------------------+
|  1 | SIMPLE      | table_key1 | ref  | c1_index      | c1_index | 5       | const |    8 | Using index condition |
+----+-------------+------------+------+---------------+----------+---------+-------+------+-----------------------+
row in set (0.00 sec)
看看double类型的索引长度，NOT NULL和NULL的时候，分别是8和9
(dg1)root@127.0.0.1 [mytest]> alter table table_key1 add key c2_index (c2);
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

(dg1)root@127.0.0.1 [mytest]> desc select * from table_key1 where c2 = '3.22';
+----+-------------+------------+------+---------------+----------+---------+-------+------+-------+
| id | select_type | table      | type | possible_keys | key      | key_len | ref   | rows | Extra |
+----+-------------+------------+------+---------------+----------+---------+-------+------+-------+
|  1 | SIMPLE      | table_key1 | ref  | c2_index      | c2_index | 8       | const |    1 | NULL  |
+----+-------------+------------+------+---------------+----------+---------+-------+------+-------+
row in set (0.00 sec)

(dg1)root@127.0.0.1 [mytest]> alter table table_key1 modify c2 double;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

(dg1)root@127.0.0.1 [mytest]> desc select * from table_key1 where c2 = '3.22';
+----+-------------+------------+------+---------------+----------+---------+-------+------+-------+
| id | select_type | table      | type | possible_keys | key      | key_len | ref   | rows | Extra |
+----+-------------+------------+------+---------------+----------+---------+-------+------+-------+
|  1 | SIMPLE      | table_key1 | ref  | c2_index      | c2_index | 9       | const |    1 | NULL  |
+----+-------------+------------+------+---------------+----------+---------+-------+------+-------+
row in set (0.00 sec)

(dg1)root@127.0.0.1 [mytest]>

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.字符类型

表结构，字符集是UTF8

(dg1)root@127.0.0.1 [mytest]> desc table_key;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| id      | int(11)     | NO   |     | NULL    |       |
| sid     | bigint(20)  | NO   |     | 0       |       |
| name    | char(10)    | YES  |     | NULL    |       |
| age     | tinyint(4)  | YES  |     | NULL    |       |
| sex     | tinyint(4)  | NO   |     | NULL    |       |
| address | varchar(10) | YES  | MUL | NULL    |       |
+---------+-------------+------+-----+---------+-------+
rows in set (0.01 sec)

看看定长字符类型char的索引长度，在NOT NULL 和NULL中分别为10*3和10*3+1

(dg1)root@127.0.0.1 [mytest]> alter table table_key add index name_index (name);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

(dg1)root@127.0.0.1 [mytest]> explain extended select * from table_key where name='zhangsan';
+----+-------------+-----------+------+---------------+------------+---------+-------+------+----------+-----------------------+
| id | select_type | table     | type | possible_keys | key        | key_len | ref   | rows | filtered | Extra                 |
+----+-------------+-----------+------+---------------+------------+---------+-------+------+----------+-----------------------+
|  1 | SIMPLE      | table_key | ref  | name_index    | name_index | 30      | const |    1 |   100.00 | Using index condition |
+----+-------------+-----------+------+---------------+------------+---------+-------+------+----------+-----------------------+
row in set, 1 warning (0.00 sec)

(dg1)root@127.0.0.1 [mytest]> 

(dg1)root@127.0.0.1 [mytest]> alter table table_key modify name char(10);
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

(dg1)root@127.0.0.1 [mytest]> explain extended select * from table_key where name='zhangsan';
+----+-------------+-----------+------+---------------+------------+---------+-------+------+----------+-----------------------+
| id | select_type | table     | type | possible_keys | key        | key_len | ref   | rows | filtered | Extra                 |
+----+-------------+-----------+------+---------------+------------+---------+-------+------+----------+-----------------------+
|  1 | SIMPLE      | table_key | ref  | name_index    | name_index | 31      | const |    1 |   100.00 | Using index condition |
+----+-------------+-----------+------+---------------+------------+---------+-------+------+----------+-----------------------+
row in set, 1 warning (0.00 sec)

看看变长长字符类型varchar的索引长度，在NOT NULL 和NULL中分别为10*3+2和10*3+2+1

(dg1)root@127.0.0.1 [mytest]> explain extended select * from table_key where address='shanghai';
+----+-------------+-----------+------+---------------+---------------+---------+-------+------+----------+-----------------------+
| id | select_type | table     | type | possible_keys | key           | key_len | ref   | rows | filtered | Extra                 |
+----+-------------+-----------+------+---------------+---------------+---------+-------+------+----------+-----------------------+
|  1 | SIMPLE      | table_key | ref  | address_index | address_index | 32      | const |    1 |   100.00 | Using index condition |
+----+-------------+-----------+------+---------------+---------------+---------+-------+------+----------+-----------------------+
row in set, 1 warning (0.01 sec)

(dg1)root@127.0.0.1 [mytest]> 

(dg1)root@127.0.0.1 [mytest]> alter table table_key modify address varchar(10);
Query OK, 0 rows affected (0.10 sec)
Records: 0  Duplicates: 0  Warnings: 0

(dg1)root@127.0.0.1 [mytest]> explain extended select * from table_key where address='shanghai';
+----+-------------+-----------+------+---------------+---------------+---------+-------+------+----------+-----------------------+
| id | select_type | table     | type | possible_keys | key           | key_len | ref   | rows | filtered | Extra                 |
+----+-------------+-----------+------+---------------+---------------+---------+-------+------+----------+-----------------------+
|  1 | SIMPLE      | table_key | ref  | address_index | address_index | 33      | const |    1 |   100.00 | Using index condition |
+----+-------------+-----------+------+---------------+---------------+---------+-------+------+----------+-----------------------+
row in set, 1 warning (0.00 sec)

(dg1)root@127.0.0.1 [mytest]>
来看看复合索引的key_len,（刚才测试GBK字符集，字符集转换成GBK了）
(dg1)root@127.0.0.1 [mytest]> desc table_key;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| id      | int(11)     | NO   |     | NULL    |       |
| sid     | bigint(20)  | NO   |     | 0       |       |
| name    | char(10)    | NO   |     |         |       |
| age     | tinyint(4)  | YES  |     | NULL    |       |
| sex     | tinyint(4)  | NO   |     | NULL    |       |
| address | varchar(10) | NO   | MUL |         |       |
+---------+-------------+------+-----+---------+-------+
rows in set (0.01 sec)

(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 |
+----+-------------+-----------+------+--------------------+--------------------+---------+-------------+------+----------+-----------------------+
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 |
+----+-------------+-----------+------+--------------------+--------------------+---------+-------------+------+----------+-----------------------+
row in set, 1 warning (0.00 sec)

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

(dg1)root@127.0.0.1 [mytest]> explain extended select * from table_key where 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 | 21      | const |    1 |   100.00 | Using index condition |
+----+-------------+-----------+------+--------------------+--------------------+---------+-------+------+----------+-----------------------+
row in set, 1 warning (0.00 sec)
那么我们来看看部分使用复合索引：2*（10）+1,将address设置为允许为空，再来看看
(dg1)root@127.0.0.1 [mytest]> alter table table_key modify address varchar(10);
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

(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 | 44      | const,const |    1 |   100.00 | Using index condition |
+----+-------------+-----------+------+--------------------+--------------------+---------+-------------+------+----------+-----------------------+
row in set, 1 warning (0.00 sec)

(dg1)root@127.0.0.1 [mytest]> 
这时候key_len=2*（10）+1+2*（10）+2+1=44

总结

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

时间： 2024-09-23 20:30:36

MySQL执行计划explain的key_len解析的相关文章

MySQL执行计划里面的key_len

  以前看MySQL的执行计划,感觉内容有些简陋,平时分析主要就是看是否全表扫描,索引使用是否合理等.基本上也能分析出很多问题来,但是显然有时候会有些疑惑,那就是对于复合索引,多列值的情况下,到底启用了那些索引列,这个时候索引的使用情况就很值得琢磨琢磨了,我们得根据执行计划里面的key_len做一个重要的参考.    我们做一个简单的测试来说明.    CREATE TABLE `department` ( `DepartmentID` int(11) DEFAULT NULL, `Depart

mysql执行计划介绍_Mysql

烂sql不仅直接影响sql的响应时间,更影响db的性能,导致其它正常的sql响应时间变长.如何写好sql,学会看执行计划至关重要.下面我简单讲讲mysql的执行计划,只列出了一些常见的情况,希望对大家有所帮助. 测试表结构: 复制代码 代码如下: CREATE TABLE `t1` (  `c1` int(11) NOT NULL DEFAULT '0',  `c2` varchar(128) DEFAULT NULL,  `c3` varchar(64) DEFAULT NULL,  `c4`

FAQ系列 | 解读EXPLAIN执行计划中的key_len

导读 EXPLAIN中的key_len一列表示什么意思,该如何解读? EXPLAIN执行计划中有一列 key_len 用于表示本次查询中,所选择的索引长度有多少字节,通常我们可借此判断联合索引有多少列被选择了. 在这里 key_len 大小的计算规则是: 一般地,key_len 等于索引列类型字节长度,例如int类型为4-bytes,bigint为8-bytes: 如果是字符串类型,还需要同时考虑字符集因素,例如:CHAR(30) UTF8则key_len至少是90-bytes: 若该列类型定义

MySQL执行计划extra中的using index 和 using where using index 的区别

原文:MySQL执行计划extra中的using index 和 using where using index 的区别   本文出处:http://www.cnblogs.com/wy123/p/7366486.html (保留出处并非什么原创作品权利,本人拙作还远远达不到,仅仅是为了链接到原文,因为后续对可能存在的一些错误进行修正或补充,无他)   mysql执行计划中的extra列中表明了执行计划的每一步中的实现细节,其中包含了与索引相关的一些细节信息其中跟索引有关的using index

mysql 执行计划优化

一条简单的SQL 语句竟花了15.87 sec, 写道 mysql> SELECT x.loc AS loc, x.lastmod AS lastmod, x.changefreq AS changefreq, x.changecount AS changecount, x.priority AS priority, x.language AS language, x.ac cess AS access, x.status AS status FROM xmlsitemap x WHERE (x

MySQL查询优化之explain的深入解析_Mysql

在分析查询性能时,考虑EXPLAIN关键字同样很管用.EXPLAIN关键字一般放在SELECT查询语句的前面,用于描述MySQL如何执行查询操作.以及MySQL成功返回结果集需要执行的行数.explain 可以帮助我们分析 select 语句,让我们知道查询效率低下的原因,从而改进我们查询,让查询优化器能够更好的工作. 一.MySQL 查询优化器是如何工作的MySQL 查询优化器有几个目标,但是其中最主要的目标是尽可能地使用索引,并且使用最严格的索引来消除尽可能多的数据行.最终目标是提交 SEL

MySQL学习系列2--MySQL执行计划分析EXPLAIN

原文:MySQL学习系列2--MySQL执行计划分析EXPLAIN 1.Explain语法 EXPLAIN SELECT -- 变体:   EXPLAIN EXTENDED SELECT -- 将执行计划"反编译"成SELECT语句,运行SHOW WARNINGS 可得到被MySQL优化器优化后的查询语句   2.执行计划分析和实例 创建员工表Employee create table Employee ( ID int auto_increment, Ename varchar(32

mysql explain 中key_len的计算

今天丁原问我mysql执行计划中的key_len是怎么计算得到的,当时还没有注意,在高性能的那本书讲到过这个值的计算,但是自己看执行计划的时候一直都没有太在意这个值,更不用说深讨这个值的计算了: ken_len表示索引使用的字节数,根据这个值,就可以判断索引使用情况,特别是在组合索引的时候,判断所有的索引字段都被查询用到. 在查看官方文档的时候,也没有发现详细的key_len的计算介绍,后来做了一些测试,在咨询了丁奇关于变长数据类型的值计算的时候,突然想到innodb 行的格式,在这里的计算中有

MySQL 索引管理与执行计划

1.1 索引的介绍 索引是对数据库表中一列或多列的值进行排序的一种结构,使用索引可快速访问数据库表中的特定信息.如果想按特定职员的姓来查找他或她,则与在表中搜索所有的行相比,索引有助于更快地获取信息. 索引的一个主要目的就是加快检索表中数据的方法,亦即能协助信息搜索者尽快的找到符合限制条件的记录ID的辅助数据结构. 1.1.1 唯一索引 唯一索引是不允许其中任何两行具有相同索引值的索引.当现有数据中存在重复的键值时,大多数数据库不允许将新创建的唯一索引与表一起保存.数据库还可能防止添加将在表中创