Usage of EXPLAIN in MySQL database and Its Precautions

MySQLDatabaseEXPLAINThe usage and precautions are the main content we will introduce in this article. Next let's take a look at this part.

1. EXPLAIN tbl_name is a synonym for DESCRIBE tbl_name or show columns from tbl_name.

2. EXPLAIN [EXTENDED] SELECT select_options if the keyword EXPLAIN is put before the SELECT statement, MySQL will EXPLAIN how it processes SELECT and provide the order of how the table is joined and joined. With the help of EXPLAIN, you can know when to add an index to the table to obtain a faster SELECT statement that uses indexes to search for records.

You can also know whether the optimizer joins the table in an optimal order. To force the optimizer to set a SELECT statement to join in the table naming order, the statement should start with STRAIGHT_JOIN, not just SELECT.

EXPLAIN returns a row of information for each table in the SELECT statement. Tables are listed in the order they are read by MySQL during query processing. MySQL scans the single-sweep multi-join connections multiple times at a time to solve all connections. This means that MySQL reads a row from the first table, finds a matching row in the second table, and then in 3rd tables. After all the tables are processed, It outputs the selected columns and returns 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 generates additional information, which can be viewed using show warnings. This information shows the optimizer limits the tables and column names in the SELECT statement, what the SELECT statement looks like after rewriting and executing the optimization rule, and may include other annotations of the optimization process.

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

Id: SELECT identifier. This is the serial number of the SELECT query.

Select_type: SELECT type.

1. SIMPLE: simple select (UNION or subquery is not used)

2. PRIMARY: exclusive SELECT

3. UNION: the second or subsequent SELECT statement in UNION

4. dependent union: the second or subsequent SELECT statement in the UNION statement depends on the external query.

5. union result: RESULT of UNION

6. SUBQUERY: The first SELECT IN THE SUBQUERY

7. dependent subquery: The first select in the subquery, depending on the external Query

8. DERIVED: SELECT (subquery of the from clause) of the export table)

Table: table Name

Type: Join 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 can have at most one matching row, which will be read at the beginning of the query. Because there is only one row, the column value in this row can be considered as a constant by the rest of the optimizer. Const is used to compare all the parts of a primary key or UNIQUE index with a constant value.

3. eq_ref: for each row combination from the preceding table, read a row from the table. This may be the best join type except the const type. It is used to join all parts of an index and the index is UNIQUE or primary key. Eq_ref can be used to compare indexed columns with the = Operator. The comparison value can be a constant or an expression that uses the column of the table read before the table.

4. ref: for each row combination from the preceding table, all rows with matching index values will be read from this table. If the join only uses the 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 keyword), use ref. If the key used matches only a few rows, the join type is good. Ref can be used for indexed columns using the = or <=> operator.

5. ref_or_null: The join type is like ref, but MySQL can search for rows containing NULL values. The optimization of this join type is often used in solving subqueries.

6. index_merge: The join type indicates that the index merge optimization method is used. In this case, the key column contains the list of indexes used, and key_len contains the longest key element of the index used.

7. unique_subquery: Replace the ref: value IN (SELECT primary_key FROMsingle_table WHERE some_expr) IN the following form of IN subquery. unique_subquery is an index lookup function that can replace subquery completely, higher efficiency.

8. index_subquery: The join type is similar to unique_subquery. The IN subquery can be replaced, but it is only applicable to non-unique indexes IN the following forms: value IN (SELECT key_column FROM single_table WHERE some_expr)

9. range: searches for rows within a specified range and uses an index to select rows. The key column shows the index used. Key_len contains the longest key element of the index used. In this type, the ref column is NULL. When using the =, <>,> =, <, <=, is null, <=>, BETWEEN, or IN operator, you can use the range

10. index: the join type is the same as that of ALL, except that the index tree is scanned. This is usually faster than ALL because index files are usually smaller than data files.

11. all: perform a full table scan for each row combination from the previous table. If the table is the first table without the const Mark, this is usually not good, and it is usually very bad. Generally, you can add more indexes instead of ALL, so that the rows can be retrieved Based on the constant values or column values in the preceding 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 keys in possible_keys cannot be used in the order of the generated table.

Key: displays the key indexes actually determined by MySQL ). If no index is selected, the key is NULL. To FORCE MySQL to USE or IGNORE the indexes in the possible_keys column, use force index, use index, or ignore index in the query.

The key_len: key_len column displays the key length determined by MySQL. If the key is NULL, the length is NULL. Note that using the key_len value, we can determine the parts of MySQL that will actually use multiple keywords.

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

Rows: The rows column displays the number of rows that MySQL considers to be checked when executing a query.

Extra: This column contains detailed information about MySQL queries.

1. Distinct: After MySQL finds 1st matching rows, it stops searching for more rows for the current row combination.

2. Not exists: MySQL can perform left join Optimization on queries. After one row matches the left join standard, check more rows in the table for the combination of the preceding rows.

3. range checked for each record (index map: #): MySQL does not find any indexes that can be used. However, if the column values from the preceding table are known, some indexes may be used. For each row combination in the preceding table, MySQL checks whether the range or index_merge access method can be used to obtain rows.

4. Using filesort: MySQL requires an additional pass to find out how to retrieve rows in order. You can browse all rows based on the join type and save the sorting keywords and row pointers for all rows matching the WHERE clause. Then the keywords are sorted and the rows are retrieved in the order of sorting.

5. Using index: use only the information in the index tree instead of further searching and reading the actual row to retrieve the column information in the table. This policy can be used when queries only use columns that are part of a single index.

6. Using temporary: To solve the query, MySQL needs to create a temporary table to accommodate the results. In typical cases, if a query contains the group by and order by clauses that can list columns according to different situations.

7. Using where: The WHERE clause is used to limit which row matches the next table or sends it to the customer. Unless you request or check ALL rows from a table, if the Extra value is not Using where and the table join type is ALL or index, the query may have some errors.

8. Using sort_union (...), Using union (...), Using intersect (...): These functions describe how to merge index scans for index_merge join types.

9. Using index for group-by: similar to the Using index method used to access the table, Using index for group-by indicates that MySQL has found an index, it can be used to query all columns queried by group by or DISTINCT, rather than searching the actual table accessed BY hard disk. In addition, indexes are used in the most effective way to read only a small number of index entries for each group.

By multiplying all the values in the rows column output by EXPLAIN, you can get a prompt about how a join works. This should roughly tell you how many lines MySQL must check to execute the query. When you use the max_join_size variable to limit queries, you can also use this product to determine which multi-Table SELECT statement to execute.

This section describes how to use the EXPLAIN statement in the MySQL database and its precautions. I hope this introduction will be helpful to you!