开发者社区> memos1> 正文
阿里云
为了无法计算的价值
打开APP
阿里云APP内打开

MySQL Index Extensions

简介: Index Extensions含义 MySQL5.6开始 InnoDB可以通过主键自动扩展二级索引的功能称为Index Extensions,即二级索引除了存储本列索引的key值外,还存储着主键列key值。
+关注继续查看

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

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
【C#】【MySQL】【GridView】删除出现Parameter index is out of range
【C#】【MySQL】【GridView】删除出现Parameter index is out of range
0 0
MySQL 覆盖索引(Cover Index)
MySQL 覆盖索引(Cover Index)
0 0
【实施工程师之家】——mysql四种索引PRIMARY(主键索引)、INDEX(一般索引)、UNIQUE(非空索引)、FULLTEXT(全文索引)应用
【实施工程师之家】——mysql四种索引PRIMARY(主键索引)、INDEX(一般索引)、UNIQUE(非空索引)、FULLTEXT(全文索引)应用
0 0
mysql 索引(index)
mysql 索引(index)
0 0
mysql index cond push down 索引下推
mysql 索引下推在8.0的代码中进行了重构,目前的逻辑比较清晰。本文对该代码进行相关的分析。本文介绍的代码为mysql-8.0.21版本。
0 0
[MySQL] 联合索引与using index condition
[MySQL] 联合索引与using index condition1.测试联合索引的最左原则的时候, 发现了5.6版本后的新特性Index Condition Pushdown 2.含义就是存储引擎层根据索引尽可能的过滤数据,然后在返回给服务器层根据where其他条件进行过滤 3.
1494 0
MySQL · myrocks · clustered index特性
Cluster index介绍 最近在RDS MyRocks中,我们引入了一个重要功能,二级聚集索引(secondary clustering index). 我们知道innodb和rocksdb引擎的主键就是clustered index。
1296 0
软件测试|MySQL安装最全教程
软件测试|MySQL安装最全教程
0 0
+关注
memos1
点滴积累,做好技术。
文章
问答
文章排行榜
最热
最新
相关电子书
更多
让 MySQL 原生分布式触手可及
立即下载
好的 MySQL 兼容可以做到什么程度
立即下载
云数据库RDS MySQL从入门到高阶
立即下载