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

This article mainly introduces why explain is the Mysql database performance optimization artifact, the contents of the article are carefully selected and edited by the author, with a certain pertinence, the reference significance for everyone is still relatively great, the following with the author to understand why explain is the Mysql database performance optimization artifact.

Introduction to Explain tools

Use the EXPLAIN keyword to simulate the optimizer's execution of SQL statements and analyze performance bottlenecks in queries or structures. Add the explaion keyword before the select statement, and MySQL sets a flag on the query, and executing the query returns information about the execution plan instead of executing SQL.

Explaion Analysis exampl

-- 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 creation statement: CREATE TABLE `actor` (`id`int (11) NOT NULL, `name` varchar (10) NOT NULL,PRIMARY KEY (`id`), KEY `idx_ name` (`name`)) ENGINE=InnoDB DEFAULT CHARSET=utf8-- film_actor table creation statement: CREATE TABLE `film_ actor` (`id` int (11) NOT NULL, `film_ id` int (11) NOT NULL `remark` int (11) NOT NULL, `remark` varchar (255th) DEFAULT NULL, PRIMARY KEY (`id`), KEY `idx_film_actor_ id` (`film_ id`, `actor_ id`) ENGINE=InnoDB DEFAULT CHARSET=utf8

Perform 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.

Two varieties of EXPLAIN

1 、 explain extended

Some additional information on query optimization will be provided on top of explain. Then you can get the optimized query statement through the show warnings command to see what the optimizer has optimized. In addition, there is a filtered column, which is a half-fraction value, and rows*filtered / 100can estimate the number of rows that will be joined to the previous table in the explain (the previous table refers to the table whose id value in explain is lower than the current table id value).

Explain EXTENDED select * from actor where id = 1

2 、 explain partitions

There is an extra partitions field compared to explain, and if the query is based on a partition table, it shows the partition that the query will access.

Columns in Explain

Id column

The number of the id column is the serial number of the select, there are several id for every select, and the order of the id is increased in the order in which the select appears.

The larger the id, the higher the execution priority, the same id is executed from top to bottom, and id is the last execution of NULL.

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 include subqueries and union.

Explain select * from film where id=1

Primary: outermost select in complex queries

Subquery: subqueries contained in select (not in the from clause)

Derived: a 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 after the union keyword.

EXPLAIN select 1 union all select 1

Table column

This column indicates which table a row of explain is accessing.

When there is a subquery in the from clause, the table column is formatted, indicating that the current query depends on the query of id=N, so execute the query of id=N first.

When there is union, the value of the table column of UNION RESULT is, and 1 and 2 represent the select row id that participates in union.

Type column

This column represents the association type or access type, that is, the MySQL determines how to look up the rows in the table, looking for the approximate range of the data rows.

The order from best to worst is system > const > eq_ref > ref > range > index > All.

In general, you need to ensure that the query reaches the range level, preferably ref.

NULL:MySQL can decompose query statements during the optimization phase without having to access tables or indexes during the execution phase. For example, if you select the minimum value in the index column, you can find the index separately without having to access the table at execution time.

EXPLAIN select min (id) from film

Const and system:mysql can optimize some part of the query and convert it into a constant (which can be seen 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 faster. System is a special case of const, where only one row of elements in the table matches as system.

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

All parts of the eq_ref:primay key or unique key index are concatenated, and at most one qualified record is returned. This is probably the best join type outside of const, and this kind of type does not occur in simple select queries.

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

Ref: compared to eq _ ref, it does not apply to a unique index, but uses a normal index or a partial prefix of a unique index. When an index is compared to a value, multiple rows that match the criteria may be found.

Simple select query, name is a normal index (non-primary key index or unique index)

EXPLAIN select * from film where name='film1'

Associated table query, idx_film_actor_id is the joint index of film_id and actor_id, which uses 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 in (), between, >, =, and so on. Use an index to retrieve a given range of rows.

EXPLAIN select * from actor WHERE id > 1

Index: scan full table indexes, usually faster than All

EXPLAIN select * from film

All: a full table scan, which means that MySQL needs to find the desired row from beginning to end. In this case, you need to add an index to optimize.

EXPLAIN SELECT * from actor

Possible_ Keys column

This column shows which queries select might use to find it.

When explain, there may be a situation where possible_keys has columns and key is displayed as NULL, because there is not much data in the table, and MySQL thinks that the index is not very helpful to this query and chooses a full table scan.

If the column is NULL, there is no associated index. In this case, you can improve query performance by checking the where clause to see if an appropriate index can be created, and then use 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 the index is not used, it is renamed as NULL. If you want to force MySQL to use or ignore indexes in the possible_keys column, use force index, ignore index in the query.

Key_ Lenn column

This column shows the number of bytes used by mysql in the index, which allows you to estimate which columns in the index are used.

EXPLAIN SELECT * from film_actor where film_id = 1

Film_actor 's federated index idx_film_actor_id consists of two id columns, film_id and actor_id, and each int is 4 bytes. From the key_len=4 in the result, it can be inferred that the query uses the first column, the film_id column, to perform an index lookup.

The ken_len calculation rules are as follows:

String

Char (n): n byte length

Varchar (n): n bytes store the string length, or 3n+2 in the case of utf-8

Numerical type

Tinyint:1 byte

Smallint:2 byte

Int:4 byte

Bigint:8 byte

Time type

Date:3 byte

Timestamp:4 byte

Datetime:8 byte

If the field is allowed to be NULL, a 1-byte record is required to determine whether it is NULL

The maximum length of the index is 768 bytes. When the string is too long, MySQL will do a process similar to prefix indexing, extracting the first half of the string for indexing.

Ref column

This column shows the columns or constants used by the table to find values in the index of the key column record, such as const (constant), field name, and so on. It is usually the value to the right of the equal sign in the query or association condition. If it is constant, then the refl column is const, and if it is not constant, the ref column is the field name.

EXPLAIN SELECT * from film_actor where film_id = 1

Row column

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

Extra column

This column is additional information.

Using index: use an overlay index (the field of the result set is the index, that is, the film_id after select)

Explain select film_id from film_actor where film_id=1

Using index condition: the column of the query is not completely covered by the index. It is a leading range in the where condition.

Explain select * from film_actor where film_id > 1

Using where: use the where statement to process the results, and the columns of the query are not overwritten by the index

Explain select * from actor where name ='a'

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

Explain select DISTINCT name from actor

Actor.name has no index, and a temporary table is created to process the distinct.

Explain select DISTINCT name from film

File.name sets up a normal index, where Extra is Using index when querying, and no temporary tables are used.

Using filesort: the external sort will be used instead of the index, and the data will be sorted from memory when the data is small, otherwise it needs to be sorted on disk. In this case, it is generally necessary to consider using indexes to optimize.

Explain select * from actor order by name

Actor.name does not create an index and browses the entire acotr table, saving the sort keyword name and the corresponding id, and then sorts the name and retrieves the row records.

Explain select * from film order by name

Film.name builds the idx_name index, and extra is Using index when querying.

Select tables optimized away: use some aggregate functions (such as max, min) to access a field that has an index

Explain select min (id) from film

After reading the above about why explain is a Mysql database performance optimization artifact, many readers must have some understanding, if you need to get more industry knowledge and information, you can continue to follow our industry information column.

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.