MySQL Explain Analyze for Advanced Query Performance Insights

EXPLAIN ANALYZE is a query performance analysis tool introduced in MySQL 8.0.18. It helps locate performance bottlenecks by actually executing queries and recording indicators such as time-consuming and row count of each step. 1. It combines execution plan with actual operation data; 2. The output contains key information such as Query_time, Execution time, Rows_produced_per_step, Time_per_step and Loops; 3. It can identify problems such as full table scanning, temporary tables, file sorting, and excessive nesting loops; 4. It is often used for complex query debugging, comparing statements with side effects in SQL writing and testing environments. When using it, you should pay attention to avoid directly executing write operations in the production environment, and combine the output data to optimize the index and query structure.

EXPLAIN ANALYZE is a very practical tool when you want to dig into MySQL query performance issues. It can not only tell you how the query is executed, but also show the time overhead of each step in the actual operation process, helping you quickly locate performance bottlenecks.

What is EXPLAIN ANALYZE?

EXPLAIN ANALYZE is a feature introduced in MySQL 8.0.18, combining EXPLAIN 's execution plan analysis and actual query performance data during execution. Unlike traditional EXPLAIN only displays estimated information, ANALYZE will actually execute queries and record key indicators such as the actual time consumption and number of rows at each step.

It is very simple to use:

 EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 123;

How to understand the output of EXPLAIN ANALYZE?

The output content of EXPLAIN ANALYZE is more detailed than that of ordinary EXPLAIN , mainly including the following aspects:

• Query_time : The total time of the entire query.
• Execution time : The time of the SQL execution phase (excluding parsing and optimization).
• Rows_produced_per_step : The number of rows generated by each execution step.
• Time_per_step : The specific time-consuming of each step.
• Loops : How many times does this step loop (such as nested loop joins).

The key to understanding this data is to find out the "most time-consuming" step. For example, a table scans a large number of rows, or uses temporary tables or file sorting, these may be performance problems.

For example, if you see an output snippet like this:

 -> Index range scan on orders using idx_customer_id (cost=10.50 rows=100) (actual time=0.050..0.120 rows=100 loops=1)

This means that the index scanning efficiency is pretty good. But if you see:

 -> Table scan on orders (cost=10000 rows=100000) (actual time=10.000..50.000 rows=100000 loops=1)

Then we need to consider whether we should add indexes or optimize query conditions.

Common performance issues and optimization suggestions

The following are some problems that are easy to find through EXPLAIN ANALYZE and corresponding optimization ideas:

• Full table scan
  Check if there is a suitable index available. If not, consider adding indexes to columns that are often used for querying.

• Using temporary tables
  Usually occurs in GROUP BY or DISTINCT operations. Try optimizing field selection or adjusting sorting logic.

• File sorting (Using filesort)
  Indicates that the index cannot be used to sort. This can be solved by establishing a composite index on the ORDER BY field.

• Too many nested loops
  If the outer result set is large, it will cause the inner layer to execute multiple times. You can consider overwriting the query structure or using more efficient connection methods (such as hash join, but MySQL support is limited).

• Execution time is too long but the number of returned lines
  This indicates that there may be unnecessary calculation or filtering operations to check whether the WHERE conditions are reasonable and avoid meaningless scanning of large tables.

Use scenarios and precautions

• Suitable for complex query debugging
  Especially when dealing with complex logic such as multi-table associations, subqueries, GROUP BY and ORDER BY, EXPLAIN ANALYZE can help you find the real performance bottleneck.

• Pay attention to execution side effects
  Because ANALYZE will actually execute SQL, if it is UPDATE, DELETE or SELECT with a greater impact, it is best to use a read-only account to test first, or run it in a test environment.

• Comparing the effects of different writing methods
  You can use it to compare which of the two versions of SQL is faster and which resource consumption is lower, rather than just "look simpler".

Basically that's it. Once you master EXPLAIN ANALYZE , you will have a "perspective mirror" that can clearly see the true performance of the query. Although it can't help you write the best SQL directly, it can let you know where to improve.

The above is the detailed content of MySQL Explain Analyze for Advanced Query Performance Insights. For more information, please follow other related articles on the PHP Chinese website!