preface

  • How to write efficient SQL statementsExplainAnalysis of execution plans, as to what an execution plan is, and how to write efficient SQL, this article will explain one by one.

The execution plan

  • The execution plan is a query plan made by the database based on SQL statements and related table statistics, which is automatically analyzed by the query optimizer.

  • Use the Explain keyword to simulate the optimizer’s execution of SQL queries to see how MySQL is handling your SQL statements, analyze performance bottlenecks in your SELECT statements or table structures, and let us know why select is inefficient so we can improve our queries.

  • Explain results are as follows:

  • The following is a detailed description of each columnid,type,key,rows,extra.

id

  • The id column number is the serial number of the SELECT. It can also be understood as the identification of the order of SQL execution.
    • Different ID values: For query only, the ID number increases. A larger ID value has a higher priority and is executed earlier.
    • If the ID values are the same, the commands are executed from top to bottom.
    • Id column NULL: indicates that this is a result set and does not need to be used for queries.

select_type

  • Query types are mainly used to distinguish common query, joint query, sub-query and other complex queries.

    • Simple: indicates that the query does not include the union operation or subquery. The select_type of the outermost query is simple and there is only one.
      explain select * from t3 where id=3952602;
Copy the code
    • Primary: select_type (select with union or subquery);
    explain select * from (select * from t3 where id=3952602) a ;
Copy the code
    • Derived: a subquery that appears in a list, also called a derived table; Mysql or recursively executes these subqueries, putting the results in temporary tables.
      explain select * from (select * from t3 where id=3952602) a ;
Copy the code
    • Subquery: Any subquery that appears anywhere except in the FROM clause can be a subquery.
    explain select * from t3 where id = (select id from t3 whereid=3952602);Copy the code
  • Union: If the second select appears after the union, it is marked as union; If union is included in the subquery of the FROM clause, the outer select is marked as derived. 
    explain select * from t3 where id=3952602 union all select * from t3;
Copy the code
    • Union result: The select that gets the result from the UNION table. Since it does not participate in the query, the ID field is null.
      explain select * from t3 where id=3952602 union all select * from t3;
Copy the code
  • Dependent Union: Like union, occurs in a union or union all statement, but this query is affected by external queries;
  • Dependent subquery: Similar to the dependent Union, the first SELECT in a subquery whose query is affected by external table queries.

table

  • Indicates which table the explain row is accessing.
    • If the query uses an alias, the alias is displayed here;
    • If no operation on the data table is involved, this displays null;
    • If the table is enclosed in Angle brackets, it is a temporary table. The following N is the ID of the execution plan, indicating that the result is generated from the query.

    • , enclosed in Angle brackets, is similarly a temporary table, indicating that the result is from the result set M,N of the union query.

type

  • The access type, which is how MySQL decides to find rows in the table.

  • From good to bad: system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index < span style = “max-width: 100%; clear: both; min-height: 1px; In general, make sure the query is at least range and preferably ref.

    1. System: there is only one row in the table (equal to the system table). This is a const exception.

    2. Const: Uses a unique index or primary key to indicate that the index was found once, and const is used to compare the primary key or unique index. Because you only need to match one row of data, everything is fast. If you place the primary key in the WHERE list, mysql can convert the query to a const.

    3. Eq_ref: Unique index scan, for each index key, only one row in the table matches it. Common for primary key or unique index scans.

    4. Ref: a non-unique index scan that returns all rows matching a single value. It is also an index in nature.

    5. Fulltext: full-text index search. Full-text index has a high priority. If both full-text index and normal index exist at the same time, mysql preferentially uses full-text index regardless of cost.

    6. Ref_or_null: Similar to the ref method, but with the added comparison of null values.

    7. Index_merge: indicates that more than two indexes are used in a query. The optimization method of index merging is used to obtain the intersection or union. Common AND and or conditions use different indexes.

    8. Unique_subquery: Used in a WHERE subquery that returns a unique value with no duplicate values;

    9. Index_subquery: Used for in subqueries that use a secondary index or list of IN constants. Subqueries that may return duplicate values can be de-duplicated using the index.

    10. Range: index range scan, commonly used in queries that use operators such as >,<,between,in, and like.

    11. Index: scans the index tree from beginning to end.

    12. All: traverses the entire table to find matching rows (Index and all are read from the Index, and all is read from the disk)

    13. NULL: MySQL breaks down statements during optimization and executes without even accessing tables or indexes.

possible_keys

  • Displays the indexes that may be used by the query.

