An explanation of the calculation method of key_len in mysql explain-Mysql Tutorial-php.cn

An explanation of the calculation method of key_len in mysql explain

jacklove

Release： 2018-06-08 17:51:23

Original

1977 people have browsed it

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, &#39;fdipzone&#39;, &#39;18&#39;), (NULL, &#39;jim&#39;, &#39;19&#39;), (NULL, &#39;tom&#39;, &#39;19&#39;);

Copy after login

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=&#39;fdipzone&#39;;
+----+-------------+--------+------+---------------+------+---------+-------+------+-----------------------+| 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 |
+----+-------------+--------+------+---------------+------+---------+-------+------+-----------------------+

Copy after login

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=&#39;fdipzone&#39;;
+----+-------------+--------+------+---------------+------+---------+-------+------+-----------------------+| 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 |
+----+-------------+--------+------+---------------+------+---------+-------+------+-----------------------+

Copy after login

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=&#39;fdipzone&#39;;
+----+-------------+--------+------+---------------+------+---------+-------+------+-----------------------+| 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 |
+----+-------------+--------+------+---------------+------+---------+-------+------+-----------------------+

Copy after login

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!