Mysql大师之路：索引应用

最新推荐文章于 2024-10-02 06:14:43 发布

饭叮叮的学习日记

最新推荐文章于 2024-10-02 06:14:43 发布

阅读量929

点赞数 14

文章标签： mysql 数据库

本文链接：https://blog.csdn.net/m0_55839103/article/details/141707538

版权

MySQL 中的索引类型

在 MySQL 中，索引是用于提高查询速度的一种数据结构。不同类型的索引适用于不同的应用场景，以下是 MySQL 中常见的索引类型：

主键索引（Primary Key Index）:
- 定义: 主键索引是建立在表的主键字段上的索引。在 MySQL 的 InnoDB 引擎中，主键索引是聚簇索引，意味着主键索引的 B+树叶子节点存储了实际的行数据。
- 特点:
  - 一个表只能有一个主键索引。
  - 主键索引列的值必须唯一且不允许为空。
  - 主键索引提供快速的记录定位，确保每一行数据的唯一性和完整性。
唯一索引（Unique Index）:
- 定义: 唯一索引是建立在 UNIQUE 字段上的索引，用于保证索引列的值是唯一的。
- 特点:
  - 一个表可以有多个唯一索引。
  - 唯一索引列的值必须唯一，但允许存在空值。
  - 在查询操作中，唯一索引可用于加速数据的查找和过滤。
普通索引（Normal Index）:
- 定义: 普通索引是建立在普通字段上的索引，既不要求字段为主键，也不要求字段为 UNIQUE。
- 特点:
  - 普通索引没有唯一性要求，一个表可以有多个普通索引。
  - 提供快速的数据检索，但不能保证数据的唯一性。
  - 适用于频繁进行数据检索的字段。
前缀索引（Prefix Index）:
- 定义: 前缀索引是指对字符类型字段的前几个字符建立的索引，而不是在整个字段上建立的索引。前缀索引可用于 CHAR、VARCHAR、BINARY、VARBINARY 类型的列。
- 特点:
  - 前缀索引仅对字段的前部分字符进行索引，从而减少索引占用的存储空间。
  - 提升查询效率，尤其适用于长文本或字符串字段（如文章标题、商品名称等）。
  - 因为不是全字段索引，可能导致查询的精确度降低，需根据实际情况设置前缀长度。
联合索引（Composite Index）:
- 定义: 联合索引是将多个字段组合在一起作为一个索引，索引中的字段按顺序组成一个多级索引。
- 特点:
  - 联合索引的目的是为了覆盖更多的查询场景，减少回表操作。
  - 联合索引遵循 最左前缀匹配原则：索引会按字段的组合顺序来使用，查询时应按索引字段的顺序来使用查询条件。
  - 能有效利用索引覆盖，提高查询性能。

MySQL 主键是否是聚簇索引？

聚簇索引（Clustered Index） 是一种特殊的索引类型，它将表中的数据行实际存储在 B+树的叶子节点中。聚簇索引中的叶子节点不仅包含索引键值，还存储了整行数据。这种数据组织方式将索引和数据聚集在一起，故称为聚簇索引。

在 MySQL 的 InnoDB 存储引擎中，每张表都会有一个聚簇索引，这是表数据在物理上的实际存储方式。

InnoDB 聚簇索引创建规则

InnoDB 存储引擎在创建聚簇索引时，会根据以下规则选择索引键：

如果定义了主键:
- InnoDB 会将主键作为聚簇索引的索引键。也就是说，表的主键字段会作为 B+树的索引，并且叶子节点会存储实际的行数据。
如果没有定义主键:
- InnoDB 会选择第一个不包含 NULL 值的唯一列作为聚簇索引的索引键。
如果既没有主键也没有唯一列:
- InnoDB 会自动创建一个隐式自增的行 ID 列（row ID），作为聚簇索引的索引键。

为什么不推荐使用带有业务含义的主键？

业务字段可能发生变化:
- 任何有业务含义的字段都有可能在未来发生变化。因为业务需求可能会变更或扩展，使用带有业务含义的字段作为主键意味着一旦这些字段需要更新，就会导致主键的变化。
- 变更主键会导致 B+树的重新组织，数据的存储位置可能会发生变化，从而导致磁盘 I/O 增加。
可能导致页分裂和性能下降:
- 带有业务含义的主键不一定是顺序自增的。例如，UUID 这样的主键是随机的，而不是顺序递增的，这会导致新数据插入 B+树的中间位置。
- 当数据插入中间位置时，如果目标数据页已经满了，就会触发页分裂，将部分数据移到新页面。页分裂会导致存储碎片增加，查询和插入效率下降。
维护成本高:
- 主键是数据库中非常重要的字段，关联着很多外键约束和索引，一旦需要修改主键，修改的成本非常高。因此，在设计数据库表结构时，推荐使用无业务含义且顺序自增的主键。

