How to analyze the performance of a sql

This article will introduce how to use explain to analyze a sql.

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. Trust me, take it seriously. You should have special gains after reading it.

explain translates to explain. It is called an execution plan in mysql. That is, you can use this command to see how mysql decides to execute the sql after being analyzed by the optimizer.

Speaking of the optimizer, let me say one more thing. MySQL has a powerful built-in optimizer. The main task of the optimizer is to optimize the SQL you write and execute it at the lowest cost possible. For example, scan fewer rows, avoid sorting, etc. What have you experienced when executing a sql statement? I have introduced the optimizer in my previous article.

You may ask, when do you usually use explain? In most cases, some SQL with relatively slow query efficiency is extracted from the slow query log of mysql to use explain analysis, and some are It is when optimizing mysql, such as adding indexes, and using explain to analyze whether the added index can be hit. In addition, during business development, if the needs are met, you may need to use explain to choose one. More efficient sql.

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. The fields returned by different versions are slightly different. Each field represents a specific meaning. I am not going to detail each field in this article. There are a lot of things in the introduction, and I’m afraid it won’t be easy for you to remember. It’s better to understand a few important fields first.

I think the fields type, key, rows, and Extra are more important. We will 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 represents the way MySQL accesses data. Common ones include full table scan (all), index traversal (index), interval query (range), constant or equivalent query (ref, eq_ref), primary key and other values. Query (const), when there is only one record in the table (system). Below is a ranking of efficiency from best to worst.

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

key represents the index name that will actually be used in the query process.

rows represents the number of rows that may need to be scanned during the query process. This data is not necessarily accurate and is a data of MySQL sampling statistics.

Extra represents some additional information, usually showing whether indexes are used, whether sorting is required, whether temporary tables will be used, etc.

Okay, let’s officially start the example analysis.

Still use the storage engine created in the previous article to create a test table. We insert 10 w pieces of test data here. 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, pay attention to this The table currently has only one primary key index, and no ordinary index has been created yet.

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, indicating that the entire table has been scanned. Please note that the rows field displays 100332 entries. In fact, we only have a total of 100,000 pieces of data, so this field is just an estimate of mysql. Not necessarily accurate. The efficiency of this full table scan is very low and needs to be optimized.

Next, we add ordinary indexes to fields a and b respectively, and then look at the several 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 seem a little confusing? The type actually shows that an index has just been added to field a, and possible_keys also shows that a_index is available, but the key shows null, indicating that mysql actually It doesn't use a index, why?

This is because if you select *, you need to go back to the primary key index to find the b field. This process is called table return. This statement will filter out 90,000 pieces of data that meet the conditions, which means that these 9,000 pieces of data are all A table return operation is required, and a full table scan only has 100,000 pieces of data. Therefore, in the view of the MySQL optimizer, it is not as good as a direct full table scan, at least it eliminates the table return process.

Of course, this does not mean that as long as there is a table return operation, the index will not be hit. The key to whether to use an index or not depends on which 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_index, which means that the a index is hit. This is a good choice because there are only 1000 pieces of data that meet this SQL condition. MySQL considers 1000 pieces of data to be considered. Table return is also cheaper than full table scan, 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 the table needs to be returned. 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)

这个 Extra 中的值为 Using where; Using index ，表示查询用到了索引，且要查询的字段在索引中就能拿到，不需要回表，显然这种效率比上面的要高，所以不要轻易写 select * ，只查询业务需要的字段即可，这样可以尽可能避免回表。

再来看一个需要排序的。

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)

这个 Extra 中返回了一个 Using filesort，意味着需要排序，这种是需要重点优化的的，也就是说查到数据后，还需要 mysql 在内存中对其进行排序，你要知道索引本身就是有序的，所以一般来讲要尽量利用索引的有序性，比如像下面这样写。

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)

我们再创建一个复合索引看看。

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)

这条 sql 刚刚在上面也有讲到过，在没有创建复合索引的时候，是走的全表扫描，现在其实是利用了覆盖索引，同样是免去了回表过程，即在 （ab_index） 索引上就能找出要查询的字段。

这篇文章通过几个实例介绍了如何使用 explain 分析一条 sql 的执行计划，也提到了一些常见的索引优化，事实上还有更多的可能性，你也可以自己去写一个 sql ，然后使用 explain 分析，看看有哪些是可以被优化的。

这篇文章我断断续续写了有三四天了，本来准备了更多的例子，但每次都是写了一部分，思路也打乱了，好了，有问题欢迎在下面留言交流，文章对你有帮助，点个赞表示鼓励支持。

更多MySQL相关技术文章，请访问MySQL教程栏目进行学习！

The above is the detailed content of How to analyze the performance of a sql. For more information, please follow other related articles on the PHP Chinese website!