explain command details


explain command

By adding the explain keyword before the select statement, MySQL will set a flag on the query, so that when the query is executed, the execution plan information will be returned instead of executing the SQL.

The explain command can obtain information about how MySQL executes SELECT statements to view the execution plan of these SQL statements, such as whether the SQL statement uses an index, whether it does a full table scan, etc. This is an indispensable part of query performance optimization. Therefore, we should form the habit of using explain analysis when developing SQL.

mysql> explain select * from actor;
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE      | actor | ALL  | NULL          | NULL | NULL    | NULL |    2 | NULL  |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+

There are 10 columns of information from export, namely id and select_type,table,type,possible_keys,key,key_len,ref,rows,Extra . The possible of these fields are explained below:

Listing explain
id The execution number identifies the row to which the select belongs.
If there is no subquery or associated query in the statement, there is only a unique select, and 1 will be displayed for each row. Otherwise, the inner select statements are generally numbered sequentially, corresponding to their positions in the original statement
select_type Displays whether the line is a simple or complex select.
If the query has any complex subqueries, the outermost layer is marked as PRIMARY (DERIVED, UNION, UNION reset)
table Which table (table name or alias) are you accessing
type Data access / read operation type (ALL, index, range, ref, eq_ref, const/system, NULL)
possible_keys Which indexes might be useful for efficient lookup
key Which index is actually used to optimize the query
key_len Length of index field
ref Shows the columns or constants used by the previous table to find values in the index of the key column record
rows The number of rows to read in order to find the desired row (estimated, imprecise).
By multiplying all rows column values, you can roughly estimate the number of rows that will be checked by the entire query
Extra Additional description and description of implementation.
partitions
(MySQL 8 NEW)		 If the query is based on a partition table, the partition that the query will access is displayed
filtered
(MySQL 8 NEW)		 Percentage of rows filtered by table criteria.
rows * filtered/100 can estimate the number of rows to be connected to the previous table in explain (the previous table refers to the table whose id value in explain is smaller than that of the current table)

After explain, you can also get the optimized query statement through the show warnings command to see what the optimizer has optimized.

mysql> explain extended select * from film where id = 1;
+----+-------------+-------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | film  | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+----------+-------+

mysql> show warnings;
+-------+------+--------------------------------------------------------------------------------+
| Level | Code | Message                                                                        |
+-------+------+--------------------------------------------------------------------------------+
| Note  | 1003 | /* select#1 */ select '1' AS `id`,'film1' AS `name` from `test`.`film` where 1 |
+-------+------+--------------------------------------------------------------------------------+

id

The number of the id column is the serial number of the select. If there are several selections, there will be several IDS, and the statements with larger IDS will be executed first.

  • If it is a subquery, there will be multiple id values that are incremented. The larger the id value, the higher the priority and the earlier it will be executed.
  • The id value may be NULL, indicating that this row is the joint result of other rows;
  • If the IDs are the same, they can be cons id ered as a group and executed from top to bottom.

MySQL divides select query into simple query and complex query. Complex queries are divided into three categories: simple subqueries, derived tables (subqueries in the from statement) and union queries.

Simple subquery:

mysql> explain select (select 1 from actor limit 1) from film;
+----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key      | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+
|  1 | PRIMARY     | film  | index | NULL          | idx_name | 32      | NULL |    1 | Using index |
|  2 | SUBQUERY    | actor | index | NULL          | PRIMARY  | 4       | NULL |    2 | Using index |
+----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+

Subquery in from clause:

mysql> explain select id from (select id from film) as der;
+----+-------------+------------+-------+---------------+----------+---------+------+------+-------------+
| id | select_type | table      | type  | possible_keys | key      | key_len | ref  | rows | Extra       |
+----+-------------+------------+-------+---------------+----------+---------+------+------+-------------+
|  1 | PRIMARY     | <derived2> | ALL   | NULL          | NULL     | NULL    | NULL |    2 | NULL        |
|  2 | DERIVED     | film       | index | NULL          | idx_name | 32      | NULL |    1 | Using index |
+----+-------------+------------+-------+---------------+----------+---------+------+------+-------------+

For example, when the above query is executed, there is a temporary table alias der, which is referenced by the external select query.

union query:

mysql> explain select 1 union all select 1;
+----+--------------+------------+------+---------------+------+---------+------+------+-----------------+
| id | select_type  | table      | type | possible_keys | key  | key_len | ref  | rows | Extra           |
+----+--------------+------------+------+---------------+------+---------+------+------+-----------------+
|  1 | PRIMARY      | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL | No tables used  |
|  2 | UNION        | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL | No tables used  |
| NULL | UNION RESULT | <union1,2> | ALL  | NULL          | NULL | NULL    | NULL | NULL | Using temporary |
+----+--------------+------------+------+---------------+------+---------+------+------+-----------------+

The union result is always placed in an anonymous temporary table. Because the temporary table does not appear in SQL, its id is NULL.


select_type

select_type indicates whether the corresponding row is a simple or complex query. If it is a complex query, which of the above three complex queries is it.

  • Simple: simple query, that is, the query does not contain subqueries and union s.
mysql> explain select * from film where id = 2;
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref   | rows | Extra |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
|  1 | SIMPLE      | film  | const | PRIMARY       | PRIMARY | 4       | const |    1 | NULL  |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
  • primary: the outermost select in a complex query.
  • Subquery: the subquery contained in the select (not in the from clause).
  • Derived: the subquery contained in the from clause. MySQL will store the results in a temporary table, also known as derived table.
mysql> explain select (select 1 from actor where id = 1) from (select * from film where id = 1) der;
+----+-------------+------------+--------+---------------+---------+---------+-------+------+-------------+
| id | select_type | table      | type   | possible_keys | key     | key_len | ref   | rows | Extra       |
+----+-------------+------------+--------+---------------+---------+---------+-------+------+-------------+
|  1 | PRIMARY     | <derived3> | system | NULL          | NULL    | NULL    | NULL  |    1 | NULL        |
|  3 | DERIVED     | film       | const  | PRIMARY       | PRIMARY | 4       | const |    1 | NULL        |
|  2 | SUBQUERY    | actor      | const  | PRIMARY       | PRIMARY | 4       | const |    1 | Using index |
+----+-------------+------------+--------+---------------+---------+---------+-------+------+-------------+
  • Union: the second and subsequent select in the union.
  • union result: select to retrieve the result from the union temporary table.
mysql> explain select 1 union all select 1;
+----+--------------+------------+------+---------------+------+---------+------+------+-----------------+
| id | select_type  | table      | type | possible_keys | key  | key_len | ref  | rows | Extra           |
+----+--------------+------------+------+---------------+------+---------+------+------+-----------------+
|  1 | PRIMARY      | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL | No tables used  |
|  2 | UNION        | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL | No tables used  |
| NULL | UNION RESULT | <union1,2> | ALL  | NULL          | NULL | NULL    | NULL | NULL | Using temporary |
+----+--------------+------------+------+---------------+------+---------+------+------+-----------------+
  • dependent union: first, the conditions of the UNION and the second and subsequent SELECT statements in the UNION must be met, and the statement depends on external queries.
  • dependent subquery: the same as DEPENDENT UNION relative UNION.

table

Table indicates which table, table name or alias the corresponding row is accessing.

  • The association optimizer will select the association order for the query, with the left depth first.
  • When there are subqueries in the from clause, the table column is in the < deriven > format, which means that the current query depends on the query with id=N, so the query with id=N is executed first.
  • When there is a union, the value of the table column of UNION RESULT is < union1, 2 >, and 1 and 2 represent the select row id participating in the union.

Note: MySQL treats these tables as ordinary tables, but these "temporary tables" do not have any indexes.


type

