Shulou(Shulou.com)05/31 Report--

This article is to share with you about the principle of the implementation of index types in MySQL, the editor thinks it is very practical, so I share it with you to learn. I hope you can get something after reading this article.

Classify according to the attributes of the table column:

1. Single column index

An index created with a single column field of a table

two。 Joint index

An index created by a combination of multiple column fields of a table will not take effect until the left-hand field order of the index is used in the query condition, following the leftmost matching principle.

Single-column indexes and federated indexes also include:

General index

Non-primary key, index of non-unique column

Primary key index

Based on the index automatically generated by the primary key of the table, if there is no primary key defined for the table, it will find out whether there is a non-empty, shaping, unique index in the table as its primary key (which can be seen through the select _ rowid from table name). If it is not satisfied, an rowid will be implicitly generated as the primary key (cannot be found directly)

Unique index

An index generated based on the unique column of a table, allowing null values

Full-text index

Find out any content information in the whole book or article stored in the database, such as like% keyword% in a large number of text, the efficiency of the general index is very low compared with the full-text index.

2. Classified according to data structure:

1.B+tree index

B+tree is a multi-path balanced search tree based on balanced binary tree. All records are stored in leaf nodes sequentially, and each leaf node is directly connected by a linked list. Unlike the b tree:

Non-leaf nodes only store key value information.

There is a chain pointer between all leaf nodes.

The data records are stored in the leaf node.

2.hash index

Indexes based on hash table structure are only supported by MEMORY/HEAP and NDB storage engines in mysql

The InnoDB engine supports adaptive hash indexes, but it is used by the database itself and cannot be artificially defined. When the secondary index is accessed frequently, an adaptive hash index is automatically created.

You can view the usage of adaptive hash indexes by using the command SHOW ENGINE INNODB STATUS

Check to see if the adaptive hash index is turned on by SHOW VARIABLES LIKE'% ap%hash_index'.

Contrast:

Because the hash index compares its hash value, the hash index can only do equivalent lookups, not range lookups.

Hash index cannot be sorted for the same reason as above

The leftmost matching principle is not supported. Hash values are calculated when composite indexes are merged.

The retrieval efficiency of hash index is very high and can be located at once, but when a large number of hash collisions occur, the linked list becomes longer, and the efficiency of hash index is not as efficient as b+tree.

Due to the problem of hash collisions, hash indexes can not avoid table scans at any time when the total number is needed.

3.T-tree Index of Wuxi abortion Hospital which is a good http://www.wxbhnkyy120.com/

4.R-tree index

Third, classify by storage structure:

1. Clustered index (clustered index)

The clustered index of InnoDB actually stores both the index and the whole row data in the same BTree structure, and the query data rows can be obtained directly through the index query.

Clustering index is not a single index type, but a way to store data. The order of clustering index is the physical order of data on the hard disk.

In mysql, a clustered index is usually synonymous with a primary key, and each table contains only one clustered index (not necessarily for other databases).

two。 Secondary index (nonclustered index, secondary index, secondary index)

Nonclustered indexes hold index columns and primary keys in the leaf node of the BTree. If the query column is not in the index, you can only find its primary key value, and you need to go back to the table operation to query the clustered index.

Advantages of clustered indexes:

You can save the relevant data together, for example, when implementing an e-mail box, you can aggregate the data according to the user's ID, so that you only need to read a small number of data pages from the disk to get all the messages of a user. If the clustered index is not used, each message may result in a disk IO.

Data access is faster, and clustered indexes save indexes and data in the same btree, so getting data from a clustered index is usually faster than finding it in a nonclustered index.

Queries that use override index scanning can directly use the primary key values in the page node

Disadvantages of clustered indexes:

Clustered data maximizes the performance of IO-intensive applications, but if the data is all in memory, the order of access is less important, and clustered indexes have no advantage.

The insertion speed depends heavily on the insertion order. Inserting according to the primary key order is the fastest way to load data into the innodb table, but if the data is not loaded in the primary key order, it is best to use the optimize table command to reorganize the table after loading.

Updating clustered index columns is expensive because it forces innodb to move each updated row to a new location

A table based on a clustered index may face the problem of page splitting when a new row is inserted, or when the primary key is updated and the row needs to be moved. When the primary key value of the row requires that the row be inserted into a full page, the storage engine splits the page into two pages to accommodate the row, which is a page split operation, which causes the table to take up more disk space.

Clustered indexes may cause full table scans to be slower, especially when rows are sparse, or when data storage is discontinuous due to page fragmentation

The secondary index may be larger than expected because the leaf node of the secondary index contains the primary key column that references the row.

Secondary index access requires two index lookups instead of one

These are the principles of the implementation of index types in MySQL. The editor believes that there are some knowledge points that we may see or use in our daily work. I hope you can learn more from this article. For more details, please follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.