0, written in the previous words about the content of the index is originally wanted to write, probably collected the next information, found and did not imagine the simple, and do not want to summarize, tangled up a bit, decided to write some superficial, well, is lazy, first dig a shallow pit, and then dig deeper. The basic use is simple, just write it here.
Indexes are well-known to increase the speed of queries, and are specific to the field, and are used (without specifying a nonclustered index):
< Span class= "PLN" >create INDEX Span class= "pun" >< index name; Span class= "PLN" > on < table name (relationship name); e g create INDEX yearindex on movie year
And undo the index:
DROP INDEX <索引名> ON <表名>;e.g.DROP INDEX yearIndex ON movie;
1, index 1.1 The concept of index in the process of database query, even if the records satisfy the given conditions are very few, also need to scan the whole table relationship, when the relationship is very large, its overhead is very large.
For example, in a movie table, look for Disney's 2000 films:
SELECT *FROM movieWHERE studioName=‘Disney‘ AND year=2000;
Suppose there are 10,000 records in the movie table, of which there are 200 films produced in 2000, of which the production company is Disney's only 10. If we do not take steps to implement the query, we will have to check all 10,000 records to see if the conditions are met. If there is a way to let us only take the year 2000 years of 200 records, and then go to the production company for Disney, obviously more efficient.
There is a way to do this, it's called
Index, it is a
data structure that provides access paths for a given field in a table。
So what is an index? Above this explanation completely does not understand, we still is the image a little explanation, Netizen Elysee in its blog "The Database Optimization Practice" The index article "" The story, very image, but I always understand some improper, in accordance with their own meaning modified a bit, the story is as follows:
A long time ago, there were thousands of books in the big Library of an ancient city, but the books on the shelves were not placed in any order, so whenever someone asked for a book, the librarian had to look for it and spend a lot of time each time.worse, the library's books are getting more and more, and the librarian's work becomes extremely painful.
one day, the library came to a clever lad, he saw the librarian's painful work, came up with a way, he said: "You have all the bookshelf, according to the English alphabet into 26 parts, each book according to the title of the alphabetical order, put on the corresponding shelves." For example, "Avanti" is placed in a bookshelf, if there is "Avatar", also placed in a bookshelf, and according to the title of the alphabetical "Avatar" (AfD) in the "Avanti" (aft) ", so that if someone has designated the name of the book,then the librarian will soon be able to find its place.
(books are placed alphabetically on the corresponding letter shelves, just like creating a clustered index, where all rows in the table are physically sorted on the file system based on the title, and when any row in the table is queried, the database first uses the clustered index to find the corresponding data page, just as the corresponding letter bookshelf is first found And then the target line can be found in order, just like finding a book on a bookshelf)
So the librarian began sorting and placing, for which he spent a whole week, and finally, he found that the efficiency of finding books really improved.
(only one clustered index can be created on a table, just as a book can be placed as a single rule)
But the problem is not completely solved, there are many people want to see the author of all the books, the librarian helpless and only scan all the author of the book, to look for one by one, the time has become too long, so he asked the smart lad for help.
(It's like you've added index bookname to the Book table, but there's no other index, and when retrieving with Bookauthor, the database engine is looking for a full-table scan, one at a time)
The smart guy tells the librarian, so create a directory document. Well, the document will be sorted by author, the books rearranged, and the author's books and books corresponding to the Bookshelf location together to record it, not to move the real location of the book. In this way, once someone has designated the author, the author's book can be found according to the catalog document classified by the author.
So the librarian took the time to tidy up a directory document, and it worked, and then hestarting with new thinking, readers may also find books based on other properties of the book, such as the type of books, fiction? Poetry? Or something else, so he used this method to create a catalogue of books, which can now be quickly found by title, author, and genre, and the librarian's work became easier.
This is the end of the story. The above mentioned a concept, called
Clustered Index, the index order and the physical order are the same, there can only be one, that is, the title here;
Nonclustered Indexes, there can be multiple, the index order and the physical order is not related, that is, later the author directory, category directory.
1.2 Storage of the index to understand the difference and meaning of the two, you need to first talk about the storage of the index. First, an index record contains:
- key value (that is, the value of all fields specified when you define the index)
- logical Pointer (point to a data page or another index page)
In addition, whether the index is a clustered index or a nonclustered index, it is a separate space for the database, not attached to the original data. So when an index is created, the database system assigns an index page, and whenever you insert a row of data into a table, the database system inserts a row of index records. The index page at this time is
root node, if full, splits the logical pointer that originally points to the data page, replacing it with a logical pointer to the child index page (for example).
1.3 Type of index 1.3.1 clustered index so-calledClustered Indexis to determine the physical order of the data in the table, such as the titles of the books listed above, or the contacts of the mobile phone book by last name. It
specifies the order in which the data is physically stored in the table
, so a table can contain only one clustered index。 That is, the index store order is consistent with the order in which the data rows are stored.
For example, if we establish a clustered index on a name field, the database system looks for the root of this index based on a particular system table and then finds the next one, based on the pointer, when it is necessary to find a particular record based on this field. For example we want to query "green", because it is between [Bennet,karsen], so we found the index page 1007, in the page "green" between [Greane, Hunter], so we find the leaf node 1133 (also known as Data node), The target data row is finally found on this page.
1.3.2 Nonclustered Indexes we say
clustered index storage records are physically contiguous, and nonclustered indexes are logically contiguous, so there is no such thing as a sequence in physics, and it is independent of the physical storage order.
As you can see, the index record structure of a nonclustered index has changed a certain amount, which includes:
- Indexed field value (key value)
- The page pointer of the data page, and the pointer offset (relative to the clustered index, the new section)
- Pointer to next index page
Because the clustered index is sequential, we end up simply pointing to the index page, which can be found in order,
The Clustered Index final index page stores the page pointer, not the row pointer .
instead of a clustered index, because it is unordered, it means that the nonclustered
index stores an index record for each row of data for accurate querying. Here is the page pointer to the data page, and the pointer offset, similar to the coordinates of the data row, stored in the index record. That is, the nonclustered index stores the key value and its corresponding data coordinates, and for index paging, it also contains a third section, which is used to store the next index page pointer.
(Like the previous story, if the books have been sorted by name, you have to look for "flowers in the evening," you know, in the Z Bookshelf, if the books are not sorted, and you have a directory of the author category, you have to find Lu Xun's "Chao Hua Xi", meaning that the document must record the specific location of the book)
2. Examples of Chinese dictionaries if you don't understand the difference between clustered and nonclustered indexes, here's an example of the image:
1) Clustered indexThe text of a Chinese dictionary is itself a clustered index.
For example, we have 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 alphabetical order of Chinese characters in the dictionary is the English letter "a" beginning and "Z", then the word "Ann" naturally ranked in the front of the dictionary. If you have turned over all the parts that begin with "a" and still can't find the word, then it means you don't have the word in your dictionary.
Similarly, 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. The body content itself is a directory of rules arranged by a certain rule called a "clustered index".
2) Nonclustered indexesIf you know a word, you can quickly check it out automatically. But you may also encounter the words you do not know, do not understand its pronunciation, at this time, you can not follow the method to find the word you want to check, and need to go to the "radicals" to find the word you are looking for, and then according to the page number after the word directly to a page to find the word you are looking for.
But the sort of words you find in combination with the "radicals" and "gept" is not really the sort method of the body, for example, you check the word "Zhang", we can see in the Gept table after the Radicals "Zhang" page number is 672 pages, gept table "Zhang" above is "Chi" word, but the page number is 63 pages, "Zhang" below is "crossbow "Word, page is 390 pages. Obviously, these words are not really in the "Zhang" the word of the upper and lower side, now you see the continuous "Chi, Zhang, crossbow" three words is actually their order in the nonclustered index, is the dictionary body of words in the non-clustered index mapping. We can find the words you need in this way, but it takes two procedures to find the results in the catalog and then turn to the page numbers you need.
3. Brief summary
- Indexes can improve query efficiency
- Clustered index A table can have only one, and a table with a nonclustered index may have multiple
- Clustered index storage records are physically contiguous, while nonclustered indexes are logically contiguous, and physical storage is not contiguous
Also, it is well known that indexes can actually improve query speed, but it will affect the efficiency of insertions, deletions, and updates, and it is precisely because of changes in the data that indexes must be maintained and consumed performance. Therefore, it is not possible to create indexes blindly, but to create and use them reasonably.
4. Reference links
- SQL Index One Step
- Database Optimization Practice "index chapter"
- One of the advanced series of database: Random Talk about database index
- Differences in clustered and nonclustered indexes
Introduction to SQL Optimization: 3, using the index
