“This article has participated in the good article call order activity, click to see: back end, big front end double track submission, 20,000 yuan prize pool for you to challenge!” When we write a complex SQL, instead of blindly executing it, we choose to explain the SQL in advance, which is a good choice, to avoid not using the index or using the wrong index to cause mysql to scan a lot of tables, causing bad consequences on the line. Mysql provides the explain command to obtain the execution plan of the select statement. By explaining, we can know: the table read order, the data read operation type, which indexes can be used, which indexes are actually used, and so on.

mysql> explain select * from user; +----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+------------- + | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+------------- + | 1 | SIMPLE | user | NULL | index | NULL | name | 402 | NULL | 1 | | 100.00 Using index | +----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+------------- + 1 row in set, 1 warning (0.01sec)Copy the code

All of the following are based on mysql5.7.32

mysql> select version(); + -- -- -- -- -- -- -- -- -- -- -- + | version () | + -- -- -- -- -- -- -- -- -- -- -- + | 5.7.32 | + -- -- -- -- -- -- -- -- -- -- - + 1 row in the set (0.00 SEC)Copy the code

id

mysql> explain select * from user a left join user_info b on a.id=b.user_id where a.id=1;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | a     | NULL       | const | PRIMARY       | PRIMARY | 8       | const |    1 |   100.00 | NULL  |
|  1 | SIMPLE      | b     | NULL       | ref   | user_id       | user_id | 8       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
Copy the code

When the ids are the same, the execution starts from top to bottom

mysql> explain select * from user where id = (select user_id from user_info where age=10); +----+-------------+-----------+------------+-------+---------------+---------+---------+-------+------+----------+----- --------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra  | +----+-------------+-----------+------------+-------+---------------+---------+---------+-------+------+----------+----- -- -- -- -- -- -- -- -- + | | 1 PRIMARY | user | NULL | const | PRIMARY | PRIMARY 8 | | const | 1 | | NULL 100.00 | | 2 | SUBQUERY | User_info | NULL | | NULL ALL | NULL | NULL | NULL | 1 | | 100.00 Using the where | +----+-------------+-----------+------------+-------+---------------+---------+---------+-------+------+----------+----- --------+Copy the code

When the id is different, the larger the ID is, the faster the query is executed. For this subquery, the subSQL is preferentially executed before the main SQL is executed

mysql> explain select * from user union select * from user; +----+--------------+------------+------------+-------+---------------+------+---------+------+------+----------+------- ----------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+--------------+------------+------------+-------+---------------+------+---------+------+------+----------+------- -- -- -- -- -- -- -- -- -- -- + | 1 | PRIMARY | user | NULL | index | NULL | name | 402 | NULL | 1 | | 100.00 Using index | | 2 | UNION | The user | NULL | index | NULL | name | 402 | NULL | 1 | | 100.00 Using index | | NULL | UNION RESULT | < union1, 2 > | NULL | ALL | NULL | NULL | NULL | NULL | NULL | NULL | Using temporary | +----+--------------+------------+------------+-------+---------------+------+---------+------+------+----------+------- ----------+ 3 rows in set, 1 warning (0.00 SEC)Copy the code

For a UNION query, a temporary table is created with an ID of NULL

select_type

SIMPLE

Simple is a simple query. Mysql considers simple queries that do not contain subqueries and union queries to be simple. Even simple join queries are simple

mysql> explain select * from user; +----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+------------- + | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+------------- + | 1 | SIMPLE | user | NULL | index | NULL | name | 402 | NULL | 1 | | 100.00 Using index | +----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+------------- + 1 row in set, 1 Warning (0.00 SEC)Copy the code

