How to use the EXPLAIN command in SQL

In daily work, we sometimes run slow queries to record some SQL statements that take a long time to execute. Finding these SQL statements does not mean that the job is done. Sometimes we often use the explain command to view the execution plan of these SQL statements to see whether the SQL statement uses an index and whether a full table scan is performed. This can be viewed through the explain command. So we have a deep understanding of MySQL's cost-based optimizer, and we can also get a lot of details about the access strategies that may be considered by the optimizer, and which strategy is expected to be adopted by the optimizer when running SQL statements.

EXPLAIN shows how MySQL uses indexes to process select statements and join tables. It can help choose better indexes and write more optimized query statements.

To use it, just add explain before the select statement:

like:

explain select surname,first_name form a,b where a.id=b.id 

1. EXPLAIN tbl_name

EXPLAIN tbl_name is a synonym for DESCRIBE tbl_name or SHOW COLUMNS FROM tbl_name.

2. EXPLAIN [EXTENDED] SELECT select_options

If you place the keywords EXPLAIN before a SELECT statement, MySQL explains how it processes the SELECT, providing information about how the tables are joined and the order in which they are joined.

With the help of EXPLAIN, you can know when you must add an index to the table to get a faster SELECT that uses the index to find records.

You can also tell whether the optimizer joined the tables in an optimal order. To force the optimizer to join a SELECT statement in the order in which the tables are named, the statement should begin with STRAIGHT_JOIN rather than just SELECT.

EXPLAIN returns one row for each table used in a SELECT statement. The tables are listed in the order in which they will be read by MySQL while processing the query. MySQL resolves all joins using a single-sweep multi-join approach. This means that MySQL reads a row from the first table, then finds a matching row in the second table, then in the third table, and so on. When all tables have been processed, it outputs the selected columns and returns to the table list until it finds a table with more matching rows. Read the next row from the table and continue processing the next table.

When the EXTENDED keyword is used, EXPLAIN produces additional information that can be viewed with SHOW WARNINGS. This information shows what the optimizer does after qualifying the table and column names in the SELECT statement, rewriting and executing the optimization rules, and may include other comments about the optimization process.

Each output row of EXPLAIN provides information about a table, and each row consists of the following columns:

id: SELECT identifier. This is the query sequence number for the SELECT.

select_type: SELECT type.

1. SIMPLE: Simple SELECT (without UNION or subquery)
2. PRIMARY: The outermost SELECT
3. UNION: The second or subsequent SELECT statement in a UNION
4. DEPENDENT UNION: The second or subsequent SELECT statement in a UNION depends on the outer query
5. UNION RESULT: The result of UNION
6. SUBQUERY: The first SELECT in a subquery
7. DEPENDENT SUBQUERY: The first SELECT in a subquery that depends on the outer query
8. DERIVED: SELECT of the exported table (subquery in the FROM clause)
   

table: table name

type: connection type

1. system: The table has only one row (= system table). This is a special case of the const join type.
2. const: The table has at most one matching row, which will be read when the query starts. Because there is only one row, the column values ​​in this row can be considered constants by the rest of the optimizer. const is used when comparing all parts of a PRIMARY KEY or UNIQUE index with constant values.
3. eq_ref: For each combination of rows from the previous table, read a row from this table. This is probably the best join type, except for const types. It is used when all parts of an index are used by the join and the index is UNIQUE or PRIMARY KEY. eq_ref can be used with indexed columns that are compared using the = operator. The comparison value can be a constant or an expression using a column of a table read before this one.
4. ref: For each combination of rows from the previous tables, all rows with matching index values ​​will be read from this table. If the join uses only a leftmost prefix of the key, or if the key is not UNIQUE or PRIMARY KEY (in other words, if the join cannot select a single row based on the key), then use ref. This join type is good if the keys used match only a small number of rows. ref can be used with indexed columns using the = or <=> operators.
5. ref_or_null: This join type is like ref, but adds that MySQL can specifically search for rows containing NULL values. This join type optimization is often used in resolving subqueries.
6. index_merge: This join type indicates that the index merge optimization method is used. In this case, the key column contains the list of used indexes and key_len contains the longest key element of the used indexes.
7. unique_subquery: This type replaces the ref of the IN subquery of the following form: value IN (SELECT primary_key FROM single_table WHERE some_expr); unique_subquery is an index lookup function that can completely replace the subquery and is more efficient.
8. index_subquery: This join type is similar to unique_subquery. Can replace IN subqueries, but only for non-unique indexes in subqueries of the following form: value IN (SELECT key_column FROM single_table WHERE some_expr)
9. range: retrieve only the rows in a given range, using an index to select the rows. The key column shows which index was used. key_len contains the longest key element of the used index. The ref column is NULL in this type. You can use range when using the =, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, or IN operators to compare keyword columns with constants.
10. index: This join type is the same as ALL, except that only the index tree is scanned. This is usually faster than ALL because index files are usually smaller than data files.
11. all: For each combination of rows from the previous tables, a full table scan is performed. This is usually bad if the table is the first table not marked const, and is usually very bad in other cases. Often you can add more indexes instead of using ALL so that rows can be retrieved based on constant values ​​or column values ​​in the previous table.
    

possible_keys: The possible_keys column indicates which index MySQL can use to find rows in the table. Note that this column is completely independent of the order of the tables shown in the EXPLAIN output. This means that some of the keys in possible_keys cannot actually be used in the order the table is generated.

key: The key column shows the key (index) that MySQL actually decided to use. If no index was chosen, key is NULL. To force MySQL to use or ignore the index on the possible_keys column, use FORCE INDEX, USE INDEX, or IGNORE INDEX in your query.

key_len: The key_len column shows the key length that MySQL decided to use. If key is NULL, length is NULL. Note that the key_len value allows us to determine how many parts of a multi-part keyword MySQL will actually use.

ref: The ref column shows which column or constant is used along with key to select rows from the table.

rows: The rows column shows the number of rows MySQL thinks it must examine to execute the query.

Extra: This column contains detailed information about how MySQL solved the query.

1. Distinct: After MySQL finds the first matching row, it stops searching for more rows for the current row combination.
2. Not exists: MySQL can perform LEFT JOIN optimization on the query. After finding one row that matches the LEFT JOIN criteria, it will not check more rows in the table for the previous row combination.
3. range checked for each record (index map: #): MySQL did not find a good index to use, but found that if the column values ​​from the previous table are known, a partial index may be used. For each combination of rows from the preceding tables, MySQL checks whether it can retrieve the row using the range or index_merge access method.
4. Using filesort: MySQL needs an extra pass to figure out how to retrieve the rows in sorted order. The sort is done by going through all the rows according to the join type and saving the sort key and row pointers for all rows that match the WHERE clause. The keys are then sorted, and the rows are retrieved in sorted order.
5. Using index: Retrieve column information from a table using only the information in the index tree without further searching to read the actual rows. This strategy can be used when the query uses only columns that are part of a single index.
6. Using temporary: To resolve the query, MySQL needs to create a temporary table to hold the results. A typical case is when the query contains GROUP BY and ORDER BY clauses that list columns in different cases.
7. Using where: The WHERE clause is used to restrict which rows are matched to the next table or sent to the client. Unless you specifically request or examine all rows from the table, your query may have some errors if the Extra value is not Using where and the table join type is ALL or index.
8. Using sort_union(...), Using union(...), Using intersect(...): These functions illustrate how to merge index scans for the index_merge join type.
9. Using index for group-by: Similar to the Using index method for accessing a table, Using index for group-by means that MySQL has found an index that can be used to query all columns of a GROUP BY or DISTINCT query without additionally searching the hard disk to access the actual table. Also, the index is used in the most efficient way so that for each group, only a small number of index entries are read.
   

By multiplying all the values ​​in the rows column of EXPLAIN output, you can get a hint about how a join will work. This should tell you roughly how many rows MySQL had to examine to execute the query. This product is also used to determine which multi-table SELECT statement to execute when you use the max_join_size variable to limit the query.

Summarize

This is the end of this article about the use of EXPLAIN command in SQL. For more information about the use of SQL EXPLAIN command, please search previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope you will support 123WORDPRESS.COM in the future!