postgresql - index scan & index only scanIndex Only Scan 和 I

Index Only Scan 和 Index Scan 都是通过索引来查找数据的扫描方式，但它们的工作方式和适用场景有所不同。下面是这两种扫描方式的区别和工作原理：

1. Index Only Scan

Index Only Scan 是指在扫描过程中，完全通过索引获取查询所需的所有数据，而无需访问表的数据行（heap）。

特点：
- 无需访问堆（table） ：Index Only Scan 通过索引本身存储的数据来返回查询结果，避免了访问堆（数据表）中的记录。
- 适用于索引包含所有查询所需字段的情况：例如，如果查询的所有列都能从索引中直接获得，那么就不需要访问堆中的数据行。
- 较高的性能：因为它避免了额外的 I/O 操作，仅通过索引返回数据，通常能带来更好的性能，尤其是在查询非常大的表时。
何时使用：
- 当索引包含查询中所需的所有列时（即索引覆盖了查询所需的列）。
- 查询不涉及需要返回的数据行之外的其他字段时。
例子：假设我们有一个包含以下数据的 employees 表，索引是基于 department_id 和 salary 列的。
```
CREATE INDEX idx_department_salary ON employees(department_id, salary);
```
查询如下：
```
SELECT department_id, salary
FROM employees
WHERE department_id = 101;
```
由于索引 idx_department_salary 已经包含了查询需要的所有列（department_id 和 salary），在这种情况下，PostgreSQL 可能会使用 Index Only Scan，仅通过索引直接返回结果，而无需访问堆。

2. Index Scan

Index Scan 是通过索引来查找符合条件的数据行，但需要 访问表的堆（数据行） 来获取额外的列或更多的字段。

特点：
- 访问索引和堆：Index Scan 仅使用索引来查找符合查询条件的记录，之后可能需要访问堆来获取查询中索引不包含的列。
- 需要额外的 I/O：由于返回的字段不完全包含在索引中，Index Scan 需要访问堆中的实际数据行来完成查询。
何时使用：
- 当查询所需的列 不完全 包含在索引中时，Index Scan 依然可以有效地通过索引来定位数据行，但还需要访问堆来获取完整的记录。
- 当查询中涉及的列较多，索引不能覆盖所有列时。
例子：假设 employees 表包含以下列：id, name, department_id, salary，并且我们有如下索引：
```
CREATE INDEX idx_department_id ON employees(department_id);
```
查询如下：
```
SELECT id, name
FROM employees
WHERE department_id = 101;
```
在这种情况下，idx_department_id 索引只包含 department_id 列，查询需要返回 id 和 name 两列。由于 id 和 name 不在索引中，PostgreSQL 会首先通过 Index Scan 查找符合条件的行，然后通过堆访问对应的数据行来获取 id 和 name。

区别总结：

特性	Index Only Scan	Index Scan
访问方式	仅通过索引获取数据，不需要访问堆	需要通过索引定位行，然后访问堆获取数据
适用场景	查询所需列完全包含在索引中时	查询所需列未完全包含在索引中时
性能	更高，避免了堆的访问，减少了 I/O 操作	可能较低，因为需要额外访问堆中的数据行
例子	查询仅涉及索引的列，所有列都可以通过索引返回	查询涉及的列不完全在索引中，需访问堆以获取额外数据
优点	减少 I/O 操作，通常更高效	可以适用于更多复杂的查询
缺点	只能在索引覆盖所有查询列时使用	相比 `Index Only Scan`，性能较差，涉及更多 I/O 操作

为什么会有 Index Only Scan？

Index Only Scan 的效率较高，因为它避免了频繁的堆访问。当数据库在执行查询时，可以通过索引直接找到所有符合条件的行，而不必回到堆中查找对应的行。因此，如果索引包含了查询中所需的所有列，它可以显著提高查询性能。

但是，Index Only Scan 只有在索引覆盖查询所有需要的列时才能生效。如果查询列超出了索引的覆盖范围，PostgreSQL 会选择使用 Index Scan 并访问堆以获取额外的数据。

