SoFunction
Updated on 2025-04-14

How to use MySQL Explain to analyze SQL execution plan

MySQL Explain Analysis SQL Execution Plan

When optimizing SQL query performance, it is crucial to understand the execution plan of a query. Provided by MySQLEXPLAINTools can help us gain insight into the execution process of query statements, index usage, and potential performance bottlenecks. This article will introduce in detail how to use EXPLAIN to analyze SQL execution plans and explore the meaning of each important field and optimization suggestions.

1. What is EXPLAIN

EXPLAIN is a built-in analysis tool in MySQL to demonstrate the execution plan of query statements. By executingEXPLAIN SELECT ..., we can get information about how the query accesses tables, which indexes are used, and the data filtering process. With this information, developers can optimize query and index design in a targeted manner, thereby improving query performance.

2. Important fields of EXPLAIN output

When an EXPLAIN statement is executed, MySQL returns a result set containing multiple fields. The following lists common fields and their meanings:

  • id: The identifier of each SELECT clause in the query, the greater the value, the higher the priority. For complex or nested queries, id can help identify the execution order of individual subqueries.
  • select_type: The type of query, such as SIMPLE (simple query), PRIMARY (main query), SUBQUERY (subquery), etc. Understanding the query type helps identify the query structure.
  • table: Displays the currently accessed table name or alias.
  • partitions: Display matching partition information (such as when using partition tables).
  • type: Access type is an important indicator to measure query efficiency. Common values ​​include:
    • ALL: Full table scanning, the lowest efficiency;
    • index: Full index scan;
    • range: index range scan;
    • ref: non-unique index lookup;
    • eq_ref, const: Use primary key or unique index to directly locate records, with the highest efficiency.
  • possible_keys: Displays a list of indexes that may be used in the query.
  • key: The actual index used. If this field is NULL, it means that no index is used.
  • key_len: Use the byte length of the index to help determine whether the index is fully utilized.
  • ref: Displays columns or constants matching the index, used to judge the query filtering conditions.
  • rows: Estimate the number of rows to be scanned. The larger the value, the higher the query cost.
  • filtered: Based on the percentage of data filtering in the table, the lower the percentage means the larger the amount of data to be filtered.
  • Extra: Supplementary information, such as Using index, Using where, Using temporary, Using filesort, etc. Pay special attention to Using temporary and Using filesort, which usually indicate a performance bottleneck in the query.

3. Use EXPLAIN to analyze the query

3.1 Basic usage

Just add EXPLAIN before the query statement. For example:

EXPLAIN
SELECT order_id, order_date, amount
FROM orders
WHERE customer_id = 1001;

After execution, you will get a table showing how MySQL parses and executes this query.

3.2 Analyze query execution plan

  • Check access type (type): Try to avoidALL(full table scan), recommended to userange、reforconst。
  • Observe index usage:Checkpossible_keysandkeyFields, ensuring that the columns involved in the query criteria are indexed and that MySQL actually uses these indexes.
  • Evaluate the number of scanned rows (rows): Larger row counts may cause degradation in query performance, consider reducing the number of scanned rows by optimizing WHERE conditions or adjusting indexes.
  • Pay attention to Extra information: If you seeUsing temporaryorUsing filesort, indicating that there may be performance bottlenecks caused by sorting or grouping operations, and you can consider improving by establishing composite indexes or optimizing SQL logic.

3.3 Sample Optimization

Assume that the following query exists:

EXPLAIN
SELECT *
FROM orders
WHERE YEAR(order_date) = 2024
  AND customer_id = 1001;

The query isorder_dateThe function is used on it, resulting in the inability to utilize the index. Optimization suggestions are as follows:

Avoid function calls: Rewrite the query conditions into a range query:

EXPLAIN
SELECT order_id, order_date, customer_id, amount
FROM orders
WHERE order_date BETWEEN '2024-01-01' AND '2024-12-31'
  AND customer_id = 1001;

Create a suitable composite index:existorder_dateandcustomer_idCreate index on:

CREATE INDEX idx_order_date_customer ON orders (order_date, customer_id);

After checking with EXPLAIN, you should seekeyThe fields are displayed asidx_order_date_customer, and the number of scan lines is significantly reduced.

4. Advanced usage of EXPLAIN

4.1 EXPLAIN FORMAT=JSON

Starting with MySQL 5.6, EXPLAIN supports JSON format output, which can describe execution plans in more detail:

EXPLAIN FORMAT=JSON
SELECT order_id, order_date, amount
FROM orders
WHERE customer_id = 1001;

JSON output provides richer information and is useful for automation tools and complex query analysis.

4.2 Analyze complex queries

For complex queries that contain subqueries, JOIN, or UNION, you can view the execution plan of each subquery separately, identify bottlenecks in each part, and gradually optimize them.

5. Summary and optimization suggestions

  • Check query with EXPLAIN: Regularly use EXPLAIN to analyze SQL execution plans, and promptly discover potential problems such as full table scanning, inefficient index use, and temporary tables.
  • Targeted optimization: Adjust SQL statements and index design based on the information output by EXPLAIN, and pay special attention to avoid using functions or implicit type conversions in WHERE conditions.
  • Combined with actual scenarios: EXPLAIN provides estimated data, and actual performance needs to be comprehensively judged in combination with test and monitoring data.

By proficient in using the EXPLAIN tool, you can have a more intuitive understanding of the execution details of MySQL queries and optimize them in a targeted manner to provide strong support for database performance improvement. I hope this article can provide you with practical guidance and reference in query optimization and database tuning!

This is the article about how to use MySQL Explain to analyze SQL execution plans. For more related contents of MySQL Explain SQL execution plans, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!