转载

InnoDB索引存储结构知识点

一、InnoDB索引结构

InnoDB除了表结构.frm文件外,就只有一个.ibd 文件,索引和数据存储在一起,所以在InnoDB的B+树中叶子节点直接存储的是整条数据记录,而不是记录磁盘地址。InnoDB引擎和MyISAM引擎还有一个最大的不同就是InnoDB引擎是以主键索引来组织数据的(主键索引和非主键索引的存储结构是不同的),InnoDB存储引擎中这种组织数据的方式被称之为聚集索引组织表(clustered index organize table)
主键索引也被称之为聚集索引。

1. 聚集索引

聚集索引(又称之为聚簇索引),聚集的术语表示的是索引键值和数据紧凑的存储在一起。而数据又不会同时存在两个地方,所以InnoDB每张表都有且只有一个聚集索引,换言之,也就是说每张表都必须有且只有一个主键。

1.1 没有主键索引呢?

  • InnoDB会选择一个非空的唯一索引列作为主键
  • 如果这个也没有,那么InnoDB就会选择一个选择其自己内置 的6字节长的ROWID自增列作为主键

2. 非聚集索引

除了主键索引之外的其他索引都是非聚集索引,既然聚集索引的索引键值和数据行存放在一起,而聚集索引又只有一个,那么非聚集索引又是怎么存储数据的呢?接下来要画重点了哈:
非聚集索引的叶子节点存储的是当前索引的键值和主键索引的键值。 大致结构如下图所示:

所以非聚集索引查询数据和聚集索引查询数据是不同的,因为非聚集索引的叶子节点只有当前索引的键值和主键的键值,也就是说查询数据的时候获取到非聚集索引的叶子节点只能拿到当前索引值和主键索引值。

2.1 回表

什么是回表?回表指的就是非聚集索引从叶子节点拿到数据(主键的键值)之后,
还需要再根据主键键值去扫描主键索引的B+树,这种操作就叫做回表,也就是说他需要扫描两颗B+树,这也就是为什么在InnoDB中主键索引的效率相比较其他索引是最高的。

2.2 覆盖索引

假如说我们查的数据本身就是一个索引值,那么我们就不用像前面那样,根据从叶子节点拿到主键,然后在根据主键去查数据了。这样就减少了一次查b+树的了。

二、MySQL对索引的优化

1. Index Condition Pushdown(ICP)

Index Condition Pushdown中文含义为:索引条件下推
是在MySQL5.6版本之后引进的优化措施。如果没有ICP,存储引擎将遍历索引以定位基表中的行,并将它们返回给MySQLServer层,由Server层计算行的where条件。在启用ICP的情况下,如果WHERE条件的一部分可以通过只使用索引中的列来计算,那么MySQL服务器将这一部分WHERE条件下推到存储引擎。然后,存储引擎使用索引条目来计算已推入的索引条件,只有满足这个条件,才从表中读取行。ICP可以减少存储引擎必须访问基表的次数和MySQL服务器必须访问存储引擎的次数。

查询是否开启

执行如下语句:show variables like 'optimizer_switch';

1
2
3
4
5
6
7
mysql> show variables like 'optimizer_switch';
+------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Variable_name | Value |
+------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| optimizer_switch | 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,use_invisible_indexes=off,skip_scan=on |
+------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.02 sec)

其中:index_condition_pushdown这个参数就是是否开启索引下推优化的,on表示开启,off表示关闭。
可以通过如下语句设置:

1
SET optimizer_switch='index_condition_pushdown=off';

2. Multi-Range Read(MRR)

Multi-Range Read和Index Condition Pushdown一样,也是在MySQL5.6版本之后引进的优化措施。MRR优化的目的是为了减少磁盘的随机IO访问,并且将随机访问转化为顺序的数据访问,所以MRR优化措施对IO-bound型的SQL查询语句可能带来极大的性能提升。

  • 和ICP一样,也是通过【optimizer_switch】变量查询,找到返回结果中的下面两个参数:
1
2
mrr=on
mrr_cost_based=on

mrr=on表示启用,mrr_cost_based 表示是否通过基于开销的方式来启用MRR,如果mrr_cost_based=on,则即使满足了使用MRR的条件,优化器也会视当前查询的开销来决定是否使用MRR,如果我们想总是开启MRR,则可以将mrr设置为on,mrr_cost_based设置为off,如下:

SET optimizer_switch='mrr=off,mrr_cost_based=off';

我们开启了 MRR,重新执行 sql 语句,发现 Extra 里多了一个「Using MRR」。

2.1 工作方式

  1. 将查询得到的辅助索引键值存放于缓存之中,注意,这时候缓存中的数据是根据辅助索引的键值排序的。
  2. 将缓存中的数据根据row ID(主键)进行重排序。
  3. 然后再根据row ID(主键)的顺序去访问。

2.2 顺序读取的好处

  1. 磁盘和磁头不再需要来回做机械运动;
  2. 可以充分利用磁盘预读

比如在客户端请求一页的数据时,可以把后面几页的数据也一起返回,放到数据缓冲池中,这样如果下次刚好需要下一页的数据,就不再需要到磁盘读取。这样做的理论依据是计算机科学中著名的局部性原理:

当一个数据被用到时,其附近的数据也通常会马上被使用。

  1. 在一次查询中,每一页的数据只会从磁盘读取一次

MySQL 从磁盘读取页的数据后,会把数据放到数据缓冲池,下次如果还用到这个页,就不需要去磁盘读取,直接从内存读。

但是如果不排序,可能你在读取了第 1 页的数据后,会去读取第2、3、4页数据,接着你又要去读取第 1 页的数据,这时你发现第 1 页的数据,已经从缓存中被剔除了,于是又得再去磁盘读取第 1 页的数据。

而转化为顺序读后,你会连续的使用第 1 页的数据,这时候按照 MySQL 的缓存剔除机制,这一页的缓存是不会失效的,直到你利用完这一页的数据,由于是顺序读,在这次查询的余下过程中,你确信不会再用到这一页的数据,可以和这一页数据说告辞了。

顺序读就是通过这三个方面,最大的优化了索引的读取。
别忘了,索引本身就是为了减少磁盘 IO,加快查询,而 MRR,则是把索引减少磁盘 IO 的作用,进一步放大。

3. INDEX MERGE

索引合并优化

索引合并优化,MySQL在5.0及之后的版本引入了这种优化方案。这个意思就是我们在一个表中建立了很多单列索引,然后查询的时候同时用到了多列作为条件,MySQL能够识别并分别使用单列索引进行扫描,然后将结果合并。
这种算法一般用于以下三种情况:

  • or条件的并集(union 或者 union all)
  • and条件的交际
  • 综合前面两种情况

注意:过多的单列索引大部分情况下并不能提高性能。《高性能MySQL》一书中的作者认为,索引合并虽然是MySQL的优化方案,但是出现了这种现象,更多是说明索引建的很糟糕。

分享