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

Original: https://www.enmotech.com/web/detail/1/767/1.html

The article is reproduced from the official account architect's Road, author Shen Jian 58

Introduction: this article explains the common type results and the meaning of their representatives, and shows how important it is to build an index through the performance differences of the same SQL statement.

What does the type field in the explain result mean?

MySQL's official website explanation is very concise, using only three words: connection type (the join type). It describes the scanning method used to find the required data.

The most common scanning methods are:

System: system table, a small amount of data, often without the need for disk IO

Const: constant connection

Eq_ref: primary key index (primary key) or non-null unique index (unique not null) equivalent scan

Ref: non-primary key non-unique index equivalent scan

Range: range scan

Index: index tree scan

ALL: full table scan (full table scan)

Voiceover: these are the most common. 95% of the SQL statements in explain your work are of the above types.

The above scanning methods range from fast to slow:

System > const > eq_ref > ref > range > index > ALL

The following examples are given.

1. System

Explai select * from mysql.time_zone

In the above example, the data is queried from the system table time_zone of the system library mysql, and the scan type is system. The data has been loaded into memory and does not need to be IO on disk.

This kind of scanning is the fastest.

Explain select * from (select * from user where id=1) tmp

To give another example, inner nesting (const) returns a temporary table, and the outer layer is nested to query from the temporary table. The scan type is also system, and it does not need to go to disk IO, which is super fast.

II. Const

Data preparation:

Create table user (

Id int primary key

Name varchar (20)

) engine=innodb

Insert into user values (1)

Insert into user values (2 recorder Zhangsan')

Insert into user values (3 recorder Lisi`)

The conditions for const scanning are:

(1) hit the primary key (primary key) or unique (unique) index

(2) the connected part is a constant (const) value.

Explain select * from user where id=1

As in the above example, id is a competition, and the connection part is a constant 1.

Voiceover: don't make any kind of conversion.

This kind of scanning is very efficient, the amount of data returned is small, and the speed is very fast.

III. Eq_ref

Data preparation:

Create table user (

Id int primary key

Name varchar (20)

) engine=innodb

Insert into user values (1)

Insert into user values (2 recorder Zhangsan')

Insert into user values (3 recorder Lisi`)

Create table user_ex (

Id int primary key

Age int

) engine=innodb

Insert into user_ex values (1BI 18)

Insert into user_ex values (2Jing 20)

Insert into user_ex values (3Jing 30)

Insert into user_ex values (4pl 40)

Insert into user_ex values (5pm 50)

The condition for eq_ref scanning is that for each row of the previous table, only one row of the back table is scanned.

A little more detail:

(1) join query

(2) hit primary key (primary key) or non-null unique (unique not null) index

(3) equivalent connection

Explain select * from user,user_ex where user.id=user_ex.id

As in the example above, id is the primary key, and the join query is eq_ref scan.

The speed of this kind of scanning is also unusually fast.

IV. Ref

Data preparation:

Create table user (

Id int

Name varchar (20)

Index (id)

) engine=innodb

Insert into user values (1)

Insert into user values (2 recorder Zhangsan')

Insert into user values (3 recorder Lisi`)

Create table user_ex (

Id int

Age int

Index (id)

) engine=innodb

Insert into user_ex values (1BI 18)

Insert into user_ex values (2Jing 20)

Insert into user_ex values (3Jing 30)

Insert into user_ex values (4pl 40)

Insert into user_ex values (5pm 50)

If you change the primary key index in the eq_ref case above to a normal non-unique (non unique) index.

Explain select * from user,user_ex where user.id=user_ex.id

Eq_ref is downgraded to ref, where more than one row of data in the back table may be scanned for each row (row) of the previous table.

Explain select * from user where id=1

When id is changed to a normal non-unique index, constant join queries are also downgraded from const to ref, because more than one row of data may also be scanned.

Ref scans, which may appear in join or in a single table normal index, may have multiple rows of data returned per match. Although it is slower than eq_ref, it is still a fast join type.

5. Range

Data preparation:

Create table user (

Id int primary key

Name varchar (20)

) engine=innodb

Insert into user values (1)

Insert into user values (2 recorder Zhangsan')

Insert into user values (3 recorder Lisi`)

Insert into user values (4dwangwu')

Insert into user values (5 minzhaoliu')

Range scanning is easier to understand, which is a range query on the index that scans the index for values within a specific range.

Explain select * from user where id between 1 and 4

Explain select * from user where idin (1, 2, 3)

Explain select * from user where id > 3

Like the between,in in the example above, > are typical range queries.

Voiceover: it must be an index, otherwise you can't skip it in batches.

VI. Index

Index type, which needs to scan all the data on the index.

Explain count (*) from user

As in the example above, id is the primary key, and the count query needs to be counted by scanning all the data on the index.

Voiceover: this table is the InnoDB engine.

It is only a little faster than a full table scan.

7. ALL

Data preparation:

Create table user (

Id int

Name varchar (20)

) engine=innodb

Insert into user values (1)

Insert into user values (2 recorder Zhangsan')

Insert into user values (3 recorder Lisi`)

Create table user_ex (

Id int

Age int

) engine=innodb

Insert into user_ex values (1BI 18)

Insert into user_ex values (2Jing 20)

Insert into user_ex values (3Jing 30)

Insert into user_ex values (4pl 40)

Insert into user_ex values (5pm 50)

Explain select * from user,user_ex where user.id=user_ex.id

If there is no index on the id, for each row of the previous table, the latter table will be scanned throughout the table.

In today's article, this same join statement appears three times:

(1) scan type is eq_ref, and id is the primary key

(2) scan type is ref, and id is a non-unique ordinary index.

(3) scan type is ALL, full table scan. There is no index on id.

This shows how important it is to improve the performance of the database by establishing the correct index.

In addition, incorrect SQL statements may result in a full table scan.

Full table scan is very expensive and low performance, so it should be avoided. It is very necessary to analyze SQL statements through explain.

Summary

(1) the type field in the explain result, indicating the (generalized) connection type, which describes the scanning method used to find the required data.

(2) the common scanning types are:

System > const > eq_ref > ref > range > index > ALL

Its scanning speed changes from fast to slow.

(3) the main points of various scanning types are:

System fastest: no disk IO

Equivalent query on const:PK or unique

Join query on eq_ref:PK or unique, equivalent matching, for each row (row) of the previous table, only one row of the latter table hits

Ref: non-unique index, equivalent match, possible multiple line hits

Range: range scan on the index, for example: between/in/ >

Index: full set scan on the index, for example: count of InnoDB

Slowest ALL: full table scan (full table scan)

(4) it is very important to establish a correct index (index)

(5) it is very important to use explain to understand and optimize the implementation plan

Ideas are more important than conclusions. I hope you will gain something.

Voiceover: this article is tested in MySQL5.6.

Origin: the architect's path (ID:road5858)

Want to know more about databases and cloud technologies?

Come and follow the official account of "data and Cloud" and the official website of "Yunhe Enmo". We look forward to learning and making progress with you!

(scan the QR code above and follow the official account of "data and Cloud" for more science and technology articles.)

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.