当前位置：首页 > article >正文

MySQL中索引全详解

article 2024/11/26 1:38:23

第一部分：什么是索引

索引在数据库中就像书的目录，能够快速定位数据位置，从而提升查询效率。没有索引时，数据库查询需要从头到尾扫描整个表（称为全表扫描），这在数据量大时非常耗时。有了索引后，查询就像查找字典中某个字母开头的单词一样，可以直接跳转到相关数据，大大加快了响应速度。

索引的原理可以简单理解为：

数据库通过创建额外的“数据结构”（类似目录）存储索引字段的信息。
查询时，数据库优先查找索引，再根据索引定位到实际数据。
索引对查询速度有提升，但同时也会增加写入和更新的时间，因为每次修改表数据时，索引也需要维护和更新。

简单总结，索引的核心作用是提高查询速度，但这是一种以额外存储和维护成本换取查询效率的优化方式。

第二部分：索引的分类

我们按照三个角度来详细分析索引的分类：数据结构、字段特性、字段个数。

1.按数据结构分类

这是基于索引底层的存储和组织方式来划分的，常见的有以下三种：

1.1 B+Tree 索引

原理：
- B+Tree 索引是 MySQL 中最常用的索引结构，底层采用的是平衡多路搜索树。
- 数据以“节点”的形式存储，叶子节点存储了表中所有的索引字段值，并以链表形式连接；非叶子节点作为目录，用于快速查找。
- 查找过程：从根节点开始，依次向下遍历，直到叶子节点。
特点：
- 有序存储，支持范围查询。
- 查询效率稳定，查找时间复杂度为 O(log⁡n)O(logn)。
- 适用于大多数场景，包括等值查询、范围查询、排序等。
适用场景：
- 查询条件中包含范围操作（如 BETWEEN、> <）。
- 常见于主键索引和普通索引。
局限性：
- 如果字段值分布不均匀，可能导致某些查询的效率下降。
- 对频繁插入或更新的大表性能有一定影响，因为需要维护树的平衡。

1.2 Hash 索引

原理：
- 使用哈希函数将字段值映射为哈希值，哈希值对应实际数据的位置。
- 适合等值查询，类似于通过钥匙直接打开锁。
特点：
- 查询速度极快，时间复杂度接近 O(1)O(1)。
- 不支持范围查询，因为哈希值无法保持顺序性。
- 哈希冲突可能影响性能。
适用场景：
- 等值查询场景，例如 WHERE id = 10。
- 用于对性能要求极高、数据分布均匀的表。
局限性：
- 不支持范围查询（如 BETWEEN、> <）。
- 不适合排序或分组操作。

1.3 Full-Text 索引

原理：
- 专门为全文搜索设计的一种索引，类似搜索引擎的倒排索引。
- 将文本内容切分为关键词，并建立关键词到文档的映射关系。
特点：
- 支持模糊查询、多关键字匹配。
- 效率远高于使用 LIKE '%...%' 的查询方式。
适用场景：
- 对大文本字段（如文章、评论）进行搜索，例如实现类似搜索引擎的功能。
局限性：
- 配置复杂，只支持特定存储引擎（如 MyISAM、InnoDB 的部分版本）。
- 对于频繁更新的大文本字段性能较差。

2. 按字段特性分类

这是基于字段在表中的角色和限制条件来划分的，主要有以下几种：

2.1 主键索引

定义：
- 主键是表中的唯一标识，每张表只能有一个主键。主键索引是数据库默认为主键字段生成的索引。
特点：
- 唯一性，保证每行数据的主键值不同。
- 主键索引一般使用 B+Tree 实现，叶子节点存储完整的行数据。
适用场景：
- 必须保证数据唯一性，例如用户 ID、订单号等。

2.2 唯一索引

定义：
- 唯一索引与主键类似，但表中可以有多个唯一索引。
- 保证字段值唯一，但允许存在空值（NULL）。
特点：
- 避免重复值，确保数据完整性。
- 唯一索引一般用于非主键字段。
适用场景：
- 确保特定字段的值不重复，例如邮箱、用户名等。

2.3 普通索引

定义：
- 没有唯一性限制，仅用于提升查询速度。
特点：
- 普通索引可以在任意字段上添加。
- 支持多种查询操作（等值、范围等）。
适用场景：
- 提高查询效率，但对唯一性无严格要求的字段。

2.4 前缀索引

定义：
- 针对字符串字段，只索引前面几位字符，而不是整列。
特点：
- 节省存储空间，提升索引效率。
- 对于区分度较高的字符串字段适用。
适用场景：
- 长字符串字段，如 URL、电子邮件地址等。

3. 按字段个数分类

这是根据字段数量来划分的，主要有以下两种：

3.1 单列索引

定义：
- 索引只包含一个字段。
特点：
- 适合简单的查询条件（单字段查询）。
- 无法直接优化多字段组合查询。
适用场景：
- 单一字段的等值或范围查询。

3.2 联合索引（复合索引）

定义：
- 索引包含多个字段，以字段顺序为准。
特点：
- 支持最左前缀原则，即查询条件必须包含从左到右的字段顺序。
- 高效优化多字段查询，但顺序很重要。
适用场景：
- 多字段组合查询，例如 WHERE col1 = ? AND col2 = ?。

第三部分：何时需要/无需索引

1. 什么时候需要创建索引？

创建索引的核心目的是优化查询性能，因此以下场景适合创建索引：

