Explain how to analyze SQL efficiency

The Explain command is the first recommended command for solving database performance problems. Most performance problems can be easily solved with this command. Explain can be used to view the execution effect of SQL statements, which can help choose better indexes and optimize query statements, and write better optimized statements.

Explain syntax:

EXPLAIN tbl_name or: EXPLAIN [EXTENDED] SELECT select_options

The former can derive the field structure of a table, etc., while the latter mainly provides some related index information. Today we will focus 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 = '[email protected]'; 

Execution Result:

Let’s take a look at each attribute:

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

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

SIMPLE: Simple SELECT (does not use UNION or subqueries, etc.)

PRIMARY: the outermost SELECT

UNION: The second or subsequent SELECT statement in a UNION

DEPENDENT UNION: The second or subsequent SELECT statement in a UNION depends on the outer query

UNION RESULT: The result of UNION.

SUBQUERY: The first SELECT in a subquery

DEPENDENT SUBQUERY: The first SELECT in a subquery that depends on the outer query

DERIVED: SELECT of the exported table (subquery in the FROM clause)

3. table: shows the actual table name (such as select * from customer;) or table alias (such as select * from customer a) of which the data in this row is related;

4. type: This column is the most important. It shows which type of connection is used and whether an index is used. It is one of the key items for analyzing performance bottlenecks using the Explain command.

The results from best to worst are:

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

Generally speaking, you must ensure that the query reaches at least the range level, preferably the ref level, otherwise performance issues may occur.

all : means to scan the entire table row by row, starting from the first row of the table. If you are unlucky, you may scan the last row.

index : slightly better performance than all.
In layman's terms: all scans all data rows, equivalent to data_all index scans all index nodes, equivalent to index_all

Note: all is scanning along the disk, index is scanning along the index

range : means that when querying, you can scan the range based on the index

explain select * from customer where customer_id > 4;

index_subquery In a subquery, scan based on an index other than a unique index;

unique_subquery scans based on a unique index in a subquery, similar to EQ_REF;

index_merge multiple range scans. There is an index on the connection field of each table in the connection of the two tables and the indexes are in order, and the results are merged together. Applicable to the union and intersection operations of sets.

ref_or_null is similar to REF, except that the search condition includes the case where the value of the connection field can be NULL, such as where col = 2 or col is null

fulltext full text index

ref This is also an index access that returns all rows that match a single value. However, it may find multiple qualifying rows, so it should be a mixture of search and scan (also a range interval, but more precise than range).

explain select * from payment where customer_id =4;

eq_ref means directly referencing a row of data (accurate to one row of data) through the index column. It is commonly used in join queries.

const, system, null When MySQL can optimize part of the query 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: The column indicates which index MySQL can use to find rows in the table

6. key: Shows the key (index) that MySQL actually decided to use. If no index is selected, the key is NULL

7. key_len: Displays the key length that MySQL decides to use. If key is NULL, length is NULL. The length of the index to use. The shorter the length, the better without losing accuracy.

8. ref: Shows which column or constant is used together with key to select rows from the table.

9. rows: Shows the number of rows that MySQL thinks it must examine to execute the query.

10. Extra: Contains detailed information on how MySQL solves queries and is also one of the key reference items.

using index: This indicates that MySQL uses a covering index to avoid accessing the data rows of the table, which is quite efficient! 
using where: This indicates that the server will filter the rows after receiving them from the storage engine. Some where conditions may contain columns that belong to the index. When the index is read, it will be filtered. Therefore, some where statements do not have the description of "using where" in the extra column. 
using temporary: This means that MySQL uses a temporary table when sorting the query results. 
using filesort: This means that MySQL will use an external index to sort the data instead of reading it in the order of the index in the table. 

In addition, the extended extension of explain can provide some additional query optimization information based on the original explain. This information can be obtained through the mysql show warnings command. Here 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 = '[email protected]';

Next, execute Show Warnings

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

|
+---------+------+--------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
-------------------------------+
| Warning | 1681 | 'EXTENDED' is deprecated and will be removed in a future release
ase.

|
| Note | 1003 | /* select#1 */ select sum(`sakila`.`b`.`amount`) AS `sum(amount
nt)` from `sakila`.`customer` `a` join `sakila`.`payment` `b` where ((`sakila`.`
b`.`customer_id` = `sakila`.`a`.`customer_id`) and (`sakila`.`a`.`email` = 'JANE
[email protected]')) |
+---------+------+--------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
-------------------------------+
2 rows in set (0.00 sec)

You can see that the optimizer automatically removes the condition that 1=1 is always true.

MySQL 5.1 began to support the partitioning function, and the explain command also added support for partitioning. You can use the explain partitions command to view the partitions accessed by SQL.

The above explain method of analyzing SQL efficiency is all the content that the editor shares with you. I hope it can give you a reference. I also hope that you will support 123WORDPRESS.COM.