Database ~ Description of Explain in Mysql, mysqlexplain

Database ~ Description of Explain in Mysql, mysqlexplain

You can add an Explain statement before the select statement to implement the execution plan of mysql. It tells us about the statement performance.

The following is a specific description of the explain, which is also official and will be used for reference in the future.

Id	SELECT identifier. This is the serial number of the SELECT query.
Select_type	SELECT type, which can be any of the following: SIMPLE: Simple SELECT (do not use UNION or subquery) PRIMARY: Exclusive SELECT UNION: The second or subsequent SELECT statement in UNION DEPENDENT UNION: The second or subsequent SELECT statement in UNION depends on the external query. UNION RESULT: UNION result SUBQUERY: The first select in the subquery DEPENDENT SUBQUERY: The first select in the subquery depends on the external query. DERIVED: SELECT (subquery of the from clause) of the export table)
Table	Table referenced by the output row
Type	Join type. The following describes various join types, sorted by the best type to the worst type: System: The table has only one row (= system table ). This is a special case of the const join type. 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 tables are fast because they are read only once! 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. Ref: For each row combination from the preceding table, all rows with matching index values will be read from this table. Ref_or_null: The join type is like ref, but MySQL can search for rows containing NULL values. Index_merge: This join type indicates that the index merge optimization method is used. Unique_subquery: This type replaces the ref: value IN (SELECT primary_key FROM single_table WHERE some_expr) of the in subquery IN the following form. unique_subquery is an index lookup function that can completely replace subqueries, which is more efficient. 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) Range: Only retrieve rows in a given range and use an index to select rows. 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. ALL: Perform a full table scan for each row combination from the previous table.
Possible_keys	Specifies the index that MySQL can use to find rows in the table.
Key	Displays the keys (indexes) actually used by MySQL ). If no index is selected, the key is NULL.
Key_len	Displays the key length determined by MySQL. If the key is NULL, the length is NULL.
Ref	Displays the column or constant used to select rows from the table with the key.
Rows	Displays the number of rows that MySQL considers to be required for query execution. You can estimate the number of rows to be processed by multiplying the data between multiple rows.
Filtered	Shows the percentage of rows filtered out by the condition.
Extra	This column contains detailed information about MySQL queries. Distinct: After MySQL finds 1st matched rows, it stops searching for more rows for the current row combination. Not exists: MySQL can perform left join Optimization on queries. After finding a row that matches the left join standard, it no longer checks more rows for the preceding row combination in the table. 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. Using filesort: MySQL requires an additional pass to find out how to retrieve rows in order. 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. Using temporary: To solve the query, MySQL needs to create a temporary table to accommodate the results. Using where: The WHERE clause is used to limit which row matches the next table or sends it to the customer. Using sort_union (...), Using union (...), Using intersect (...): These functions describe how to merge index scans for the index_merge join type. Using index for group-: Similar to the Using index method used to access a table, Using index for group-by indicates that MySQL has found an index and can be used to query all columns queried by group by or DISTINCT, instead of searching for the actual table by hard disk.

Type: this is an important column that shows the type used by the connection. The connection types from the best to the worst are const, eq_reg, ref, range, index, and ALL.

Type Displays the access type and is an important indicator. The result values are as follows: system> const> eq_ref> ref> fulltext> ref_or_null> index_merge> unique_subquery> index_subquery> range> index> ALL
In general, make sure that the query reaches the range level at least, and it is best to reach the ref level.

We recommend that you use indexes when performing conditional queries. Otherwise, full table scanning will occur. I/O overhead and program performance cannot be guaranteed!

Thank you for reading!