MySQL EXPLAIN MySQL EXPLAIN MySQL EXPLAIN

Updates continue at….

1. Introduction

SQL file used for this test:

Link: pan.baidu.com/s/1i11Ce2fx… Extraction code: Java

EXPLAIN statements provide information about how MySQL executes statements. EXPLAIN is used with SELECT, DELETE, INSERT, REPLACE, and UPDATE statements.

mysql> EXPLAIN SELECT * FROM employees WHERE emp_no = 10001;
+----+-------------+-----------+------------+-------+---------------+---------+---------+-------+------+----------+------ -+
| id | select_type | table     | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-----------+------------+-------+---------------+---------+---------+-------+------+----------+------ -+| | 1 SIMPLE | employees | NULL | const | PRIMARY | PRIMARY 4 | | const | | | NULL | 100.00 + 1----+-------------+-----------+------------+-------+---------------+---------+---------+-------+------+----------+------ -+
1 row in set.1 warning (0.00 sec)
Copy the code

In a nutshell, EXPLAIN can be used to analyze whether the SQL statement went to the index, which index. EXPLAIN returns a row of information for each table used in the SELECT statement, which lists the tables in the output in the order in which MySQL read them when processing the statement. MySQL uses nested-loop Join Algorithms to parse all connections, which means MySQL reads a row from the first table, then finds a matching row in the second table, then a matching row in the third table, and so on. After all the tables are processed, MySQL outputs the selected columns through the table list and backtracks until it finds a table with more matching rows. Read the next row from the table and proceed to the next table.

2. The EXPLAIN output columns

MySQL version 5.7.33
Windows 10 64

Table header ID, select_TYPE, table, PARTITIONS, type, possible_keys, key, key_len, ref, rows, filtered, Extra, We’re going to learn what these fields mean and then we’re going to look at them by example.

2.1 id

SELECT identifier, the sequence number of the SELECT in the query. This value can be NULL if this row references the union result of other rows. In this case, the table column displays a value like

to indicate that the row references the union of rows with ID values M and N.
,n>

The ID value can be divided into three cases:

The ids are the same and the execution sequence is from top to bottom

mysql> EXPLAIN (
    -> SELECT * FROM employees emp
    -> LEFT JOIN dept_emp de ON emp.emp_no = de.emp_no
    -> LEFT JOIN departments dept ON dept.dept_no = de.dept_no
    -> WHERE emp.emp_no = 10001);
+----+-------------+-------+------------+--------+---------------+---------+---------+----------------------+------+----- -----+-------+
| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref                  | rows | filtered | Extra |
+----+-------------+-------+------------+--------+---------------+---------+---------+----------------------+------+----- -----+-------+| | 1 SIMPLE | emp | NULL | const | PRIMARY | PRIMARY 4 | | const | 1 | | NULL 100.00 | | 1 | SIMPLE | NULL | | DE ref | PRIMARY | PRIMARY 4 | | const | 1 | | NULL 100.00 | | 1 | SIMPLE | dept | NULL | eq_ref | PRIMARY | PRIMARY 12 | | Employees. DE. Dept_no | | | NULL | 100.00 + 1----+-------------+-------+------------+--------+---------------+---------+---------+----------------------+------+----- -----+-------+
3 rows in set.1 warning (0.03 sec)

Copy the code

The IDS are different. In the case of sub-query, the ID sequence increases. The greater the ID value, the higher the execution priority

mysql> EXPLAIN SELECT * FROM employees emp
    -> WHERE emp.emp_no NOT IN ( SELECT de.emp_no FROM dept_emp de 
    -> WHERE de.dept_no NOT IN ( SELECT dept_no FROM departments WHERE dept_name = 'Development'));
+----+-------------+-------------+------------+-------+-------------------+-----------+---------+-------+--------+------- ---+--------------------------+
| id | select_type | table       | partitions | type  | possible_keys     | key       | key_len | ref   | rows   | filtered | Extra                    |
+----+-------------+-------------+------------+-------+-------------------+-----------+---------+-------+--------+------- ---+--------------------------+| | 1 PRIMARY emp | | NULL | | NULL ALL | NULL | NULL | NULL | 299468 | | 100.00 Using the where | | 2 | SUBQUERY | | DE NULL | index | PRIMARY | dept_no 12 | NULL | | 308493 | | 100.00 Using the where; Using the index | | 3 | SUBQUERY | the departments | NULL | const | PRIMARY, dept_name | dept_name | 122 | const | 1 | | 100.00 Using index | +----+-------------+-------------+------------+-------+-------------------+-----------+---------+-------+--------+------- ---+--------------------------+
3 rows in set.1 warning (0.00 sec)

