MySQL中的回表

目录

1、表扫描和索引：

表扫描（Table Scan）：

索引：

2、聚簇索引 vs. 非聚簇索引：

聚簇索引（Clustered Index）：

非聚簇索引（Non-clustered Index）：

选择索引类型的考虑：

3、回表：

回表的工作流程：

为什么发生回表：

回表的影响：

我的其他博客

---

"回表"是数据库查询中的一个概念，特别是在执行查询时涉及到非聚簇索引的情况下。让我们来详细解释一下：

1、表扫描和索引：

表扫描（Table Scan）：

表扫描是指数据库系统对整个表进行逐行扫描以满足查询条件。当没有适用的索引可用或查询条件无法充分利用索引时，数据库引擎可能会选择执行表扫描。表扫描有两种类型：全表扫描和局部扫描。

1. 全表扫描（Full Table Scan）：

   • 概念： 在全表扫描中，数据库按顺序读取整个表的每一行，以找到满足查询条件的记录。
   • 应用场景： 当没有可用的索引，或者对整个表的数据进行操作时，可能会执行全表扫描。

2. 局部扫描：

   • 概念： 局部扫描是在某些条件下扫描表的一部分，而不是整个表。
   • 应用场景： 当查询条件只涉及到表的一部分数据时，数据库引擎可能会执行局部扫描以提高性能。

索引：

索引是一种数据结构，用于提高数据库系统对表中数据的检索速度。索引可以看作是表的快速查找表，允许数据库引擎更迅速地定位和访问特定值。

1. 聚簇索引（Clustered Index）：

   • 概念： 表数据的物理顺序与索引的顺序一致。InnoDB 存储引擎的主键是聚簇索引。
   • 性能优势： 在聚簇索引上的查找速度很快，因为相邻的数据通常存储在相邻的磁盘页上。

2. 非聚簇索引（Non-clustered Index）：

   • 概念： 索引和表的数据存储在不同的地方。MySQL的InnoDB引擎的非主键索引就是非聚簇索引。
   • 性能考虑： 在非聚簇索引上的查找需要进行两次查找：首先在索引中找到主键，然后使用主键再到表中查找实际数据。

3. 索引的作用：

   • 提高检索速度： 通过使用索引，数据库引擎可以快速定位到满足查询条件的数据，而不必扫描整个表。
   • 唯一性约束： 索引可以强制表中的某一列或一组列的唯一性，确保数据的一致性。
   • 加速排序和分组操作： 在排序或分组操作中，索引可以提高性能。

4. 索引的选择和创建：

   • 根据查询模式选择： 索引的选择应该根据查询模式和频率进行，以优化常用查询的性能。
   • 权衡空间和性能： 索引会占用额外的磁盘空间，并在写操作时有一定的开销。在设计中需要权衡空间和性能。

在设计数据库时，合理使用索引，尽量减少表扫描，对于特定查询场景使用合适的索引，可以有效提高数据库的性能。

2、聚簇索引 vs. 非聚簇索引：

聚簇索引（Clustered Index）：

1. 概念：

   • 表数据的物理顺序与索引的顺序一致。换句话说，表的数据行的存储顺序与聚簇索引的顺序相同。
   • 在 InnoDB 存储引擎中，主键是聚簇索引。如果没有显式指定主键，InnoDB 会选择一个唯一非空索引来作为聚簇索引。

2. 性能优势：

   • 查找速度很快，因为相邻的数据通常存储在相邻的磁盘页上，减少了 I/O 操作。
   • 范围查询和排序操作的性能较好，因为相关数据行在物理上是相邻的。

3. 空间效率：

   • 由于表的数据行的物理顺序与聚簇索引的顺序相同，不需要额外的存储空间。

非聚簇索引（Non-clustered Index）：

1. 概念：

   • 索引和表的数据存储在不同的地方，它们的物理顺序不一定一致。在 InnoDB 中，非主键索引就是非聚簇索引。
   • 非聚簇索引中的叶子节点包含指向实际数据行的指针。

