Shulou(Shulou.com)06/02 Report--

This article shows you what the role of the keyword Explain in Mysql is. The content is concise and easy to understand. It can definitely make you shine. I hope you can gain something through the detailed introduction of this article.

Explain Tool Introduction

Use the EXPLAIN keyword to simulate the optimizer executing SQL statements, analyzing query statements or structural performance bottlenecks. Add the explanation keyword before the select statement, MySQL sets a flag on the query, and executing the query returns information about executing the plan instead of executing SQL.

Example of Explained Analysis-- actor table creation statement: CREATE TABLE `actor`( `id` int(11) NOT NULL, `name` varchar(45) DEFAULT NULL, `update_time` datetime DEFAULT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8-- film Table-building sentence: CREATE TABLE `film`( `id` int(11) NOT NULL, `name` varchar(10) NOT NULL, PRIMARY KEY (`id`), KEY `idx_name` (`name`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8-- film_actor CREATE TABLE `film_actor` ( `id` int(11) NOT NULL, `film_id` int(11) NOT NULL, `actor_id` int(11) NOT NULL, `remark` varchar(255) DEFAULT NULL, PRIMARY KEY (`id`), KEY `idx_film_actor_id` (`film_id`,`actor_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8

Execute explain:

explain select * from actor;

If the select statement returns the execution result, adding explain before the select statement returns the execution SQL of the query statement.

EXPLAIN two variants explain extended

Some additional query optimization information is provided on top of the explanation. Then you can see what the optimizer optimized by using the show warnings command to get the optimized query statement. An additional filtered column is a half-ratio value, rows*filtered / 100, which estimates the number of rows to join to the previous table in explain (the previous table in explain has a smaller id than the current table).

explain EXTENDED select * from actor where id = 1;

explain partitions

There is one more partition field than explain, which shows the partitions the query will access if the query is based on a partitioned table.

Column id column in Explain

The id column number is the sequence number of select, there are several id, and the order of id is increasing in the order in which select appears. The higher the id, the higher the execution priority. If the id is the same, it will be executed from top to bottom. If the id is NULL, it will be executed last.

explain select (select 1 from actor where id = 1) from (select * from filmwhere id = 1) der;

select type column

Select type indicates whether the corresponding row is a simple or complex query. Simple: Simple query. The query does not contain subqueries and unions.

explain select * from film where id=1

primary: the outermost select subquery in a complex query: the subquery contained in the select (not in the from clause) derived: the subquery contained in the from clause. MySQL stores the results in a temporary table, also known as a derived table.

explain select (select 1 from actor where id = 1) from (select * from filmwhere id = 1) der;

union: selelct followed by union keyword.

EXPLAIN select 1 union all select 1;

table column

This column indicates which table a row of explain is accessing. When there are subqueries in the from clause, the table column is formatted to indicate that the current query depends on the query with id=N, so the query with id=N is executed first. When there is a union, the table column of UNION RESULT has a value of 1 and 2 indicating the select row id participating in the union.

type column

This column represents the association type or access type, i.e. MySQL determines how to look up rows in the table and the approximate range of data rows. From best to worst: system>const>eq_ref>ref>range>index>All Generally speaking, it is necessary to ensure that the query reaches the range level, preferably ref. NULL: MySQL is able to parse query statements in the optimization phase without having to access tables or indexes in the execution phase. For example, selecting the minimum value in the index column can be done by looking up the index alone, without accessing the table at execution time.

EXPLAIN select min(id) from film;

const, system: mysql can optimize some part of the query and convert it into a constant (think of it as the result of show warnings). When all columns used for primay key or unique key are compared to constants, the table has at most one matching row, read once, and read quickly. system is a special case of const, system when only one row matches in the table.

EXPLAIN select * from (select * from film where id= 1) as tmp;

eq_ref: All parts of the primay key or unique key index are concatenated, returning at most one eligible record. This is probably the best join type outside of const, and it doesn't appear in simple select queries.

EXPLAIN select * from (select * from film where id= 1) as tmp;

ref: instead of using a unique index as opposed to eq_ref, use a normal index or partial prefix of a unique index, which is compared to a value and may find more than one eligible row. Simple select query, name is an ordinary index (non-primary key index or unique index)

EXPLAIN select * from film where name='film1';

The associated table query, idx_film_actor_id is the joint index of film_id and actor_id, here using the left prefix film_id part of film_actor.

EXPLAIN select film_id from film LEFT JOIN film_actor on film.id = film_actor.film_id;

Range: Range scanning usually occurs in operations such as in(), between,>,=, etc. Retrieves a given range of rows using an index.

EXPLAIN select * from actor WHERE id >1;

index: Scan the full table index, usually faster than All

EXPLAIN select * from film;

All: full table scan, meaning MySQL needs to find the required rows from beginning to end. In this case, the index needs to be increased for optimization.

EXPLAIN SELECT * from actor;

possible_keys column

This column shows which queries select might use to find. When explaining, there may be cases where possible_keys have columns, but key is displayed as NULL. This situation is because there is not much data in the table, MySQL thinks that the index is not helpful for this query, and selects the full table scan. If the column is NULL, there is no associated index. In this case, you can improve query performance by examining the where clause to see if you can create an appropriate index, and then using explain to see the effect.

EXPLAIN SELECT * from film_actor where film_id =1;

key column

This column shows which index MySQL actually uses to access the table. If no index is used, the column is changed to NULL. If you want to force MySQL to use or ignore indexes in the possible_keys column, use force index, ignore index in queries.

key_len column

This column shows the number of bytes mysql uses in the index, which can be used to estimate which columns in the index are used.

EXPLAIN SELECT * from film_actor where film_id =1;

The joint index idx_film_actor_id of film_actor consists of two id columns, film_id and actor_id, and each int is 4 bytes. Infer from key_len=4 in the result that the query uses the first column: the film_id column to perform the index lookup. ken_len is calculated as follows:

String char(n): n-byte length varchar(n): n-byte store string length, if utf-8, length is 3n+2

Numeric type tinyint: 1 byte smallint: 2 bytes int: 4 bytes bigint: 8 bytes

Date: 3 bytes timestamp: 4 bytes datetime: 8 bytes

If the field is allowed to be NULL, it requires 1 byte to record whether it is NULL. The maximum length of the index is 768 bytes. When the string is too long, MySQL will do a similar process to prefix index, extracting the first half of the string for index.

ref column

This column shows the columns or constants used in the table lookup value in the index of the key column record. Common ones are: const(constant), field name, etc. Generally, it is the value to the right of the equal sign in the query condition or association condition. If it is a constant, then the ref column is const. If it is an extraordinary quantity, the ref column is the field name.

EXPLAIN SELECT * from film_actor where film_id =1;

row column

This column is mysql's estimate of the number of rows to read and detect. Note that this is not the number of rows in the result set.

Extra column

This column contains additional information.

Using index: Use overlay index (the field of the result set is index, i.e. film_id after select)

explain select film_id from film_actor where film_id=1;

Using index condition: The query column is not completely covered by the index, where the condition is a leading range

explain select * from film_actor where film_id > 1;

Using where: Use where statements to process results, query columns are not covered by index

explain select * from actor where name ='a'

Using temporary: mysql needs to create a temporary table to process queries. This situation generally needs to be optimized, and the first thing to think about is index optimization.

explain select DISTINCT name from actor;

There is no index on actor.name, so temporary tables are created to handle distinct.

explain select DISTINCT name from film;

file.name establishes a common index, in this case Extra is Using index, no temporary table is used.

Using filesort: sorting will be done externally rather than by index, sorting from memory when data is small, otherwise sorting needs to be done on disk. In this case, it is generally necessary to consider the use of indexes to optimize.

explain select * from actor order by name;

actor.name does not create an index, it browses acotr through the table, saves the sort keyword name and corresponding id, then sorts name and retrieves row records.

explain select * from film order by name;

film.name establishes idx_name index, in this case extra is Using index when querying.

select tables optimized away: Use some aggregate function (e.g. max, min) to access a field where an index exists

explain select min(id) from film ;

What is the function of the keyword Explain in Mysql? Have you learned any knowledge or skills? If you want to learn more skills or enrich your knowledge reserves, please pay attention to the industry information channel.

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.