What are the key metrics to look for in an EXPLAIN output (type, key, rows, Extra)?

Key metrics for EXPLAIN commands include type, key, rows, and Extra. 1) The type reflects the access type of the query. The higher the value, the higher the efficiency, such as const is better than ALL. 2) The key displays the index used, and NULL indicates no index. 3) rows estimates the number of scanned rows, affecting query performance. 4) Extra provides additional information, such as using filesort prompts that it needs to be optimized.

introduction

When we talk about database optimization, EXPLAIN command is a powerful tool in our hands, which helps us peek into the execution plan of SQL queries. Today we will explore in-depth the key indicators in EXPLAIN output: type , key , rows and Extra . These metrics not only reveal how queries are executed, but also provide valuable clues for us to optimize our database. Read this article and you will learn how to interpret these metrics and use them to improve your database performance.

Review of basic knowledge

EXPLAIN command is used in MySQL to display the execution plan of SQL statements. It helps us understand information such as how the query is executed, which indexes are used, and the estimated number of rows. Understanding the basic concepts of this information is crucial for our subsequent in-depth analysis.

• type : Indicates how MySQL looks up rows in tables. It reflects the access type of the query, from optimal to worst, in order: system , const , eq_ref , ref , range , index , ALL .
• key : Displays the index that MySQL decides to use. If no index is used, NULL will be displayed here.
• rows : Estimate the number of rows that MySQL needs to scan. This number is crucial to assess the efficiency of a query.
• Extra : Contains additional information that is not suitable for display in other columns, such as the use of temporary tables, file sorting, etc.

Core concept or function analysis

Definition and function of type

type field is one of the most intuitive metrics in EXPLAIN output, and it tells us how MySQL accesses rows in a table. The higher the value of type , the higher the query efficiency. For example, const means only one row is accessed, while ALL means full table scan, which is the least efficient access type.

Let's look at a simple example:

 EXPLAIN SELECT * FROM users WHERE id = 1;

The output may show that type is const because id is a primary key and MySQL can locate this line directly.

Definition and function of key

The key field shows the index that MySQL chooses to use when executing a query. If there is no appropriate index, MySQL will select full table scan, and key will be displayed as NULL . Choosing the right index is critical to improving query performance.

For example:

 EXPLAIN SELECT * FROM users WHERE name = 'John';

If there is an index on the name field, key may display the name of the index.

Definition and function of rows

The rows field represents the number of rows that MySQL estimates to scan. This number directly affects the performance of the query, because the more rows are scanned, the longer the query takes.

For example:

 EXPLAIN SELECT * FROM users WHERE age > 30;

If the age field has no index, rows may display a larger number indicating that a large number of rows need to be scanned.

The definition and function of Extra

The Extra field contains additional information that may be very helpful for us to understand how queries are performed. For example, if you see Using temporary or Using filesort , this usually means that the query needs to be optimized.

For example:

 EXPLAIN SELECT * FROM users ORDER BY name;

If name field is not indexed, Extra may display Using filesort , indicating that MySQL requires file sorting, which will affect performance.

Example of usage

Basic usage

Let's look at a simple query and its EXPLAIN output:

 EXPLAIN SELECT * FROM users WHERE id = 1;

The output may be as follows:

 ---- ------------- ------- ------- --------------- --------- --------- ------- ------ ------- 
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
 ---- ------------- ------- ------- --------------- --------- --------- ------- ------ ------- 
| 1 | SIMPLE | users | const | PRIMARY | PRIMARY | 4 | const | 1 | |
 ---- ------------- ------- ------- --------------- --------- --------- ------- ------ -------

Here we can see type is const , key is PRIMARY , and rows is 1, indicating that MySQL directly found this line through the primary key index.

Advanced Usage

Now let's look at a more complex query:

 EXPLAIN SELECT * FROM users u JOIN orders o ON u.id = o.user_id WHERE u.age > 30;

The output may be as follows:

 ---- ------------- ------- -------- --------------- --------- --------- --------------- ------ ------------- 
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
 ---- ------------- ------- -------- --------------- --------- --------- --------------- ------ ------------- 
| 1 | SIMPLE | u | range | PRIMARY,age | age | 4 | NULL | 100 | Using where |
| 1 | SIMPLE | o | ref | user_id | user_id | 4 | test.u.id | 10 | |
 ---- ------------- ------- -------- --------------- --------- --------- --------------- ------ -------------

Here we can see type is range and ref , key is age and user_id , and rows are 100 and 10 respectively. This shows that MySQL first finds the user that meets the criteria through the age index, and then finds the relevant order through user_id index.

Common Errors and Debugging Tips

Common errors when using EXPLAIN include:

• Ignore warnings in Extra fields such as Using filesort or Using temporary .
• No appropriate index is created for commonly used queries, resulting in key being NULL .
• The rows field is misunderstood, thinking it is the number of rows actually scanned, when in fact it is the estimated value.

Methods to debug these problems include:

• Read the Extra field carefully and optimize according to the prompts, such as adding an index to the sorted field.
• Analyze the key fields to make sure the query uses the appropriate index, and if not, consider adding the index.
• Verify the accuracy of the rows field by actually executing the query and using the SHOW PROFILE command.

Performance optimization and best practices

In practical applications, optimizing the key indicators of EXPLAIN output can significantly improve database performance. Here are some optimization suggestions:

• Ensure that the commonly used query conditions have appropriate indexes and reduce the value of rows .
• Avoid full table scanning, optimize the value of type field, and try to use const , eq_ref or ref as much as possible.
• Pay attention to the warnings in the Extra field and optimize according to the prompts, such as adding an index to the sorted field.

Let's see a comparison before and after optimization:

 -- Before optimization EXPLAIN SELECT * FROM users WHERE name LIKE '%John%';

-- Optimized EXPLAIN SELECT * FROM users WHERE name LIKE 'John%';

Before optimization, type may be ALL and rows may be a larger number, because LIKE '%John%' cannot use index. After optimization, if name field has an index, type may become range and the value of rows will be significantly reduced.

In terms of programming habits and best practices, it is recommended:

• Regularly use EXPLAIN to analyze and query, and promptly discover and optimize performance bottlenecks.
• Maintain the readability and maintenance of the code, and ensure that the index and query logic are clear and easy to understand.
• Based on actual business needs, rationally design indexes to avoid performance degradation caused by excessive indexing.

By deeply understanding and applying key metrics of EXPLAIN output, we can more effectively optimize database queries and improve the overall performance of the application.

The above is the detailed content of What are the key metrics to look for in an EXPLAIN output (type, key, rows, Extra)?. For more information, please follow other related articles on the PHP Chinese website!