数据库索引详解：类型、优缺点及使用场景

---

一、什么是 MySQL 索引？

MySQL 索引，通俗来说，就是 一本书的目录，它可以帮助数据库快速找到数据。假设你要查找一本厚厚的书中某个特定的内容，如果没有目录，你可能需要从第一页翻到最后一页，效率很低。而如果这本书有了目录，你只需要查找目录中相关的章节，快速定位到所需内容。

在数据库中，索引的作用就像书籍目录一样，帮助数据库 快速定位到具体的数据，避免全表扫描（也就是从头到尾查找），大大提高查询效率。

举个例子：

假设有一个 users 表，里面有成千上万条数据，存储了每个用户的姓名、年龄、邮箱等信息。

id	name	age	email
1	Alice	30	alice@example.com
2	Bob	25	bob@example.com
3	Charlie	35	charlie@example.com
4	Dave	40	dave@example.com

如果你想通过 name 字段来查找某个用户，假如没有索引，数据库会需要扫描所有行数据，直到找到 name 匹配的记录，效率很低。假如 users 表有几十万条记录，这种做法非常浪费时间。

如何提高查询效率？

在 name 字段上创建一个索引，就像给这张表加了一个目录。数据库可以通过索引，直接找到符合条件的记录，而不需要扫描整个表。

CREATE INDEX idx_name ON users (name);

这样，数据库在执行查询时，会先利用索引快速定位到 name 字段的值，而不需要从第一行开始扫描每个记录。

二、索引的分类

1. 主键索引（Primary Key Index）

主键索引用于保证每一行数据的唯一性。主键索引在表中自动创建，且一个表只能有一个主键索引。主键索引不仅保证数据唯一性，还自动创建聚集索引，数据会按照主键的顺序进行存储。

MySQL 5.x vs MySQL 8.x：

• MySQL 5.x： 主键索引行为一致，NULL 值无法存在于主键列中，范围查询对性能有影响，特别是在多条件查询时。

• MySQL 8.x： 在范围查询和多条件组合查询上与 MySQL 5.x 相似，但在一些复杂查询的优化上，MySQL 8.x 提供了更好的查询计划优化。

优点

• 保证数据的唯一性。

• 自动创建聚集索引，数据存储的顺序是按主键排序的。

失效场景

• NULL值： 如果查询条件中涉及 NULL 值，索引会失效，因为主键不允许 NULL 值。

• 范围查询： 主键索引适合精确查找，对于范围查询（例如 BETWEEN、>、<）有时会导致索引失效，尤其是多个条件组合时。

SQL 示例

-- 正常：主键索引适用于精确匹配
SELECT * FROM users WHERE id = 10;-- 失效：使用范围查询，主键索引可能失效
SELECT * FROM users WHERE id BETWEEN 1 AND 100;

2. 唯一索引（Unique Index）

唯一索引保证索引列中的值是唯一的，但允许 NULL 值。一个表可以有多个唯一索引。唯一索引比普通索引多了唯一性的约束，保证索引列的数据不重复。

MySQL 5.x vs MySQL 8.x：

• MySQL 5.x： 唯一索引在 IN 查询中会按照常规方式优化，但如果查询条件中涉及大量值，性能可能下降。

• MySQL 8.x： 在 MySQL 8.x 中，优化器进行了很多改进，特别是对于包含 IN 操作符的查询，MySQL 8.x 可能会更加智能地选择索引，提升查询性能。

优点

• 保证索引列的数据唯一性。

• 能够提高查询性能，尤其是对于唯一性字段（如邮箱、用户名等）。

失效场景

• OR 查询： 如果查询中使用了 OR 操作符，且条件列没有索引，会导致索引失效。

• 函数使用： 如果查询中对唯一索引列使用了函数（例如 LOWER()、CONCAT()），则索引会失效。

SQL 示例

--- 正常：唯一索引适用于精确查找
SELECT * FROM users WHERE email = 'test@example.com';-- 失效：使用 `OR` 查询时，索引可能失效
SELECT * FROM users WHERE email = 'test@example.com' OR username = 'john';-- 失效：使用函数时，索引失效
SELECT * FROM users WHERE LOWER(email) = 'test@example.com';

3. 普通索引（Non-Unique Index）

