MYSQL EXPLAIN 中的KEY_LEN的說明

• 2019 年 10 月 5 日
• 筆記

對於explain extended 查看執行計劃裡面的一些資訊作為一個DBA還是必須掌握的。

參考博文：http://www.cnblogs.com/xuanzhi201111/p/4554769.html

環境： MySQL5.6.36

默認字符集： utf8

一、前置回顧：

1、數值型的欄位長度

欄位類型   長度    UNSIGNED          SIGNED有符號型           適用場合

tinyint:    1bytes   2^8-1 0-255           -128~127                    小整數值

smallint:   2bytes  2^16-1 0-65535         -32768~32767                大整數值

mediumint:  3bytes  2^24-1 0-16777215      -8388608~8388607            大整數值

int:        4bytes  2^32-1 0-4294967295    -2147483648-2147483647      大整數值(最大只能存10位數字)

bigint:     8bytes  2^64-1                                             大整數值

float       4bytes  單精度浮點型

double      8bytes  雙精度浮點型

2、char和varchar型的欄位長度

char和varchar是日常使用最多的字元類型。char（N）用於保存固定長度的字元串，長度最大為255，比指定長度大的值將被截短，而比指定長度小的值將會用空格進行填補。

varchar（N）用於保存可以變長的字元串，長度最大為65535，只存儲字元串實際實際需要的長度（它會增加一個額外位元組來存儲字元串本身的長度），varchar使用額外的1~2位元組來存儲值的的長度，如果列的最大長度小於或者等於255，則用1位元組，否則用2位元組。

char和varchar跟字元編碼也有密切的聯繫，latin1佔用1個位元組，gbk佔用2個位元組，utf8佔用3個位元組。（不同字元編碼佔用的存儲空間不同）

3、日期&時間類型的欄位長度

DataType  Storage Required Before MySQL 5.6.4    Storage    Requiredas of MySQL 5.6.4

YEAR                1byte                                  1byte

DATE                3bytes                                 3bytes

TIME                3bytes                                 3 bytes + fractional seconds storage

DATETIME            8bytes                                 5 bytes + fractional seconds storage

TIMESTAMP           4bytes                                 4 bytes + fractional seconds storage

二、創建測試用表：

> use test;

> CREATE TABLE `t1` (

  `a`int(11)  NULL,

 `aa` int(11) NOT NULL,

  `b`char(10)  NULL,

 `bb` char(10) NOT NULL,

  `c`varchar(20)  NULL,

 `cc` varchar(20) NOT NULL,

  `d`time  NULL,

 `dd` time NOT NULL,

  `e`timestamp NULL,

 `ee` timestamp NOT NULL,

  `f`datetime  NULL,

 `ff` datetime NOT NULL,

  KEY`index_a` (`a`),

  KEY`index_aa` (`aa`),

  KEY`index_b` (`b`),

  KEY`index_bb` (`bb`),

  KEY`index_c` (`c`),

  KEY`index_cc` (`cc`),

  KEY`index_d` (`d`),

  KEY`index_dd` (`dd`),

  KEY`index_e` (`e`),

  KEY`index_ee` (`ee`),

  KEY`index_f` (`f`),

  KEY`index_ff` (`ff`)

) ENGINE=InnoDB  CHARSET=utf8 ;

上面創建了一張奇怪的表，基本上覆蓋了常見的欄位類型了，每個欄位都加了索引。

> insert into t1 values(1,11,'2','22','3','33','12:23:12','02:11:45','2017-02-12 01:00:12','2015-04-2121:09:12','2015-12-21 21:09:12','2021-04-21 01:45:56') ;

> insert into t1 values(12,131,'62','262','38','332','22:23:12','02:18:45','2017-12-1201:34:12','2005-04-21 21:09:12','2065-12-21 21:09:12','1949-10-01 01:45:56') ;

> explain extended select * from t1where a=12;

