Using explain to parse SQL statements to implement optimized SQL statements

Usage: Explain SQL

Role: For parsing SQL statements

Mysql> Explain select * from quser_1 where loginemail = "[email protected]", +----+-------------+---------+------+---- -------------+-----------------+---------+-------+------+-----------------------+| ID | Select_type | Table   | type | Possible_keys   | key             | key_len | ref   | rows | Extra                 |+----+-------------+---------+------+-----------------+-----------------+---------+-------+------+-- ---------------------+|  1 | Simple      | quser_1 | ref  | loginemailindex | loginemailindex | 302     | const |    

　　

0:id represents a number of explain (no practical significance)

1:table the table name of the query

2:select_type the query type, which is a single-table query, a union query, or a subquery, the following values may appear:

Query type	Description
Simple	Simple select query with no union and subquery
PRIMARY	Outermost select query (using the primary key as the query condition)
Union Union	The second or subsequent select query in the UNION, independent of the result set of the external query
DEPENDENT UNION	The second or subsequent select query in the UNION, dependent on the result set of the external query
Subquery	The first select query in a subquery that does not depend on the result set of an external query
DEPENDENT subquery	The first select query in a subquery, dependent on the result set of the external query
DERIVED	Used in cases where there is a subquery in the FROM clause. MySQL executes these subqueries recursively, putting the results in a temporary table
Uncacheable subquery	The result set cannot be cached by a subquery and must be evaluated again for each row of the outer query
Uncacheable Union Union	Second or subsequent select query in union, which belongs to a non-cacheable subquery

For example, use the following table structure:

CREATE TABLE ' quser_1 ' (' Quserid ' int () unsigned NOT null default ' 0 ', ' username ' varchar (+) NOT null default ' ', ' Id ' int ' unsigned NOT null default ' 0 ', ' ver ' int (ten) unsigned NOT null default ' 0 ', ' password ' varchar (+) NOT NULL Default ', ' randomkey ' varchar (1) NOT null default ' ', ' sealtime ' varchar (in) NOT null default ' ', ' status ' tinyint Not null default ' 0 ', ' src ' varchar (+) NOT null default ' ', ' IP ' varchar (one-by-one) NOT null default ' 0 ', ' regtime ' Datetim E NOT null default ' 0000-00-00 00:00:00 ', ' tastetime ' datetime NOT NULL default ' 0000-00-00 00:00:00 ', ' lastmodifytime '  Timestamp NOT NULL default Current_timestamp on UPDATE current_timestamp, ' loginemail ' varchar (+) NOT null default ' ', ' Loginmethod ' tinyint (3) unsigned NOT NULL default ' 0 ', ' va ' varchar (+) NOT null default ' COMMENT ' virtual account Info ', PRIMARY key (' Quserid ') key_block_size=1024, UNIQUE key ' username ' (' username ') key_block_size=1024, key ' id ' (' Id ') key_block_size=1024, KEY ' Loginemailindex ' (' Loginemail ') key_block_size=2048) Engine=myisam DEFAULT Charset=utf8 row_format=compressed Key_block_size=8

　　

Example 1: Using a simple query

Mysql> Explain select * from quser_1 where loginemail = "[email protected]", +----+-------------+---------+------+---- -------------+-----------------+---------+-------+------+-----------------------+| ID | Select_type | Table   | type | Possible_keys   | key             | key_len | ref   | rows | Extra                 |+----+-------------+---------+------+-----------------+-----------------+---------+-------+------+-- ---------------------+|  1 | Simple      | quser_1 | ref  | loginemailindex | loginemailindex | 302     | const |    

　　

Type description:

Type	Description
System	Table has only one row (= system table). This is a special case of the const connection type
Const	Const is used to compare PRIMARY KEY with a constant value. When you query a table that has only one row, use the System
Eq_ref	Const is used to compare PRIMARY KEY with a constant value. When you query a table that has only one row, use the System
Ref	A connection cannot select a single row based on a keyword, and may find multiple rows that match the criteria. This is called ref because the index is compared to a reference value. This reference is either a constant or a result of a multi-table query from a list of values
Ref_or_null	Like ref, but MySQL must find the null entry in the results of the initial lookup, and then perform two lookups
Index_merge	Description Index merge optimization is used
Unique_subquery	This type is used in some in queries instead of the regular ref value in : (the SELECT primary_key from single_table WHERE some_expr)
Index_subquery	This type is used in some in queries, similar to Unique_subquery, but is a non-unique index of the query: value in (The SELECT key_column from single_table WHERE some_ Expr
Range	Retrieves only the rows for a given range, using an index to select rows. The key column shows which index is used. When you use the =, <>, >, >=, <, <=, is NULL, <=>, between, or in operators to compare key columns with a constant, you can use the range
Index	Full table scan, just scan the table by the index order instead of the line. The main advantage is to avoid sorting, but the cost is still very large
All	Worst case scenario, full table scan from end to end

Example 2:type to const

Mysql> Explain select * from quser_1 where Quserid = "3000096101", +----+-------------+---------+-------+------------ ---+---------+---------+-------+------+-------+| ID | Select_type | Table   | type  | possible_keys | key     | key_len | ref   | rows | Extra |+----+-------------+---------+-------+---------------+---------+---------+-------+------+-------+|  1 | Simple      | quser_1 | const | PRIMARY       | PRIMARY | 4       | const |    1 | NULL  |+----+-------------+---------+-------+---------------+---------+---------+-------+------+-------+1 Row in Set

　　

Example 3:type for all (this is to be optimized and avoided)

Mysql> Explain select * from quser_1 where src = "PCW", +----+-------------+---------+------+---------------+------+- --------+------+------+-------------+| ID | Select_type | Table   | type | possible_keys | key  | key_len | ref  | rows | Extra       |+----+-------------+---------+------+---------------+------+---------+------+------+-------------+|  1 | Simple      | quser_1 | All  | NULL          | NULL | NULL    | NULL | 1274 | Using where |+----+-------------+---------+------+---------------+------+---------+------+------+-------------+1 Row in Set

　　

Example 4:type is ref

Mysql> Explain select * from quser_1 where loginemail = ' [email protected] ', +----+-------------+---------+------+---- -------------+-----------------+---------+-------+------+-----------------------+| ID | Select_type | Table   | type | Possible_keys   | key             | key_len | ref   | rows | Extra                 |+----+-------------+---------+------+-----------------+-----------------+---------+-------+------+-- ---------------------+|  1 | Simple      | quser_1 | ref  | loginemailindex | loginemailindex | 302     | const |    1 | Using index Condition |+----+-------------+---------+------+-----------------+-----------------+---------+------- +------+-----------------------+1 row in Set

　　

Prossible_keys: Which indexes can be used in this table to help query

Key: The actual index used

Key_len: The length of the index, without loss of accuracy, the shorter the better

Ref: Which column of the index is used

Rows: The number of rows returned by the result

Extra: Other Notes

Using explain to parse SQL statements to implement optimized SQL statements