MySQLIndexExtensions-创新互联

Index Extensions含义

在黄岩等地区，都构建了全面的区域性战略布局，加强发展的系统性、市场前瞻性、产品创新能力，以专注、极致的服务理念，为客户提供做网站、网站制作 网站设计制作定制网站建设,公司网站建设,企业网站建设,成都品牌网站建设,营销型网站建设,成都外贸网站建设公司,黄岩网站建设费用合理。

MySQL5.6开始 InnoDB可以通过主键自动扩展二级索引的功能称为Index Extensions，即二级索引除了存储本列索引的key值外，还存储着主键列key值。

创建如下测试表

mysql> show create table t1\G *************************** 1. row ***************************        Table: t1 Create 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=utf8 1 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=on 1 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

另外有需要云服务器可以了解下创新互联cdcxhl.cn，海内外云服务器15元起步，三天无理由+7*72小时售后在线，公司持有idc许可证，提供“云服务器、裸金属服务器、高防服务器、香港服务器、美国服务器、虚拟主机、免备案服务器”等云主机租用服务以及企业上云的综合解决方案，具有“安全稳定、简单易用、服务可用性高、性价比高”等特点与优势，专为企业上云打造定制，能够满足用户丰富、多元化的应用场景需求。

分享题目：MySQLIndexExtensions-创新互联
分享地址：http://cdkjz.cn/article/jdsie.html