SQL Review Five (index)

SQL index occupies a very large proportion in database optimization, a good index design, can make your efficiency increase dozens of or even hundreds of times times, here will take you step by step to uncover his mysterious veil.

1.1 What is an index?

There are two types of SQL indexes, clustered and nonclustered, and indexes are primarily designed to improve the performance of SQL Server systems, speed up data queries and reduce system response time

Here are two simple examples:

Library Example: a library so many books, how to manage it? Create a directory with the beginning of a letter, for example: A book, in the first row, B starts in the second row, so in search of what book to say, this is a clustered index, but a lot of people borrow books to find a writer, do not know the title how to do? The librarian is writing a table of contents, the books of a certain author in the first row, the first few rows, this is a nonclustered index

Dictionary example: the dictionary in front of the directory, you can follow the pinyin and radicals to query, we want to query a word, just according to pinyin or radical to query, you can quickly locate the Chinese character, this is the benefit of the index, pinyin query method is a clustered index, the radical query is a nonclustered index.

Looking at the above example, the following sentence is easy to understand: Clustered index storage records are physically contiguous, while nonclustered indexes are logically contiguous, and physical storage is not contiguous. Just like a field, a clustered index is contiguous, A is definitely B, a nonclustered index is not contiguous, like a library author's book, possibly on the 1th shelf and the 10th shelf. There is also a small knowledge point is: A clustered index can only have one table, and a non-clustered index a table may exist more than one.

　

1.2 Storage mechanism of indexes

First, the table without index, query, is the sequential method of scanning each record to find eligible records, so inefficient, for example, if we will be the dictionary of Chinese characters, no previous in accordance with the pinyin or radical query, then we want to find a word, in order to go to a page to find, It's so efficient, you can imagine.

The fundamental difference between clustered and nonclustered indexes is that the table records are arranged in the same order as the index, in fact it is very simple to understand, or a dictionary example: if you follow the pinyin query, then all are from A-Z, is a continuity, a behind b,b is C, the clustered index is like this, He is the same as the physical order of the table, for example, the ID is a clustered index, then 1 must be behind the 2,2 is definitely 3, so that the search order is the clustered index. The nonclustered index is the same as the radical query, and may be queried according to the side room, depending on the radicals ' Bow ' word, index out two Chinese characters, Zhang, but these two actually one in 100 pages, one on 1000 pages, (here is just for example), their index order and database table order is not the same, this kind of is a nonclustered index.

The principle is clear, then how do they store it? To put it simply, a clustered index is a physical space in which the database is opened to hold the value of his arrangement, for example, 1-100, so when the data is inserted, he rearranges the entire physical space, and the nonclustered index can actually be thought of as a table containing a clustered index. He only contains columns with non-clustered indexes in the original table and pointers to actual physical tables. He just records a pointer, actually a little bit like the stack.

1.3 Where to set the index

Action Description	Using Clustered Indexes	Using Nonclustered indexes
FOREIGN key columns	Should	Should
Primary key columns	Should	Should
Columns are often sorted by group (order by)	Should	Should
Return data in a range	Should	should not
A small number of different values	Should	should not
A large number of different values	should not	Should
Columns that are frequently updated	should not	Should
Frequently modifying index columns	should not	Should
One or very few different values	should not	should not

Principles for indexing:

1) The data column defining the primary key must be indexed.

2) The data column that defines the foreign key must be indexed.

3) It is best to index data columns that are queried frequently.

4) For data columns that require quick or frequent queries within a specified range;

5) Data columns that are frequently used in the WHERE clause.

6) often appear in the keyword order BY, group by, distinct after the field, to establish an index. If you are building a composite index, the field order of the index is consistent with the order of the fields following these keywords, otherwise the index will not be used.

7) For columns that are rarely involved in those queries, duplicate values are not indexed for more columns.

8) do not index columns for data types that are defined as text, image, and bit.

9) Avoid indexing for frequently accessed columns

9) Limit the number of indexes on the table. For a table that has a large number of update operations, the number of indexes is typically no more than 3, not more than 5. Although the index improves access speed, too many indexes can affect the update operation of the data.

10) Index The composite index, based on the frequency of the fields appearing in the query criteria. In a composite index, a record is first sorted by the first field. For records with the same value on the first field, the system is sorted by the value of the second field, and so on. Therefore, only the first field of the composite index appears in the query criteria, the index may be used, so the application of a high frequency field, placed in front of the composite index, will allow the system to use the index in the most possible way to play the role of the index.

1.4 How to create an index

　　1.41 syntax for creating indexes:

CREATE [unique][clustered | Nonclustered] Index < index name >

on {table_name | view_name} (column name [Sort by] [, column name ranking method] ...)

Description

Unique: Create a unique index.

CLUSTERED: Build a clustered index.

Nonclustered: Builds a nonclustered index.

Column name, ranking method: The column created by the index can be a column, or multiple columns, separated by a comma between the column names. The sort method can be DESC and ASC (Ascending), ASC by default

UNIQUE: The index can take either a clustered index structure or a nonclustered index structure, and the SQL Server system defaults to a nonclustered index structure if the index structure is not specified.

Create Unique Index  on desc);

1.42 Delete Index Syntax:

Drop INDEX < index name > on < table name >

Drop Index  on EMP;

1.43 Display index Information:

Show index from Tbname;

Former part reprinted from: http://www.cnblogs.com/AK2012/archive/2013/01/04/2844283.html

Create a look at this article: http://blog.csdn.net/yuanzhuohang/article/details/6497021

SQL Review Five (index)