MySQL執行計劃explain的key_len解析

沃趣科技發表於2016-05-10

作者：沃趣科技高階專家邱文輝

前言

當用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讓統計更加複雜，並且需要額外的儲存空間。這個結論在此得到了證實，複合索引有最左字首的特性，如果複合索引能全部使用上，則是複合索引欄位的索引長度之和，這也可以用來判定複合索引是否部分使用，還是全部使用。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/28218939/viewspace-2097246/，如需轉載，請註明出處，否則將追究法律責任。

相關文章

explain執行計劃中的key_len的計算規則
2017-04-26
AI
FAQ系列|解讀EXPLAIN執行計劃中的key_len
2017-08-02
AI
mysql執行計劃explain
2020-10-23
MySqlAI
mysql explain 執行計劃
2023-12-04
MySqlAI
MySQL執行計劃explain輸出列結果解析
2016-10-19
MySqlAI
MySQL執行計劃裡面的key_len
2017-09-26
MySql
MySQL Explain執行計劃 - 詳解
2021-09-25
MySqlAI
十六、Mysql之Explain執行計劃
2018-12-14
MySqlAI
mysql explain 執行計劃詳解
2019-01-06
MySqlAI
MySQL執行計劃EXPLAIN詳解
2017-12-15
MySqlAI
mysql調優之——執行計劃explain
2019-03-24
MySqlAI
(4) MySQL中EXPLAIN執行計劃分析
2019-01-08
MySqlAI
MySQL——通過EXPLAIN分析SQL的執行計劃
2018-08-03
MySqlAI
MySQL執行計劃解析
2018-06-19
MySql
MySQL explain執行計劃詳細解釋
2021-09-09
MySqlAI
mysql之 explain、optimizer_trace 執行計劃
2017-06-27
MySqlAI
explain執行計劃分析
2020-11-14
AI
oracle執行計劃的使用(EXPLAIN)
2014-07-24
OracleAI
MySQL執行計劃解析（四）
2020-05-09
MySql
Explain執行計劃詳解
2022-01-24
AI
explain 查詢執行計劃
2022-03-23
AI
在MySQL中使用explain查詢SQL的執行計劃
2021-09-09
MySqlAI
MySQL explain 中 key_len的詳解
2020-04-07
MySqlAI
MySQL調優篇 | EXPLAIN執行計劃解讀（4）
2024-02-01
MySqlAI
ORACLE執行計劃 explain說明
2016-01-07
OracleAI
MySQL EXPLAIN命令詳解學習（檢視執行計劃）
2016-09-16
MySqlAI
【Explain Plan】檢視SQL的執行計劃
2009-10-07
AISQL
Oracle執行計劃Explain Plan 如何使用
2019-01-11
OracleAI
MongoDb學習之Explain執行計劃
2021-12-14
MongoDBAI
Explain For理論執行計劃相關
2013-12-04
AI
Oracle 執行計劃（Explain Plan）說明
2010-08-20
OracleAI
Oracle 執行計劃（Explain Plan）說明
2015-10-09
OracleAI
MySQL優化從執行計劃開始(explain超詳細)
2021-03-17
MySql優化AI
【Mysql】MySQL查詢計劃key_len全知道
2016-01-25
MySql
使用EXPLAIN PLAN來檢視執行計劃
2014-09-25
AI
TOAD中檢視執行計劃（Explain Plan）
2008-10-07
AI
配置oracle 解釋執行計劃--explain plan
2007-01-14
OracleAI
MySQL 執行計劃
2020-12-24
MySql