An explanation of the calculation method of key_len in mysql explain

Mysql's explain command can analyze the performance of sql, one of which is the statistics of key_len (the length of the index). This article will analyze the calculation method of key_len in mysql explain.

1. Create test table 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 field type of

#name is

varchar(20), the character encoding is utf8, one character occupies 3 bytes, then key_len should be 20*3= 60. The key_len of

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 |
+----+-------------+--------+------+---------------+------+---------+-------+------+-----------------------+

explain is

63, which is 3.
The name field allows NULL, Change the 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 |
+----+-------------+--------+------+---------------+------+---------+-------+------+-----------------------+

The key_len is now

62, 1 less than before, but still 2 more. It is certain that a NULL field will occupy one more 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 |
+----+-------------+--------+------+---------------+------+---------+-------+------+-----------------------+

Change to fixed length After the field, key_len is

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

This article explains the calculation method of key_len in mysql explain. For more related content, please pay attention to the PHP Chinese website.

Related recommendations:

How to use curl to simulate ip and source for access through php

Convert NULL data through mysql

About PHP functions that use a variable number of parameters

The above is the detailed content of An explanation of the calculation method of key_len in mysql explain. For more information, please follow other related articles on the PHP Chinese website!