+—-+————-+——-+——+—————+———+———+——-+——+———-+——-+

| id | select_type | table | type |possible_keys | key     | key_len |ref   | rows | filtered | Extra |

+—-+————-+——-+——+—————+———+———+——-+——+———-+——-+

|  1| SIMPLE      | t1    | ref | index_a       | index_a | 5       | const |    1 |  100.00 | NULL  |

+—-+————-+——-+——+—————+———+———+——-+——+———-+——-+

4（int佔4bytes）+ 1 (1byte用來標記是否為null)

> explain extended select * from t1where aa=12;

+—-+————-+——-+——+—————+———-+———+——-+——+———-+——-+

| id | select_type | table | type |possible_keys | key      | key_len |ref   | rows | filtered | Extra |

+—-+————-+——-+——+—————+———-+———+——-+——+———-+——-+

|  1| SIMPLE      | t1    | ref | index_aa      | index_aa | 4       | const |    1 |  100.00 | NULL  |

+—-+————-+——-+——+—————+———-+———+——-+——+———-+——-+

4（int佔4bytes）

> explain extended select * from t1where b='abc';

+—-+————-+——-+——+—————+———+———+——-+——+———-+———————–+

| id | select_type | table | type |possible_keys | key     | key_len |ref   | rows | filtered | Extra                 |

+—-+————-+——-+——+—————+———+———+——-+——+———-+———————–+

|  1| SIMPLE      | t1    | ref | index_b       | index_b | 31      | const |    1 |  100.00 | Using index condition |

+—-+————-+——-+——+—————+———+———+——-+——+———-+———————–+

10*3 （char每個字元在utf字符集下佔3bytes）+ 1 (1byte用來標記是否為null)

> explain extended select * from t1where bb='stfdg4';

+—-+————-+——-+——+—————+———-+———+——-+——+———-+———————–+

| id | select_type | table | type |possible_keys | key      | key_len |ref   | rows | filtered | Extra                 |

+—-+————-+——-+——+—————+———-+———+——-+——+———-+———————–+

|  1| SIMPLE      | t1    | ref | index_bb      | index_bb |30      | const |    1 |  100.00 | Using index condition |

+—-+————-+——-+——+—————+———-+———+——-+——+———-+———————–+

10*3 （char每個字元在utf字符集下佔3bytes）

> explain extended select * from t1where c='hfdg';

+—-+————-+——-+——+—————+———+———+——-+——+———-+———————–+

| id | select_type | table | type |possible_keys | key     | key_len |ref   | rows | filtered | Extra                 |

+—-+————-+——-+——+—————+———+———+——-+——+———-+———————–+

|  1| SIMPLE      | t1    | ref | index_c       | index_c | 63      | const |    1 |  100.00 | Using index condition |

+—-+————-+——-+——+—————+———+———+——-+——+———-+———————–+

20*3（varchar每個字元在utf字符集下佔3bytes） + 2 (2bytes用來記錄varchar這種變長字元的長度) + 1 (1byte用來標記是否為null)

> explain extended select * from t1where cc='fgd';

+—-+————-+——-+——+—————+———-+———+——-+——+———-+———————–+

| id | select_type | table | type |possible_keys | key      | key_len |ref   | rows | filtered | Extra                 |

+—-+————-+——-+——+—————+———-+———+——-+——+———-+———————–+

|  1| SIMPLE      | t1    | ref | index_cc      | index_cc | 62      | const |    1 |  100.00 | Using index condition |

+—-+————-+——-+——+—————+———-+———+——-+——+———-+———————–+

20*3（varchar每個字元在utf字符集下佔3bytes） + 2 (2bytes用來記錄varchar這種變長字元的長度)

> explain extended select * from t1where d='01:02:02';

+—-+————-+——-+——+—————+———+———+——-+——+———-+———————–+

| id | select_type | table | type |possible_keys | key     | key_len |ref   | rows | filtered | Extra                 |

