Detailed explanation of explain type in MySQL

Introduction:

In many cases, many people think that their work is successfully completed after they have used various select statements to query the data they want.
These things often happen to some students or newbies who have just entered the workplace but do not have a good database foundation before. However, as the saying goes, everyone learns at a different time. As long as we newbies study hard and make progress every day, it is still very reliable.

When a SQL query statement is written, your work is actually only half done. The next more important task is to evaluate the quality and efficiency of the SQL you wrote. MySQL provides us with a very useful auxiliary weapon explain, which shows us the execution plan of a SQL statement received by MySQL. Based on the results returned by explain, we can know how our SQL is written and whether it will cause query bottlenecks. At the same time, we can continuously modify and adjust the query statement based on the results to complete the SQL optimization process.

Although explain returns many result items, here we only focus on three types, namely type, key, and rows. The key indicates the index used in this search, and rows refers to the number of rows scanned for this search (you can understand it this way first, but it is actually the number of inner loops). Type is the connection type that this article will record in detail. The first two items are important and simple, so there is no need to explain more.

type -- connection type

Type means type. The official full name of type here is "join type", which means "join type". This can easily give people the illusion that more than two tables are required to have a join type. In fact, the connection type here is not as narrow as its name suggests. It is more precisely a way for a database engine to search for a table. In the book "High Performance MySQL", the author thinks it is more appropriate to call it an access type.

There are as many as 14 types of type in MySQL 5.7. Here we only record and understand the six most important and frequently encountered types, namely all, index, range, ref, eq_ref, and const. From left to right, their efficiency increases. Regardless of the specific application environment of SQL and other factors, you should try to optimize your SQL statement so that its type is as close to the right as possible, but in actual application, you still need to consider all aspects comprehensively.

Next, in order to demonstrate and reproduce these connection types, I created a new data test table to better understand these five types.