Copy the code

Id identical and id different exist

If the ids in the same group are the same, they are considered to be a group. The execution sequence for the same group of ids is from top to bottom. Among different groups, the higher the ID value is, the higher the execution priority is.

mysql> EXPLAIN SELECT * FROM employees emp
    -> WHERE emp.emp_no IN ( SELECT de.emp_no FROM dept_emp de 
    -> WHERE de.dept_no IN ( SELECT dept_no FROM departments WHERE dept_name LIKE '%Develop%'));
+----+--------------+-------------+------------+-------+-----------------+-----------+---------+------------------------- ------+--------+----------+----------------------------------------------------+
| id | select_type  | table       | partitions | type  | possible_keys   | key       | key_len | ref                           | rows   | filtered | Extra                                              |
+----+--------------+-------------+------------+-------+-----------------+-----------+---------+------------------------- ------+--------+----------+----------------------------------------------------+| | 1 SIMPLE | < subquery2 > | NULL | | NULL ALL | NULL | NULL | NULL | NULL 100.00 | NULL | | | | SIMPLE | emp | NULL | 1 ALL | PRIMARY | NULL | NULL | NULL | 299468 | | 0.00 Using the where; Using join buffer (Block Nested Loop) | | 2 | MATERIALIZED | departments | NULL | index | PRIMARY | dept_name | 122 | NULL | | | 11.11 9 Using the where; Using index | | 2 | MATERIALIZED | de | NULL | ref | PRIMARY,dept_no | dept_no | 12 | employees.departments.dept_no | 38561 | | 100.00 Using index | +----+--------------+-------------+------------+-------+-----------------+-----------+---------+------------------------- ------+--------+----------+----------------------------------------------------+
4 rows in set.1 warning (0.01 sec)

Copy the code

2.2 select_type

The type of query is mainly used to distinguish ordinary query, joint query, sub-query and other complex queries. Including SIMPLE, PRIMARY, UNION, DEPENDENT UNION, UNION RESULT, SUBQUERY, DEPENDENT SUBQUERY, DERIVED, MATERIALIZED, UNCACHEABLE SUBQUERY, UNCACHEABLE UNION

SIMPLE

Simple SELECT, without using UNION or subqueries.

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

PRIMARY

The outermost query is marked as PRIMARY if it contains any complex subparts

mysql> EXPLAIN SELECT * FROM employees emp
    -> WHERE emp.emp_no IN ( SELECT max(emp_no) FROM dept_emp);
+----+--------------------+-------+------------+------+---------------+------+---------+------+--------+----------+------ ------------------------+
| id | select_type        | table | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra                        |
+----+--------------------+-------+------------+------+---------------+------+---------+------+--------+----------+------ ------------------------+| | 1 PRIMARY emp | | NULL | | NULL ALL | NULL | NULL | NULL | 299468 | | 100.00 Using the where | | 2 | DEPENDENT SUBQUERY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |Select tables optimized away |
+----+--------------------+-------+------------+------+---------------+------+---------+------+--------+----------+------ ------------------------+
2 rows in set.1 warning (0.00 sec)
Copy the code

UNION

The second or later SELECT statement that appears after the UNION is marked as a UNION

mysql> EXPLAIN (SELECT emp_no,dept_no FROM dept_emp LIMIT 10) - >UNION
    -> SELECT emp_no,dept_no FROM dept_manager;
+----+--------------+--------------+------------+-------+---------------+---------+---------+------+--------+----------+- ----------------+
| id | select_type  | table        | partitions | type  | possible_keys | key     | key_len | ref  | rows   | filtered | Extra           |
+----+--------------+--------------+------------+-------+---------------+---------+---------+------+--------+----------+- ----------------+| | 1 PRIMARY | dept_emp | NULL | index | NULL | dept_no 12 | NULL | | 308493 | | 100.00 Using index | | 2 | UNION | Dept_manager | NULL | index | NULL | dept_no 12 | | NULL 24 | | | 100.00 Using index | | NULL | UNION RESULT | < union1, 2 > | NULL | | NULL ALL | NULL | NULL | NULL | NULL | NULL | Using temporary | +----+--------------+--------------+------------+-------+---------------+---------+---------+------+--------+----------+- ----------------+
3 rows in set.1 warning (0.00 sec)
Copy the code

DEPENDENT UNION

Like UNION, it occurs in a UNION or UNION ALL statement, but this query is affected by an external query

mysql> EXPLAIN SELECT * FROM employees
    -> WHERE emp_no IN (SELECT 10001 UNION SELECT 10002);
+----+--------------------+------------+------------+------+---------------+------+---------+------+--------+----------+- ----------------+
| id | select_type        | table      | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra           |
+----+--------------------+------------+------------+------+---------------+------+---------+------+--------+----------+- ----------------+| | 1 PRIMARY | employees | NULL | | NULL ALL | NULL | NULL | NULL | 299468 | | 100.00 Using the where | | 2 | DEPENDENT SUBQUERY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used | | 3 | DEPENDENT UNION | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables 2 | | NULL | UNION RESULT | < union2, 3 > | NULL | ALL | NULL | NULL | NULL | NULL | NULL | NULL | Using temporary | +----+--------------------+------------+------------+------+---------------+------+---------+------+--------+----------+- ----------------+
4 rows in set.1 warning (0.00 sec)

Copy the code

SUBQUERY

The first SELECT in the subquery, depending on the external query, occurs in a complex unrelated subquery

Examples are as follows:

mysql> EXPLAIN SELECT * FROM employees emp
    -> WHERE emp.emp_no IN (SELECT emp_no FROM dept_manager);
+----+-------------+--------------+------------+--------+---------------+---------+---------+---------------------------- ---+------+----------+------------------------+
| id | select_type | table        | partitions | type   | possible_keys | key     | key_len | ref                           | rows | filtered | Extra                  |
+----+-------------+--------------+------------+--------+---------------+---------+---------+---------------------------- ---+------+----------+------------------------+| | 1 SIMPLE | dept_manager | NULL | index | PRIMARY | PRIMARY 16 | NULL 24 | | | | 100.00 Using index; LooseScan | | | 1 SIMPLE | emp | NULL | eq_ref | PRIMARY | PRIMARY | | 4. Employees dept_manager. Emp_no | 1 | | 100.00 NULL | +----+-------------+--------------+------------+--------+---------------+---------+---------+---------------------------- ---+------+----------+------------------------+
2 rows in set.1 warning (0.00 sec)

Copy the code

MySQL > alter table select_type SUBQUERY (select * from select_type); MySQL > select SUBQUERY (select * from select_type); MySQL > select SUBQUERY (select * from select_type); The optimizer rewrites this as a correlation subquery. Consider the following statements using unrelated subqueries:

SELECT.FROM t1 WHERE t1.a IN (SELECT b FROM t2); 
Copy the code

It is probably like the following, the probability is, there are big guys also please give advice:

mysql> EXPLAIN SELECT /*+ SEMIJOIN(@subq1 LOOSESCAN) */ * FROM employees emp
    -> WHERE emp.emp_no IN (SELECT /*+ QB_NAME(subq1) */ emp_no FROM dept_manager);
+----+-------------+--------------+------------+--------+---------------+---------+---------+---------------------------- ---+------+----------+------------------------+
| id | select_type | table        | partitions | type   | possible_keys | key     | key_len | ref                           | rows | filtered | Extra                  |
+----+-------------+--------------+------------+--------+---------------+---------+---------+---------------------------- ---+------+----------+------------------------+| | 1 SIMPLE | dept_manager | NULL | index | PRIMARY | PRIMARY 16 | NULL 24 | | | | 100.00 Using index; LooseScan | | | 1 SIMPLE | emp | NULL | eq_ref | PRIMARY | PRIMARY | | 4. Employees dept_manager. Emp_no | 1 | | 100.00 NULL | +----+-------------+--------------+------------+--------+---------------+---------+---------+---------------------------- ---+------+----------+------------------------+
2 rows in set.1 warning (0.00 sec)

Copy the code

Back to select_type, SUBQUERY

mysql> EXPLAIN SELECT * FROM employees emp
    -> WHERE emp.emp_no IN (SELECT /*+ SUBQUERY(MATERIALIZATION) */ emp_no FROM dept_manager);
+----+-------------+--------------+------------+-------+---------------+---------+---------+------+--------+----------+-- -----------+
| id | select_type | table        | partitions | type  | possible_keys | key     | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+--------------+------------+-------+---------------+---------+---------+------+--------+----------+-- -----------+| | 1 PRIMARY emp | | NULL | | NULL ALL | NULL | NULL | NULL | 299468 | | 100.00 Using the where | | 2 | SUBQUERY | Dept_manager | NULL | index | PRIMARY | dept_no 12 | | NULL 24 | | | 100.00 Using index | +----+-------------+--------------+------------+-------+---------------+---------+---------+------+--------+----------+-- -----------+
2 rows in set.1 warning (0.00 sec)

Copy the code

Annotations output effect, refer to the official documentation: dev.mysql.com/doc/refman/…

DEPENDENT SUBQUERY

If a subquery uses any references/references from an external query, it is called a dependent/related subquery.

mysql> EXPLAIN SELECT emp.emp_no,emp.first_name FROM employees emp
    -> WHERE emp_no NOT IN (SELECT MAX(dm.emp_no) FROM dept_emp dm);
+----+--------------------+-------+------------+------+---------------+------+---------+------+--------+----------+------ ------------------------+
| id | select_type        | table | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra                        |
+----+--------------------+-------+------------+------+---------------+------+---------+------+--------+----------+------ ------------------------+| | 1 PRIMARY emp | | NULL | | NULL ALL | NULL | NULL | NULL | 299468 | | 100.00 Using the where | | 2 | DEPENDENT SUBQUERY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |Select tables optimized away |
+----+--------------------+-------+------------+------+---------------+------+---------+------+--------+----------+------ ------------------------+
2 rows in set.1 warning (0.01 sec)
Copy the code

DERIVED

This keyword is displayed when the query uses an inline view in a derived table

mysql> EXPLAIN SELECT foo.first_name FROM- > (- >SELECT 1 AS first_name
    -> UNION
    -> SELECT 2 AS first_name
    -> ) AS foo;
+----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+--------- --------+
| id | select_type  | table      | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra           |
+----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+--------- --------+| 1 | PRIMARY | < derived2 > | NULL | | NULL ALL | NULL | NULL | NULL | 2 | | NULL 100.00 | | 2 | DERIVED | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used | | 3 | UNION | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables 2 | | 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

MATERIALIZED

Materialized view of subqueries, equivalent to caching

mysql> EXPLAIN SELECT  * FROM employees emp
    -> WHERE emp.emp_no IN ( SELECT /*+ NO_RANGE_OPTIMIZATION(dept_emp PRIMARY) */ emp_no FROM dept_emp);
+----+--------------+-------------+------------+--------+---------------+------------+---------+----------------------+-- ------+----------+-------------+
| id | select_type  | table       | partitions | type   | possible_keys | key        | key_len | ref                  | rows   | filtered | Extra       |
+----+--------------+-------------+------------+--------+---------------+------------+---------+----------------------+-- ------+----------+-------------+| | 1 SIMPLE | emp | NULL | | ALL PRIMARY | NULL | NULL | NULL | 299468 | | 100.00 Using the where | | SIMPLE | | 1 < subquery2 > | NULL | eq_ref | < auto_key > | < auto_key > | | 4 employees. The emp. The emp_no | 1 | | NULL 100.00 | | | 2 MATERIALIZED | dept_emp | NULL | index | PRIMARY | dept_no 12 | NULL | | 308493 | | 100.00 Using index | +----+--------------+-------------+------------+--------+---------------+------------+---------+----------------------+-- ------+----------+-------------+
3 rows in set.1 warning (0.00 sec)

Copy the code

Tips: the MySQL optimizer is really powerful

In certain cases, with the help of the MySQL optimizer, double-ended fuzzy queries go indexed.

mysql> EXPLAIN SELECT emp_no,dept_no FROM dept_emp WHERE dept_no LIKE '%d00%';
+----+-------------+----------+------------+-------+---------------+---------+---------+------+--------+----------+------ --------------------+
| id | select_type | table    | partitions | type  | possible_keys | key     | key_len | ref  | rows   | filtered | Extra                    |
+----+-------------+----------+------------+-------+---------------+---------+---------+------+--------+----------+------ --------------------+| | 1 SIMPLE | dept_emp | NULL | index | NULL | dept_no 12 | NULL | | 308493 | | 11.11 Using the where; Using index | +----+-------------+----------+------------+-------+---------------+---------+---------+------+--------+----------+------ --------------------+
1 row in set.1 warning (0.00 sec)
Copy the code

