Shulou(Shulou.com)06/01 Report--

This article mainly explains "which index types are supported by MySql". The content in the article is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn which index types are supported by MySql.

What is an index?

Official explanation: index (Index) is a data structure that helps MySQL obtain data efficiently.

Popular understanding: an index is a special file (an index on an InnoDB data table is an integral part of the table space) that contains reference pointers to all records in the data table.

Second, what can index do?

First of all, the index is not omnipotent, the index can speed up the data retrieval operation, but will make the data modification operation slower. Each time the data record is modified, the index must be refreshed. To make up for this to some extent, many SQL commands have a DELAY_KEY_WRITE entry. The purpose of this option is to temporarily prevent MySQL from refreshing the index every time the command inserts a new record and modifies an existing one, and the refresh of the index will wait until all records have been inserted / modified. In situations where many new records need to be inserted into a data table, the role of the DELAY_KEY_WRITE option will be obvious.

Third, why can the use of data indexes improve efficiency?

The storage of the data index is orderly.

In an orderly case, there is no need to traverse index records to query a data through an index

In extreme cases, the query efficiency of data index is dichotomy query efficiency, which is close to log2 (N).

4. What types of indexes does MySQL support?

The index type we are talking about here does not refer to "primary key index" or "foreign key index", but to the underlying data structure of the index. MySQL's index data structures support the following two types:

B-Tree index. The B + tree is a balanced multi-fork tree. The height difference from the root node to each leaf node is less than 1, and the pointers of the nodes at the same level are linked to each other, which is orderly, as shown in the following figure:

Hash index. Hash indexing uses a certain hash algorithm to convert the key value into a new hash value. It does not need to be searched step by step from the root node to the leaf node like the B+ tree, but only needs one hash algorithm, which is unordered, as shown in the following figure:

Range query is not supported

Index completion sorting is not supported

Leftmost prefix matching rules for federated indexes are not supported

The advantage of hash indexing: equivalent query, hash indexing has absolute advantage (premise: there are not a large number of repeated key values, if a large number of repeated key values, the efficiency of hash indexing is very low, because of the so-called hash collision problem. )

Situations where hash indexing is not applicable:

5. Under what circumstances should no or less indexes be built?

We all know when indexes should be used, so when should indexes not be used? As we said above, indexing is not omnipotent, so there must be scenarios where indexing does not apply. We should try not to build or build as few indexes as possible in the following scenarios:

There are too few table records.

Tables that are frequently inserted, deleted, and modified.

For a table field with duplicate and evenly distributed data, if a table has 100000 rows of records, one field A has only T and F values, and the distribution probability of each value is about 50%. Then indexing this table A field will not generally improve the query speed of the database.

A table field that is often queried with the main field but has more index values for the main field.

What is a joint index?

A federated index is an index on two or more columns. For federated indexes, MySQL supports the use of fields in the index from left to right, and a query can use only a portion of the index, but only the leftmost part. For example, the index is key index, which can support the combination of a, b, and b, but it does not support the search of bjournal c. When the leftmost field is a constant reference, the index is very effective.

With additional columns in the index, you can narrow your search, but using an index with two columns is different from using two separate indexes.

The structure of the composite index is similar to that of the phone book, where the first name consists of a last name and a first name. The phone book first sorts pairs by last name, and then sorts people with the same surname by first name. The phone book will be very useful if you know the last name; if you know the last name and first name, the phone book will be more useful, but if you only know the first name and not the last name, the phone book will be useless.

Why is B+ more suitable for file index and database index of operating system in practical application than B-tree?

The disk read and write cost of B+ is lower. The internal node of B+ does not have a pointer to the specific information of the keyword, so its internal node is smaller than the B-tree. If all the keywords of the same internal node are stored in the same disk, the more keywords the disk can hold. The more keywords you need to find when you read them into memory at once. Relatively speaking, the number of IO reads and writes has been reduced.

The query efficiency of B+-tree is more stable. Because the non-endpoint is not the node that ultimately points to the content of the file, but only the index of the keyword in the leaf node. Therefore, any keyword search must take a road from the root node to the leaf node. The path length of all keyword queries is the same, resulting in the same query efficiency for each data.

Thank you for your reading, the above is the content of "which index types does MySql support". After the study of this article, I believe you have a deeper understanding of which index types MySql supports, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

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.