| |
This paper introduces the MySQL query plan ken_len meaning; then introduces the calculation method key_len; and finally through a fake example to illustrate how key_len joint index to see how many columns are used.
key_len meaning
In MySQL, can be viewed explain SQL statements walking paths, as follows:
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)
Wherein, key_len index represents the length of use, in bytes. In the above example, since the int type take 4 bytes, and the index contains only one, therefore, key_len 4.
Here is the joint 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)
Ix joint index contains two, and used to, so here is ken_len 8.
Here, we are able to understand the meaning of key_len, it seems to have nothing to talk about, but, MySQL key_len in computing, there are many areas requiring attention.
For example, we will b NOT NULL constraints of this column is removed, and then we would expect ken_len not the same, as follows:
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)
MySQL in key_len calculation rules
In MySQL, key_len calculation rules are as follows:
1. If the column is empty, then applied to the data type uses 1 byte basis, such as int type, can not be empty key_len 4, 5 can be empty key_len
2. If the column is variable length, the column on the data base percentage plus 2 bytes, as varbinary (10), can not be empty, key_len 10 + 2 can is empty key_len 10 + 2 +1
3. If the character is, you also need to consider character sets, such as the definition of a column is varchar (10), and is utf8, not empty, then key_len 10 * 3 + 2 can is empty key_len 10 * 3 + 2 + 1
In addition, the calculation method decimal column as above, if you can is empty, then applied to the data type uses 1 byte basis, however, the number of bytes occupied itself decimal calculation is more complicated.
According to official documents can know, decimal is defined as decimal (M, D), where, M is the total number of bits, D digits after the decimal point. Before decimal digit after the decimal point and stored separately, and the median of a group of 9 to save 4 bytes, if less than 9 digits, the number of bytes required are as follows:
Leftover Digits Number of Bytes
-----------------------------
| 0 | 0 |
| 1-2 | 1 |
| 3-4 | 2 |
| 5-6 | 3 |
| 7-9 | 4 |
-----------------------------
E.g:
• decimal (20,6) => the left of the decimal point 14, the right of the decimal point 6 => left of the decimal grouped into 5 + 9, requires 3 bytes + 4 bytes of storage, a decimal point packet requires 3 bytes of storage => total of 10 bytes
• decimal (18,9) => 9 left of the decimal digits, 9 digits to the right of the decimal point =>, respectively, using 4 bytes of storage => requires a total of 8 bytes
• decimal (18,2) => 16 digits left of the decimal, 2 digits to the right of the decimal point => Group of 7 + 9, requiring 8 bytes of storage, the right of the decimal point one byte memory => requires a total 9 bytes
Joint analysis by key_len index
As shown below, we define a table t, t table contains a, b, c, d total of four:
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)
Now we want to execute a SQL statement is as follows:
select a from t where b = 5 and d = 10 order by c;
Suppose we have an index ix_x (b, d, c), by explain get the following output:
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 | ix_x | 10 | const, const | 1 | Using where; Using index |
+ ------------- + ---- + ------- + ------ + --------------- + ------------- + --------- + ------ + ------ + ----------- --------------- +
1 row in set (0.00 sec)
You can see, the query using the joint index b and d two to filter the data.
If we define the joint index is not `ix_x (b, d, c)`, but `ix_x (b, c, d)`, obtained by explain the following entries:
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 | ix_x | 5 | const | 2 | Using where; Using index |
+ ------------- + ---- + ------- + ------ + --------------- + --------- + ------- + ------ + ------ + ------ --------- +
1 row in set (0.00 sec)
key_len 5, that is, only use the joint index in the first column, you can see that although the joint index contains all the columns that we want to query, but, since the order of the problem definition, SQL statements do not take full advantage of index. |
|
|
|