mysql> explain select * from user a left join user_info b on a.id=b.user_id; +----+-------------+-------+------------+-------+---------------+---------+---------+-----------+------+----------+----- --------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra  | +----+-------------+-------+------------+-------+---------------+---------+---------+-----------+------+----------+----- -- -- -- -- -- -- -- -- + | | 1 SIMPLE | a | NULL | index | NULL | name | 402 | NULL | 1 | | 100.00 Using index | | 1 | SIMPLE | | b NULL | ref | user_id | user_id | | 8 test. Anderson d | 1 | | NULL | 100.00 +----+-------------+-------+------------+-------+---------------+---------+---------+-----------+------+----------+----- --------+ 2 rows in set, 1 warning (0.00 SEC)Copy the code

PRIMARY

If the query contains any complex subparts, the outermost query is marked as primary

mysql> explain select * from user where id = (select id from user_info where age=10); +----+-------------+-----------+------------+-------+---------------+---------+---------+-------+------+----------+----- --------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra  | +----+-------------+-----------+------------+-------+---------------+---------+---------+-------+------+----------+----- -- -- -- -- -- -- -- -- + | | 1 PRIMARY | user | NULL | const | PRIMARY | PRIMARY 8 | | const | 1 | | NULL 100.00 | | 2 | SUBQUERY | User_info | NULL | | NULL ALL | NULL | NULL | NULL | 1 | | 100.00 Using the where | +----+-------------+-----------+------------+-------+---------------+---------+---------+-------+------+----------+----- --------+ 2 rows in set, 1 warning (0.00 SEC)Copy the code

mysql> explain select * from user union all select * from user; +----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+------------- + | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+------------- + | 1 | PRIMARY | user | NULL | index | NULL | name | 402 | NULL | 1 | | 100.00 Using index | | 2 | UNION | user | NULL 402 | | index | NULL | name | NULL | 1 | | 100.00 Using index | +----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+------------- + 2 rows in set, 1 warning (0.00 SEC)Copy the code

SUBQUERY

Contains a subquery statement in the SELECT or WHERE list

mysql> explain select (select id from user) from user; +----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+------------- + | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+------------- + | 1 | PRIMARY | user | NULL | index | NULL | name | 402 | NULL | 1 | | 100.00 Using index | | 2 | SUBQUERY | user | NULL | index | NULL | name | 402 | NULL | 1 | | 100.00 Using index | +----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+------------- + 2 rows in set, 1 warning (0.00 SEC)Copy the code

mysql> explain select * from user where id= (select user_id from user_info where age=10); +----+-------------+-----------+------------+-------+---------------+---------+---------+-------+------+----------+----- --------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra  | +----+-------------+-----------+------------+-------+---------------+---------+---------+-------+------+----------+----- -- -- -- -- -- -- -- -- + | | 1 PRIMARY | user | NULL | const | PRIMARY | PRIMARY 8 | | const | 1 | | NULL 100.00 | | 2 | SUBQUERY | User_info | NULL | | NULL ALL | NULL | NULL | NULL | 1 | | 100.00 Using the where | +----+-------------+-----------+------------+-------+---------------+---------+---------+-------+------+----------+----- --------+ 2 rows in set, 1 warning (0.00 SEC)Copy the code

DERIVED

Select (subquery of from clause) from derived table

mysql> explain select * from (select * from user union select * from user) c; +----+--------------+------------+------------+-------+---------------+------+---------+------+------+----------+------- ----------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+--------------+------------+------------+-------+---------------+------+---------+------+------+----------+------- -- -- -- -- -- -- -- -- -- -- + | 1 | PRIMARY | < derived2 > | NULL | | NULL | NULL | NULL | NULL ALL 4 100.00 | | | NULL | | 2 | DERIVED | The user | NULL | index | NULL | name | 1023 | NULL | 1 | | 100.00 Using index | | 3 | UNION | user | NULL | index | NULL | The name | 1023 | NULL | 1 | | 100.00 Using index | | NULL | UNION RESULT | < union2, 3 > | NULL | | NULL ALL | NULL | NULL | NULL | NULL | NULL | Using temporary | +----+--------------+------------+------------+-------+---------------+------+---------+------+------+----------+------- ----------+ 4 rows in set, 1 warning (0.00 SEC)Copy the code

