mysql执行计划key意义_MySQL执行计划explain的key_len解析

最新推荐文章于 2023-01-05 17:15:31 发布

weixin_39723519

最新推荐文章于 2023-01-05 17:15:31 发布

阅读量233

点赞数

文章标签： mysql执行计划key意义

本文链接：https://blog.youkuaiyun.com/weixin_39723519/article/details/113951289

版权

本文详细解析了MySQL中不同数据类型索引长度的计算方法，包括整数、浮点、日期、时间类型及字符类型，还展示了如何根据key_len判断复合索引使用情况。通过实际操作演示，提供整数类型、浮点类型索引长度的计算公式，以及字符类型索引长度的变长和定长区别。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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

1、整数类型

(dg1)root@127.0.0.1 [mytest]>desc table_key;+---------+-------------+------+-----+---------+-------+

+---------+-------------+------+-----+---------+-------+

| id | int(11) | NO | | NULL | |

| sid | bigint(20) | NO | | 0 | |

| age | tinyint(4) | YES | | NULL | |

| sex | tinyint(4) | NO | | NULL | |

+---------+-------------+------+-----+---------+-------+

6 rows in set (0.01sec)

(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;+----+-------------+-----------+------+---------------+-----------+---------+-------+------+----------+-------+

+----+-------------+-----------+------+---------------+-----------+---------+-------+------+----------+-------+

| 1 | SIMPLE | table_key | ref | age_index | age_index |1 | const | 1 | 100.00 | NULL |

+----+-------------+-----------+------+---------------+-----------+---------+-------+------+----------+-------+

1 row in set, 1 warning (0.00sec)

(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;+----+-------------+-----------+------+---------------+-----------+---------+-------+------+----------+-------+

+----+-------------+-----------+------+---------------+-----------+---------+-------+------+----------+-------+

| 1 | SIMPLE | table_key | ref | age_index | age_index | 2 | const | 1 | 100.00 | NULL |

+----+-------------+-----------+------+---------------+-----------+---------+-------+------+----------+-------+

1 row in set, 1 warning (0.00sec)

(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.04sec)

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.00sec)

(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.08sec)

+----+-------------+-----------+------+---------------+-----------+---------+-------+------+-------+

+----+-------------+-----------+------+---------------+-----------+---------+-------+------+-------+

1 row in set (0.00sec)看看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.04sec)

Records:9 Duplicates: 0 Warnings: 0(dg1)root@127.0.0.1 [mytest]> desc select * from table_key where sid=6;+----+-------------+-----------+------+---------------+-----------+---------+-------+------+-------+

+----+-------------+-----------+------+---------------+-----------+---------+-------+------+-------+

+----+-------------+-----------+------+---------------+-----------+---------+-------+------+-------+

1 row in set (0.00sec)

(dg1)root@127.0.0.1 [mytest]>alter table table_key modify sid smallint ;

Query OK,0 rows affected (0.07sec)

+----+-------------+-----------+------+---------------+-----------+---------+-------+------+-------+

+----+-------------+-----------+------+---------------+-----------+---------+-------+------+-------+

1 row in set (0.00sec)看看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.06sec)

+----+-------------+-----------+------+---------------+-----------+---------+-------+------+-------+

+----+-------------+-----------+------+---------------+-----------+---------+-------+------+-------+

1 row in set (0.00sec)

