How to use explain in MySql to query the execution plan of SQL
- 2021-10-25 00:01:14
- OfStack
The explain command is the primary way to see how the query optimizer decides to execute the query.
This feature has limitations and doesn't always tell the truth, but its output is the best information available, and it's worth the time to understand because you can learn how the query is executed.
1. What is the MySQL execution plan
A good understanding of the execution plan requires a simple understanding of the MySQL infrastructure and query fundamentals.
The functional architecture of MySQL itself is divided into three parts, namely application layer, logical layer and physical layer, not only MySQL, but most other database products are divided according to this architecture.
The application layer is mainly responsible for interacting with the client, establishing links, remembering the link status, returning data and responding to requests. This layer deals with the client.
Logic layer is mainly responsible for query processing, transaction management and other database function processing, taking query as an example.
After receiving the query SQL at first, the database will immediately allocate a thread to process it. In the first step, the query processor will optimize the SQL query, and after optimization, it will generate an execution plan, which will be executed by the plan executor.
The plan executor needs to access the lower transaction manager and storage manager to operate the data, and their respective division of labor is different. Finally, the query structure information is obtained by calling the files in the physical layer, and the final result is responded to the application layer.
Physical layer, the actual physical disk on the storage of files, mainly penny data files, log files.
Through the above description, generating an execution plan is an essential step to execute an SQL, and the performance of an SQL can be seen intuitively by looking at the execution plan. The execution plan provides various query types and levels, which we view and serve as the basis for performance analysis.
2. How to analyze the execution plan
MySQL provides us with the explain keyword to visually view an SQL execution plan.
explain shows how MySQL uses indexes to process select statements and join tables to help select better indexes and write more optimized query statements.
Let's do a query using explain, as follows:
mysql> explain select * from payment;
+----+-------------+---------+------------+------+---------------+------+---------+------+-------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+-------+----------+-------+
| 1 | SIMPLE | payment | NULL | ALL | NULL | NULL | NULL | NULL | 16086 | 100.00 | NULL |
+----+-------------+---------+------------+------+---------------+------+---------+------+-------+----------+-------+
1 row in set, 1 warning (0.01 sec)There are 12 columns in the query structure. Understanding the meaning of each column is very important to understand the execution plan. The following is explained in the form of a table.
| 列名 | 说明 |
|---|---|
| id | SELECT识别符，这是SELECT的查询序列号。 |
| select_type |
SELECT类型,可以为以下任何1种:
|
| table | 输出的行所引用的表 |
| partitions | 如果查询是基于分区表的话，显示查询将访问的分区。 |
| type |
联接类型。下面给出各种联接类型,按照从最佳类型到最坏类型进行排序:
1般来说，得保证查询至少达到range级别，最好能达到ref。 |
| possible_keys | 指出MySQL能使用哪个索引在该表中找到行 |
| key | 显示MySQL实际决定使用的键(索引)。如果没有选择索引,键是NULL。 |
| key_len | 显示MySQL决定使用的键长度。如果键是NULL,则长度为NULL。在不损失精确性的情况下，长度越短越好 |
| ref | 显示使用哪个列或常数与key1起从表中选择行。 |
| rows | 显示MySQL认为它执行查询时必须检查的行数。多行之间的数据相乘可以估算要处理的行数。 |
| filtered | 显示了通过条件过滤出的行数的百分比估计值。 |
| Extra |
该列包含MySQL解决查询的详细信息
|
Summarize