Index Extensions含义MySQL5.6开始 InnoDB可以通过主键自动扩展二级索引的功能称为Index Extensions，即二级索引除了存储本列索引的key值外，还存储着主键列ke
Index Extensions含义
MySQL5.6开始 InnoDB可以通过主键自动扩展二级索引的功能称为Index Extensions，即二级索引除了存储本列索引的key值外，还存储着主键列key值。
创建如下测试表
mysql> show create table t1/G*************************** 1. row *************************** Table: t1Create Table: CREATE TABLE `t1` ( `i1` int(11) NOT NULL DEFAULT '0', `i2` int(11) NOT NULL DEFAULT '0', `d` date DEFAULT NULL, PRIMARY KEY (`i1`,`i2`), KEY `k_d` (`d`)) ENGINE=InnoDB DEFAULT CHARSET=utf81 row in set (0.00 sec)mysql> INSERT INTO t1 VALUES -> (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 OK, 25 rows affected (0.08 sec)Records: 25 Duplicates: 0 Warnings: 0
其中PK是(i1,i2)，二级索引是(d)，但是InnoDB会自动扩展这个二级索引，变成(d,i1,i2)，这样对于一些特殊的查询，可以提高性能；
开启index extensions,并测试使用该功能
即：use_index_extensions=on
mysql> show variables like 'optimizer_switch%'/G*************************** 1. row ***************************Variable_name: optimizer_switch Value: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on, block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=on1 row in set (0.00 sec)mysql> FLUSH TABLE t1;Query OK, 0 rows affected (0.02 sec)mysql> FLUSH STATUS;Query OK, 0 rows affected (0.01 sec)mysql> SELECT COUNT(*) FROM t1 WHERE i1 = 3 AND d = '2000-01-01';+----------+| COUNT(*) |+----------+| 1 |+----------+1 row in set (0.00 sec)mysql> SHOW STATUS LIKE 'handler_read%';+-----------------------+-------+| 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 |+-----------------------+-------+7 rows in set (0.00 sec)mysql> desc SELECT COUNT(*) FROM t1 WHERE i1 = 3 AND d = '2000-01-01';+----+-------------+-------+------------+------+---------------+------+---------+-------------+------+----------+-------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+-------+------------+------+---------------+------+---------+-------------+------+----------+-------------+| 1 | SIMPLE | t1 | NULL | ref | PRIMARY,k_d | k_d | 8 | const,const | 1 | 100.00 | Using index |+----+-------------+-------+------------+------+---------------+------+---------+-------------+------+----------+-------------+1 row in set, 1 warning (0.00 sec)
关闭index extensions,并测试没有使用该功能
即：use_index_extensions=off
mysql> set session optimizer_switch='use_index_extensions=off';Query OK, 0 rows affected (0.00 sec)mysql> FLUSH TABLE t1;Query OK, 0 rows affected (0.02 sec)mysql> FLUSH STATUS;Query OK, 0 rows affected (0.02 sec)mysql> SELECT COUNT(*) FROM t1 WHERE i1 = 3 AND d = '2000-01-01';+----------+| COUNT(*) |+----------+| 1 |+----------+1 row in set (0.00 sec)mysql> SHOW STATUS LIKE 'handler_read%';+-----------------------+-------+| 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 |+-----------------------+-------+7 rows in set (0.00 sec)mysql> desc SELECT COUNT(*) FROM t1 WHERE i1 = 3 AND d = '2000-01-01';+----+-------------+-------+------------+------+---------------+---------+---------+-------+------+----------+-------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+-------+------------+------+---------------+---------+---------+-------+------+----------+-------------+| 1 | SIMPLE | t1 | NULL | ref | PRIMARY,k_d | PRIMARY | 4 | const | 5 | 20.00 | Using where |+----+-------------+-------+------------+------+---------------+---------+---------+-------+------+----------+-------------+1 row in set, 1 warning (0.00 sec)
总结
开启Index Extensions的时候，使用了索引扩展索引(d,i1,i2)，从key=k_id，key_len=8，ref=const,const可以看出来，直接通过索引，不用回表(Using index)，返回1行结果；
而关闭Index Extensions的时候，使用的是部分PK，并且检索5行，通过where后面的条件过滤20%，即最终返回1行数据，需要回表，效率肯定没有前面一种好；
参考链接
https://dev.mysql.com/doc/refman/5.7/en/index-extensions.html