However, emp_no and depT_NO are both foreign keys, so they are also indexes. When a to_date is added to a column, it is not an index

mysql> EXPLAIN SELECT emp_no,dept_no,to_date FROM dept_emp WHERE dept_no LIKE '%d00%';
+----+-------------+----------+------------+------+---------------+------+---------+------+--------+----------+---------- ---+
| id | select_type | table    | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+----------+------------+------+---------------+------+---------+------+--------+----------+---------- ---+| | 1 SIMPLE | dept_emp | NULL | | NULL ALL | NULL | NULL | NULL | 308493 | | 11.11 Using the where | +----+-------------+----------+------------+------+---------------+------+---------+------+--------+----------+---------- ---+
1 row in set.1 warning (0.00 sec)
Copy the code

If type is ALL, full table scan is performed

UNCACHEABLE SUBQUERY

The results of the subquery cannot be cached and must be re-evaluated for each row of the external query, indicating that the results of the subquery cannot be cached

mysql> EXPLAIN SELECT * FROM employees emp 
    -> where emp.emp_no = (select de.emp_no from dept_emp de where de.dept_no=@@sort_buffer_size);
+----+----------------------+-------+------------+-------+---------------+---------+---------+------+--------+----------+ --------------------------------+
| id | select_type          | table | partitions | type  | possible_keys | key     | key_len | ref  | rows   | filtered | Extra                          |
+----+----------------------+-------+------------+-------+---------------+---------+---------+------+--------+----------+ --------------------------------+| 1 | PRIMARY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | no matching row in const table | | 2 | UNCACHEABLE SUBQUERY | NULL | | DE index | dept_no | dept_no 12 | NULL | | 308493 | | 10.00 Using the where; Using index | +----+----------------------+-------+------------+-------+---------------+---------+---------+------+--------+----------+ --------------------------------+
2 rows in set.65535 warnings (0.82 sec)

Copy the code

UNCACHEABLE UNION

Is the second or higher version selection in the UNION of the non-cacheable subquery

mysql> EXPLAIN SELECT * FROM employees emp 
    -> WHERE emp.birth_date IN (
    -> SELECT to_date FROM (
    -> SELECT de.to_date,de.dept_no FROM dept_emp de WHERE de.dept_no=@@sort_buffer_size
    -> UNION
    -> SELECT dm.to_date,dm.dept_no FROM dept_manager dm WHERE dm.dept_no=@@sort_buffer_size) AS dep WHERE dep.dept_no=@@sort_buffer_size
    -> );
+----+-------------------+-------------+------------+--------+---------------+------------+---------+-------------------- ------+--------+----------+-----------------+
| id | select_type       | table       | partitions | type   | possible_keys | key        | key_len | ref                      | rows   | filtered | Extra           |
+----+-------------------+-------------+------------+--------+---------------+------------+---------+-------------------- ------+--------+----------+-----------------+| | 1 PRIMARY emp | | NULL | | NULL ALL | NULL | NULL | NULL | 299468 | | 100.00 Using the where | | 1 | PRIMARY | < subquery2 > | NULL | eq_ref | < auto_key > | < auto_key > | 3 | employees. The emp. The birth_date | 1 | | NULL 100.00 | | | 2 MATERIALIZED | < derived3 > | NULL | | NULL ALL | NULL | NULL | NULL | 30851 | | 10.00 Using the where | | 3 | DERIVED | | DE NULL | | ALL dept_no | NULL | NULL | NULL | 308493 | | 10.00 Using the where | | | 4 UNCACHEABLE UNION | dm | NULL | | ALL Dept_no | NULL | NULL | NULL 24 | | | 10.00 Using the where | | NULL | UNION RESULT | < union3, 4 > | NULL | | NULL ALL | NULL | NULL | NULL | NULL | NULL | Using temporary | +----+-------------------+-------------+------------+--------+---------------+------------+---------+-------------------- ------+--------+----------+-----------------+
6 rows in set.5 warnings (0.00 sec)
Copy the code

The select_Type value of the non-SELECT statement shows the statement type of the affected table. For example, with DELETE statements, select_type is DELETE.

Table 2.3

Table (JSON name: table_name) The name of the table referenced by the output row. This can also be one of the following values:

