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

This article mainly shows you "the meaning of key_len in mysql explain and what is the calculation method". The content is simple and clear. I hope it can help you solve your doubts. Let me lead you to study and learn "what is the meaning and calculation method of key_len in mysql explain".

Preface

One of the results of executing explain in Mysql is listed as key_len, so what does key_len mean?

Key_len: indicates the number of bytes used by the index, which can be used to determine the use of the index, especially when combining the index, it is very important to determine how many parts of the index are used.

Considerations for calculating key_len:

Additional information of the index field: it can be divided into variable length and fixed length data types. When the index field is a fixed length data type, such as char,int,datetime, you need to have an empty tag, which takes up 1 byte (for not null fields, this 1 byte is not needed); for variable length data types, such as varchar, in addition to whether the tag is empty, you also need length information, which requires two bytes.

For char, varchar, blob, text and other character sets, the length of key len is also related to the character set. A character of latin1 takes up 1 byte, a character of gbk takes 2 bytes, and a character of utf8 occupies 3 bytes.

Examples are as follows:

Column type KEY_LEN Note id intkey_len = 4+1int is 4bytes, allowed is NULL, plus 1byteid bigint not nullkey_len=8bigint is 8bytesuser char (30) utf8key_len=30*3+1utf8 each character is 3bytes, allowed is NULL, plus 1byteuser varchar (30) not null utf8key_len=30*3+2utf8 each character is 3bytes, variable length data type, plus 2bytesuser varchar (30) utf8key_len=30*3+2+1utf8 each character is 3bytes, allowed for NULL, plus 1byte, variable length data type, plus 2bytesdetail text (10) utf8key_len=30*3+2+1TEXT intercept part Is considered a dynamic column type.

Note: key_len indicates only the selected index columns in where for conditional filtering, and does not include the selected index columns in order by/group by.

For example, if you have a federated index idx (c1memc2 and c3) and all three columns are int not null, then in the following SQL execution plan, the value of key_len is 8 instead of 12:

Select... From tb where c1s? And c2? Order by C1; example

The following is illustrated by a specific example, table structure:

CREATE TABLE `t4` (`id` int (11) NOT NULL AUTO_INCREMENT, `a` int (11) NOT NULL DEFAULT '0mm, `b` int (11) NOT NULL DEFAULT' 0mm, `c` int (11) NOT NULL DEFAULT '0mm, PRIMARY KEY (`id`), KEY `index1` (`a`, `b`) ENGINE=InnoDB

The result of executing explain is as follows:

Mysql > explain select * from T4 where a = 0 and b > 0 +-- + | id | select_type | table | type | possible_keys | key | key_len | Ref | rows | Extra | +-+ | 1 | SIMPLE | T4 | range | index1 | | index1 | 8 | NULL | 1 | Using where | +-+ mysql > explain select * from T4 where a > 0 and b = 0 | +-- + | id | select_type | table | type | possible_keys | key | key_len | Ref | rows | Extra | +-+ | 1 | SIMPLE | T4 | range | index1 | | index1 | 4 | NULL | 1 | Using where | +-+ |

You can see that the results of axi0roomb > 0 and a > 0&b=0 explain are "almost" the same, using the index index1, which is the result of the index range (that is, scanning the index interval). The only difference is key_len. Key_len describes the length of the index used in the search, based on which you can infer how many dimensions are referenced by the multidimensional index. (MySQL indexes are all prefix indexes)

Index1 is a two-dimensional index KEY index1 (aformab), so the length should be 4-4.

Key_len 0 > 0 is 8, which means that only the index is used to get the result. First, find the tree node with astat0, and then filter under it according to b > 0 to get the result. That is, the result can be obtained by using the index "completely".

A > 0&b=0 key_len is 4, which means that only the first dimension of the prefix index is used, only a > 0 is used to get the result (primary key), and then the whole row is read in the primary key index (clustered index), and the relevant data is filtered according to bread0 to get the result. That is, the "incomplete" index is used to get the result.

The above is all the content of the article "what is the meaning and calculation method of key_len in mysql explain". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

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.