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

This article will explain in detail what the index in MySQL is. The editor thinks it is very practical, so I share it for you as a reference. I hope you can get something after reading this article.

For advanced development, we often have to write some complex sql, so to prevent writing inefficient sql, we need to understand some of the basics of the index. Through this basic knowledge, we can write a more efficient sql.

01 advantages of index

Greatly reduce the amount of data that the server needs to scan, that is, the amount of IO

Help the server avoid sorting and temporary tables (try to avoid file sorting, but use index sorting)

Change random IO to sequential IO

02 the use of the index

Quickly find rows in the matching where clause

If you can choose among multiple indexes, mysql usually uses the index that finds the fewest rows

If the table has multiple column indexes, the optimizer can use any leftmost prefix of the index to find rows

Retrieve row data from other tables when there is a table join

Find the values of min and max for a specific index column

If sorting or grouping is done with the leftmost prefix of the index, sort and group the tables

In some cases, queries can be optimized to retrieve data values without having to find data rows

03 classification of indexes

The default index of the database is for unique keys.

Primary key index (unique and not empty)

Unique index (unique can be null)

General index (index of ordinary fields)

Full-text indexing (generally established by varchar,char,text types, but rarely used)

A combined index (an index established by multiple words)

Technical terms of 04 index

1. Return to the table

The name field is a normal index. Find the primary key from the B+ tree of the name column, and then find the final data from the B+ tree of the primary key. This is the return table. (the leaf node of the primary key index holds all the data of the column, but the normal leaf node holds the corresponding primary key ID.)

As shown in the figure: the index structure established by name in a use table sql is select * from use where name='sun' will first find the primary key Id=2 corresponding to sun through the non-primary key index name, and then find the entire row data in the primary key index through id=2 and return it. This is the return table.

two。 Overlay index

The required fields can be queried on the non-primary key index, and it is called overwriting index without going back to the table and querying again.

For example, in the name index above, sql is select id,name from user where name = "1". The value of id is already available in the non-primary key index in the first step, so there is no need to query row data in the primary key index according to ID.

3. Leftmost match

In the combined index, match the left first, and then continue to match backward; for example, if there is a federated index composed of name+age in the user table, select * from user where name= "Mr. Ji" and age = 18 matches the leftmost matching, which can be used. However, select * from user where age = 18 does not match, and this index is not needed.

Expand

How to index if it is the following two sql

Select * from user where name= "Mr. Ji" and age = 18 select * from user where age = 18

Because of the leftmost matching principle: you only need to build a composite index age+name.

What if it is the following three sql

Select * from user where name= "Mr. Ji" and age = 18 select * from user where name= "Mr. Ji"

Set up name+age and age indexes, or age+name and name indexes, and look at both.

In fact, name+age and age are better, because indexes also need persistent storage, which takes up disk space and takes up memory when reading. Name+age and age+name are the same, but compared with age alone, it is certain that age takes up less space and name is longer (the larger the index, the more IO may be).

Be careful! Be careful! Be careful! :

When reading many articles, I often see some examples of leftmost matching errors:

If the index is a combined index of name+age, sql is select * from user where age = 18 and name= "Mr. Ji" many people think that this kind of index can not go, in fact, it can. Mysql's optimizer will optimize the adjustment order to name= "Mr. Ji" and age = 18

4. Index push-down

Use the index information as much as possible in the combined index to reduce the number of times of returning to the table as much as possible.

Example: or name+age 's composite index if the query pushed down without an index is to query all matching data through name in the composite index, and then return to the table to query the data rows according to ID, and then filter out the data that meets the age criteria. Index push-down is to match the matching data ID according to age in the composite index through name query, and then return to the table to query the corresponding row data according to ID, which will obviously reduce the number of data items.

05 index matching mode

The mysql official website has prepared some learning test databases, which can be downloaded directly and imported into our own database through source.

Official website address: dev.mysql.com/doc/index-o …

As shown in the figure above, download zip, which contains sakila-schema.sql and sakila-data.sql, which are the sakila library, table and data creation scripts.

Mysql > source / Users/ajisun/Downloads/sakila-db/sakila-schema.sql;mysql > source / Users/ajisun/Downloads/sakila-db/sakila-data.sql

You need to check the execution of the index through explain. The implementation plan has been described in detail in previous articles. For more information, please refer to the implementation plan explain

1. Full value matching

To match all the columns in an index, such as using the staff table in the database sakila

Create a new federated index of three fields:

Mysql > alter table staff add index index_n1 (first_name,last_name,username)

Perform sql:

Mysql > explain select * from staff where first_name='Mike' and last_name='Hillyer' and username='Mike' copy the code

The ref consists of three const, which uses three fields and can all match one piece of data.

two。 Leftmost prefix match

Match only the first few fields in the combined index

Perform sql:

Mysql > explain select * from staff where first_name='Mike' and last_name='Hillyer'

There are only 2 const in ref, one less than the full value match above, so only the first two fields are matched

3. Match column prefix

Can match the beginning of a column, such as the like attribute

Perform sql:

Mysql > explain select * from staff where first_name like 'Mi%'

Type=range, a range query that can match part of a field without the need for a full-value match

If there are fuzzy matching fields, do not put in front of the index, otherwise the index can not be used, as follows

4. Match a range value

You can find a certain range of data

Mysql > explain select * from staff where first_name > 'Mike'

5. Exactly match one column and range match another column

You can query all of the first column and parts of the other column

Mysql > explain select * from staff where first_name = 'Mike' and last_name like' Hill%'

6. Query that accesses only the index

When querying, you only need to access the index, not the data rows, which is actually an index override.

Mysql > explain select first_name,last_name,username from staff where first_name='Mike' and last_name='Hillyer'

Extra=Using index indicates that an index override is used and there is no need to query the table again.

In fact, it is not always best to have an index in a table. In general, an index is effective only if the benefits of finding records outweigh the extra work that the index helps the storage engine to quickly improve. For very small tables, in most cases, there is no index, and full table scanning is more efficient; for medium and large tables, the index is very efficient; but for super-large tables, the cost of establishing and using the index is also very high. it is generally necessary to deal with very large tables separately, such as partitioning, sub-database, sub-table and so on.

This is the end of the article on "what is the index in MySQL". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, please share it for more people to see.

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.