Shulou(Shulou.com)05/31 Report--

This article mainly introduces the use of key_len in the MySQL implementation plan, has a certain reference value, interested friends can refer to, I hope you can learn a lot after reading this article, the following let the editor take you to understand it.

In the past, when I looked at the implementation plan of MySQL, I felt that the content was somewhat crude, and the usual analysis was mainly to see whether the full table was scanned and whether the index was used reasonably. Basically, a lot of problems can be analyzed, but obviously sometimes there will be some confusion, that is, for the composite index, which index columns are enabled in the case of multiple column values, the use of the index is worth pondering at this time, and we have to make an important reference according to the key_len in the implementation plan.

Let's do a simple test to show.

CREATE TABLE `department` (

`DepartmentID` int (11) DEFAULT NULL

`DepartmentName` varchar (20) DEFAULT NULL

KEY `IND_ D` (`DepartmentID`)

KEY `DN` (`DepartmentName`)

) ENGINE=InnoDB DEFAULT CHARSET=gbk

Run the statement as: explain select count (*) from department\ G

How much is key_len for this statement?

Mysql > explain select count (*) from department\ G

* * 1. Row *

Id: 1

Select_type: SIMPLE

Table: department

Type: index

Possible_keys: NULL

Key: IND_D

Key_len: 5

Ref: NULL

Rows: 1

Extra: Using index

1 row in set (0.00 sec)

In this example, possible_keys,key,Extra you may be a little dizzy, let's see the value of key_len is 5, how to calculate this value, first of all, the table has two fields, the type of the first field is numeric, the length of int is 4, because the field can be null, so you need a byte to store, so it is 4 percent 1 percent 5. From this we can see that this statement enables the index ind_d.

Let's take a case in point and modify the sentence to see the changes in key_len.

Mysql > explain select departmentName from department b where departmentName='TEST'\ G

* * 1. Row *

Id: 1

Select_type: SIMPLE

Table: b

Type: ref

Possible_keys: IND_DN

Key: IND_DN

Key_len: 43

Ref: const

Rows: 1

Extra: Using where; Using index

1 row in set (0.09 sec)

As you can see from the above, key_len is 43, how is this value calculated? let's break it. Field 2 is character type, length 20, because it is the GBK character set, it needs to be multiplied by 2, because the allowed field is NULL, it needs one byte, and for the long type (in this case, VARCHAR), key_len also needs to add 2 bytes. So it's 20-2-1-2-43.

This is just the beginning, we need to look at a slightly more complex situation, we need a composite index. Let's just change the watch, test_keylen2.

Create table test_keylen2 (C1 int not null,c2 int not null,c3 int not null)

Alter table test_keylen2 add key idx1 (C1, c2, c3)

The following statement is very practical

Explain SELECT * from test_keylen2 WHERE C1 / 1 AND c2 / 1 ORDER BY C1\ G

In this statement, should keylen be 4, 8 or 12? We need to verify it.

Mysql > explain SELECT * from test_keylen2 WHERE C1 / 1 AND c2 / 1 ORDER BY C1\ G

* * 1. Row *

Id: 1

Select_type: SIMPLE

Table: test_keylen2

Type: ref

Possible_keys: idx1

Key: idx1

Key_len: 8

Ref: const,const

Rows: 1

Extra: Using index

1 row in set (0.07 sec)

Obviously, key_len only calculates the columns involved in where, and because it is a numeric type, it is 4-4-8.

What about the following sentence?

Explain SELECT * from test_keylen2 WHERE C1 > = 1 and c2o2\ G

Let's add a range and see how this should be split.

Mysql > explain SELECT * from test_keylen2 WHERE C1 > = 1 and c2o2\ G

* * 1. Row *

Id: 1

Select_type: SIMPLE

Table: test_keylen2

Type: index

Possible_keys: idx1

Key: idx1

Key_len: 12

Ref: NULL

Rows: 1

Extra: Using where; Using index

1 row in set (0.07 sec)

Here, not only the columns in the where are calculated, but because the condition > 1 directly selects three columns to calculate.

There is a slight difference in the handling of date types. Let's change to another table with fields of event type.

CREATE TABLE `tmp_ users` (

`id`int (11) NOT NULL

AUTO_INCREMENT

`uid` int (11) NOT NULL

`l _ date` datetime NOT NULL

`data`varchar (32) DEFAULT NULL

PRIMARY KEY (`id`)

KEY `ind_ uidldate` (`uid`, `ldate`)

) ENGINE=InnoDB DEFAULT CHARSET=gbk

How should the following statement key_len be calculated?

Explain select * from tmp_users where uid = 9527 and l_date > = '2012-12-10 10 purl 13 purl 17'\ G

This is unexpected to me. According to the impression of datetime, it is 8 bytes, so it should be 8 bytes, 4 bytes 12, but here it happens to be 9, how to calculate this number.

Id: 1

Select_type: SIMPLE

Table: tmp_users

Type: range

Possible_keys: ind_uidldate

Key: ind_uidldate

Key_len: 9

Ref: NULL

Rows: 1

Extra: Using index condition

1 row in set (0.07 sec)

A technical detail is involved here, which is the storage difference of datetime in MySQL 5.6. Before 5.6.4, it was 8 bytes, then 5 bytes.

So according to this algorithm, it is 4-5-9.

Thank you for reading this article carefully. I hope the article "what is the use of key_len in the MySQL implementation Plan" shared by the editor will be helpful to you. At the same time, I also hope you will support us and pay attention to the industry information channel. More related knowledge is waiting for you to learn!

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.