在SQL中的表扫描和索引扫描

Question

在SQL中，表扫描和索引扫描有什么区别，它们在哪些特定场景下使用？

- Manisha Awasthi

---

可能是重复的问题：表扫描和聚集索引扫描有什么区别？ - gbn

一个扫描表行，另一个索引行。你使用的是哪个关系型数据库管理系统？ - Martin Smith

Answer 1

表扫描是迭代所有表行。

索引扫描是迭代所有索引项，当索引项满足搜索条件时，通过索引检索表行。

通常情况下，索引扫描比表扫描更节省成本，因为索引比表更扁平。

有很多关于这个问题的文献。例如：

• Microsoft：哪个更快：索引访问还是表扫描？：

索引访问是一种访问方法，在此方法中，SQL Server使用现有的索引读取和写入数据页面。由于索引访问大大减少了I/O读操作的数量，因此它通常优于表扫描。

• Oracle：查询优化器

在这种方法中，通过遍历索引并使用语句指定的索引列值来检索行。索引扫描基于索引中一个或多个列的值从索引中检索数据。要执行索引扫描，Oracle会根据语句访问的索引列值搜索索引。如果语句仅访问索引的列，则Oracle会直接从索引中读取索引列值，而不是从表中读取。

• MySql：如何避免表扫描

- dani herrera

Answer 2

大多数查询引擎都有一个查询优化器，它试图生成一个有效的查询执行策略。如果存在可以使查询更快的索引，则查询优化器将执行索引扫描或索引查找，否则将进行表扫描。

例如：

SELECT * FROM tbl WHERE category_id = 5;

如果 category_id 没有索引，将执行表扫描，即将检查表中每个记录以找到正确的 category_id。

然而，如果 category_id 被索引，情况会变得更加复杂。如果表非常大，则可能选择索引搜索。但是，如果表很小，则优化器可能仍然决定表扫描更快，因为需要访问索引的一些开销。如果 category_id 不够具有选择性，例如只有两个类别，则对于大表来说，扫描表可能更快。

索引通常是以树结构组织的。在树中查找项是一个 O(log n) 操作，而表扫描是一个 O(n) 操作。速度主要由执行查询所需的磁盘访问次数确定。对于小表，先查找索引，然后访问查找到的条目可以生成更多的磁盘访问。

让我们再看另一个查询：

SELECT category_id FROM tbl WHERE category_id BETWEEN 10 AND 100;

还有另一种选择可供使用。在这种情况下，索引扫描可能不比表扫描快，但由于我们只检索category_id，因此索引扫描（而不是索引查找）可能更快。索引扫描读取索引表的每个条目，而不是利用树结构（索引查找所做的）。然而，由于所请求的信息完全包含在索引中，因此不需要访问数据表。索引扫描和表扫描都是O（n）操作，但由于索引通常比表小，因此扫描索引所需的磁盘访问次数比扫描表少。

整个问题非常复杂，非常依赖于数据库引擎。如果想了解更多，请阅读db供应商提供的文档。

- Olivier Jacot-Descombes

---

在您的示例中，它将使用索引查找（可能是范围查找），而不是索引扫描。如果索引覆盖但不是在有用的前导列上或谓词具有选择性并且索引比表窄，我会期望进行索引扫描。 - Martin Smith

好的，我更正了我的答案，以考虑索引扫描和索引查找之间的区别。 - Olivier Jacot-Descombes

Answer 3

至少对于SQL Server：

索引扫描可能更快，因为索引不覆盖表中的所有列，而表（或聚集索引）扫描必须读取所有数据。如果索引包括表中的所有列，则它应该与表扫描大致相等，并且在索引扫描和表（或CIX）扫描之间选择将是一个抛硬币的决定。区别在于，当索引中的列较少时，您可以在8kb页面上放置更多的索引行，从而减少了扫描索引中所有数据所需读取的页面总数。

为了说明我的意思，请想象一下你有两本电话簿，一本有姓氏、名字、街道地址和电话号码，另一本只有姓氏、名字和电话号码。现在想象一下，由于不需要打印街道地址，您可以在电话簿的任何一页上放置两个额外的姓名和电话号码列。这样做的结果是电话簿变得更薄，因为您可以在较少的页面上放置相同数量的电话号码。接下来，想象一下您被要求计算电话簿中的电话号码数量。您会选择哪一个，有街道地址的那一个（具有更多页面，类似于表扫描）还是没有街道地址的那一个（具有更少页面，类似于大多数索引扫描）？我会选择页面较少的那一个。

这其中还有一个问题，有些索引可以被过滤，这意味着它们在大多数情况下具有较少的列（因此可以将更多行放入单个页面中），而且它们也可以有一个WHERE子句，消除很多行。在这种情况下，索引扫描将比表扫描更好（但这仅适用于具有匹配WHERE子句和相同语义的查询）。

- Aaron Bertrand

Answer 4

作为问题的第一部分，@danihp已经回答了，我将尝试回答第二个部分“它在哪里被特别使用”。这是针对Oracle的，但对大多数RDBMS都适用。

假设我们有一个表my_table，它在列id上具有唯一索引，并且在列yet_another_column上具有第二个非唯一索引：

create my_table ( id varchar2(20) not null
                , another_column not null
                , yet_another_column
                , constraint pk_my_table primary key (id) 
                );

create index i_my_table on my_table ( yet_another_column );

现在，如果我们执行select * from my_table where id = '1'，这将/应该对索引pk_my_table进行唯一索引扫描。然后我们使用索引重新进入表格，返回my_table中所有id ='1'的内容。

如果查询改为select id from my_table where id = 'a'，则没有必要进行第二阶段，因为我们需要的所有值都包含在索引中。在这种情况下，查询仅会进行唯一索引扫描。

接下来，如果我们的查询是select * from my_table where yet_another_column = 'y'，那么我们在列上有一个索引，但它不是唯一的，因此我们必须浏览整个索引以尝试找到与我们的where条件匹配的所有值，即索引扫描。再次选择了不在我们的索引中的列，因此我们必须重新进入表格以获取它们。

最后，如果我们的查询是select id from my_table where another_column = 'yes'。我们在another_column上没有索引，因此我们必须进行表扫描以查找该值，即我们必须在表格中查找where another_column = 'yes'的所有内容。

现在，在这些情况下，表扫描和索引扫描之间似乎没有太大区别。我们仍然必须去查找数据库中对象的值。但是，由于索引要小得多且专门设计为进行扫描（请参见其他答案），因此如果您只想获取表格中的一小部分行，通常更快地进行索引扫描。如果您想获取表格的10％，那么这一点就变成了“视情况而定”。

- Ben

---

Oracle是否不区分查找和扫描？ - Martin Smith

@MartinSmith，我从未听说/看到过它们被描述为“seeks”，但我理解你的观点。我已经编辑了我的答案，包括一个非主键示例。我想唯一索引将是“seek”，而非唯一索引则是“scan”。 - Ben