: This line references the union of rows with ids of M and N.
,n>
: The row references the result of a derived table of the row whose ID is N. Derived tables may come FROM a subquery such as the FROM clause.
: result of the entity subquery in which the row references the row with ID N.

2.4 partitions

Partitions, JSON name: Partitions are queried to match records from. For non-partitioned tables, this value is NULL.

2.5 type

Type, JSON name: access_type, join type. Description of different types. The type column of the EXPLAIN output describes how to join the tables. These are used as values for the access_type attribute in jSON-formatted output. The following list describes the connection types, from the best to the worst:

system

This table has only one row and is a special case of const. InnoDB cannot reliably maintain table sizes if the InnoDB engine is used, so the query optimizer cannot ensure that the table has an exact 1 row.

mysql> alter table test_system engine=myisam;
Query OK, 1 row affected (0.04 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> select count(*) from test_system;
+----------+
| count(*) |
+----------+| | + 1----------+
1 row in set (0.00 sec)

mysql> EXPLAIN select * from test_system;
+----+-------------+-------------+------------+--------+---------------+------+---------+------+------+----------+------- +
| id | select_type | table       | partitions | type   | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------------+------------+--------+---------------+------+---------+------+------+----------+------- +| | 1 SIMPLE | test_system | NULL | system | NULL | NULL | NULL | NULL | | | NULL | 100.00 + 1----+-------------+-------------+------------+--------+---------------+------+---------+------+------+----------+------- +
1 row in set.1 warning (0.00 sec)
Copy the code

const

The table has at most one matching row, which is read at the start of the query. 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 can only be read once.

This table can be used as a const table when using a primary key index or the whole of a unique index, for example:

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

eq_ref

When only one row is matched, eq_ref is the best type, except for const system. When all values with a primary key or unique index are used, only one single value is matched. Use the primary key or unique index as the condition for a join table query

mysql> EXPLAIN SELECT emp.emp_no,emp.first_name FROM employees emp,dept_manager dm  WHERE emp.emp_no = dm.emp_no;
+----+-------------+-------+------------+--------+---------------+---------+---------+---------------------+------+------ ----+-------------+
| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref                 | rows | filtered | Extra       |
+----+-------------+-------+------------+--------+---------------+---------+---------+---------------------+------+------ ----+-------------+| | SIMPLE | 1 dm | NULL | index | PRIMARY | dept_no 12 | | NULL 24 | | | 100.00 Using index | | | 1 SIMPLE | emp | NULL | eq_ref | PRIMARY | PRIMARY | | 4 employees. The dm. The emp_no | | | NULL | 100.00 + 1----+-------------+-------+------------+--------+---------------+---------+---------+---------------------+------+------ ----+-------------+
2 rows in set.1 warning (0.00 sec)
Copy the code

ref

For each combination of rows from the previous table, all rows from the index that best matches are read from the table. If the join uses only the leftmost prefix of the key

If you configure an index consisting of A(column name)+B(column name)+C(column name), use ref if A is used as A condition

Example:

mysql> ALTER TABLE `employees`.`employees` ADD INDEX first_last(`first_name`.`last_name`) USING BTREE;
Query OK, 0 rows affected (2.33 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> EXPLAIN SELECT * FROM employees WHERE first_name = "Mary";
+----+-------------+-----------+------------+------+---------------+------------+---------+-------+------+----------+---- ---+
| id | select_type | table     | partitions | type | possible_keys | key        | key_len | ref   | rows | filtered | Extra |
+----+-------------+-----------+------------+------+---------------+------------+---------+-------+------+----------+---- ---+
|  1 | SIMPLE      | employees | NULL       | ref  | first_last    | first_last | 44      | const |  224 |   100.00 | NULL  |
+----+-------------+-----------+------------+------+---------------+------------+---------+-------+------+----------+---- ---+
1 row in set.1 warning (0.00 sec)
Copy the code

, or if the KEY is not a PRIMARY KEY or UNIQUE index (in other words, if the join cannot select a single row based on the KEY value), use ref. This is a good type of join if you use keys that match only a few rows.

fulltext
ref_or_null
index_merge
unique_subquery
index_subquery
range
index
ALL

3. Explanation of related terms

3.1 Related sub-queries

Subqueries that depend on variables in the main query and cannot run independently.
Therefore, a subquery is relevant if you cannot run it alone.