MySQL's explain

Explain is typically used for parsing SQL.
As follows

[SQL] Plain Text View Copy Code?

010203040506070809101112131415161718192021222324252627282930

drop table if exists test1;CREATE TABLE test1 (    id INT NOT NULL primary keyauto_increment, -- 自动递增    username varchar(5) not null -- 用户名) ENGINE=innodb ; insert into test1 (username) values (concat(‘test‘,rand()));insert into test1 (username)   select username from test1;insert into test1 (username)   select username from test1;insert into test1 (username)   select username from test1;insert into test1 (username)   select username from test1;insert into test1 (username)   select username from test1;insert into test1 (username)   select username from test1;insert into test1 (username)   select username from test1;insert into test1 (username)   select username from test1;insert into test1 (username)   select username from test1;insert into test1 (username)   select username from test1;insert into test1 (username)   select username from test1;insert into test1 (username)   select username from test1;insert into test1 (username)   select username from test1;insert into test1 (username)   select username from test1;insert into test1 (username)   select username from test1;insert into test1 (username)   select username from test1;insert into test1 (username)   select username from test1;insert into test1 (username)   select username from test1;insert into test1 (username)   select username from test1;insert into test1 (username)   select username from test1;insert into test1 (username)   select username from test1;insert into test1 (username)   select username from test1;

Create a subset of the data first. And then execute

[SQL] Plain Text View Copy Code?

1	explain select* fromtest1;

Results

Explain select username from test1;
+----+-------------+-------+------+---------------+------+---------+------+---------+-------+
| ID | Select_type | Table | Type | Possible_keys | Key | Key_len | Ref | Rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+---------+-------+
| 1 | Simple | Test1 | All | NULL | NULL | NULL | NULL | 4187248 | NULL |
+----+-------------+-------+------+---------------+------+---------+------+---------+-------+
1 row in Set (0.00 sec)

Re-execute

[SQL] Plain Text View Copy Code?

1	explain selectusername from test1 whereusername = ‘test‘;

+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
| ID | Select_type | Table | Type | Possible_keys | Key | Key_len | Ref | Rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
| 1 | Simple | Test1 | All | NULL | NULL | NULL | NULL | 4187248 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
1 row in Set (0.00 sec)

Next, create an index for the username of the table.

[SQL] Plain Text View Copy Code?

1	ALTERTABLE `test1` ADD INDEX`i_name` (`username`) comment ‘‘;

The process compares the cards. It takes a few seconds to dozens of seconds. Depending on the machine. You can also insert a few data less. And then execute the two statements just now.

Mysql> explain select username from test1 where username = ' test ';
+----+-------------+-------+------+---------------+--------+---------+-------+------+-------------------------- +
| ID | Select_type | Table | Type | Possible_keys | Key | Key_len | Ref | Rows | Extra |
+----+-------------+-------+------+---------------+--------+---------+-------+------+-------------------------- +
| 1 | Simple | Test1 | Ref | I_name | I_name | 7 |    Const | 1 | Using where; Using Index |
+----+-------------+-------+------+---------------+--------+---------+-------+------+-------------------------- +
1 row in Set (0.00 sec)

Mysql> explain select username from test1;
+----+-------------+-------+-------+---------------+--------+---------+------+---------+-------------+
| ID | Select_type | Table | Type | Possible_keys | Key | Key_len | Ref | Rows | Extra |
+----+-------------+-------+-------+---------------+--------+---------+------+---------+-------------+
| 1 | Simple | Test1 | Index | NULL | I_name | 7 | NULL | 4187248 | Using Index |
+----+-------------+-------+-------+---------------+--------+---------+------+---------+-------------+
1 row in Set (0.00 sec)

Found that the type has changed and the key has changed. Extra is not the same. Look at the situation in comparison.

which

type=Const indicates that it was found by index one time;

key=Primary words, the use of the primary key;

Type=All, expressed as full table scan;

key=null indicates that the index is useless.
Type=ref, because this is considered to be more than one matching row, in a federated query, it is generally ref.

After analysis, we can determine which fields need to be indexed, which conditions can be optimized and so on. and number of bars, etc.



System > Const > EQ_REF > Ref > Fulltext > Ref_or_null > Index_merge > Unique_subquery > Index_sub Query > Range > Index > All

MySQL's explain