MySQL执行计划explain的key_len解析

共计 23569 个字符，预计需要花费 59 分钟才能阅读完成。

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

(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]> 
 整数类型索引长度跟字段长度有关，如果允许为空，需要额外一个字节去标记为空

表结构
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]>

看看 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 年），不排除后续版本会修改其范围值

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

本文永久更新链接地址：http://www.linuxidc.com/Linux/2016-06/132168.htm