MySQL 索引的存储原理

索引的作用

1. 是以插入、更新、删除的速度为代价提高查询的效率
2. 使查询数据的结果按索引排序，及查询结果有序 。 (聚簇索引)
3. 大幅提高访问索引连续数据的速度.(聚簇索引)
   (主要讨论InnoDB引擎支持的聚簇索引)

索引的分类

主键索引(primary key)
唯一索引(unique)
普通索引(index)
全文索引(fulltext)–解决中子文索引问题。

注意：主键索引和唯一建索引的区别如下：
唯一索引：唯一索引是一种索引类型，它确保索引列中的值是唯一的，即不允许有重复的值。唯一索引可以包含NULL值，但是NULL值在索引列中只能出现一次。一个表可以有多个唯一索引，可以在一个或多个列上创建唯一索引。
主键索引：主键索引也是一种唯一索引，但它具有额外的特性。主键索引不允许NULL值，并且在表中只能有一个主键。主键索引用于唯一标识表中的每一行数据，

存储的规则

1. MySQL数据存储在磁盘中，但对数据今昔增/删/查/改操作时要先加载到内存中，
   MySQL在内存中用Buffer Pool缓存数据库中的数据页。
2. MySQL数据存储以16KB(MySQL page的大小)为基本单位存储和访问数据而不是操作系统的4kb。

原因: 无论是1还是2都是为了减少磁盘和内存间的I0次数。

索引的原理

MySQL 以MySQL page的大小为基本单位访问数据，page为一个结构体。MySQL在Buffer Pool中以B+树为数据结构组织数据，其中MySQL page就是B+树中的基本节点。 （B+树说明）

索引原理——B+树整体情况：

非叶子page(数据page)节点情况：

叶子page(存值page)的情况：

叶子page所在层的整体情况：

目录page的本质也是页，数据page中存的数据是用户数据，而目录page中存的数据是普通页的地址

非聚簇索引

聚簇索引与非聚簇索引的区别是B+树底层存储的数据是value还是指向value的指针(前面图展示的为聚簇索引)。聚簇索引B+树叶子层存储value，非聚簇索引叶子层存储指向value的指针 。

聚簇索引优势: 避免对指针进行二次访问的时间开销（与之相对的劣势为：修改效率低，每次修改都要对节点内索引进行重新排序）; 聚簇索引查询结果有序 。

非聚簇索引优势：减少维护开销，相比聚簇索引，非聚簇索引的维护开销相对较低。当表中的数据发生变化时（如插入、更新、删除操作），不需要实际移动、排序同一叶子节点内的内容。（与之相对的劣势为：
查询效率低，需要二次访问指针和遍历叶子节点内的指针）

聚簇索引适合于那些经常需要按照索引顺序进行数据查询的表,非聚簇索引适合于那些需要频繁插入和更新数据的表。

补充与说明

索引类型(聚簇/非聚簇)是根据存储引擎的选择而变化的，除此之外存储引擎的选择还有如下影响：

性能：不同的存储引擎在性能方面有所差异。例如，InnoDB存储引擎适合处理大量的写操作和事务处理，而MyISAM存储引擎在处理大量的读操作时性能较好。根据应用程序的读写比例和性能需求，选择合适的存储引擎可以提高数据库的性能。

事务支持：不同的存储引擎对事务的支持程度不同。例如，InnoDB存储引擎支持事务和行级锁，而MyISAM存储引擎不支持事务和只支持表级锁。如果应用程序需要支持事务操作，就需要选择支持事务的存储引擎。

并发控制：存储引擎的并发控制方式不同，会影响数据库的并发处理能力。例如，InnoDB存储引擎使用MVCC（多版本并发控制）来实现高并发处理，而MyISAM存储引擎使用表级锁，可能会导致并发性能瓶颈。

数据完整性：不同的存储引擎对数据完整性的支持程度也有所不同。一些存储引擎提供了更严格的数据完整性约束，如外键约束、触发器等，可以保证数据的一致性和完整性。

索引类型：不同的存储引擎支持的索引类型和限制也有所不同。例如，InnoDB存储引擎支持外键约束和全文索引，而MyISAM存储引擎不支持外键和全文索引。

空间占用：不同的存储引擎在存储数据时占用的空间也有所不同。一些存储引擎会对数据进行压缩或使用更高效的存储方式，从而减少存储空间的占用。