Question about whether the two-level index of MySQL InnoDB table joins the primary key column

• For MySQL InnoDB table Two-level index whether to join the primary key, summarized as follows:

1 for the MySQL InnoDB table two level index whether to join the primary key, the official also has the explicit explanation, recommends that the line MySQL two level index creates the column which the primary key is forced to join , can do all MySQL version unification.

Before 2.MySQL 5.6.9, the InnoDB engine layer would automatically extend the level two index, but the optimizer could not recognize the extended primary key.

3.MySQL 5.6.9 start InnoDB the engine layer will automatically extend the level two index, and the optimizer will recognize the extended primary key.

4. the size of the index, the level two index is not joined the primary key column, the InnoDB engine Layer Two index will automatically expand the primary key, this is not related to the version.

5. With or without a primary key column, the two-level index has the same organizational structure and physical size, because the organizational structure is the same at the storage engine level.

6. At the optimizer level, the 5.6.9 is not recognized as an auto-extended primary key column, and the switch from the 5.6.9 start optimizer use_index_extensions=on is a primary key column that can recognize the extension . so it is advantageous to join the primary key column in a Level two index. This can also be done regardless of version, so that all MySQL versions are unified.

Summary: Garther column, beneficial harmless.

* Here is my demo example:

One. The following is the MySQL 5.5.36-log:

XXX 5.5.36-log test 11:33:54>create TABLE T1 (
-I1 INT not NULL DEFAULT 0,
-I2 INT not NULL DEFAULT 0,
-D DATE DEFAULT NULL,
-PRIMARY KEY (I1, I2),
-INDEX K_d (d)
) ENGINE = InnoDB;
Query OK, 0 rows affected (0.07 sec)

After inserting 25 rows of data:

XXXX 5.5.36-log test 11:40:01>explain SELECT COUNT (*) from t1 WHERE i1 = 3 and D = ' 2000-01-01 ' \g
1. Row ***************************
Id:1
Select_type:simple
Table:t1
Type:ref
Possible_keys:primary,k_d
Key:k_d
Key_len: 4
Ref: Const
Rows: 5
Extra: Using where; Using Index

Analysis: Key_len is 4, only the I1 column (int type key length is 4byte) does not have an extended primary key. Ref: There is only one const: indicates that the optimizer only uses the I1 column. Using Where: The table has been returned.

*************************************************************************************************************** *********************************

Below I add the index: ' k_d_2 ' (D,I1,I2)

ALTER TABLE T1 add key ' K_d_2 ' (D,I1,I2)     ;

XXX 5.5.36-log Test 11:36:11>explain SELECT COUNT (*) from t1 WHERE i1 = 3 and D = ' 2000-01-01 ' \g

1. Row ***************************
Id:1
Select_type:simple
Table:t1
Type:ref
Possible_keys:primary,k_d,k_d_2
Key: k_d_2
Key_len: 8
Ref: Const,const
Rows: 1
Extra: Using where; Using Index

Analysis: key: k_d_2 and Key_len are 8, indicating the extended primary key. Ref: There are 2 const: Indicates that the optimizer is in the I1 column. Rows: 1 also shows that the primary key is used.

Two. At the same time I did the same table in MySQL 5.6.16-log:

lxxx 5.6.16-log test 08:20:46>show variables like '%optimizer_switch% ';

Firstmatch=on,subquery_materialization_cost_based=on,use_index_extensions=on....

Use_index_extensions has been opened.

XXX 5.6.16-log test 08:20:46>create TABLE T1 (

   ->   I1 int not null DEFAULT 0,
   ->   i2 int NOT NULL Default 0,
   ->   D DATE DEFAULT NULL,
   ->   PRIMARY KEY (I1, I2),
   ->   index K_d (d)
   ) ENGINE = InnoDB;
Query OK, 0 rows Affected (0.00 sec)

XXX 5.6.16-log Test 08:21:04>explain SELECT COUNT (*) from t1 WHERE i1 = 3 and D = ' 2000-01-01 ' \g
**************** 1. Row ***************************
           id:1
  Select_type:simple
        table:t1
          type:ref
Possible_keys:primary,k_d
           Key: k_d
      Key_len: 8
          ref:  Const,const
         rows:  1
        extra: using index

Analysis: key: k_d_2 and Key_len are 8, indicating that MySQL automatically expands the primary key for the two-level index . Ref: There are 2 const: Indicates that the optimizer recognizes the extended primary key .

Three. Index size:

With a primary key column followed by a level two index, storage space is not incremented.

Here is my analysis:

One. The following is MySQL 5.6.16:

CREATE TABLE ' T1 ' (
' I1 ' int (one) not NULL DEFAULT ' 0 ',
' I2 ' int (one) not NULL DEFAULT ' 0 ',
' d ' date is DEFAULT NULL,
PRIMARY KEY (' I1 ', ' i2 '),
KEY ' k_d ' (' d ')
) Engine=innodb DEFAULT Charset=utf8 Collate=utf8_bin;

CREATE TABLE ' Tt1 ' (
' I1 ' int (one) not NULL DEFAULT ' 0 ',
' I2 ' int (one) not NULL DEFAULT ' 0 ',
' d ' date is DEFAULT NULL,
PRIMARY KEY (' I1 ', ' i2 '),
KEY ' k_d ' (' d ', ' i1 ', ' i2 ')
) Engine=innodb DEFAULT Charset=utf8 Collate=utf8_bin;

To insert data into a table through a stored procedure:

Call Proc_insert (500000); Insert 50w rows of data:

The following is the size of the index, the same size:

XXX Test 03:38:36>select index_length from INFORMATION_SCHEMA. TABLES WHERE table_schema= ' test ' and table_name= ' T1 ';
+--------------+
| Index_length |
+--------------+
| 8929280 |
+--------------+
1 row in Set (0.00 sec)
XXX 5.6.16-log test 03:43:42>select index_length from INFORMATION_SCHEMA. TABLES WHERE table_schema= ' test ' and table_name= ' tt1 ';
+--------------+
| Index_length |
+--------------+
| 8929280 |
+--------------+
1 row in Set (0.01 sec)

The size of the data file is the same as the size:

-RW-------1 mysql myinstall 36M January 15:38 t1.ibd
-RW-------1 mysql myinstall 36M January 15:39 tt1.ibd

Two. The following is MySQL 5.5.36:

Table T1, TT1 and the above structure are consistent.

Index size:

XXX 5.5.36-log (none) 03:48:05>select index_length from INFORMATION_SCHEMA. TABLES WHERE table_schema= ' test ' and table_name= ' T1 ';
+--------------+
| Index_length |
+--------------+
| 8929280 |
+--------------+
1 row in Set (0.00 sec)

xxx 5.5.36-log (none) 03:48:06>select index_length from INFORMATION_SCHEMA. TABLES WHERE table_schema= ' test ' and table_name= ' tt1 ';

+--------------+
| Index_length |
+--------------+
| 8929280 |
+--------------+
1 row in Set (0.00 sec)

Data file size: Same as

-RW-RW----. 1 MySQL myinstall 36M January 15:39 tt1.ibd
-RW-RW----. 1 MySQL myinstall 36M January 15:39 t1.ibd

This article is from the My DBA life blog, so be sure to keep this source http://huanghualiang.blog.51cto.com/6782683/1607496

Question about whether the two-level index of MySQL InnoDB table joins the primary key column