Mysql 索引原理(MyISAM&InnoDB)

一、要点

在MySQL中,索引属于存储引擎级别的概念,不同存储引擎对索引的实现方式是不同的。

二 、MyISAM的索引

MyISAM的索引方式是“非聚集”索引。MyISAM索引文件和数据文件是分离的,索引文件仅保存数据记录的地址。MyISAM引擎使用B+Tree作为索引结构,叶节点的data域存放的是数据记录的地址

1. MyISam主索引

myisam-primary-index

MyISAM表的主索引(Primary key)

 

如图一共有三列,假设我们以Col1为主键,以col1构造B+树。B+树的叶子节点上保存的是该记录的地址,可以理解为该行数据的物理存储位置。

2. MyISam辅助索引

在MyISAM中,主索引和辅助索引(Secondary key)在结构上没有任何区别,只是主索引要求key是唯一的,而辅助索引的key可以重复。

Myisam 辅助索引(Second index)

Myisam 辅助索引(Second index)

如图,在col2上建立辅助索引,则以col2构造B+树,B+树的叶子节点上保存的是该记录的地址。

三、?InnoDB索引

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

1. InnoDB主索引

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

InnoDB主索引(同时也是数据文件)

InnoDB主索引(同时也是数据文件)

如图,主键是col1,则根据col1构造B+树,整个数据行其实就存在B+树的叶子上。

2. InnoDB的辅助索引

InnoDB的辅助索引data域存储相应记录主键的值而不是地址

innodb-second

InnoDB的辅助索引

如图在col3上建立辅助索引,则根据col3构造B+树,B+树的叶节点存的是该行的主键,即col1的值。

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

很久没有理解innodb的辅助索引为什么关联的是主键,造成检索的时候要检索两遍。而不是和mssql的非聚集索引一样,直接记录行的物理位置,一次检索即可?最近在比较集中索引设计的时候才发现,一直太关注读的效率才会有这样的想法。想设计innodb索引的人一定是兼顾了索引维护的写操作。如果辅助索引中记录物理位置,则当主索引变更引起的物理位置变化会导致辅助索引也要更新;而记录主键则避免辅助索引随着主索引更新。oracle的索引组织表的辅助索引也是按照这个思路来设计的。

知道了InnoDB的索引实现后,就很容易明白为什么不建议使用过长的字段作为主键,因为所有辅助索引都引用主索引,过长的主索引会令辅助索引变得过大。再例如,用非单调的字段作为主键在InnoDB中不是个好主意,因为InnoDB数据文件本身是一颗B+Tree,非单调的主键会造成在插入新记录时数据文件为了维持B+Tree的特性而频繁的分裂调整,十分低效,而使用自增字段作为主键则是一个很好的选择。

四、参考:

MySQL索引背后的数据结构及算法原理

 

 

原创文章。为了维护文章的版本一致、最新、可追溯,转载请注明: 转载自idouba

本文链接地址: Mysql 索引原理(MyISAM&InnoDB)


, ,

No comments yet.

发表评论