InnoDB 引擎中自动创建和按需创建的索引

非原创、ChatGPT 问答生成

在 MySQL 的 InnoDB 引擎中，索引有些是需要手动创建的，而有些则是由数据库引擎自动添加的。以下是一个概述：

这些索引是开发者根据应用需求手动创建的，用于优化查询性能。

主键索引（Primary Key Index）
- 如果你在创建表时定义了一个主键（PRIMARY KEY），InnoDB 会自动为主键创建一个聚簇索引。
- 如果表没有主键，InnoDB 会自动选择一个唯一非空的列创建为主键索引，或者内部生成一个隐式的主键列（6 字节大小）。
唯一索引（Unique Index）
- 需要手动创建，通过 UNIQUE 关键字指定。
- 唯一索引保证列中的数据是唯一的，并能加速基于该列的查询。
普通索引（Non-Unique Index）
- 需要手动创建，通过 INDEX 或 KEY 关键字指定。
- 普通索引用于加速基于该列的查询，但不保证数据的唯一性。
组合索引（Composite Index）
- 需要手动创建，通过在多个列上创建一个联合索引来加速涉及多列的查询。
- 常用于查询条件涉及多个列时，以提高查询效率。
全文索引（Full-Text Index）
- 在特定的文本字段上手动创建，用于全文搜索。
- 在创建时使用 FULLTEXT 关键字。
前缀索引（Prefix Index）
- 对于长字符串字段，手动创建索引时可以指定只使用前几位字符来创建索引。
- 通过指定前缀长度来减少索引的存储空间。

这些索引由数据库引擎在特定条件下自动创建。

主键索引（Primary Key Index）
- 如果表没有显式定义主键，InnoDB 会选择第一个唯一非空的索引作为主键索引。
- 如果没有唯一非空的索引，InnoDB 会自动创建一个隐式主键（6 字节大小的 row_id）。
外键索引（Foreign Key Index）
- 在定义外键约束时，InnoDB 自动在外键列上创建索引，以确保外键约束的有效性。
自适应哈希索引（Adaptive Hash Index, AHI）
- 这是 InnoDB 自动生成的一种特殊的哈希索引，用于加速某些热点数据的查询。
- AHI 是动态创建和管理的，通常不需要开发者的干预。

聚簇索引（Clustered Index）
- 如果手动定义了主键，InnoDB 会自动将其作为聚簇索引。
- 如果没有定义主键，InnoDB 会自动选择一个合适的列作为聚簇索引，或者生成一个内部的聚簇索引。

需要手动创建的索引：包括唯一索引、普通索引、组合索引、全文索引、前缀索引等，主要是为特定查询优化而设计的。
数据库引擎自动添加的索引：包括主键索引（在未显式定义主键时）、外键索引、自适应哈希索引等。这些索引是为了维护数据完整性或提高性能，数据库引擎会自动生成。

在实际开发中，手动创建的索引需要根据具体的查询模式和性能需求进行设计和调整，而自动创建的索引则是数据库引擎的默认行为，通常不需要额外的干预。

晴耕雨读