内容简介：MySQL InnoDB的二级索引（Secondary Index）会自动补齐主键，将主键列追加到二级索引列后面。详细一点来说，InnoDB的二级索引（Secondary Index）除了存储索引列key值，还存储着主键的值(而不是指向主键的指针)。为什么这样做呢？因为InnoDB是以聚集索引方式组织数据的存储，即主键值相邻的数据行紧凑的存储在一起（索引组织表）。当数据行移动或者发生页分裂的时候，可以减少大量的二级索引维护工作。InnoDB移动行时，无需更新二级索引。我们以官方文档的例子来测试：如上所示，
MySQL InnoDB的二级索引（Secondary Index）会自动补齐主键，将主键列追加到二级索引列后面。详细一点来说，InnoDB的二级索引（Secondary Index）除了存储索引列key值，还存储着主键的值(而不是指向主键的指针)。为什么这样做呢？因为InnoDB是以聚集索引方式组织数据的存储，即主键值相邻的数据行紧凑的存储在一起（索引组织表）。当数据行移动或者发生页分裂的时候，可以减少大量的二级索引维护工作。InnoDB移动行时，无需更新二级索引。我们以官方文档的例子来测试：
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;
如上所示，这个t1表包含主键和二级索引k_d，二级索引k_d（d）的元组在InnoDB内部实际被扩展成（d,i1,i2），即包含主键值。因此在设计主键的时候，常见的一条设计原则是要求主键字段尽量简短，以避免二级索引过大(因为二级索引会自动补齐主键字段)。
优化器会考虑扩展二级索引的主键列，确定什么时候使用以及如何使用该索引。 这样可以产生更高效的执行计划和达到更好的性能。有不少博客介绍索引扩展是从 MySQL 5.6.9开始引入的。不过个人还没有在官方文档看到相关资料。
优化器可以用扩展的二级索引来进行ref,range,index_merge等类型索引访问（index access）,松散的索引扫描（index sacns）,连接和 排序 优化，以及min()/max()优化。
我们先来插入测试数据（脚本来自官方文档）：
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');
#默认情况下，索引扩展（use_index_extensions）选项是开启的。可以在当前会话通过修改优化器开关optimizer_switch开启、关闭此选项。
mysql> show variables like '%optimizer_switch%'; mysql> SET optimizer_switch = 'use_index_extensions=off'; Query OK, 0 rows affected (0.00 sec) mysql> EXPLAIN -> SELECT COUNT(*) FROM t1 WHERE i1 = 3 AND d = '2000-01-01'; +----+-------------+-------+------+---------------+------+---------+-------+------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+-------+------+--------------------------+ | 1 | SIMPLE | t1 | ref | PRIMARY,k_d | k_d | 4 | const | 5 | Using where; Using index | +----+-------------+-------+------+---------------+------+---------+-------+------+--------------------------+ 1 row in set (0.00 sec)
这种情况下，优化器不会使用主键，因为主键由字段（i1,i2）组成，但是该查询中没有引用t2字段;优化器会选择二级索引 k_d(d) 。
我们将use_index_extensions选项在当前会话开启，那么 SQL 语句的执行计划会怎样变化呢?
mysql> SET optimizer_switch = 'use_index_extensions=on'; Query OK, 0 rows affected (0.00 sec) mysql> EXPLAIN -> SELECT COUNT(*) FROM t1 WHERE i1 = 3 AND d = '2000-01-01'; +----+-------------+-------+------+---------------+------+---------+-------------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+-------------+------+-------------+ | 1 | SIMPLE | t1 | ref | PRIMARY,k_d | k_d | 8 | const,const | 1 | Using index | +----+-------------+-------+------+---------------+------+---------+-------------+------+-------------+ 1 row in set (0.00 sec) mysql>
当use_index_extensions=off的时候，仅使用索引k_d中d列的数据，忽略了扩展的主键列的数据。而use_index_extensions=on时，使用了k_d索引中（i1,i2,d）三列的数据。可以从上面两种情况下的explain输出结果中信息得以验证。
key_len：由4变到8，说明不仅仅使用了d列上的索引，而且使用了扩展的主键i1列的数据
ref：由const变为 ” const,const ” , 使用了索引的两部分。
rows:从5变为1，表明InnoDB只需要检查更少的数据行就可以产生结果集。
Extra： ” Using index,Using where ” 变为 ” Using index ” 。通过索引覆盖就完成数据查询，而不需要读取任何的数据行。官方文档的介绍如下：
The Extra value changes from Using where; Using index to Using index. This means that rows can be read using only the index, without consulting columns in the data row.
其实关于这两者的区别，查了很多资料都没有彻底搞清楚 ” Using index,Using where ” 与 ” Using index ” 的区别。此处不做展开。
另外，从status信息中 “ Handler_read_% ” 相关状态值可以观察实际执行过程中索引和数据行的访问统计。
flush table 关闭已打开的数据表，并清除缓存（表缓存和查询缓存）。
flush status 把status计数器清零。
Handler_read_key：The number of requests to read a row based on a key. If this value is high, it is a good indication that your tables are properly indexed for your queries.
Handler_read_next：The number of requests to read the next row in key order. This value is incremented if you are querying an index column with a range constraint or if you are doing an index scan.（ 此选项表明在进行索引扫描时，按照索引从数据文件里取数据的次数。）
关闭use_index_extensions情况下，status的统计信息
mysql> SET optimizer_switch = 'use_index_extensions=off'; Query OK, 0 rows affected (0.00 sec) mysql> FLUSH TABLE t1; Query OK, 0 rows affected (0.00 sec) mysql> FLUSH STATUS; Query OK, 0 rows affected (0.00 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)
开启use_index_extensions情况下，status的统计信息
mysql> SET optimizer_switch = 'use_index_extensions=on'; Query OK, 0 rows affected (0.00 sec) mysql> FLUSH TABLE t1; Query OK, 0 rows affected (0.00 sec) mysql> FLUSH STATUS; Query OK, 0 rows affected (0.00 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>
对比两个执行计划，发现Handler_read_next的值从5变为1，表明索引的访问效率更高了，减少了数据行的读取次数。
本文结合官方文档Use of Index Extensions和自己理解整理。
参考资料 ：
https://docs.oracle.com/cd/E17952_01/mysql-5.6-en/index-extensions.html
https://dev.mysql.com/doc/refman/5.6/en/index-extensions.html
http://blog.51cto.com/huanghualiang/1557306
以上就是本文的全部内容，希望对大家的学习有所帮助，也希望大家多多支持 码农网
猜你喜欢:
- Linq中带有迭代索引的Select扩展方法，为啥知道的人不多呢？
- MySQL索引使用说明(单列索引和多列索引)
- Elasticsearch索引的基本操作(3)-索引的滚动索引
- Coreseek 增量索引模拟实时索引
- Coreseek 增量索引模拟实时索引
- MySQL高效索引之覆盖索引
本站部分资源来源于网络，本站转载出于传递更多信息之目的，版权归原作者或者来源机构所有，如转载稿涉及版权问题，请联系我们。
算法Ⅰ~Ⅳ(C++实现):基础、数据结构、排序和搜索
Sedgewick / 高等教育出版社 / 2002-1 / 49.00元
本书通过C++实现方案以简洁、直接的方式对书中的算法和数据结构进行表述，并向学生提供在实际应用中验证这种方法的手段。 本书广泛地论述了与排序、搜索及相关应用有关的基本数据结构和算法。覆盖了数组、链表、串、树和其他基本数据结构，更多地强调抽象数据类型(ADT)、模块化程序设计、面向对象程序设计和C++类。本书包括排序、选择、优先队列ADT实现和符号表ADT(搜索)实现，配有帮助学生学习计算......一起来看看 《算法Ⅰ~Ⅳ(C++实现):基础、数据结构、排序和搜索》 这本书的介绍吧!