普通索引用于加速查询操作，不强制要求列唯一。普通索引适用于需要快速查询的列，通常应用于大数据表的查询。

MySQL 5.x vs MySQL 8.x：

• MySQL 5.x： IN 查询和 LIKE 查询中，普通索引的使用有一些限制，尤其是对于前缀通配符的 LIKE 查询，普通索引可能不生效。

• MySQL 8.x： MySQL 8.x 中，对 IN 查询的优化做了许多改进，IN 查询能够更好地利用索引。对于 LIKE 查询，MySQL 8.x 在某些情况下能够优化前缀通配符的查询，从而有效利用索引。

优点

• 提升查询性能，尤其是对于经常被查询的非唯一字段。

失效场景

• LIKE 查询： 如果使用 LIKE 查询，且使用了前置通配符（例如 %value），索引可能会失效。后置通配符（例如 value%）通常可以利用索引。

• IN 操作符： 如果 IN 中的值非常多，数据库可能不会使用索引，或者索引效果会变差。

SQL 示例

-- 正常：普通索引适用于精确匹配
SELECT * FROM users WHERE name = 'John';-- 失效：`LIKE` 查询时使用了前置通配符
SELECT * FROM users WHERE name LIKE '%John';-- 正常：`LIKE` 查询时使用了后置通配符
SELECT * FROM users WHERE name LIKE 'John%';

4. 联合索引（Composite Index）

联合索引指的是一个由多个字段组合而成的索引，它能够提高对多个列的查询效率。在 MySQL 中，联合索引通常用于加速多列条件的查询，尤其是在涉及多个列的 WHERE 子句中。

MySQL 5.x vs MySQL 8.x：

• MySQL 5.x： 联合索引对列的顺序要求严格，不正确的列顺序会导致索引失效。MySQL 5.x 对联合索引的优化较少，查询性能受限。

• MySQL 8.x： MySQL 8.x 对联合索引的优化得到了增强，对于特定列顺序的查询，索引使用效率更高，查询计划优化更加智能。

优点

• 可以加速多列联合查询的效率。

• 在某些查询中，联合索引可以代替多个单列索引，提高性能。

失效场景

• 使用 IN 时：

  在 MySQL 5.x 中，IN 会导致联合索引失效，因为 IN 会导致查询条件的顺序不固定。

  在 MySQL 8.x 中，IN 可能仍然会利用联合索引，但仍然依赖于查询的条件顺序是否满足最左前缀原则。

• 使用 LIKE 时： 如果在 LIKE 查询中使用了通配符（如 %）放在字段的前面（%abc），那么联合索引会失效。如果通配符在字段的后面（abc%），则联合索引仍然会生效。

• 使用 OR 时： 如果查询条件中包含 OR，那么联合索引通常会失效，特别是 OR 查询条件中涉及到的字段不完全匹配索引的顺序或包含了索引外的字段。

SQL 示例

创建联合索引的 SQL 示例：

假设有一个 users 表，我们希望根据 name 和 age 来查询用户信息，可以创建一个联合索引：

CREATE INDEX idx_name_age ON users (name, age);

这个联合索引包含了两个字段 name 和 age，它可以帮助加速如下的查询：

SELECT * FROM users WHERE name = 'John' AND age = 25;

联合索引的使用原则

1. 最左前缀原则： 联合索引的使用依赖于查询条件的顺序。如果查询条件匹配联合索引的字段顺序，那么索引就会生效。最左前缀原则指的是，在查询时，如果条件字段顺序从左到右依次与联合索引的字段匹配，那么 MySQL 就会使用该联合索引。

   符合最左前缀原则的查询：

   SELECT * FROM users WHERE name = 'John';  -- 只查询 name，符合最左前缀原则
   SELECT * FROM users WHERE name = 'John' AND age = 25;  -- 符合最左前缀原则
   SELECT * FROM users WHERE name = 'John' AND age = 25 AND sex = 'M';  -- 完全匹配联合索引的字段

2. 不符合最左前缀原则的查询： 如果查询中的条件顺序不符合联合索引的字段顺序，或者缺少某些字段，那么该索引就不能被有效利用。

   不符合最左前缀原则的查询：

   SELECT * FROM users WHERE age = 25;  -- 不符合最左前缀原则，索引不生效
   SELECT * FROM users WHERE sex = 'M' AND age = 25 AND name = 'John';  -- 不符合最左前缀原则

