MySQL The meaning of key_len in explain and calculation method

Preface

There is a column in the result of executing explain in Mysql as key_len, so what does key_len mean?

key_len: represents the number of bytes used by the index. Based on this value, you can judge the usage of the index. Especially when combining the index, it is very important to determine how many parts of the index are used.

Notes on calculating key_len:

• Additional information for index fields: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, and datetime, there needs to be a mark of whether it is empty. This mark takes 1 byte (for fields with not null, this 1 byte is not needed); for variable-length data types, such as varchar, in addition to whether it is empty, there needs to be length information, which needs to occupy two bytes.
• For character sets such as char, varchar, blob, text, the length of key len is also related to the character set. Latin1 takes 1 byte, gbk takes 2 bytes, and utf8 takes 3 bytes.

Give an example:

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

For example, there is a joint index idx(c1,c2,c3), and the 3 columns are int not null, so in the following SQL execution plan, the value of key_len is 8 instead of 12:

select ... from tb where c1=? and c2=? order by c1;

Example

The following is a specific example to illustrate the table structure:

CREATE TABLE `t4` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `a` int(11) NOT NULL DEFAULT '0',
  `b` int(11) NOT NULL DEFAULT '0',
  `c` int(11) NOT NULL DEFAULT '0',
  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 a=0&b>0 and a>0&b=0 explanation are "almost" the same. Both use index index1, and are the results obtained by range index (i.e. scan the index interval). The only difference is key_len. key_len explains the index length used during searching. You can infer that the multi-dimensional index uses several dimensions based on the length. (MySQL indexes are all prefix indexes)

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

a=0&b>0 key_len is 8, which means that you can get the result by just using the index. First use a=0 to find the tree node, and then filter it according to b>0 below to get the result. That is, if you use the index completely, you can get the result.

a>0&b=0 key_len is 4, which means that only the first dimension of the prefix index is used, and only a>0 is used to obtain the result (primary key), then read the entire row in the primary key index (clustered index), filter the relevant data according to b=0, and get the result. That is, if the index is used "incompletely", the result can be obtained.

Summarize

This is the article about the meaning and calculation method of key_len in mysql explain. For more related content of key_len in mysql explain, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!