A standard summary of analyzing the performance of a sql 02/24 Update SLTechnology News&Howtos

A standard summary of analyzing the performance of a sql

2025-02-24 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

Shulou(Shulou.com)06/01 Report--

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

In fact, there are many articles on the Internet have introduced the use of explain in detail, this article combines examples and principles, try to let you have a better understanding, believe me, you should have a special harvest after reading carefully.

Explain translates to explain and is called execution plan in mysql, that is, you can see from this command that mysql decides how to execute the sql after being analyzed by the optimizer.

Speaking of optimizer, mysql has a powerful built-in optimizer. The main task of the optimizer is to re-optimize your sql and execute it as cheaply as possible, such as scanning fewer lines and avoiding sorting. What does it take to execute a sql statement? I introduced optimizer-related in the previous article.

You may ask, when do you usually use explain? in most cases, some sql with slow query efficiency are found from the slow query log of mysql to use explain analysis, and some are used to optimize mysql, such as adding indexes, using explain to analyze whether the added indexes can be hit, and in business development, when the requirements are met. You may need to choose a more efficient sql through explain.

So how do you use explain? it's simple, just add explain before sql, as shown below.

As you can see, explain will return about 10 fields, different versions of the returned fields are slightly different, each field represents a specific meaning, this article I do not intend to introduce each field in detail, there are many things, I am afraid it is not easy for you to remember, it is better to understand a few important fields first.

Among them type, key, rows, Extra these fields I think are more important, we next through the concrete example 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, such as full table scan (all), traversal index (index), interval 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 represents the index name that will actually be used by the query process.

Rows indicates the number of rows that may need to be scanned during the query, which is not necessarily accurate and is a sample of mysql statistics.

Extra represents some additional information, usually showing whether indexes are used, whether sorting is needed, whether temporary tables are used, and so on.

All right, let's officially start the case analysis.

Or use the storage engine created in the previous article to create a test table. We insert 10 w pieces of test data here, and 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 take a look at the following query statement and notice that the table currently has only one primary key index and no normal 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 whole table has been scanned. Notice that there are 100332 items in the rows field. In fact, we have only 10w items in total, so this field is only an estimate of mysql and may not be accurate. This kind of full table scan is very inefficient and needs to be optimized.

Next, let's add a normal index to fields an and b, respectively, and then look at several sql after adding the index.

Does the above sql look a little confused? type shows that field a has just been indexed, and possible_keys also shows that a_index is available, but key shows null, indicating that mysql does not actually use an index. Why?

This is because if select *, you also need to go back to the primary key index to find the b field. This process is called returning to the table. This statement will filter out 9w pieces of data that meet the criteria, that is, these 9w pieces of data need to be returned to the table, and the whole table is scanned only 10w pieces of data. Therefore, in the view of mysql's optimizer, it is better to scan the whole table directly, or at least avoid the process of returning to the table.

Of course, it does not mean that as long as there is a table return operation, the index will not be hit. The key lies in which kind of query mysql thinks is cheaper. Let's modify the where condition in the above sql slightly.

This time the type value is range, and the key is a_index, which means that the an index has been hit, which is a good choice, because only 1000 pieces of data meet this sql condition, and 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 we need to go back to the table and look at the following statement.

The value in this Extra is Using where; Using index, which means that the query uses an index, and the fields to be queried can be obtained in the index without returning the table, which is obviously more efficient than the above, so don't easily write select *, just query the fields needed by the business, so you can avoid returning the table as much as possible.

Let's take a look at one that needs to be sorted.

A Using filesort is returned in this Extra, which means sorting is needed, which needs to be optimized. That is to say, after finding the data, you also need mysql to sort it in memory. You should know that the index itself is ordered, so generally speaking, you should make use of the order of the index, such as writing like this.

Let's create another composite index.

This sql has just mentioned above that when a composite index is not created, it is a full table scan, but now it actually uses an overlay index, which also avoids the process of returning to the table, that is, the fields to be queried can be found on the (ab_index) index.

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

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.