Do you really know how to use EXPLAIN in MySQL?

EXPLAIN is a powerful tool in MySQL database for query performance analysis and optimization. Through EXPLAIN, you can view the execution plan of the MySQL query and understand how MySQL executes your query statement. This article will introduce the use of EXPLAIN in detail to help you better understand and optimize MySQL queries.

Why use EXPLAIN?

Before we dive into EXPLAIN, let’s understand why we need to use it. MySQL is a relational database management system used to store and retrieve large amounts of data. When you execute a SQL query, MySQL needs to decide how to get the required data, which usually involves scanning tables, using indexes, merging result sets, etc. The quality of query performance is closely related to the MySQL execution plan.

The main function of EXPLAIN is to help you analyze the execution plan of the query statement, find out the places that may cause performance problems, and optimize the query. Through EXPLAIN, you can get the following information:

  • Table reading order: MySQL determines the order in which tables are accessed during queries, which is critical to performance. You can see the tables involved in the query and the order in which they were read.
  • Access type: This tells you how MySQL accesses the table, including full table scan, index scan, range scan, etc.
  • Indexes used: You can see which indexes are used to speed up queries.
  • Number of rows returned: This displays the estimated number of rows returned by the query.
  • Join operation: If the query involves multiple tables, you can learn the type of join operation (such as nested loop join, join operation, etc.).
  • Conditional processing: You can view conditional filtering, that is, how MySQL handles conditions in the WHERE clause.

Use EXPLAIN

Using EXPLAIN is very simple, just add the **EXPLAIN** keyword before the SQL query. Here’s an example:

sqlCopy code
EXPLAIN SELECT * FROM employees WHERE department_id = 10;

This will return a table containing information about the query. Let’s understand in detail what each column means:

  • id: This is the serial number of the query. If the query contains subqueries, they will be displayed here.
  • select_type: This indicates the type of query. Common types include **SIMPLE(simple query), PRIMARY(outermost query), SUBQUERY** (subquery), etc.
  • table: This is the name of the table being accessed.
  • partitions: If the table uses partitions, the partition information will be displayed here.
  • type: This is the type of table access, usually involving full table scan, range scan, index scan, etc. Performance is closely related to this column.
  • possible_keys: This column shows indexes that may be used to speed up queries.
  • key: This is the actual index used.
  • key_len: Displays the number of bytes used in the index. Shorter indexes are usually faster.
  • ref: This is the column compared to the index, if an index is used in the column it will appear here.
  • rows: This column displays the estimated number of query result rows.
  • filtered: This is the percentage of rows filtered by the index.
  • Extra: This column contains other important information about the query, such as **Using where (using WHERE conditions), Using index ** (index used), etc.

Analyze the output of EXPLAIN

Now let’s look at a sample EXPLAIN output and analyze it:

sqlCopy code
 + ---- + ------------- + ------------- + ------- + -------- ------- + --------- + --------- + ------ + ------ + -------- ----- +
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
 + ---- + ------------- + ------------- + ------- + -------- ------- + --------- + --------- + ------ + ------ + -------- ----- +
| 1 | SIMPLE | employees | ref | department_id | key_idx | 4 | const| 6 | Using where |
 + ---- + ------------- + ------------- + ------- + -------- ------- + --------- + --------- + ------ + ------ + -------- ----- +

In this example:

  • **id** is 1, indicating that this is the sequence number of the query.
  • select_typeisSIMPLE, indicating that this is a simple query.
  • tableisemployees, indicating the table being accessed.
  • typeisref, indicating that this is a range scan.
  • possible_keysis a department_id that represents an index that may be used to speed up queries.
  • keyiskey_idx, indicating the actual index used.
  • **key_len** is 4, indicating the number of bytes used in the index.
  • refisconst and represents the column to be compared to the index.
  • **rows** is 6, indicating the estimated number of query result rows.
  • **filtered** is an empty percentage, indicating that no index filtering is used.
  • ExtraisUsing where, indicating that the WHERE condition is used.

Optimize query

By analyzing the output of EXPLAIN, you can identify performance bottlenecks in your queries and take steps to optimize them. Here are some common optimization suggestions:

  1. Use appropriate indexes: Make sure the columns on the table have appropriate indexes to speed up queries. Analyzing the **possible_keys and key** columns can help you determine whether the correct index is being used.
  2. Reduce full table scans: The **type** column shows how the table is accessed. Try to avoid full table scans and try using index scans or range scans.
  3. Note the WHERE condition: **Using where in the Extra column Indicates the use of a WHERE condition. Optimizing WHERE** conditions can significantly improve performance.
  4. Consider partitioning the table: If the table is very large, consider partitioning it to reduce the scope of the query.
  5. Use appropriate data types: Table columns should use appropriate data types. Do not store string data in integer columns.
  6. Cache query results: If queries do not change frequently, consider using cache to store query results to reduce the load on the database.
  7. Regular database maintenance: Regular database maintenance, including index rebuilding, table optimization, etc.

Conclusion

EXPLAIN is a powerful tool for query performance analysis and optimization in MySQL. By analyzing query execution plans, you can identify performance issues in your queries and take appropriate actions to improve performance. Optimizing queries is a key task in database management and application development. I hope the information in this article can help you better understand and optimize MySQL queries.

In order to better help everyone understand the content of the article, the editor has sorted out a mind map of the above article content, as follows:

syntaxbug.com © 2021 All Rights Reserved.