Standard summary for analyzing the performance of a SQL statement
|
This article will introduce how to use explain to analyze a SQL statement. There are actually many articles on the Internet that introduce the use of explain in detail. This article combines examples and principles to try to give you a better understanding. Believe me, you should gain something special after reading it carefully. explain means explanation, which is called execution plan in MySQL. That is, you can use this command to see how MySQL decides to execute the SQL after the optimizer analyzes it. Speaking of optimizers, let me add that MySQL has a built-in powerful optimizer. The main task of the optimizer is to optimize the SQL you wrote and execute it at the lowest cost possible, such as scanning fewer rows, avoiding sorting, etc. What happens when executing a SQL statement? I have introduced the optimizer in the previous article. You may ask, when do we generally need to use explain? In most cases, we use explain to analyze some SQL statements with slow query efficiency from the MySQL slow query log. Sometimes, we use explain to analyze whether the added index can be hit when optimizing MySQL, such as adding an index. Sometimes, when developing business, you may need to use explain to select a more efficient SQL statement to meet the needs. So how to use explain? It’s very simple. Just add explain in front of sql, as shown below. mysql> explain select * from t; +----+-------------+-------+------+---------------+------+---------+------+--------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+--------+-------+ | 1 | SIMPLE | t | ALL | NULL | NULL | NULL | NULL | 100332 | NULL | +----+-------------+-------+------+---------------+------+---------+------+--------+-------+ 1 row in set (0.04 sec) As you can see, explain returns about 10 fields. The fields returned by different versions are slightly different. Each field represents a specific meaning. In this article, I am not going to introduce each field in detail. There are too many things and I am afraid it is not easy for you to remember them. It is better to understand several important fields first. Among them, I think the type, key, rows, and Extra fields are more important. Let's use specific examples to help you better understand the meaning of these fields. First of all, it is necessary to briefly introduce the literal meaning of these fields. type indicates the way MySQL accesses data. Common ones include full table scan (all), traversal index (index), interval query (range), constant or equal query (ref, eq_ref), primary key equal query (const), when there is only one record in the table (system). Below is a ranking from best to worst efficiency. system > const > eq_ref > ref > range > index > all key indicates the index name that will be actually used in the query process. rows indicates the number of rows that may need to be scanned during the query process. This data may not be accurate and is a sampling statistic of MySQL. Extra indicates some additional information, which usually shows whether an index is used, whether sorting is required, whether a temporary table is used, etc. Okay, let’s start the case analysis. Let’s create a test table using the storage engine created in the previous article. We will insert 100,000 test data items into the table. The table structure is as follows: CREATE TABLE `t` ( `id` int(11) NOT NULL, `a` int(11) DEFAULT NULL, `b` int(11) DEFAULT NULL, PRIMARY KEY (`id`) )ENGINE=InnoDB; Then look at the following query statement. Note that this table currently has only one primary key index and no ordinary index has been created. mysql> alter table t add index a_index(a); Query OK, 0 rows affected (0.19 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> alter table t add index b_index(b); Query OK, 0 rows affected (0.20 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> show index from t; +-------+------------+----------+--------------+--------------+--------------+-------------+------+--------+------+------------+---------+------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +-------+------------+----------+--------------+--------------+--------------+-------------+------+--------+------+------------+---------+------------+ | t | 0 | PRIMARY | 1 | id | A | 100332 | NULL | NULL | | BTREE | | | | t | 1 | a_index | 1 | a | A | 100332 | NULL | NULL | YES | BTREE | | | | t | 1 | b_index | 1 | b | A | 100332 | NULL | NULL | YES | BTREE | | | +-------+------------+----------+--------------+--------------+--------------+-------------+------+--------+------+------------+---------+------------+ 3 rows in set (0.00 sec) The type value is ALL, which means that the entire table is scanned. Note that the rows field shows 100,332 records. In fact, we only have 100,000 records in total, so this field is just an estimate by MySQL and may not be accurate. This full table scan is very inefficient and needs to be optimized. Next, we will add normal indexes to fields a and b respectively, and then look at the SQL statements after adding the indexes. mysql> alter table t add index a_index(a); Query OK, 0 rows affected (0.19 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> alter table t add index b_index(b); Query OK, 0 rows affected (0.20 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> show index from t; +-------+------------+----------+--------------+--------------+--------------+-------------+------+--------+------+------------+---------+------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +-------+------------+----------+--------------+--------------+--------------+-------------+------+--------+------+------------+---------+------------+ | t | 0 | PRIMARY | 1 | id | A | 100332 | NULL | NULL | | BTREE | | | | t | 1 | a_index | 1 | a | A | 100332 | NULL | NULL | YES | BTREE | | | | t | 1 | b_index | 1 | b | A | 100332 | NULL | NULL | YES | BTREE | | | +-------+------------+----------+--------------+--------------+--------------+-------------+------+--------+------+------------+---------+------------+ 3 rows in set (0.00 sec) mysql> explain select * from t where a > 1000; +----+-------------+-------+------+---------------+------+---------+------+--------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+--------+-------------+ | 1 | SIMPLE | t | ALL | a_index | NULL | NULL | NULL | 100332 | Using where | +----+-------------+-------+------+---------------+------+---------+------+--------+-------------+ 1 row in set (0.00 sec) Does the above SQL look a bit confusing? Type actually shows that an index has just been added to field a, and possible_keys also shows that a_index is available, but key shows null, indicating that MySQL will not actually use the a index. Why is this? This is because if you use select *, you need to go back to the primary key index to search for field b. This process is called table return. This statement will filter out 90,000 pieces of data that meet the conditions. That is to say, these 90,000 pieces of data need to be returned to the table, and a full table scan only has 100,000 pieces of data. Therefore, in the eyes of the MySQL optimizer, it is better to scan the entire table directly, at least the table return process is avoided. Of course, it does not mean that the index will not be hit as long as there is a table return operation. The key to using the index lies in which query cost MySQL considers to be lower. Let's slightly modify the where condition in the above SQL. mysql> explain select * from t where a > 99000; +----+-------------+-------+-------+---------------+---------+---------+------+------+---------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+---------+---------+------+------+---------------------------------+ | 1 | SIMPLE | t | range | a_index | a_index | 5 | NULL | 999 | Using index condition | +----+-------------+-------+-------+---------------+---------+---------+------+------+---------------------------------+ 1 row in set (0.00 sec) This time the type value is range, and the key is a_index, which means that index a is hit, which is a good choice because there are only 1,000 records that meet this SQL condition. MySQL believes that even if 1,000 records are returned to the table, the cost is lower than scanning the entire table. So MySQL is actually a very smart guy. We can also see that the value in the Extra field is Using index condition, which means that the index is used, but a table return is required. Let's look at the following statement. mysql> explain select a from t where a > 99000; +----+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+ | 1 | SIMPLE | t | range | a_index | a_index | 5 | NULL | 999 | Using where; Using index | +----+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+ 1 row in set (0.00 sec) The value in this Extra is Using where; Using index, which means that the query uses the index and the fields to be queried can be obtained in the index without returning to the table. Obviously, this efficiency is higher than the above, so do not write select * easily. Only query the fields required by the business, so as to avoid returning to the table as much as possible. Let’s look at another one that needs to be sorted. mysql> explain select a from t where a > 99000 order by b; +----+-------------+-------+-------+---------------+---------+---------+------+------+---------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+---------+---------+------+------+---------------------------------------+ | 1 | SIMPLE | t | range | a_index | a_index | 5 | NULL | 999 | Using index condition; Using filesort | +----+-------------+-------+-------+---------------+---------+---------+------+------+---------------------------------------+ 1 row in set (0.00 sec) This Extra returns a Using filesort, which means that sorting is required. This needs to be optimized. That is to say, after finding the data, MySQL needs to sort it in memory. You must know that the index itself is ordered, so generally speaking, you should try to use the orderliness of the index as much as possible, such as writing it as follows. mysql> explain select a from t where a > 99990 order by a; +----+-------------+-------+-------+------------------+---------+---------+------+------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+------------------+---------+---------+------+------+--------------------------+ | 1 | SIMPLE | t | range | a_index,ab_index | a_index | 5 | NULL | 10 | Using where; Using index | +----+-------------+-------+-------+------------------+---------+---------+------+------+--------------------------+ 1 row in set (0.00 sec) Let's create another composite index and see. mysql> alter table t add index ab_index(a,b); Query OK, 0 rows affected (0.19 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> explain select * from t where a > 1000; +----+-------------+-------+-------+------------------+----------+----------+------+------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+------------------+----------+----------+------+------+--------------------------+ | 1 | SIMPLE | t | range | a_index,ab_index | ab_index | 5 | NULL | 50166 | Using where; Using index | +----+-------------+-------+-------+------------------+----------+----------+------+------+--------------------------+ 1 row in set (0.00 sec) This SQL has been mentioned above. When no composite index is created, a full table scan is performed. Now, a covering index is used, which also avoids the table return process. That is, the field to be queried can be found on the (ab_index) index. This article introduces how to use explain to analyze the execution plan of a SQL statement through several examples. It also mentions some common index optimizations. In fact, there are more possibilities. You can also write a SQL statement yourself and then use explain to analyze it to see which ones can be optimized. You may also be interested in:
|
<<: Nginx dynamically forwards to upstream according to the path in the URL
>>: WeChat applet implements calculator function
Recommend
Html+CSS floating advertisement strip implementation
1.html part Copy code The code is as follows: <...
Detailed explanation of scroll bar scrolling control of DOM elements in HTML
I don't know if you have ever encountered suc...
Installing linux7.2 Internet access configuration tutorial on VMware virtual machine under win7
Install linux7.2 Internet access configuration on...
Two ways to manage volumes in Docker
In the previous article, I introduced the basic k...
MySQL case when usage example analysis
First we create the database table: CREATE TABLE ...
Detailed explanation of the whole process of building a personal blog with nginx+WordPress
0x00 Introduction WordPress is the most popular C...
Summary of commonly used commands for docker competition submission
Log in to your account export DOCKER_REGISTRY=reg...
How to modify create-react-app's configuration without using eject
1. Why is eject not recommended? 1. What changes ...
JavaScript code to achieve a simple calendar effect
This article shares the specific code for JavaScr...
How to start the spring-boot project using the built-in linux system in win10
1. Install the built-in Linux subsystem of win10 ...
How to install JDK and Mysql on Ubuntu 18.04 Linux system
Platform deployment 1. Install JDK step1. Downloa...
A mobile adaptive web page effect solves the problem of small display page
For work needs, I need to make a mobile phone adap...
Vue implements the countdown component for second kills
This article shares the specific code of Vue to i...
Solve the problem that Docker cannot ping the host machine under Mac
Solution Abandon the Linux virtual machine that c...
Example of how to achieve semi-transparent background image and opaque content in CSS3
I encountered this problem when I was making the ...