SQL Server INDEX OPTIMIZATION Series II: index performance considerations

As mentioned above, indexing can greatly improve the data retrieval speed. Why not index every column? Beginners may be confused about this problem, and usually do not know which columns should be indexed or which should not be created, or even use like fuzzy query as index columns. In fact, in most cases, like does not use indexes. Only equal to, greater than, and in operators use indexes. Sqlserver indexes must be updated for data insertion, update, and deletion. This will undoubtedly greatly increase the update time. In addition, if a data page is full, if you want to insert data to the page, the page split will generate fragments (which will be discussed later), affecting performance. Therefore, an index is created only when the query performance is more important than the update performance.

Columns to be indexed

1. Primary Key
2. Foreign key
3. frequently searched columns and columns frequently searched in order of sorting

Generally, the column referenced by the condition following the WHERE clause is the column to be indexed, except for Fuzzy queries (such as like queries)
Do not consider creating indexed Columns

1. Few or never reference columns in queries
2. Columns with only two or more values (for example, columns with only two values for male and female)
3. Small tables (tables with few rows, at this time, SQL Server takes a longer time to index than to scan the table directly)

SQL Server pays a certain cost to maintain the index for the columns that create an index. In addition, sqlserver automatically analyzes whether to use the index of this column. For example, if a column has only two values, male and female, it is considered that the efficiency of using the index to search for a column is not very high. Because the percentage of the returned result set is large, sqlserver will record the statistics. When you look for this column next time, the index of the modified column is determined based on the statistical data.

SQL Server may not use the index of a column with a large percentage of returned result sets (for example, if 1 million of data is returned and 0.5 million is returned, the full table scan method is used. You can perform a test on your own to insert 2000 data records, and 1999 data records are the same. For example, if forumid is 2, there are 1999 data records, and forumid is 3, there is only one data entry.

Set showplan_text on-display the execution plan to view the indexes used by the query statement
Go

Select * from posts where forumid = 2
The index of the forumid column is not used.

Select * from posts where forumid = 3
The index of the forumid column is used.

To insert or update a large volume of data, you must first Delete the index and then re-create the index to avoid updating the index every time you insert or update a data record, which affects the update speed.
Composite Index (an index composed of two or more columns. When the WHERE clause is followed by a condition consisting of multiple columns, you can create a composite index for these columns)

1) This index is used only when the first column of the index key is specified in the WHERE clause.
Example:
Create index posts_index
On posts (threadid, forumid)

If select * from posts where forumid = 2, the query will not use the posts_index index.
While select * from posts where threadid = 10 will use posts_index Index

2) The index should not be too large (<= 8 bytes is the best, int type is equivalent to 4 bytes, smallint is equivalent to 2 bytes ).
3) first define the most unique column (the order is different, and the index is different)
For example, if column A contains 30% of the duplicate data, column B has 10% of the duplicate data, and column C has 25% of the duplicate data, at this time, the order of the columns to be indexed should be B C.

There is also an important option to create an index: fill factor. Next article continues.

 

One of the SQL Server INDEX OPTIMIZATION series: Working Principle & clustered index | non-clustered Index

SQL Server INDEX OPTIMIZATION Series II: index performance considerations

SQL Server INDEX OPTIMIZATION Series 3: Fill Factor