Shulou(Shulou.com)06/01 Report--

This paper first introduces the meaning of ken_len in MySQL's query plan, then introduces the calculation method of key_len, and finally uses a fake example to illustrate how to use key_len to see how many columns are used in the federated index.

The meaning of key_len

In MySQL, you can view the path taken by the SQL statement through explain, as follows:

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

Here is what the federated index looks like:

Key_len calculation rules 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, such as int, key_len is 4, key_len is 5. If the column is long, add 2 to the base of bytes occupied by the data column, such as varbinary (10), which cannot be empty, then key_len is 10: 2, if it can be empty, key_len is 10: 2. If it is a character type, you also need to consider the character set, such as the definition of a column is varchar (10). If it is utf8, it cannot be empty, then key_len is 10 percent 3 percent 2, and if it can be empty, the key_len is 10 percent 3 percent 2 percent 1 (why multiply by 3? Because we are using utf8 equivalent utf8-mb3 encoding, which takes up three bytes of space)

In addition, the decimal column is calculated the same way as above, adding 1 to the bytes occupied by the data type if it can be empty, but calculating the number of bytes occupied by the decimal itself is more complex.

As you can see from the official documentation, decimal is defined as decimal, where M is the total number of digits and D is the number of digits retained after the decimal point. The numbers before and after the decimal point are stored separately, and a group of 9 digits is saved with 4 bytes. If it is less than 9 digits, the number of bytes required is as follows:

For example:

Decimal (20Magazine 6) = > 14 digits to the left of the decimal point, 6 digits to the right of the decimal point = > packet to the left of the decimal point is 5 + 9, requires 3 bytes + 4 bytes to store, 1 packet to the decimal point, requires 3 bytes storage = > needs a total of 10 bytes decimal (18Magazine 9) = > 9 digits to the left of the decimal point, 9 digits to the right of the decimal point = > uses 4 bytes storage respectively = > requires a total of 8 bytes decimal (18Magin2) = > 16 digits to the left of the decimal point 2 digits to the right of the decimal point = > grouped to 7 + 9, requiring 8 bytes of storage, 1 byte storage to the right of the decimal point = > a total of 9 bytes

Analysis of federated index by key_len

As shown below, we define a table t, which contains four columns a, b, c, and d:

Now the SQL statement will be executed as follows:

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

Suppose we have an index ix_x (bmae dje c) and get the following output through explain:

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

If the federated index defined by us is not `ix_x (bdjinc) `, but `federated index (b, c, d)`, the input obtained through explain is as follows:

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

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.