MySQL query plan key_len all know

Source: Internet
Author: User
Tags mysql query

MySQL query plan key_len all know

This article first introduces the meaning of ken_len In the MySQL query plan, then introduces the calculation method of key_len, and finally uses a forged example, to show how to use key_len to view the number of columns used by the Union index.

Description of key_len

In MySQL, you can use explain to view the path of the SQL statement, as shown below:

Mysql> create table t (a int primary key, B int not null, c int not null, index (B ));
Query OK, 0 rows affected (0.01 sec)
Mysql> explain select B from t;
+ ---- + ------------- + ------- + --------------- + ------ + --------- + ------ + ------------- +
| Id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+ ---- + ------------- + ------- + --------------- + ------ + --------- + ------ + ------------- +
| 1 | SIMPLE | t | index | NULL | B | 4 | NULL | 1 | Using index |
+ ---- + ------------- + ------- + --------------- + ------ + --------- + ------ + ------------- +
1 row in set (0.00 sec)

The key_len indicates the index length, in bytes. In the above example, because the int type occupies 4 bytes and the index only contains 1 column, the key_len is 4.

The following figure shows the combined index:

Mysql> alter table t add index ix (B, c );
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
Mysql> explain select B, c from t;
+ ---- + ------------- + ------- + --------------- + ------ + --------- + ------ + ------------- +
| Id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+ ---- + ------------- + ------- + --------------- + ------ + --------- + ------ + ------------- +
| 1 | SIMPLE | t | index | NULL | ix | 8 | NULL | 1 | Using index |
+ ---- + ------------- + ------- + --------------- + ------ + --------- + ------ + ------------- +
1 row in set (0.00 sec)

The associated index ix contains two columns and is used. Therefore, here ken_len is 8.

So far, we can understand the meaning of key_len, and there seems to be nothing to talk about. However, there are still many things to note about key_len computing in MySQL.

For example, we remove the not null constraint of Column B, and then ken_len is different from our expectation, as shown below:

Mysql> alter table t modify B int;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
 
Mysql> explain select B from t;
+ ---- + ------------- + ------- + --------------- + ------ + --------- + ------ + ------------- +
| Id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+ ---- + ------------- + ------- + --------------- + ------ + --------- + ------ + ------------- +
| 1 | SIMPLE | t | index | NULL | B | 5 | NULL | 1 | Using index |
+ ---- + ------------- + ------- + --------------- + ------ + --------- + ------ + ------------- +
1 row in set (0.00 sec)
 

Key_len calculation rules in MySQL

In MySQL, the calculation rules of key_len are as follows:
1. If the column can be empty, add 1 to the byte occupied by the data type, for example, int type. The key_len cannot be empty and the key_len can be empty.
2. if the column is variable, add 2 to the base of the byte occupied by the data column, such as varbinary (10). If the value of key_len is 10 + 2, if it can be null, key_len is 10 + 2 + 1
3. if it is character type, you also need to consider the character set. For example, if a column is defined as varchar (10) and utf8 and cannot be blank, then key_len is 10*3 + 2, if it can be null, key_len is 10*3 + 2 + 1.

In addition, the calculation method of the decimal column is the same as that above. If it can be null, 1 is added based on the bytes occupied by the data type. However, the number of bytes occupied by the decimal column itself, computing is complicated.

According to the official documentation, decimal is defined as decimal (M, D), where M is the total number of digits, and D is the number of digits retained after the decimal point. The values are stored separately before the decimal point and after the decimal point. The values are in a group of 9-digit numbers and are saved in 4 bytes. If the number is smaller than 9 digits, the number of bytes is as follows:

Leftover Digits Number of Bytes
-----------------------------
| 0 | 0 |
| 1-2 | 1 |
| 3-4 | 2 |
| 5-6 | 3 |
| 7-9 | 4 |
-----------------------------

For example:
• Decimal (20, 6) => 14 digits on the left of the decimal point, 6 digits on the right of the decimal point => the group on the left of the decimal point is 5 + 9, which must be stored in 3 bytes + 4 bytes and one decimal point group, 3 bytes required for storage => 10 bytes in total
• Decimal () => 9 digits to the left of the decimal point, 9 digits to the right of the decimal point => 4 bytes for storage respectively => A total of 8 bytes are required
• Decimal () => 16 digits to the left of the decimal point, 2 digits to the right of the decimal point => the group is 7 + 9 and needs to be stored in 8 bytes, 1 byte storage to the right of the decimal point => A total of 9 bytes are required

Joint Index analysis through key_len

As shown in the following figure, we define a table t, which contains four columns: a, B, c, and d:

Mysql> show create table t \ G
* *************************** 1. row ***************************
Table: t
Create Table: create table 'T '(
'A' int (11) not null,
'B' int (11) DEFAULT NULL,
'C' int (11) default null,
'D' int (11) default null,
Primary key ('A '),
KEY 'ix _ x' ('B', 'D', 'C ')
) ENGINE = InnoDB default charset = utf8
1 row in set (0.00 sec)

Execute the following SQL statement:

Select a from t where B = 5 and d = 10 order by c;

Suppose we have an index ix_x (B, d, c) and get the following output through the explain statement:

Mysql> explain select a from t where B = 5 and d = 10 order by c;
+ ---- + ------------- + ------- + ------ + --------------- + ------ + --------- + ------------- + ------ + -------------------------- +
| Id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+ ---- + ------------- + ------- + ------ + --------------- + ------ + --------- + ------------- + ------ + -------------------------- +
| 1 | SIMPLE | t | ref | ix_x | 10 | const, const | 1 | Using where; Using index |
+ ---- + ------------- + ------- + ------ + --------------- + ------ + --------- + ------------- + ------ + -------------------------- +
1 row in set (0.00 sec)

We can see that the query statement uses the B and d columns in the joint index to filter data.

If the joint index we define is not 'ix _ x (B, d, c) ', but 'ix _ x (B, c, d )', the input obtained by using explain is as follows:

Mysql> alter table t drop index ix_x;
Mysql> alter table t add index ix_x (B, c, d );
Mysql> explain select a from t where B = 5 and d = 10 order by c;
+ ---- + ------------- + ------- + ------ + --------------- + ------ + --------- + ------- + ------ + -------------------------- +
| Id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+ ---- + ------------- + ------- + ------ + --------------- + ------ + --------- + ------- + ------ + -------------------------- +
| 1 | SIMPLE | t | ref | ix_x | 5 | const | 2 | Using where; Using index |
+ ---- + ------------- + ------- + ------ + --------------- + ------ + --------- + ------- + ------ + -------------------------- +
1 row in set (0.00 sec)

Key_len is 5, that is to say, only the first column in The Union index is used. We can see that although the Union Index contains all the columns to be queried, due to the definition order, SQL statements cannot fully utilize indexes.

This article permanently updates the link address:

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.