Abstract: The database explanation plan clarifies the SQL execution process, shows the details of the execution, as long as the database tells us the problem according to the picture of the analysis can be.

This article is shared from huawei cloud community “easy to understand mysql execution plan, no longer afraid of SQL optimization”, author: cilancilanet chat game.

Recently some SQL optimization of work to do, although remember some commonly used SQL optimization techniques, but in the work is not enough, so I need with the help of tools, database of interpretation illuminates the SQL execution plan, shows the implementation details, we just according to the database to tell us the analysis of the problem take a good, But explaining a plan is not always easy to understand, so today we will learn the meaning of some parameters of explaining a plan.

1. Preparation

Prepare three tables, one character table, one equipment table, and one basic data table. Here are only some fields needed in the tutorial. There will be more fields in the game development process, I think we all understand.

Character sheet:

CREATE TABLE `role` (
  `n_role_id` int DEFAULT NULL,
  `s_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
Copy the code

Equipment list:

CREATE TABLE `equip` (
  `n_equip_id` int DEFAULT NULL,
  `s_equip_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
  `n_config_id` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
Copy the code

Equipment configuration table:

CREATE TABLE `dict_equip` (
  `n_equip_id` int DEFAULT NULL,
  `s_desc` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
Copy the code

2. Understanding the interpretation plan for the first time

There are two ways to view the explanation plan:

1, the way of command: explainsql, or descsql, both commands can be used, I think remember explain is better, the words are very direct.

2, with the help of the tool Navicat (other unfamiliar, estimated also have), click the query window to explain, can not add the keyword explain

As you can see, the result contains a number of columns, some null and some value, as long as we understand whether the interpretation plan can be targeted to optimize SQL.

3. Field details

There are quite a few fields to explain the plan. Navicat shows 12 fields, some of which we need to focus on, and some of which we just need to know.

The official documents to explain: dev.mysql.com/doc/refman/…

1. Order of id execution

Id indicates the order in which the select is executed. A larger ID has a higher priority and is executed earlier.

The reason is that subqueries are executed to look up the inner layer first and then the outer layer

SELECT de.* FROM dict_equip de WHERE de.n_equip_id = ( SELECT n_equip_id FROM equip e WHERE e.n_role_id = ( SELECT N_role_id FROM role r WHERE r.s_name = 's_id')Copy the code

As can be seen from the above execution plan, role query was performed first, then DICt_EQUIP was executed

2, select_type select type

Table Indicates the related table or derived table

The current output of the table in use can be one of the following:


: the row data is the data id in m and N after the union
,n>

< derived * N * > : derived table

<subquery_N_> : subquery

4. Partitions involved in the query

You can only use it when using partitioned tables. You haven’t used this advanced feature yet.

5. Type Indicates the query type

Access type indicates the way MySQL finds rows in a table. The common types are as follows:

Performance: all < index < range < index_merge < ref_or_NULL < ref < eq_ref < system/const

From left to right, from worst to best

During optimization, if the volume of data queried is large, you can use full table scan instead of using indexes.

Use indexes if you are querying only small amounts of data.

Possible_keys: Possible index

When not associated with other tables, the table is queried for possible indexes

7. Key: the index used in the actual query

Displays the actual index used by MySQL in the query. If no index is used, NULL is displayed

8 key_len.

Represents the number of bytes used in the index, which is used to calculate the length of the index used in the query

Ref shows which table fields are associated with which table fields

Note: I have added indexes to both EQUIP and DICT_EQUIP tables, respectively, with the column N_EQUIP_ID

As you can see from the execution plan above, indexes are used first

10. Rows: Approximate the number of rows to find or read based on table statistics and selection. The smaller the value, the better

For example, if there is no index on a column, but all columns are unique, then when the search is full table read, this value is the number of data in the table. In this case, you need to optimize to read as few tables as possible, can increase the index, reduce the number of rows read

11, Filtered: percentage of the number of rows returned as a result of the read rows, the greater the value, the better

In this case, the percentage is 1. Therefore, you need to make the ratio as large as possible. In other words, you need to read as much data as possible.

12, extra

Common ones are the following

Use Filesort: MySQL needs an extra pass to figure out how to retrieve rows in sort order, if this is the value, the index should be optimized.

Use temporary: To solve a query, MySQL needs to create a temporary table to hold the results. A typical case is when a query contains GROUP BY and Order Derby clauses that can list columns in different cases.

Use index: Retrieves column information from a table using only the information in the index tree without further searching by reading the actual rows. This policy can be used when queries use only columns that are part of a single index

Use WHERE: The WHERE clause is used to restrict which line

4, summarize

SQL optimization principle is under the condition of guarantee the right to shorten time, goal is to determine, through the goal back can know want to execute soon as less as possible to read data, reduce the manner of data read only two kinds of filter and use the index, within the scope of such a rule is optimized, but pay attention to the index will take up extra space, A good balance should be struck between the two.

Click to follow, the first time to learn about Huawei cloud fresh technology ~