UNION

Select statement after union

mysql> explain select * from user union select * from user; +----+--------------+------------+------------+-------+---------------+------+---------+------+------+----------+------- ----------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+--------------+------------+------------+-------+---------------+------+---------+------+------+----------+------- -- -- -- -- -- -- -- -- -- -- + | 1 | PRIMARY | user | NULL | index | NULL | name | 1023 | NULL | 1 | | 100.00 Using index | | 2 | UNION | The user | NULL | index | NULL | name | 1023 | NULL | 1 | | 100.00 Using index | | NULL | UNION RESULT | < union1, 2 > | NULL |  ALL | NULL | NULL | NULL | NULL | NULL | NULL | Using temporary | +----+--------------+------------+------------+-------+---------------+------+---------+------+------+----------+------- ----------+ 3 rows in set, 1 warning (0.02sec)Copy the code

DEPENDENT UNION

Appears in a union or union ALL statement, but this query is affected by an external query

mysql> explain select * from user where id in(select id from user union select id from user); +----+--------------------+------------+------------+--------+---------------+---------+---------+------+------+-------- --+--------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+--------------------+------------+------------+--------+---------------+---------+---------+------+------+-------- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | | 1 PRIMARY | user | NULL | index | NULL | name | 1023 | NULL | 1 | | 100.00 Using where; Using the index | | 2 | DEPENDENT SUBQUERY | user | NULL | eq_ref | PRIMARY | PRIMARY 4 | | func | 1 | | 100.00 Using the index | | 3 | DEPENDENT UNION | user | NULL | eq_ref | PRIMARY | PRIMARY 4 | | func | 1 | | 100.00 Using index | | NULL | UNION RESULT | < union2, 3 > | NULL | | NULL ALL | NULL | NULL | NULL | NULL | NULL | Using temporary | +----+--------------------+------------+------------+--------+---------------+---------+---------+------+------+-------- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + 4 rows in the set, 1 warning (0.11 SEC)Copy the code

DEPENDENT SUBQUERY

Similar to DEPENDENT UNION, it contains subqueries and is influenced by external queries

mysql> explain select * from user where id in(select id from user union select id from user); +----+--------------------+------------+------------+--------+---------------+---------+---------+------+------+-------- --+--------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+--------------------+------------+------------+--------+---------------+---------+---------+------+------+-------- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | | 1 PRIMARY | user | NULL | index | NULL | name | 1023 | NULL | 1 | | 100.00 Using where; Using the index | | 2 | DEPENDENT SUBQUERY | user | NULL | eq_ref | PRIMARY | PRIMARY 4 | | func | 1 | | 100.00 Using the index | | 3 | DEPENDENT UNION | user | NULL | eq_ref | PRIMARY | PRIMARY 4 | | func | 1 | | 100.00 Using index | | NULL | UNION RESULT | < union2, 3 > | NULL | | NULL ALL | NULL | NULL | NULL | NULL | NULL | Using temporary | +----+--------------------+------------+------------+--------+---------------+---------+---------+------+------+-------- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + 4 rows in the set, 1 warning (0.11 SEC)Copy the code

UNION RESULT

Appears in union or Union ALL and represents a result set

mysql> explain select id from user union select id from user; +----+--------------+------------+------------+-------+---------------+------+---------+------+------+----------+------- ----------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+--------------+------------+------------+-------+---------------+------+---------+------+------+----------+------- -- -- -- -- -- -- -- -- -- -- + | 1 | PRIMARY | user | NULL | index | NULL | name | 1023 | NULL | 1 | | 100.00 Using index | | 2 | UNION | The user | NULL | index | NULL | name | 1023 | NULL | 1 | | 100.00 Using index | | NULL | UNION RESULT | < union1, 2 > | NULL |  ALL | NULL | NULL | NULL | NULL | NULL | NULL | Using temporary | +----+--------------+------------+------------+-------+---------------+------+---------+------+------+----------+------- ----------+ 3 rows in set, 1 warning (0.00 SEC)Copy the code

