MySQL —— MySQL索引介绍、索引数据结构、聚集索引和辅助索引、索引覆盖

索引概念

索引是对数据库表中一列或多列的值进行排序后的一种数据结构。用于帮助 mysql 提高查找效率的数据结构。

索引的作用

• 索引用于快速定位特定值所在的行，避免全表扫描。当没有索引时，MySQL 必须从第一条记录开始逐行读取，直到找到匹配的行。通过索引，MySQL 可以更高效地执行 WHERE、ORDER BY 和 JOIN 等操作，从而显著提高查询速度。

索引的存储结构

• 索引是一个独立的数据库结构，以索引文件的形式存储的磁盘上，包含对数据表中所有记录的引用指针。
• 索引是对数据库表中一列或多列值进行排序后的一种数据结构，能够实现快速查找。

索引的存储类型

索引是在存储引擎中实现的，因此，每种存储引擎的索引都不一定完全相同，并且每种存储引擎也不一定支持所有索引类型。每种存储引擎对每个表的最大索引数量及每个索引的最大长度有特定限制。

MySQL 中索引的主要存储类型包括：

• BTREE 索引：

  定义：一种基于平衡树（B-Tree）的索引结构，支持范围查询和排序。

  支持：MyISAM 和 InnoDB 存储引擎均支持 BTREE 索引。

• HASH 索引：

  定义：使用哈希表实现的索引，适用于等值查询。

  支持：MEMORY（HEAP）存储引擎支持 HASH 索引，但 MyISAM 和 InnoDB 不支持。

索引优势：

1. 类似大学图书馆建书目索引，提高数据检索的效率，降低数据库的 IO 成本；
2. 通过索引列对数据进行排序，降低数据排序的成本，降低了 CPU 的消耗。

索引缺点：

1. 实际上索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也是要占用空间的；（占用磁盘空间）
2. 虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行 INSERT、UPDATE 和 DELETE。因为更新表时，MySQL 不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段，都会调整因为更新所带来的键值变化后的索引信息；（增删改需要维护索引，降低效率）
3. 索引只是提高效率的一个因素，如果你的 MySQL 有大数据量的表，就需要花时间研究建立最优秀的索引，或优化查询。

索引分类

http://c.biancheng.net/view/7897.html

1. 普通索引：（和单值索引是同一个概念？不是，它们是所属关系。）

   定义：普通索引是最基本的索引类型，没有任何限制，仅用于加速查询，允许重复和空值。

   特点：

   • 允许在索引列中插入重复值和空值；
   • 适合用于加速检索，但不保证数据的唯一性；

   -- 创建表时定义普通索引
   CREATE TABLE tb_user (
       id VARCHAR(20),
       name VARCHAR(20),
       INDEX idx_name (name)  -- 创建普通索引
   );
   

2. 主键索引

   定义：主键索引是一种特殊的唯一索引，主键列的值必须唯一，并且不允许为空。

   特点：

   • 数据库在定义主键时会自动创建主键索引。
   • 每个表只能有一个主键。
   • 主键通常用于唯一标识表中的每一行记录。

   -- 创建表时指定主键索引
   CREATE TABLE tb_user (
       id VARCHAR(20) PRIMARY KEY,  -- 指定主键
       name VARCHAR(20)
   );
   
   -- 创建表后指定主键
   ALTER TABLE tb_user ADD PRIMARY KEY (id);
   
   -- 查看索引
   SHOW INDEX FROM tb_user;
   

3. 唯一索引

   定义：唯一索引与普通索引类似，但其索引列的值必须唯一，允许有空值。

   特点：

   • 适用于需要确保某列值唯一但又希望允许空值的场景。
   • 可以有多个唯一索引。

   -- 1. 创建表时创建唯一索引
   CREATE TABLE tb_user (
       id VARCHAR(20) PRIMARY KEY,
       name VARCHAR(20),
       UNIQUE (name)  -- 创建唯一索引，索引名默认为字段名
   );
   
   -- 2. 创建表后再指定唯一索引
   CREATE UNIQUE INDEX name_index ON tb_user(name);  -- 可以指定索引名
   
   

