Principles of database indexing

Introduced:

An index is a structure that sorts the values of one or more columns in a database table, such as the name (name) column of the employee table. If you want to find him or her by the name of a particular employee, the index helps you get information faster than searching all the rows in the table.

For example such a query: SELECT * FROM table1 where id=10000. If there is no index, you must traverse the entire table until the row with the ID equals 10000 is found, and after the index (which must be an index established on the ID column), you can find it in the index. Because the index is optimized by some algorithm, the number of lookups is much less. It is visible that the index is used for positioning.

Category: Unique indexes

A unique index is one that does not allow any two rows to have the same index value.
When duplicate key values exist in existing data, most databases do not allow a newly created unique index to be saved with the table. The database may also prevent the addition of new data that will create duplicate key values in the table. For example, if a unique index is created on the employee's last name (lname) in the Employees table, none of the two employees will have a namesake.

Primary key Index

Database tables often have one or more column combinations whose values uniquely identify each row in the table. This column is called the primary key of the table.
Defining a primary key for a table in a database diagram automatically creates a primary key index, which is a specific type of unique index. The index requires that each value in the primary key be unique. When a primary key index is used in a query, it also allows quick access to the data.

Clustered index

In a clustered index, the physical order of rows in a table is the same as the logical (indexed) Order of the key values. A table can contain only one clustered index.
If an index is not a clustered index, the physical order of the rows in the table does not match the logical order of the key values. Clustered indexes typically provide faster data access than nonclustered indexes.

Pros and Cons: Advantages: Creating an index can greatly improve the performance of your system.

First, by creating a unique index, you can guarantee the uniqueness of each row of data in a database table.

Second, it can greatly speed up the retrieval of data, which is the main reason for creating indexes.

Thirdly, the connection between tables and tables can be accelerated, particularly in terms of achieving referential integrity of the data.

Finally, when using grouping and sorting clauses for data retrieval, you can also significantly reduce the time to group and sort in queries.

By using the index, we can improve the performance of the system by using the optimized hidden device in the process of querying.

Disadvantage: The purpose of indexing is to speed up the lookup or sorting of records in a table. Setting an index on a table costs a lot of effort.

One is to increase the storage space of the database.

Second, it takes more time to insert and modify the data (because the index changes as well). A database index is a directory in which values in some fields are established to improve the search efficiency of a table.

Usage scenario: On the column that is the primary key, force the column to be unique and the structure of the data in the organization table;

1. On the columns that are often used on the connection, these columns are mainly foreign keys, which can speed up the connection and create an index on a column that often needs to be searched by scope, because the index is sorted and its specified range is continuous;

2. Create an index on a column that is often ordered, because the index is sorted so that the query can use the sorting of the index to speed up the sort query time;

3. Speed up the judgment of the condition by creating an index on a column that is frequently used in the WHERE clause.

Similarly, indexes should not be created for some columns. In general, these columns that should not create an index have the following characteristics:

First, the index should not be created for columns that are seldom used or referenced in queries. This is because, since these columns are seldom used, they are indexed or non-indexed and do not improve query speed. Conversely, by increasing the index, it reduces the system maintenance speed and increases the space requirement.

Second, you should not increase the index for columns that have only a few data values. This is because, because these columns have very few values, such as the gender column of the personnel table, in the results of the query, the data rows of the result set occupy a large proportion of the data rows in the table, that is, the data rows that need to be searched in the table are large. Increasing the index does not significantly speed up the retrieval.

Third, for those columns defined as text, the image and bit data types should not be indexed. This is because the amount of data in these columns is either quite large or has little value and is not conducive to the use of indexes.

The index should not be created when the performance of the modification is far greater than the retrieval performance. This is because modifying performance and retrieving performance are conflicting. When you increase the index, the retrieval performance is improved, but the performance of the modification is reduced. When you reduce the index, you increase the performance of the modification and reduce the retrieval performance. Therefore, you should not create an index when the modification operation is much more than a retrieval operation.

Technical principle:

Database index is a sort of data structure in the database management system, which helps to quickly query and update data in database tables.

The implementation of an index typically uses a B-tree and its variants, plus trees. For the b+,b-tree, and the principle of the search tree, see my previous related blog, links are as follows:

B-Tree, B + Tree, b* tree Detailed

Data structure-red and black tree detailed

Data structure-Self-balancing binary search tree (AVL) detailed

In addition to data, the database system maintains a data structure that satisfies a particular lookup algorithm that references (points to) data in some way, so that an advanced find algorithm can be implemented on those data structures. This data structure is the index.

Summarize:

Shows a possible way to index. On the left is the data table, a total of two columns seven records, the leftmost is the physical address of the data record (note that logically adjacent records on disk is not necessarily physically adjacent). To speed up the search for Col2, you can maintain a two-fork lookup tree on the right, each containing the index key value and a pointer to the physical address of the corresponding data record, so that the binary lookup can be used to obtain the corresponding data in the complexity of O (log2n).

My QR code is as follows, welcome to exchange discussion

You are welcome to pay attention to the "It question summary" subscription number. Every day to push the classic face test and interview tips, are dry! The QR code of the subscription number is as follows:

Reference:
Http://baike.baidu.com/view/2079871.htm
http://blog.csdn.net/kennyrose/article/details/7532032

Principles of database indexing