blog

MySQL索引引擎：InnoDB VS MyISAM

MySQL索引引擎：InnoDB VS MyISAM

MySQL提供了多种存储引擎，每种引擎在索引实现和数据存储方式上都有所不同。

主要的存储引擎包括InnoDB和MyISAM，它们在索引类型、事务支持、锁机制等方面有显著差异。

InnoDB引擎

InnoDB是MySQL的默认存储引擎，支持ACID事务和行级锁。

InnoDB使用B+树作为索引结构，支持聚集索引和非聚集索引。

聚集索引将数据行存储在主键索引的叶子节点中，查询效率高，但插入和更新操作较慢

CREATE TABLE users (
	id INT PRIMARY KEY,
	username VARCHAR(50)
) ENGINE=InnoDB;

MyISAM引擎

MyISAM是MySQL早期的默认存储引擎，不支持事务和行级锁。MyISAM使用B树作为索引结构，不支持聚集索引。

MyISAM的索引和数据是分开存储的，适用于读多写少的场景。

CREATE TABLE users (
	id INT,
	username VARCHAR(50),
	INDEX (username)
) ENGINE=MyISAM;

聚集索引与非聚集索引

聚集索引（Clustered Index）将数据行存储在索引的叶子节点中，查询效率高，但插入和更新操作较慢。

聚集索引决定了表中数据的物理存储顺序，即表中数据行的物理排列与索引键的逻辑顺序一致。一张表最多只能创建一个聚集索引（通常由主键自动生成，若未显式定义主键，数据库会选择唯一非空列或隐式生成唯一标识列作为聚集索引）。

结构特点：

  • 数据与索引绑定：聚集索引的叶子节点直接存储数据行的完整记录，而非仅存储索引键和指针。
  • B + 树结构：索引按 B + 树结构组织，根节点和中间节点存储索引键及指向子节点的指针，叶子节点存储实际数据。
  • 主键的特殊性：若表定义了主键，InnoDB 存储引擎会自动将主键作为聚集索引；若没有主键，会选择第一个唯一非空索引，否则生成隐藏的 ROWID 作为聚集索引。

优点：

  • 范围查询高效：由于数据物理存储有序，范围查询（如BETWEEN、>）可通过遍历索引快速定位数据，减少 I/O 操作。
  • 主键查询极速：通过主键查询时，直接通过聚集索引定位到数据行，无需二次查找。

缺点：

  • 插入 / 更新成本高：数据物理顺序需与索引一致，插入新数据时可能引发页分裂（Page Split），导致性能下降。
  • 索引维护开销大：对索引列（如主键）的修改会触发整个索引结构的调整，影响写入性能。

适用场景：

  • 频繁进行范围查询或排序的表（如日志表、时间序列数据）。
  • 以主键或聚集索引列作为条件的高频查询场景。

非聚集索引（Non-Clustered Index）则将索引和数据分开存储，适用于读多写少的场景。

非聚集索引的逻辑顺序与数据物理存储顺序无关，其叶子节点存储的是索引键值及对应数据行的物理地址（指针），而非完整数据记录。一张表可创建多个非聚集索引。

InnoDB 存储引擎（MySQL）： 主键会自动成为聚集索引。

若表未定义主键，InnoDB 会按以下顺序选择一列作为聚集索引：
① 第一个定义的唯一非空索引（UNIQUE NOT NULL）；
② 自动生成一个隐藏的ROWID作为聚集索引（不可见，性能略低于显式主键）。

MyISAM不支持聚集索引，其索引与数据分离存储（索引文件.MYI和数据文件.MYD）。
主键本质上是唯一非空索引，与普通非聚集索引结构一致，仅约束唯一性。

PostgreSQL：主键默认创建非聚集索引，需手动创建聚集索引（通过CLUSTER命令，但会锁定表，不建议频繁使用）。

选择存储引擎的建议

选择存储引擎时，应根据应用的实际需求和负载来做出决策。

如果需要高并发和事务支持，InnoDB是更合适的选择。

如果应用是只读或读多写少，MyISAM可能会带来更好的查询性能。

通过合理选择和使用索引，可以大大提升数据库的查询性能，是优化数据库性能的关键。