Use explain to optimize your MySQL performance

Time:2021-9-8

Explain features

  • The results returned by explain are table granularity, and each table outputs one row. Here, it represents a table in a broad sense, which can be a sub query or a result after union.
  • Not all explain queries do not execute. If the from clause contains subqueries, MySQL will actually execute subqueries to optimize the outer queries.
  • Explain cannot tell us how triggers, stored procedures, and UDFs affect queries
  • Explain use “filesort” for both memory sorting and temporary file sorting
  • Explain “using temporary” is used for both temporary tables on disk and in memory
  • Explain can only parse the select query, which is not supported for update and insert. We can use select to simulate the update operation and approximate the execution process of obtaining update

Columns in explain

id

Identifier of the select query. Each select is automatically assigned a unique identifier

select_type

select_ Type indicates the type of query. Its common values are:

  • Simple: indicates that this query does not contain a union query or a subquery
  • Subquery: the subquery contained in the select list, that is, the subquery not in the from clause
  • Derived: represents the select query contained in the from clause
  • Union: indicates that this query is the second and subsequent query of union
  • Union result: the select of the result retrieved from the union anonymous temporary table
  • Primary, indicating that this query is the outermost query
  • Dependent Union: the second or subsequent query statement in the union. Dependent means that select depends on the data found in the outer query
  • Dependent subquery: a subquery contained in the select list, but the subquery depends on the results of the outer query
table

Which table is queried? The association order executed by the MySQL query optimizer is not the same as that when we write SQL. Let’s talk about how MySQL optimizes the association query:

  • The MySQL query execution plan is always executed according to the rules of the left depth first tree, that is, the nested loop starts from a table and does not execute two branches at the same time like the balanced binary tree
  • In multi table Association, the same execution results can be obtained through a variety of different association sequences. The query optimizer will evaluate different sequences and select an association query with the lowest cost
  • If you don’t want the optimizer to change your association order, you can use the strong join keyword to enforce your association order
  • If there are too many associated tables, it exceeds optimizer_ search_ When the depth is limited, the optimizer evaluates that the execution cost of each association order is too high. At this time, it will choose the “greedy” search mode
type

The type field is important, which provides an important basis for judging whether the query is efficient. Through the type field, we can judge whether the query is a full table scan or an index scan. The performance comparison of type types. Generally speaking, the performance relationship of different type types is as follows:

ALL < index < range < ref < eq_ref < const < system < NULL

  • Null: this access means that MySQL can decompose query statements in the optimization phase, and there is no need to access tables or indexes in the execution phase
  • System: it is known in advance that there is only one piece of data in the whole table. This type is a special const type
#Because the backend in the table_ User is the primary key, so you can select at most one piece of data in the sub query, so the type of the outermost query is system and the type of the inner query is const
explain select * from (select * from backend_user where id = 1) a;
Copy code
  • Const: the equivalent query scan for the primary key or unique index returns only one row of data at most. Const query is very fast because it can be read only once
  • eq_ Ref: this type usually appears in multi table join queries, which means that each result in the previous table can only match one row of results in the subsequent table. Moreover, the comparison operation of the query is usually =, which is more efficient
  • Ref: this type usually appears in multi table join queries. For non unique or non primary key indexes, or queries using the leftmost prefix rule index, multiple values may be queried
  • Range: indicates to use the index range query to obtain some data records in the table through the index field range. This type usually appears in =, < >, >, > =, <, < =, is null, < = >, between, in() operations. However, for the same type = range query, there are differences in performance:
#Although they are all range queries, in fact, the second query is a query with multiple equivalent conditions
#For the first query, MySQL can no longer use other query indexes behind the column, while the second query can continue to use indexes
select id from actor where id > 45 and class_id = 3;
select id from actor where id in (44, 47, 48) and class_id = 3;
Copy code
  • Index: indicates that the full table scan through the index is similar to the all type. It avoids sorting. It is determined that it needs to bear the cost of reading the table according to the index order. If “using index” appears in the extra column, it indicates that the index is overwritten, and the cost is very small.
  • All: indicates full table scanning. This type of query is one of the worst performing queries. Generally, all rows are scanned from beginning to end, unless limit or extra is used, and “using distinct / not exists” is displayed in the column.
possible_keys

The indexes that may be selected in this query are determined according to the query columns and comparison operators, and may not be used in subsequent real queries

key

The exact index used in this query. If it is in possible_ It does not appear in keys but appears in key, which indicates that the optimizer may choose to overwrite the index for other reasons, so it is possible_ Keys reveals which index helps to search efficiently, and key shows which index can minimize the query cost.

key_len

Indicates the number of bytes of the index used by the query optimizer. This field can evaluate whether the composite index is fully used or only the leftmost fields are used. For example, we have built a composite index (col1, col2). Although the following two queries use the composite index, the corresponding key_ Len’s is just different. key_ Len shows the maximum possible length in the index field, not the actual number of bytes used by the data

select * from table1 where col1 = 1;
select * from table1 where col1 = 1 and col2 = 2;
Copy code
ref

This column shows the columns or constants used by the previous table to find values in the index of the key column record

rows

Rows is also an important field. The MySQL query optimizer estimates the number of data rows that SQL needs to scan and read to find the result set according to the statistical information. This value is very intuitive to show the efficiency of SQL. In principle, the fewer rows, the better.

  • By multiplying the rows value of each table, you can roughly estimate the number of rows to be checked in the whole query
  • This value is only an estimated value, not an actual value
filtered

Filtered is added in MySQL 5.1. It appears when explain extended is used. It indicates the percentage of data filtered by this query condition. Dividing rows by filtered can estimate the number of rows in the whole table.

Extra

Many additional information in expplain will be displayed in the extra field. The common contents are as follows:

  • Using filesort

When using filesort is available in extra, it means that MySQL needs additional sorting operations and cannot achieve the sorting effect through index order. Generally, using filesort is recommended to be optimized and removed, because such queries consume a lot of CPU resources. But explain won’t tell you whether MySQL will use file sorting or memory sorting:

--For example, we create an index as: key ` user_ product_ detail_ index` (`user_ id`, `product_ Name `, ` product `), then the following two queries

EXPLAIN SELECT * FROM order_ info ORDER BY product_ name;    --  Using filesort cannot be sorted by index and needs to be optimized
EXPLAIN SELECT * FROM order_ info ORDER BY user_ id, product_ name;--  No using filesort, sorting by index, optimization successful
Copy code
  • Using index

“Overwrite index scanning” means that the query can find the required data in the index tree without scanning the table data file, which often indicates good performance

  • Using where

This means that the MySQL server will filter the rows after the storage engine retrieves them. Generally, “using where” will benefit from different indexes

  • Using temporary

Queries use temporary tables, which generally occur in sorting, grouping and multi table joins. Temporary tables may be memory temporary tables or file temporary tables

partitions

Explain partitions is introduced in MySQL version 5.1 to display the partitions to be accessed by the query
I need a private letter about MySQL learning materials. Let me pay a little attention first

Recommended Today

The selector returned by ngrx store createselector performs one-step debugging of fetching logic

Test source code: import { Component } from ‘@angular/core’; import { createSelector } from ‘@ngrx/store’; export interface State { counter1: number; counter2: number; } export const selectCounter1 = (state: State) => state.counter1; export const selectCounter2 = (state: State) => state.counter2; export const selectTotal = createSelector( selectCounter1, selectCounter2, (counter1, counter2) => counter1 + counter2 ); // […]