Explain analyzes SQL efficiency and explain SQL Efficiency

Explain analyzes SQL efficiency and explain SQL Efficiency

The Explain command is the first recommended command for solving database performance. Most performance problems can be solved simply by using this command. The Explain command can be used to view the execution results of SQL statements, it can help you select better indexes and Optimize Query statements to write better optimization statements.

Explain Syntax:

EXPLAIN tbl_name or: EXPLAIN [EXTENDED] SELECT select_options

The former can obtain the field structure of a table, and the latter mainly provides related index information. The latter focuses on the latter.

Example:

EXPLAIN   SELECT sum(amount)     FROM customer a, payment b    WHERE     1 = 1          AND a.customer_id = b.customer_id          AND a.email = 'JANE.BENNETT@sakilacustomer.org';

Execution result:

The following describes the attributes:

1. id: This is the serial number of the SELECT query.

2. select_type: select_type is the select type, which can be:

SIMPLE: simple select (UNION or subquery is not used)
PRIMARY: exclusive SELECT
UNION: the second or subsequent SELECT statement in UNION
Dependent union: the second or subsequent SELECT statement in UNION, depending on the external Query
Union result: the RESULT of UNION.
SUBQUERY: The first select in the subquery.
Dependent subquery: The first select in the subquery, depending on the external Query
DERIVED: SELECT (subquery of the from clause) of the export table)

3. table: displays the actual table name of the table in this row (for example, select * from customer ;)
Or the table alias (such as select * from customer );

4. type: this column is the most important. It shows the category used for the connection and whether or not the index is used. It is one of the key items for analyzing the performance bottleneck using the Explain command.

The result values are as follows:
System> const> eq_ref> ref> fulltext> ref_or_null> index_merge> unique_subquery> index_subquery> range> index> ALL
In general, make sure that the query reaches at least the range level, and it is best to reach the ref level. Otherwise, performance problems may occur.
All: It means to scan the entire table row by row from the first row of the table. If you are lucky enough to scan the last row.
Index: Better performance than all,
In general, all scans all data rows, which is equivalent to data_all index scans all index nodes and equivalent to index_all.
Note: all is scanned along the disk, and index is scanned along the index.
Range: Scan the range according to the index during query.
Explain select * from customer where customer_id> 4;
Index_subqueryIn a subquery, A scan is performed based on an index other than a unique index;
Unique_subqueryIn a subquery, A scan is performed based on a unique index, similar to EQ_REF;
Index_mergeMulti-range scan. The join fields of each table connected to the two tables have indexes in order and the results are merged. This operation is applicable to Union and intersection of sets.
Ref_or_nullSimilar to REF, only the search conditions include: the value of the connection field can be NULL, for example, where col = 2 or col is null.
FulltextFull-text index
RefThis is also an index access. It returns all rows that match a single value. However, it may find multiple rows that meet the conditions, so it should be a mixture of search and scan (also a range, but more accurate than range ).
Explain select * from payment where customer_id = 4;
Eq_refIt means that using index columns to directly reference a certain row of data (accurate to a row of data) is common in connection queries.
Const, system, nullWhen mysql can optimize the query part and convert it into a constant, it will use this access type. For example, if you put the primary key of a row as the where condition, mysql can convert it into a constant and then query it.

5. possible_keys: indicates which index MySQL can use to find rows in the table.

6. key: displays the key (INDEX) actually determined by MySQL ). If no index is selected, the key is NULL.

7. key_len: displays the key length determined by MySQL. If the key is NULL, the length is NULL. The length of the index used. The shorter the length, the better.

8. ref: displays the column or constant used with the key to select rows from the table.

9. rows: displays the number of rows that MySQL considers to be required for query execution.

10. Extra: contains detailed information about MySQL queries and is also a key reference item.

Using index: This indicates that mysql uses the overwriting index to avoid accessing the data rows of the table, which is efficient! Using where: This indicates that the server will filter the rows received by the storage engine. Some where conditions have indexed columns. When it reads and uses indexes, it will be filtered out, therefore, some where statements do not show the using where statement in the extra column. Using temporary: this means that mysql uses a temporary table to sort the query results. Using filesort: mysql sorts data using an external index instead of reading data in the index order of the table.

In addition,ExtendedExtended foundations for original explain
Some additional query optimization information can be obtained through the show warnings command of mysql. The following is a simple example.

EXPLAIN EXTENDED
SELECT sum (amount)
FROM customer a, payment B
WHERE 1 = 1
AND a. customer_id = B. customer_id
AND a. email = 'Jane. BENNETT@sakilacustomer.org ';

Run Show Warnings.　　

Mysql> show warnings;
+ --------- + ------ + --------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
------------------------------- +
| Level | Code | Message

|
+ --------- + ------ + --------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
------------------------------- +
| Warning | 1681 | 'extended' is deprecated and will be removed in a future rele
Ase.

|
| Note | 1003 |/* select #1 */select sum ('sakila'. 'B'. 'amount ') AS 'sum (amou
Nt) 'From 'sakila'. 'customer' 'a 'join' sakila'. 'payment' 'B' where ('sakila '.'
B '. 'Customer _ id' = 'sakila'. 'A'. 'Customer _ id') and ('sakila '. 'A'. 'email' = 'Jane
BENNETT@sakilacustomer.org ') |
+ --------- + ------ + --------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
------------------------------- +
2 rows in set (0.00 sec)

We can see that the optimizer automatically removes the 1 = 1 constant standing condition.

MySQL5.1 began to support the partition function, while the explain command also added support for partitions. You can run the explain partitions command to view the partitions accessed by SQL.