SQL optimization often makes mistakes, that's because you don't understand the usage of MySQL explain plan

1. Preparation

Prepare three tables, a character table, an equipment table, and a basic data table. Here we only show some fields needed in the tutorial. There must be more than these fields in the process of game development. I think everyone understands.

Role table:

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;

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;

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;

2. Introduction to the Explanation Plan

There are two ways to view the explain plan:

1. Command method: explain sql, or desc sql, both commands are fine. I think it is better to remember explain, the words are very direct.

2. Use the tool Navicat (I'm not familiar with other tools, but I guess there are some too). Click Explain in the query window. You don't need to add the keyword explain.

You can see that the result contains many columns, some of which are null and some of which have values. As long as we understand the explain plan, we can optimize the SQL in a targeted manner.

3. Detailed explanation of fields

There are quite a lot of fields in the explain plan. Navicat displays 12 fields. We need to pay special attention to some of them, and it is good to just know what is going on with some of them.

Official documentation explanation: https://dev.mysql.com/doc/refman/5.7/en/explain-output.html

1. Order of id execution

id is the execution order of select. The larger the id is, the higher the priority is and the earlier it is executed. If the id is the same, the following one will be executed first .

The reason is that when executing a subquery, the inner layer is checked 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 = 'Coriander' )
    ) 

From the execution plan above, we can see that the query of the role table is executed first, then equip is executed, and finally dict_equip is executed.

2. select_type select type

3. Table query involves tables or derived tables

The currently used tables for output can be of the following types:

<union M , N > ： The row data is the data after the union. The id is between m and n.

<derived*N*>: derived table

<subquery N >: Subquery

4. Partitions query involves partitions

It can only be used when using the partition table. This advanced function has not been used yet.

5. Type of query

Indicates how MySQL finds the required row in the table, also known as the "access type". Common types are as follows:

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

From left to right, worst to best

When optimizing, if the amount of data queried is large, you can use a full table scan to avoid using indexes.

If you are only querying a small amount of data, try to use indexes.

6. possible_keys: expected indexes to be used

When not associated with other tables, the query table is the possible index to use

7. key: the index used in the actual query process

Displays the index actually used by MySQL in the query. If no index is used, it is displayed as NULL

8. key_len

Indicates the number of bytes used in the index. This column can be used to calculate the length of the index used in the query.

9. ref shows which field of which table the index field of the table is associated with

Note: I added indexes to both equip and dict_equip tables, and the index column is n_equip_id

As can be seen from the above execution plan, the index is used first.

10. rows: Based on the table statistics and selection, roughly estimate the number of records or rows to be found or read. The smaller the value, the better.

For example, although there is no index on a column, it is unique. At this time, if the entire table is read during the search, the value is the same as the amount of data in the table. At this time, what you need to optimize is to read as few tables as possible. You can add indexes to reduce the number of rows read.

11. filtered: The percentage of rows returned as a percentage of rows read. The larger the value, the better.

For example, if the entire table contains 100 records, all the data in the table may be read, but only one record is matched. In this case, the percentage is 1. Therefore, you need to make this ratio as large as possible, that is, the data read should be as useful as possible, and avoid reading unused data, because IO is very time-consuming.

12. Extra

The following are the most common

use filesort: MySQL needs an extra pass to figure out how to retrieve the rows in sorted order. If this value is true, the index should be optimized.

use temporary: To resolve the query, MySQL needs to create a temporary table to hold the results. A typical case is when the query contains GROUP BY and ORDER BY clauses that list columns in different cases.

use index: Retrieve column information from a table using only the information in the index tree without further searching to read the actual rows. This strategy can be used when the query uses only columns that are part of a single index.

use where: where clause is used to limit which row

Summarize

The principle of SQL optimization is to shorten the time while ensuring correctness. The goal is certain. By pushing back on the goal, you can know that if you want to execute quickly, you must read as little data as possible. There are only two major ways to reduce the amount of data read: filtering and using indexes. Optimize within such rules, but note that indexes will take up extra space, so you need to balance the relationship between the two.

This is the end of this article about SQL optimization errors because you don’t understand the usage of MySQL explain plan. For more information about SQL optimization and MySQL explain plan, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope you will support 123WORDPRESS.COM in the future!