A brief discussion on the calculation method of key_len in mysql explain
|
The MySQL explain command can analyze the performance of SQL, one of which is the key_len (index length) statistic. This article will analyze the calculation method of key_len in MySQL explain. 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` (`id`, `name`, `age`) VALUES (NULL, 'fdipzone', '18'), (NULL, 'jim', '19'), (NULL, 'tom', '19'); 2. View explain The name field type is varchar(20) , the character encoding is utf8 , and one character occupies 3 bytes, so 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 , which is 3 more. 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 key_len is 62, which is 1 less than before, but still 2 more. It is certain that a NULL field will occupy one extra 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 | +----+-------------+--------+------+---------------+------+---------+-------+------+-----------------------+ After changing to a fixed-length field, 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. Therefore, for indexed fields, it is best to use fixed-length and NOT NULL definitions to improve performance. The above brief discussion on the calculation method of key_len in MySQL explain is all the content that the editor shares with you. I hope it can give you a reference. I also hope that you will support 123WORDPRESS.COM. You may also be interested in:
|
<<: Example of how to configure multiple virtual hosts in nginx
>>: Detailed explanation of js closure and garbage collection mechanism examples
Recommend
Linux kernel device driver character device driver notes
/******************** * Character device driver**...
An article to help you learn more about JavaScript arrays
Table of contents 1. The role of array: 2. Defini...
Causes and solutions for slow MySQL queries
There are many reasons for slow query speed, the ...
Two ways to install Python3 on Linux servers
First method Alibaba Cloud and Baidu Cloud server...
The vue configuration file automatically generates routing and menu instance code
Table of contents Written in front router.json Ro...
Storage engine and log description based on MySQL (comprehensive explanation)
1.1 Introduction to storage engines 1.1.1 File sy...
Vue project packaging, merging and compression to optimize web page response speed
Table of contents Preface 1. The request content ...
Perfect solution to MySQL common insufficient memory startup failure
1. If MySQL is not started successfully, check th...
Analysis of Vue element background authentication process
Preface: Recently, I encountered a management sys...
JavaScript implements the pot-beating game of Gray Wolf
1. Project Documents 2. Use HTML and CSS for page...
Nginx+FastDFS to build an image server
Installation Environment Centos Environment Depen...
Common front-end JavaScript method encapsulation
Table of contents 1. Enter a value and return its...
43 Web Design Mistakes Web Designers Should Watch Out For
This is an article about website usability. The a...
Detailed analysis of the blocking problem of js and css
Table of contents DOMContentLoaded and load What ...
Detailed explanation of MySQL three-value logic and NULL
Table of contents What is NULL Two kinds of NULL ...