MySQL execution plan 【explain

Java foundation, MySQL, Springboot, Mybatis, Redis, rabbitMQ, etc.

Github address: github.com/Tyson0314/J…

Gitlab address: gitee.com/tysondai/Ja…

Introduction to the

This article describes how to obtain the execution plan of the SELECT statement by using the EXPLAIN command.

• The loading order of the table
• SQL query type
• What indexes might be used, and what indexes are actually used
• Number of rows read
• .

Explain execution plan contains the following fields: ID, select_TYPE, TABLE, PARTITIONS, type, possible_keys, key, KEY_len, ref, rows, filtered, and Extra.

With explain Extended + Show Warnings, you can provide some additional query optimization information on the basis of the original EXPLAIN, and get the optimized possible query statements (not necessarily the final optimization results).

First set up the test environment:

CREATE TABLE `blog` (
  `blog_id` int NOT NULL AUTO_INCREMENT COMMENT 'Unique blog ID -- primary key',
  `blog_title` varchar(255) NOT NULL COMMENT 'Post title',
  `blog_body` text NOT NULL COMMENT 'Blog content',
  `blog_time` datetime NOT NULL COMMENT 'Post time',
  `update_time` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  `blog_state` int NOT NULL COMMENT 'Blog status --0 deleted 1 normal',
  `user_id` int NOT NULL COMMENT 'user id'.PRIMARY KEY (`blog_id`)
) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8

CREATE TABLE `user` (
  `user_id` int NOT NULL AUTO_INCREMENT COMMENT 'User unique ID -- primary key',
  `user_name` varchar(30) NOT NULL COMMENT 'Username - cannot be repeated',
  `user_password` varchar(255) NOT NULL COMMENT 'User password'.PRIMARY KEY (`user_id`),
  KEY `name` (`user_name`)
) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8

CREATE TABLE `discuss` (
  `discuss_id` int NOT NULL AUTO_INCREMENT COMMENT 'Comment unique ID',
  `discuss_body` varchar(255) NOT NULL COMMENT 'Comment content',
  `discuss_time` datetime NOT NULL COMMENT 'Comment time',
  `user_id` int NOT NULL COMMENT 'user id',
  `blog_id` int NOT NULL COMMENT 'the post id'.PRIMARY KEY (`discuss_id`)
) ENGINE=InnoDB AUTO_INCREMENT=61 DEFAULT CHARSET=utf8
Copy the code

id

Indicates the order in which the select clause or operation table is executed in the query. The larger the ID value is, the higher the priority is.

explain select discuss_body 
from discuss 
where blog_id = (
    select blog_id from blog where user_id = (
        select user_id from user where user_name = 'admin'));
Copy the code

The three tables are nested in sequence, and it is found that the innermost subquery id is the largest and is executed first.

select_type

The select query type is used to distinguish complex queries, such as common query, union query, and sub-query.

1. SIMPLE: indicates the simplest select query statement. The query does not contain operations such as subquery or union difference set.
2. PRIMARY: SELECT the outermost table in the query (the outermost table with a subquery operates as PRIMARY).
3. SUBQUERY: the first SELECT in a SUBQUERY.
4. DERIVED: Driven SELECT subquery (subquery in FROM clause)
5. UNION: UNION is used after SELECT.

table

The name of the query table, which is not necessarily a real table, has an alias display alias, or may be a temporary table. When there are subqueries in the FROM clause, the table column is in the format derivenN>, indicating that the current query depends on the query whose ID is N and the query whose ID is N is executed first.

partitions

For non-partitioned tables, the value is NULL. If partitioned tables are queried, partitions that are matched by partitioned tables are displayed.

type

What type of query is used is a very important metric in SQL optimization.

system

When a table has only one row (system table), the amount of data is very small, and disk I/O is often not required, which is very fast. For example, the Mysql system table proxies_priv is loaded in memory when Mysql services are started. Querying this table does not require disk I/O.

const

In single-table operations, the query uses a primary key or unique index.

eq_ref

The primary key and unique index are used as association conditions for multiple table associated query. For each row in the user table (outer loop), only one row in the USER_Role table (inner loop) meets the join condition. As long as this row is found, the inner loop will be broken and the next round of the outer loop will be continued.

ref

Search criteria columns use indexes and do not have primary keys and unique indexes. Although an index is used, the value of the index column is not unique, so even if the first data is found using the index, you still cannot stop and do a small scan near the target value. But it has the advantage that you don’t need to scan the entire table, because the index is ordered, and even if there are duplicate values, the scan is done in a very small range.

ref_or_null

Similar to ref, additional searches are made for rows containing NULL values.

index_merge

Using index merge optimization, the query uses more than two indexes. Create a comment table with id as the primary key and value_id as a non-unique index. Run explain select content from comment where value_id = 1181000 and ID > 1000. The result shows that both id and value_id indexes are used in the query, and the value of type column is index_merge.

range

A scoped index scan, as opposed to a full index scan of index, is scoped and therefore superior to index. Things like BETWEEN, and, ‘>’, ‘<‘, in, and OR are range index scans.

index

Index includes select index columns and ORDER by primary keys.

1. Order by primary key. In this case, the entire table is scanned by index order, and the retrieved data is sorted by primary key, no additional sorting is required.

   

2. Select index column. Type is index, and extra is using index, also called index overwriting. The required data are in the index column, no need to query back to the table.

   

all

Full table scan with no index is the worst performance.

possible_keys

Possible indexes for this query. However, this index is not necessarily the index that will be used in the final query.

key

The exact index used in this query.

rows

Estimate the number of rows that need to be read to find the desired record. A more important data to evaluate SQL performance, mysql needs to scan the number of rows, a very intuitive display of SQL performance, in general, the smaller the rows value is the better.

filtered

The percentage of records returned by the storage engine that meet the criteria after filtering.

extra

Indicates additional information description. Let’s create two new tables for testing purposes.

CREATE TABLE `t_order` (
  `id` int NOT NULL AUTO_INCREMENT,
  `user_id` int DEFAULT NULL,
  `order_id` int DEFAULT NULL,
  `order_status` tinyint DEFAULT NULL,
  `create_date` datetime DEFAULT NULL.PRIMARY KEY (`id`),
  KEY `idx_userid_order_id_createdate` (`user_id`,`order_id`,`create_date`)
) ENGINE=InnoDB AUTO_INCREMENT=99 DEFAULT CHARSET=utf8

CREATE TABLE `t_orderdetail` (
  `id` int NOT NULL AUTO_INCREMENT,
  `order_id` int DEFAULT NULL,
  `product_name` varchar(100) DEFAULT NULL,
  `cnt` int DEFAULT NULL,
  `create_date` datetime DEFAULT NULL.PRIMARY KEY (`id`),
  KEY `idx_orderid_productname` (`order_id`,`product_name`)
) ENGINE=InnoDB AUTO_INCREMENT=152 DEFAULT CHARSET=utf8
Copy the code

using where

The column in the query is not covered by the index, and where filters the leading column that is not the index. Post-filtering (post-filtering) of the results returned by the storage engine generally occurs on the MySQL server, not the storage engine layer.

using index

The columns in the query are overwritten by the index, and the WHERE filter criteria conform to the leftmost prefix principle. You can directly find the qualified data through the index lookup, without the need to query the data back to the table.

Using where&Using index

The queried column is overwritten by the index, but the eligible data cannot be found through index lookup, but the eligible data can be found through index scan, and the data does not need to be queried back to the table.

There are two cases:

• The WHERE filter does not comply with the leftmost prefix rule

  

• A WHERE filter is a range of leading columns in an index column

  

null

Column is not covered by the index of queries, and where the filter is the leading column index, which is used to index, but part of the field is not covered by the index, must back to the table query these fields, the Extra to NULL.

using index condition

Index condition pushdown (ICP) filters the index using the WHERE condition, then finds all rows that match the index condition, and then filters the rows using other conditions in the WHERE clause.

If ICP is not used (set optimizer_switch=’ index_condition_PUSHdown =off’), then in Step 4, the where condition is not used to filter the index:

Set optimizer_switch=’index_condition_pushdown=on’

The following example uses ICP:

explain select user_id, order_id, order_status  
from t_order where user_id > 1 and user_id < 5\G;
Copy the code

With ICP off (set optimizer_switch=’index_condition_pushdown=off’), you can see the extra column using WHERE without index push-down.

Example: an index pushdown reference linking an index pushdown | an index pushdown graphic | an index pushdown optimization

using temporary

Temporary tables are used to hold intermediate results, common in Order BY and Group by. Typically, when group by and Order BY exist together and act on different fields, temporary tables are created to compute the final result set.

filesort

File sorting. An index cannot be used to complete a sort operation, which causes filesort:

• The order by field is not an index field
• Select query fields not all index fields
• The select query fields are indexed, but the order by and index fields are not the same

using join buffer

Nested Loop computations are required. This occurs when two associated tables join and the associated fields are not indexed. For example, the type of the inner layer and the outer layer are ALL and the rows are 4, which requires 4*4 calculation cycles. A common optimization is to add indexes on associated fields to avoid each nested loop.

This article takes a look at some of the best blogs out there, if you’re interested:

• Explain execution plan

Code word is not easy, if this article is good, you can like, let me know, support me to write a better article!