select version()：5.7.21

EXPLAINWhat is it?

MySQL provides aEXPLAINCommand, it canSQLStatement, and outputSQLImplementation details for developers targeted optimization

For example, analyze a select statement

EXPLAIN SELECT * FROM `user` WHERE id = 1 

In the explain resulttypefield

Tips: a common scanning method

• System: system table, a small amount of data, often do not need disk IO
• Const: constant connection
• eq_ Ref: primary key or unique not null
• Ref: non primary key non unique index equivalent scan
• Range: range scan
• Index: index tree scanning
• All: full table scan

typeScanning mode from fast to slow

system > const > eq_ref > ref > range > index > ALL

1.system

In the above example, from the system label of MySQL, proxies_ Query the data in priv. The data here has been loaded into the memory when the MySQL service is started. There is no need for disk IO.

Explanation in the official document:The table has only one row (= system table). This is a special case of the const join type

2.const

Analog data

create table user (
  id int primary key,
  name varchar(20)
)engine=innodb;

insert into user values(1,'ar414');
insert into user values(2,'zhangsan');
insert into user values(3,'lisi');
insert into user values(4,'wangwu');

Explain analysis results

In the above example, ID is the primary key, and the connection part is constant 1, which can be found by indexing once. The speed is very fast

Scene:

• HitPrimary keyperhapsUnique index
• The connected part is aConstant value (const)

3.eq_ref

Analog data

create table user (
  id int primary key,
  name varchar(20)
)engine=innodb;

insert into user values(1,'ar414');
insert into user values(2,'zhangsan');
insert into user values(3,'lisi');
insert into user values(4,'wangwu');

create table user_balance (
  uid int primary key,
  balance int
)engine=innodb;

insert into user_balance values(1,100);
insert into user_balance values(2,200);
insert into user_balance values(3,300);
insert into user_balance values(4,400);
insert into user_balance values(5,500);

Explain analysis results

ar414

For the previous table in the above exampleuserEach row in the table corresponds to the followinguser_balanceOnly one row of the table is scanned, and this kind of scanning is very fast

Scene:

1. Joint table(join) query
2. HitPrimary key(primary key) orNon empty unique index(unique not null)
3. equivalenceconnect

4.ref

Analog data

Same as EQ_ Ref analog data difference: user_ In the balance tableprimary key Change toGeneral index

create table user (
  id int primary key,
  name varchar(20)
)engine=innodb;

insert into user values(1,'ar414');
insert into user values(2,'zhangsan');
insert into user values(3,'lisi');
insert into user values(4,'wangwu');

create table user_balance (
  uid int,
  balance int,
  index(uid)
)engine=innodb;

insert into user_balance values(1,100);
insert into user_balance values(2,200);
insert into user_balance values(3,300);
insert into user_balance values(4,400);
insert into user_balance values(5,500);

Explain analysis results

Joint table query

Because the following table usesGeneral non unique index, for the previous tableuserEach row of the table, followed by the tableuser_balanceA table may have more than one row of data scanned

Single table query

When ID is changed to ordinary non unique index, constant join query is also degraded from const to Ref. because of non unique index, more than one row of data may be scanned

refEach match may have multiple rows of data returned, although it is better than Eq_ Ref is slower, but it’s still a fast join type

Scene:

• Joint table query
• General non unique index

5.range

Analog data

create table user (
  id int primary key,
  name varchar(20)
)engine=innodb;

insert into user values(1,'ar414');
insert into user values(2,'zhangsan');
insert into user values(3,'lisi');
insert into user values(4,'wangwu');
insert into user values(5,'zhaoliu');

Explain analysis results

between

in

>,>=,<,<=

Range is easy to understand. It is a range query on the index. It will scan the value in a specific range on the index

6.index

Voice over: the current test table is InnoDB. MyISAM has a built-in counter, which reads directly from the counter when count()

The index type needs to scan all the data on the index, which is only a little faster than the full table scan

7.ALL

Analog data

create table user (
  id int,
  name varchar(20)
)engine=innodb;

insert into user values(1,'ar414');
insert into user values(2,'zhangsan');
insert into user values(3,'lisi');
insert into user values(4,'wangwu');
insert into user values(5,'zhaoliu');

Explain analysis results

If no index is built on the ID, the whole table is scanned

summary

• From fast to slow: system > const > EQ_ ref>ref>range>index>ALL
• As a qualified back-end developer, you should be familiar with explain
• Build the right index in combination with the business, not every field (abuse)

This work adoptsCC agreementReprint must indicate the author and the link of this article