How to calculate the value of ken_len in MySQL query plan

The meaning of key_len

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

Among them, key_len represents the index length used, which is in bytes. In the above example, since the int type occupies 4 bytes and the index contains only one column, key_len is 4.

Here is what a joint index looks like:

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

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

For example, if we remove the NOT NULL constraint of column b, ken_len will be different from what we expected, 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)

Calculation rules for key_len in MySQL

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

• If the column can be empty, add 1 to the bytes occupied by the data type. For example, if the int type cannot be empty, key_len is 4, and if it 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. For example, if varbinary(10) cannot be empty, then key_len is 10 + 2. If it can be empty, then key_len is 10+2+1.
• If it is a character type, you also need to consider the character set. For example, if a column is defined as varchar(10) and is utf8, and cannot be empty, then key_len is 10 * 3 + 2. If it can be empty, then key_len is 10*3+2+1.
• In addition, the calculation method of the decimal column is the same as above. If it can be empty, add 1 to the bytes occupied by the data type. However, the calculation of the number of bytes occupied by the decimal itself is more complicated.

According to official documents, 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 digits before and after the decimal point are stored separately, and 9 digits are grouped as one, using 4 bytes to store. If the number is less than 9 digits, the number of bytes required 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 to the left of the decimal point, 6 digits to the right of the decimal point => The digits to the left of the decimal point are grouped into 5 + 9, requiring 3 bytes + 4 bytes to store, and the decimal point is grouped into 3 bytes to store => 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 => 4 bytes are used to store each => 8 bytes in total
decimal(18,2) => 16 digits to the left of the decimal point, 2 digits to the right of the decimal point => grouped as 7 + 9, requiring 8 bytes to store, 1 byte to the right of the decimal point => a total of 9 bytes required

Analyze joint indexes by key_len

As shown below, we define a table t, which contains 4 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)

Now the SQL statement to be executed 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), and we get the following output through 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 joint index to filter the data.

If the joint index we define is not `ix_x(b, d, c)`, but `ix_x(b, c, d)`, the input obtained by 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, that is, only the first column in the joint index is used. It can be seen that although the joint index contains all the columns we want to query, due to the definition order, the SQL statement cannot make full use of the index.