+—-+————-+——-+——+—————+———+———+——-+——+———-+———————–+

|  1| SIMPLE      | t1    | ref | index_d       | index_d | 4       | const |    1 |  100.00 | Using index condition |

+—-+————-+——-+——+—————+———+———+——-+——+———-+———————–+

3 (time 類型佔據3bytes) + 1 (1byte用來標記是否為null)

> explain extended select * from t1where dd='01:02:02';

+—-+————-+——-+——+—————+———-+———+——-+——+———-+———————–+

| id | select_type | table | type |possible_keys | key      | key_len |ref   | rows | filtered | Extra                 |

+—-+————-+——-+——+—————+———-+———+——-+——+———-+———————–+

|  1| SIMPLE      | t1    | ref | index_dd      | index_dd |3       | const |    1 |  100.00 | Using index condition |

+—-+————-+——-+——+—————+———-+———+——-+——+———-+———————–+

3 (time 類型佔據3bytes)

> explain extended select * from t1where e='2017-02-11 01:02:02';

+—-+————-+——-+——+—————+———+———+——-+——+———-+——-+

| id | select_type | table | type |possible_keys | key     | key_len |ref   | rows | filtered | Extra |

+—-+————-+——-+——+—————+———+———+——-+——+———-+——-+

|  1| SIMPLE      | t1    | ref | index_e       | index_e | 5       | const |    1 |  100.00 | NULL  |

+—-+————-+——-+——+—————+———+———+——-+——+———-+——-+

4 (timestamp 類型佔據4bytes) + 1 (1byte用來標記是否為null)

> explain extended select * from t1where ee='2017-02-11 01:02:02';

+—-+————-+——-+——+—————+———-+———+——-+——+———-+——-+

| id | select_type | table | type |possible_keys | key      | key_len |ref   | rows | filtered | Extra |

+—-+————-+——-+——+—————+———-+———+——-+——+———-+——-+

|  1| SIMPLE      | t1    | ref | index_ee      | index_ee | 4       | const |    1 |  100.00 | NULL  |

+—-+————-+——-+——+—————+———-+———+——-+——+———-+——-+

4 (timestamp 類型佔據4bytes)

> explain extended select * from t1where f='2017-02-11 01:02:02';

+—-+————-+——-+——+—————+———+———+——-+——+———-+——-+

| id | select_type | table | type |possible_keys | key     | key_len |ref   | rows | filtered | Extra |

+—-+————-+——-+——+—————+———+———+——-+——+———-+——-+

|  1| SIMPLE      | t1    | ref | index_f       | index_f | 6       | const |    1 |  100.00 | NULL  |

+—-+————-+——-+——+—————+———+———+——-+——+———-+——-+

5 (datetime 類型佔據5bytes) +  1 (1byte用來標記是否為null)

> explain extended select * from t1where ff='2017-02-11 01:02:02';

+—-+————-+——-+——+—————+———-+———+——-+——+———-+——-+

| id | select_type | table | type |possible_keys | key      | key_len |ref   | rows | filtered | Extra |

+—-+————-+——-+——+—————+———-+———+——-+——+———-+——-+

|  1| SIMPLE      | t1    | ref | index_ff      | index_ff |5       | const |    1 |  100.00 | NULL  |

+—-+————-+——-+——+—————+———-+———+——-+——+———-+——-+

5 (datetime 類型佔據5bytes)

聯合索引的key_len的計算：

先去掉上面的2個單列索引，防止執行計劃跑偏，然後加上一個聯合索引：

alter table t1 drop index index_c;

alter table t1 drop index index_d;

alter table t1 add index c_d (c,d);

> explain extended select * from t1where c='aaa' and d='12:21:11';

+—-+————-+——-+——+—————+——+———+————-+——+———-+———————–+

| id | select_type | table | type |possible_keys | key  | key_len | ref         | rows | filtered | Extra                 |

+—-+————-+——-+——+—————+——+———+————-+——+———-+———————–+

