A brief discussion on the calculation method of key_len in mysql explain

The MySQL explain command can analyze the performance of SQL, one of which is the key_len (index length) statistic. This article will analyze 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 explain

The name field type is varchar(20) , the character encoding is utf8 , and one character occupies 3 bytes, so 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 explain is 63 , which is 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 before, but still 2 more. It is certain that a NULL field will occupy one extra byte.

The name field type is varchar, which is a variable-length 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 a fixed-length field, key_len is 60 , which is consistent with the prediction.

Summary: Using variable-length fields requires an additional 2 bytes, and using NULL requires an additional 1 byte. Therefore, for indexed fields, it is best to use fixed-length and NOT NULL definitions to improve performance.

The above brief discussion on the calculation method of key_len in MySQL explain is all the content that the editor shares with you. I hope it can give you a reference. I also hope that you will support 123WORDPRESS.COM.

You may also be interested in: