MySQL数据库——索引介绍

大家好，这里是编程Cookbook。本文详细介绍MySQL数据库中的索引，包括索引结构设计（B+树、哈希等）、用途（主键索引、唯一索引等）、聚集索引和非聚集索引，以及索引创建和使用的原则等。

目录

• 按底层数据结构分类
• 1. B+树 索引
• 2. 哈希索引（Hash Index）
• 3. 全文索引（Full-Text Index）
• 4. 空间索引（Spatial Index）
• Hash索引和B+树索引
• 按功能和用途分类
• 1. 主键索引（Primary Key Index）
• 2. 唯一索引（Unique Index）
• 3. 普通索引（Normal Index）
• 4. 全文索引（Full-Text Index）
• 5. 覆盖索引（Covering Index）
• 6. 组合索引（Composite Index）
• 总结
• 按存储方式划分的索引
• 1. 聚簇索引 vs 非聚簇索引
• 2. 聚簇索引（Clustered Index）
• 3. 非聚簇索引（Non-Clustered Index）
• 为什么非聚簇索引的叶子节点存储主键值？
• 索引创建原则
• 索引失效
• 1. 最左前缀匹配原则
• 2. LIKE 通配符使用不当
• 3. 索引列参与计算或函数操作
• 4. 使用 OR 条件
• 5. 字段类型不匹配
• 6. 不支持的操作符
• 7. 使用 IS NULL 或 IS NOT NULL
• 8. 表关联字段编码不一致
• 9. MySQL 优化器选择全表扫描
• 10. 列对比导致索引失效

按底层数据结构分类

MySQL 提供了四种主要的索引实现，每种索引类型有不同的应用场景和特点。以下是这四种索引类型：

1. B+Tree 索引：适用场景最广，是 MySQL 中默认的索引类型，适合绝大多数查询操作，如常规的 WHERE
    和 ORDER BY
   。
2. 哈希索引：只适用于 MEMORY
    存储引擎，专注于等值查询，速度极快，但不支持范围查询。
3. 全文索引：用于文本搜索，主要支持全文检索操作，适合需要高效搜索大文本内容的场景。用于快速检索较长的文本。
4. 空间索引：适用于 GIS 空间数据，主要用于地理信息相关的查询和存储，支持 R-Tree 数据结构。

1. B+树 索引

B+树 索引是 MySQL 默认的索引类型，适用于大多数存储引擎，特别是 InnoDB 和 MyISAM。

• 特点：

• 适用于 等值查询（=
  ）、范围查询（>
  、<
  、BETWEEN
  ）、排序（ORDER BY
  ）等操作。
• 索引的数据以树的结构存储，B+树索引通过多层次的树结构来组织数据，使得数据查找、插入和删除的时间复杂度为 O(log N)。

• 使用场景：

• 常用于经常被用作查询条件的列，如主键、外键、以及经常参与筛选、排序和连接操作的列。

• 存储引擎支持：

• 默认存储引擎 InnoDB 和 MyISAM 都使用 B+树 索引。

2. 哈希索引（Hash Index）

哈希索引主要用于 Memory 存储引擎，它通过哈希表来实现索引，数据的存储和查找速度非常快，尤其是等值查询。

• 特点：

• 等值查询（=
  ）速度最快，但不支持范围查询（>
  、<
  、BETWEEN
   等），这也是哈希索引的一个主要限制。
• 哈希索引的查询复杂度是 O(1)，但会消耗更多的内存。

• 使用场景：

• 适用于查询操作主要是等值查询，且数据量较小或查询速度要求非常高的情况。适合用于存储引擎如 MEMORY。

• 存储引擎支持：

• 仅支持 MEMORY 存储引擎。

3. 全文索引（Full-Text Index）

全文索引用于 TEXT 类型字段（如 CHAR
、VARCHAR
、TEXT
 等），主要用于快速检索较长的文本。适用于 自然语言搜索。

• 特点：

• 适用于需要全文搜索的字段，能够对文本数据进行快速的查找和匹配。
• 使用 MATCH...AGAINST
   语法来执行查询，支持 自然语言模式 和 布尔模式，可以进行模糊搜索。
• 对于一些特定的词汇（如 a
  、the
   等）会被忽略，这些词汇被称为 "停用词"。

• 使用场景：

• 适用于需要进行全文检索的场景，如新闻网站、搜索引擎、博客系统等。

• 存储引擎支持：

• 仅支持 InnoDB 和 MyISAM 存储引擎。

4. 空间索引（Spatial Index）

空间索引主要用于 地理信息相关GIS 数据类型，如 POINT
、LINESTRING
、POLYGON
 等，支持空间数据的快速查询。

• 特点：

• 空间索引基于 R-Tree 索引，用于优化对空间数据的查询，如查询某个区域内的地理位置数据。
• 支持一些特定的空间查询，如 包含、相交、距离计算 等。

• 使用场景：

• 适用于地理信息系统（GIS）相关的应用，如地图服务、位置数据、地理信息数据的存储和查询等。

• 存储引擎支持：

• 仅支持 MyISAM 存储引擎（InnoDB 8.0 及之后版本支持空间索引，但支持有限）。

Hash索引和B+树索引

Hash索引和B+树区别是什么？在设计索引是怎么抉择的？

• B+树可以进行BETWEEN范围查询，Hash索引不能。
• B+树支持order by排序，Hash索引不支持。
• B+树使用like 进行模糊查询的时候，like后面（比如%开头）的话可以起到优化的作用，Hash索引根本无法进行模糊查询。
• B+树支持 InnoDB、MyISAM 和 Memory，Hash索引仅支持Memory（默认情况）
• B+树支持联合索引的最左侧原则，Hash索引不支持。
• Hash索引在等值查询上比B+树效率更高。

按功能和用途分类

1. 主键索引（Primary Key Index）

• 特点：
• 主键索引是一种特殊的唯一索引。
• 不允许重复，不允许为 NULL。
• 一个表只能有一个主键索引。
• 在 InnoDB 中，主键索引是聚集索引（Clustered Index），数据会按照主键的顺序存储。
• 作用：唯一标识表中的每一行数据。
• 创建方式：

  CREATE TABLE table_name (
      id INT PRIMARY KEY
  );
  
  复制

1. MyISAM 和 InnoDB 对主键的要求

• MyISAM：

• 可以不设置主键。
• 如果不设置主键，MyISAM 依然可以正常工作。

• InnoDB：

• 必须设置主键。如果没有显式定义主键，InnoDB 会根据以下规则选择主键索引：
• 原因：InnoDB 的表文件结构与聚簇索引（Clustered Index）密切相关。每张 InnoDB 表都必须有且仅有一个聚簇索引。

  在 InnoDB 中，主键索引就是聚簇索引。

1. 如果显式设置了主键，则该主键会被选为聚簇索引。
2. 如果没有主键，则会选择第一个不包含 NULL 值的唯一索引作为主键索引。
3. 如果没有符合条件的唯一索引，InnoDB 会创建一个内置的 6 字节长的 ROWID 作为隐含的聚簇索引。ROWID 随着记录的写入而递增。

2. 自增主键 vs 非自增主键

• 自增主键的优势：顺序插入，效率高，数据页分裂少。
• 非自增主键的缺点：插入效率低，产生数据碎片，增加维护开销。

自增主键

• 特点：
• 新记录顺序添加到索引节点的末尾，主键有序排列。
• 写入效率高：由于记录是按顺序插入的，不会产生大量数据移动。
• 当数据页写满时，系统会自动开辟一个新的页，数据页分裂较少。

非自增主键（如身份证号、学号等随机值）

• 特点：
• 主键值分布随机，每次插入新记录时，可能需要将数据插入到索引页的中间位置，这个插入动作就需要移动数据。
• 性能问题：
1. 为了插入新记录，MySQL 需要移动数据，以确保记录顺序存储。
2. 如果目标数据页被刷出缓存，MySQL 需要重新从磁盘加载目标页，增加了 I/O 开销。
3. 数据页分裂会导致存储碎片，索引结构不够紧凑。
4. 需要通过 OPTIMIZE TABLE
    命令进行表重建，优化填充页面。

2. 唯一索引（Unique Index）

• 特点：
• 索引列中的值必须是唯一的。
• 允许为 NULL 值（因为在 MySQL 中，NULL 值不参与唯一性比较）。
• 一个表可以有多个唯一索引。
• 作用：用于防止数据列中出现重复值，同时可以加速查询。
• 创建方式：

  CREATE UNIQUE INDEX idx_unique_name ON table_name(column_name);
  
  复制

3. 普通索引（Normal Index）

• 特点：
• 最基本的索引类型，没有唯一性约束。
• 允许重复值和 NULL 值。
• 作用：加速查询，提高数据检索效率。
• 创建方式：

  CREATE INDEX idx_normal_name ON table_name(column_name);
  
  复制

4. 全文索引（Full-Text Index）

• 特点：

• 主要用于文本数据的分词和内容搜索。
• 支持对 CHAR
  、VARCHAR
   和 TEXT
   类型的列进行全文索引。
• 只能在 MyISAM、InnoDB 存储引擎上使用（5.6 版本后 InnoDB 开始支持）。

• 作用：用于关键字搜索、文本内容检索。

• 常见语法：

  CREATE FULLTEXT INDEX idx_fulltext_name ON table_name(column_name);
  
  复制

• 使用示例：

  SELECT * FROM table_name
  WHERE MATCH(column_name) AGAINST ('关键字');
  
  复制

5. 覆盖索引（Covering Index）

• 特点：
• 如果一个查询所需的列都包含在一个索引中，则该索引称为覆盖索引。
• 覆盖索引可以避免回表操作，查询时直接从索引中获取数据，从而提高性能。
• 作用：提高查询性能，减少 I/O 操作。
• 示例：

  CREATE INDEX idx_covering ON table_name(column1, column2);
  
  -- 查询时，column1 和 column2 都被索引覆盖，不需回表
  SELECT column1, column2 FROM table_name WHERE column1 = 'value';
  
  复制

6. 组合索引（Composite Index）

• 特点：

• 将多个列组合起来创建一个索引。
• 适用于需要多个列进行条件筛选的查询，效率通常高于多个单列索引的合并。
• 组合索引遵循最左前缀原则：查询条件必须包含索引中最左侧的列，才能有效利用索引。

• 作用：加速多列条件的查询。

• 示例：

  CREATE INDEX idx_composite ON table_name(column1, column2, column3);
  
  复制

• 最左前缀原则：

• WHERE column1 = 'value'
   → 有效。
• WHERE column1 = 'value' AND column2 = 'value'
   → 有效。
• WHERE column2 = 'value'
   → 无效（不符合最左前缀原则）。
• 索引 idx_composite(column1, column2, column3)
   的使用情况：

总结

• 主键索引：唯一标识一行数据，不允许 NULL 和重复。
• 唯一索引：确保数据唯一，允许 NULL，表中可以有多个唯一索引。
• 普通索引：基础索引类型，允许重复值和 NULL。
• 全文索引：适合文本内容搜索，通过分词技术实现。
• 覆盖索引：查询列被索引完全覆盖，避免回表，提高查询性能。
• 组合索引：多个列的组合索引，遵循最左前缀原则，适用于多条件查询。

这些索引类型各有特点，具体使用时需要根据查询需求和性能要求进行选择和优化。

按存储方式划分的索引

在 MySQL 中，存储数据和索引的组织方式上存在根本性差异。可以分为聚簇索引 和 非聚簇索引 是两种重要的索引结构。

1. 聚簇索引 vs 非聚簇索引

• 聚簇索引：数据和索引一起存储，索引即数据，适合频繁主键查询和范围查询的场景，InnoDB 默认使用，在 InnoDB 中，主键索引就是聚簇索引。
• 非聚簇索引：索引和数据分开存储，灵活性更高，适合需要多个索引和频繁更新的场景。

2. 聚簇索引（Clustered Index）

聚簇索引是指数据和索引存储在同一个结构中，索引的叶子节点保存的是实际的数据行。因此，表的行数据会按照主键（或聚簇索引的列）进行物理排序。

• InnoDB 引擎 中，聚簇索引是表的默认存储方式，主键索引就是聚簇索引。
• 每张表只能有一个主键/聚簇索引，因为数据的物理存储顺序只能有一种，索引的叶子节点存储的是完整数据。

3. 非聚簇索引（Non-Clustered Index）

非聚簇索引是指索引和数据分开存储，索引的叶子节点存储的是数据的指针或主键值，而不是实际的数据行。

• 非聚簇索引可以有多个，一个表可以创建多个非聚簇索引。
• MyISAM 引擎中的所有索引都是非聚簇索引。
• 在 InnoDB 中，所有非主键索引都是非聚簇索引，也称为辅助索引或者二级索引。
• 非聚簇索引的叶子节点存储的是数据的指针（或主键值），通过指针找到对应的数据行，需要 "回表" 操作才能获取完整的数据。

为什么非聚簇索引的叶子节点存储主键值？

InnoDB 的索引分为两类：

1. 聚簇索引（Clustered Index）：主键索引，叶子节点存储的是数据行本身。
2. 非聚簇索引（Secondary Index）：非主键索引（也称：二级索引、非主键索引、辅助索引），叶子节点存储的是主键值。

非聚簇索引的叶子节点存储主键值的主要原因是为了减少二级索引的维护开销：

• 如果二级索引的叶子节点存储的是主键值，当发生行移动或数据页分裂时，二级索引不需要更新。
• 数据的真实位置发生变化，只需在聚簇索引（主键索引）中修改数据的物理位置即可。
• 由于一个表只能有一个聚簇索引，二级索引的维护开销更小，不需要重新构建索引。

示例：

• 假设有一个 InnoDB 表，包含主键索引和多个二级索引。
• 当你更新某个记录的物理位置（如数据页分裂导致数据移动）：
• 聚簇索引：记录数据位置的变化。
• 二级索引：由于叶子节点存储主键值（而非物理地址），所以不受影响，不需要重新构建二级索引。

索引创建原则

• 数据量少的不适合创建索引
• 频繁更新的字段不适合创建索引
• 频繁作为查询条件的字段才去创建索引
• 区分度低的字段不适合加索引（如性别）
• 优先考虑扩展索引，而不是新建索引，避免不必要的索引
• 删除不再使用或者很少使用的索引

1. 选择合适的字段创建索引：

• 不为 NULL 的字段：索引字段的数据应该尽量不为 NULL，因为对于数据为 NULL 的字段，数据库较难优化。如果字段频繁被查询，但又避免不了为 NULL，建议使用 0
  、1
  、true
  、false
   这样语义较为清晰的短值或短字符作为替代。
• 被频繁查询的字段：我们创建索引的字段应该是查询操作非常频繁的字段。
• 被作为条件查询的字段：被作为 WHERE
   条件查询的字段，应该被考虑建立索引。
• 频繁需要排序的字段：索引已经排序，这样查询可以利用索引的排序，加快排序查询时间。
• 被经常频繁用于连接的字段：经常用于连接的字段可能是一些外键列，对于外键列并不一定要建立外键，只是说该列涉及到表与表的关系。对于频繁被连接查询的字段，可以考虑建立索引，提高多表连接查询的效率。

2. 被频繁更新的字段应该慎重建立索引。

• 虽然索引能带来查询上的效率，但是维护索引的成本也是不小的。如果一个字段不被经常查询，反而被经常修改，那么就更不应该在这种字段上建立索引了。

3. 尽可能的考虑建立联合索引而不是单列索引。

• 因为索引是需要占用磁盘空间的，可以简单理解为每个索引都对应着一颗 B+树。如果一个表的字段过多，索引过多，那么当这个表的数据达到一个体量后，索引占用的空间也是很多的，且修改索引时，耗费的时间也是较多的。如果是联合索引，多个字段在一个索引上，那么将会节约很大磁盘空间，且修改数据的操作效率也会提升。

4. 注意避免冗余索引。

• 冗余索引指的是索引的功能相同，能够命中索引 (a, b)
   就肯定能命中索引 (a)
   ，那么索引 (a)
   就是冗余索引。如（name, city
  ）和（name
  ）这两个索引就是冗余索引，能够命中前者的查询肯定是能够命中后者的。在大多数情况下，都应该尽量扩展已有的索引而不是创建新索引。

5. 考虑在字符串类型的字段上使用前缀索引代替普通索引。

• 前缀索引仅限于字符串类型，较普通索引会占用更小的空间，所以可以考虑使用前缀索引带替普通索引。

索引失效

索引会失效的主要原因包括：

1. 未遵循最左前缀匹配原则。
2. LIKE 模糊匹配使用 %
    开头。
3. 索引列参与计算或函数操作。
4. OR 条件中的列不同时使用索引。
5. 字段类型不匹配（如字符串索引字段查询时未用引号括起来）。
6. 使用不支持的操作符（如 !=
   、NOT IN
   ）。
7. 使用 IS NULL 或 IS NOT NULL。
8. 表关联字段的编码格式不一致。
9. MySQL 优化器评估全表扫描更快。
10. 列对比查询条件导致索引失效。

1. 最左前缀匹配原则

