SQL-Understanding Indexes

I personally think that writing is good and helpful for understanding the index. The following is an excerpt from http://www.cnblogs.com/xiaobier/archive/2009/10/30/1592866.html's understanding of the index.

Creating an appropriate index is the primary prerequisite for query optimization.

Index (IndexIs a user-defined Data Structure Stored on physical media, which is another important item except the table. When you search for data based on the value of the index code, the Index provides quick access to the data. In fact, there is no index,The database can alsoSelectThe statement successfully retrieves the results, but as the table grows larger, the effect of using an "appropriate" index becomes more and more obvious. Note that the word "appropriate" is used in this sentence, because if you do not seriously consider the implementation process when using indexes, indexes can both improve and damage the performance of the database.

(1) an in-depth understanding of the index structure

In fact, you can think of indexes as a special directory. MicrosoftSQL ServerTwo types of indexes are provided: clustered index (Clustered Index, Also known as clustering index, cluster index) and non-clustered index (Nonclustered Index, Also known as non-clustering index and non-cluster index ). The following is an example of the difference between clustered indexes and non-clustered indexes:

In fact, the body of our Chinese dictionary is a clustered index. For example, if we want to check the word "an", we will naturally open the first few pages of the dictionary, because the Pinyin of "an" is"An", And the dictionary of Chinese characters sorted by Pinyin is an English letter"A"And starts with"Z", Then the word" an "is naturally placed in the front of the dictionary. If you have finished allA", It indicates that you do not have the word in the dictionary. Similarly, if you query the word" Zhang, then you will turn your dictionary to the last part, because the Pinyin of "Zhang" is"Zhang". That is to say, the body of the dictionary itself is a directory, and you do not need to query other directories to find the content you need.

We refer to this text content as a directory arranged according to certain rules as "clustered Index ".

If you know a word, you can quickly find it automatically. However, you may also encounter a word you do not know and do not know its pronunciation. At this time, you cannot find the word you want to query according to the method just now, you need to find the word you are looking for based on the "radicals", and then directly go to a page based on the page number after the word to find the word you are looking. However, the words you find in combination with the "radical directory" and "word checking table" are not really the sorting method of the text. For example, you can query the word "Zhang, we can see that the page number of the "Zhang" in the word table after the department head is672Page, the text above the "Zhang" in the word table is "Chi", but the page number is63Page, under "Zhang" is the word "", the page is390Page. Obviously, these words are not really in the upper and lower sides of the word "Zhang, the continuous "Chi, Zhang, and "words you see are actually their sorting in the non-clustered index, which is the ing of words in the dictionary body in the non-clustered index. We can find the words you need in this way, but it requires two steps: first find the results in the directory, and then flip to the page number you need.

We refer to this directory as a directory, and the text as a non-clustered index ".

Through the above example, we can understand what is "clustered index" and "non-clustered index ".

Further, we can easily understand that each table can only have one clustered index, because directories can only be sorted in one way.

(2) When to use clustered or non-clustered Indexes

The following table summarizes when to use clustered or non-clustered indexes (important ).

Action Description	Use clustered Index	Use non-clustered Index
Columns are sorted by group
return data in a certain range		NO
one or a few different values	NO	NO
different decimal places		NO
different values of large numbers	NO
Frequently updated columns	NO
foreign key column
primary key column
Frequently modify index columns	NO

In fact, we can understand the above table through the previous examples of clustered index and non-clustered index definitions. For example, a data item in a certain range is returned. For example, if you have a time column in a table and you have created an aggregate index in this column, you can query2004Year1Month1DayTo2004Year10Month1DayThis speed will be very fast for all the data between them, because the text of your dictionary is sorted by date, the clustering index only needs to find the start and end data of all the data to be retrieved. Unlike the non-clustered index, you must first check the page number of each data item in the directory, then you can find the specific content based on the page number.

(3) discuss the mistakes in Indexing Based on actual conditions

The purpose of the theory is to apply it. Although we have just listed when clustered or non-clustered indexes should be used, in practice, the above rules are easily ignored or cannot be comprehensively analyzed based on the actual situation. Next we will talk about the mistakes in Indexing Based on the actual problems encountered in practice, so that you can master the indexing method.

1Primary keys are clustered indexes.

I think this is an extremely bad idea and a waste of clustered indexes. AlthoughSQL ServerThe clustered index is created on the primary key by default.

Generally, we createIDColumn to distinguish each data entry.IDThe column size is automatically increased, and the step size is generally1. Columns in our office automation instanceGIDThat's it. At this point, if we set this column as the primary key,SQL ServerThis column is the clustered index by default. The advantage of doing so is that your data can be stored in the database accordingIDPhysical sorting, but I think this is of little significance.

Obviously, the advantages of clustered indexes are obvious, and each table can have only one clustered index rule, which makes clustered indexes more precious.

From the definition of clustered index we mentioned above, we can see that the biggest advantage of using clustered index is that it can quickly narrow the query scope according to the query requirements to avoid full table scanning. In actual application, becauseIDNumber is automatically generated, and we do not knowIDSo it is difficult for us to useIDNumber. This enablesIDThis primary key is a waste of resources as a clustered index. Second, let eachIDFields with different numbers as clustered indexes do not comply with the rule of "aggregation index should not be set up for different values of large numbers". Of course, this is only for users who often modify the record content, in particular, index items play a negative role, but it does not affect the query speed.

In the office automation system, whether the system homepage displays files, meetings, or file queries that need to be signed by the user, data query is inseparable from the field "date" and the user's "User Name" in any case".

generally, the office automation homepage displays files or meetings that have not been signed by each user. Although our where statement can only limit the situations where the current user has not signed, however, if your system has been established for a long time and the data volume is large, then each user opens the homepage and performs a full table Scan each time. This is of little significance, most users 1 files have been browsed a month ago, this can only increase the database overhead. In fact, when you open the system homepage, the database only queries the user near 3 for files not viewed in the past month, the" date "field is used to restrict table scanning and increase the query speed. If your office automation system has been set up for 2 Years, in theory, the display speed on your homepage is 8 times or even faster.