Explain do you really know how to use it? August more challenge

EXPLAIN statements provide information about how MySQL executes statements. EXPLAIN can be applied to SELECT, DELETE, INSERT, REPLACE, and UPDATE statements

Explain returns one row of information for each table used in the SELECT statement. It lists the output tables in the order they were read by mysql while processing the statement. Mysql uses the nested loop join method to resolve all joins, which means mysql reads a row from the first table, then finds a matching row in the second table, the third table, and so on. After processing all the tables, Mysql outputs the selected columns and backtracks through the list of tables until it finds a table with more matching rows. Read the next row from the table, and proceed to the next table.

Explain Output column information

EXPLAIN SELECT s.*, t.* FROM student s, teacher t
Copy the code

You can see that the explain command isselectThe identifier statement generates a row of information for each table, where the ID is the same, indicating that the two tables are executed from top to bottom, regardless of the order of execution in the SQL.

EXPLAIN SELECT s.id, s.NAME, c.id, c.NAME FROM class c, student s WHERE s.tid = ( SELECT t.id FROM teacher t WHERE t.id = "1")
Copy the code

If the ids are the same, they can be considered as a group and executed from top to bottom in the order shown in the result set (regardless of the order declared in the SQL). A larger ID indicates a higher priority, and the command is executed first.

Simple query without any complex queries such as subqueries or union [see example 1.1.1 for details]

The result of the merger of two unions. [See example 1.2.3 for details]

1.2.5 DEPENDENT UNION

EXPLAIN SELECT `NAME` FROM tb_employees_china WHERE `NAME` IN ( SELECT `NAME` FROM tb_employees_china UNION SELECT `NAME` FROM tb_employees_usa )
Copy the code

The first condition to satisfy the UNION, and the second or subsequent select in the UNION, depends on external queries.

Select in a subquery that relies on an external query [see example 1.2.4 for details] The first select of this example neutron query is identified as dependent subQuery

1.2.8 DERIVED

Subqueries included in the FROM clause are marked as derived, and mysql recurses these subqueries to put the results in temporary tables (temporary tables are a system burden, but sometimes have to be used).

Note: In this example, the mysql environment is: 5.7

EXPLAIN SELECT * FROM ( SELECT id FROM tb_course tc ) temp
Copy the code

Because Mysql 8.0 is optimized over Mysql 5.7, the above instance will be explained in the 8.0 environment. The subquery will not be identified as derived. No instance of Mysql 8.0 derived has been found. (Follow-up findings will be supplemented)

Subqueries whose results cannot be cached must recalculate the results for each row of the external query

The name of the table referenced by the output row. There are three cases in addition to the table name

• 
  : union of id values between M and N [see example 1.2.4 for details]
  
• 
  : derived table result with id value N [see example 1.2.8 for details]
  
• 
  : result of materialized subquery with id value N [see example 1.2.10 for details]
  

Full scan, a full table scan is performed for each row combination in the table. If the table is the first table that is not marked const, ALL can usually be avoided by indexes that allow rows to be retrieved from the table based on constant or column values from the previous table.

ALL is a violent and primitive search method that is time-consuming and inefficient. But Mysql official describes a few cases where you can use ALL scans:

• The table is so small that performing a table scan is much faster than interrupting a key lookup, which is common for short tables with fewer than 10 rows
• For index columns, there are no restrictions available in the ON or WHERE clauses
• Comparing index columns to constant values
• A low cardinality key (many rows match the key value) is being used through another column, in which case Mysql assumes that multiple key lookups may be required by using keys and that table scans will be faster.

For small tables, table scans are usually appropriate and have a negligible impact on performance, and for large tables, query optimization is a must.

SELECT * FROM `tb_employees_china` WHERE `name` = "zhangsan"
Copy the code

This is because the name column is neither a primary key nor an index, so a full scan is used to find it.

For example, between… between… between… between… between… between… between… And, <, >, in, or all belong to index range scans.

What makes eQ_REF so much more interesting than ref is that eQ_REF knows that there is only one result set for this type of lookup, and only one result set if the lookup is using a primary key or a unique index. It is known before the search that there must be only one result set, so the query is stopped immediately the first time a value is found. This type of connection performs a precise query each time and does not require much scanning, so the search is more efficient.

select * from tb_employees_usa join tb_employees_china using(id); \Copy the code

The indexes that the query might use are listed here

The smaller the key_len, the better the index. How to calculate the key_len length?

Calculate the length of an index using our execution plan above.



Where, the index name isidx_nameThe index type of thevarchar(30)According to the above chart,varchar(30)Key_len as follows:30 * 3 + 2 + 1 = 93

Key_len can also be used to determine whether the union index is in effect and which union index is being overridden, as shown in an example.

We will betb_employees_usaDelete all indexes from the table and create a joint index(name,age)

Execute the following SQL to view the execution plan

explain select * from tb_employees_usa where name='rose';

1Copy the code

• 1

key_lenThe value is 93, and the SQL is calculated to use a single index of the federated indexesname

Then run the following SQL to view the execution plan

explain select * from tb_employees_usa where name='rose';
Copy the code

key_lenThe value is 98, and the SQL is calculated to use the federated index.

Indicates the number of rows to be scanned during SQL execution. A larger value indicates the number of rows to be scanned and the corresponding time is longer. However, it is important to note that the rows output in EXPLAIN is only an estimate and cannot be fully trusted.

Performance from good to bad:

using index > using where > using temporary > using filesort
Copy the code

• using index: overwrites the index and does not need to return to the table
• using where: Column data from only used
• using temporary: indicates that MySQL needs to use temporary tables to store result sets. This is common in sorted and grouped queries
• using filesort: MySQL cannot use the index to complete the sort operation called “file sort”, generally has this value, it is recommended to use the index to optimize
• using join buffer: highlights the fact that indexes are not used when fetching join conditions and that connection buffers are needed to store intermediate results. If this value occurs, indexes should be added on a case-by-case basis to improve performance
• distinct: The distInc keyword is used in the select section