table

Query table name, sometimes not the real table name displayed

mysql> explain select * from (select * from user union select * from user) c; +----+--------------+------------+------------+-------+---------------+------+---------+------+------+----------+------- ----------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+--------------+------------+------------+-------+---------------+------+---------+------+------+----------+------- -- -- -- -- -- -- -- -- -- -- + | 1 | PRIMARY | < derived2 > | NULL | | NULL | NULL | NULL | NULL ALL 4 100.00 | | | NULL | | 2 | DERIVED | The user | NULL | index | NULL | name | 1023 | NULL | 1 | | 100.00 Using index | | 3 | UNION | user | NULL | index | NULL | The name | 1023 | NULL | 1 | | 100.00 Using index | | NULL | UNION RESULT | < union2, 3 > | NULL | | NULL ALL | NULL | NULL | NULL | NULL | NULL | Using temporary | +----+--------------+------------+------------+-------+---------------+------+---------+------+------+----------+------- ----------+ 4 rows in set, 1 warning (0.00 SEC)Copy the code

Derived [num]; derived[num]; derived[num];

partitions

Query the partition that matches the record. For non-partitioned tables, the value is NULL. Create a partition table first

CREATE TABLE users (
     id INT NOT NULL ,
     name varchar(100) NOT NULL 
)
PARTITION BY RANGE (id) (
     PARTITION p0 VALUES LESS THAN (10),
     PARTITION p1 VALUES LESS THAN (20),
     PARTITION p2 VALUES LESS THAN (30),
     PARTITION p3 VALUES LESS THAN MAXVALUE
);
Copy the code

Four partitions are divided by id, and two data are inserted

mysql> select * from users; + - + -- -- -- -- -- -- -- + | | id name | + - + -- -- -- -- -- -- -- + | 1 | Tom | | | 11 jerry | + - + -- -- -- -- -- -- -- + 2 rows in the set (0.00 SEC)Copy the code

mysql> explain select * from users where id=1; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+  | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ | | 1 SIMPLE | users | p0 | | NULL ALL | NULL | NULL | NULL | 1 | | 100.00 Using the where | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ 1 Row in set, 1 Warning (0.00 SEC)Copy the code

Id =1 is distributed on partition P0

mysql> explain select * from users where id=11; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+  | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ | | 1 SIMPLE | users | | | NULL ALL p1 | NULL | NULL | NULL | 1 | | 100.00 Using the where | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ 1 Row in set, 1 Warning (0.00 SEC)Copy the code

Id =11 distributed in partition P1

type

Type is a very important indicator. It indicates what type we use to look up data. Here are the types from good to bad

system

This type does not usually occur. The official explanation:

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

A table has only one row of records and is a special const type

const

The table has at most one matching row, and because there is only one row, the rest of the optimizer can treat the values of the columns in that row as constants. Const tables are fast because they are read only once.

mysql> explain select * from user where id=1; +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+ | | 1 SIMPLE | user | NULL | const | PRIMARY | PRIMARY 4 | | const | 1 | | NULL | 100.00 +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+ 1 row in set, 1 warning (0.01sec)Copy the code

Const is used only when a primary key or unique index is used.

mysql> explain select * from user where name="Tom"; +----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+------------- + | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+------------- + | 1 | SIMPLE | user | NULL | ref | name | name | 1023 | const | 1 | | 100.00 Using index | +----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+------------- + 1 row in set, 1 warning (0.01sec)Copy the code

Name is a common index, so it is not const

eq_ref

It’s the best except for system and const. It is typically used in join queries where the join condition is a primary key index or a unique index.

