Key_len Analysis of MySQL execution Plan explain 04/20 Update SLTechnology News&Howtos

Key_len Analysis of MySQL execution Plan explain

2025-04-20 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

Shulou(Shulou.com)06/01 Report--

Author: Qiu Wenhui, senior database expert of Walk Science and Technology

Preface when using Explain to view the execution plan of SQL, there is a column showing the value of key_len. According to this value, you can judge the length of the index. In the combined index, you can know more clearly which part of the field uses the index. In the following demonstration, let's not talk about the rationality of the table structure here, just to prove the method of calculating the index length. At present, most blog posts are index length calculation methods for character types. Here are several types of index length calculation methods:

1. Dg1 root@127.0.0.1 [mytest] > desc table_key

+-+ +

+-+ +

| | id | int (11) | NO | | NULL |

| | sid | bigint (20) | NO | | 0 | |

| | age | tinyint (4) | YES | | NULL |

| | sex | tinyint (4) | NO | | NULL |

+-+ +

Rows in set (0.01sec)

(dg1) root@127.0.0.1 [mytest] > create index age_index on table_key (age)

Let's take a look at the index length of type tinyint, which is 1 and 2 in NOT NULL and NULL, respectively. The length of the tinyint field is 1, because NULL needs an extra byte marked as empty.

(dg1) root@127.0.0.1 [mytest] > explain extended select * from table_key where age=38

+-- +

+-- +

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

+-- +

+-- +

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

Look at the index length of the bigint type, which is also the NOT NULL and NULL values, which are 8 and 9, respectively. You should know that the bigint length is 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

+-- +

+-- +

+-- +

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

+-- +

+-- +

+-- +

Row in set (0.00 sec)

Take a look at the smallint type index length, which is also NOT NULL and NULL values, 2 and 3 smallint length 2 respectively, allowing a byte token to be null

(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

+-- +

+-- +

+-- +

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

+-- +

+-- +

+-- +

Row in set (0.00 sec)

Look at the mediumint type index length, which is also NOT NULL and NULL values, 3 and 4, respectively.

(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

+-- +

+-- +

+-- +

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

+-- +

+-- +

+-- +

Row in set (0.00 sec)

(dg1) root@127.0.0.1 [mytest] >

The integer type index length is related to the field length. If null is allowed, an extra byte is required to mark it as empty.

two。 Floating point type table structure

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

)

If you look at the index length of the float type, NOT NULL and NULL are 4 and 5, respectively.

(dg1) root@127.0.0.1 [mytest] > desc select * from table_key1 where C1 = '3.22'

+-+-

+-+-

+-+-

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'

+-+-

+-+-

+-+-

Row in set (0.00 sec)

When you look at the index length of the double type, NOT NULL and NULL, it's 8 and 9, respectively.

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

+-- +

+-- +

+-- +

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'

+-- +

+-- +

+-- +

Row in set (0.00 sec)

(dg1) root@127.0.0.1 [mytest] >

3. Look at the time type and look at the index length of the date type. In NOT NULL and NULL, it is 3 and 4, respectively.

(dg1) root@127.0.0.1 [mytest] > desc select * from table_key1 where c4 = '2015-05-06'

+-+-

+-+-

+-+-

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'

+-+-

+-+-

+-+-

Row in set, 3 warnings (0.00 sec)

(dg1) root@127.0.0.1 [mytest] >

For timestamp, the index length is 4 and 5 for NOT NULL and NULL, respectively.

(dg1) root@127.0.0.1 [mytest] > desc select * from table_key1 where c5 = '2015-05-06 11 purl 23 mytest 21'

+-+-

+-+-

+-+-

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 110purl 23pur21'

+-+-

+-+-

+-+-

Row in set, 3 warnings (0.00 sec)

(dg1) root@127.0.0.1 [mytest] >

# # datetime is known to be eight bytes in length. Let's see if this is true.

(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 mytest 10 mytest 36'

+-- +

+-- +

+-- +

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 mytest 10 mytest 36'

+-- +

+-- +

+-- +

Row in set (0.00 sec)

Subverted our understanding, datetime is not 8 bytes, let's take a look at the version of MySQL, yes, MySQL5.6 is datetime length is 5 bytes

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

Summary: in the MySQL5.6 version, whether to use the timestamp type should be a matter of opinion. Datetime is five bytes, and the range of timestamp is relatively narrow (1970-2037). Do not rule out the possibility that subsequent versions will modify its range.

4. Character type table structure, character set is UTF8

(dg1) root@127.0.0.1 [mytest] > desc table_key

+-+ +

+-+ +

| | id | int (11) | NO | | NULL |

| | sid | bigint (20) | NO | | 0 | |

| | age | tinyint (4) | YES | | NULL |

| | sex | tinyint (4) | NO | | NULL |

+-+ +

Rows in set (0.01sec)

Look at the index length of the fixed-length character type char, which is 10'3 and 10'3'1 in NOT NULL and NULL, respectively.

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

+-+-

+-+-

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

+-+-

+-+-

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

Look at the index length of the variable length character type varchar, which is 10'3'2 and 10'3'2'1 in NOT NULL and NULL, respectively.

(dg1) root@127.0.0.1 [mytest] > explain extended select * from table_key where address='shanghai'

+-+-

+-+-

| | 1 | SIMPLE | table_key | ref | address_index | address_index | 32 | const | 1 | 100.00 | Using index condition |

+-+-

Row in set, 1 warning (0.01sec)

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

+-+-

+-+-

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

Let's take a look at the key_len of the composite index, (just tested the GBK character set, the character set has been converted to GBK)

(dg1) root@127.0.0.1 [mytest] > desc table_key

+-+ +

+-+ +

| | id | int (11) | NO | | NULL |

| | sid | bigint (20) | NO | | 0 | |

| | age | tinyint (4) | YES | | NULL |

| | sex | tinyint (4) | NO | | NULL |

| | address | varchar (10) | NO | MUL | |

+-+ +

Rows in set (0.01sec)

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

+- -- +

+- -- +

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

Look at the length of the composite index, because it can all be used in the composite index, so it is: 2 * (10) + 2 * (20) + 2 * 42. Let's allow the name field to be empty. Let's take a look.

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

+- -- +

+- -- +

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

Look at the length of the composite index, because it can all be used in the composite index, so it is: 2 * (10) + 1 # 2 * (20) + 2 # 43

(dg1) root@127.0.0.1 [mytest] > explain extended select * from table_key where name='zhangsan'

+- -+

+- -+

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

So let's take a look at the part that uses the composite index: 2 * (10) + 1, set address to allow null, and then take a look.

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

+- -- +

+- -- +

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

At this time, key_len=2* (10) + 1x 2* (10) + 2m 1m 44

Summary

1. Index length of integer type, floating point type, time type

The length of the NOT NULL= field itself

The field length of the NULL= field itself is + 1, because an empty tag is required, which takes up 1 byte

The field length of the datetime type in 5.6is 5 bytes.

two。 Character type

Varchr (n) variable length field and allow NULL = n * (utf8=3,gbk=2,latin1=1) + 1 (NULL) + 2

Varchr (n) variable length field and NULL = n * (utf8=3,gbk=2,latin1=1) + 2 is not allowed

Char (n) fixed field and allows NULL = n * (utf8=3,gbk=2,latin1=1) + 1 (NULL)

Char (n) fixed field and allow NULL = n * (utf8=3,gbk=2,latin1=1)

Variable length fields require an additional 2 bytes (VARCHAR values are saved with only the number of characters required, plus one byte to record the length (two bytes are used if the declared length of the column is greater than 255), so add 2 to the VARCAHR index length calculation, while fixed-length fields do not require additional bytes. Null requires an extra byte of space, so it's best not to have the index field NULL, because NULL makes statistics more complex and requires extra storage space. This conclusion has been confirmed here that the composite index has the characteristic of the leftmost prefix. If the composite index can be fully used, it is the sum of the index length of the composite index field, which can also be used to determine whether the composite index is partially used or all.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.