In-depth analysis of MySQL execution plans

Preface

In the previous interview process, when asked about the execution plan, many people didn’t know what it was. Some even thought that the execution plan and execution time were the same concept. Today, let's take a look at what an execution plan is and what are its uses?

What is an execution plan?

To put it simply, the execution plan is the performance of SQL when it is executed in the database. It is usually used in scenarios such as SQL performance analysis and optimization.

1. What can the execution plan tell us?

• How SQL uses indexes
• Execution order of join queries
• Query scanned data function

2. Contents of the Implementation Plan

The output of the SQL execution plan may be multiple lines, each line represents an operation on a database object

1. ID column

• If the data in the ID column is a set of numbers, it indicates the order in which the SELECT statements are executed; if it is NULL, it means that this row of data is the result set generated by the UNION operation of two other SQL statements.
• If the ID values ​​are the same, it means that the SQL execution order is from top to bottom as shown.
• When the ID values ​​are different, the larger the ID value, the higher the priority, and the earlier it will be executed.

Demo

You can see that the execution plan above returns 3 rows of results. The value of the id column can be regarded as the sequence number of the SELECT operation in SQL.

Since there is only one SELECT in the above SQL, all ids are 1. Therefore, we need to read the execution plan from top to bottom.

According to our SQL statement, we think the execution order is a, b, c, but from the above figure, we can see that Mysql does not complete the table association operation in the order written in SQL.

The execution order of the tables is a, c, b. This is because the MySQL optimizer will adjust the actual order of table associations based on the statistical information of the indexes in the table.

2. SELECT_TYPE column

3. TABLE columns

The following results are included:

Output the name of the table where the data row is located. If the table has an alias, the alias is displayed.
<union M,N>: The result set generated by querying the union with ID M and N
<derived N>/<subquery N> : results generated by the query with ID N

4. PARTITIONS column:

Query which partition the matching records come from. For partitioned tables, display the partition ID of the query.
For non-partitioned tables, this is NULL.

5. TYPE column

The performance is listed as follows from highest to lowest:

6. Extra Column

Contains additional information about how MySQL executes queries

7. POSSIBLE_KEYS column

Indicates which indexes MySQL can use to optimize queries

Indexes on columns involved in the query will be listed, but they may not be used.

8. KEY column

The index actually used by the query optimizer to optimize the query

If there is no available index on the table, it will be displayed as NULL

If the query uses a covering index, the index appears only on the Key column.

9. KEY_LEN column

Displays the number of bytes used by the MySQL index. If there are three columns in a joint index and the total length of the three columns is 100 bytes, Key_len may be less than 100 bytes, such as 30 bytes. This means that not all columns of the joint index are used during the query, only the first one or two columns are used.

• Indicates the maximum possible length of the index field
• The length of Key_len is calculated from the field definition, not the actual length of the data

10. Ref Column

Indicates the columns or constants used by the current table when querying using the index in the Key column record

11. rows

• Indicates the number of rows that MySQL estimates based on index statistics (for associated queries, it shows the number of rows required for each nested query)
• The value of Rows is a statistical sampling result and is not very accurate.

12. Filtered columns

• Indicates the percentage of rows returned to the number of rows to be read
• The larger the value of the Filtered column, the better (the larger the value, the closer the number of rows actually read is to the number of rows required to be returned)
• The value of the Filtered column depends on statistical information, so it is also not very accurate and is only a reference value.

3. Limitations of execution plan

• Unable to show the impact of stored procedures, triggers, and UDFs on queries
• Unable to use EXPLAIN to analyze stored procedures
• Earlier versions of MySQL only supported parsing of SELECT statements.

Summarize

The above is the full content of this article. I hope that the content of this article will have certain reference learning value for your study or work. If you have any questions, you can leave a message to communicate. Thank you for your support for 123WORDPRESS.COM.