查询条件中频繁使用的字段
- 如果某个字段经常出现在 WHERE、JOIN、GROUP BY 或 ORDER BY 中，应该为该字段创建索引。例如：

SELECT * FROM orders WHERE customer_id = 1001;

- 为 customer_id 创建索引可以显著提升查询速度。
数据量大的表
- 对于大表，如果没有索引，查询时需要进行全表扫描，这会严重拖慢性能。
- 例如，有一张订单表有上百万条数据，为订单号字段 order_id 创建索引，可以极大提高查找效率。
高频查询的字段
- 如果一个字段经常用于查询，即便表数据量不大，也可以考虑创建索引，因为优化高频操作的效率尤为重要。
排序和分组操作的字段
- 如果查询中包含排序（ORDER BY）或分组（GROUP BY），为相关字段创建索引可以加速操作。

SELECT product_id, COUNT(*) FROM sales GROUP BY product_id;

2. 什么时候不需要创建索引？

索引虽然有助于查询，但也有成本，以下情况不适合创建索引：

小表或结果集较大的查询
- 如果表的数据量很小（例如几十条数据），索引的优化效果微乎其微，反而会增加维护成本。
- 类似地，如果查询结果总是返回表的大部分数据（如 80% 以上），索引优化的意义不大。
更新频繁的字段
- 索引需要在数据修改时同步更新，因此对于频繁更新的字段，创建索引可能导致写入性能下降。
- 例如日志表中的 last_updated_time 字段频繁更新，此时不建议为其创建索引。
重复性高的字段
- 如果字段的值高度重复，索引会失去意义。例如：性别字段 gender（只有男或女）在查询中不建议单独建立索引。
临时表或测试表
- 临时数据表通常存活时间短，查询次数少，因此无需索引。

第四部分：优化索引的方法

索引的设计和使用需要考虑性能和实际需求，以下是几种常见的优化索引的方法：

1. 前缀索引优化

场景和原理

适用场景：当字段值较长（如字符串类型），且前缀部分可以区分大多数记录时，使用前缀索引既能节省存储空间，又能加速查询。
原理：前缀索引只记录字段值的前 N 个字符，降低了索引的大小，但仍能起到查询加速的作用。

优化步骤

选择合适的前缀长度
选择长度时，需要保证前缀的区分度（即前缀的唯一性较高）。可以通过以下查询评估：
```
SELECT COUNT(DISTINCT LEFT(column_name, N)) / COUNT(*) AS prefix_selectivity FROM table_name;
```
- 如果区分度接近 1，说明前缀长度合适。
创建前缀索引
- 使用 CREATE INDEX 指定前缀长度：
```
CREATE INDEX idx_prefix ON users (email(10));
```

适用场景的示例
假设有一个邮件用户表，每个用户的邮箱 email 字段长度不一，且查询通常只匹配前缀部分：

SELECT * FROM users WHERE email LIKE 'john%';

使用前缀索引可显著提升效率。

2. 覆盖索引优化

定义与优点

覆盖索引：当索引本身包含了查询所需的全部字段，无需回表查询，即称为覆盖索引。
优点：减少磁盘 I/O 和查询时间。

如何实现覆盖索引

设计包含所有查询字段的索引
- 例如，针对以下查询：
```
SELECT id, name FROM employees WHERE department_id = 10;
```
  可以创建覆盖索引：
```
CREATE INDEX idx_covering ON employees (department_id, id, name);
```
- 索引中的字段顺序很重要，应优先按查询条件出现的字段排列。
使用查询分析工具
- 使用 EXPLAIN 分析查询是否使用了覆盖索引：
```
EXPLAIN SELECT id, name FROM employees WHERE department_id = 10;
```

3. 主键索引最好是自增的

自增主键的优势

避免页分裂：
- 自增主键的值是递增的，因此每次插入数据时，新记录会追加到索引的最后一个叶子节点，避免了频繁的页分裂。
提升插入效率：
- 自增主键的插入是顺序的，减少了磁盘 I/O。

非自增主键的问题

随机插入导致性能下降：
- 如果主键是随机值（如 UUID），新数据可能插入到索引的任意位置，导致频繁的页分裂和性能下降。

4. 防止索引失效

什么是索引失效？

当查询无法使用已有索引时，称为索引失效。这会导致查询退化为全表扫描，性能显著下降。

常见导致索引失效的场景

使用函数或计算操作

如以下查询会导致索引失效：

SELECT * FROM orders WHERE YEAR(order_date) = 2023;

解决方法：将计算移到索引之外：

SELECT * FROM orders WHERE order_date >= '2023-01-01' AND order_date < '2024-01-01';

类型不匹配

如果索引字段和查询条件的类型不一致，会导致索引失效。

SELECT * FROM users WHERE phone_number = 12345; -- phone_number 是字符串类型

解决方法：确保类型一致：

SELECT * FROM users WHERE phone_number = '12345';

查询条件中使用 OR
- 如果 OR 中的字段未全部使用索引，会导致索引失效。
```
SELECT * FROM employees WHERE department_id = 10 OR name = 'Alice';
```
  - 解决方法：改为使用 UNION，确保每个查询条件单独使用索引：
```
SELECT * FROM employees WHERE department_id = 10 UNION SELECT * FROM employees WHERE name = 'Alice';
```
模糊查询中通配符的位置
- 以下查询会导致索引失效：
```
SELECT * FROM products WHERE name LIKE '%phone';
```
  - 解决方法：避免通配符在前，或者考虑全文索引：
```
SELECT * FROM products WHERE name LIKE 'iPhone%';
```