Shulou(Shulou.com)05/31 Report--

This article focuses on "how to master SQL optimization", interested friends may wish to take a look. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn how to master SQL optimization.

What information does Explain have?

First confirm the MySQL version of the trial, which is version 5.7.31.

You only need to add the explain keyword before the SQL statement to view the execution plan, which includes the following information: id, select_type, table, partitions, type, possible_keys, key, key_len, ref, rows, filtered, Extra, a total of 12 field information.

Then create three tables:

CREATE TABLE `int (10) NOT NULL AUTO_INCREMENT, `name` varchar (36) NOT NULL, PRIMARY KEY (`id`), KEY `index_ name` (`name`) USING BTREE) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COMMENT=' student form; CREATE TABLE `tb_ class` (`id`INT (10) primary key not null auto_increment, `name`VARCHAR (36) NOT NULL, `stu_ id`INT (10) NOT NULL, `tea_ id` INT (10) NOT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT=' class table CREATE TABLE `tb_ teacher` (`id` INT (10) primary key not null auto_increment, `name` VARCHAR (36) NOT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT=' teacher list'

Detailed explanation of Explain implementation plan

The use of explain is very simple, you only need to add the keyword explain before the SQL statement. The key is how to look at the field information returned by explain. This is the key point.

1. Id

SELECT identifier. This is the query serial number of SELECT. Identification of the order in which SQL executes, and SQL executes from large to small. Id lists the following caveats:

When the id is the same, the execution order is from top to bottom.

Id is different, if it is a subquery, the sequence number of id will be incremented, and the higher the id value, the higher the priority, the first to be executed.

EXPLAIN SELECT * FROM `tb_ student` WHERE id IN (SELECT stu_id FROM tb_class WHERE tea_id IN (SELECT id FROM tb_teacher WHERE `name` = 'teacher Ma'))

According to the principle, when the id is different, the SQL executes from large to small, and the same id executes from top to bottom.

II. Select_type

Represents the type of select query, used to distinguish between various complex queries, such as normal queries, federated queries, subqueries, and so on.

SIMPLE

Represents the simplest query operation, that is, there are no subqueries, union, and other operations in the query SQL statement.

PRIMARY

When a query statement contains subsections of a complex query, it represents the outermost select in the complex query.

SUBQUERY

When a select or where contains a subquery, the subquery is marked as SUBQUERY.

DERIVED

A subquery contained in the from clause in a SQL statement.

UNION

Represents the second and subsequent select statements in union.

UNION RESULT

Represents reading data from the temporary table of union.

EXPLAIN SELECT u.`name`FROM ((SELECT s.id, s.`name`FROM `tb_ student`s) UNION (SELECT t.id, t.`name` FROM tb_teacher t)) AS u

The union operation is performed on behalf of the results of select queries with id 2 and 3.

MATERIALIZED

MATERIALIZED represents a materialized subquery, which comes from the view.

III. Table

The table name that represents the output result set is not necessarily a real table, but may also be an alias, temporary table, and so on.

IV. Partitions

Indicates the partition information matched by the SQL statement when querying. If the value of the non-partition table is NULL, the partition information hit by the partition table will be displayed when the partition table is queried.

5. Type

A field information that needs to be paid attention to indicates which type of query is used, which is a very important indicator in SQL optimization, in the following order: system > const > eq_ref > ref > range > index > ALL.

System and const

There is at most one matching row in a single table, and the query is the most efficient, so the values of the other columns of this matching row can be treated by the optimizer as a constant in the current query. It usually occurs in queries based on primary keys or unique indexes, where system is a special case of const and is system when there is only one tuple match in the table (system table).

Eq_ref

All parts of the primary key or unique key index are concatenated and only one qualified record is returned at most, so this type often occurs in multi-table join queries.

Ref

Compared to eq _ ref, instead of using a unique index, but using a partial prefix of a normal index or a unique index, multiple eligible rows may be found.

Range

Use the index to select rows and retrieve only rows within a given range. Generally speaking, it is for an indexed field to retrieve data in a given range, which usually appears in where statements using bettween...and,

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.