What is a database index? Detailed explanation of database index

What is the index?

Search operations are very common in databases, and indexing is a means to improve search speed.

Index classification

1. B+tree index
   It is an index in the traditional sense, it is the most commonly used and most effective index.

2. Hash index
   The hash index is an adaptive index. The database will automatically generate a hash index based on the usage of the table. We cannot intervene manually.

3. Full text index
   is used to implement keyword search. But it can only segment words based on spaces, so it does not support Chinese.
   To implement the search function, you can choose lucene.

4. RTree index
   It is rarely used in mysql and only supports the geometry data type; compared with BTREE, the advantage of RTREE is range search.

B+Tree Index

The database uses pages as storage units. One page is 8K (8192Byte), and one page can store N records.
The page is divided into: data page and index page in the B+ tree.
The height of the B+ tree is generally 2-4 layers, so searching for a row record of a certain key value only requires 2-4 IO times, which is more efficient.

Clustered index and non-clustered index

Whether it is a clustered index or a non-clustered index, their logical structure is a B+ tree. The only difference between them is:

• The data page of the clustered index stores complete records; that is to say, the clustered index determines the physical storage order of the table;

• The data page of the non-clustered index Only the address information pointing to the record is stored, and its real data has been stored in the clustered index.

Joint index and covering index

1. Joint index
   Joint index can be used when the query conditions involve multiple columns.

2. Covered index
   You can obtain the information you want to query only through the auxiliary index, without querying the specific record information through the clustered index again.
   Because the covering index does not contain the entire row of records, its size is much smaller than the clustered index.
   It is more suitable for doing some statistical operations.

MyISAM index implementation

1. Primary key index
   In the primary key index, the index page stores the primary key and the offset pointing to the data page. Shift amount; the data page stores the primary key and the address space of the row record to which the primary key belongs.

2. Secondary index
   In MyISAM, there is no structural difference between the primary index and the secondary index (Secondary key), except that the primary index requires that the key be unique, while the secondary index Key can be repeated.

To sum up, in MyISAM, index files and data files are stored separately. Whether it is the primary key index or the auxiliary index, they are all non-clustered indexes.

InnoDB index implementation

1. Primary key index
   The index page still stores the primary key and the offset pointing to the data page, but the data page stores the complete record .
   That is, in InnoDB, data and primary key indexes are stored together.

2. Auxiliary index
   The content stored in the index node is the same, it is still the key value information and the offset pointing to the data page; but the data page stores the key value information and the offset of the data page. The primary key corresponding to the key value. Then the record can be found by querying the primary key index through the primary key.

To sum up:

• The implementation of clustered index makes the search by primary key very efficient, but the auxiliary index search needs to retrieve two Index pass: First retrieve the auxiliary index to obtain the primary key, and then use the primary key to retrieve the records in the primary index.

• InnoDB's auxiliary index will also include the primary key column, so if the primary key is defined relatively large, other indexes will also be large. If you want to define many indexes on the table, try to define the primary key as small as possible. InnoDB does not compress indexes.

Advantages of Index

• First, by creating a unique index, the uniqueness of each row of data in the database table can be guaranteed.

• Second, it can greatly speed up data retrieval, which is also the main reason for creating an index.

• Third, it can speed up the connection between tables, which is particularly meaningful in achieving referential integrity of data.

• Fourth, when using grouping and sorting clauses for data retrieval, the time for grouping and sorting in the query can also be significantly reduced.

• Fifth, by using indexes, you can use optimization hiders during the query process to improve system performance.

Disadvantages of index

• First, it takes time to create and maintain indexes, which increases as the amount of data increases. .

• Second, the index needs to occupy physical space. In addition to the data space occupied by the data table, each index also occupies a certain amount of physical space. If you want to build a clustered index, then you need The space will be larger.

• Third, when adding, deleting, and modifying data in the table, the index must be dynamically maintained, which reduces the data maintenance speed.

What situations require indexing?

• On columns that often need to be searched, you can speed up the search;

• On the column that is the primary key, force the uniqueness of the column Sexuality and organization of the arrangement structure of data in the table;

• are often used in columns that are connected. These columns are mainly foreign keys, which can speed up the connection;

• Create an index on the column that often needs to be searched based on the range, because the index has been sorted, and its specified range is continuous;

• In the column that often needs to be sorted Create an index on the column, because the index has been sorted, so that the query can use the sorting of the index to speed up the sorting query time;

• Create an index on the column that is often used in the WHERE clause to speed up The speed of judging conditions.

What situations do not require indexing?

• First, indexes should not be created for columns that are rarely used or referenced in queries. This is because, since these columns are rarely used, indexing or not indexing does not improve query speed. On the contrary, due to the addition of indexes, the maintenance speed of the system is reduced and the space requirements are increased.

• Second, indexes should not be added to columns with few data values. This is because, since these columns have very few values, such as the gender column of the personnel table, in the query results, the data rows in the result set account for a large proportion of the data rows in the table, that is, the data that needs to be searched in the table The proportion of rows is huge. Increasing the index does not significantly speed up retrieval.

• Third, indexes should not be added to columns defined as text, image and bit data types. This is because the data volume of these columns is either quite large or has very few values.
  Fourth, when the modification performance is far greater than the retrieval performance, the index should not be created. This is because modification performance and retrieval performance are contradictory to each other. When adding indexes, retrieval performance will be improved, but modification performance will be reduced. When reducing indexes, modification performance will increase and retrieval performance will decrease. Therefore, when modification performance is much greater than retrieval performance, indexes should not be created.

The above is the detailed content of What is a database index? Detailed explanation of database index. For more information, please follow other related articles on the PHP Chinese website!