This time, read the Mysql execution plan thoroughly

Write it up front

This article mainly introduces the function of each field of mysql execution plan, and how to use mysql execution plan to optimize our Sql, this article is mainly for back-end development programmers, as well as students who need to deal with databases.

1. What isMysqlThe execution plan

An execution plan is how Mysql executes an Sql statement, including the order of Sql queries, whether or not indexes are used, and the index information used. An example:

The basic grammar

explain select.Copy the code

Some variants

explain extended select.Copy the code

The above statement converts the table execution plan into a SELECT statement. Show Warnings can be used to obtain the mysql optimizer optimized query statement.

explain partitions select.Copy the code

EXPLAIN for partition tables

2. Information contained in the execution plan

Different versions of Mysql and different storage engines have different execution plans, but the basic information is the same. The mysql execution plan contains the following information:

2.1 id

It’s a set of numbers. Represents the execution order of subqueries in a query.

• Id Same execution sequence from top to bottom.

• Different ids. A larger ID has a higher priority and is executed earlier.

• Id fornullIs a result set that does not need to be queried, and is often present in containsunionAnd other query statements.

2.2 select_type

Query types for each subquery, some common query types.

Table 2.3

When querying data from derived tables,

x is displayed indicating the corresponding execution plan ID.

2.4 partitions

Table partitioning, table creation can specify which column to partition the table by. Here’s an example:

create table tmp (
    id int unsigned not null AUTO_INCREMENT,
    name varchar(255),
    PRIMARY KEY (id))engine = innodb
partition by key (id) partitions 5;
Copy the code

2.5 type

Access type

• ALLScan all table data
• indexTraverse index
• rangeIndex range lookup
• index_subqueryUse ref in subqueries
• unique_subqueryUse eq_ref in subqueries
• ref_or_null 对NullThe index optimized ref
• fulltextUsing full-text indexes
• refUse non-unique indexes to find data
• eq_ref 在joinUse in queryPRIMARY KEYorUNIQUE NOT NULLIndex association.

• constA primary key or unique index is used and only one record is matched.
• system constThis is a special case of the connection type. The queried table is the system table.

2.6 possible_keys

Possible indexes that may or may not be used. If there is an index on the field involved in the query, the index will be listed. When this column is NULL, it is time to consider whether the current SQL needs to be optimized.

2.7 the key

Displays the actual index used by MySQL in the query. If no index is used, NULL is displayed.

TIPS: If an overwritten index is used in a query, it only appears in the key list

2.8 key_length

Index length char(), varchar()

(Character Set: UTF8MB4 =4, UTF8 =3, GBK =2,latin1=1)Copy the code

Other types of index length calculation formula: ex:

CREATE TABLE `student` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(128) NOT NULL DEFAULT ' '.`age` int(11),
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx` (`name`),
  KEY `idx_age` (`age`))ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4;
Copy the code

The name index length is utF8MB4, the column length is 128, and the field type is VARCHAR (128). key_length = 128 * 4 + 0 + 2 = 514;

null

2.9 ref

Represents the join match criteria for the above table, that is, which columns or constants are used to find values on indexed columns

2.10 rows

Returns the estimated number of result sets, which is not an exact value.

2.11 extra

The information of EXTRA is very rich, common ones are: 3. Use filesort to sort files Using non-indexed columns. 4. Use temporary to create a table

3 Reference Documents

Mysql official documentation

Dev.mysql.com/doc/refman/…