|  1| SIMPLE      | t1    | ref | c_d           | c_d  | 67 | const,const |    1 |  100.00 | Using index condition |

+—-+————-+——-+——+—————+——+———+————-+——+———-+———————–+

`c`列 varchar(20)DEFAULT NULL,

`d`列 timeDEFAULT NULL,

key_len = （20*3+2+1）+（3+1）= 67

總結1：

char和varchar類型key_len計算公式:

varchr(N)變長欄位且允許NULL   =  N *( character set：utf8=3,gbk=2,latin1=1)+1(NULL)+2(變長欄位)

varchr(N)變長欄位且不允許NULL  =  N * ( character set：utf8=3,gbk=2,latin1=1)+2(變長欄位)

char(N)固定欄位且允許NULL     =  N* ( character set：utf8=3,gbk=2,latin1=1)+1(NULL)

char(N)固定欄位且允許NULL     =  N* ( character set：utf8=3,gbk=2,latin1=1)

數值數據的key_len計算公式：

TINYINT允許NULL = 1 +1(NULL)

TINYINT不允許NULL = 1

SMALLINT允許為NULL =2+1(NULL)

SMALLINT不允許為NULL = 2

INT允許為NULL =4+1(NULL)

INT不允許為NULL = 4

BIGINT允許為NULL = 8+1(NULL)

BIGINT不允許為NULL = 8

日期時間型的key_len計算：（針對mysql5.5及之前版本）

DATETIME允許為NULL=  8 + 1(NULL)

DATETIME不允許為NULL = 8

TIMESTAMP允許為NULL = 4+ 1(NULL)

TIMESTAMP不允許為NULL = 4

日期時間型的key_len計算：（針對mysql5.6.4及之後的版本）

DATETIME允許為NULL=  5 + 1(NULL)

DATETIME不允許為NULL = 5

TIMESTAMP允許為NULL = 4+ 1(NULL)

TIMESTAMP不允許為NULL = 4

總結2：

1、從上面幾個測試可以看出，假如我們建表時候使用varchar(20),varchar(255) 雖然實際存放的內容長度是一樣的。但是這2種情況下索引佔據的長度是不一樣的。

MySQL建立索引時如果沒有限制索引的大小，索引長度會默認採用的該欄位的長度，

也就是說varchar(20)和varchar(255)對應的索引長度分別為20*3(utf-8)(+2+1),255*3(utf-8)(+2+1)，

其中"+2"用來存儲長度資訊，「+1」用來標記是否為空。

載入索引資訊時用varchar(255)類型會佔用更多的記憶體。

2、另外，我們可以看到如果建表的時候，欄位設置為DEFAULT NULL，會導致索引的長度增加1bytes，此外，NULL列會導致索引失效。

補充：

查看test庫中的行數量、數據大小、索引大小等統計情況：

> SELECTCONCAT(table_schema,'.',table_name) AS 'Table Name',CONCAT(ROUND(table_rows/1000000,4),'M') AS 'Number of Rows',

    CONCAT(ROUND(data_length/(1024*1024*1024),4),'G')AS 'Data Size',

    CONCAT(ROUND(index_length/(1024*1024*1024),4),'G')AS 'Index Size',

    CONCAT(ROUND((data_length+index_length)/(1024*1024*1024),4),'G')AS'Total'

    FROMinformation_schema.TABLES

    WHEREtable_schema LIKE 'test';

+————+—————-+———–+————+———+

| Table Name | Number of Rows | Data Size |Index Size | Total   |

+————+—————-+———–+————+———+

| test.a    | 0.0164M        | 0.0015G   | 0.0020G   | 0.0034G |

| test.b    | 0.0165M        | 0.0015G   | 0.0000G   | 0.0015G |

| test.c    | 0.3594M        | 0.0210G   | 0.0000G   | 0.0210G |

+————+—————-+———–+————+———+

3 rows in set (0.00 sec)