EXPLAIN the usage of MySQL Execution Plan Analysis Tool

EXPLAIN the usage of MySQL Execution Plan Analysis Tool
Familiar with SQL Execution Plan analysis skills for DBAs is vital for quickly locating database performance problems. The following describes how to analyze MySQL execution plans.
1. EXPLAIN how to use EXPLAIN: explain select ......

Variant: 1. explain extended select ...... Decompile the execution plan into a SELECT statement and run show warnings to obtain the query statement optimized by the MySQL optimizer.

2. explain partitions select ...... Used to EXPLAIN the Partition Table

Example:

1. Execution id

Contains a group of numbers. If the IDs are the same, they can be considered as a group and executed sequentially from top to bottom. In all groups, the higher the id value, the higher the priority. 2. Select _ type

Type Displays the access type and is an important indicator. The result values are as follows: system> const> eq_ref> ref> fulltext> ref_or_null> index_merge> unique_subquery> index_subquery> range> index> ALL. ALL: Scan full table index: scan ALL index tree range: Scan part of the index, index range scan, index scanning starts at a certain point, return rows that match the value range, query ref that is common in between, <,>, and so on: Non-unique index scan, returns all rows that match a single value. Query eq_ref is common when a non-unique index is used, that is, a unique index prefix. For each index key, a table has only one record matching it. Common in primary key or unique index scanning const, system: these types of access are used when MySQL optimizes a certain part of the query and converts it to a constant. For example, if the primary key is placed in the where list, MySQL can convert the query to a constant. System is a special case of the const type. When the queried table has only one row, system is used. NULL: MySQL breaks down statements during optimization, and does not even need to access tables or indexes during execution.

3. possible_keys indicates which index MySQL can use to locate rows in the table. If an index exists in the fields involved in the query, the index will be listed, but not necessarily used by the query. key: displays the indexes actually used by MySQL in the query. If no index is used, it is shown as NULLTIPS: If the index is overwritten in the query, the index only appears in the key list. key_len indicates the number of bytes used in the index. You can use this column to calculate the length of the Index Used in the query. 6. ref indicates the join matching condition for the above table, that is, which columns or constants are used to find the value of the index column 7. rows indicates that MySQL estimates the number of rows to be read Based on the table statistics and index selection. 8. extra contains additional information that is not suitable for displaying in other columns but is very important. II. The limitations of the MySQL execution plan are summarized as follows: 1. EXPLAIN will not tell you about triggers, stored procedures, or the impact of user-defined functions on queries. EXPLAIN does not consider various Cache3.EXPLAIN and cannot display the optimization work performed by MySQL during query execution. 4. partial statistical information is estimated, not accurate value 5. EXPALIN can only explain the SELECT Operation. Other operations must be rewritten to SELECT to view the execution plan.