4. 全文索引

   定义：全文索引用于对文本内容进行分词和搜索，通常用于处理大量文本数据。

   特点：

   • 仅可用于 MyISAM 表（在 MySQL 的较新版本中，InnoDB 也支持全文索引）。
   • 允许在索引列中插入重复值和空值。
   • 适合于实现复杂的文本搜索功能，如查找包含特定词汇的记录。

   -- 创建表时创建全文索引
   CREATE TABLE tb_article (
       id INT PRIMARY KEY,
       content TEXT,
       FULLTEXT (content)  -- 创建全文索引
   );
   
   -- 创建表后再指定全文索引
   CREATE FULLTEXT INDEX ft_content ON tb_article(content);
   

5. 单值索引（单列索引）

   定义：单值索引是指只包含一个列的索引，可以是普通索引、唯一索引或全文索引。

   特点：

   • 一个表可以有多个单值索引。
   • 适合对单个列进行索引，提高查询效率。

   -- 1. 创建表时创建单值索引
   CREATE TABLE tb_user (
       id VARCHAR(20) PRIMARY KEY,
       name VARCHAR(20),
       KEY (name)  -- 创建单值索引，索引名默认为字段名
   );
   
   -- 2. 创建表后再指定单值索引
   CREATE INDEX name_index ON tb_user(name);  -- 可以指定索引名
   
   -- 删除索引
   DROP INDEX name_index ON tb_user;
   

6. 复合索引（组合索引或多列索引）

   定义：复合索引是指一个索引包含多个列的索引。

   特点：

   • 复合索引在数据库操作时通常比多个单值索引更高效，尤其是在查询涉及多个列时。
   • 可以提高多条件查询的性能。

   -- 1. 创建表时创建复合索引
   CREATE TABLE tb_user (
       id VARCHAR(20) PRIMARY KEY,
       name VARCHAR(20),
       age INT,
       KEY (name, age)  -- 创建复合索引
   );
   
   -- 2. 创建表后再指定复合索引
   CREATE INDEX name_age_index ON tb_user(name, age);  -- 可以指定索引名
   

   多列值组成一个索引，专门用于组合搜索，这种索引在查询时可以同时考虑多个列，从而减少查询的开销。

   • 复合索引特别适合于需要同时过滤多个列的查询，能够提高查询效率。
   • 复合索引的性能通常优于单独使用多个单列索引的索引合并。
   • 复合索引遵循“最左前缀”原则，意味着在使用复合索引时，查询条件必须包含索引中最左边的列，才能有效利用后续列的索引。因此，在复合索引中索引列的顺序至关重要。（这个顺序是指定义复合索引时的字段顺序（KEY (name, age)），where 中使用了 name 字段索引，才会使用 age 字段索引。而不是字段在 where 中出现的先后顺序，因为会被优化。即 where name = ’zhangsan‘ and age = 18 和 where age = 18 and name = ’zhangsan‘ 使用 explain 查看都是一样的。）
     
     

   最左前缀：复合索引查询条件中左边的字段使用了索引，右边的字段才会使用索引；否则，左边字段没有使用，直接使用右边字段，则右边字段不会使用索引，而是会遍历表查找。

   如：索引 index1:(a,b,c) 有三个字段，以下是索引使用情况的具体说明：

   1. 有效使用索引：

      查询条件 WHERE a = ‘1’：使用 a 字段索引。

      查询条件 WHERE a = ‘1’ AND b = ‘2’：同时使用 a 和 b 字段索引。

      查询条件 WHERE a = ‘1’ AND b = ‘2’ AND c = ‘3’：同时使用 a、b 和 c 字段索引。

   2. 部分有效使用：

      查询条件 WHERE a = ‘1’ AND c = ‘3’：只使用 a 字段索引，c 字段索引失效。

      查询条件 WHERE a = ‘1’ AND b > ‘2’ AND c = ‘3’：只使用 a 和 b 字段索引，c 字段索引失效。因为索引是有序的，index1 索引在索引文件中的排列是有序的，首先根据 a 来排序，然后才是根据 b 来排序，最后是根据 c 来排序，在 a、b 走完索引后，c 肯定是无序了，所以 c 就没法走索引，只能遍历a、b字段索引后的所有行。

   3. 无效使用：

      查询条件 WHERE b = ‘2’ 或 WHERE c = ‘3’：不会使用任何索引，而是进行全表扫描。

