MySQL執行計劃explain的key_len解析

當用explain檢視sql的執行計劃時，裡面有列顯示了 key_len 的值，根據這個值可以判斷索引的長度，在組合索引裡面可以更清楚的了解到了哪部分字段使用到了索引。下面示範中，表結構的合理性這邊暫且不說，隻是證明一下索引長度的計算方法。目前大部分博文是字元類型的索引長度計算方法，下面列舉幾個類型的索引長度計算方法：

1、整數類型

(dg1)[email protected] [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)[email protected] [mytest]>create index age_index on table_key (age);

來看看tinyint類型的索引長度，在not null 和 null 的時候 分别是1和2,tinyint字段長度為1，因為null 需要額外一個位元組标記為空

(dg1)[email protected] [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)[email protected] [mytest]> alter table table_key modify age  tinyint(4);

|  1 | simple      | table_key | ref  | age_index     | age_index | 2       | const |    1 |   100.00 | null  |

(dg1)[email protected] [mytest]> 

看看bigint類型的索引長度，同樣是 not null 和 null值的時候，分别是8和9,聰明的你應該知道了，bigint長度為8。

(dg1)[email protected] [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)[email protected] [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)[email protected] [mytest]> alter table table_key modify sid bigint(20);

query ok, 0 rows affected (0.08 sec)

|  1 | simple      | table_key | ref  | sid_index     | sid_index | 9       | const |    1 | null  |

看看smallint類型索引長度，同樣是 not null 和 null值的時候，分别是2和3 smallint長度為2，允許為空需要一個位元組标記

(dg1)[email protected] [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

|  1 | simple      | table_key | ref  | sid_index     | sid_index | 2       | const |    1 | null  |

(dg1)[email protected] [mytest]> alter table table_key modify sid smallint ;

query ok, 0 rows affected (0.07 sec)

|  1 | simple      | table_key | ref  | sid_index     | sid_index | 3       | const |    1 | null  |

看看mediumint類型索引長度，同樣是 not null 和 null值的時候，分别是3和4

(dg1)[email protected] [mytest]> alter table table_key modify sid mediumint not null;

query ok, 0 rows affected (0.06 sec)

（dg1)[email protected] [mytest]> alter table table_key modify sid mediumint ; 

query ok, 0 rows affected (0.06 sec) 

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

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

整數類型索引長度跟字段長度有關，如果允許為空，需要額外一個位元組去标記為空

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)[email protected] [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 |

(dg1)[email protected] [mytest]> alter table table_key1 modify c1 float;

query ok, 0 rows affected (0.05 sec)

|  1 | simple      | table_key1 | ref  | c1_index      | c1_index | 5       | const |    8 | using index condition |

看看double類型的索引長度，not null和null的時候，分别是8和9

(dg1)[email protected] [mytest]> alter table table_key1 add key c2_index (c2);

(dg1)[email protected] [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  |

(dg1)[email protected] [mytest]> alter table table_key1 modify c2 double;

query ok, 0 rows affected (0.03 sec)

|  1 | simple      | table_key1 | ref  | c2_index      | c2_index | 9       | const |    1 | null  |

(dg1)[email protected] [mytest]>

3、看看時間類型

看看date類型的索引長度，在not null和null的時候，分别是3和4

(dg1)[email protected] [mytest]> desc select * from table_key1 where c4 = '2015-05-06';

|  1 | simple      | table_key1 | ref  | c4_index      | c4_index | 3       | const |    4 | using index condition |

row in set, 3 warnings (0.00 sec)

(dg1)[email protected] [mytest]> alter table table_key1 modify c4 date;

query ok, 0 rows affected (0.09 sec)

|  1 | simple      | table_key1 | ref  | c4_index      | c4_index | 4       | const |    4 | using index condition |

在timestamp類型的時候索引長度，在not null 和 null的時候，分别是4和5

(dg1)[email protected] [mytest]> desc select * from table_key1 where c5 = '2015-05-06 11:23:21' ;

|  1 | simple      | table_key1 | ref  | c5_index      | c5_index | 4       | const |    5 | using index condition |

dg1)[email protected] [mytest]> alter table table_key1 modify c5 timestamp on update current_timestamp ;

(dg1)[email protected] [mytest]> desc select * from table_key1 where c5 = '2015-05-06 110:23:21';

|  1 | simple      | table_key1 | ref  | c5_index      | c5_index | 5       | const |    5 | using index condition |

##############################在大家認識裡datetime是八個位元組的長度，下面就來看看，是不是真的這樣

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

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

|  1 | simple      | table_key1 | ref  | c6_index      | c6_index | 5       | const |    1 | null  |

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

|  1 | simple      | table_key1 | ref  | c6_index      | c6_index | 6       | const |    1 | null  |

颠覆了我們認識，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

看看定長字元類型char的索引長度，在not null 和null中分别為10*3和10*3+1

(dg1)[email protected] [mytest]> alter table table_key add index name_index (name);

query ok, 0 rows affected (0.01 sec)

(dg1)[email protected] [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 |

(dg1)[email protected] [mytest]> alter table table_key modify name char(10);

|  1 | simple      | table_key | ref  | name_index    | name_index | 31      | const |    1 |   100.00 | using index condition |

看看變長長字元類型varchar的索引長度，在not null 和null中分别為10*3+2和10*3+2+1

(dg1)[email protected] [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)[email protected] [mytest]> alter table table_key modify address varchar(10);

query ok, 0 rows affected (0.10 sec)

|  1 | simple      | table_key | ref  | address_index | address_index | 33      | const |    1 |   100.00 | using index condition |

來看看複合索引的key_len,（剛才測試gbk字元集，字元集轉換成gbk了）

| name    | char(10)    | no   |     |         |       |

| address | varchar(10) | no   | mul |         |       |

(dg1)[email protected] [mytest]> alter table table_key drop index name_index;

(dg1)[email protected] [mytest]> alter table table_key drop index address_index;

(dg1)[email protected] [mytest]> alter table table_key add index name_address_index (name,address);

query ok, 0 rows affected (0.02 sec)

(dg1)[email protected] [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 |

看看複合索引的長度，因為能全部使用到組合索引，是以是：2*（10）+2*（20）+2=42,下面将name字段允許為空，再來看看

(dg1)[email protected] [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 |

看看複合索引的長度，因為能全部使用到組合索引，是以是：2*（10）+1+2*（20）+2=43

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

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

那麼我們來看看部分使用複合索引：2*（10）+1,将address設定為允許為空，再來看看

|  1 | simple      | table_key | ref  | name_address_index | name_address_index | 44      | const,const |    1 |   100.00 | using index condition |

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

<b>本文來自雲栖社群合作夥伴“dbgeek”</b>