What is the role of mysql explain?

The function of mysql explain is to simulate how the Mysql optimizer executes SQL query statements, so as to know how Mysql processes the user's SQL statements, improve data retrieval efficiency, and reduce the IO cost of the database.

The function of mysql explain is:

Simulate how the Mysql optimizer executes SQL query statements, so as to know How Mysql processes your SQL statements. Analyze the performance bottlenecks of your query statements or table structures.

mysql> explain select * from tb_user;
+----+-------------+---------+------+---------------+------+---------+------+------+-------+
| id | select_type | table   | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+---------+------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE      | tb_user | ALL  | NULL          | NULL | NULL    | NULL |    1 | NULL  |
+----+-------------+---------+------+---------------+------+---------+------+------+-------+

(1) id column:

(1)、id 相同执行顺序由上到下
mysql> explain  
    -> SELECT*FROM tb_order tb1
    -> LEFT JOIN tb_product tb2 ON tb1.tb_product_id = tb2.id
    -> LEFT JOIN tb_user tb3 ON tb1.tb_user_id = tb3.id;
+----+-------------+-------+--------+---------------+---------+---------+---------------------------+------+-------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref                       | rows | Extra |
+----+-------------+-------+--------+---------------+---------+---------+---------------------------+------+-------+
|  1 | SIMPLE      | tb1   | ALL    | NULL          | NULL    | NULL    | NULL                      |    1 | NULL  |
|  1 | SIMPLE      | tb2   | eq_ref | PRIMARY       | PRIMARY | 4       | product.tb1.tb_product_id |    1 | NULL  |
|  1 | SIMPLE      | tb3   | eq_ref | PRIMARY       | PRIMARY | 4       | product.tb1.tb_user_id    |    1 | NULL  |
+----+-------------+-------+--------+---------------+---------+---------+---------------------------+------+-------+
(2)、如果是子查询，id序号会自增，id值越大优先级就越高，越先被执行。
mysql> EXPLAIN
    -> select * from tb_product tb1 where tb1.id = (select tb_product_id from  tb_order tb2 where id = tb2.id =1);
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref   | rows | Extra       |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+
|  1 | PRIMARY     | tb1   | const | PRIMARY       | PRIMARY | 4       | const |    1 | NULL        |
|  2 | SUBQUERY    | tb2   | ALL   | NULL          | NULL    | NULL    | NULL  |    1 | Using where |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+
(3)、id 相同与不同，同时存在
mysql> EXPLAIN 
    -> select * from(select * from tb_order tb1 where tb1.id =1) s1,tb_user tb2 where s1.tb_user_id = tb2.id;
+----+-------------+------------+--------+---------------+---------+---------+-------+------+-------+
| id | select_type | table      | type   | possible_keys | key     | key_len | ref   | rows | Extra |
+----+-------------+------------+--------+---------------+---------+---------+-------+------+-------+
|  1 | PRIMARY     | <derived2> | system | NULL          | NULL    | NULL    | NULL  |    1 | NULL  |
|  1 | PRIMARY     | tb2        | const  | PRIMARY       | PRIMARY | 4       | const |    1 | NULL  |
|  2 | DERIVED     | tb1        | const  | PRIMARY       | PRIMARY | 4       | const |    1 | NULL  |
+----+-------------+------------+--------+---------------+---------+---------+-------+------+-------+
derived2：衍生表   2表示衍生的是id=2的表 tb1

Related learning recommendations: mysql video tutorial

(2) select_type column: Operation type of data reading operation

1. SIMPLE: Simple select query, SQL does not contain subqueries or UNION.

2. PRIMARY: The query contains a complex subquery part, and the outermost query is marked as PRIMARY

3. SUBQUERY: The subquery is included in the select or WHERE list

　4. DERIVED: The subqueries contained in the FROM list will be marked as DERIVED (derived tables). MYSQL will recursively execute these subqueries and put the result set into the zero-time table.

　5. UNION: If the second SELECT appears after UNION, it will be marked as UNION; if UNION is included in the subquery of the FROM clause, the outer SELECT will be marked as DERIVED

　6. UNION RESULT: Select the result obtained from the UNION table

(3) table column: Which table the row of data is about

(4) type column: access type from good to bad system > const > eq_ref > ref > range > index > ALL

1, system: The table has only one record (equal to the system table). This is a special case of the const type and does not appear in normal business.

2, const: Find the data once through the index. This type is mainly used to compare primary key or unique index. Because it only matches one row of data, it is very fast; if the primary key is placed After the WHERE statement, Mysql can convert the query into a constant.

3, eq_ref: Unique index scan, for each index key, only one record in the table matches it. Commonly seen in primary key or unique index scans.

4, ref: Non-unique index scan returns all rows matching a single value. It is essentially an index access. It returns all rows matching a single value, that is It says that it may find multiple pieces of data that meet the conditions, so it is a mixture of search and scanning.