mysql> explain select * from user a left join user_info b on a.id=b.user_id; +----+-------------+-------+------------+--------+---------------+---------+---------+-----------+------+----------+---- ---------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+--------+---------------+---------+---------+-----------+------+----------+---- -- -- -- -- -- -- -- -- -- + | | 1 SIMPLE | a | NULL | index | NULL | name | 1023 | NULL | 1 | | 100.00 Using index | | 1 | SIMPLE | | b NULL | eq_ref | user_id | user_id | | 4 test, Anderson d | 1 | | NULL | 100.00 +----+-------------+-------+------------+--------+---------------+---------+---------+-----------+------+----------+---- ---------+ 2 rows in set, 1 warning (0.00 SEC)Copy the code

Table A is associated with table B. Table B is associated with table A by id and user_id. Since ID is a primary key and user_id is a unique index, each record in table A is associated with only one record in table B. So the type of table B is eq_ref.

ref

Compared with eQ_REF, the difference is that the fields of the associated table query are not unique or primary key index, or they are matched, and the matching will continue. There may be multiple records.

mysql> explain select b.* from user a left join user_info b on a.id=b.user_id; +----+-------------+-------+------------+-------+---------------+---------+---------+-----------+------+----------+----- --------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra  | +----+-------------+-------+------------+-------+---------------+---------+---------+-----------+------+----------+----- -- -- -- -- -- -- -- -- + | | 1 SIMPLE | a | NULL | index | NULL | name | 402 | NULL | 1 | | 100.00 Using index | | 1 | SIMPLE | | b NULL | ref | user_id | user_id | | 8 test. Anderson d | 1 | | NULL | 100.00 +----+-------------+-------+------------+-------+---------------+---------+---------+-----------+------+----------+----- --------+ 2 rows in set, 1 warning (0.01sec)Copy the code

Id is a primary key. Uid is a common index key. A may be associated with multiple records of B, but at least one index exists.

fulltext

Innodb does not support full-text indexing, but with the advent of various search engines, where full-text indexing is needed, it is common to use a store like ES which is good at word segmentation.

ref_or_null

This type is similar to ref, but MySQL searches extra rows that contain null values.

mysql> explain select * from user_info where user_id is null or user_id=1; +----+-------------+-----------+------------+-------------+---------------+---------+---------+-------+------+---------- +-----------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------+------------+-------------+---------------+---------+---------+-------+------+---------- + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | | 1 SIMPLE | user_info | NULL | ref_or_null | user_id | user_id | | 9 const | 108 | | 100.00  Using index condition | +----+-------------+-----------+------------+-------------+---------------+---------+---------+-------+------+---------- + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + 1 row in the set, 1 warning (0.00 SEC)Copy the code

User_id is a common index key, and null is allowed.

index_merge

The index combined

mysql> explain select * from user_info where id=1 or user_id=999; +----+-------------+-----------+------------+-------------+-----------------+-----------------+---------+------+------+- ---------+-------------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------+------------+-------------+-----------------+-----------------+---------+------+------+- ---------+-------------------------------------------+ | 1 | SIMPLE | user_info | NULL | index_merge | PRIMARY,user_id | PRIMARY user_id, 8, 9 | | NULL | 2 | | 100.00 Using the union (PRIMARY, user_id); Using where | +----+-------------+-----------+------------+-------------+-----------------+-----------------+---------+------+------+- + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + 1 row in the set, 1 warning (0.00 SEC)Copy the code

User_id = primary key; user_id = primary key; user_id = primary key

unique_subquery

Used for in subqueries in where. Subqueries return unique values that are not repeated and can completely replace subqueries with higher efficiency. This type replaces the ref of the IN subquery of the following form.

explain select * from user_info where user_id in (select id from user where id>10);
Copy the code

The id of the subquery is primary key or UNIQUE key.

index_subquery

Similar to the unique_subquery subquery, but the subquery returns a non-unique index.

explain select * from user_info where user_id in (select id from user where id>10);
Copy the code

The id of the subquery is not primary key or unique key.

range

