Preface: This article mainly talks about how to use EXPLAIN, and explain various parameter concepts, and then will talk about optimization

A, Explain usage

Simulate how the Mysql optimizer executes SQL queries to see how Mysql handles your SQL statements. Analyze performance bottlenecks in your query or table structure.

Syntax: Explain + SQL statements;

Explain select * from user; The following SQL analysis results are generated. The following sections explain each field in detail

Second, the id

Is a group of numbers, representing the order of the query between multiple tables, or contains the order of the clause query statement. There are three types of IDS, which are explained in detail

  • The ids are the same and the execution sequence is from top to bottom
  • The IDS are different. For sub-queries, the IDS are incremented. A larger ID has a higher priority and is executed earlier
  • The same id and different id exist at the same time

Third, select_type

Select_type contains the following values

  • simple
  • primary
  • subquery
  • derived
  • union
  • union result

simple

A simple SELECT query that does not contain subqueries or union queries

primary

If the SQL statement contains any subqueries, the outermost layer of the subquery is marked as primary

subquery

If a subquery is included in a SELECT or WHERE, the subquery is marked as a subQquery, as in 3. Two simultaneous occurrence

derived

Subqueries contained in from are marked as derived queries, and the query results are placed in a temporary table

union / union result

If you have two SELECT queries that are joined by a union, the second select will be marked as union, and the result of the union will be marked as union result. Its ID is null

Fourth, the table

Represents the data for which table this row is from

Five, the type,

Type indicates the index type used by MySQL. Different index types have different query efficiency

  • system
  • const
  • eq_ref
  • ref
  • range
  • index
  • all

system

There is only one row in the table, and system is the const exception, which is almost never the case and can be ignored

const

MySQL can change the query condition to a constant that matches only one row of data and finds the data once

eq_ref

In multi-table queries, such as T1 and T2, only a single row can be found in T2 for a row in T1. To be clear, the condition for the associated query between T1 and T2 is primary key index or unique index. In this way, each row in T1 can be guaranteed to correspond to only one row in T2

EXPLAIN SELECT * from T1, t2 where t1.id = t2.id

ref

Not a primary key index, not a unique index, but a normal index that may return multiple rows that match the criteria.

range

It is reflected in the range search of an index, which generally occurs in the range search of BETWEEN, and, <, >, and in in where conditions.

index

Run through all index trees to find rows that match the criteria. The index file is still much smaller than the data file, so it is still much faster than a full table scan without an index.

all

Did not use the index, the simple table data are all traversed, find the data in line with the conditions

Six, possible_keys

If there are indexes on the fields involved in this query, they are listed, indicating the indexes that might be used, but not the ones that are actually required

Seven, the key

The index actually used in this query

Eight, key_len

Indicates the number of bytes used in the index. By using this attribute, you can know the index length used in the query. Note: This length is the maximum possible length, not the actual length

Nine, ref

Displays associated fields. If a constant equivalence query is used, const is displayed, and if a join query, the associated fields are displayed.

  • Tb_emp table is a non-unique index scan, the actual index column used is IDx_name, because tb_emp.name=’rose’ is a constant, so ref=const.
  • Table tb_EMP (ref=db01.tb_emp. depTID); table TB_EMP (ref=db01.tb_emp. depTID); table TB_EMP (ref=db01.tb_emp. depTID);

Ten, rows

Based on table statistics and index usage, a rough estimate of the number of rows that need to be read to find the desired record is as small as possible

Eleven, extra

Information that is not suitable for display in other columns, but is important for optimization purposes

Using fileSort

Select * from ‘order by’; select * from ‘group by’; select * from ‘select’; Select * from fileSort; select * from fileSort; select * from fileSort

Using temporary

Temporary tables are used to hold intermediate results, common in Order BY and Group by.

USING index

Coveing Index (Coveing Index) is used in the select operation, avoiding access to rows of the table. Using WHERE indicates that the index is used to perform a key lookup. If a using WHERE is not present at the same time, the surface index is used to read data rather than perform lookup actions.

Using wher

Indicates where filtering is used

using join buffer

Connection caching is used

impossible where

The value of the WHERE clause is always false and cannot be used to get any tuples

select tables optimized away

In the absence of the GROUPBY clause, optimization of MIN/MAX operations based on indexes or COUNT(*) operations for MyISAM storage engines does not have to wait until the execution stage to perform the calculation. Optimization is completed at the generation stage of the query execution plan.

distinct

Optimize distinct to stop looking for the same value once the first matching tuple is found

How to optimize MySQL index

Follow wechat public account: IT elder brother

Java foundation, Java Web, JavaEE all tutorials, including Spring Boot, etc

Reply: Resume template, you can get 100 beautiful resumes

Reply: Java learning route, you can get the latest and most complete a learning roadmap

Re: Java ebooks, get 13 must-read books for top programmers