Calculation of Key_len in MySQL explain

Today, Ding Yuan asked me how the key_len in the mysql execution plan was calculated. I didn’t pay attention at the time. I talked about the calculation of this value in the high-performance book, but I didn’t pay much attention to it when I looked at the execution plan. Value, let alone discuss the calculation of this value:

ken_len represents the number of bytes used by the index. According to this value, the index usage can be judged, especially when combining indexes, it is judged that all index fields are used by the query.

When I checked the official documents, I did not find a detailed introduction to the calculation of key_len. Later, I did some tests. When I consulted Ding Qi about the value calculation of the variable-length data type, I suddenly thought of the format of the innodb row. The calculation here It's a bit similar in the middle. To sum up, we need to consider the following situations:

(1). Additional information for index fields: int occupies 4 bytes. Char can be divided into variable length and fixed length data types. When the index field is fixed length data type, such as char, int, datetime, whether it needs to be For an empty tag, this tag needs to occupy 1 byte; for variable-length data types, such as varchar, in addition to whether it is empty or not, it also needs length information, which needs to occupy 2 bytes;

(Note: When the field is defined as non-empty, the mark of whether it is empty will not occupy bytes)

(2). For char or varchar types, it is also necessary to consider the character set used in the table, different character sets, gbk encoding is 2 bytes for a character, and utf8 encoding is 3 bytes for a character;

 

First look at an example of fixed-length data type (encoded as gbk):

[email protected] 07:32:39>create table test_char(id int not null ,name_1 char(20),name_2 char(20),

-> primary key(id),key ind_name(name_1,name_2))engine=innodb charset=gbk;

[email protected] 07:33:55>insert into test_char values(1,’xuancan’,‘taobaodba’);

[email protected] 07:34:55>explain select * from test_char where name_1=’xuancan’\G;

*************************** 1. row ******************** *******

id: 1

select_type: SIMPLE

table: test_char

type: ref

possible_keys: ind_name

key: ind_name

key_len: 41

ref: const

rows: 1

Extra: Using where; Using index

key_len=41=20*2+1 (Note: Since name_1 is empty, the flag of isnull is marked, and 1 byte needs to be calculated)

[email protected] 07:35:31>explain select * from test_char where name_1=’xuancan’ and name_2=’taobaodba’\G;

*************************** 1. row ******************** *******

id: 1

select_type: SIMPLE

table: test_char

type: ref

possible_keys: ind_name

key: ind_name

key_len: 82

ref: const,const

rows: 1

Extra: Using where; Using index

key_len=82=20*2+20*2+1+1 (Note: Since the two columns name_1 and name_2 are used, but both columns are empty, 2 bytes need to be calculated)

Variable length data type (gbk encoding):

[email protected] 08:30:51>create table test_varchar(id int not null ,name_1 varchar(20),name_2 varchar(20),

-> primary key(id),key ind_name(name_1,name_2))engine=innodb charset=gbk;

[email protected] 08:37:51>insert into test_varchar values(1,’xuancan’,‘taobaodba’);

[email protected] 08:38:14>explain select * from test_varchar where name_1=’xuancan’\G;

*************************** 1. row ******************** *******

id: 1

select_type: SIMPLE

table: test_varchar

type: ref

possible_keys: ind_name

key: ind_name

key_len: 43

ref: const

rows: 1

Extra: Using where; Using index

key_len=43=20*2+1+2 (Note: Since the name_1 field is defined as empty, it needs to be calculated 1, and because it is a variable length field varchar, it needs to be added 2)

[email protected] 08:38:46>alter table test_varchar modify column name_1 varchar(20) not null;

Query OK, 1 row affected (0.52 sec)

Records: 1 Duplicates: 0 Warnings: 0

[email protected] 08:42:11>explain select * from test_varchar where name_1=’xuancan’\G;

*************************** 1. row ******************** *******

id: 1

select_type: SIMPLE

table: test_varchar

type: ref

possible_keys: ind_name

key: ind_name

key_len: 42

ref: const

rows: 1

Extra: Using where; Using index

key_len=42=20*2+2 (Note that after the name_1 field is modified to not null, the bytes occupied by the flag lock of isnull are released, but the 2 bytes occupied by the variable length field length are not released);

The above is a test to test gbk encoding, and you can also test the key_len calculation of other encodings.

Calculation of key_len in mysql explain