ADADADADAD

MySQL执行计划explain的key_len解析[ mysql数据库 ]

mysql数据库时间：2024-12-03 12:12:39

作者：文/会员上传

如何提高mysql查询效率

简介：

作者：沃趣科技高级数据库专家邱文辉
前言当用Explain查看SQL的执行计划时，里面有列显示了 key_len 的值，根据这个值可以判断索引的长度，在组合索引里面可以更清楚的了解到了

以下为本文的正文内容，内容仅供参考！本站为公益性网站，复制本文以及下载DOC文档全部免费。

作者：沃趣科技高级数据库专家邱文辉
前言当用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让统计更加复杂，并且需要额外的存储空间。这个结论在此得到了证实，复合索引有最左前缀的特性，如果复合索引能全部使用上，则是复合索引字段的索引长度之和，这也可以用来判定复合索引是否部分使用，还是全部使用。

MySQL执行计划explain的key_len解析.docx

将本文的Word文档下载到电脑

下载

热门标签: mysqlexplainkey

精品

申请https需要什么条件

MySQL执行计划explain的key_len解析[ mysql数据库 ]

MySQL执行计划explain的key_len解析.docx

精品

热门推荐

大家都在看