创建索引：

创建普通索引：
	CREATE INDEX index_name ON table_name(col_name);
创建唯一索引:
	CREATE UNIQUE INDEX index_name ON table_name(col_name);
创建普通组合索引：
	CREATE INDEX index_name ON table_name(col_name_1,col_name_2);
创建唯一组合索引：
	CREATE UNIQUE INDEX index_name ON table_name(col_name_1,col_name_2);

修改索引：

ALTER table table_name ADD  [UNIQUE ]  INDEX [indexName] ON (columnname(length));

删除索引：

DROP INDEX index_name ON table_name;

索引数据结构种类

1. BTree 索引

   BTREE 索引就是一种将索引值按一定的算法，存入一个树形的数据结构中（二叉树），每次查询都是从树的入口 root 开始，依次遍历 node，获取 leaf。B树是一种自平衡的树形数据结构，能够在大量数据中进行高效的插入、删除和查找操作。这是 MySQL 里默认和最常用的索引类型。

   特点：

   • 每个节点可以有多个子节点，节点中的键值按顺序排列。
   • 查询时从树的根节点开始，逐层向下遍历，直到达到叶子节点。
   • 是 MySQL 中的默认索引类型，适用于大多数场景。

2. Hash 索引

   Hash索引使用哈希函数将键值映射到哈希表中。HASH 索引可以一次定位，不需要像树形索引那样逐层查找。

   特点：

   • 适合等值查询（= 和 IN），能够在 O(1) 的时间复杂度内定位。
   • 不支持范围查询（如 >、< 等），因为哈希值不保留原始大小关系。
   • 一般用于需要快速查找的场景，但不适用于排序和范围过滤，因为经过相应的 Hash 算法处理之后的Hash值的大小关系，并不能保证和 Hash 运算前完全一样。

3. FULL-TEXT 全文索引

   即为全文索引，全文索引用于对文本数据进行高效的模糊查询。目前只有 MyISAM 引擎支持，可以在 CREATE TABLE ，ALTER TABLE ，CREATE INDEX 使用，不过目前只有 CHAR、VARCHAR ，TEXT 列上可以创建全文索引。

   特点：

   • 目前主要在 MyISAM 存储引擎中支持。
   • 适用于 CHAR、VARCHAR 和 TEXT 类型的列。
   • 解决了使用 LIKE ‘%word%’ 进行模糊查询时的性能问题，能够处理自然语言查询。

4. RTree 索引

   RTREE 在 MySQL 很少使用，仅支持 geometry 数据类型，支持该类型的存储引擎只有 MyISAM、BDb、InnoDb、NDb、Archive几种。

   R树是一种多维数据的索引结构，是B树的多维版，常用于空间数据的存储和检索。

   特点：

   • 支持几何数据类型的索引，适合用于地理信息系统（GIS）。
   • 可以处理二维、三维及更高维的数据，不仅限于平面数据。
   • 适合范围查找，特别是在需要处理矩形区域的查询时。

Innodb 存储引擎默认是 B+Tree索引、MyISAM 存储引擎默认是 Full-Text 索引；Memory 存储引擎默认 Hash 索引。

Innodb 索引数据结构

参考：MySQL —— Innodb 索引数据结构

聚集索引和辅助索引（非聚集索引）

参考

聚集索引（聚簇索引）并不是一种单独的索引类型，而是一种数据存储方式。数据行是根据聚集索引的顺序存储的，聚簇索引决定了数据在磁盘上的物理存储顺序。。

