Indexed access to-sql Server

Full table Scan unordered scan table scan/unordered clustered Index Scan

Either a table scan or an unordered clustered index scan continuously scans all the data pages in the table.

Table Scan

An unordered full table scan on the heap is called a table scan .

For example, the order table is organized by heap structure (without a clustered index) and executes the following query:

SELECT OrderID, CustID, Empid, ShipperID, OrderDate from Orders;

Based on the table's IAM page, SQL Server indicates that the magnetic arm scans the extents that belong to the table in physical order, which equals the number of pages that the table contains. However, in this type of scan, SQL Server typically uses a very efficient read-ahead policy (for continuous I/O) that can be used to read data in chunks larger than 8 KB (pages), so the actual number of reads is less than the pages that the table contains.

unordered clustered Index Scanan unordered full table scan on a clustered table is called an unordered clustered index scan . Although there is a clustered index on the table, SQL Server still scans the data continuously using an IAM page of the clustered index (clustered table), which means that the access method does not depend on the linked list that maintains the logical order of the clustered index, but rather on the clustered index leaf-level page The physical order on the disk.

Because the operation is not different from the table scan, it is collectively referred to as a table scan .

unordered Overlay non-clustered index scan

The unordered overlay non-clustered index scan concept is similar to an unordered clustered index scan. An overwrite index represents a nonclustered index (an indexed column) that contains all the columns in the query. In other words, the overwrite index is not an index with a special attribute, but an overlay index on a particular query. SQL Server access to index data (non-clustered index leaf pages) allows you to find all the data needed to satisfy the query without having to access the full data rows. On the other hand, the access method is the same as an unordered clustered index scan, except that the leaf level that overrides the nonclustered index is less than the leaf level of the clustered index, so the row size is smaller (containing only the indexed columns), and each page can accommodate more rows.

For example, theorder table has a nonclustered index on the OrderID column, which means that all OrderID of the table ( Order ID ) is on the leaf level of the nonclustered index, which means that the nonclustered index overrides the query above. Execute the following query:

SELECT OrderID from Orders;

SQL Server uses an IAM page for nonclustered indexes to continuously scan leaf-level pages for nonclustered indexes, which are accessed in the physical order of the leaf-level pages of a nonclustered index on disk.

Sequential Scan

Sequential Clustered Index Scan

An ordered clustered index scan is a full scan of the clustered index leaf level performed by a list of links.

For example, the order table has a clustered index on the OrderID column and executes the following query:

SELECT OrderID, CustID, Empid, ShipperID, OrderDate

From Orders

ORDER by OrderDate;

Unlike unordered index scans, the performance of ordered scans depends on the fragmentation level of the index. That is, an unordered page in the index leaf level is a percentage of the total number of pages. Out-of-order pages refer to logically appearing behind a page, but physically in front of it, depending on the linked table. For an index without fragmentation, the performance of an ordered index scan is close to an unordered scan because both are continuously reading the physical data. However, as fragmentation levels become more and more high, their performance differences are becoming more pronounced. Of course, the disorderly scan performance is higher.

Ordered overlay nonclustered index scan

An ordered overlay nonclustered index scan is conceptually similar to an ordered clustered index scan, except that only the non-clustered index leaf-level pages need to be accessed. Because only fewer pages are involved, it is certainly less expensive than a clustered index scan.

For example, theorder table has a nonclustered index on the OrderID column, and the nonclustered index overwrites our query, executing the following query:

SELECT OrderID from Orders ORDER by OrderID;

Local ScanOrdered scan for nonclustered index lookups+ordered local scan+lookups

This type of access is typically used for small-scale queries (including point queries ), and the nonclustered indexes used do not overwrite the query.

The first step is to perform a lookup within a nonclustered index to locate the first key (orderid=101) of the queried range.

The second step is to perform an ordered local scan at the leaf level starting at the first key in the range until the last key is found.

The third step is to find each key to find ( Lookup ) corresponds to the data row.

Note The third step does not need to wait until the second step is completed. For each key found in the range , lookupis applied, and lookup isperformed in the heap by simply reading one page while the clustered table performs the lookup The number of pages read equals the series of the clustered index.

This method of access, the third step Lookup operations typically account for a large part of the query cost because it involves a lot of i/o lookup Read a page in the heap for each key found or in the clustered index Perform a full lookup lookup i/o (not continuous I/O)

to estimate the I/O cost of this query, you can usually focus on the cost of lookups, and if you want to more accurately estimate I/o costs, you can also consider finding and ordered local scanning of nonclustered indexes. But as the range becomes larger, the cost of this part is negligible. The cost of a lookup operation for a nonclustered index is approximately 3 (the series of non-clustered indexes) secondary logical reads. The cost of I/O for an ordered local scan depends on the number of rows in the range and the number of rows that the nonclustered index leaf page can hold. The general local scan does not actually contain additional reads, because all the keys in our lookup range are generally located on the leaf page where the find operation arrives, or on the next page or pages in the connection list. The I/O cost of the lookups operation equals the number of rows in that range multiplied by the cost of a lookup. For lookup on a heap, the cost is a logical read at a time. For lookup on a clustered table, the number of logical reads is the series of the clustered index. Because all lookup operations in a clustered table query the non-leaf level of the clustered index, the non-leaf level of the clustered index is usually in the cache, so you don't have to worry too much about the high cost of the lookup surface in the clustered table.

