How does Explain execute the MySQL statement

This article mainly introduces "how Explain executes MySQL statements". In daily operation, I believe many people have doubts about how Explain executes MySQL statements. The editor consulted all kinds of materials and sorted out simple and easy-to-use operation methods. I hope it will be helpful to answer the doubts of "how Explain executes MySQL sentences". Next, please follow the editor to study!

Explain

In MySQL, we usually use desc tableName to view the information of a table, the definition of each column, etc., and to understand how MySQL executes the current SQL through Explain SQL.

In fact, desc, describe, and explain can all be used to see how MySQL executes the current SQL. After MySQL 8.0.19, these three functions can be said to be equivalent, and explain can also be used to view table information. Later, we will directly take explain as an example to illustrate the specific role.

The official documentation makes it clear that explain can work with SELECT, INSERT, UPDATE, and DELETE to show the statement execution plan of the MySQL optimizer, that is, to tell the user how the SQL will be executed by MySQL, in what order, and how Join if it is multiple tables.

The screenshot of the official website document of the output field is as follows:

There seems to be a lot of returns above, but let's focus on type, key, and rows.

Our common application scenarios are more reading and less writing, and the evaluation of the efficiency of SQL execution generally means the efficiency of querying data from 100%, millions or even tens of millions of pieces of data that have been stored.

Let's take SELECT as an example to see what help and advice explain can give us.

Suppose you have the following table definition and data:

CREATE TABLE `t3` (`id` int NOT NULL, `a` int DEFAULT NULL, `b` int DEFAULT NULL, PRIMARY KEY (`id`), KEY `a` (`a`) ENGINE=InnoDB; delimiter;; create procedure idata () begin declare i int; set iTun1; while (I, > =