The meaning of Ken_len in the MySQL query plan

This paper first introduces the meaning of Ken_len in the query plan of MySQL, then introduces the calculation method of Key_len, and finally, through a fake example, shows how to see how many columns of the federated index are used by Key_len.

The meaning of Key_len

In MySQL, you can view the path to the SQL statement through explain, as shown here:

1. 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 indicates the index length used , in bytes. In the above example, because the int type occupies 4 bytes, and the index contains only 1 columns, the Key_len is 4.

The following is the case of a federated index:

1. 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 2 columns, and all are used, so here Ken_len is 8.

Here, we can already understand the meaning of key_len, there seems to be nothing to talk about, but, MySQL in the calculation of Key_len there are many areas to be aware of.

For example, we remove the NOT NULL constraint for this column of B, and then the Ken_len is not the same as we expected, as follows:

1. 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, Key_len's calculation rules are as follows:

1. If the column can be empty, add 1 on the basis of the data type taking up bytes, such as int, cannot be empty Key_len is 4, can be empty Key_len is 5
2. If the column is longer, then add 2 to the base of the byte in the data column, such as varbinary (10), cannot be empty, then Key_len is 2 and can be empty key_len to 10+2+1
3. If the character type, you also need to consider the character set, such as a column is defined as varchar (10), and is UTF8, cannot be empty, then Key_len is 10 * 3 + 2, can be empty key_len to 10*3+2+1

In addition, the decimal column is calculated as the above, and if it can be empty, add 1 on the basis of the data type taking up bytes, but the number of bytes consumed by the decimal itself is more complex.

According to official documentation, decimal is defined as decimal (m,d), where M is the total number of digits and D is the number of digits left after the decimal point. Before the decimal point and the number after the decimal point is stored separately, and in 9-digit 1 groups, with 4 bytes to save, if less than 9 digits, the number of bytes required is as follows:

1. Leftover Digits number of Bytes
2. -----------------------------
3. |
4. |1-2 |
5. |3-4 |
6. |5-6 |3 |
7. |7-9 |4 |
8. -----------------------------

For example:

• Decimal (20,6) = 14 digits to the left, 6 digits to the right of the decimal point = 5 + 9 for the left of the decimal point, 3 bytes + 4 bytes to store, one for the decimal point, and 3 bytes for storage. = 10 Bytes Total Required
• Decimal (18,9) + 9 digits to the left of the decimal point, 9 digits to the right of the decimal point = 4 bytes of storage and 8 bytes in total
• Decimal (18,2) = 16 digits to the left of the decimal point, 2 digits to the right of the decimal number = 7 + 9, 8 bytes to storage, 1 bytes to the right of the decimal point = 9 bytes in total

Analyzing federated indexes with Key_len

As shown below, we define a table T with a table t containing a, B, C, and D total of 4 columns:

1. Mysql> Show CREATE TABLE T\g
   1. Row ***************************
   Table:t
   Create table:create Table ' t ' (
   ' A ' int (one) is not NULL,
   ' B ' int (one) DEFAULT NULL,
   ' C ' int (one) DEFAULT NULL,
   ' d ' int (one) DEFAULT NULL,
   PRIMARY KEY (' a '),
   KEY ' ix_x ' (' B ', ' d ', ' C ')
   ) Engine=innodb DEFAULT Charset=utf8
   1 row in Set (0.00 sec)
   
   

Now execute the SQL statement as follows:
Select a from t where B = 5 and D = ten order by C;

Let's say we have an index ix_x (b,d,c), and we get the following output through explain:

1. Mysql> explain select a from t where B = 5 and D = ten 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 two columns in the Federated index to filter the data.

If the federated index we define is not ' ix_x (b,d,c) ', but ' ix_x (b, C, D) ', the input obtained through explain is as follows:

1. 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 = ten 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 Federated Index is used, as you can see, although the Federated Index contains all the columns we want to query, the SQL statement is not able to take full advantage of the index because of the order of the definitions.

The meaning of Ken_len in the MySQL query plan