MySQL explain Execution Plan Optimization

MySQL explain Execution Plan Optimization
Explain execution plan

Introduction to MySQL optimization:

1. First discover the problem (slow query, profile)
2. If indexes are used and no indexes are used, you can quickly find data by using indexes.
3. What is an index (an index is a data structure that is sorted for quick search)
4. Manage indexes (view, create, and delete indexes)
5. The data structure of the index itself (B-TREE structure and clustering structure)
6. How MySQL uses these indexes (the explain tool), it is necessary to identify whether the index is properly used to prevent index abuse.

    

   Note: indexes are helpful for queries, but they are not helpful for updates. The index should be properly established.

    

   MySQL tuning is a general idea (interview questions): Through the above steps, you can tune MySQL, as long as the above steps are repeated, mySQL can work in optimal conditions.

    

   Profile definition: A tool provided by MySQL that can analyze the SQL statement execution process. Through this tool, you can get a general idea of how MySQL will execute the SQL statements passed by the user.

   Usage

    

   When using the command, you only need to add the keyword "explain" or "desc" before the corresponding SQL statement.

   # Desc select * from tableName where id = 1000000;

   # Explain select * from tableName where id = 1000000;

   The execution of the preceding two SQL statements is equivalent.

    

   You can first compare the execution plans with and without indexes:

   When no_index is not indexed:

    

   Rows: indicates the number of rows that the query results meet the SQL statement.

    

   If the id has a primary key index:

    

   Type: Key Analysis

    

7. All indicates full table scan. This value is usually returned if no index is used.

   Full table scan: Compare rows by row in one row, and return records that meet the conditions.

    

8. Const indicates that the primary key index is used. During development, try to see this. Constant search

    

   3. range indicates a range operation. You can also use an index when the executed SQL statement is a range query.

   Meaning of range: The index is a sorted structure. Along this ordered structure, you can use the index to capture a piece of ordered data.

    

    

   Interview question: what fields are suitable for indexing? (What are the considerations when creating an index ?)

   A:

9. You need to create an index for the query field following the where condition.
10. Indexes can also be used in cases such as (sorting order range group), so you can add an index after the sorting field.

    Note: not all fields are indexed after the where condition, because the index itself is overhead.

     

11. Index indicates that the index is used.

    When recording statistics are used, you can directly use the index to return the number of rows of records, so there is no need to make statistics on the number of rows of disk files.

     

12. System. When there is only one row of records in the table, MySQL considers that the record information can be directly returned without using the index file. It usually appears in a row of records (self-built tables) or system-level tables (mysql. user) when MySQL is started, some system-level tables are preferentially loaded into the memory for a cache.

     

13. Null, which may occur. If this occurs, it also means good. It is not easy to appear.

    Conclusion: null, system, const, range, index, all

    Note: Do not show all

This article permanently updates the link address: