What is the meaning of key_len in mysql explain and how to calculate it

Preface

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

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

Notes on calculating key_len:

• Additional information on index fields: can be divided into variable-length and fixed-length data types for discussion. When index fields When it is a fixed-length data type, such as char, int, datetime, a mark is required to indicate whether it is empty. This mark occupies 1 byte (for fields that are not null, this 1 byte is not required); for variable-length fields Data types, such as varchar, need to have length information in addition to the empty mark, which takes up two bytes.

• For character sets such as char, varchar, blob, text, etc., the length of key len is also related to the character set. One character of latin1 occupies 1 byte, and one character of gbk occupies one byte. 2 bytes, one UTF8 character occupies 3 bytes.

Example:

##user char(30) utf8key_len=30*3 1 Each character of utf8 is 3 bytes, NULL is allowed, add 1 byte##user varchar(30) not null utf8##user varchar(30) utf8key_len=30*3 2 1utf8 each character is 3 bytes, NULL is allowed, add 1 byte, variable length data type, add 2 bytesdetail text(10) utf8key_len=30*3 2 1TEXT intercepted part is regarded as a dynamic column type. Note: key_len only indicates the index column that is selected when used for conditional filtering in where, and does not include the order by/group by part that is selected. index column.

Column type	KEY_LEN	Remarks
id int	key_len = 4 1	int is 4bytes, NULL is allowed, add 1byte
id bigint not null	key_len=8	bigint is 8bytes
key_len=30*3 2	utf8 each character is 3 bytes, variable length data type, add 2 bytes

For example, there is a joint index idx (c1, c2, c3), and the three columns are all int not null, then 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, 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 a= The results of 0&b>0 and a>0&b=0 explain are "almost" the same. They both use the index index1, and they are both the results obtained by the range index (that is, scanning the index range). The only difference is key_len. Based on the index length key_len, you can infer how many dimensions are used by the multidimensional index. (MySQL indexes are all prefix indexes)

index1 is a 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 the result can be obtained only by using the index. First use a=0 to find the tree node, and then filter according to b>0 below it to get the result. That is, you can get the result "completely" by using the index.

a>0&b=0 key_len is 4, indicating 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 entire key_len is read in the primary key index (clustered index) OK, filter the relevant data based on b=0 and get the result. That is, "incomplete" uses the index to get the result.

The above is the detailed content of What is the meaning of key_len in mysql explain and how to calculate it. For more information, please follow other related articles on the PHP Chinese website!