The Value Calculation Method of ken_len in the Query Plan of MySQL

  • 2021-07-03 00:59:13
  • OfStack

Meaning of key_len

In MySQL, you can view the path taken by the SQL statement through explain, 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)

Where key_len represents the length of the index used, in bytes. In the above example, key_len is 4 because the int type takes 4 bytes and the index contains only 1 column.

The following is the case for federated indexes:


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 federated index ix contains two columns, and both are used, so here ken_len is 8.

At this point, we can understand the meaning of key_len, and it seems that there is nothing to say, but there are still many places to pay attention to in the calculation of key_len in MySQL.

For example, we remove the NOT NULL constraint in column 1 of b, and then ken_len is not what we expected, 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)

key_len Calculation Rules in MySQL

In MySQL, the calculation rules of key_len are as follows:

If the column can be empty, add 1 on the basis of the bytes occupied by the data type, such as int type, which cannot be empty key_len is 4, but can be empty key_len is 5 If the column is variable length, add 2 to the base number of bytes occupied by the data column, such as varbinary (10). If it cannot be empty, key_len is 10 + 2, if it can be empty, key_len is 10 +2 +1 If it is a character type, the character set should also be considered. If the definition of a column is varchar (10) and utf8, which cannot be empty, then key_len is 10 * 3 + 2, and if it can be empty, key_len is 10*3 +2 +1 In addition, the calculation method of decimal column is the same as the above 1. If it can be empty, add 1 on the basis of the bytes occupied by the data type. However, the calculation of the number of bytes occupied by decimal itself is more complicated.

According to the official documents, decimal is defined as decimal (M, D), where M is the total number of digits and D is the number of digits reserved after the decimal point. The digits before and after the decimal point are stored separately, and are stored in 4 bytes in a group of 9 digits. If it is lower than 9 digits, the required bytes are 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 to the left of the decimal point and 6 to the right of the decimal point = > Group to the left of the decimal point is 5 + 9, which requires 3 bytes +4 bytes of storage, and group to the decimal point, which requires 3 bytes of storage = > A total of 10 bytes are required
decimal(18,9)= > 9 digits to the left of the decimal point, 9 digits to the right of the decimal point = > Use 4 bytes to store = > A total of 8 bytes are required
decimal(18,2)= > 16 digits to the left of the decimal point and 2 digits to the right of the decimal point = > Grouping is 7 + 9, which requires 8 bytes of storage and 1 byte to the right of decimal point = > A total of 9 bytes are required

Parsing the Federated Index by key_len

As shown below, we defined a table t, and the table t contains four columns of 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)

Now you want to execute the SQL statement 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), and we get the following output from explain:


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)

As you can see, the query statement uses the b and d columns in the federated index to filter the data.

If the union index we define is not ` ix_x (b, d, c) ` but ` ix_x (b, c, d) `, the input from 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     | ix_x | 5    | const |  2 | Using where; Using index |
+----+-------------+-------+------+---------------+------+---------+-------+------+--------------------------+
1 row in set (0.00 sec)

key_len is 5, which means that only the first column in the federated index is used. As you can see, although the federated index contains all the columns we want to query, the SQL statement does not make full use of the index due to the order of definition.


Related articles: