mysql中InnoDB索引与MyISAM索引

mysql索引

InnoDB 索引存储

主键索引（聚簇索引）

定义：主键索引是 InnoDB 存储引擎的聚簇索引，它决定了表中数据的物理存储顺序。每个 InnoDB 表都有一个且仅有一个聚簇索引。
存储：主键索引的叶子节点直接包含表的数据行。这意味着数据行按照主键索引的顺序物理存储在磁盘上。
特点：
- 数据和索引紧密关联：数据行和索引项紧密关联，优化了基于主键的查询。
- 唯一性：主键必须是唯一的，且不能为 NULL。

二级索引（辅助索引）

定义：二级索引是用于快速查找非主键列的索引。它们不是聚簇索引，因此叶子节点不包含完整的数据行。
存储：二级索引的叶子节点包含索引列的值和对应行的主键值。这允许数据库通过二级索引快速找到主键，然后使用主键在聚簇索引中找到完整数据行。
特点：
- 支持多列索引：二级索引可以包含多个列。
- 查询优化：通过二级索引可以优化非主键列的查询。
- 回表操作：当需要访问非主键列的完整数据时，需要执行回表操作，即使用二级索引中的主键值在聚簇索引中查找数据行。

覆盖索引

定义：覆盖索引是一个索引包含了查询中所需的全部列，因此查询可以直接从索引中获取数据，而不需要访问实际的数据行。

注意这里一定是一个索引，

如果查询中所需的三个列，它们各自都有索引，这个查询也不算覆盖索引查询。

因为数据库在执行查询时，不能仅通过一个索引来获取 A、B 和 C 的值。数据库可能需要使用索引 idx_A 来定位行，然后访问表来获取 B 和 C 的值

而如果是一个索引中包含了查询中所需的全部列的话：就不需要再去查询其他表了，因为此时索引中就已经有了全部所需信息。

使用覆盖索引是也要注意 “最左前缀原则”。

如果所需的全部列是A，C，但是有一个复合索引A，B，C，此时也不需要多余的回表查询，因为索引都包含所需数据。
优点：
- 减少磁盘I/O：避免了对数据行的访问，减少了磁盘I/O操作。
- 提高查询性能：查询速度显著提高，因为不需要回表操作。
- 创建：通过创建复合索引（也不一定是复合索引，一般是复合索引，可以确保索引覆盖查询中的所有列）。
- 覆盖索引原理：

MyISAM 索引

定义：MyISAM 是 MySQL 的另一个存储引擎，它使用非聚簇索引。
存储：MyISAM 的索引和数据是分开存储的。索引的叶子节点包含指向数据行的指针，而不是数据行本身。
特点：
- 性能优化：MyISAM 支持压缩索引，这可以减少索引文件的大小，提高磁盘空间的利用率。
- 全文索引：MyISAM 支持全文索引，这在处理大量文本数据时非常有用。
- 写操作效率：由于数据和索引分离，MyISAM 在写操作上通常比 InnoDB 更快。

总结来说，InnoDB 和 MyISAM 在索引的存储方式和内部操作上存在显著差异。InnoDB 的聚簇索引和二级索引设计使其更适合事务处理和多用户并发访问，而 MyISAM 的非聚簇索引和全文索引支持使其在特定的读密集型应用中表现更佳。覆盖索引是优化查询性能的重要手段，通过创建合适的索引，可以显著提高数据库的查询效率。