目录
参考资料:姜承尧的MySQL实战宝典
InnoDB存储引擎是MySQL数据库中使用最广泛的引擎,在海量大并发的 OLTP 业务中尤其适用。它在数据存储方面有一个非常大的特点:索引组织表(Index Organized Table)。
什么是索引组织表
数据存储有堆表和索引组织表两种方式。
堆表
堆表中的数据无序存放, 数据的排序完全依赖于索引。在堆表的组织结构中,数据和索引分开存储。索引是排序后的数据,而堆表中的数据是无序的,索引的叶子节点存放了数据在堆表中的地址。当堆表的数据发生改变,且位置发生了变更,所有索引中的地址都要更新,这非常影响性能,特别是对于OLTP业务。Oracle、Microsoft SQL Server、PostgreSQL 早期默认支持的数据存储都是堆表结构。
索引组织表
在索引组织表中,数据根据主键排序存放在索引中,数据即索引,索引即数据。主键索引也叫聚集索引。
MySQL中InnoDB存储引擎就是这样的数据组织方式,Oracle、Microsoft SQL Server后期也推出了支持索引组织表的存储方式。但是,PostgreSQL 数据库因为只支持堆表存储,不适合OLTP的访问特性,虽然它后期对堆表有一定的优化,但本质是通过空间换时间,对海量并发的OLTP业务支持依然存在局限性。
什么是二级索引
InnoDB 存储引擎中数据是根据主键索引排序存储的,除了主键索引外,其他的索引都称之为二级索引, 或非聚集索引。二级索引也是一颗 B+ 树索引,但它和主键索引不同的是叶子节点存放的是索引键值和主键值。
通过二级索引只能定位到主键值,需要额外再通过主键索引来查询其他数据。这种二级索引通过主键索引进行再一次查询的操作叫作“回表”。
索引组织表中二级索引设计有一个非常大的好处,若记录发生了修改,其他二级索引无须进行维护,除非记录的主键发生了修改。与堆表的索引实现对比,索引组织表在存在大量变更的场景下,性能优势会非常明显,因为大部分情况下都不需要维护其他二级索引。
由于每个二级索引都包含了主键值,查询数据时需要通过主键值进行回表,所以在设计表结构时主键值应尽可能紧凑,为的就是提升二级索引的性能。尽可能紧凑指的是一个页能存放的记录数尽可能多。
在实际业务场景中,开发同学很有可能会设计带有业务属性的主键,但请牢记以下两点设计原则:
- 要比较顺序,对聚集索引性能友好;
- 尽可能紧凑,对二级索引的性能和存储友好。
总结
- 索引组织表中主键索引即聚集索引,索引的叶子节点存放表中一整行完整记录;
- 除主键索引外的索引都是二级索引,索引的叶子节点存放的是(索引键值,主键值);
- 由于二级索引不存放完整记录,因此需要通过主键值再进行一次回表才能定位到完整数据;
- 索引组织表对比堆表,在海量并发的OLTP业务中能有更好的性能表现。