2. 性能考虑：

   • 查找时需要进行两次查找：首先在索引中找到主键值，然后使用主键值再到表中查找实际数据。因此，相对于聚簇索引，查找性能可能较慢。
   • 范围查询和排序操作的性能可能较差，因为相关数据行在物理上可能不是相邻的。

3. 空间效率：

   • 非聚簇索引需要额外的存储空间，因为索引和实际数据存储在不同的位置。

选择索引类型的考虑：

1. 查询模式： 根据实际查询的模式来选择索引类型。如果经常使用范围查询、排序或者特定的列查询，聚簇索引可能更适合。

2. 唯一性： 如果需要确保某一列或一组列的唯一性，可以选择在该列上创建唯一索引，这可能是一个聚簇索引或非聚簇索引。

3. 表的写入操作： 聚簇索引在表的写入操作（插入、更新、删除）时可能产生较大的开销，因为数据的物理顺序需要维护。非聚簇索引相对于写入操作的开销可能较小。

4. 表的大小： 聚簇索引对小表可能更为适用，而非聚簇索引可能更适合大表。

5. 存储引擎支持： 不同的存储引擎对于聚簇索引和非聚簇索引的支持程度可能有所不同。在选择索引类型时需要考虑使用的存储引擎。

在设计数据库时，需要根据实际应用场景和性能需求来选择合适的索引类型。

3、回表：

"回表"是数据库查询优化中的一个概念，特别是在涉及非聚簇索引的情况下。当执行查询时，如果需要检索的数据不在索引中，数据库引擎就需要通过索引中的指针回到实际数据行所在的表中进行进一步的检索，这个过程就被称为“回表”。

回表的工作流程：

1. 索引查找： 初始查询通过索引定位到符合条件的记录。这通常是通过 B 树等数据结构实现的，能够快速定位到索引键的值。

2. 获取指针： 在非聚簇索引中，索引的叶子节点不直接包含数据，而是包含指向实际数据行的指针。数据库引擎需要获取这个指针。

3. 回到表中检索实际数据： 使用获取的指针，数据库引擎回到表中，找到实际的数据行，获取查询所需的列的值。

为什么发生回表：

1. 非聚簇索引的特点： 在非聚簇索引中，索引和实际数据存储在不同的位置。因此，当查询的列不在索引中时，就需要回到实际的数据行中获取这些列的值。

2. 覆盖索引： 如果查询的列都包含在索引中，这样的索引被称为“覆盖索引”，就不会发生回表。覆盖索引可以减少回表的需求，提高查询性能。

回表的影响：

1. 性能影响： 回表操作会增加额外的 I/O 操作和访问实际数据行的开销，可能导致查询性能下降。

2. 优化策略： 为了减少回表的发生，可以考虑使用覆盖索引，即在索引中包含查询所需的所有列。

3. 查询优化： 在设计数据库表结构和选择索引时，需要根据查询模式来优化，以最小化回表的次数。

总的来说，回表是在执行查询时需要注意的一个性能方面的考虑因素。合理设计表结构、选择适当的索引，以及考虑覆盖索引的使用，都是减少回表的发生，提高查询性能的重要策略。

我的其他博客

简单介绍一些其他的树-CSDN博客

认识二叉树（详细介绍）-CSDN博客

正则表达式详细讲解-CSDN博客

低代码开发：创新之道还是软件开发的捷径？-CSDN博客

HTTP与HTTTPS的区别-CSDN博客

什么情况下会产生StackOverflowError（栈溢出）和OutOfMemoryError（堆溢出）怎么排查-CSDN博客

在多线程中sleep()和wait()的区别（详细）-CSDN博客

谈谈我对HashMap扩容机制的理解及底层实现-CSDN博客

堆排序详细讲解（一文足矣JAVA）-CSDN博客