Author: IT Wang Xiaoer

Blog: itwxe.com

Why does a query SQL statement execute slowly? What index was used during execution? What is the general execution flow of SQL when multi-table query? Explain the implementation plan for each guest officer one by one.

MySQL installation mode and version

First of all, you need to install MySQL to see these two articles. Xiao Er demonstrates the use of Docker installation mode:

  • Install MySQL5.7.26 on Linux(CentOS7)
  • Docker builds MySQL and mounts data

The MySQL database version installed by Primary 2 is 5.7.36.

Second, Explain tool introduction

We often encounter SQL statements that are slow to query in a production environment, so how do we know why SQL statements are slow to execute? This is where the Explain execution plan is needed to analyze our statement.

By using the Explain keyword, you can simulate the optimizer to execute an SQL query that returns information about the execution plan instead of executing the SQL, although if the FROM contains a subquery, the subquery will still be executed, putting the results into a temporary table.

In general, use Explain to find out how MySQL handles your SQL statements and analyze performance bottlenecks in queries or table structures. Then the execution plan can be known by Explain:

  • The read order of the table
  • Operation type of data read operation
  • Which indexes are likely to be used
  • Which indexes are actually used
  • References between tables
  • Each table estimates how many rows will be executed

Sample tables used in this article

CREATE TABLE `author` (
  `id` int(11) NOT NULL,
  `name` varchar(45) DEFAULT NULL,
  `update_time` datetime DEFAULT NULL.PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `author` (`id`, `name`, `update_time`) VALUES (1.'itwxe'.'the 2022-01-12 19:27:18'), (2.'admin'.'the 2022-01-12 19:27:18'), (3.'superAdmin'.'the 2022-01-12 19:27:18');

CREATE TABLE `blog` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(10) DEFAULT NULL.PRIMARY KEY (`id`),
  KEY `idx_title` (`title`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `blog` (`id`, `title`) VALUES (1.'blog1'), (2.'blog2'), (3.'blog3');

CREATE TABLE `author_blog` (
  `id` int(11) NOT NULL,
  `author_id` int(11) NOT NULL,
  `blog_id` int(11) NOT NULL,
  `remark` varchar(255) DEFAULT NULL.PRIMARY KEY (`id`),
  KEY `idx_author_blog_id` (`author_id`,`blog_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `author_blog` (`id`, `author_id`, `blog_id`) VALUES (1.1.1), (2.2.1), (3.1.2);
Copy the code

4. Variations of Explain

The syntax of Explain is very simple; you just need to prefix the query statement with the Explain keyword.

explain select * from author where id = 1;
Copy the code

If you find that your version is missing partitions or filtered column field information, it is because those two columns were not available in previous versions of MySQL5.7. Rows * filtered/100 estimates the number of rows that will join with the previous table in Explain (the previous table refers to the table in Explain whose ID value is smaller than the current table ID value).

There is a way to see these two fields in the lower version, and these are the two variants of Explain that Xiao Ji said.

  • Explain Extended: Provides additional filtered columns of query-optimized information over the lower version of MySQL EXPLAIN.
  • Explain Partitions: An additional partition field is provided in addition to earlier versions of MySQL EXPLAIN, which partitions will be accessed by the query if it is based on a partitioned table.

The show Warnings command executed immediately after explain can get the optimized query statement to see what the optimizer optimized. Of course, the optimized SQL does not necessarily conform to the SYNTAX of SQL, but MySQL can recognize it and execute it.

5. Columns in Explain

Below small 2 he said for each column in the Explain information, of course, the value of each column will only introduce several common, if not mentioned each friend can view Explain the official document: dev.mysql.com/doc/refman/…

1. id

The id column represents the serial number of the select. If there are several SELECT, there are several ids, and the order of ids increases according to the order in which the SELECT appears. The larger the ID column is, the higher the execution priority is.

explain select * from author where id = (select author_id from author_blog where id = 2);
Copy the code

You can see that the subquery ID is 2, so execute the subquery first and then execute the outer query.

2. select_type

The select_Type column indicates the type of query for the corresponding row.

1) SIMPLE: SIMPLE query, excluding sub-query and union.

explain select * from author where id = 1;
Copy the code

2) PRIMARY: the outermost select in a complex query.

3) SUBQUERY: SUBQUERY included in select (not in the FROM clause).

4) DERIVED: Subqueries contained in the from clause. MySQL stores the results in a temporary table, also known as a derived table.

Use this example to understand the PRIMARY, SUBQUERY, and DERIVED types.

Close # MySQL57.New feature for merge optimization of derived tablesset session optimizer_switch = 'derived_merge=off';

explain select (select 1 from blog where id = 1) from (select 1 from author where id = 1) tmp;
Copy the code

Don’t ask why xiao 2 wrote this statement, everything is just for demonstration effect

Enable merge optimization for derived tables by default in MySQL5.7, and you will see a different effect.

set session optimizer_switch = 'derived_merge=on';
Copy the code

5) UNION: the second and subsequent select in UNION.

6) UNION RESULT: select the RESULT from UNION temporary table.

Use this example to understand the UNION, UNION RESULT type.

explain select id from author where id = 1 union select id from blog where id = 2;
Copy the code

3. table

The table column indicates which table the explain row is accessing.

When there are subqueries in the FROM clause, the table column is in the

format, indicating that the current query depends on the query id=N. Therefore, the query id=N is executed first.

When there is a union, the value of the table column of the Union RESULT is

, and 1 and 2 represent the IDS of the select rows participating in the union.
,2>

4. type

The Type column represents the association type or access type, which is the approximate range of data row records that MySQL determines how to find rows in the table.

The order from best to worst is: system > const > eq_ref > ref > range > index > ALL. Generally speaking, it is necessary to ensure that the query reaches the range level and preferably the ref level.

In addition to the priority values above, NULL means that MySQL is able to decompose the query during the optimization phase without having to access the table or index during the execution phase. For example, if you select a minimum value in the index column, you can do this by looking up the index separately without accessing the table during execution.

explain select min(id) from author;
Copy the code

1) const, system: MySQL can optimize part of a query and convert it to a constant (see the result of show Warnings). Select * from primary key; select * from constant; select * from primary key; System is a special case of const; it is system if there is only one match in the table

explain select * from (select * from author where id = 1) tmp;

show warnings;
Copy the code

2) eq_ref: if all parts of the primary key or unique key index are joined, only one record will be returned. This is probably the best type of join besides const, and it does not appear in simple SELECT queries.

explain select * from author_blog left join blog on author_blog.blog_id = blog.id;
Copy the code

3) ref: compared with eq_REF, it does not use a unique index, but uses the partial prefix of a normal index or a joint index. The index must be compared with a certain value, and more than one row matching the condition may be found.

  • . Simple select query, title is normal index (not unique index)
explain select * from blog where title = 'blog1';
Copy the code

  • Associative table query, idx_author_blog_id is the joint index of author_id and blog_id, using the author_id part of the left prefix of author_blog.
explain select author_id from author left join author_blog on author.id = author_blog.author_id;
Copy the code

A scan typically occurs in, between, >, <, >=, <=, etc., using an index to retrieve rows in a given range.

explain select * from author where id > 1;
Copy the code

5) index: Scan the whole index can get the result, general is to scan a secondary index or joint index, index tree root node this scan will not start quickly find, but directly to the secondary indexes or joint of the index leaf node traversal and scanning, speed or slower, the query is commonly used to cover index (index) cover, secondary indexes is compared commonly small, So this is usually faster than ALL.

Overwrite index (index overwrite) definition: Overwrite index is not a type of index, but a secondary index or a joint index contains all the fields that need to be queried. There is no need to go back to the table to query the data row to obtain the value of other fields.

MySQL > insert into MySQL; MySQL > insert into MySQL; insert into MySQL;

explain select * from blog;
Copy the code

6) ALL: full table scan, scan ALL the leaf nodes of your cluster index. Normally this would need to be optimized by adding an index.

explain select * from author;
Copy the code

5. possible_keys

The possible_keys column indicates which indexes the query might use to find, but the final query might not use indexes.

When explaining, possible_keys column may contain indexes that can be used, but key column may display NULL. The reason is that MySQL considers that the query speed is not as fast as full table scan after the query cost calculation, and finally selects full table query.

If the column is NULL, there is no associated index. In this case, you can improve query performance by examining the WHERE clause to see if you can create an appropriate index, and then using Explain to see the effect.

6. key

The key column indicates which index MySQL actually uses to query the table.

If no index is used, the column is NULL. To force MySQL to use or ignore indexes in the possible_keys column, use force index and ignore index in the query.

For example, the blog table forces idx_title and idx_title.

7. key_len

The key_ len column represents the number of bytes used by MySQL in the index. This value can be used to figure out which columns are used in the index.

explain select * from author_blog where author_id = 1;

explain select * from author_blog where author_id = 1 and blog_id = 2;
Copy the code

In the author_blog table idx_author_blog_id is the joint index of author_id and blog_id. Author_id and blog_id are both int types, and the int type occupies 4 bytes. Key_len =4 indicates the use of the AUTHOR_id column in the first SQL, and key_len=8 in the second SQL indicates the use of the author_id and blog_id columns.

Key_lenth Computer Rules:

  • String, char(n) and vARCHar (n). In versions after 5.0.3, n represents the number of characters, not the number of bytes. In UTF-8, a number or letter is 1 byte and a Chinese character is 3 bytes.

    • Char (n) : The length of a character3 * nBytes.
    • Varchar (n) : The length is if Chinese characters are stored3 * n + 2Bytes. Since vARCHar is a variable length string, the additional 2 bytes are used to store the length of the string.

  • Numeric types

    • Tinyint: 1 byte
    • Smallint: 2 bytes
    • Int: 4 bytes
    • Bigint: 8 bytes

  • Time to type

    • Date: 3 bytes
    • Timestamp: 4 bytes
    • Datetime: 8 bytes

  • If the field is allowed NULL, 1 byte is required to record whether the field is NULL. For example, if char(n) is allowed to be NULL, the length of the stored Chinese character is 3 x N + 1.

The maximum length of an index is 768 bytes. If the string is too long, mysql extracts the first half of the string from the left prefix index.

8. ref

The ref column represents the column or constant used by the table to find the value in the index of the key column record. Common examples are const(constant), field name (e.g. film.id).

9. rows

The rows column represents the number of rows that MySQL estimates to be read and examined. Note that this is an estimate, not the number of rows in the final result set.

10. Extra

The Extra column represents some additional information. Common important values are as follows:

1) Using index:

explain select blog_id from author_blog where author_id = 2;
Copy the code

2) Using WHERE: A where statement is used to process the result, and the queried column is not covered by the index.

explain select * from author where name = 'itwxe';
Copy the code

3) Using index condition: the column in the query is not completely covered by the index.

explain select * from author_blog where author_id > 1;
Copy the code

MySQL needs to create a temporary table to process the query. This situation is generally to optimize, the first is to think of using indexes to optimize.

  • Author. name has no index and a temporary table is created to be distinct.
explain select distinct name from author;
Copy the code

  • Blog. title creates the idx_title index, and the query extra is Using index instead of Using temporary.
explain select distinct title from blog;
Copy the code

* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * In this case, index optimization is also generally considered.

  • Author.name, which has no index, looks up the entire author table, saves the sort key and the row pointer, then sorts the key and retrieves the row information in order.
explain select * from author order by name;
Copy the code

  • Blog. Title creates the idx_title index, and extra is using index.
explain select * from blog order by title;
Copy the code

6) Select Tables Optimized Away: Occurs when aggregate functions such as Max and min are used to access a field with an index.

explain select min(id) from author;
Copy the code

Each column and common values of the execution plan are explained. See you next time

Now that you’ve read this, please like, comment, follow and bookmark it!