mysql 索引 原理_MySQL索引实现原理分析

目前大部分数据库系统及文件系统都采用B-Tree(B树)或其变种B+Tree(B+树)作为索引结构。B+Tree是数据库系统实现索引的首选数据结构。在MySQL中,索引属于存储引擎级别的概念,不同存储引擎对索引的实现方式是不同的,本文主要讨论MyISAM和InnoDB两个存储引擎的索引实现方式。MyISAM索引实现MyISAM引擎使用B+Tree作为索引结构,叶节点的data域存放的是数据记录的地址。

索引是一个排序的链表，在这个列表中存储着索引的值和包含这个值的数据所在行的物理地址或者数据的内容，在数据十分庞大的时候，索引可以大大加快查询的速度，这是因为使用索引后可以不用扫描全表来定位某行的数据，而是先通过索引表找到该行数据对应的物理地址然后访问相应的数据。

索引的优缺点：

优势：可以快速检索，减少I/O次数，加快检索速度；根据索引分组和排序，可以加快分组和排序；

劣势：索引本身也是表，因此会占用存储空间，一般来说，索引表占用的空间的数据表的1.5倍(一般创建索引只是表的一个字段或者几个字段，索引表占用的表空间应该不会太大)；索引表的维护和创建需要时间成本，这个成本随着数据量增大而增大；构建索引会降低数据表的修改操作(删除，添加，修改)的效率，因为在修改数据表的同时还需要修改索引表；

MyISAM 索引实现

MyISAM 引擎使用 B+Tree 作为索引结构,叶节点的 data 域存放的是数据记录的地址。下图是 MyISAM 索引的原理图:

这里设表一共有三列,假设我们以 Col1 为主键,则图 8 是一个 MyISAM 表的主索引(Primary key)示意。可以看出 MyISAM 的索引文件仅仅保存数据记录的地址。

辅助索引

在 MyISAM 中,主索引和辅助索引(Secondary key)在结构上没有任何区别,只是主索引要求 key 是唯一的,而辅助索引的 key 可以重复。如果我们在 Col2 上建立一个辅助索引,则此索引的结构如下图所示

同样也是一颗 B+Tree,data 域保存数据记录的地址。因此,MyISAM 中索引检索的算法为首先按照 B+Tree 搜索算法搜索索引,如果指定的 Key 存在,则取出其data 域的值,然后以 data 域的值为地址,读取相应数据记录。

MyISAM 的索引方式也叫做“非聚集索引”,之所以这么称呼是为了与 InnoDB的聚集索引区分。

InnoDB 索引实现

虽然 InnoDB 也使用 B+Tree 作为索引结构,但具体实现方式却与 MyISAM 截然不同。第一个重大区别是 InnoDB 的数据文件本身就是索引文件(生成的索引文件的叶子节点上的直接存放在key:主键相对应的数据内容),非叶子节点只存放主键信息。

从上文知道,MyISAM 索引文件和数据文件是分离的,索引文件仅保存数据记录的地址。

而在InnoDB 中,表数据文件本身就是按 B+Tree 组织的一个索引结构,这棵树的叶点data 域保存了完整的数据记录。这个索引的 key 是数据表的主键,因此InnoDB 表数据文件本身就是主索引。

上图是 InnoDB 主索引(同时也是数据文件)的示意图,可以看到叶节点包含了完整的数据记录。这种索引叫做聚集索引。因为 InnoDB 的数据文件本身要按主键聚集的

1 .InnoDB 要求表必须有主键(MyISAM 可以没有),如果没有显式指定,则 MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键,如果不存在这种列,则MySQL 自动为 InnoDB 表生成一个隐含字段作为主键,类型为长整形。

设置主键测策略：

显式的创建主键Primary key。

判断表中是否有非空唯一索引，如果有，则为主键。

如果都不符合上述条件，则会生成6个字节的bigint unsigned值。6个字节，因此最大值是2^48

同时,请尽量在 InnoDB 上采用自增字段做表的主键。因为 InnoDB 数据文件本身是一棵B+Tree,非单调的主键会造成在插入新记录时数据文件为了维持 B+Tree 的特性而频繁的分裂调整,十分低效,而使用自增字段作为主键则是一个很好的选择。如果表使用自增主键,那么每次插入新的记录,记录就会顺序添加到当前索引节点的后续位置,当一页写满,就会自动开辟一个新的页。如下图所示:

