本文介绍MySQL的index extensions特性，以及如何利用这个特性实现SQL查询优化。
index extensions是MySQL 5.6.9之后的新特性，关于这个特性，手册中的解释是这样的：InnoDB automatically extends each secondary index by appending the primary key columns to it（出处详见手册 8.2.1.7 Use of Index Extensions，原文链接：https://dev.mysql.com/doc/refman/5.6/en/index-extensions.html ）。简言之就是，InnoDB引擎表中，会把主键所有列值附加存储在辅助索引中。
假设有这样一个表： CREATE TABLE t( a int not null, b int not null, c int not null, d int not null, PRIMARY KEY(a, b), KEY i_c(c) ) ENGINE=InnoDB;
意思是，该表中的辅助索引 i_c 的索引键值，实际上也同时存储了主键中的两个列值，也就是说，i_c 的索引数据结构中，实际上存储的列是：c、a、b 三列的值。
我们可通过 innodb_table_monitor 查看验证下：
TABLE: name test/t, id 681, flags 1, columns 7, indexes 2, appr.rows 0 COLUMNS: a: DATA_INT DATA_BINARY_TYPE DATA_NOT_NULL len 4; b: DATA_INT DATA_BINARY_TYPE DATA_NOT_NULL len 4; c: DATA_INT DATA_BINARY_TYPE DATA_NOT_NULL len 4; d: DATA_INT DATA_BINARY_TYPE DATA_NOT_NULL len 4; DB_ROW_ID: DATA_SYS prtype 256 len 6; DB_TRX_ID: DATA_SYS prtype 257 len 6; DB_ROLL_PTR: DATA_SYS prtype 258 len 7;
INDEX: name PRIMARY, id 1159, fields 2/6, uniq 2, type 3 root page 3, appr.key vals 0, leaf pages 1, size pages 1 FIELDS: a b DB_TRX_ID DB_ROLL_PTR c d
INDEX: name i_c, id 1160, fields 1/3, uniq 3, type 0 root page 4, appr.key vals 0, leaf pages 1, size pages 1 FIELDS: c a b
可见，确实是如此。我们顺便也看到 PRIMARY KEY 里包含了所有的列值，以及 DB_TRX_ID、DB_ROLL_PTR 等额外属性（InnoDB引擎独有特性，用于实现InnoDB的事务）。
事实上，辅助索引实际也存储主键值的特性，在InnoDB引擎中一直都是如此，只是从5.6.9版本开始后，在计算执行计划时，查询优化器（optimizer）才能识别到这个特性，并且利用这个特性。而在5.6.9以前，虽然这个特性也存在，但并不被查询优化器识别，也就无法被利用了。
这个特性可适用于 ref, range, and index_merge 等多种索引访问方式，在稀松索引扫描（loose index scan）、联接（join）、排序以及MIN()/MAX()等场景下。
我们来看看这个特性怎么被优化器识别并利用的，假设上述测试表中的测试数据有： SELECT * FROM t; +—-+—-+—-+—-+ | a | b | c | d | +—-+—-+—-+—-+ | 1 | 2 | 4 | 2 | | 1 | 3 | 2 | 2 | | 1 | 4 | 9 | 2 | | 1 | 5 | 9 | 2 | | 1 | 6 | 8 | 2 | | 2 | 2 | 9 | 2 | | 3 | 2 | 8 | 2 | | 4 | 2 | 6 | 2 | | 5 | 2 | 6 | 2 | | 6 | 2 | 1 | 2 | +—-+—-+—-+—-+
MySQL版本：5.6.21-70.0-log Percona Server (GPL), Release 70.0, Revision 688。
假设有下面的查询，看下它的执行计划： mysql> DESC SELECT a,b,c FROM t WHERE a = 1 AND c = 9\G id: 1 select_type: SIMPLE table: t type: ref possible_keys: PRIMARY,i_c key: i_c key_len: 8 ref: const,const rows: 2 Extra: Using index
在5.6.9以前的版本（或者修改优化器开关，关闭 index extensions 特性。如果用5.6.9以后的版本测试，还请记得）： mysql> DESC SELECT a,b,c FROM t WHERE a = 1 AND c = 9\G id: 1 select_type: SIMPLE table: t type: ref possible_keys: PRIMARY,i_c key: i_c key_len: 4 ref: const rows: 3 Extra: Using where; Using index
可执行下面的命令关闭 index extensions 特性： mysql> SET optimizer_switch = ‘use_index_extensions=off’;
这两个执行计划的区别在于：
前者的key_len是8而后者是4，预示着可以用到的索引不仅是i_c这个索引，还有主键索引；
前者的ref列值是const,const，而后者只有const，预示着前者用到了2个索引部分，而后者只有一个；
前者评估的rows为2，而后者评估的rows为3，因为前者效率更高；
后者的Extra列中多了Using Where，表示后者还需要从结果中再次过滤数据，而不能像前者那样直接利用索引取得结果。
我们还可以根据观察STATUS中的Handler_read_%值差异来对比两个SQL的实际执行代价（执行FLUSH STATUS后，执行查询SQL，再执行SHOW STATUS LIKE ‘Handler_read_%’ 查看）：
后者的代价是 Handler_read_next = 3；
前者的代价是 Handler_read_next = 2；
如果数据量更大的话，这个差值也会随之增大。
由此可见，前者的效率确实要比后者来的更高。
我们应该经常关注新版本的新特性，利用这些新特性提升SQL效率 ：）
文章转自老叶茶馆公众号，原文链接：https://mp.weixin.qq.com/s/uMrgcgQ_f6bijGqrteOkJQ
相关资源：七夕情人节表白HTML源码(两款)