3. 动态调整字段顺序： 如果查询中的条件字段顺序与联合索引顺序不完全一致，但包含了联合索引的字段时，MySQL 会动态调整查询顺序来尽量匹配最左前缀原则。因此，即使查询字段顺序不完全符合索引的顺序，MySQL 也有可能仍然使用联合索引。

   例如：

   SELECT * FROM users WHERE sex = 'M' AND name = 'John' AND age = 25;  -- MySQL 会调整查询顺序来匹配索引

4. 字段缺失： 如果查询中的条件缺少联合索引中的某些字段（特别是索引的最左边字段），那么该联合索引也无法生效。

   例如：

   SELECT * FROM users WHERE age = 25 AND sex = 'M';  -- 缺少索引中的最左字段 'name'，索引不生效

5. 覆盖索引（Covering Index）

覆盖索引是一种特殊类型的索引，覆盖索引的核心原则只有当查询的所有字段都被某个索引包含时，才能触发覆盖索引，无论是单列索引还是联合索引，只要索引中包含了查询所需的所有字段，MySQL 就会使用覆盖索引来完成查询，而不需要回表。

MySQL 5.x vs MySQL 8.x：

• MySQL 5.x： 覆盖索引的支持与当前查询的条件字段是否完全包含在索引中直接相关。若查询的字段包含在索引中，则能完全利用覆盖索引。

• MySQL 8.x： MySQL 对覆盖索引的支持更加智能，可能会更优化地选择使用覆盖索引，即使查询中的某些字段是通过联合索引的顺序进行匹配的。

覆盖索引的使用场景

1. SELECT 查询只涉及索引列： 如果查询只涉及索引中的字段，并且所有字段都能从索引中获取，那么 MySQL 就会选择覆盖索引，而无需回表查询。

2. ORDER BY 和 GROUP BY： 如果 ORDER BY 或 GROUP BY 的字段也被索引覆盖，MySQL 就能通过索引直接完成排序或分组操作，而不需要访问实际的数据表。

优点

• 减少 I/O 操作： 覆盖索引避免了回表查询的操作，因为查询的字段已经包含在索引中，不需要再去查找数据表。

• 提升查询性能： 由于 MySQL 不需要访问数据表，查询速度更快，尤其在大数据量表中，性能提升尤为明显。

• 适用于 SELECT 操作： 覆盖索引特别适用于 SELECT 查询，尤其是涉及多个字段的查询。

覆盖索引的触发条件

• 单列索引：当查询的字段仅包含索引中的单一字段时，如果这个字段是查询所需的全部字段，则 MySQL 会使用该单列索引作为覆盖索引。

• 联合索引（复合索引）：当查询涉及多个字段时，MySQL 会根据最左前缀原则，判断是否可以使用联合索引。如果联合索引中包含了查询所需的所有字段，则可以触发覆盖索引。

失效场景

• 查询涉及的字段不完全包含在索引中： 如果查询中涉及的字段没有完全被覆盖（即索引中不包含查询字段），那么就无法利用覆盖索引，需要回表查询。

• 索引字段顺序不符合最左前缀原则：如果查询的条件字段顺序不符合联合索引的最左前缀原则，则无法使用覆盖索引。

• 当查询字段有多个单列索引组成：

  在某些查询中，MySQL 会使用多个单列索引来处理查询条件，这被称为“索引合并”。然而，即使使用了索引合并，MySQL 仍然可能需要回表来获取其他未包含在索引中的字段。

  如果每个查询条件都有单列索引，并且 MySQL 可以使用索引合并策略，那么它会结合多个单列索引的结果进行查询，但这不等同于触发覆盖索引

SQL 示例

假设有一个 users 表，包含 id、name、age 和 email 四个字段。如果我们常常查询 name 和 age 字段，那么我们可以为这两个字段创建一个覆盖索引：

CREATE INDEX idx_name_age ON users (name, age);

接下来，如果我们进行如下查询：

SELECT name, age FROM users WHERE name = 'John' AND age = 25;

由于查询的 name 和 age 都被包含在索引 idx_name_age 中，MySQL 就可以通过这个索引直接返回结果，而不需要查找 users 表中的实际数据。

---