This column represents the association type or access type, that is, MySQL determines how to find rows in the table. It is a more important indicator.

The result values from good to bad are: null > system > const > Eq_ ref > ref > fulltext > ref_ or_ null > index_ merge > unique_ subquery > index_ subquery > range > index > ALL.

Generally speaking, it is necessary to ensure that the query reaches at least range level, preferably ref.

  • NULL: MySQL can decompose query statements in the optimization phase, and there is no need to access tables or indexes in the execution phase. For example, selecting the minimum value in the index column can be done by looking up the index value separately, and there is no need to access the table during execution.
mysql> explain select min(id) from film;
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                        |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
|  1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | Select tables optimized away |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
  • const, system: MySQL can optimize a part of the query and convert it into a constant (see the results of show warnings). It is often used when all columns of the primary key or unique key are compared with the constant. Therefore, the table has at most one matching row and can be read once, which is faster.
mysql> explain extended select * from (select * from film where id = 1) tmp;
+----+-------------+------------+--------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table      | type   | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+------------+--------+---------------+---------+---------+-------+------+----------+-------+
|  1 | PRIMARY     | <derived2> | system | NULL          | NULL    | NULL    | NULL  |    1 |   100.00 | NULL  |
|  2 | DERIVED     | film       | const  | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+------------+--------+---------------+---------+---------+-------+------+----------+-------+

mysql> show warnings;
+-------+------+---------------------------------------------------------------+
| Level | Code | Message                                                       |
+-------+------+---------------------------------------------------------------+
| Note  | 1003 | /* select#1 */ select '1' AS `id`,'film1' AS `name` from dual |
+-------+------+---------------------------------------------------------------+
  • eq_ref: all parts of the primary key or unique key index are used by connection, and only one qualified record will be returned at most. This is probably the best join type outside const, which will not appear in a simple select query.
mysql> explain select * from film_actor left join film on film_actor.film_id = film.id;
+----+-------------+------------+--------+---------------+-------------------+---------+-------------------------+------+-------------+
| id | select_type | table      | type   | possible_keys | key               | key_len | ref                     | rows | Extra       |
+----+-------------+------------+--------+---------------+-------------------+---------+-------------------------+------+-------------+
|  1 | SIMPLE      | film_actor | index  | NULL          | idx_film_actor_id | 8       | NULL                    |    3 | Using index |
|  1 | SIMPLE      | film       | eq_ref | PRIMARY       | PRIMARY           | 4       | test.film_actor.film_id |    1 | NULL        |
+----+-------------+------------+--------+---------------+-------------------+---------+-------------------------+------+-------------+
  • ref: compared to eq_ref, instead of using a unique index, use the partial prefix of a normal index or a unique index. If the index is to be compared with a value, multiple qualified rows may be found.
-- 1. simple select Query, name Is a normal index (non unique index)
mysql> explain select * from film where name = "film1";
+----+-------------+-------+------+---------------+----------+---------+-------+------+--------------------------+
| id | select_type | table | type | possible_keys | key      | key_len | ref   | rows | Extra                    |
+----+-------------+-------+------+---------------+----------+---------+-------+------+--------------------------+
|  1 | SIMPLE      | film  | ref  | idx_name      | idx_name | 33      | const |    1 | Using where; Using index |
+----+-------------+-------+------+---------------+----------+---------+-------+------+--------------------------+

-- 2. Association table query, idx_film_actor_id yes film_id and actor_id The joint index of is used here film_actor Left prefix of film_id part
mysql> explain select * from film left join film_actor on film.id = film_actor.film_id;
+----+-------------+------------+-------+-------------------+-------------------+---------+--------------+------+-------------+
| id | select_type | table      | type  | possible_keys     | key               | key_len | ref          | rows | Extra       |
+----+-------------+------------+-------+-------------------+-------------------+---------+--------------+------+-------------+
|  1 | SIMPLE      | film       | index | NULL              | idx_name          | 33      | NULL         |    3 | Using index |
|  1 | SIMPLE      | film_actor | ref   | idx_film_actor_id | idx_film_actor_id | 4       | test.film.id |    1 | Using index |
+----+-------------+------------+-------+-------------------+-------------------+---------+--------------+------+-------------+
  • ref_or_null: similar to ref, but can search for rows with NULL value.
