【MySQL】索引的概念 && 常见的索引

索引的概念

在 MySQL 中，索引是一种用于提高查询效率的数据结构。它可以帮助数据库系统快速定位和访问表中的数据。索引可以基于一个或多个列创建，并且可以应用于表中的任何列。

不用加内存、不用改程序、不用调sql、只要执行正确的 create index ，查询速度就可能提高成百上千倍。但是天下没有免费的午餐，查询速度的提高是以插入、更新、删除的速度为代价的，这些写操作，增加了大量的 IO。

MySQL 支持多种类型的索引，包括下面几种：

1. 主键索引（Primary Key Index）：主键索引是一种唯一性索引，用于标识表中的每一行。每个表只能有一个主键索引，它可以跨多个列定义。
   1. 推荐为每个表定义一个主键。如果没有逻辑上唯一且非空的列或列集可以使用主键，则 MySQL 会自动添加一个自增列。
2. 唯一索引（Unique Index）：唯一索引确保索引列中的值是唯一的，但允许包含空值。一个表可以有多个唯一索引。
3. 普通索引（Normal Index）：普通索引是最基本的索引类型，它没有唯一性或主键的限制。一个表可以有多个普通索引。
4. 全文索引（Full-Text Index）：全文索引用于在大文本数据中进行全文搜索。它可以提供更高级的搜索功能，如关键字搜索和排序。
   1. 这类大文本数据现在通常用专门的文档类型数据库来存储，如 mongodb，效率更高！
5. 组合索引（Composite Index）：组合索引是基于多个列创建的索引。它可以提高多列查询的性能，但只有在查询中使用了索引的第一个列时才能发挥作用。
   1. 最左前缀原则：组合索引只有在查询条件中【使用了】索引的第一个列时，才会被使用。这是组合索引的一个重要特性。
6. 聚簇索引：与主键索引是同义词
   1. 一个表只能有一个聚簇索引，它决定了数据的物理存储顺序。
   2. 索引的叶节点直接存储了完整的数据行。
   3. 是否发生回表：
      1. 当查询条件是【聚簇索引列】时，不存在回表操作，因为可以直接从索引的叶节点获取完整的数据行。
      2. 当查询条件是【非聚簇索引列】时，会发生回表操作，因为需要通过非聚簇索引找到指向数据行的指针（通常是主键值），然后通过主键值去聚簇索引中查找完整的数据行。
   4. 自增列的选取：
      1. 如果没有为表定义 PRIMARY KEY，则 InnoDB 使用第一个 UNIQUE 和 NOT NULL 的列作为聚集索 引。
      2. 如果表中没有 PRIMARY KEY 或合适的 UNIQUE 索引，InnoDB 会为新插入的行生成一个行号并用 6 字节的 ROW_ID 字段记录，ROW_ID 单调递增，并使用 ROW_ID 做为索引。
7. 非聚簇索引：聚簇索引以外的索引，统称为非聚簇索引或二级索引
   1. 一个表可以有多个非聚簇索引，它们不改变数据的物理存储顺序，叶节点存储的是指向数据行的指针。
   2. 是否发生回表：
      1. 通常存在回表操作，因为索引的叶节点只存储了指向数据行的指针。
      2. 在覆盖索引的情况下，可以避免回表操作。
8. 索引覆盖：当一个 select 语句使用了普通索引且查询列表中的列刚好是创建普通索引时的所有或部分列，这时就可以直接返回数据，而不用回表查询，这样的现象称为索引覆盖
   1. 要实现索引覆盖，查询中涉及的列必须在索引中都有覆盖