sql索引面试_关于索引的25个最佳SQL面试问答

sql索引面试

Q1：堆表和群集表之间有什么区别？ 我们如何确定该表是否为堆表？ (Q1: What is the difference between a Heap table and a Clustered table? How can we identify if the table is a heap table?)

A Heap table is a table in which, the data rows are not stored in any particular order within each data page. In addition, there is no particular order to control the data page sequence, that is not linked in a linked list. This is due to the fact that the heap table contains no clustered index.

堆表是一个表，其中的数据行未按任何特定顺序存储在每个数据页内。 此外，没有特殊的顺序来控制数据页顺序，即未在链接列表中链接。 这是由于堆表不包含聚簇索引这一事实。

A clustered table is a table that has a predefined clustered index on one column or multiple columns of the table that defines the storing order of the rows within the data pages and the order of the pages within the table, based on the clustered index key.

聚集表是一种在表的一列或多列上具有预定义聚集索引的表，该索引基于聚集索引键定义数据页内行的存储顺序和表内页的顺序。

The heap table can be identified by querying the sys.partitions system object that has one row per each partition with index_id value equal to 0. You can also query the sys.indexes system object also to show the heap table index details, that shows, the id of that index is 0 and the type of it is HEAP.

可以通过查询sys.partitions系统对象来识别堆表，该对象在每个分区中每个行都有一行，且index_id值等于0。您还可以查询sys.indexes系统对象，以显示堆表索引详细信息，该详细信息显示，该索引的ID为0，类型为HEAP。

For more information, see the article: SQL Server table structure overview.

有关更多信息，请参见文章： SQL Server表结构概述 。

Q2：说明SQL Server Engine如何使用索引分配图（IAM）？ (Q2: Explain how the SQL Server Engine uses an Index Allocation Map (IAM)?)

SQL Server Engine uses an Index Allocation Map (IAM) to keep an entry for each page to track the allocation of these available pages. The IAM is considered as the only logical connection between the data pages, that the SQL Server Engine will use to move through the heap. 

SQL Server Engine使用索引分配映射 （IAM）为每个页面保留一个条目，以跟踪这些可用页面的分配。 IAM被认为是数据页之间唯一的逻辑连接，SQL Server Engine将使用它们在堆中移动。

For more information, see the article: SQL Server table structure overview.

有关更多信息，请参见文章： SQL Server表结构概述 。

问题3：什么是“转发指针问题”，我们如何解决？ (Q3: What is the “Forwarding Pointers issue” and how can we fix it?)

When a data modification operation is performed on heap table data pages, Forwarding Pointers will be inserted into the heap to point to the new location of the moved data. These forwarding pointers will cause performance issues over time due to visiting the old/original location vs the new location specified by the forwarding pointers to get a specific value.

在堆表数据页上执行数据修改操作时，会将转发指针插入到堆中，以指向已移动数据的新位置。 随着时间的流逝，这些转发指针将导致性能问题，原因是访问转发指针指定的旧位置/原始位置与新位置以获得特定值。

Starting from SQL Server version 2008, a new method was introduced to overcome the forwarding pointers performance issue, by using the ALTER TABLE REBUILD command, that will rebuild the heap table.

从SQL Server 2008版开始，引入了一种新方法，该方法通过使用ALTER TABLE REBUILD命令来克服转发指针性能问题，该方法将重建堆表。

For more information, see the article: SQL Server table structure overview.

有关更多信息，请参见文章： SQL Server表结构概述 。

Q4：什么是SQL Server索引？ (Q4: What is a SQL Server Index?)

A SQL Server index is considered as one of the most important factors in the performance tuning process. Indexes are created to speed up the data retrieval and the query processing operations from a database table or view, by providing swift access to the database table rows, without the need to scan all the table’s data, in order to retrieve the requested data.

SQL Server索引被认为是性能调整过程中最重要的因素之一。 通过提供对数据库表行的快速访问，而无需扫描所有表的数据即可检索索引，从而创建索引以加快从数据库表或视图的数据检索和查询处理操作。

You can imagine a table index akin to a book’s index that allows you to find the requested information very fast within your book, rather than reading all the book pages in order to find a specific item you are looking for.

您可以想象一个类似于书本索引的表索引，它使您可以在书本中快速找到所需的信息，而不是阅读所有书本页以查找所需的特定项目。

For more information, see the article: SQL Server index structure and concepts.

有关更多信息，请参见文章： SQL Server索引结构和概念 。

问题5：描述SQL Server索引的结构，该结构可提供对表数据的更快访问？ (Q5: Describe the structure of a SQL Server Index that provides faster access to the table’s data?)

A SQL Server index is created using the shape of B-Tree structure, that is made up of 8K pages, with each page, in that structure, called an index node. The B-Tree structure provides the SQL Server Engine with a fast way to move through the table rows based on index key, that decides to navigate left or right, to retrieve the requested values directly, without scanning all the underlying table rows. You can imagine the potential performance degradation that may occur due to scanning large database table.

使用B-Tree结构的形状创建SQL Server索引，该结构由8K页组成，该结构中的每一页称为索引节点。 B树结构为SQL Server引擎提供了一种快速的方法，该方法可以基于索引键在表行中移动，该索引键决定向左或向右导航，以直接检索请求的值，而无需扫描所有基础表行。 您可以想象由于扫描大型数据库表可能导致的潜在性能下降。

The B-Tree structure of the index consists of three main levels:

索引的B树结构包含三个主要级别：

• Root Level, the top node that contains a single index page, form which SQL Server starts its data search, Root Level ，即包含单个索引页的顶部节点，SQL Server以这种形式开始其数据搜索，
• the Leaf Level, the bottom level of nodes that contains the data pages we are looking for, with the number of leaf pages depends on the amount of data stored in the index,
• Leaf Level （ 叶子级别） ，它是包含我们要查找的数据页面的节点的最底层，叶子页面的数量取决于索引中存储的数据量，
• and finally the Intermediate Level, one or multiple levels between the root and the leaf levels that holds the index key values and pointers to the next intermediate level pages or the leaf data pages. The number of intermediate levels depends on the amount of data stored in the index.
• 最后是中