mysql> explain select * from film where name = "film1" or name is null;
+----+-------------+-------+-------------+---------------+----------+---------+-------+------+--------------------------+
| id | select_type | table | type        | possible_keys | key      | key_len | ref   | rows | Extra                    |
+----+-------------+-------+-------------+---------------+----------+---------+-------+------+--------------------------+
|  1 | SIMPLE      | film  | ref_or_null | idx_name      | idx_name | 33      | const |    2 | Using where; Using index |
+----+-------------+-------+-------------+---------------+----------+---------+-------+------+--------------------------+
  • index_merge: indicates that the optimization method of index merging is used. For example, the following table: id is the primary key, tenant_id is a normal index. or does not use the primary key, but uses the primary key(id) and tenant_id index.
mysql> explain select * from role where id = 11011 or tenant_id = 8888;
+----+-------------+-------+-------------+-----------------------+-----------------------+---------+------+------+-------------------------------------------------+
| id | select_type | table | type        | possible_keys         | key                   | key_len | ref  | rows | Extra                                           |
+----+-------------+-------+-------------+-----------------------+-----------------------+---------+------+------+-------------------------------------------------+
|  1 | SIMPLE      | role  | index_merge | PRIMARY,idx_tenant_id | PRIMARY,idx_tenant_id | 4,4     | NULL |  134 | Using union(PRIMARY,idx_tenant_id); Using where |
+----+-------------+-------+-------------+-----------------------+-----------------------+---------+------+------+-------------------------------------------------+
  • Range: range scanning usually occurs in operations such as in(), between, >, <, > = and indicates that an index is used to retrieve rows of a given range. A good SQL efficiency should be guaranteed to this level at least.
mysql> explain select * from actor where id > 1;
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | actor | range | PRIMARY       | PRIMARY | 4       | NULL |    2 | Using where |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
  • Index: like ALL, the difference is that MySQL only needs to scan the index tree, which is usually faster than ALL.
mysql> explain select count(*) from film;
+----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key      | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+
|  1 | SIMPLE      | film  | index | NULL          | idx_name | 33      | NULL |    3 | Using index |
+----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+
  • ALL: full table scan, which means that MySQL needs to find the required rows from beginning to end. Usually, this needs to increase the index for optimization.
mysql> explain select * from actor;
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE      | actor | ALL  | NULL          | NULL | NULL    | NULL |    2 | NULL  |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+

possible_keys

This column shows which indexes the query may use to find, but the listed indexes may not be useful for the subsequent optimization process.

Possible when explain ing_ When the keys column has a value and the key column displays NULL, it is usually because there is not much data in the table. MySQL thinks that the index is not helpful for this query, so it selects the full table query.

If the column is NULL, the associated index is not used. In this case, you can improve query performance by checking the where clause to see if you can create an appropriate index, and then use explain to see the effect.


key

The key column displays the keys (indexes) that MySQL actually decides to use.

If no index is used, the column is NULL. If you want to force MySQL to use or ignore possible_ The index in the keys column. You can use force index and ignore index in the query.


key_len

key_ The len column shows the index length (bytes) determined by MySQL. This value can be used to calculate which columns in the index are used.

  • If the value is NULL, the length is NULL.
  • Without losing accuracy, the shorter the index length, the better.

For example, film_ Union index idx of actor_ film_actor_id by film_id and actor_id consists of two int columns, and each int is 4 bytes. Pass the key in the result_ Len = 4, it can be inferred that the query uses the first column: film_id column to perform index lookup.