建立索引的三个主要优点

减少数据扫描量:
- 快速查找: 当我们为一个字段创建了索引，MySQL 在查询时可以直接通过索引进行快速定位，大大减少需要扫描的数据量。
- 效率提升: 没有索引的情况下，MySQL 需要进行全表扫描，时间复杂度为 O(n)O(n)O(n)。而使用索引后，数据按照 B+树结构排序存储，查找时间复杂度为 O(log⁡dN)O(\log_d N)O(logdN)，大大提高了查询速度。
避免外部排序和临时表:
- 优化排序: 如果查询语句中包含 ORDER BY 或 GROUP BY，且这些字段上有索引，MySQL 可以直接从索引中读取排序好的数据，而无需额外的排序操作，避免使用临时表进行排序，从而提高查询性能。
- 降低系统开销: 减少排序操作和临时表的使用能够显著降低内存和磁盘 I/O 的开销。
将随机 I/O 转为顺序 I/O:
- 优化磁盘读取: 索引的 B+树结构使数据按顺序存储，这种顺序存储能将磁盘的随机 I/O 转换为顺序 I/O。顺序 I/O 的速度远远快于随机 I/O，因为顺序 I/O 充分利用了磁盘的顺序读取特性，提高了数据访问速度。
- 减少磁盘寻道时间: 顺序 I/O 减少了磁盘的寻道时间，从而提升了查询性能。

选择哪些字段来建立索引？

在设计数据库索引时，选择合适的字段来建立索引可以大大提高查询性能。以下是适合建立索引的字段和不适合建立索引的字段：

适合建立索引的字段

唯一性高的字段:
- 字段值的唯一性越高，索引的选择性越好，查询效率也会越高。例如，商品编码、身份证号等唯一性强的字段非常适合建立索引。
经常用于 WHERE 查询条件的字段:
- 索引主要用于快速定位记录，因此常用于查询条件的字段（WHERE 子句中的字段）适合建立索引。例如，用户 ID、订单状态等。
经常用于 GROUP BY 和 ORDER BY 的字段:
- 当查询语句经常需要对某些字段进行分组和排序时，在这些字段上建立索引可以减少排序和分组操作的开销。例如，订单的创建时间、用户的注册日期等。
多列组合的联合索引:
- 当多个字段经常一起出现在查询条件中时，可以考虑建立联合索引，以减少查询过程中索引树的遍历次数，提升性能。例如，城市和年龄的组合查询 WHERE city = 'New York' AND age > 30。

不适合建立索引的字段

字段值重复度高:
- 如果一个字段有很多重复的值，索引的选择性很低，索引的使用效果并不好。典型例子是性别字段（只有男女两种值）。在这种情况下，MySQL 的查询优化器可能会直接选择全表扫描而不是使用索引。
不用于 WHERE、GROUP BY、ORDER BY 的字段:
- 如果一个字段不会出现在查询条件或排序操作中，那么它对查询性能没有影响，不需要建立索引。例如，描述性字段（如商品描述、文章内容等）通常不适合建立索引。
短字符串或布尔类型字段:
- 对于很短的字符串（例如状态标识）或布尔类型的字段，由于其值的唯一性差，索引的作用不明显，也不适合建立索引。

建立索引后，查询是否一定会使用索引？

即使在数据库中为字段建立了索引，查询操作也不一定总会使用这些索引。这是因为 MySQL 查询优化器会根据多种因素来决定是否使用索引，以选择最低成本的查询方案。以下是一些可能导致索引未被使用的情况：

1. 索引失效的场景

对索引字段进行左模糊匹配:
- 如果查询条件使用了 LIKE '%xxx' 这种左模糊匹配的方式，MySQL 无法使用索引进行查找，因为索引是有序的，从左往右匹配时可以利用二分查找的特性，但左模糊匹配破坏了这一有序性。
对索引字段进行表达式计算或函数操作:
- 如果在查询中对索引字段进行了任何形式的计算、函数调用（例如 YEAR(date)）或使用了表达式，索引将无法被使用。因为这些操作会导致 MySQL 无法直接使用索引中的排序信息来快速查找数据。
隐式类型转换:
- 当索引列的类型与查询中的条件类型不一致时（例如，索引列为字符串类型，而查询条件为整数类型），MySQL 可能会进行隐式的类型转换，这样也会导致索引失效。例如，SELECT * FROM table WHERE varchar_column = 123。
不满足最左前缀原则的联合索引:
- 如果创建了联合索引（如 INDEX(a, b)），但在查询时没有按照索引的最左前缀原则来使用（例如 WHERE b = 1），则索引不会被使用。联合索引只能从最左边的字段开始使用，否则索引失效。
字段数据分布不均匀:
- 如果字段的索引选择性差（即该字段的重复数据很多，例如性别字段），查询时 MySQL 可能认为使用索引的效率不高，反而会进行全表扫描。

