聚簇索引与覆盖索引

聚簇索引与覆盖索引

聚簇索引

Innodb 和 MyIsam 的数据分布

MyIsam 数据分布：

MyIsam 按照数据插入的顺序存储在磁盘上

MyIsam 中主键索引和其他索引没有区别，主键索引就是一个名为 primary 的唯一非空索引。

MyIsam 在行的旁边显示了行号，从 0 递增，可以从表的开头跳过所需字节找到需要的行。

Innodb 的数据分布：

聚簇索引的每个叶子节点都包含了主键值、事务 ID、用于事务和 MVCC 的回滚指针和所有剩余列。如果主键是一个列前缀索引，Innodb 会包含主键列和剩下的其他列

聚簇索引保存了整个表，和 MyIsam 使用独立的行存储不同。

Innodb 的二级索引的叶子节点中存储的是主键值

最好避免随机的聚簇索引，特别是对应 IO 密集型的应用，如果使用 UUID 作为聚簇索引使得聚簇索引的插入变得完全随机，Innodb 可能在插入之前先找到从磁盘读取目标页到内存中，导致大量的随机 IO。写入乱序，Innodb 不得不频繁做页分裂操作，以便为新的行分配空间。页分裂导致移动大量数据，一次插入最少需要修改三个页，由于频繁页分裂，页变得稀疏并被不规则填充，最终数据会有碎片。

所以使用 Innodb 尽可能按主键顺序插入数据，尽可能使用单调增加的聚簇键的值插入新行

覆盖索引

一个索引包含所有需要查询的字段的值，我们称为覆盖索引。如果 Mysql 可以使用索引来直接获取列的数据，就不再需要读取数据行。

覆盖索引必须存储索引列的值，因此 Mysql 只能使用 B-Tree 索引做覆盖索引。 而哈希索引、空间索引和全文索引等都不存储索引列的值。

覆盖索引优点：

• 索引条目远小于数据行大小，所以如果只需要读取索引，mysql 就会极大地减少数据访问量。覆盖索引对于 IO 密集型应用很好，因为索引比数据更小，更容易全部放入内存中

• 索引按照列值顺序存储，所以对于 io 密集型的范围查询会比随机从磁盘读取每行数据的 io 要少得多。

• MyIsam 在内存中只缓存索引，数据依赖操作系统缓存，如果没有覆盖索引，访问数据就需要进行系统调用

• Innodb 的二级索引在叶子节点中保存了行的主键值，所以如果二级主键能够覆盖查询，就避免了对主键索引的二次查询

这就是聚簇索引和覆盖索引的大体情况了，