apply on the heap

For example, the Orders table (organized by heap) has a nonclustered index on the OrderID column, but the index does not overwrite the following query.

SELECT OrderID, CustID, Empid, ShipperID, OrderDate

From Orders

WHERE OrderID between 101 and 120;

apply on a clustered table

For example, the Orders table has a clustered index on the OrderID column and a nonclustered index on the OrderDate column, and the table does not have an index that overrides the query.

SELECT OrderID, CustID, Empid, ShipperID, OrderDate

From Orders

WHERE OrderDate >= ' 20060101 ' and OrderDate < ' 20070101 ';

Clustered index Lookup+ordered local scan

This access method is used for range queries that are filtered by the first key column of a clustered index . This method first performs a find operation (seek) to find the first key within the range, and then applies a local scan at the clustered index leaf level, from the first key to the last key. The main advantage of this approach is that it does not involve lookups. For larger scopes, The cost of lookups is very high, and as the range becomes larger, it does not involve lookups access methods and the use of nonclustered indexes and lookups access Methods will be more and more significant in terms of performance.

For example, table ordershas a clustered index on (OrderDate,OrderID) . Execute the following query:

SELECT OrderID, CustID, Empid, ShipperID, OrderDate

From Orders

WHERE orderdate = ' 20060212 ';

Although the filter uses the Equals operator, it is essentially a range lookup because there are multiple qualifying rows in a table, and a point query can also be considered a special case of a range query. The cost of this access method includes the cost of the lookup cost on the clustered index (the clustered index series) and the ordered local scan within the clustered index leaf-level page.

Ordered scanning typically accounts for most of the cost of the query because it involves most of the I/O. For an ordered index scan, index fragmentation plays a critical role. When fragments are at the lowest level, physical reads are almost continuous. However, as the fragmentation level grows, the arm must move frantically back and forth, severely reducing the performance of the scan.

Overwrite nonclustered index lookups+ordered local scan

Overwrite nonclustered index lookup + ordered local Scan This access method is almost identical to the previous access method, except that the former uses an overlay nonclustered index instead of a clustered index. To use this method, the filtered column must be the first key column of the index. In contrast to the previous method, the benefit of this access method is that a leaf-level page of a nonclustered index can accommodate more rows than a single page-level page of a clustered index, so most of the cost of the access method, the leaf-level local scan cost, is lower, that is, within the same range, fewer pages are scanned, Of course, index fragmentation also has an important impact on performance because local scans are ordered.

For example, table orders has a nonclustered index in (CustID,OrderDate), and the clustered index overrides the following query.

SELECT OrderDate, CustID

From Orders

WHERE custid= ' C0000000001 ' and OrderDate >= ' 20060101 ' and OrderDate < ' 20070101 ';

Unordered Scan unordered nonclustered index scan +lookupsApplicable conditions:1, the selectivity of the query is high enough. Selectivity is defined as the percentage of rows in the table that are returned by the query. 2. Nonclustered indexes do not maintain the keys that are found in order. For example, this is the case when you filter a column that is not the first key column of a nonclustered index. The access method performs an unordered full scan of the index leaf level and then executes a series of Lookups. The selectivity of the query must be high enough to apply this access method, otherwise, too much lookup will make the method's access cost higher than scanning the entire table directly. To calculate the selectivity of a query, SQL Server needs to have the statistics for the filtered columns (a histogram that contains the distribution of values). The cost of the query includes the cost of an unordered scan of a nonclustered index (continuous I/O using IAM pages) and lookups cost (random I/O). The number of pages scanned for a nonclustered index equals the number of pages in the nonclustered index leaf level. The cost of lookups equals the number of qualifying rows multiplied by the cost of one lookup. In the on-top lookup cost is 1 page reads, on the clustered table, the number of logical reads is the progression of the clustered index. application on the heap

For example, the table order has a nonclustered index (OrderDate, CustID), where CustID is not the first key column in a nonclustered index. Execute the following query:

SELECT OrderID, CustID, Empid, ShipperID, OrderDate from Orders WHERE CustID = ' C0000000001 ';

applications on the aggregation table

For example, the table order has a nonclustered index (OrderDate, CustID), where CustID is not the first key column in the nonclustered index, and the table order has a clustered index on the column OrderID. Execute the following query:

SELECT OrderID, CustID, Empid, ShipperID, OrderDate from Orders WHERE CustID = ' C0000000001 ';

Indexed access to-sql Server