• 联合索引必须从最左列开始连续匹配，否则索引失效。
• 示例：
• 联合索引 (a, b, c)
  ，查询 WHERE b = 1
   或 WHERE c = 1
   索引失效。
• 查询 WHERE a = 1 AND c = 3
  ：这种其实严格意义上来说是属于索引截断，在 MySQL 5.5 中只对 a
   有效，从 MySQL 5.6 开始可通过**索引下推（Using index condition）**优化对 a
   和 c
   利用索引。

2. LIKE 通配符使用不当

• 使用 LIKE
   时，以下情况可能导致索引失效：
• LIKE '%xx'
   或 LIKE '%xx%'
   索引失效。
• LIKE 'xx%'
   索引有效，因为前缀有序。但需要注意字段的数据类型。例如，如果索引列为 CHAR 类型且查询值长度不匹配，也可能导致索引失效。
• 示例：

  EXPLAIN SELECT * FROM table_name WHERE a LIKE '%As'; -- 索引失效
  EXPLAIN SELECT * FROM table_name WHERE a LIKE 'As%'; -- 使用索引
  
  复制

3. 索引列参与计算或函数操作

• 计算：对索引列进行计算操作（如 +
  、-
  、*
  、/
  ）会导致索引失效。

  EXPLAIN SELECT * FROM t_user WHERE id + 1 = 10; -- 索引失效
  EXPLAIN SELECT * FROM t_user WHERE id = 10 - 1; -- 使用索引
  
  复制

• 函数操作：在查询条件中对索引列使用函数（如 LENGTH(name)
  ），索引失效。
• 从 MySQL 8.0 开始，可以通过创建函数索引解决此问题：

  ALTER TABLE t_user ADD KEY idx_name_length ((LENGTH(name)));
  
  复制

  为函数(LENGTH(name))
  创建索引。

4. 使用 OR 条件

• 如果 WHERE
   子句中存在 OR
   条件，且 OR
   两侧的字段不同时使用索引，索引失效。
• 示例：

  EXPLAIN SELECT * FROM t_user WHERE id = 1 OR age = 18; -- 索引失效
  
  复制

• 如果 OR
   两边的条件都能使用索引，且每个索引在不同的列上，MySQL 可以合并索引结果。
• 示例：

  SELECT * FROM t_user WHERE id = 1 OR name = 'Alice';
  
  复制

  如果 id
   和 name
   都有索引，可以通过 EXPLAIN
   验证是否使用了索引。

5. 字段类型不匹配

• 索引字段的类型与查询条件中的数据类型不一致，索引失效。
• 示例：索引字段为字符串类型（VARCHAR
  ），查询条件中未用引号括起来。

  SELECT * FROM t_user WHERE id = 123; -- 索引失效（id 为字符串）
  SELECT * FROM t_user WHERE id = '123'; -- 使用索引
  
  复制

6. 不支持的操作符

• 以下操作符可能导致索引失效：
• !=
  、<>
  、NOT IN
  。
• 示例：

  SELECT * FROM t_user WHERE id != 10; -- 索引失效
  
  复制

7. 使用 IS NULL 或 IS NOT NULL

• 查询条件中使用 IS NULL
   或 IS NOT NULL
  ，可能导致索引失效。

• 示例：

  SELECT * FROM t_user WHERE id IS NOT NULL; -- 索引可能失效
  
  复制

  说明：

• IS NULL
   和 IS NOT NULL
   不一定总是导致索引失效。在某些版本的 MySQL 中，IS NULL
   可以利用索引。
• 示例：

  SELECT * FROM t_user WHERE column IS NULL; -- 可能使用索引（取决于索引和版本）
  
  复制

• 是否失效取决于具体版本、存储引擎（如 InnoDB）以及查询优化器的行为。建议根据实际查询计划（EXPLAIN
  ）进行验证。

8. 表关联字段编码不一致

• 在左连接或右连接查询中，如果关联字段的编码格式不一致，索引失效。

9. MySQL 优化器选择全表扫描

• 当 MySQL 估计全表扫描比使用索引更快时，会选择全表扫描，导致索引失效。
• 例如：
• 数据量少。
• 索引列的区分度低。

10. 列对比导致索引失效

• 如果查询条件中用两个已建索引的列做比较，可能导致索引失效。
• 示例：

  SELECT * FROM table_name WHERE id = height; -- 索引失效
  
  复制