Shulou(Shulou.com)05/31 Report--

This article is to share with you about how to calculate key_len in MySQLexplain. The editor thinks it is very practical, so I share it with you to learn. I hope you can get something after reading this article.

Calculation of key_len in MySQL explain

Key_len represents 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.

Here are some points to consider when calculating key_len:

Additional information for index fields:

1. It can be divided into variable length and fixed length data types.

two。 When the index field is a fixed-length data type, such as char,int,datetime, an empty tag is required, which takes up one byte (not required for not null)

3. When the index field is a variable-length data type, such as varchar, in addition to the empty tag, length information is required, which takes up two bytes

4. For char,varchar,blob,text, etc., the length of key_len is also related to the character set. One character in latin1 takes up one byte, one character in gbk takes two bytes, and one character in utf8 takes three bytes.

For example:

Column type KEY_LEN comment

Id int key_len = 4: 1 int is 4bytes, allowed is NULL, plus 1byte

Id bigint not nullkey_len=8 bigint is 8bytes

User char (30) utf8key_len=30*3+1 utf8 each character is 3bytes, NULL is allowed, plus 1byte

User varchar (30) not null utf8key_len=30*3+2utf8 each character is 3bytes, variable length data type, plus 2bytes

User varchar (30) utf8key_len=30*3+2+1utf8 each character is 3bytes, allowed to be NULL, plus 1byte, variable length data type, plus 2bytes

Detail text (10) utf8key_len=30*3+2+1 TEXT intercept part, which is regarded as a dynamic column type

Note:

Key_len represents only the selected index columns in the where for conditional filtering, and does not contain the selected index columns in the order by / group by section.

For example:

If there is a federated index idx (A1 recordb2jc3), and all three columns are int not null, the value of key_len in the following execution plan is 8, not 12.

Select.... From table where c1s? And c2? Order by c1

This is how key_len is calculated in MySQLexplain. The editor believes that there are some knowledge points that we may see or use in our daily work. I hope you can learn more from this article. For more details, please 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.