一些索引的设计与使用思想

聚簇索引与非聚簇索引

1. 聚簇索引

聚簇索引是与数据绑定的索引。它在 InnoDB 中，数据插入时会与某个索引绑定。该索引可以是主键索引、唯一索引，或生成一个6字节的 rowid。聚簇索引的叶子节点存储了实际的数据。当表中有多个索引时，数据仅存储一份，存储数据的索引称为聚簇索引。

• 特点：

  • 数据存储在索引的叶子节点中。

  • 数据表的数据和聚簇索引是一起存储的，按索引的顺序组织。

  • 一个表只能有一个聚簇索引。

2. 非聚簇索引

非聚簇索引包含了索引列以及与主键的引用。当查询需要访问非索引列的值时，非聚簇索引只能提供索引列和主键的映射关系，而无法直接获取数据行内容。因此，非聚簇索引查询通常需要回表操作。

• 特点：

  • 索引的叶子节点只存储索引键和主键值，而不包含实际数据。

  • 数据表的数据与索引是分离的。

  • 一个表可以有多个非聚簇索引。

回表

回表是指在使用非聚簇索引进行查询时，如果需要查询非索引列的值，必须通过查询获取索引行的主键值，再去主表中查询实际数据。

• 回表操作的过程：

  1. 首先，通过非聚簇索引找到满足查询条件的索引行。

  2. 然后，根据索引行中的主键值，再回到原始表中查找其他列的值。

• 影响：

  • 回表操作会导致额外的 I/O 开销，因为需要执行两次查询操作。

  • 为了提高性能，可以通过优化索引设计或使用覆盖索引来减少回表的次数。

索引覆盖

索引覆盖是指查询所需的所有列都已经包含在索引中，因此可以直接通过索引来获取结果，避免回表操作。

• 优势：

  • 能够有效减少回表操作，提升查询性能。

• 注意：

  • 覆盖索引并不是越多越好，因为过多的索引会增加存储空间的需求，并可能影响写操作的性能。

  • 创建覆盖索引时应根据实际的查询需求来选择字段。

最左匹配原则（最左前缀原则）

最左匹配原则是指在使用复合索引（多列索引）进行查询时，只有在查询条件中使用了索引列的最左部分，才能利用该索引。

• 原理：

  • 在创建复合索引时，应将查询条件中最常用的列放置在索引的最左边。

  • 查询时，即使不需要所有的索引列，也需要包括最左边的列。

  • 如果查询条件能够使用索引的最左部分，数据库可以直接利用该索引进行定位和过滤。

• 影响：

  • 如果查询条件中没有包含索引的最左部分，数据库将无法使用该索引，可能需要全表扫描或回表操作，导致性能下降。

索引下推（ICP优化）

索引下推（Index Condition Pushdown，简称 ICP）是一种优化技术，它能在索引扫描的过程中直接对索引中的字段进行过滤，减少回表操作。

• 原理：

  • 在没有索引下推优化时，索引扫描首先获取满足条件的索引行，然后再根据 WHERE 子句的条件进一步过滤记录。

  • 支持 ICP 优化后，MySQL 在扫描索引时，提前执行部分 WHERE 条件的过滤，减少回表的次数。

  • 这能够减少不必要的 I/O 操作，从而提升查询性能。

前缀索引设计原则

前缀索引是指只对字段的部分内容建立索引，而不是整个字段的内容。它适用于长文本或较长字符串字段的索引，可以节省存储空间并提升查询效率。

• 设计原则：

  • 选择合适的前缀长度： 根据实际的查询需求，选择合适的前缀长度。前缀索引应当覆盖常见查询的部分字段内容。

  • 避免过长的前缀： 前缀长度过长可能导致索引变得非常庞大，反而会降低查询效率。应确保选择足够的前缀长度来满足查询，但也不应过长。

  • 结合查询需求设计： 在需要对字符串字段进行模糊查询时，前缀索引可有效提升性能。否则，应考虑其他类型的索引。

  • 适用于高基数字段： 对于长文本或高基数字段（如 URL 或长邮件地址）使用前缀索引，可以显著减少存储需求和索引开销。

通过以上原则和优化方法，合理设计索引能够提高数据库查询性能，减少回表和 I/O 操作。