如下图，左侧的索引就是聚簇索引，因为数据行在磁盘的排列和索引排序保持一致。

聚集索引

每张表按照聚集索引（clustered index）构造一棵B+树，同时叶子节点中存放的即为整张表的行记录数据，也将聚集索引的叶子节点称为数据页。聚集索引的这个特性决定了索引组织表中数据也是索引的一部分。

• 聚簇索引决定了数据在磁盘上的物理存储顺序。数据行按照聚簇索引的顺序存放，意味着索引和数据是紧密结合的。

• 每个表只能有一个聚簇索引，因为数据行只能以一种方式排序，实际的数据页只能按照一棵B+树进行排序；聚集索引叶子节点就是数据节点，数据行与对应的键（索引）存放在一起。

• 在 InnoDB 存储引擎中，主键通常自动成为聚簇索引。如果没有定义主键，InnoDB 会选择一个唯一的非空索引作为聚簇索引。如果没有主键或合适的唯一索引，InnoDB 会生成一个隐藏的主键（6个字节的自增列）作为聚簇索引。

• 聚集索引对范围查询和顺序访问的性能非常好，因为相邻的记录是物理上紧挨着存储的，可以减少磁盘 I/O 操作。

• 由于聚簇索引的特性，插入和更新操作可能会导致数据的重新排序，尤其是当新数据的键值在现有数据中间时。

• innodb 中的普通索引，唯一索引，联合索引都是辅助索引，采用非聚集索引结构，这些索引不直接存储行数据，而是存储索引键与主键的映射，InnoDB 的所有辅助索引都引用主键作为数据项。

  聚集索引这种实现方式使得按主键的搜索十分高效，但是辅助索引搜索需要检索两遍索引：首先检索辅助索引获得主键，然后使用主键在聚簇索引中查找完整的行数据。这种设计主要是为了在进行增、删、改时更方便维护，避免因数据页的变化而导致的复杂性。

聚簇索引的限制：

• 对于mysql数据库目前只有innodb数据引擎支持聚簇索引，而Myisam并不支持聚簇索引。
• 由于数据物理存储排序方式只能有一种，所以每个Mysql的表只能有一个聚簇索引。一般情况下就是该表的主键。
• 为了充分利用聚簇索引的聚簇的特性，所以innodb表的主键列尽量选用有序的顺序id，而不建议用无序的id，比如uuid这种。

辅助索引（非聚集索引）

• 辅助索引（Secondary Index，也称非聚集索引），叶子节点不包含完整的行记录数据。叶子节点存储的是索引键值以及一个指向对应行数据的聚簇索引，用于在聚簇索引中查找完整的行数据。因为 InnoDB 的表是索引组织表，辅助索引的叶子节点提供的书签是指向主索引的主键。当通过辅助索引查询数据时，首先在辅助索引中查找到相关的键值和主键，然后再通过主键访问聚簇索引，以获取完整的行记录。

• 辅助索引的逻辑顺序与磁盘上行数据的物理存储顺序不同。主索引的物理顺序是按照主键的顺序存储的，而辅助索引则可以根据索引键的顺序组织数据，因此一个表可以有多个辅助索引。

• 辅助索引的叶子节点为索引节点，但是有一个指针指向数据节点。索引文件和数据文件是分离的，这种分离的结构使得索引和数据的管理相对独立。

聚集索引与辅助索引的区别：

1. 一个表中只能拥有一个聚集索引，而辅助索引一个表可以存在多个。
2. 聚集索引的叶节点就是数据节点，辅助索引的叶节点仍然是索引节点，存储的是索引键和指向相应数据块的指针。
3. 聚集索引的键值的逻辑顺序决定了表中相应行的物理顺序。数据行是按照聚集索引的顺序存储的，相邻的记录在物理上也是相邻的；辅助索引的物理存储不按照索引排序。辅助索引的数据结构是独立于主键的，数据行的物理存储顺序与辅助索引的顺序没有直接关系。