Index range scan. Queries using >, <, between, in, etc.

mysql> explain select * from user where id>=1 and id <=100; +----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+---------- ---+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+---------- -- -- -- + | | 1 SIMPLE | user | NULL | range | PRIMARY | PRIMARY 8 | NULL | | 100 | | 100.00 Using the where | +----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+---------- --+ 1 Row in set, 1 Warning (0.00 SEC)Copy the code

index

Similar to a full table sweep, but only the index tree is scanned, which is usually faster than a full table sweep because the index file is usually smaller than the data file.

mysql> explain select user_id from user_info; +----+-------------+-----------+------------+-------+---------------+---------+---------+------+--------+----------+---- ---------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------+------------+-------+---------------+---------+---------+------+--------+----------+---- -- -- -- -- -- -- -- -- -- + | | 1 SIMPLE | user_info | NULL | index | NULL | user_id 9 | NULL | | 100100 | | 100.00 Using index | +----+-------------+-----------+------------+-------+---------------+---------+---------+------+--------+----------+---- ---------+ 1 row in set, 1 Warning (0.00 SEC)Copy the code

ALL

A full table scan

mysql> explain select * from user_info where age=1; +----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+-------- -----+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+-------- -- -- -- -- -- + | | 1 SIMPLE | user_info | NULL | | NULL ALL | NULL | NULL | NULL | 100100 | | 10.00 Using the where | +----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+-------- -----+ 1 row in set, 1 Warning (0.00 SEC)Copy the code

Age has no index, the engine layer does a full table scan.

possible_keys

Query the indexes that may be used.

mysql> explain select * from user_info where id>1 and user_id >2; +----+-------------+-----------+------------+-------+-----------------+---------+---------+------+-------+----------+--- ----------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------+------------+-------+-----------------+---------+---------+------+-------+----------+--- -- -- -- -- -- -- -- -- -- -- + | | 1 SIMPLE | user_info | NULL | range | PRIMARY, user_id | PRIMARY 8 | NULL | | 50050 | | 50.00 Using the where  | +----+-------------+-----------+------------+-------+-----------------+---------+---------+------+-------+----------+--- ----------+ 1 row in set, 1 Warning (0.00 SEC)Copy the code

Both ID and user_id have indexes

key

Index of the final selection of the actuator

mysql> explain select * from user_info where id>1 and user_id >2; +----+-------------+-----------+------------+-------+-----------------+---------+---------+------+-------+----------+--- ----------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------+------------+-------+-----------------+---------+---------+------+-------+----------+--- -- -- -- -- -- -- -- -- -- -- + | | 1 SIMPLE | user_info | NULL | range | PRIMARY, user_id | PRIMARY 8 | NULL | | 50050 | | 50.00 Using the where  | +----+-------------+-----------+------------+-------+-----------------+---------+---------+------+-------+----------+--- ----------+ 1 row in set, 1 Warning (0.00 SEC)Copy the code

The primary key index is selected here.

key_len

The length of the index to use, which can be quite complicated, especially for composite indexes. Let’s say there’s a table like this

