The introduction

In daily work, there are many opportunities to use MySQL, mainly considering Schema and data type optimization, how to create indexes, query optimization based on business scenarios. This article aims to sort out the use and analysis of EXPLAIN statements.

EXPLAINWhat is the statement?

The official website defines the role of EXPLAIN as follows:

The EXPLAIN statement provides information about how MySQL executes statements. EXPLAIN works with SELECT, DELETE, INSERT, REPLACE, and UPDATE statements.

In short, EXPLAIN statements tell us how MySQL executes SQL statements, and we can use this information to optimize the efficiency of SQL statement execution.

Next, we need to understand the format of the EXPLAIN return, as follows:

The field name The field
id Query the serial number of the SELECT statement
select_type SELECT the type
table The name of the accessed table
partitions Hit the partition
type Data access type,Details below
possible_keys Regarding indexes, they may not actually be available
key The actual index used by the MySQL query optimizer
key_len Index storage length
ref The constant or column used for comparison in the actual index used
rows Query the number of rows that need to be read. Innodb engine is a measure of efficiency and can be inaccurate at times
Extra Additional information about query execution,Details below

  • In the analysis of SQL statement execution, the main columns are Type and Extra. The test cases below are sakila database provided by the official website, and the download link is attached.

  • MySQL 8.0.12 and Navicat 12.1 are used in this article

The Type column mainly presents values (good to poor performance)

  • System: Special case of a const table with only one row.
  • Const: Query result with at most one row, primary key, unique index and constant comparison.
explain select * from actor where actor_id = 1
Copy the code
  • Eq_ref: a special index lookup in which MySQL knows to return at most one record that matches the criteria, as seen by the only index that uses a primary key, NOT NULL (navicat finds the same result as ref).
explain select * from actor, film_actor where actor.actor_id = film_actor.actor_id and actor.actor_id = 1
Copy the code
  • Ref: An index lookup that returns all rows that match a single value. However, it is possible to find more than one row that matches the condition, when a non-unique index or a non-unique prefix to a unique index is used.
explain select * from film where title= 'ACE GOLDFINGER'
Copy the code
  • -Leonard: A range scanA limited index scan, do not traverse all indexes, such as index inBETWEEN,>,>In the range of.
explain select * from film where film_id BETWEEN 1 AND 100
Copy the code
  • Index: a full table scan, but MySQL scans the table in index order instead of rows.

“Using index” is displayed in the Extra column, indicating that the index is overwritten. Only the index column needs to be read. Full table reads using index order.

explain select actor.actor_id from film_actor, actor where film_actor.actor_id = actor.actor_id 
Copy the code
  • ALL: scans ALL tables, reads row data, and finds required rows.
explain select * from film_actor, actor where film_actor.actor_id = actor.actor_id 
Copy the code

The Extra column is the main value present

  • Using index: Use an overwrite index to avoid querying row data back into a table.
  • Using WHERE: The storage engine retrieves rows before filtering them.
  • Using temporary: A temporary table is used to sort query results. Avoid Using temporary tables.
  • Using filesort: Using an external index sort for results, rather than reading rows from the table in index order, needs to be optimized.

conclusion

Due to the existence of the query optimizer, the actual running of the query statement will be inconsistent with what you want, so in the query statement optimization, it is best to run the EXPLAIN statement to see if it is consistent with what you want.

reference

  • High Performance MySQL (Version 3)
  • Dev.mysql.com/doc/refman/…