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

This article mainly introduces how to optimize SQL, has a certain reference value, interested friends can refer to, I hope you can learn a lot after reading this article, the following let the editor take you to understand it.

Brief introduction

Although using Explain does not immediately tune our SQL, nor does it give us any suggestions for tuning, it allows us to understand how the MySQL optimizer executes SQL statements

Through Explain, we can analyze the following results:

Reading order of the table

Operation type of data read operation

Which indexes can be used

Which indexes are actually used

References between tables

How many rows per table are queried by the optimizer

The use of the Explain command is very simple, just add Explain before the select statement, for example:

Explain select * from user

Its result mainly consists of the following fields

Id 、 select_type 、 table 、 partitions 、 type 、 possible_keys 、 key 、 ref 、 rows 、 filtered 、 extra

Next, let's look at the meaning of each field.

Id query serial number

Order in which tables are loaded

The load order of each table in the join query is the same, so it is all 1.

When a subquery is included, the subquery is executed first, so the id value of the user table is the largest.

Select_type query type

Common values are:

SIMPLE: a simple select query, excluding subqueries and indexes

PRIMARY: if any subquery is included in the query, the outermost query is marked as PRIMARY

SUBQUERY: subqueries are included in the SELECT or WHERE list

DERIVED: subqueries included in the FROM list are marked as DERIVED (derivation), and MySQL recursively executes these subqueries to put the results in a temporary table

UNION: if the second SELECT appears after the index, it is marked as UNION: if the index is included in the subquery of the FROM clause, the outer SELECT will be marked as: DERIVED

UNION RESULT: a query that gets results from an index table

Tables or derived tables involved in table queries

Type query type

Through the type field, we can determine whether the query is a full table scan or an index scan. The commonly used values for type are:

System: the table has only one piece of data

Const: an equivalent query scan for a primary key or unique index, which is simply understood as getting data in a single read, such as the query for the primary key index below

Eq_ref: unique index scan, with only one record in the table matching for each index key. Common in primary key or unique index scans

Ref: a non-unique index scan that returns all rows that match a single value

Range: indicates the use of index range queries, such as =, >, > =,

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.