其他的一些思考

对于 Index Only Scan，如果在 PostgreSQL 中你创建了多个索引，像是 a, b, c, ab, 和 bc，这些索引之间的确可能会存在数据重复存储的问题，因为每个索引都会独立地存储数据的副本，存储的是索引列的值以及指向数据行的指针。

让我们更详细地分析：

1. 索引的存储结构

在 PostgreSQL 中，索引是一种 B-tree（或其他类型的树结构），用于高效地查找数据。
每个索引 都会存储一组值，以及这些值在数据表中的位置（即对应的数据行的指针），以便在查询时能够快速定位数据。

2. 不同的索引对数据存储的影响

单列索引：例如，索引 a 会存储 a 列的值及其对应的数据行的指针；索引 b 会存储 b 列的值及其对应的数据行的指针。
复合索引：例如，ab 和 bc 索引，分别会存储 a, b 和 b, c 列的组合以及它们对应的数据行指针。

3. 数据的重复存储

在你提到的场景中，如果你有 a, b, c, ab, 和 bc 索引，所有这些索引都需要存储指向相同数据行的指针，因此 数据行指针是重复存储的。每个索引存储的都是不同列的值和这些值在数据表中的位置。
- 举个例子：假设你有一个包含列 a, b, c 的表，每一行有一组数据 (a, b, c)。如果你创建了以下索引：
```
CREATE INDEX idx_a ON table_name(a);
CREATE INDEX idx_b ON table_name(b);
CREATE INDEX idx_c ON table_name(c);
CREATE INDEX idx_ab ON table_name(a, b);
CREATE INDEX idx_bc ON table_name(b, c);
```
  - idx_a 会存储 a 列的值和数据行指针。
  - idx_b 会存储 b 列的值和数据行指针。
  - idx_c 会存储 c 列的值和数据行指针。
  - idx_ab 会存储 a, b 组合列的值和数据行指针。
  - idx_bc 会存储 b, c 组合列的值和数据行指针。
  每个索引都会分别保存数据行的指针，这些指针指向同一数据行。因此，数据行的指针在每个索引中都存储了，导致了重复存储。

4. 重复存储的影响

存储空间：确实，多个索引会增加存储的开销，因为每个索引都需要独立存储一份数据行的指针（或者说是“元数据”）。如果你创建了多个索引，尤其是复合索引，重复存储的指针会占用更多的磁盘空间。
查询性能的优化：尽管多个索引会导致存储重复的指针，但它们也可以帮助优化查询性能。PostgreSQL 会根据查询的需要选择合适的索引，如果索引中包含所有查询所需的列，它可以直接通过 Index Only Scan 来避免访问堆，从而加快查询速度。
更新成本：多个索引的维护成本也更高。每当数据表进行 INSERT, UPDATE, 或 DELETE 操作时，所有相关的索引也需要更新。多个索引意味着更多的工作量。

5. 是否必要创建多个索引？

创建多个索引确实可能导致存储上的冗余，尤其是当这些索引的列有重叠时。比如，a, b, 和 ab 索引，ab 实际上已经包含了 a 和 b 列，因此你不一定需要单独为 a 和 b 创建索引。在这种情况下，复合索引 ab 已经足够提供对 a 和 b 的查询优化。

6. 优化策略

使用复合索引：你可以通过创建复合索引来减少索引数量和冗余。例如，a 和 b 列常一起查询时，创建一个 a, b 的复合索引，就能避免创建单独的 a 和 b 索引。
仅保留必要的索引：不要为了提高每个可能查询的速度而创建过多的索引。通过分析查询模式，确定哪些索引最为关键，避免过多的重复存储。

总结

在 PostgreSQL 中，多个索引确实会导致 数据行指针的重复存储。每个索引都会存储指向数据行的指针，并且每个索引可能会存储相同数据行的多个指针。
虽然这会增加存储空间的开销，但通过适当的索引选择和优化，可以有效地提升查询性能。
为了避免不必要的重复存储，可以考虑使用复合索引来代替多个单列索引，减少冗余。