(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;

+----+-------------+-----------+------+---------------+-----------+---------+-------+------+-------+

+----+-------------+-----------+------+---------------+-----------+---------+-------+------+-------+

+----+-------------+-----------+------+---------------+-----------+---------+-------+------+-------+

1 row in set (0.00 sec)

(dg1)root@127.0.0.1 [mytest]>

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

2.浮点数类型

表结构

CREATE TABLE `table_key1` (

`id` intNOT NULL AUTO_INCREMENT ,

`c1`floatNOT NULL ,

`c2`doubleNOT NULL ,

`c3`decimalNOT NULL ,

`c4`dateNOT 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';+----+-------------+------------+------+---------------+----------+---------+-------+------+-----------------------+

+----+-------------+------------+------+---------------+----------+---------+-------+------+-----------------------+

+----+-------------+------------+------+---------------+----------+---------+-------+------+-----------------------+

1 row in set (0.00sec)(dg1)root@127.0.0.1 [mytest]> alter table table_key1 modify c1 float;

Query OK,0 rows affected (0.05sec)

Records:0 Duplicates: 0 Warnings: 0(dg1)root@127.0.0.1 [mytest]> desc select * from table_key1 where c1 = '3.22';+----+-------------+------------+------+---------------+----------+---------+-------+------+-----------------------+

+----+-------------+------------+------+---------------+----------+---------+-------+------+-----------------------+

+----+-------------+------------+------+---------------+----------+---------+-------+------+-----------------------+

1 row in set (0.00sec)

看看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.04sec)

Records:0 Duplicates: 0 Warnings: 0(dg1)root@127.0.0.1 [mytest]> desc select * from table_key1 where c2 = '3.22';+----+-------------+------------+------+---------------+----------+---------+-------+------+-------+

+----+-------------+------------+------+---------------+----------+---------+-------+------+-------+

+----+-------------+------------+------+---------------+----------+---------+-------+------+-------+

1 row in set (0.00sec)(dg1)root@127.0.0.1 [mytest]> alter table table_key1 modify c2 double;

Query OK,0 rows affected (0.03sec)

+----+-------------+------------+------+---------------+----------+---------+-------+------+-------+

+----+-------------+------------+------+---------------+----------+---------+-------+------+-------+

1 row in set (0.00sec)

(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';+----+-------------+------------+------+---------------+----------+---------+-------+------+-----------------------+

+----+-------------+------------+------+---------------+----------+---------+-------+------+-----------------------+

+----+-------------+------------+------+---------------+----------+---------+-------+------+-----------------------+

1 row in set, 3 warnings (0.00sec)

(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.09sec)

Records:0 Duplicates: 0 Warnings: 0(dg1)root@127.0.0.1 [mytest]> desc select * from table_key1 where c4 = '2015-05-06';+----+-------------+------------+------+---------------+----------+---------+-------+------+-----------------------+

+----+-------------+------------+------+---------------+----------+---------+-------+------+-----------------------+

+----+-------------+------------+------+---------------+----------+---------+-------+------+-----------------------+

1 row in set, 3 warnings (0.00sec)

(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';+----+-------------+------------+------+---------------+----------+---------+-------+------+-----------------------+

+----+-------------+------------+------+---------------+----------+---------+-------+------+-----------------------+

+----+-------------+------------+------+---------------+----------+---------+-------+------+-----------------------+

1 row in set, 3 warnings (0.00sec)

(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.06sec)

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';+----+-------------+------------+------+---------------+----------+---------+-------+------+-----------------------+

+----+-------------+------------+------+---------------+----------+---------+-------+------+-----------------------+

+----+-------------+------------+------+---------------+----------+---------+-------+------+-----------------------+

1 row in set, 3 warnings (0.00sec)

(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.03sec)

Records:0 Duplicates: 0 Warnings: 0(dg1)root@localhost [mytest]> desc select * from table_key1 where c6 = '2015-05-06 11:10:36';+----+-------------+------------+------+---------------+----------+---------+-------+------+-------+

+----+-------------+------------+------+---------------+----------+---------+-------+------+-------+

+----+-------------+------------+------+---------------+----------+---------+-------+------+-------+

1 row in set (0.00sec)

(dg1)root@localhost [mytest]> alter table table_key1 modify c6 datetime null;

Query OK,0 rows affected (0.06sec)

+----+-------------+------------+------+---------------+----------+---------+-------+------+-------+

+----+-------------+------------+------+---------------+----------+---------+-------+------+-------+

1 row in set (0.00sec)

颠覆了我们认识，datetime不是8个字节么，下面来看一下MySQL的版本，没错MySQL5.6是datetime长度是5个字节

(dg1)root@localhost [mytest]>\s--------------mysql Ver14.14 Distrib 5.6.22, for linux-glibc2.5(x86_64) using EditLine wrapper

Connectionid: 3Current database: mytest

Current user: root@localhost

SSL: Notinuse

Current pager: stdout

Using outfile:''Using delimiter: ;

Server version:5.6.22-log MySQL Community Server (GPL)

Protocol version:10Connection: 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 47sec

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]>

那么真的是这样么，

(dg1)root@localhost [mytest]>alter table table_key1 change c6 c6 datetime(6) not null ;

(dg1)root@localhost [mytest]>explain select * from table_key1 where c6='2015-05-06 11:13:12.000000';

+----+-------------+------------+------------+------+---------------+----------+---------+-------+------+----------+-------+

+----+-------------+------------+------------+------+---------------+----------+---------+-------+------+----------+-------+

| 1 | SIMPLE | table_key1 | NULL | ref | c6_index | c6_index | 8 | const | 1 | 100.00 | NULL |

+----+-------------+------------+------------+------+---------------+----------+---------+-------+------+----------+-------+

1 row in set, 1 warning (0.00 sec)

小结：在MySQL5.6版本，是否还得使用timestamp类型应该是仁者见仁智者见智的问题了，datetime不存毫秒时候五个字节，因为官方文档说了，5.6.4以上版本，时间和毫秒分开存储了。timestamp范围比较窄(1970-2037年)，不排除后续版本会修改其范围值

4.字符类型

表结构，字符集是UTF8

(dg1)root@127.0.0.1 [mytest]>desc table_key;+---------+-------------+------+-----+---------+-------+

+---------+-------------+------+-----+---------+-------+

| id | int(11) | NO | | NULL | |

| sid | bigint(20) | NO | | 0 | |

| age | tinyint(4) | YES | | NULL | |

| sex | tinyint(4) | NO | | NULL | |

+---------+-------------+------+-----+---------+-------+

6 rows in set (0.01sec)

看看定长字符类型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.01sec)

Records:0 Duplicates: 0 Warnings: 0(dg1)root@127.0.0.1 [mytest]> explain extended select * from table_key where name='zhangsan';+----+-------------+-----------+------+---------------+------------+---------+-------+------+----------+-----------------------+

+----+-------------+-----------+------+---------------+------------+---------+-------+------+----------+-----------------------+

| 1 | SIMPLE | table_key | ref | name_index | name_index | 30 | const | 1 | 100.00 | Using index condition |

+----+-------------+-----------+------+---------------+------------+---------+-------+------+----------+-----------------------+

1 row in set, 1 warning (0.00sec)

(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.05sec)

+----+-------------+-----------+------+---------------+------------+---------+-------+------+----------+-----------------------+

| 1 | SIMPLE | table_key | ref | name_index | name_index | 31 | const | 1 | 100.00 | Using index condition |

+----+-------------+-----------+------+---------------+------------+---------+-------+------+----------+-----------------------+

1 row in set, 1 warning (0.00sec)

看看变长长字符类型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';+----+-------------+-----------+------+---------------+---------------+---------+-------+------+----------+-----------------------+

+----+-------------+-----------+------+---------------+---------------+---------+-------+------+----------+-----------------------+

| 1 | SIMPLE | table_key | ref | address_index | address_index | 32 | const | 1 | 100.00 | Using index condition |

+----+-------------+-----------+------+---------------+---------------+---------+-------+------+----------+-----------------------+

1 row in set, 1 warning (0.01sec)

(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.10sec)

Records:0 Duplicates: 0 Warnings: 0(dg1)root@127.0.0.1 [mytest]> explain extended select * from table_key where address='shanghai';+----+-------------+-----------+------+---------------+---------------+---------+-------+------+----------+-----------------------+

+----+-------------+-----------+------+---------------+---------------+---------+-------+------+----------+-----------------------+

| 1 | SIMPLE | table_key | ref | address_index | address_index | 33 | const | 1 | 100.00 | Using index condition |

+----+-------------+-----------+------+---------------+---------------+---------+-------+------+----------+-----------------------+

1 row in set, 1 warning (0.00sec)

(dg1)root@127.0.0.1 [mytest]>

修改字符集为GBK，再来看看

(dg1)root@127.0.0.1 [mytest]> ALTER TABLE `table_key` DEFAULT CHARACTER SET=gbk COLLATE=gbk_chinese_ci;

(dg1)root@127.0.0.1 [mytest]> ALTER database `mytest` DEFAULT CHARACTER SET=gbk COLLATE=gbk_chinese_ci;

(dg1)root@127.0.0.1 [mytest]> show global variables like '%char%';+--------------------------+--------------------------------+

| Variable_name | Value |

+--------------------------+--------------------------------+

| character_set_client | gbk |

| character_set_connection | gbk |

| character_set_database | gbk |

| character_set_filesystem | binary |

| character_set_results | gbk |

| character_set_server | gbk |

| character_set_system | utf8 |

| character_sets_dir | /opt/app/mysql/share/charsets/ |

+--------------------------+--------------------------------+

8 rows in set (0.00sec)

奇怪了，为什么还是10*3+2+1呢，是因为字段的字符集还没修改过来

(dg1)root@127.0.0.1 [mytest]> explain extended select * from table_key where address='shanghai';+----+-------------+-----------+------+---------------+---------------+---------+-------+------+----------+-----------------------+

+----+-------------+-----------+------+---------------+---------------+---------+-------+------+----------+-----------------------+

| 1 | SIMPLE | table_key | ref | address_index | address_index | 33 | const | 1 | 100.00 | Using index condition |

+----+-------------+-----------+------+---------------+---------------+---------+-------+------+----------+-----------------------+

1 row in set, 1 warning (0.01sec)

(dg1)root@127.0.0.1 [mytest]> ALTER TABLE `table_key` MODIFY COLUMN `name` char(10) CHARACTER SET gbk COLLATE gbk_chinese_ci NULL DEFAULT NULL , DEFAULT CHARACTER SET=gbk COLLATE=gbk_chinese_ci;

Query OK,9 rows affected (0.04sec)

Records:9 Duplicates: 0 Warnings: 0(dg1)root@127.0.0.1 [mytest]> ALTER TABLE `table_key` MODIFY COLUMN `address` char(10) CHARACTER SET gbk COLLATE gbk_chinese_ci NULL DEFAULT NULL , DEFAULT CHARACTER SET=gbk COLLATE=gbk_chinese_ci;

Query OK,9 rows affected (0.03sec)

Records:9 Duplicates: 0 Warnings: 0(dg1)root@127.0.0.1 [mytest]> explain extended select * from table_key where address='shanghai';+----+-------------+-----------+------+---------------+---------------+---------+-------+------+----------+-----------------------+

+----+-------------+-----------+------+---------------+---------------+---------+-------+------+----------+-----------------------+

| 1 | SIMPLE | table_key | ref | address_index | address_index | 21 | const | 1 | 100.00 | Using index condition |

+----+-------------+-----------+------+---------------+---------------+---------+-------+------+----------+-----------------------+

1 row in set, 1 warning (0.00sec)

(dg1)root@127.0.0.1 [mytest]>(dg1)root@127.0.0.1 [mytest]> alter table table_key modify address varchar(10);

Query OK,9 rows affected (0.04sec)

+----+-------------+-----------+------+---------------+---------------+---------+-------+------+----------+-----------------------+

| 1 | SIMPLE | table_key | ref | address_index | address_index | 23 | const | 1 | 100.00 | Using index condition |

+----+-------------+-----------+------+---------------+---------------+---------+-------+------+----------+-----------------------+

1 row in set, 1 warning (0.01sec)

(dg1)root@127.0.0.1 [mytest]>(dg1)root@127.0.0.1 [mytest]> explain extended select * from table_key where name='zhangsan';+----+-------------+-----------+------+---------------+------------+---------+-------+------+----------+-----------------------+

+----+-------------+-----------+------+---------------+------------+---------+-------+------+----------+-----------------------+

| 1 | SIMPLE | table_key | ref | name_index | name_index | 20 | const | 1 | 100.00 | Using index condition |

+----+-------------+-----------+------+---------------+------------+---------+-------+------+----------+-----------------------+

1 row in set, 1 warning (0.00sec)

(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.00sec)

+----+-------------+-----------+------+---------------+------------+---------+-------+------+----------+-----------------------+

| 1 | SIMPLE | table_key | ref | name_index | name_index | 21 | const | 1 | 100.00 | Using index condition |

+----+-------------+-----------+------+---------------+------------+---------+-------+------+----------+-----------------------+

1 row in set, 1 warning (0.01 sec)

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

(dg1)root@127.0.0.1 [mytest]>desc table_key;

+---------+-------------+------+-----+---------+-------+

+---------+-------------+------+-----+---------+-------+

| id | int(11) | NO | | NULL | |

| sid | bigint(20) | NO | | 0 | |

| age | tinyint(4) | YES | | NULL | |

| sex | tinyint(4) | NO | | NULL | |

| address | varchar(10) | NO | MUL | | |

+---------+-------------+------+-----+---------+-------+

6 rows in set (0.01sec)

(dg1)root@127.0.0.1 [mytest]>alter table table_key drop index name_index;

Query OK,0 rows affected (0.01sec)

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.01sec)

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.02sec)

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';+----+-------------+-----------+------+--------------------+--------------------+---------+-------------+------+----------+-----------------------+

