Shulou(Shulou.com)06/01 Report--

This article mainly introduces the calculation of mysql explain key_len process analysis, the contents of the article are carefully selected and edited by the author, the calculation of mysql explain key_len process analysis has a certain pertinence, for everyone's reference significance or relatively great, the following with the author to understand the next topic content.

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` (NULL, 'fdipzone',' 18'), (NULL, 'jim',' 19'), (NULL, 'tom',' 19')

two。 View explain

The field type of name is varchar (20), the character encoding is utf8, and a character takes up 3 bytes, so the 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, an increase of 3.

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 the key_len is 62, which is 1 less than just now, but still 2 more. You can be sure that a field of NULL takes up an extra byte.

Name field type is varchar, which belongs to variable length field. Change varchar to char and retest.

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 the fixed length field, the 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, so for indexed fields, it is best to use fixed length and NOT NULL definitions to improve performance.

After reading the above analysis of the key_len process in computing mysql explain, many readers must have some understanding. If you need to get more industry knowledge and information, you can continue to follow our industry information column.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.