Explain the detailed explanation of several key fields that need to be paid attention to when executing the plan

In useEXPLAINWhen analyzing MySQL queries, the following key fields are usually paid attention to to understand the execution plan and performance bottlenecks of the query. The following is a detailed explanation:

1. Fields that need attention in EXPLAIN

• id：
  • The query identifier. If it is a simple query, the value is 1; if it is a subquery or a joint query, there will be different values.
• select_type：
  • The type of query, for exampleSIMPLE(Simple query),PRIMARY(Main query),SUBQUERY(Subquery) etc.
• table：
  • Query the table name involved.
• partitions：
  • Query the partition involved (if the table uses partitions).
• type：
  • One of the most important fields, indicating how MySQL accesses data in tables. Common values ​​includeALL、index、range、refwait.
• possible_keys：
  • Possible indexes.
• key：
  • The index actually used.
• key_len：
  • The length of the index used (number of bytes).
• ref：
  • Show which column of the index is used, the common value isconst、funcorNULL。
• rows：
  • Estimate the number of rows to be scanned.
• filtered：
  • Indicates the percentage of rows filtered by query conditions.
• Extra：
  • Additional information, such asUsing where、Using index、Using temporarywait.

2. Common values ​​of type field

• system：
  • There is only one row of data in the table (system table), which is the best performance type.
• const：
  • Search by primary key or unique index, at most one row of data is returned. For example:

    SELECT * FROM table WHERE id = 1;
    

• eq_ref：
  • In conjunction table query, match using primary keys or unique indexes. For example:

    SELECT * FROM table1 t1 JOIN table2 t2 ON  = ;
    

• ref：
  • Find data using non-unique indexes. For example:

    SELECT * FROM table WHERE key_column = 'value';
    

• fulltext：
  • Full-text index was used.
• ref_or_null：
  • Similar toref, but containsNULLSearch of values. For example:

    SELECT * FROM table WHERE key_column = 'value' OR key_column IS NULL;
    

• index_merge：
  • Index Merge is used, that is, the result merging of multiple indexes.
• unique_subquery：
  • Unique indexes are used in subqueries.
• index_subquery：
  • Non-unique indexes are used in subqueries.
• range：
  • Use indexes for range search. For example:

    SELECT * FROM table WHERE id BETWEEN 1 AND 100;
    

• index：
  • Full index scan, that is, scan the entire index tree.
• ALL：
  • Full table scans have the worst performance and usually require optimization.

3. Common values ​​for Extra fields

• Using where：
  • UsedWHEREConditional filtering data.
• Using index：
  • Covering Index is used, that is, the columns query are all in the index.
• Using temporary：
  • Temporary tables are used, which usually occur when sorting or grouping.
• Using filesort：
  • File sorting is used, which usually occurs when index sorting cannot be used.
• Using join buffer：
  • Join Buffer is used, which usually occurs during joint table query.
• Impossible WHERE：
  • WHEREConditions are always false, for exampleWHERE 1 = 0。

4. Focus at work

• type：
  • Make sure that query uses efficient types as possible (e.g.const、eq_ref、ref、range),avoidALL(Full table scan).
• key：
  • Make sure the query uses the appropriate index.
• rows：
  • The fewer rows are estimated to be scanned, the better the query performance.
• Extra：
  • avoidUsing temporaryandUsing filesort, these usually mean performance issues.

5. Example analysis

EXPLAIN SELECT * FROM users WHERE age > 30;

Output result:

+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | users | ALL  | NULL          | NULL | NULL    | NULL | 1000 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+

• typeforALL, indicates full table scanning and needs optimization.
• ExtraforUsing where, means that it has been usedWHEREConditional filtering.

6. Summary

• Focus ontype、key、rowsandExtraField.
• Make sure the query uses the appropriate index and avoid full table scans and temporary tables.
• according toEXPLAINThe result is to adjust the query statement or index design and optimize performance.

This is the article about several key fields that need to be paid attention to in the explanation execution plan. For more relevant explanation execution plan keyword fields, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!