MySQL view SQL statement execution efficiency

The explain command is the first recommended command to address database performance, and most performance issues can be easily resolved with this command, explain can be used to see how SQL statements are executed, to help select better indexes and refine query statements, and to write better optimization statements. Explain syntax: Explain select ... [Where ...] For example: Explain select * from news; Output: +----+-------------+-------+-------+-------------------+---------+---------+-------+------+-------+| ID | Select_type | Table | Type | Possible_keys | Key | Key_len | Ref | Rows | Extra |+----+-------------+-------+-------+-------------------+---------+---------+-------+------+-------+ The following are the various properties: 1, ID: This is the query for select sequence Number 2, Select_type:select_type is the type of select, you can have the following: simple: Easy Select (Do not use union or subquery, etc.) PRIMARY: The second or subsequent SELECT statement in the outermost select union:union the second or subsequent SELECT statement in DEPENDENT union:union, depending on the outer query UNION The result of result:union. Subquery: The first select in a subquery DEPENDENT subquery: The first select in the subquery, depending on the outer query DERIVED: Export table's SELECT (subquery FROM clause) 3, Table: Displays the data for this row about which table is 4, type: This column is the most important, showing which category the connection is using, whether or not the index is used, and is one of the keys to analyzing the performance bottleneck using the explain command. Result values from good to Bad are: System > Const > EQ_REF > Ref > Fulltext > Ref_or_null > Index_merge > Unique_subQuery > Index_subquery > Range > Index > All in general, it is best to ensure that the query reaches at least the range level, preferably ref, or performance issues may occur. 5. Possible_keys: column indicates which index MySQL can use to find row 6, key: Displays the keys (indexes) that MySQL actually decides to use. If no index is selected, the key is null 7, Key_len: Displays the key length that MySQL decides to use. If the key is null, the length is null. The length of the index to use. With no loss of accuracy, the shorter the length, the better 8, ref: shows which column or constant is used together with key to select rows from the table. 9. Rows: Shows the number of rows that MySQL must check when it executes a query. 10. Extra: Contains the details of MySQL solution query and is also one of the key reference items.

　　

MySQL view SQL statement execution efficiency