mysql> explain select * from film_actor where film_id = 2;
+----+-------------+------------+------+-------------------+-------------------+---------+-------+------+-------------+
| id | select_type | table      | type | possible_keys     | key               | key_len | ref   | rows | Extra       |
+----+-------------+------------+------+-------------------+-------------------+---------+-------+------+-------------+
|  1 | SIMPLE      | film_actor | ref  | idx_film_actor_id | idx_film_actor_id | 4       | const |    1 | Using index |
+----+-------------+------------+------+-------------------+-------------------+---------+-------+------+-------------+

key_len calculation rules are as follows:

  • character string
    • char(n): n-byte length
    • varchar(n): 2-byte storage string length; In the case of utf-8, the length is 3*n + 2
  • value type
    • tinyint: 1 byte
    • smallint: 2 bytes
    • int: 4 bytes
    • bigint: 8 bytes
  • Time type
    • date: 3 bytes
    • timestamp: 4 bytes
    • datetime: 8 bytes
  • If NULL is allowed for the field, whether the 1-byte record is null is required

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


ref

This column shows the columns or constants used in the table lookup value in the index of the key column record. The common ones are const (constant), func, NULL and field name (for example: film.id).


rows

The rows column shows the number of rows MySQL thinks it must check when executing a query. Note that this is an estimate.


Extra

Extra is another very important column in the EXPLAIN output. This column displays some detailed information of MySQL in the query process and important supplementary information to the query plan during the query execution by the MySQL query optimizer.

  • distinct: once MySQL finds a row that matches the union of rows, it will no longer search.
mysql> explain select distinct name from film left join film_actor on film.id = film_actor.film_id;
+----+-------------+------------+-------+-------------------+-------------------+---------+--------------+------+------------------------------+
| id | select_type | table      | type  | possible_keys     | key               | key_len | ref          | rows | Extra                        |
+----+-------------+------------+-------+-------------------+-------------------+---------+--------------+------+------------------------------+
|  1 | SIMPLE      | film       | index | idx_name          | idx_name          | 33      | NULL         |    3 | Using index; Using temporary |
|  1 | SIMPLE      | film_actor | ref   | idx_film_actor_id | idx_film_actor_id | 4       | test.film.id |    1 | Using index; Distinct        |
+----+-------------+------------+-------+-------------------+-------------------+---------+--------------+------+------------------------------+

  • Using index: this occurs when all the requested columns of the table are indexes. Instead of reading the data file, information can be obtained from the index tree (index file). This is also the identification of overwriting the index, which is a performance of high performance. This is completed by the MySQL service layer, and there is no need to return to the table to query records.

    • If using where appears at the same time, it indicates that the index is used to search the index key value;
    • No using where indicates that the index is used to read data rather than perform a lookup action.
      mysql> explain select id from film order by id;
      +----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
      | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
      +----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
      | 1 | SIMPLE | film | index | NULL | PRIMARY | 4 | NULL | 3 | Using index |
      +----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+

  • using where: the WHERE clause is used to limit which rows match the next table or are returned to the user. Note: using where in the Extra column indicates that the MySQL server will return the storage engine to the service layer and then apply the WHERE condition for filtering. If it meets the requirements, it will be left behind, and if it does not, it will be discarded.

mysql> explain select * from film where id > 1;
+----+-------------+-------+-------+---------------+----------+---------+------+------+--------------------------+
| id | select_type | table | type  | possible_keys | key      | key_len | ref  | rows | Extra                    |
+----+-------------+-------+-------+---------------+----------+---------+------+------+--------------------------+
|  1 | SIMPLE      | film  | index | PRIMARY       | idx_name | 33      | NULL |    3 | Using where; Using index |
+----+-------------+-------+-------+---------------+----------+---------+------+------+--------------------------+
  • Using temporary: indicates that the intermediate results need to be saved with a temporary table. It is often used in GROUP BY and ORDER BY operations. Generally, it indicates that the query needs to be optimized. Even if the use of temporary tables cannot be avoided, the use of hard disk temporary tables should be avoided as much as possible.