key

  • Shows which index the query actually uses to optimize access to the table;

  • If select_Type is index_merge, more than two indexes may appear, and only one of other select_types may appear.

key_len

  • The length of the index used to process the query, representing the number of bytes used in the index. From this value, you can figure out what part of a multi-column index is actually used.
  • Note: key_len displays the maximum possible length of the index field, not the actual length, i.e. key_len is calculated from the table definition, not retrieved from the table. In addition, key_len only calculates the length of the index used by the WHERE condition, and sorting and grouping do not count to key_len if indexes are used.

ref

  • Shows which field or constant is used with the key.

    1. If the constant equivalent query is used, const is displayed here.

    2. If it is a join query, the execution plan of the driven table shows the associated fields of the driven table.

    3. If the condition uses an expression or a function, or if the condition column is implicitly converted, it might be shown as func.

rows

  • Indicates a rough estimate of the number of rows that MySQL needs to read to find the desired target record based on table statistics and index selection.

extra

  • Additional information that is important but not suitable for display in other columns.

  • This column can display dozens of different types of information, including:

type instructions
Using filesort MySQL has two ways to generate ordered results, either by sorting or by Using indexes. Extra uses filesort to generate ordered results. Sorting is done in memory whenever possible. In most cases it’s faster to sort by index, so it’s generally a good time to consider optimizing the query. Sorting operations with files, which can be the result of ordery by, Group BY statements, can be cpu-intensive and can be improved by selecting the appropriate index to sort the query results.
Using temporary Use temporary tables to store intermediate results. This is commonly used in GROUP BY and ORDER BY operations. It is common to see this as a sign that the query needs to be optimized, and to avoid the use of temporary tables on hard disks if it cannot be avoided.
Not exists MYSQL optimizes LEFT JOIN, and once it finds a row that matches the LEFT JOIN standard, it no longer searches.
Using index Note The query overwrites the index. You can obtain the information from the index tree (index file) without reading the data file. Using WHERE indicates that the index is used to perform a lookup of the index key value. Without using WHERE, the index is used to read data rather than perform a lookup. This is done by the MySQL service layer, but there is no need to go back to the table to query the records.
Using index condition This is a new feature in MySQL 5.6 called “index conditional push”. To put it simply, MySQL used to not be able to perform like operations on indexes, but now it can. This reduces unnecessary I/O operations, but can only be used on secondary indexes.
Using where The WHERE clause is used to restrict which rows will match the next table or be returned to the user.Pay attention toUsing WHERE indicates that the MySQL server will return the storage engine to the server layer and then apply the WHERE condition filtering.
Using join buffer The connection cache is used:Block Nested Loop, the join algorithm is block nested loop join;Batched Key AccessThe join algorithm is batch indexed join
impossible where The value of the WHERE clause is always false and cannot be used to get any tuples
select tables optimized away In the absence of the GROUP BY clause, optimizing MIN/MAX operations based on indexes, or optimizing COUNT(*) operations for MyISAM storage engine, does not have to wait until the execution phase to perform the calculation, which is completed at the generation stage of the query execution plan.
distinct Optimize the distinct operation to stop finding the same value once the first matching tuple is found

filtered

  • This column appears when you use Explain Extended, which is available by default in versions after 5.7 and is not required.
  • This field represents the percentage of the data returned by the storage engine that is filtered through the server layer to meet the number of records in the query. Note that it is a percentage, not the specific number of records.

Limitations of MySQL execution plan

  1. EXPLAIN does not tell you about triggers, stored procedures, or the impact of user-defined functions on queries;
  2. EXPLAIN does not consider various caches;
  3. EXPLAIN does not show optimization work done by MySQL when executing queries.
  4. Some statistics are estimated and not exact;
  5. EXPALIN can only interpret SELECT operations.

Query plan case analysis

Execution order

  1. (id=4) : [select id, name from t2] : select_type = union;

  2. (id = 3) : 【select id, name from t1 where address = ’11’ 】 : DERIVED because it is a subquery contained in the FROM statement, where address = ’11’ can be retrieved by the compound index idx_name_email_address, so type is index.

  3. (id = 2) : [select id from t3] : [select id from t3]

  4. (id = 1) : [select name,… d2 from… d1] : Select_type PRIMARY indicates that the query is the outermost query. Derived3 indicates that the query result is derived from a derived table (select result with id = 3).

  5. (id = NULL) : [… union…] : represents the stage of reading rows from the temporary table of union. “union 1, 4” in the table column indicates that the union operation is performed with the select results of ID =1 and ID =4.