Recently, there is a need to modify the existing storage structure, which involves the consideration of query conditions and query efficiency. I have read several articles related to index and HBase, recalled the relevant knowledge, and combined with the project requirements, talked about my understanding and summary.
The first two articles introduced the advantages of index, the evolution process of index structure and the execution process of SQL, focusing on the analysis of SQL execution order and data positioning process, missed friends can first review below:
- Index structure and data location process
- Query procedure and advanced query
This article introduces how to view and analyze SQL execution and identify performance problems. You will learn:
- Overview of explain Commands
- Details about the select_type field
- This section describes the type field
- Extra field details
Part of the content is an excerpt from a few bloggers’ posts, with links at the end to thank them for their excellent analysis.
Overview of explain Commands
In work, MySQL will record the SQL statements that take a long time to execute. Finding out these SQL statements is the first step. The important thing is to check the execution plan of SQL statements. The output from this command gives you an idea of how the MySQL optimizer executes SQL statements.
The MySQL optimizer works based on overhead, which is calculated dynamically at the time of execution of each SQL statement. The command usage is simple, such as adding Explain to the front of the SELECT statement.
Let’s start with an example
Take the basic employee table as an example. The table structure is as follows:
mysql> show create table employee \G; *************************** 1. row *************************** Table: mcc_employee Create Table: CREATE TABLE 'employee' (' id 'bigint(20) NOT NULL AUTO_INCREMENT,' userId 'vARCHar (50) DEFAULT NULL COMMENT' employee ID ', 'nickName' varchar(50) DEFAULT NULL COMMENT 'nickName' varchar(50) DEFAULT NULL COMMENT 'nickName ', 'gender' varchar(10) DEFAULT NULL COMMENT 'gender' Varchar (20) DEFAULT NULL COMMENT 'mobilePhone' Varchar (20) 'miliao' varchar(100) DEFAULT NULL COMMENT 'email' varchar(100) DEFAULT NULL COMMENT 'email ', PRIMARY KEY (' id ') ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET= UTF8 1 row in set (0.00 SEC)Copy the codeA simple query:
mysql> explain select * from employee where id =1 \G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: employee
type: const
possible_keys: PRIMARY
key: PRIMARY
key_len: 8
ref: const
rows: 1
Extra: NULL
1 row in set (0.00 sec)
Copy the codeIf select_type is simple, it is a common query and does not contain subqueries and union queries.
Summary of field
Id field: select The id of the query. Each select is automatically assigned a unique identifier. The larger the number of ids, the higher the number of ids, and the same id from the top down.
Select_type: select Specifies the query type. If there is no subquery or union query, the value is simple. If there is a subquery or union query, there are several cases.
Table: identifies which table the query is for, shows which table the row is about, sometimes not the actual table name, seeing the derived (n is a number, id field)
mysql> explain select * from (select * from (select * from employee where id =76) table1 ) table2 ; +----+-------------+------------+--------+---------------+---------+---------+-------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+------------+--------+---------------+---------+---------+-------+------+-------+ | 1 | PRIMARY | <derived2> | system | NULL | NULL | NULL | NULL | 1 | NULL | | 2 | DERIVED | <derived3> | system | NULL | NULL | NULL | NULL | 1 | NULL | | 3 | DERIVED | mcc_inform | const | PRIMARY | PRIMARY | 8 | const | 1 | NULL | +----+-------------+------------+--------+---------------+---------+---------+-------+------+-------+Copy the codeType: indicates the type of data access or read operations. This operation has a significant impact on performance, as described in detail later.
Possible_keys: a possible index to use in this query, which will be listed, but not necessarily used, if an index exists on a field involved in the query.
Key: specifies the index used in the query. If no index is selected, the key is NULL.
Key_len: indicates the number of bytes used in the index. You can use this column to calculate the length of the index used in the query.
Ref: Which field or constant is used with the key.
Rows: Total number of rows scanned by this query. This is an estimate.
Filtered: Indicates the percentage of the data filtered by this query criteria.
-Blair: Extra information, more on that later.
Details about the select_type field
Indicates the query type, which can be simple query or complex query. If the query is complex query, SIMPLE, SIMPLE, UNION, UNION RESULT, SUBQUERY, DEPENDENT, DEPENDENT UNION, DEPENDENT SUBQUERY, DERIVED, etc.
SIMPLE
Simple select without union, subquery, etc. :
mysql> explain select * from employee where id =1 ;
+----+-------------+------------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+-------+---------------+---------+---------+-------+------+-------+
| 1 | SIMPLE | employee | const | PRIMARY | PRIMARY | 8 | const | 1 | NULL |
+----+-------------+------------+-------+---------------+---------+---------+-------+------+-------+
Copy the codePRIMARY
For complex queries, this is the outermost select:
mysql> explain select * from (select * from employee where id =1) a ; +----+-------------+------------+--------+---------------+---------+---------+-------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+------------+--------+---------------+---------+---------+-------+------+-------+ | 1 | PRIMARY | <derived2> | system | NULL | NULL | NULL | NULL | 1 | NULL | | 2 | DERIVED | employee | const | PRIMARY | PRIMARY | 8 | const | 1 | NULL | +----+-------------+------------+--------+---------------+---------+---------+-------+------+-------+Copy the codeUNION & UNION RESULT
The second or following SELECT statement in the UNION, where UNION RESULT is the RESULT of the UNION:
mysql> explain select * from employee where id =1 union all select * from employee where id=2; +----+--------------+------------+-------+---------------+---------+---------+-------+------+-----------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | + - + -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | | 1 PRIMARY | employee | const | PRIMARY | PRIMARY | 8 | const | 1 | NULL | | 2 | UNION | employee | const | PRIMARY | Eight PRIMARY | | const | 1 | NULL | | NULL | UNION RESULT | < 2 > union1, | | NULL ALL | NULL | NULL | NULL | NULL | Using temporary | +----+--------------+------------+-------+---------------+---------+---------+-------+------+-----------------+Copy the codeSUBQUERY
First SELECT in subquery:
mysql> explain select * from employee where id = (select id from employee where id =1); +----+-------------+------------+-------+---------------+---------+---------+-------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+------------+-------+---------------+---------+---------+-------+------+-------------+ | 1 | PRIMARY | employee | const | PRIMARY | PRIMARY | 8 | const | 1 | NULL | | 2 | SUBQUERY | employee | const | PRIMARY | PRIMARY | 8 | const | 1 | Using index | +----+-------------+------------+-------+---------------+---------+---------+-------+------+-------------+Copy the codeDEPENDENT UNION & DEPENDENT SUBQUERY
DEPENDENT UNION, the second or following SELECT statement in the UNION, but the result depends on the outside query; DEPENDENT SUBQUERY = DEPENDENT SUBQUERY = DEPENDENT SUBQUERY = DEPENDENT SUBQUERY = DEPENDENT SUBQUERY = DEPENDENT SUBQUERY
mysql> explain select * from employee where id in (select id from employee where id =1 union all select id from employee where id=2); +----+--------------------+------------+-------+---------------+---------+---------+-------+------+-----------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+--------------------+------------+-------+---------------+---------+---------+-------+------+-----------------+ | 1 | PRIMARY | employee | ALL | NULL | NULL | NULL | NULL | 26 | Using where | | 2 | DEPENDENT SUBQUERY | employee | const | PRIMARY | PRIMARY | 8 | const | 1 | Using index | | 3 | DEPENDENT UNION | employee | const | PRIMARY | PRIMARY | 8 | const | 1 | Using index | | NULL | UNION RESULT | < union2, 3 > | | NULL ALL | NULL | NULL | NULL | NULL | Using temporary | +----+--------------------+------------+-------+---------------+---------+---------+-------+------+-----------------+Copy the codeDERIVED
Subquery of derived table SELECT, FROM clause:
mysql> explain select * from (select * from employee where id =1) a ; +----+-------------+------------+--------+---------------+---------+---------+-------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+------------+--------+---------------+---------+---------+-------+------+-------+ | 1 | PRIMARY | <derived2> | system | NULL | NULL | NULL | NULL | 1 | NULL | | 2 | DERIVED | employee | const | PRIMARY | PRIMARY | 8 | const | 1 | NULL | +----+-------------+------------+--------+---------------+---------+---------+-------+------+-------+Copy the codeThis section describes the type field
Type indicates the type of data access/read operation. It shows what type of join is used and whether indexes are used. It provides an important basis for determining whether queries are efficient or not.
The common types are: ALL, index, range, ref, eq_ref, const, system, NULL
NULL
To get the result directly without accessing the table or index:
mysql> explain select sysdate(); +----+-------------+-------+------+---------------+------+---------+------+------+----------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+------+----------------+ | 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used | +----+-------------+-------+------+---------------+------+---------+------+------+----------------+Copy the codeConst, system
By placing the primary key or unique index in the WHERE list, MySQL can convert the query to a constant of type SYSTEM when there is only one row in the table.
mysql> explain select * from (select id from mcc_inform where id =1) a; +----+-------------+------------+--------+---------------+---------+---------+-------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | + - + -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | | 1 PRIMARY | <derived2> | system | NULL | NULL | NULL | NULL | 1 | NULL | | 2 | DERIVED | employee | const | PRIMARY | PRIMARY | 8 | const | 1 | Using index | +----+-------------+------------+--------+---------------+---------+---------+-------+------+-------------+Copy the codeeq_ref
This type is usually used in join queries with multiple tables, indicating that each result of the former table can only match the result of one row of the later table. And the query comparison operation is usually =, the query efficiency is high:
mysql> explain select * from t3,t4 where t3.id=t4.accountid;
+----+-------------+-------+--------+-------------------+-----------+---------+----------------------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+-------------------+-----------+---------+----------------------+------+-------+
| 1 | SIMPLE | t4 | ALL | NULL | NULL | NULL | NULL | 1000 | |
| 1 | SIMPLE | t3 | eq_ref | PRIMARY,idx_t3_id | idx_t3_id | 4 | dbatest.t4.accountid | 1 | |
+----+-------------+-------+--------+-------------------+-----------+---------+----------------------+------+-------+
Copy the coderef
Similar to eq_ref, except that ref is not a unique index. This type is usually found in join queries with multiple tables that are not unique or primary key indexes, or queries that use the left-most prefix rule index. It can be used for indexed columns that use the = or <=> operators:
index_merge
This type of join indicates that the index merge optimization method has been used. If there are multiple conditions (OR joins) in a WHERE involving multiple fields, AND OR OR between them, it is possible to use the index merge technique.
Intersect/UNION: Perform conditional scan on multiple indexes, and then merge their results (INTERSECT/Union).
range
This type IS usually used IN =, <>, >=, <, <=, IS NULL, <=>, BETWEEN, IN() operations
index
Full index scan is similar to ALL, except that ALL is a full table scan. Index scans ALL indexes but does not scan data.
ALL
Represents a full table scan. This type of query is one of the worst performing and is not usually seen.
Extra field details
A lot of the Extra information in EXplain is shown in the Extra field, which gives us further details to understand the execution plan. Here are some common ones.
Using where
In the case of an index lookup, you need to go back to the table to query the required data.
Using index
Indicates that the query can find the required data in the index tree without scanning the table data file, indicating that the performance is good.
Using filesort
When AN ORDER BY operation is included in SQL and the sorting operation cannot be completed using an index, the query optimizer has to choose the appropriate sorting algorithm to implement.
If the result set exceeds sort_buffer_size, MySQL will transfer the chunk to file. If the result set exceeds sort_buffer_size, MySQL will transfer the chunk to file. Finally, the multi-way merge sort is used to sort all the data.
MySQL can only use filesort on a single table. If there are multiple tables that need to be sorted, MySQL can use using TEMPORARY to save temporary data, then use filesort on temporary tables to sort data, and then output results.
Using temporary
Temporary tables are used in the query, usually in the case of sorting, grouping and multi-table join. Therefore, the query efficiency is not high. Therefore, optimization is recommended.
The next article will cover index optimization principles and case studies.
Reference article:
- Mysql Explain explanation
- Explain Type Indicates an example of a connection type
Please scan the qr code below and follow my wechat official account for more articles ~