-- 1. actor.name There is no index. A temporary table is created at this time distinct
mysql> explain select distinct name from actor;
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra           |
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------+
|  1 | SIMPLE      | actor | ALL  | NULL          | NULL | NULL    | NULL |    2 | Using temporary |
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------+

-- 2. film.name Established idx_name Index, when querying extra yes using index，No temporary tables are used
mysql> explain select distinct name from film;
+----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key      | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+
|  1 | SIMPLE      | film  | index | idx_name      | idx_name | 33      | NULL |    3 | Using index |
+----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+
  • Using filesort: MySQL needs an extra pass to find out how to retrieve rows in sorted order. Sorting is accomplished by browsing all rows according to the join type and saving sorting keywords and row pointers for all rows that match the WHERE clause. The keywords are then sorted and the rows are retrieved in the sort order.
    • MySQL can generate ordered results in two ways, through sorting operations or using indexes. When Using filesort appears in Extra, it indicates that the former is used for query sorting. Note that although it is called filesort, it does not mean that files are used for sorting. Whenever possible, sorting is completed in memory. In most cases, index sorting is faster, so you should also consider optimizing queries at this time.
    • filesort can be sorted in two ways:
      • Generate < sort for the records to be sorted_ Key, rowid >, which contains only the sorting field and rowid. After sorting, there are only rowids sorted by fields. Therefore, it is also necessary to use the rowid to get the required column values, which may lead to a large amount of random I/O reading consumption. The solution is to use overlay indexes.
      • Generate < sort for the records to be sorted_ key, additional_ Fields >, which contains sorting fields and all columns to be returned. After sorting, there is no need to return to the table, but the metadata is much longer than the first method and requires more space for sorting. The solution is: the algorithm used by filesort is QuickSort, that is, the metadata generated by the records to be sorted is sorted in blocks, and then the blocks are merged using the mergeport method. The memory space that filesort can use is the parameter sort_ buffer_ The value of size is 2M by default. When too many sorting records cause sort_ buffer_ When the size is not enough, MySQL will use temporary files to store each block, then sort each block, and then merge the blocks several times, and finally complete the sorting globally. Therefore, you can increase sort_buffer_size to solve the filesort problem.
-- 1. actor.name No index created, browse actor The entire table, save sorting keywords name And corresponding id，Then sort name And retrieve row records
mysql> explain select * from actor order by name;
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra          |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
|  1 | SIMPLE      | actor | ALL  | NULL          | NULL | NULL    | NULL |    2 | Using filesort |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+

-- 2. film.name Established idx_name Index, when querying extra yes using index
mysql> explain select * from film order by name;
+----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key      | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+
|  1 | SIMPLE      | film  | index | NULL          | idx_name | 33      | NULL |    3 | Using index |
+----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+

  • Not exists: MYSQL optimizes the LEFT JOIN. Once it finds a row that matches the LEFT JOIN criteria, it will no longer search.

  • Using index condition: This is a new feature of MySQL 5.6, called "index condition push". In short, MySQL can't perform like operations on the index, but now it can. This reduces unnecessary I/O operations, but it can only be used on the secondary index.

  • Using join buffer: indicates that the connection buffer is used.

    • BlockNestedLoop, the connection algorithm is a block nested loop connection.
    • BatchedKeyAccess, the connection algorithm is a batch index connection.

  • impossible where: the value of the clause is always false and cannot be used to get any tuples.

  • select tables optimized away: without the GROUP BY clause, optimize the MIN/MAX operation based on the index, or optimize the COUNT(*) operation for the MyISAM storage engine without waiting for the execution stage, that is, the optimization is completed at the stage of query execution plan generation.

Keywords: MySQL

Added by programming.name on Mon, 03 Jan 2022 02:13:21 +0200

Popular Keywords

©2024 Programming VIP