这样就会形成一个紧凑的索引结构,近似顺序填满。由于每次插入时也不需要移动已有数据,因此效率很高,也不会增加很多开销在维护索引上。

2.第二个与 MyISAM 索引的不同是InnoDB 的辅助索引 data 域存储相应记录主键索引的值而不是地址。换句话说,InnoDB 的所有辅助索引都引用主键作为 data 域，在主键索引中查询到数据，与辅助索引中的字段信息比较，确定准确的数据，辅助索引查询多少条数据就回表多少次。

select * 或者索引字段时的回表场景:

当select * 时，就会拿改行的主键信息去主索引查找全部的记录与组合索引字段的数据进行比较(这个过程就叫回表)，确认准确的查找信息

组合索引查到的数据有多条就会回表几次

当select 索引字段或者主键字段  from时，就不会有回表操作，因为在索引中已经确认到需要展示的数据，就不需要再到主键索引中查找全部的数据了

例如,图 11 为定义在 Col3 上的一个辅助索引:

聚集索引这种实现方式使得按主键的搜索十分高效,但是辅助索引搜索需要检索两遍索引:首先检索辅助索引获得主键,然后用主键到主索引中检索获得记录。

引申:为什么不建议使用过长的字段作为主键?

因为所有辅助索引都引用主索引,过长的主索引会令辅助索引变得过大。

linux 系统：局部性原理与磁盘预读:

由于存储介质的特性，磁盘本身存取就比主存慢很多，再加上机械运动耗费，磁盘的存取速度往往是主存的几百分分之一，因此为了提高效率，要尽量减少磁盘I/O。为了达到这个目的，磁盘往往不是严格按需读取，而是每次都会预读，即使只需要一个字节，磁盘也会从这个位置开始，顺序向后读取一定长度的数据放入内存。预读可以提高I/O效率。预读的长度一般为页(page:计算机管理存储器的逻辑块-通常为4k)的整倍数. 主存和磁盘以页为单位交换数据。当程序要读取的数据不在主存中时，会触发一个缺页异常，此时系统会向磁盘发出读盘信号，磁盘会找到数据的起始位置并向后连续读取一页或几页载入内存中。

B-/+Tree索引的性能优势： 一般使用磁盘I/O次数评价索引优劣。

1.结合操作系统存储结构优化处理： mysql巧妙运用操作系统存储结构(一个节点分配到一个存储页中->尽量减少IO次数) & 磁盘预读(缓存预读->加速预读马上要用到的数据).

2.B+Tree 单个节点能放多个子节点，相同IO次数，检索出更多信息。

3.B+TREE 只在叶子节点存储数据 & 所有叶子结点包含一个链指针 & 其他内层非叶子节点只存储索引数据。只利用索引快速定位数据索引范围，先定位索引再通过索引高效快速定位数据。

每个节点中的key个数越多，那么树的高度越小，需要I/O的次数越少，因此一般来说B+Tree比BTree更快，因为B+Tree的非叶节点中不存储data，就可以存储更多的key。

详解：Mysql设计利用了磁盘预读原理，将一个B+Tree节点大小设为一个页大小，在新建节点时直接申请一个页的空间，这样就能保证一个节点物理上存储在一个页里，加之计算机存储分配都是按页对齐的，这样就实现了每个Node节点只需要一次I/O操作。

B-Tree索引、B+Tree索引： 单个节点能放多个子节点，查询IO次数相同(mysql查询IO次数最多3-5次-所以需要每个节点需要存储很多数据)

B+TREE 只在叶子节点存储数据 & 所有叶子结点包含一个链指针 & 其他内层非叶子节点只存储索引数据。只利用索引快速定位数据索引范围，先定位索引再通过索引高效快速定位数据。

B+Tree更适合外存索引，原因和内节点出度d有关。从上面分析可以看到，d越大索引的性能越好，而出度的上限取决于节点内key和data的大小：

B+Tree内节点去掉了data域，因此可以拥有更大的出度，拥有更好的性能。只利用索引快速定位数据索引范围，先定位索引再通过索引高效快速定位数据。

dmax=floor(pagesize/(keysize+datasize+pointsize))

------------------------------------------------------------------------------------------------------------------------------------------------------

InnoDB表空间管理

InnoDB物理存储文件结构说明：

