Basic database index"

An index is a structure that sorts the values of one or more columns in a database table. You can use an index to quickly access specific information in a database table. Database indexes are like directories in front of a book, which can speed up database queries.

For example, select * From Table1 whereid = 44. If no index exists, you must traverse the entire table until the row with ID equal to 44 is found. If an index exists, it must be an index created on the column with ID ), find 44 in the index (that is, find the ID column) to find the location of this row, that is, find this row. It can be seen that the index is used for locating. It can be seen that the purpose of index creation is to speed up the query or sorting of records in the table.

Necessary for Indexing

If the following statements are often used in queries:

　　
Select * From mytable where category_id = 1;

　
The most direct response is to create a simple index for category_id:

Createindex
Mytable_categoryid on
Mytable (category_id );

What if there are more than one selection condition? For example:

Select * from
Mytable
Where category_id = 1
And user_id = 2;

The first thing we think of is to create an index for user_id. This is not the best method. Multiple indexes can be created.

Createindex mytable_categoryid_userid on mytable (category_id, user_id );

What types of indexes are there?

In general, the classification method is different, and the type is also different.

Generally, indexes can be divided into ordered indexes and hash indexes according to the storage method. Ordered indexes are ordered by values (the values in this file, that is, the value of the field for which the index is created, are placed in the index file sequentially ), the other is hash index, which distributes values evenly to several hash buckets and locates them through the hash function.

The ordered index can be divided into many classes.

If the indexed fields are sorted in a certain order, this index is called a clustered index. Otherwise, it is called a non-clustered index. If each value of the indexed field has an index corresponding to it, this index is called a dense index, otherwise it is called a sparse index. Ordered indexes are divided into two types: single-level indexes (not commonly used) and multi-level indexes (usually B + tree, which are widely used ). A single-level index is used to sort all index fields and their corresponding file locations in order. This index is relatively slow to search. Because it is stored in sequence, you can use the binary search method, but in general, the efficiency is not high. This index is the most basic index.

If you often need to perform and query on two fields at the same time, it is better to create a composite index using two independent indexes, because the database can only use one of the two independent indexes, the use of composite indexes has greatly improved the efficiency because the index itself corresponds to two fields.

Hash Index

It is an index that is located through the hash function, but few use the hash index separately. Instead, the hash file organization is usually used.

Note that creating too many indexes will affect the update and insertion speed, because it needs to update each index file as well. For a table that often needs to be updated and inserted, there is no need to create an index for a rarely used where clause. For a small table, the sorting overhead is not very high, there is no need to create another index.

We are looking forward to huge changes.