Indexes in SQL Server

1 indexes in SQL Server

　　An index is an on-disk structure associated with a table or view, which speeds up the retrieval of rows from a table or view. An index contains keys that are generated by one or more columns in a table or view. These keys are stored in a structure (B-tree), allowing SQL Server to quickly and efficiently find the rows associated with the key value.

A table or view can contain the following types of indexes:

　　Clustered index

The clustered index sorts and stores the data rows in a table or view based on the key values of the data rows. The index definition contains a clustered index column. Each table can have only one clustered index, because the data rows themselves can only be sorted in one order.

Data rows in a table are stored in sorted order only when the table contains a clustered index. If the table has a clustered index, the table is called a clustered table. If a table does not have a clustered index, its data rows are stored in an unordered structure called a heap.

Almost every table defines a clustered index on a column to achieve the following features:

1, can be used for frequently used queries.

2, to provide a high degree of uniqueness.

Before you create a clustered index, you should first understand how the data is accessed. Consider using a clustered index for queries that have the following characteristics:

Returns a series of values using operators such as between, >, >=, <, and <=.

When you use a clustered index to find the row that contains the first value, you can ensure that the row containing the subsequent index values is physically adjacent. For example, if a query retrieves records between a series of purchase order numbers, the clustered index of the Purchaseordernumber column quickly locates the row that contains the starting purchase order number, and then retrieves all successive rows in the table until the last purchase order number is retrieved.

Returns a large result set.

Use the JOIN clause; In general, the foreign key column is used for the clause.

Use an ORDER by or a GROUP by clause.

The index of the column specified in the ORDER by or GROUP By clause enables the database engine to not have to sort the data because the rows are already sorted. This can improve query performance.

Clustered indexes are not available for columns with the following properties:

Columns that change frequently

This causes the entire row to move because the database engine must keep the data values in the row in physical order. This is especially important because the data in the bulk transaction processing system is usually mutable.

Wide Key

A wide key is a combination of several columns or several large columns. All nonclustered indexes use the key values in the clustered index as lookup keys.　Any nonclustered indexes defined for the same table will grow a lot, because nonclustered index entries contain clustered keys and also contain key columns defined for this nonclustered index. Nonclustered Indexes

Nonclustered indexes have a structure that is independent of the data rows. Nonclustered indexes contain nonclustered index key values, and each key-value entry has a pointer to the data row that contains the key value.

A pointer to a data row from an index row in a nonclustered index is called a row locator. The structure of a row locator depends on whether the data page is stored in a heap or in a clustered table. For heaps, a row locator is a pointer to a row. For clustered tables, the row locator is the clustered index key.

In SQL Server 2005, you can add nonkey columns to the leaf level of a nonclustered index to skip existing index key restrictions (900-byte and 16-key columns) and perform full-scope index queries.

Nonclustered indexes have the same B-tree structure as clustered indexes, and the significant difference between them is the following two points:

1. The data rows of the underlying table are not sorted and stored in the order of the nonclustered keys.

2. The leaf layer of a nonclustered index is made up of index pages rather than data pages.

You need to be aware of the characteristics of a database when designing a nonclustered index:

Databases or tables that have a low update requirement but contain large amounts of data can benefit from many nonclustered indexes to improve query performance.

Decision support system applications and databases that primarily contain read-only data can benefit from a number of nonclustered indexes. The query optimizer has more indexes to choose from to determine the fastest access method, and the low update characteristics of the database means that index maintenance does not degrade performance.

An online transaction processing application and a database that contains a large number of update tables should avoid using too many indexes. In addition, the index should be narrow, that is, the fewer columns the better.

A table that has a large number of indexes can affect the performance of the INSERT, UPDATE, and DELETE statements because all indexes must be adjusted accordingly with the data changes in the table.

　　Unique index

A unique index ensures that the index key does not contain duplicate values, so that each row in the table or view is to some extent unique.

Both clustered and nonclustered indexes can be unique indexes.

　　Include Index of the column

A nonclustered index that expands to include not only key columns but also nonkey columns.

　Index Covers

Refers to the case where the column used by the SELECT and WHERE clause in the query is also a nonclustered index. This allows the data to be retrieved faster because all information can come directly from the index page, so that SQL Server can avoid accessing the data pages. Together with a separate set of indexed files, you can access the data with the fastest speed.

Take a look at the following example:

A. Creating a simple nonclustered index The following example creates a nonclustered index for the VendorID column of the Purchasing.ProductVendor table.

Use AdventureWorks; GO CREATE INDEX Ix_productvendor_vendorid On Purchasing.ProductVendor (VendorID); GO

B. Creating a simple nonclustered composite index

