Analysis of a standard summary of SQL performance

Time:2021-4-19

This article will show you how to use explain to analyze a SQL.

In fact, there are many articles on the Internet that introduce the use of explain in detail. This article combines examples and principles to let you have a better understanding as far as possible. Believe me, you should have a special harvest after reading them carefully.

In mysql, it is called execution plan. That is to say, you can see how MySQL decides to execute the SQL after analysis by the optimizer through this command.

When it comes to the optimizer, let’s say more, MySQL has a powerful built-in optimizer. The main task of the optimizer is to optimize the SQL you write and execute it at a lower cost as much as possible, such as scanning fewer lines and avoiding sorting. What are the experiences of executing an SQL statement? I’ve covered optimizer related issues in previous articles.

You may ask, when do you usually use explain? In most cases, you find some SQL with slow query efficiency from the slow query log of Mysql to use explain analysis. In other cases, when you optimize mysql, such as adding indexes, you can use explain to analyze whether the added indexes can be hit, What’s more, in business development, you may need to use explain to select a more efficient SQL to meet your 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 will return about 10 fields. There are some differences in the fields returned by different versions. Each field represents a specific meaning. In this article, I don’t intend to introduce every field in detail. There are many things. I’m afraid it’s not easy for you to remember. It’s better to understand several important fields first.

I think the type, key, rows and extra fields are relatively important. Next, we will help you better understand the meaning of these fields through specific examples.

First of all, it is necessary to briefly introduce the literal meaning of these fields.

Type refers to the way that MySQL accesses data. The common ways are full table scan (all), index traversal (index), range query (range), constant or equivalent query (ref, EQ)_ Ref), primary key equivalent query (const), when there is only one record in the table (system). Here is a ranking of efficiency from the best to the worst.


system > const > eq_ref > ref > range > index > all

Key indicates the index name actually used in the query process.

Rows indicates the number of rows that may need to be scanned in the query process. This data is not necessarily accurate. It is a data of MySQL sampling statistics.

Extra indicates some additional information, usually showing whether the index is used, whether sorting is needed, whether temporary tables are used, etc.

OK, let’s start the case analysis.

A test table is created by using the storage engine created in the previous article. Here we insert 10 W test data. 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 there is only one primary key index in this table, 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 value of type is all, which means that the whole table has been scanned. You can see that the rows field shows 100332 entries. In fact, we only have 10W entries in total. Therefore, this field is only an estimate of MySQL, not necessarily accurate. The efficiency of this kind of full table scanning is very low, which needs to be optimized.

Next, we will add ordinary indexes to fields a and B respectively, and then look at the SQL after adding 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)

It seems that the above SQL is a little confused. The type actually shows that the index has just been added to the field a, and it is possible_ Keys also shows that there is a_ The index is available, but the key displays null, which means that MySQL will not actually use the a index. Why?

This is because select * also needs to go back to the primary key index to find the B field. This process is called back to the table. This statement will filter out 9W pieces of data that meet the conditions. That is to say, all 9W pieces of data need to be returned to the table, and only 10W pieces of data need to be scanned in the whole table It seems that our optimizer is not as good as scanning the whole table directly, at least eliminating the process of returning the table.

Of course, it doesn’t mean that the index won’t be hit as long as there is a return table operation. The key to using or not using the index lies in which kind of query MySQL thinks is cheaper. 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? U index It means that the a index is hit, which is a good choice because only 1000 pieces of data meet this SQL condition. MySQL thinks that even if 1000 pieces of data are returned to the table, the cost is lower than that of full table scanning, 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 you need to go back to the table. 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, so there is no need to return to the table. Obviously, this efficiency is higher than the above, so do not write select * easily, just query the fields needed by the business, so as to avoid returning to the table as much as possible.

Let’s look at 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 file sort, which means sorting is needed. This kind of data needs to be optimized. That is to say, after the data is found, it needs to be sorted by MySQL in memory. You need to know that the index itself is ordered, so generally speaking, you should make the best use of the order of the index, such as the following.


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 a composite index.


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 just mentioned that when a composite index is not created, it is a full table scan. Now in fact, it uses the overlay index, which also avoids the process of table return, that is, in (AB)_ Index) index to find the fields to query.

This article introduces how to use explain to analyze an SQL execution plan through several examples, and also mentions some common index optimization. In fact, there are more possibilities. You can also write an SQL by yourself, and then use explain to analyze and see what can be optimized.

Recommended Today

Review of SQL Sever basic command

catalogue preface Installation of virtual machine Commands and operations Basic command syntax Case sensitive SQL keyword and function name Column and Index Names alias Too long to see? Space Database connection Connection of SSMS Connection of command line Database operation establish delete constraint integrity constraint Common constraints NOT NULL UNIQUE PRIMARY KEY FOREIGN KEY DEFAULT […]