Explain SQL efficiency and explain SQL Efficiency

Explain 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   WHERE1 = 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 data in this row about the actual table name (for example, select * from customer;) or table alias (for example, select * from customer a) of the table );

4. type:This column is the most important and shows the category used for the connection and whether or not indexes are used. It is one of the key items for analyzing performance bottlenecks 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:Column indicates which index MySQL can use to find rows in the table

6. key:Displays the keys (indexes) actually used 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 to select rows from the table with the key.

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

10. Extra:It 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, the extended extension of explain can provide additional query optimization information based on the original explain, which can be obtained through the mysql show warnings command. The following is a simple example.

EXPLAIN EXTENDEDSELECT sum(amount)FROM customer a, payment bWHERE 1 = 1AND a.customer_id = b.customer_idAND 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 release.|| Note | 1003 | /* select#1 */ select sum(`sakila`.`b`.`amount`) AS `sum(amount)` 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.

The above explain Method for Analyzing SQL efficiency is to share all the content with you, hoping to give you a reference, and hope you can also support the help house.