The following example creates a nonclustered composite index for the SalesQuota and SalesYTD columns of the Sales.SalesPerson table.

CREATE nonclustered INDEX IX_SALESPERSON_SALESQUOTA_SALESYTD On Sales.SalesPerson (SalesQuota, SalesYTD); GO

C. Creating a unique nonclustered index

The following example creates a unique nonclustered index for the Name column of the Production.UnitMeasure table. This index forces the data in the Insert Name column to be unique.

Use AdventureWorks; GO CREATE UNIQUE INDEX Ak_unitmeasure_name On Production.UnitMeasure (Name); GO

The SQL Server 2005 Database engine maintains indexes automatically whenever an INSERT, update, or delete operation is performed on the underlying data. Over time, these modifications may cause information in the index to be scattered in the database (containing fragmentation). Fragmentation occurs when the logical ordering (based on the key value) in the page contained by the index does not match the physical ordering in the data file. A very numerous index of fragments can degrade query performance and cause the application to respond slowly. At this point, what we need to do is reorganize and regenerate the index. Rebuilding the index deletes the index and creates a new index. This process removes fragmentation, reclaims disk space by setting compressed pages with the specified or existing fill factor, and re-sorts the indexed rows in successive pages (assigning new pages as needed). This can improve disk performance by reducing the number of page reads required to obtain the requested data.

You can regenerate clustered and nonclustered indexes using the following methods:

ALTER INDEX with the REBUILD clause. This statement replaces the DBCC dbreindex statement.

CREATE INDEX with the DROP_EXISTING clause.

Examples are as follows:

A. Rebuilding an index

The following example regenerates a single index. Use AdventureWorks;
GO
ALTER INDEX Pk_employee_employeeid on HumanResources.Employee
REBUILD;
GO
B. Rebuilding all indexes of a table and specifying options The following example specifies the ALL keyword. This will regenerate all indexes associated with the table.　      Three options are specified. ALTER INDEX all on production.product
REBUILD with (FILLFACTOR = +, sort_in_tempdb = ON,
Statistics_norecompute = ON);
GO
2 index indexes in Oracle are database objects used by Oracle to retrieve data in the accelerator tables.　　The following scenarios can be considered using the index: 1) Large table 2) primary key (auto index) 3) key column (auto index) 4) Foreign keys column (auto index) 5) Large table on the WHERE clause commonly used column 6) ORDER by or the column used in the GROUP BY clause.　　7) A column that returns at least 20% rows in the table 8) that does not contain null values. The indexes in Oracle include the following types: b* Tree index: This is the most commonly used index in Oracle, which is constructed like a binary tree, providing quick access to a row or set of rows depending on the key, usually with very few read operations to find the correct row. The b* tree index consists of two columns, the first column is ROWID, it is the position of the row, and the second column is the value of the column being indexed.

Figure: A typical b* tree index layout

The block at the bottom of the tree is called a leaf node or leaf block, which contains each index key and a rowid (it points to the row being indexed). The inner block above the leaf node is called the branch Block (branch block), which is used to implement navigation. For example, if you want to find the value 20 in the index, to start at the top of the tree and find the left branch, we examine the block and find that we need to find the block of the range "20..25", which will be a leaf block, which indicates the row containing the number 20. The leaf node of the index actually forms a doubly linked list. Once found to start from there in the leaf node, an ordered scan of the values (Index range Scan) is easy and we no longer have to navigate through the index structure: it is only possible to scan forward or backward based on the leaf node.

One of the characteristics of the b* tree is that all leaf blocks should be on the same layer of the tree, which is called the height of the index, which shows that all traversal from the root block of the index to the leaf block accesses the same number of blocks. That is, for a shape such as "Select Index_column from TABLE WHERE inxdex_column =:x" index, to reach the leaf block to get the first row, regardless of the use of: X value is what, will perform the same number of I/O, this shows b* The tree's B stands for balanced, the so-called "Height balanced". Most b* tree indexes have a height of 2 or 3, even if there are millions of rows of records in the index, which means that in general, finding a key in the index requires only 2 to 3 I/O, which is really good.

The b* tree is an excellent universal indexing mechanism, both large and small, and as the underlying table grows, the performance of the fetch data only deteriorates slightly.

For example, we create a common b* tree index for the Customers table:

CREATE INDEX idx_cus_city on customers

The b* Tree Index has the following sub-types:

　　Composite Index

A composite index is also a b* tree index, which consists of multiple columns. When we have frequent queries that use two or more than two columns, the b* Tree composite Index is used, and the two or more columns used in the WHERE clause are connected by the and logical operators. Because the order of the columns in the composite index is important, be sure to arrange them with the most efficient index, you can refer to the following two guidelines for column sorting:

