What is an index? Why do we need an index? And give examples. (Take a specific DBMS as an example)

Indexes are another way of representing data that provides an order of data different from the order in which it is physically stored on disk. The special role of an index is to rearrange the physical location of records within a table. An index can be built on a single column of a data table or on a combination of several columns of a table.

Think back to a library where shelves and shelves of books are stored. Assuming that a book is to be found, the librarian would have to search through them one by one until the desired book is found. Such a search must be time-consuming and laborious, then the administrator can be all the titles of the book according to the alphabetical classification of the establishment of index cards, in alphabetical order in different office drawers, so that a shelf to find the process of the book has become in the desk drawer to query the index cards, it is easy to get the information about the book.

Furthermore, imagine that the librarian is very diligent, he is not only in accordance with the alphabetical classification of the book title, but also by the author's name and publisher to establish another index card, this process will give borrowers in the retrieval of information about the book to greater flexibility. Therefore it is possible to create multiple indexes in a single data table.

In databases, a query of a table without an index is generally referred to as a full table scan. A full table scan is a process used by a database server to search for every record of a table until all records that meet the given criteria are returned. This operation can be compared to checking out a book in a library, starting with the first book on the first shelf and browsing through each book until the desired book is found. In order to carry out efficient query, you can build an index for a field on the data table, because the index includes a pointer to the data, the database server is only along the order of the index of only one column of data for the index to read (only one index) until the index pointer to the corresponding record until. Since the index is looked up only by a field and does not traverse the entire table, index lookups are generally faster than full table scans.

So, is not the use of index lookup must be faster than a full table scan? The answer is no. If you are querying a small data table (with few records) or a large data table (with many records) for the majority of the data, a full table scan is more practical. For example, if you query the "gender" field, the value of which can only be "male or female", it does not make sense to build an index on it, or even to build an index on Boolean or large binary types (notes, images, etc.).

Example: /diylsoft/html/manual/DBQuery/IndexSummarize.htm

Clustered index:

/view/1028053.html?wtp=tt