MyISAM和InnoDB存储引擎索引的区别
MyISAM
主键索引
MyISAM引擎使用B+树作为索引结构,叶节点的data域存放的是数据记录的地址。下图是MyISAM主键索引的原理图:
辅助索引
在MyISAM中,主索引和辅助索引(Secondary key)在结构上没有任何区别,只是主索引要求key是唯一的,而辅助索引的key可以重复,如果给其它字段创建辅助索引,结构图如下
根据上面两张图,首先按照B+Tree搜索算法搜索索引,如果指定的Key存在,则取出其data域的值,然后以data域的值为地址,读取相应数据记录。
可以看到,MyISAM存储引擎,索引结构叶子节点存储关键字和数据地址,也就是说索引关键字和数据没有在一起存放,体现在磁盘上,就是索引在一个文件存储,数据在另一个文件存储,例如一个user表,会在磁盘上存储三个文件 user.frm(表结构文件) user.MYD(表的数据文件) user.MYI(表的索引文件)。
MyISAM的索引方式也叫做非聚集索引。
InnoDB
主键索引
InnoDB存储引擎的主键索引,叶子节点中,索引关键字和数据是在一起存放的,如图:
辅助索引
InnoDB的辅助索引,叶子节点上存放的是索引关键字和对应的主键,如图:
辅助索引的B+树,先根据关键字找到对应的主键,再去主键索引树上找到对应的行记录数据。从索引树上可以看到,InnoDB的索引关键字和数据都是在一起存放的,体现在磁盘存储上,例如创建一个user表,在磁盘上只存储两种文件,user.frm(存储表的结构),user.ibd(存储索引和数据)。
InnoDB的索引树叶节点包含了完整的数据记录,这种索引叫做聚集索引。因为InnoDB的数据文件本身要按主键聚集,所以InnoDB要求表必须有主键(区别于MyISAM可以没有),如果没有显式指定,则MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键,如果不存在这种列,则MySQL自动为InnoDB表生成一个隐含字段作为主键,这个字段长度为6个字节,类型为长整形。
MyISAM存储引擎的主键索引和二级索引没有太大区别,只是主键值有unique的限制,而复杂索引的值可以重复。
而InnoDB存储引擎的主键索引和二级索引有很大的区别。二级索引只存索引的数据只存储主键的值,而主键索引存的是完整的记录。在二级索引树中查找未包含的信息时,回有回表查询的过程。