InnoDB以表空间Tablespace(idb文件)结构进行组织，每个Tablespace 包含多个Segment段，每个段(分为2种段：叶子节点Segment&非叶子节点Segment), 一个Segment段包含多个Extent，一个Extent占用1M空间包含64个Page(每个Page 16k)，InnoDB B-Tree 一个逻辑节点就分配一个物理Page,一个节点一次IO操作。,一个Page里包含很多有序数据Row行数据，Row行数据中包含Filed属性数据等信息。

• 表空间(ibd文件)

• 段(一个索引2段：叶子节点Segment & 非叶子节点Segment)

• Extent(1MB)：一个Extent(1M) 包含64个 Page(16k),一个Page里包含很多有序行数据 , InnoDB B-Tree 一个逻辑节点就分配一个物理Page,一个节点一次IO操作。

• Page(16KB)

• Row

• Field

表插入数据扩展原理： 一次扩张一个Extent空间(1M)，64个Page,按照顺序结构向每个page中插入顺序。

InnoDB逻辑组织结构：

InnoDB索引树结构

每个索引一个B+树， 一个B+树节点 = 一个物理Page(16K)

• 数据按16KB切片为Page 并编号， 编号可映射到物理文件偏移(16K * N)， B+树叶子节点前后形成双向链表， 数据按主键索引聚簇， 二级索引叶节点存储主键值， 通过叶节点主键值回表查找数据。

InnoDB索引原理：

采用聚簇索引- InnoDB数据&索引文件为一个idb文件，表数据文件本身就是主索引，相邻的索引临近存储。 叶节点data域保存了完整的数据记录(数据[除主键id外其他列data]+主索引[索引key:表主键id])。 叶子节点直接存储数据记录，以主键id为key,叶子节点中直接存储数据记录。(底层存储结构:frm -表定义、 ibd: innoDB数据&索引文件)

注：由于InnoDB采用聚簇索引结构存储，索引InnoDB的数据文件需要按照主键聚集，因此InnoDB要求表必须有主键(MyISAM可以没有)。如果没有指定mysql会自动选择一个可以唯一表示数据记录的列作为主键，如果不存在这样的列，mysql自动为InnoDB表生成一个隐含字段(6个字节长整型)作为主键。 InnoDB的所有 辅助索引 都引用 数据记录的主键 作为data域。

聚簇索引与非聚簇索引

InnoDB 使用的是聚簇索引, 将主键组织到一棵 B+树中, 而行数据就储存在叶子节点上, 若使用"where id = 14"这样的条件查找主键, 则按照 B+树的检索算法即可查找到对应的叶节点, 之后获得行数据。 若对 Name 列进行条件搜索, 则需要两个步骤:

第一步在辅助索引 B+树中检索 Name, 到达其叶子节点获取对应的主键。

第二步使用主键在主索引 B+树种再执行一次 B+树检索操作, 最终到达叶子节点即可获取整行数据。

MyISM 使用的是非聚簇索引, 非聚簇索引的两棵 B+树看上去没什么不同, 节点

的结构完全一致只是存储的内容不同而已, 主键索引 B+树的节点存储了主键, 辅助键索引B+树存储了辅助键。 表数据存储在独立的地方, 这两颗 B+树的叶子节点都使用一个地址指向真正的表数据, 对于表数据来说, 这两个键没有任何差别。 由于索引树是独立的, 通过辅助键检索无需访问主键的索引树。

为了更形象说明这两种索引的区别, 我们假想一个表如下图存储了 4 行数据。 其中Id 作为主索引, Name 作为辅助索引。 图示清晰的显示了聚簇索引和非聚簇索引的差异

联合索引及最左前缀原则

联合索引存储数据结构图：

最左原则：

例如联合索引有三个索引字段(A,B,C)

查询条件：

(A，，)---会使用索引

(A，B，)---会使用索引

(A，B，C)---会使用索引

(，B，C)---不会使用索引

(，，C)---不会使用索引

sql脚本中where条件与索引的使用情况

最左前缀原则:

使用索引时必须提供索引最左的数据，这样在查找索引的时候才可以比较准确确定数据的大致位置

例如 :索引 222，如果提供的条件是*22，2和*就无法就行比较，就不会通过索引查找数据

222，如果提供的条件是2*3，只会使用第第一个2进行查找大概信息，第二个2和*无法比较

索引类型中叶子data域存放内容：索引类型主/辅索引Date域存储的内容索引种类

MyISAM主索引数据的地址非聚合索引

辅助索引数据的地址

InnoDB主索引直接数据记录聚合索引，查询效率较快，索引文件较大

辅助索引表的主键，在去主索引查全部信息