| employee | CREATE TABLE `employee` (
 `rec_id` int(11) NOT NULL AUTO_INCREMENT,
 `no` varchar(10) NOT NULL,
 `name` varchar(20) NOT NULL,
 `position` varchar(20) NOT NULL,
 `age` varchar(2) NOT NULL,
 PRIMARY KEY (`rec_id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 |

all

This is the so-called "full table scan". If it is to display all the data items in a data table, it doesn't matter. However, if the all type appears in a SQL statement that searches for data items, it usually means that your SQL statement is in the most native state and has a lot of room for optimization.
Why do I say so? Because all is a very violent and primitive search method, it is very time-consuming and inefficient. Using all to search for data is like this: there are 20,000 people in S school, and I tell you to find Xiao Ming for me, so what do you do? Of course you will search all 20,000 people in the school one by one. Even if you are lucky and find Xiao Ming as the first person, you still cannot stop, because you cannot confirm whether there is another Xiao Ming until you have searched all 20,000 people. So, in almost all cases, we should avoid this type of lookup unless you have to.
Taking the employee table as an example, the following scenario is an all-type search:

mysql> explain select * from employee where `no` = '20150001';
+----+-------------+----------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | employee | ALL | NULL | NULL | NULL | NULL | 5 | Using where |
+----+-------------+----------+------+---------------+------+---------+------+------+-------------+

This is because the no column is neither a primary key nor an index, so only a full table scan can be used to find the target no.

index

This type of join is just another form of full table scan, except that the scan order is in the order of the indexes. This type of scan retrieves data from the table based on the index. Compared with "all", they both retrieve the data of the entire table, and the index must first read the index and then retrieve data randomly from the table. Therefore, "index" cannot be faster than "all" (retrieve the same table data). But why does the official manual say that its efficiency is better than "all"? The only possible reason is that the data of the entire table is ordered when scanned according to the index. In this way, the results are different and there is no point in comparing efficiency.
If you really want to compare efficiency, you only need to get the data in this table and sort it to see which one is more efficient:

mysql> explain select * from employee order by `no`;
+----+-------------+----------+------+---------------+------+---------+------+------+----------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------+------+---------------+------+---------+------+------+----------------+
| 1 | SIMPLE | employee | ALL | NULL | NULL | NULL | NULL | 5 | Using filesort |
+----+-------------+----------+------+---------------+------+---------+------+------+----------------+
mysql> explain select * from employee order by rec_id;
+----+-------------+----------+-------+---------------+---------+---------+------+------+------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------+-------+---------------+---------+---------+------+------+------+
| 1 | SIMPLE | employee | index | NULL | PRIMARY | 4 | NULL | 5 | NULL |
+----+-------------+----------+-------+---------------+---------+---------+------+------+------+

As can be seen above, the join type based on the sorting of the no column is all type, but note that the extra column is sorted (Using filesort), while the join type based on the sorting of the rec_id column is index, and the results are naturally ordered, without additional sorting. Perhaps it is for this reason that index is more efficient than all, but note that this requires the same conditions (that is, sorting is required).

If the connection type is type, and the value in the extra column is 'Using index', then this situation is called index covering;
What does index covering mean? Imagine such a scenario: if a Xinhua dictionary is a table, of course the previous index part (assuming the index is based on radicals) is the index of this table, then index coverage is equivalent to getting the first to last characters (all characters in the Xinhua dictionary) based on the radical index. We get all the words in the dictionary, but we don't have to look up the table once because all the words we want are already in the index, that is, the index covers.

mysql> explain select rec_id from employee;
+----+-------------+----------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------+-------+---------------+---------+---------+------+------+-------------+
| 1 | SIMPLE | employee | index | NULL | PRIMARY | 4 | NULL | 5 | Using index |
+----+-------------+----------+-------+---------------+---------+---------+------+------+-------------+

In the above example, the rec_id obtained happens to be the index column, so there is no need to go back to the table to retrieve data.

range

Range refers to a ranged index scan. Compared with a full index scan of an index, it has a range restriction and is therefore superior to an index. Range is relatively easy to understand. What you need to remember is that when a range appears, it must be based on an index. In addition to the obvious between, and, '>', '<', in and or are also index range scans.

ref

The conditions for this connection type to occur are: The search condition column uses an index and is not a primary key or unique. In fact, it means that although the index is used, the value of the index column is not unique and there are duplicates. In this way, even if the first piece of data is quickly found using the index, the process cannot stop and a small range scan near the target value must be performed. But its advantage is that it does not need to scan the entire table, because the index is ordered, and even if there are duplicate values, they are scanned in a very small range. To demonstrate this situation, add a common key to the name column in the employee table (duplicate values ​​are allowed).

 alter table employee add key I_EMPLOYEE_NAME(`name`);

Next, when searching for data by name in the employee table, the MySQL optimizer selects the ref connection type.

mysql> explain select * from employee where `name` = '张三';
+----+-------------+----------+------+----------------+----------------+---------+-------+------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------+------+----------------+----------------+---------+-------+------+-----------------------+
| 1 | SIMPLE | employee | ref | I_EMPLOYEE_NAM | I_EMPLOYEE_NAM | 62 | const | 1 | Using index condition |
+----+-------------+----------+------+----------------+----------------+---------+-------+------+-----------------------+

ref_eq

What's great about ref_eq compared to ref is that it knows there is only one result set for this type of lookup? Under what circumstances is there only one result set? That is the case when the primary key or unique index is used for search. For example, when searching for a student in a certain school based on the student ID number, we know before the search that there must be only one result, so when we find the student ID number for the first time, we immediately stop the search. This type of connection performs precise queries each time without excessive scanning, so the search efficiency is higher. Of course, the uniqueness of the column needs to be determined based on the actual situation.
In a single table, I have tried many methods to get the connection type of ref_eq, but most of the time it was const, so I had to connect a table to get the desired connection type. The table creation code for this table is. (The blogger is lazy and connected two unrelated tables, o(╯□╰)o)

CREATE TABLE `score` (
 `rec_id` INT(11) NOT NULL AUTO_INCREMENT,
 `stu_id` INT(11) NOT NULL,
 `mark` INT(11) NOT NULL DEFAULT '0',
 PRIMARY KEY (`rec_id`),
 UNIQUE KEY `UK_SCORE_STU_ID` (`stu_id`)
) ENGINE=INNODB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8

There are five records in the employee table and five corresponding records in the score table, where the employee's rec_id and the score's stu_id are one-to-one corresponding.

mysql> explain select ep.name,sc.mark from employee ep,score sc where ep.rec_id = sc.stu_id;
+----+-------------+-------+--------+-----------------+---------+---------+-----------------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+-----------------+---------+---------+-----------------+------+-------+
| 1 | SIMPLE | sc | ALL | UK_SCORE_STU_ID | NULL | NULL | NULL | 5 | NULL |
| 1 | SIMPLE | ep | eq_ref | PRIMARY | PRIMARY | 4 | my_db.sc.stu_id | 1 | NULL |
+----+-------------+-------+--------+-----------------+---------+---------+-----------------+------+-------+

From the above, we can see that the score table is a full table scan type. Rows = 5 means that the outer table is looped five times (because there are five pieces of data). But why is the rows of the employee table 1? How is that possible? I was also very confused at the beginning. This is closely related to the query principle of MySQL. Rows actually reflects the number of inner loops of the query. For each data match in the outer layer, employee can be hit with one shot, so rows is 1.

const

Normally, if you place a primary key after the where clause as a conditional query, the MySQL optimizer can optimize the query into a constant. How and when to convert depends on the optimizer.

Summarize

Explain is like a mirror. Remember to explain it after writing your SQL. At the same time, when writing articles, I also found that there are many things and details that are not so easy to understand clearly. It requires a clear understanding of the underlying query and operation principles of the operating system and database. At the same time, several types of type are almost all based on indexes, so we need to have a deep understanding of indexes. The results of explain can guide us when to add indexes and when not to add indexes, so that we can make better use of indexes.

The above is a detailed explanation of the type of explain in MySQL. For more information about the type of explain in MySQL, please pay attention to other related articles on 123WORDPRESS.COM!