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