Talking about the key_len Calculation Method in mysql explain, explainkey_len

Talking about the key_len Calculation Method in mysql explain, explainkey_len

The explain command of mysql can analyze the SQL Performance. One of them is the statistics of key_len (index length. This article analyzes the calculation method of key_len in mysql explain.

1. Create test tables and data

CREATE TABLE `member` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(20) DEFAULT NULL, `age` tinyint(3) unsigned DEFAULT NULL, PRIMARY KEY (`id`), KEY `name` (`name`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;INSERT INTO `member` (`id`, `name`, `age`) VALUES (NULL, 'fdipzone', '18'), (NULL, 'jim', '19'), (NULL, 'tom', '19');

2. view the explain

The field type of name is varchar (20), and the character encoding is utf8. If a character occupies three bytes, key_len should be 20*3 = 60.

mysql> explain select * from `member` where name='fdipzone';+----+-------------+--------+------+---------------+------+---------+-------+------+-----------------------+| id | select_type | table | type | possible_keys | key | key_len | ref  | rows | Extra         |+----+-------------+--------+------+---------------+------+---------+-------+------+-----------------------+| 1 | SIMPLE   | member | ref | name     | name | 63   | const |  1 | Using index condition |+----+-------------+--------+------+---------------+------+---------+-------+------+-----------------------+

The key_len of the explain is 63, and 3 more.

The name field allows NULL,Change name to not null and test again

ALTER TABLE `member` CHANGE `name` `name` VARCHAR(20) NOT NULL;mysql> explain select * from `member` where name='fdipzone';+----+-------------+--------+------+---------------+------+---------+-------+------+-----------------------+| id | select_type | table | type | possible_keys | key | key_len | ref  | rows | Extra         |+----+-------------+--------+------+---------------+------+---------+-------+------+-----------------------+| 1 | SIMPLE   | member | ref | name     | name | 62   | const |  1 | Using index condition |+----+-------------+--------+------+---------------+------+---------+-------+------+-----------------------+

Now key_len is 62, which is 1 less than just now, but 2 more. It can be determined that NULL occupies one byte more.

The name field belongs to the variable length field, and belongs to the varchar field,Change varchar to char and test again

ALTER TABLE `member` CHANGE `name` `name` CHAR(20) NOT NULL;mysql> explain select * from `member` where name='fdipzone';+----+-------------+--------+------+---------------+------+---------+-------+------+-----------------------+| id | select_type | table | type | possible_keys | key | key_len | ref  | rows | Extra         |+----+-------------+--------+------+---------------+------+---------+-------+------+-----------------------+| 1 | SIMPLE   | member | ref | name     | name | 60   | const |  1 | Using index condition |+----+-------------+--------+------+---------------+------+---------+-------+------+-----------------------+

After changing to the fixed length field, key_len is 60, which is consistent with the prediction.

Summary:To use a variable-length field, you need to add two additional bytes. To use NULL, you need to add one additional byte. Therefore, we recommend that you use a fixed length and not null for the indexed field to improve performance.

In the above discussion, the key_len Calculation Method in mysql explain is all the content that I have shared with you. I hope to give you a reference and support for the customer's house.