1. 聚集索引和非聚集索引(辅助索引)
- 聚簇索引:按照每张表的主键构造B+树,非叶子节点用来存放索引,叶子节点(数据页)用来存放行记录数据,按照主键的顺序排序,每个数据页都通过一个双向链表来连接,因此,聚集索引能够在B+树索引的叶子节点上直接找到数据,而且对主键的排序查找和范围查找速度非常快。(通过主键聚集数据,同一个页的数据行在物理上是连续的,不同的页之间用链表串接起来。)如果没有定义主键,InnoDB会选择一个唯一的非空索引替代。如果没有这样的索引,InnoDB会隐式定义一个主键来作为聚集索引。
- 非聚簇索引(辅助索引):叶子节点存放的是键值对,还包含一个指针,指向聚集索引的主键。当通过非聚集索引来查找数据时,首先遍历非聚集索引,找到对应的叶子节点,并获得对应聚集索引的主键,然后通过这个聚集索引找到对应的行,所以它需要两次索引查找。
将数据存储于索引分开结构,索引结构的叶子节点指向了数据的对应行,myisam通过key_buffer把索引先缓存到内存中,当需要访问数据时(通过索引访问数据),在内存中直接搜索索引,然后通过索引找到磁盘相应数据,这也就是为什么索引不在key buffer命中时,速度慢的原因。
区别:
- 聚集索引将数据存储与索引放到了一块,找到索引也就找到了数据,速度很快;非聚集索引通过索引查找主键,然后还要再通过聚集索引才能找到数据,速度较慢。
- 聚集索引一个表只能有一个,而非聚集索引一个表可以存在多个
- 聚集索引存储记录是物理上连续存在,而非聚集索引是逻辑上的连续,物理存储并不连续。
2. 聚集索引特点
缺点:
- 聚集索引提高了IO密集型应用的性能,但是如果数据全部存在内存中,聚集索引就没什么优势了。
- 建立索引的顺序依赖于数据的顺序,如果数据不是按照主键顺序排列的,建立索引会比较慢。
- 更新聚集索引的代价比较高,因为会强制将数据行记录移动到新的位置。可能还会有页分裂问题:如果要插入的位置所在的页已满,存储引擎会将该页分成两个页面来容纳该行,导致占用更多的磁盘空间。
优点:
3. 非聚集索引特点
缺点:
- 非聚集索引查找数据需要2次索引查找,因为非聚集索引的叶子节点保存的不是指向行的位置的指针,而是行的主键值。所以通过非聚集索引查找行,首先找到的是行的主键值,然后根据这个主键值去聚集索引中查找对应的行。
优点:
- 非聚集索引的叶子节点存储的不是指向行的位置的指针,而是主键值,这样减少了当出现行移动或者数据分页时非聚集索引的维护工作。移动行时不需要更新非聚集索引的叶子节点的指针。
4. 主键索引 (聚集索引) 和普通索引 (辅助索引) 的区别
- 主键索引:
主键索引不可以为空
主键索引可以做外键
一张表中只能有一个主键索引 - 普通索引:
用来加速数据访问速度而建立的索引。多建立在经常出现在查询条件的字段和经常用于排序的字段。
被索引的数据列允许包含重复的值主键索引不可以为空
主键索引可以做外键
一张表中只能有一个主键索引