2. 优化器基于成本考虑

索引的成本计算:
- MySQL 的查询优化器会基于查询的成本来决定是否使用索引。即使查询条件使用了索引字段，如果优化器判断使用索引的代价（例如回表操作的成本）高于直接进行全表扫描的代价，那么它会选择不使用索引，而是进行全表扫描。
回表操作的成本:
- 当使用二级索引查询时，如果查询的字段不在索引覆盖的范围内，MySQL 需要通过索引找到主键值，然后通过主键值回表查询实际的数据。这种情况下，优化器会评估回表操作的成本。如果回表的次数过多导致性能下降，优化器可能会选择不使用索引，改用全表扫描。

什么是最左匹配原则？

1. 联合索引的结构

在 MySQL 中，联合索引（Composite Index） 是一个包含多个列的索引。对于联合索引 (a, b, c)，它的排序顺序如下：

首先按列 a 排序。
在列 a 相同的情况下，再按列 b 排序。
在列 b 相同的情况下，再按列 c 排序。

这种排序方式决定了 MySQL 在使用联合索引时必须遵循最左匹配原则。

2. 最左匹配原则的定义

最左匹配原则是指在使用联合索引时，查询必须从联合索引的最左边的列开始匹配。如果查询条件中的第一个列不在索引的最左边，则 MySQL 无法使用该联合索引。

规则:
- 当使用联合索引 (a, b, c) 时，查询条件中必须包含列 a（最左列），才能使用该索引。
- 如果查询条件中只包含列 b 或者 c 而不包含列 a，则 MySQL 无法使用该索引。
- 一旦某个字段列没有使用，后续的所有字段列都不会被使用。例如，WHERE a = 1 AND c = 3 不能使用联合索引 (a, b, c) 的字段 c。
- 范围查询后面的索引列不会被使用。例如，WHERE a = 1 AND b > 2 AND c = 3 只使用了索引 (a, b)，因为 b 是一个范围查询。

建立联合索引的注意事项

在建立联合索引时，需要注意以下几点，以确保索引的有效性和高效性：

1. 字段顺序的选择

按区分度排序: 建立联合索引时，应优先考虑字段的区分度（selectivity），即字段中不同值的个数占表总行数的比例。区分度高的字段应放在索引的最前面。这样可以最大化减少需要扫描的数据行数，提高查询性能。
- 计算区分度: 区分度 = 不同值的个数 / 表的总行数。区分度越高的字段，意味着它们的值越多样，使用索引时过滤效果越好。例如，在用户表中，user_id 的区分度通常高于 gender，因此应将 user_id 放在联合索引的前面。

2. 使用联合索引时的注意事项

遵循最左前缀原则: 在使用联合索引的查询中，必须按照索引定义的顺序使用字段，以确保索引有效。例如，对于 (a, b, c) 联合索引，查询必须包括 a，可以进一步包含 b，然后是 c。如果跳过某个字段，后续字段不会使用索引。
避免索引失效的操作: 避免对索引列进行函数计算、表达式运算和类型转换等操作，因为这些操作会导致索引失效。例如，不要写 WHERE YEAR(date_column) = 2024，而是使用范围查询 WHERE date_column >= '2024-01-01' AND date_column < '2025-01-01'。

3. 索引维护和管理

定期优化: 联合索引会增加写入和维护成本，因此在有大量更新操作的场景下，需要定期优化和重建索引，以防止索引碎片化影响性能。
避免过多联合索引: 过多的联合索引会增加存储开销和插入、更新、删除操作的成本。应根据实际查询情况建立合适的联合索引，不要过度索引。

索引下推的工作原理

在没有索引下推的情况下，MySQL 查询执行过程如下：

使用二级索引查找：存储引擎首先定位到二级索引中满足条件的记录（例如 age > 20）。
进行回表操作：每找到一条二级索引记录，存储引擎都会回表查找聚簇索引中的完整记录。
在 Server 层过滤：回表后将记录返回到 Server 层，Server 层再根据查询条件（例如 reward = 100000）进行过滤。
继续查找：重复上述过程，直到所有记录查找完毕。

在使用索引下推的情况下，MySQL 查询执行过程如下：

使用二级索引查找：存储引擎首先定位到二级索引中满足第一个条件的记录（例如 age > 20）。
过滤条件下推到存储引擎层：在回表之前，存储引擎直接在二级索引的记录中检查其他条件（例如 reward = 100000）。
- 如果条件不成立，直接跳过该记录，无需进行回表操作。
- 如果条件成立，才进行回表操作，获取完整记录。
减少回表操作：通过在存储引擎层进行更多条件过滤，减少了不必要的回表操作，提高查询效率。