+----+-------------+-----------+------+--------------------+--------------------+---------+-------------+------+----------+-----------------------+

| 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.00sec)

看看复合索引的长度，因为能全部使用到组合索引，所以是：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';+----+-------------+-----------+------+--------------------+--------------------+---------+-------------+------+----------+-----------------------+

+----+-------------+-----------+------+--------------------+--------------------+---------+-------------+------+----------+-----------------------+

| 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.00sec)

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

(dg1)root@127.0.0.1 [mytest]> explain extended select * from table_key where name='zhangsan';+----+-------------+-----------+------+--------------------+--------------------+---------+-------+------+----------+-----------------------+

+----+-------------+-----------+------+--------------------+--------------------+---------+-------+------+----------+-----------------------+

| 1 | SIMPLE | table_key | ref | name_address_index | name_address_index | 21 | const | 1 | 100.00 | Using index condition |

+----+-------------+-----------+------+--------------------+--------------------+---------+-------+------+----------+-----------------------+

1 row in set, 1 warning (0.00sec)

那么我们来看看部分使用复合索引：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.04sec)

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';+----+-------------+-----------+------+--------------------+--------------------+---------+-------------+------+----------+-----------------------+

+----+-------------+-----------+------+--------------------+--------------------+---------+-------------+------+----------+-----------------------+

| 1 | SIMPLE | table_key | ref | name_address_index | name_address_index | 44 | const,const | 1 | 100.00 | Using index condition |

+----+-------------+-----------+------+--------------------+--------------------+---------+-------------+------+----------+-----------------------+

1 row in set, 1 warning (0.00sec)

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