1) The leading column should be the most frequently used column in the query.

2) The leading column should be the most selected column, which means it has a higher cardinality than the subsequent columns.

Composite indexes have advantages in the following situations:

1) Assume that the following conditions are frequently used in the WHERE clause: order_status_id = 1 and order_date = ' dd-mon-yyyy '. If you create an index for each column, two indexes are read to search for the column's value, but if you create a composite index for both columns, then only one index is read, which undoubtedly requires less I/O than the two indexes.

2) using the same criteria in the previous example, if you create a composite index, the rows will be retrieved more quickly because you are excluding all order_status_id rows that are not 1, thus reducing the number of rows to search for order_date.

　　Reverse Key index

Another feature of the b* tree index is the ability to "invert" the index key. First, you can ask yourself, "Why do you want to do this?" The b* Tree index is designed for specific environments and specific problems. The purpose of implementing the b* Tree index is to reduce the competition for indexed leaf blocks in the "right" index, such as in an Oracle RAC environment, where some columns are populated with a sequence value or timestamp, and the indexes established on those columns belong to the "right" (right-hand-side) index.

A RAC is an Oracle configuration in which multiple instances can mount and open the same database. If two instances need to modify the same block of data at the same time, they are shared by passing the block back and forth through a hardware interconnect (interconnect), which is a private network connection between two (or more) machines. If a sequence is populated with a primary key index on the column, then each person inserts a new value, the view modifies the left block to the right of the current index structure (see figure I, which shows that the higher values in the index are placed on the right, while the lower values are on the left). If you modify an index on a column populated with a sequence, it is clustered on a small set of leaf blocks. If the key of the index is reversed, when the index is inserted, it can be spread across all the leaf keys in the index (although this often causes the index to not be fully populated).

The Reverse key index creation statement syntax is as follows:

CREATE INDEX index_name on table_name (column_name) REVERSE;

　　Descending index

The descending index (descending index) is a feature introduced by Oracle 8i to extend the b* tree index, which allows a column to be stored in the index in descending order (from large to small). In Oracle8i and above, the DESC keyword does change the way that indexes are created and used.

We can create descending indexes like this

CREATE INDEX idx_jobs_title on Hr.jobs (Job_title DESC); SET autotrace traceonly EXPLAIN; SELECT * from Hr.jobs WHERE job_title between ' a ' and ' zzzzzzzzzzz '; Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT optimizer=choose (cost=1 card=1 bytes=33) 1 0 FILTER 2 1 TABLE ACCESS (by INDEX ROWID) of ' JOBS ' (cost=1 card=1 B YTES=33) 3 2 INDEX (RANGE SCAN) of ' Idx_jobs_title ' (non-unique) (C ost=2 card=1) Sql> SELECT * from Hr.jobs 2 WHERE job_title between ' a ' and ' zzzzzzzzzzz '; Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT optimizer=choose (cost=2 card=1 bytes=33) 1 0 FILTER 2 1 TABLE ACCESS (full) of ' JOBS ' (cost=2 card=1 bytes=33) sql> DROP INDEX Idx_jobs_title; Sql> CREATE INDEX idx_jobs_title on Hr.jobs (Job_title); Sql> Select * from Hr.jobs 2 Where job_title between ' a ' and ' zzzzzzzzzzz '; Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT optimizer=choose (cost=2 card=1 bytes=33) 1 0 FILTER 2 1 TABLE ACCESS (full) of ' JOBS ' (cost=2 card=1 bytes=33)

　　Bitmap index

The bitmap index (bitmap index) was introduced starting from the Oracle7.3 version. Currently, both Oracle Enterprise and Personal Editions support bitmap indexing, but the Standard Edition is not supported. Bitmap indexing is designed for the Data Warehouse/online analytic query environment, where the data required for all queries is not known at all in the system implementation. Bitmap indexing is particularly inappropriate for OLTP systems, which are also unsuitable for bitmap indexing if the data in the system is updated frequently by multiple concurrent sessions.

A bitmap index is a structure in which a pointer to multiple rows is stored with an index key entry, which differs from the b* tree structure in that the index key and the row in the table exist in the b* tree structure. In a bitmap index, there may be very few index entries, and each index entry points to multiple rows. In the traditional b* tree, an index entry points to one row.

The b* tree index should generally be selective. In contrast, bitmap indexes should not be selective, and generally they should be "no selectivity". Bitmap indexing is particularly useful in environments where there are a large number of online analytic queries, especially if the query references multiple columns in an ad hoc manner or generates aggregations such as count. The bitmap index is created using the Create BITMAP index index_name on table_name (column_name1,column_name2) tablespace tablespace_name command syntax.

Indexes in SQL Server