Detailed explanation: This type means that mysql will quickly find a qualified index based on a specific algorithm, instead of scanning and judging every data in the index one by one, which is what you usually call Understanding the use of index queries will retrieve data faster. To achieve this kind of search, indexes are required. To implement this fast search algorithm, the index must meet a specific data structure. To put it simply, the data in the index field must be in order to achieve this type of search and to use the index.

5, range: Retrieve only rows in a given range and use an index to select rows. The key column shows which index was used. Generally, queries such as between, 95ec6993dc754240360e28e0de8de30a, and in appear in your WHERE statement. This kind of given range scan is better than the full table scan. Because it only needs to start at a certain point in the index and end at another point, without scanning the entire index.

6, index: FUll Index Scan scans and traverses the index tree (index: This type means that mysql will scan the entire index. If you want to use this type of index, There are no special requirements for this index. As long as it is an index or part of a composite index, mysql may use the index type method to scan. However, the disadvantage is that the efficiency is not high. Mysql will start from the first data in the index. Search the last data one by one until an index that meets the judgment conditions is found).

7, ALL: Full table scan to obtain data from disk. Optimize millions of data ALL type data as much as possible.

(5) possible_keys column : Displays one or more indexes that may be applied to this table. If an index exists for the fields involved in the query, the index will be listed, but it may not be actually used by the query.

(6) keys column: The actual index used. If NULL, no index is used. If a covering index is used in the query, the index only appears in the key list. Covering index: The fields after selection are consistent with the number of fields we index.

(7) ken_len column: indicates the number of bytes used in the index. This column can be used to calculate the index length used in the query. The shorter the length the better without losing accuracy. The value displayed by key_len is the maximum possible length of the index field, not the actual length used. That is, key_len is calculated based on the table definition and is not retrieved from the table.

（八）ref列：显示索引的哪一列被使用了，如果可能的话，是一个常数。哪些列或常量被用于查找索引列上的值。

（九）rows列(每张表有多少行被优化器查询)：根据表统计信息及索引选用的情况，大致估算找到所需记录需要读取的行数。

（十）Extra列：扩展属性，但是很重要的信息。

1、 Using filesort(文件排序)：mysql无法按照表内既定的索引顺序进行读取。
 mysql> explain select order_number from tb_order order by order_money;
+----+-------------+----------+------+---------------+------+---------+------+------+----------------+
| id | select_type | table    | type | possible_keys | key  | key_len | ref  | rows | Extra          |
+----+-------------+----------+------+---------------+------+---------+------+------+----------------+
|  1 | SIMPLE      | tb_order | ALL  | NULL          | NULL | NULL    | NULL |    1 | Using filesort |
+----+-------------+----------+------+---------------+------+---------+------+------+----------------+
1 row in set (0.00 sec)
说明：order_number是表内的一个唯一索引列，但是order by 没有使用该索引列排序，所以mysql使用不得不另起一列进行排序。
2、Using temporary:Mysql使用了临时表保存中间结果，常见于排序order by 和分组查询 group by。
mysql> explain select order_number from tb_order group by order_money;
+----+-------------+----------+------+---------------+------+---------+------+------+---------------------------------+
| id | select_type | table    | type | possible_keys | key  | key_len | ref  | rows | Extra                           |
+----+-------------+----------+------+---------------+------+---------+------+------+---------------------------------+
|  1 | SIMPLE      | tb_order | ALL  | NULL          | NULL | NULL    | NULL |    1 | Using temporary; Using filesort |
+----+-------------+----------+------+---------------+------+---------+------+------+---------------------------------+
1 row in set (0.00 sec)
3、Using index 表示相应的select 操作使用了覆盖索引，避免访问了表的数据行，效率不错。
如果同时出现Using where ，表明索引被用来执行索引键值的查找。
如果没有同时出现using where 表明索引用来读取数据而非执行查找动作。
mysql> explain select order_number from tb_order group by order_number;
+----+-------------+----------+-------+--------------------+--------------------+---------+------+------+-------------+
| id | select_type | table    | type  | possible_keys      | key                | key_len | ref  | rows | Extra       |
+----+-------------+----------+-------+--------------------+--------------------+---------+------+------+-------------+
|  1 | SIMPLE      | tb_order | index | index_order_number | index_order_number | 99      | NULL |    1 | Using index |
+----+-------------+----------+-------+--------------------+--------------------+---------+------+------+-------------+
1 row in set (0.00 sec)
4、Using where 查找
5、Using join buffer ：表示当前sql使用了连接缓存。
6、impossible where ：where 字句 总是false ，mysql 无法获取数据行。
7、select tables optimized away：
8、distinct：

The above is the detailed content of What is the role of mysql explain?. For more information, please follow other related articles on the PHP Chinese website!