索引覆盖

• 定义：索引覆盖是指在查询时，所有需要返回的列都已经包含在索引中，这样就不需要回表查询具体的数据。

  在 InnoDB 存储引擎中，覆盖索引（covering index）是一种优化查询性能的技术。它允许数据库在辅助索引中直接找到所需的数据，而无需回到聚集索引中进行二次查询。这种方式 MySQL 可以直接从索引中获取所需的数据，提高查询效率。

  通常，二级索引（辅助索引）只存储索引字段和对应的主键值。如果查询的字段不是索引字段，MySQL 需要通过主键值进行二次回表查询数据，增加了额外的 I/O 操作。而这种索引覆盖了所有想要的列的情况，就不需要再回表查询具体的数据。（即要查询的字段恰好是具有索引的字段，就可以直接使用，不要回表再查一次。）

• 基于辅助索引搜索需要检索两遍索引：首先检索辅助索引获得主键，然后用主键到主索引中检索获得记录。

  1. 检索辅助索引：首先，数据库会根据查询条件在辅助索引中查找匹配的记录。辅助索引的叶子节点存储了索引键和对应的主键值，而不是完整的行数据。
  2. 回表聚集索引：通过辅助索引找到匹配的主键后，数据库会使用这个主键值来访问聚集索引（主索引），以获取完整的行记录。
     
     

  这种两次检索的过程是因为 InnoDB 的辅助索引设计为只存储主键和索引键，而不包含完整的行数据。这种设计虽然在某些情况下提高了索引的效率，但也意味着在使用辅助索引时，必须进行额外的查找操作来获取完整数据，从而可能影响性能。

  如果能够使用索引覆盖，只需要在一棵索引树上就能获取SQL所需的所有列数据，无需回表，即辅助索引获得主键过程就可以获得所需的所有列数据，而无序再用主键到主索引中检索获得记录（回表）。
  

• 索引覆盖示例

  假设一个用户表 tb_user 表结构如下：

  -- 在这个表中，我们创建了一个复合索引 idx_name_age，它包含了 name 和 age 两个字段。
  CREATE TABLE tb_user (
      id INT PRIMARY KEY,
      name VARCHAR(20),
      age INT,
      email VARCHAR(50),
      INDEX idx_name_age (name, age)  -- 创建复合索引
  );
  

  索引覆盖查询

  -- 在这个查询中，所需返回的字段 name 和 age 都已包含在复合索引 idx_name_age 中；
  -- MySQL 可以直接从索引中返回结果，而不需要回表查询
  SELECT name, age FROM tb_user WHERE name = 'zhangsan';
  

  未覆盖查询

  -- 这个查询需要返回 name、age 和 email 字段。
  -- 虽然 name 和 age 在索引中，但 email 不在索引中。
  -- MySQL 会先通过索引查找 name，然后需要回表查找 email，因此这不是一个索引覆盖查询。
  SELECT name, age, email FROM tb_user WHERE name = 'zhangsan';
  

覆盖索引总结

回想一下，如果查询只需要扫描索引而无须回表，将带来诸多好处。

• 索引条目通常远小于数据行大小，如果只读取索引，MySQL就会极大地减少数据访问量。
• 索引按照列值顺序存储，对于I/O密集的范围查询会比随机从磁盘中读取每一行数据的I/O要少很多。
• InnoDB的辅助索引（亦称二级索引）在叶子节点中保存了行的主键值，如果二级索引能够覆盖查询，则可不必对主键索引进行二次查询了。
• 不是所有类型的索引都可以成为覆盖索引。覆盖索引必须要存储索引的列，而哈希索引、空间索引和全文索引等都不存储索引列的值。

回表

• 在 MySQL 中，回表（又称为“回表查询”）是指在使用二级索引（辅助索引）进行查询时，无法直接获取所有所需的列数据，因此需要先通过二级索引找到主键，然后再通过主键访问聚簇索引（主索引）以获取完整的行数据。