SQL Server Index

What is an index

A database index is a structure that sorts the values of one or more columns of a table, and the relationship of the index to the table data is similar to the relationship between the catalog and the content of the book. There are two more important indexes in Sql-server, clustered and nonclustered, which are stored in the B + tree organization.

Why to use an index

Data query is a very frequent use of database operations, the speed of query has become a measure of the quality of the system is an important criterion, and the rational use of indexes can improve the efficiency of data retrieval, improve database performance, speed up data access speed.

1. Pre-read: Use the estimated information to go to the hard disk to read the data to the cache. Pre-read 100 times, which is estimated to read 100 pages of data from the hard disk to the cache.
2. Physical reads: After the query plan is generated, if the cache is missing the required data, let the cache read the hard disk again. Physically read 10 pages and read 10 pages of data from the hard disk to the cache.
3. Logical read: All data is fetched from the cache. Read Logic 100 times, which means fetching 100 pages of data from the cache.

Clustered index

The clustered index determines the physical order of the data in the table. A clustered index is similar to a phone book, which arranges data by last name. Because a clustered index specifies the order in which data is physically stored in a table, a table can contain only one clustered index. However, the index can contain multiple columns (combined indexes), just as the phone book is organized by last name and first name.

Clustered indexes are particularly effective for columns that are frequently searched for range values

If a table does not have a clustered index, it is stored as a heap, that is, the data in the table is in no order. Otherwise, if we create a clustered index for the table, then its storage is in order.

The greatest benefit of a clustered index is the ability to quickly narrow the query to avoid full-table scans based on query requirements

Nonclustered indexes

In fact, the body of our Chinese dictionary itself is a clustered index, for example, we want to check the word "Ann", it will be very natural to open the first few pages of the dictionary, because "ann" Pinyin is "an", and in accordance with the phonetic alphabet of Chinese character dictionary is the English letter "a" beginning and "Z" end, then The child is naturally lined up in the front of the dictionary. If you have turned over all the parts that begin with "a" and still cannot find the word, then it means that you do not have the word in your dictionary, and if you look up the word "Zhang", you will also turn your dictionary into the last part, because the pinyin of "Zhang" is "Zhang". That is, the body part of the dictionary is itself a directory, and you do not need to look up other directories to find what you need to find. We refer to this body of content itself as a directory of certain rules, called a "clustered index."

If you know a word, you can quickly find the word from the automatic. But you may also encounter the word, do not know its pronunciation, at this time, can not follow the method to look up the word, and need to according to the "Radicals" to check, we put this directory is purely a directory, the body is purely the text of the sorting method called "Nonclustered index."

SQL Server defaults to establishing a clustered index on the primary key

A simple example of the disadvantage of a primary key setting up an aggregated index

(1) The clustered index is established only on the primary key, and the time period is not divided:
Select Gid,fariqi,neibuyonghu,title from Tgongwen
Spents: 128470 milliseconds (i.e.: 128 seconds)

(2) Set up a clustered index on the primary key and a nonclustered index on Fariq:
Select Gid,fariqi,neibuyonghu,title from Tgongwen
where Fariqi> DateAdd (Day,-90,getdate ())
Spents: 53763 milliseconds (54 seconds)

(3) Set up the aggregation index on the date column (Fariqi):
Select Gid,fariqi,neibuyonghu,title from Tgongwen
where Fariqi> DateAdd (Day,-90,getdate ())
Spents: 2423 milliseconds (2 seconds)

Although each statement extracts 250,000 data, the differences in the various cases are enormous, especially when the clustered index is set in the Date column. In fact, if your database really has 10 million capacity, the primary key is built on the ID column, as in the 1th and 2 cases above, the performance on the Web page is timed out and cannot be displayed at all. This is also one of the most important factors that I discard the ID column as a clustered index. The method for the above speed is: Before each SELECT statement, add:

DECLARE @d datetime
Set @d=getdate ()
and add it after the SELECT statement:
Select [Statement execution takes time (milliseconds)]=datediff (Ms,@d,getdate ())

SQL Server Index