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

How to understand and use MySql index correctly

Index is a data structure used by storage engine to find records quickly. Through reasonable use of database index, the access performance of the system can be greatly improved. This paper mainly introduces the type of index in MySql database and how to create more reasonable and efficient index skills.

1. Overview

Index is a kind of data structure used by storage engine to find records quickly. Through reasonable use of database index, the access performance of the system can be greatly improved. Next, it mainly introduces the index types in MySql database and how to create more reasonable and efficient index skills.

Note: this is mainly aimed at the B+Tree index data structure of the InnoDB storage engine

2. The advantages of index

It greatly reduces the amount of data that the server needs to scan, thus improving the speed of data retrieval to help the server avoid sorting and temporary tables can change random Imax O into sequential I/O3 and index creation.

3.1 Primary key index

3.2 unique Index

3.3 General Index

3.4 full-text index

3.5 Composite Index

4. Index rules of B+Tree

Create a user table for the test

Create a composite index: ALTER TABLE user_test ADD INDEX idx_user (user_name, city, age)

4.1 queries with valid indexes

4.1.1 full value matching

Full-value matching refers to matching with all the columns in the index. For example, taking the index created above as an example, you can query (user_name,city,age) the data under the where condition at the same time.

Note: it has nothing to do with the order of query conditions after where. This is a place that many students are easy to misunderstand.

4.1.2 match leftmost prefix

Matching the leftmost prefix means to match the leftmost index column first. For example, the index created above can be used to query the following conditions: (user_name), (user_name, city), (user_name, city, age).

Note: the order in which the leftmost prefix query condition is satisfied is independent of the order of the index columns, such as (city, user_name), (age, city, user_name).

4.1.3 match column prefix

Refers to the beginning of a matching column value, such as querying all users whose user names begin with feinik

4.1.4 match range valu

For example, query all users whose user names begin with feinik, where the first column of the index is used

4.2 restrictions on indexes

1) if the where query condition does not contain the leftmost index column in the index column, the index query cannot be used, such as:

Or

Or

2) even if the query condition of where is the leftmost index column, you cannot use the index to query users whose user names end with feinik

3) if there is a range query for a column in the where query condition, none of the columns on the right can optimize the query using the index, such as:

5. Efficient indexing strategy

5.1 Index column

An index column cannot be part of an expression or an argument to a function, otherwise an index query cannot be used.

5.2 prefix index

Sometimes you need to index long character columns, which will increase the storage space of the index and reduce the efficiency of the index. one strategy is to use a hash index, and another is to use a prefix index. the prefix index selects the first n characters of the character column as the index, which can greatly save the index space and improve the efficiency of the index.

5.2.1 selectivity of prefix indexes

The prefix index should be long enough to ensure high selectivity, but not too long. We can calculate the appropriate selection length of the prefix index in the following ways:

(1)

Note: the selective ratio of the prefix index is calculated in the above way. The higher the ratio is, the more efficient the index is.

(2)

Note: through the above statement to gradually find the selective ratio closest to the prefix index in (1), then you can use the corresponding character truncation length to do the prefix index.

5.2.2 creation of prefix index

5.2.3 points for attention when using prefix indexing

The prefix index is an effective way to make the index smaller and faster, but MySql cannot use the prefix index for ORDER BY and GROUP BY and the prefix index for overwrite scanning.

5.3 Select the appropriate index column order

The order of index columns is very important in the creation of composite indexes. the correct index order depends on the query method of using the index. the index order of the composite index can be accomplished by the rule of thumb: putting the most selective column in the front column of the index is consistent with the selective method of the prefix index, but it does not mean that the order of all composite indexes can be determined by using this rule. You also need to determine the specific index order according to the specific query scenario.

5.4 clustered and nonclustered indexes

1), clustered index

A clustered index determines the physical sorting of data on a physical disk. A table can only have one clustered index. If a primary key is defined, InnoDB will aggregate the data through the primary key. If no primary key is defined, InnoDB will choose a unique non-empty index instead. If there is no unique non-empty index, InnoDB will implicitly define a primary key as the clustered index.

Clustered index can greatly improve the access speed, because the clustered index saves the index and row data in the same B-Tree, so when the index is found, the corresponding row data is found, but when using the clustered index, we should pay attention to avoid random clustered index (generally refers to the primary key value is not continuous, and the distribution range is uneven). If using UUID as the clustered index, the performance will be very poor. Because the discontinuity of UUID values will lead to a lot of index fragmentation and random Imax O, which will eventually lead to a sharp decline in the performance of the query.

2), nonclustered index

Unlike the clustered index, the nonclustered index does not determine the physical sorting of the data on disk, and the B-Tree contains the index but does not contain row data, the row data only points to the row data through the corresponding pointer of the index stored in the B-Tree, for example: the index established on (user_name,city, age) above is a nonclustered index.

5.5 overwrite index

If an index (such as a composite index) contains the values of all the fields to be queried, it is called an override index, such as:

Because the fields to be queried (user_name, city, age) are contained in the index column of the composite index, an overlay index query is used to see if an overlay index can be used to prove the use of an overlay index by using a value of Using index in the Extra in the execution plan. Overriding an index can greatly improve access performance.

5.6 how to use an index to sort

In the sorting operation, if you can use the index to sort, then you can greatly improve the speed of sorting, to use the index to sort need to meet the following two points.

1) the column order after the ORDER BY clause should be the same as the column order of the composite index, and the sort direction of all sort sequences (positive / reverse) should be the same.

2), the field values queried need to be included in the index column and satisfy the overlay index.

Analyze concretely through examples

Create a composite index on the user_ test table

Cases in which index sorting can be used

Note: point 4 is special. If the where query condition is the first column of the index column and is a constant condition, then the index can also be used.

Cases in which index sorting cannot be used

1), sex is not in the index column

2) the direction of the sequence is not consistent.

3) the field column sex to be queried is not included in the index column

4) the user_name after the where query condition is a range query, so other columns of the index cannot be used

5), when querying multiple table joins, index sorting can be used only if the sort fields after ORDER BY are index columns in the first table (two rules for index sorting need to be satisfied). Such as: create a user's extended table user_test_ext, and establish the index of uid.

Sort by index

Do not go index sort

6. Summary

This paper mainly talks about the index rules of B+Tree tree structure, the creation of different indexes, and how to correctly create efficient index skills to improve the query speed as much as possible. Of course, these are not the only skills for the use of indexes, and more skills about indexes also need to accumulate relevant experience.

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.