Mysql Index extends optimization

InnoDB extends the primary key columns by automatically adding them to each two-level index:

CREATE TABLET1 (I1INT  not NULL DEFAULT 0, I2INT  not NULL DEFAULT 0, D DATEDEFAULT NULL,  PRIMARY KEY(I1, i2),INDEXK_d (d)) ENGINE=InnoDB;

The table definition (T1,T2) is a federated primary key and also defines a level two index k_id on the column (d), but the internal InnoDB expands it to the column index (D,I1,I2);

Before version 5.6.9, the optimizer would not be so optimized, but at 5.6.9, starting with support, you can get better performance and a more efficient execution plan;

The optimizer can use an extended level two index to perform ref,range,index_merge such as type index access, loose index sacns,join connection and sort optimization, and min ()/max () optimization;

Data:

INSERT  intoT1VALUES(1,1,'1998-01-01'), (1,2,'1999-01-01'),(1,3,'2000-01-01'), (1,4,'2001-01-01'),(1,5,'2002-01-01'), (2,1,'1998-01-01'),(2,2,'1999-01-01'), (2,3,'2000-01-01'),(2,4,'2001-01-01'), (2,5,'2002-01-01'),(3,1,'1998-01-01'), (3,2,'1999-01-01'),(3,3,'2000-01-01'), (3,4,'2001-01-01'),(3,5,'2002-01-01'), (4,1,'1998-01-01'),(4,2,'1999-01-01'), (4,3,'2000-01-01'),(4,4,'2001-01-01'), (4,5,'2002-01-01'),(5,1,'1998-01-01'), (5,2,'1999-01-01'),(5,3,'2000-01-01'), (5,4,'2001-01-01'),(5,5,'2002-01-01');

Query sql:

SELECT COUNT (* fromWHERE=3and='2000-01-01  ';

In this case, the optimizer does not use the primary key, because the primary key is composed of (T1,T2), but the query does not reference i2; the optimizer chooses a level two index k_d (d), and the execution plan depends on whether the extension index is used;

When the optimizer does not use index extensions, he treats k_d only as (d).

Mysql>EXPLAINSELECT COUNT(*) fromT1WHEREI1= 3  andD= '2000-01-01'\g*************************** 1. Row***************************ID:1Select_type:simpleTable: T1 Type:refpossible_keys:PRIMARY, K_dKey: k_d Key_len:4ref:const rows:5extra:usingwhere; UsingIndex

When the optimizer takes the index extensions into account, it treats K_d (D,I1,I2), in which case he can use the leftmost prefix (D,I1) to get a better execution plan;

Mysql>EXPLAINSELECT COUNT(*) fromT1WHEREI1= 3  andD= '2000-01-01'\g*************************** 1. Row***************************ID:1Select_type:simpleTable: T1 Type:refpossible_keys:PRIMARY, K_dKey: k_d Key_len:8ref:const,const rows:1extra:usingIndex

In both cases, the key column display optimizer chooses to use the Level two index k-d, but:

The 1:key_len column has changed from 4bytes to 8 bytes, indicating that key is looking for columns D and I1, not just D;

The 2:rows column count is reduced from 5 to 1, indicating that InnoDB detects fewer rows to get the structure;

The 3:extra column changes from using Where;using Index to using index, which means that the result only uses index and no access data rows;

The optimizer uses the extension's index behavior differently or can be viewed by the show status command:

TABLE T1; FLUSH STATUS; SELECT COUNT (* fromWHERE=3and='2000-01-01  'like'handler_read% '

Flush table: Clears the table cache;

Flush status: Clears the status count;

No index extendsions,show Status:

+-----------------------+-------+|Variable_name|Value|+-----------------------+-------+|Handler_read_first| 0     ||Handler_read_key| 1     ||Handler_read_last| 0     ||Handler_read_next| 5     ||Handler_read_prev| 0     ||Handler_read_rnd| 0     ||Handler_read_rnd_next| 0     |+-----------------------+-------+

Have index extensions,show status:handler_read_next from 5 to 1

+-----------------------+-------+|Variable_name|Value|+-----------------------+-------+|Handler_read_first| 0     ||Handler_read_key| 1     ||Handler_read_last| 0     ||Handler_read_next| 1     ||Handler_read_prev| 0     ||Handler_read_rnd| 0     ||Handler_read_rnd_next| 0     |+-----------------------+-------+

The use_index_extensions flag of the Optimizer_switch system variable can control whether the optimizer does a two-level index extension, which, by default, is open,

SET = ' Use_index_extensions=off ';

Mysql Index extends optimization