Key_len calculation of explain

Usually when optimizing SQL queries, we use explain to analyze SQL execution plans, usually when we use a composite index, how do we judge the index to be fully used? Of course, the master look at the table structure and SQL statements to know exactly how many fields to use, for unfamiliar students. We can still look at the length of the Key_len, of course, this calculation is still a bit complicated, but after you read my blog, I believe you will certainly calculate, this is hard not to be smart you.

Nonsense not much to say, we directly on the example. The table structure is as follows. ^_^

mysql [localhost] {msandbox} (Yayun) > Show create TABLE t1\g
1row ***************************
       Table:t1
Create table:create Table ' t1 ' (
  ' id ' int () unsigned not NULL auto_increment,
  ' name ' Cha R () NOT null default ',
  ' name1 ' char (#)DEFAULT NULL,
  ' name3 ' varcharnot NULL default ',
  PRIMARY key (' id '),
  key ' name ' (' name ')
engine=innodb DEFAULT Charset=utf8
 1 row in Set (0.00 sec)

The table structure above is very simple, there is a primary key index, that is, the ID field, there is a secondary index, that is, the Name field, we execute a SQL, and analysis of the implementation plan to see exactly how Key_len calculation.
There are 3 records in the table:

mysql [localhost] {msandbox} (Yayun) > select * from t1;
+----+-------+-------+-----------+
| id | name  | name1 | name3 |
+----+-------+-------+-----------+
|  1 | Atlas | Yayun | Dengyayun |
|  2 | Alex  | talex | Jalex     |
|  3 | Je    | jetom | tomje |
+----+-------+-------+-----------+
3 rows in Set (0.00 sec)

The following explain to see the length of the Key_len (here only to explain the Key_len calculation, other options refer to my previous blog)

mysql [localhost] {msandbox} (Yayun) > explain select * from T1 where name= ' Atlas ';
+----+-------------+-------+------+---------------+------+---------+-------+------+-----------------------+
| ID | Select_type | Table | Type | Possible_keys | Key  | key_len | ref   | rows | Extra                 |
+----+-------------+-------+------+---------------+------+---------+-------+------+-----------------------+
|  1 | Simple      | t1 |    ref  | name |                Const    | 1 | Using Index Condition |
+----+-------------+-------+------+---------------+------+---------+-------+------+-----------------------+
1 row in set (0.03 sec)

You can see that the length of the Key_len is 60, so how is this 60 calculated. Of course, if it is a single-column index we do not have to calculate, because there is no sense, if it is a combination of indexes, then know that the length of this is very meaningful, we first take a look at this single-column index of Key_len equals 60 is how to calculate.
Remember the definition of the Name field in my table structure earlier.

' Name ' char ' is not null DEFAULT ', I have defined char (20) and NOT NULL.

OK, now let's calculate, first of all, the UTF8 character set used in my table, then everyone knows that the UTF8 character set occupies 3 bytes, then I define char (20), and I know the result. You must know the wise.

Key_len=20*3=60

The calculation is simple, this situation is really simple, there are complex situation, hehe.

We continue to look at the next SQL, we remove the index of Name this field, add a joint index, key (NAME,NAME1)

mysql [localhost] {msandbox} (Yayun) > ALTER TABLE T1 drop key name;
0 rows affected (0.15 sec)
0  0  0

mysql [localhost] { Msandbox} (Yayun) > ALTER TABLE T1 add key idx_key_name_name1 (name,name1);
0 rows affected (0.29 sec)
0  0  0

Let's do another query:

mysql [localhost] {msandbox} (Yayun) > explain select * from T1 where name= ' Atlas '; +----+-------------+-------+------+--------------------+--------------------+---------+-------+------+--------- --------------+
| ID | Select_type | Table | Type | Possible_keys | Key | Key_len | Ref | Rows |
Extra | +----+-------------+-------+------+--------------------+--------------------+---------+-------+------+--------- --------------+
|1| Simple | T1 | Ref | idx_key_name_name1 | idx_key_name_name1 | A| Const |1|
Using Index Condition | +----+-------------+-------+------+--------------------+--------------------+---------+-------+------+--------- --------------+1Row in Set (0.00SEC) MySQL [localhost] {msandbox} (Yayun) > explain select * from T1 where name= ' Atlas ' and name1= ' Yayun '; +----+-------------+-------+------+--------------------+--------------------+---------+-------------+------+--- --------------------+
| ID | Select_type | Table | Type | Possible_keys | Key | Key_len | Ref | Rows |
Extra | +----+-------------+-------+------+--------------------+--------------------+---------+-------------+------+--- --------------------+
|1| Simple | T1 | Ref | idx_key_name_name1 | idx_key_name_name1 |121| Const,const |1|
Using Index Condition | +----+-------------+-------+------+--------------------+--------------------+---------+-------------+------+--- --------------------+1Row in Set (0.04SEC) MySQL [localhost] {msandbox} (Yayun) >

What's the difference between seeing the execution plan for the first query and the second one? Yes, Key_len and ref are different. Why And why the Key_len of the second SQL statement is 121, and how this is calculated. Hey, if you still use the above calculation method you certainly cannot calculate. Let me tell you. Remember the definition of the name1 field?
' Name1 ' char () DEFAULT NULL,

You can see that the Name1 field is defined as default NULL, and the others are unchanged. So MySQL needs 1 bytes to identify null,

So the second SQL Key_len=20 * 3 + (20 * 3 + 1) = 121, by calculation, we know that the index of 2 fields is fully used.

Let's take a look at the other things, add a field to the table, and add a federated index, and we'll go through a range of queries.

mysql [localhost] {msandbox} (Yayun) > ALTER TABLE t1 add add_time timestamp;
0 rows affected (1.44 sec)
0  0  0

mysql [localhost] {msandbox} (Yayun) > ALTER TABLE T1 add key Idx_key_add_time_name3 (ADD_TIME,NAME3);        
0 rows affected (0.19 sec)
0  0  0

Now the table structure is like this.

mysql [localhost] {msandbox} (Yayun) > Show create TABLE t1\g
1row ***************************
       Table:t1
Create table:create Table ' t1 ' (
  ' id ' int () unsigned not NULL auto_increment,
  ' name ' Cha R () NOT null default ',
  ' name1 ' char (#)DEFAULT NULL,
  ' name3 ' varcharnot Null default ',
  ' add_time ' timestamp not NULL default current_timestamp on UPDATE current_timestamp,
  PRIMARY K EY (' id '),
  key ' idx_key_name_name1 ' (' name ', ' name1 '),
  key ' Idx_key_add_time_name3 ' (' add_time ', ' Name3 ')
) Engine=innodb auto_increment=4 DEFAULT Charset=utf8
1 row in Set (0.01 sec)

Look at the SQL, don't say much nonsense.

mysql [localhost] {msandbox} (Yayun) > explain select * from t1 where add_time >= ' 2014-09-10 02:36:46 ' and Add_time
<= ' 2014-09-11 02:36:46 ' GROUP by name3 order by NULL; +----+-------------+-------+-------+------------------------+------------------------+---------+------+------+- ---------------------------------------+
| ID | Select_type | Table | Type | Possible_keys | Key | Key_len | Ref | Rows |
Extra | +----+-------------+-------+-------+------------------------+------------------------+---------+------+------+- ---------------------------------------+
|1| Simple | T1 | Range | Idx_key_add_time_name3 | Idx_key_add_time_name3 |4| NULL |2| Using index condition;
Using Temporary | +----+-------------+-------+-------+------------------------+------------------------+---------+------+------+- ---------------------------------------+1Row in Set (0.00SEC) MySQL [localhost] {msandbox} (Yayun) >

I can see the federated index Idx_key_add_time_name3 I created, but I really use it completely. In fact, it is not used at a glance, because the front is a range query, after the index of the field will not be used, if I do not order by NULL, you will also see the using Filesort. But I still want to talk about how Key_len is calculated, and we all know that timestamp occupies 4 bytes. So the answer is obvious, see Key_len is 4, the only use of the federated Index Idx_key_add_time_name3 in the Add_time field.

Let's take another look at a situation where a federated index of char and varchar fields is formed.

mysql [localhost] {msandbox} (Yayun) > ALTER TABLE T1 add key Idx_key_name1_name3 (NAME1,NAME3);
0 rows affected (0.27 sec)
0  0  0

SQL is as follows:

mysql [localhost] {msandbox} (Yayun) > explain select * from T1 where name1= ' Yayun ' and name3= ' Dengyayun ';
+----+-------------+-------+------+---------------------+---------------------+---------+-------------+------+- ----------------------+
| id | select_type | table | type | possible_keys |       key                 | key_len | ref         | rows