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 isutf8, one character occupies 3 bytes, then key_len should be20*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 is3.
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 additional2bytes, using NULL requires an additional1bytes,Therefore, for indexed fields, it is best to use fixed length and NOT NULL definitionsto 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!