Hello, guys. Today I’m going to talk to you about a very basic MySQL command — explain. This is a command that many of you are familiar with and use almost every day, as I do. So why write it?

The thing is, on Saturday, a junior student asked me whether this command is often used.

This command is familiar to all of us in the workplace, but for some students, it may not be. Hence the article.

If you already have a job, do it again. I was actually asked how many results of type are there? What do you mean, respectively? If you haven’t graduated yet, I suggest you do. It’s really important.

So, without further ado, here’s the mind map for this article:

Past wonderful

How is the MySQL query executed?

MySQL index

MySQL logs

MySQL transactions and MVCC

MySQL lock mechanism

How to design index for MySQL string?

Interviewer: What happens when the database increment ID runs out?

Interviewer: How to optimize the order by?

Interviewer: How to optimize count(*)?

01 explain abstract

Explain is a command that analyzes SQL execution, mainly for select statements (PS: other statements I haven’t explained…). It lets you know if your SQL statements are efficient enough and where you can optimize them.

MySQL 5.6.46 can only explain select statements before PS: 5.6. MySQL later can explain other commands.

To start, I created three tables and some test data:

DROP TABLE IF EXISTS `actor`;
CREATE TABLE `actor` (
  `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 `actor` (`id`, `name`, `update_time`) VALUES (1.'a'.'the 2021-07-11 14:39:53'), (2.'b'.'the 2021-07-11 10:39:53'), (3.'c'.'the 2021-07-11 10:24:39');

DROP TABLE IF EXISTS `film`;
CREATE TABLE `film` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(10) DEFAULT NULL.PRIMARY KEY (`id`),
  KEY `idx_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `film` (`id`, `name`) VALUES (3.'film0'), (1.'film1'), (2.'film2');

DROP TABLE IF EXISTS `film_actor`;
CREATE TABLE `film_actor` (
  `id` int(11) NOT NULL,
  `film_id` int(11) NOT NULL,
  `actor_id` int(11) NOT NULL.PRIMARY KEY (`id`),
  KEY `idx_film_actor_id` (`film_id`,`actor_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `film_actor` (`id`, `film_id`, `actor_id`) VALUES (1.1.1), (2.1.2), (3.2.1);
Copy the code

At the beginning of 1.1 experience

Explain is very simple to use, add explain in front of SQL:

explain select * from film;
Copy the code

In addition, mysql also has the following two extensions, which you can know.

1.1.1 the explain extended

It provides additional query optimization information on top of Explain, as you can see in the following figure. Rows * filtered/100 estimates the number of rows that will be connected to the previous table in Explain (the previous table in Explain has a lower ID than the current table ID).

Execute with the show Warnings statement and MySQL will give you an optimized SQL statement. You can see what MySQL has optimized for us.

1.1.1 the explain partitions

There is an additional PARTITIONS field than Explain that shows the partitions the query will access if the query is based on a partitioned table.

02 Why use explain?

The reason is simple. Using the Explain command we can learn how the SQL is executed and then parse the explain results to help us use a better index and ultimately optimize it!

The explain command lets you know the order in which tables are read, the types of data read operations, which indexes are available, which indexes are actually used, references between tables, how many rows of each table are queried by the optimizer, and so on.

03 Read the result columns in Explain

As can be seen from the figure above, there are 10 columns in the output result of the explain command, including ID, select_type, table, type, possible_keys, key, key_len, ref, rows, and Extra

In the following paragraphs, I will analyze what each of these columns of results represents, in the hope that it will help you:

3.1 the id column

A group of numbers representing the order in which select statements are executed. Select ids are represented in the order in which they appear.

There are several cases:

  • The ids are the same and the execution sequence is from top to bottom
  • The serial number increases with different ids. The higher the value, the higher the priority, the earlier the execution

3.2 select_type column

Select * from (select); select * from (select); select * from (select);

  1. Simple: indicates simple query. Queries do not contain subqueries and unions, such as the statements demonstrated in the introduction above
  2. Primary: In contrast, if the query contains a subquery and union, it is marked as primary
  3. Subquery: by name, a subquery contained in a select (not in the FROM clause)
  4. Derived: In the from clause, MySQL stores the results in a temporary table, also known as a derived table

For example, you can see the difference:

explain select (select 1 from actor where id = 1) from (select * from film where id = 1) alias;
Copy the code

  1. Union: Indicates that this query is the second or subsequent query in the union
  2. Union result: The select that retrieves the result from the UNION temporary table

Here’s an example to help you tell the difference:

explain select 1 union all select 1;
Copy the code

  1. Dependent Union: This query is the second or subsequent query in the union, and then depends on the external query
  2. Uncacheable Union: This query is the second or subsequent query in the union and means that some feature in the SELECT prevents the results from being cached in an Item_cache
  3. Dependent subquery: The first select in a subquery that also depends on the external query
  4. Uncacheable subquery: the first select in a subquery, which also means that certain features in the select prevent the results from being cached in an Item_cache

PS: Just understand the first four and the rest.

3.3 the table column

Which table is accessed by a row indicating explain

  • When there are subqueries in the FROM clause, the table column is in the < derivenN > 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 < union1,2 >, and 1 and 2 represent the IDS of the select rows participating in the union

3.4 the type column

Table association type or access type, the most important column, is the one that determines whether a query is efficient: that is, MySQL determines how to find rows in a table based on this column.

The results are numerous, with performance ranging from best to worst: system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

I’ll just pick a few common values to talk about, and I haven’t come across any proof that I haven’t talked about, hahaha:

  1. ALL: indicates full table scan, with poor performance. Knock on the blackboard, this type should not appear, must be able to add index optimization.
  2. Index: scans a whole index tree, which is faster than ALL.
  3. The range:Range scan, usually appears inin (), between ,> ,<, >=And so on. Use an index to retrieve rows in a given range.
  4. Ref: an index lookup, which does not use a unique index but uses the partial prefix of a normal index or unique index. The index is compared with a value and may find multiple rows that match the condition.
  5. Eq_ref: Returns at most one record that meets the condition. This value appears when using a unique index or primary key lookup, which is very efficient.
  6. Const, system: The table has at most one matching row that is read at the start of the query, or the table is a system table with only one matching row. Const is used when there is a fixed value compared to the primary key or unique index.
  7. Null: No further table or index access is required at runtime.

3.5 possible_keys column

If the value is null, add an index to this column.

3.6 the key column

Which indexes are actually used by MySQL to perform the query. If null, no indexes are used. If you want to force or ignore an index, you can add force index (the name of the index you want to force) or ignore index (the name of the index you want to ignore) to the query statement.

PS: If possible_keys has columns and key is null, it may be because there isn’t much data in the table and adding indexes doesn’t have much effect.

3.7 key_len column

The number of bytes used in the index. If the key column is NULL, that column is also NULL

PS: The maximum length of the index is 768 bytes. If the string is too long, MySQL will extract the first part of the index. (Although we wouldn’t normally index a field that long.)

3.8 ref column

Const (constant), func, NULL, field name (e.g. Film.id)

3.9 rows column

This column is the number of rows that mysql expects to read and detect, note that this is not the number of rows in the result set. (Just a predicted number)

3.10 Extra column

Additional information is also very important

  • Using index: Using an overwrite index, which indicates that the required data can be retrieved by querying the index without returning to the table.
  • Using WHERE: The storage engine retrieves rows and then filters them. That is, the entire row is read first and then selected based on where criteria.
  • Using temporary: mysql needs to create a temporary table to process a query, usually because the query statement has a sort, group, or join condition.
  • Using filesort: select * from a table where rows are sorted by an external index instead of being sorted by an index.

04 summary

Explain is not difficult, the only result columns we need to focus on are select_Type, Type and extra columns. This is a very basic command, students in school friends very recommended that you understand in advance.

reference

  • High Performance MySQL

  • MySQL official documentation

05 idea activation

[idea activation] (www.yuque.com/docs/share/… Jetbrains Family Bucket Activation)