CREATE TABLE `testlen` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name1` varchar(10) not null default "",
  `name2` varchar(10),
  `num1` int(10) not null default 0,
  `num2` int(10),
   PRIMARY KEY (`id`),
   key(`name1`),
   key(`name2`),
   key(`num1`),
   key(`num2`)
   
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
Copy the code

When a field is of fixed length, such as char, int, etc., a byte is required to mark whether it is null. Not NULL is not required.

mysql> explain select * from testlen where num1=1; +----+-------------+---------+------------+------+---------------+------+---------+-------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------+------------+------+---------------+------+---------+-------+------+----------+-------+ | 1 | SIMPLE | testlen | NULL | ref | num1 | num1 | | 4 const | 1 | | NULL | 100.00 + - + -- -- -- -- -- -- -- -- -- -- -- -- -- + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- - + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- + 1 Row in set, 1 warning (0.03sec)Copy the code

mysql> explain select * from testlen where num2=1; +----+-------------+---------+------------+------+---------------+------+---------+-------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------+------------+------+---------------+------+---------+-------+------+----------+-------+ | 1 | SIMPLE | testlen | NULL | ref | num2 | num2 | | 5 const | 1 | | NULL | 100.00 + - + -- -- -- -- -- -- -- -- -- -- -- -- -- + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- - + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- + 1 Row in set, 1 Warning (0.00 SEC)Copy the code

When fields are variable-length, such as vARCHar, two additional bytes are required in addition to whether one byte is required to mark non-NOT NULL

3. For char, varchar, etc., utF8 encoding a character takes 3 bytes, UTF8MB4 encoding a character takes 4 bytes

mysql> explain select * from testlen where name1='1'; +----+-------------+---------+------------+------+---------------+-------+---------+-------+------+----------+-------+ |  id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------+------------+------+---------------+-------+---------+-------+------+----------+-------+ | 1 | SIMPLE | testlen | NULL | ref | name1 | name1 | | 42 const | 1 | | NULL | 100.00 + - + -- -- -- -- -- -- -- -- -- -- -- -- -- + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- - + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- + 1 Row in set, 1 warning (0.01sec)Copy the code

For UTF8MB4 encoding, not null varchar(10) final length =10*4+2 = 42

mysql> explain select * from testlen where name2='1'; +----+-------------+---------+------------+------+---------------+-------+---------+-------+------+----------+-------+ |  id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------+------------+------+---------------+-------+---------+-------+------+----------+-------+ | 1 | SIMPLE | testlen | NULL | ref | name2 | name2 43 | | const | 1 | | NULL | 100.00 + - + -- -- -- -- -- -- -- -- -- -- -- -- -- + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- - + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- + 1 Row in set, 1 Warning (0.00 SEC)Copy the code

For UTF8MB4 encoding, null varchar(10) is allowed. The final length =10*4+2+1 = 43.

ref

This column shows the columns or constants used for table lookup values in the index of the key column record.

mysql> explain select * from user where id=1; +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+ | | 1 SIMPLE | user | NULL | const | PRIMARY | PRIMARY 4 | | const | 1 | | NULL | 100.00 +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+ 1 row in set, 1 warning (0.01sec)Copy the code

Where id=1 and ref =1

mysql> explain select * from user a left join user_info b on a.id=b.user_id; +----+-------------+-------+------------+--------+---------------+---------+---------+-----------+------+----------+---- ---------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+--------+---------------+---------+---------+-----------+------+----------+---- -- -- -- -- -- -- -- -- -- + | | 1 SIMPLE | a | NULL | index | NULL | name | 1023 | NULL | 1 | | 100.00 Using index | | 1 | SIMPLE | | b NULL | eq_ref | user_id | user_id | | 4 test, Anderson d | 1 | | NULL | 100.00 +----+-------------+-------+------------+--------+---------------+---------+---------+-----------+------+----------+---- ---------+ 2 rows in set, 1 warning (0.02sec)Copy the code

Ref =test.a.id (test is the name of the database)

rows

The number of rows scanned, this is an estimate, not the actual result set

filtered

Filtered represents the number of rows returned as a percentage of the number of rows to read. The value of filtered column depends on the statistics.

extra

Some additional information on how to find the data.

using index

This occurs when an overwrite index is used and only the values of the index columns are looked up.

mysql> explain select user_id from user_info; +----+-------------+-----------+------------+-------+---------------+---------+---------+------+------+----------+------ -------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------+------------+-------+---------------+---------+---------+------+------+----------+------ -- -- -- -- -- -- -- + | | 1 SIMPLE | user_info | NULL | index | NULL | user_id | | NULL | 2 | | 100.00 4 Using the index | +----+-------------+-----------+------------+-------+---------------+---------+---------+------+------+----------+------ -------+ 1 row in set, 1 Warning (0.00 SEC)Copy the code

using where

The MYSQL server layer will apply the WHERE filter after the rows are returned by the storage engine layer. This occurs when an index scan cannot be performed or when an index scan is performed, but some query columns are not in the index.

mysql> explain select * from user_info where user_id>10; +----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+-------- -----+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+-------- -- -- -- -- -- + | | 1 SIMPLE | user_info | NULL | | ALL user_id | NULL | NULL | NULL | 100100 | | 50.00 Using the where | +----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+-------- -----+ 1 row in set, 1 warning (0.01sec)Copy the code

Using temporary

Temporary tables may be used for some order BY, group BY.

mysql> explain select * from user a left join user_info b on a.id=b.user_id where a.id>=1 order by b.user_id; +----+-------------+-------+------------+-------+---------------+---------+---------+-----------+------+----------+----- -----------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+---------+---------+-----------+------+----------+----- -----------------------------------------+ | 1 | SIMPLE | a | NULL | range | PRIMARY | PRIMARY | 8 | NULL | 5187 | 100.00 | Using the where; Using temporary; Using filesort | | | SIMPLE | b | NULL | 1 ref | user_id | user_id | | 9 test. Anderson d | 1 | | 100.00 Using the where | +----+-------------+-------+------------+-------+---------------+---------+---------+-----------+------+----------+----- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + 2 rows in the set, 1 warning (0.01 SEC)Copy the code

Using filesort

Order by: order by: order by: order by

mysql> explain select * from user_info order by age desc; +----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+-------- --------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra  | +----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+-------- -- -- -- -- -- -- -- -- + | | 1 SIMPLE | user_info | NULL | | NULL ALL | NULL | NULL | NULL | 100100 | | 100.00 Using filesort | +----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+-------- --------+Copy the code

Using index condition

Using INDEX condition using index condition using index condition using index condition using index condition using index condition

mysql> explain select * from user a left join user_info b on a.id=b.user_id where b.user_id>1; +----+-------------+-------+------------+-------+---------------+---------+---------+-----------+-------+----------+---- -------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+---------+---------+-----------+-------+----------+---- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | | 1 SIMPLE | a | NULL | index | PRIMARY | name | 402 | NULL | 10375 | | 100.00 Using index | | 1 | SIMPLE | b | NULL | ref | user_id | user_id | | 9 test. Anderson d | 1 | | 100.00 Using index condition | +----+-------------+-------+------------+-------+---------------+---------+---------+-----------+-------+----------+---- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + 2 rows in the set, 1 warning (0.00 SEC)Copy the code

mo4tech.com (Moment For Technology) is a global community with thousands techies from across the global hang out!Passionate technologists, be it gadget freaks, tech enthusiasts, coders, technopreneurs, or CIOs, you would find them all here.

Mysql explain explanation

id

select_type

SIMPLE

PRIMARY

SUBQUERY

DERIVED

UNION

DEPENDENT UNION

DEPENDENT SUBQUERY

UNION RESULT

table

partitions

type

system

const

eq_ref

ref

fulltext

ref_or_null

index_merge

unique_subquery

index_subquery

range

index

ALL

possible_keys

key

key_len

ref

rows

filtered

extra

using index

using where

Using temporary

Using filesort

Using index condition

Mysql explain explanation

id

select_type

SIMPLE

PRIMARY

SUBQUERY

DERIVED

UNION

DEPENDENT UNION

DEPENDENT SUBQUERY

UNION RESULT

table

partitions

type

system

const

eq_ref

ref

fulltext

ref_or_null

index_merge

unique_subquery

index_subquery

range

index

ALL

possible_keys

key

key_len

ref

rows

filtered

extra

using index

using where

Using temporary

Using filesort

Using index condition

Related Posts

Generate template code with Velocity

10-minute Short link service for Node + Express + MongoDB

A few minutes to understand the principles of Base64 coding