Use the EXPLAIN keyword to check SQL statement efficiency _ MySQL

Use the EXPLAIN keyword to check SQL statement efficiency Description

You can use the explain command to understand how mysql processes statements and analyze the performance bottleneck of queries or table structures. Through expalin, we can get:

1. reading sequence of the table
2. read operation type of the table
3. which indexes can be used?
4. which indexes are actually used?
5. Reference between tables
6. The Optimizer queries the number of rows in each table.

Explain field

1. id: Statement execution sequence id

2. select_type: the query type used. the query types include:

1). simple type

No subquery or union exists in the statement.

2). select of the outermost layer of primary, not the primary key

3). union

In a select statement, union is all the select statements following the second select statement. The first select is primary.

4). dependent subquery

The first select statement in the inner layer of the subquery

5). dependent union

All the select statements that start with the second select statement in the union clause depend on the external result set.

6). SUBQUERY

7). devived

Query statement of the derived table

8). uncacheable subquery

Subqueries that cannot be cached in the result set

9). union result

Union result

3. table

Display the name of the table in the database accessed in this step

4. type

This column is very important and shows the category used for the connection and whether or not the index is used. Type indicates the connection mode used by the table specified in the query execution plan (QEP. The connection types from the best to the worst are 1. system, 2. const, 3. eq_reg, 4. ref, 5. range, 6. index, and 7. all.

1). system
System is a special case of const, that is, the table has only one record.
2). const
The where condition uses a constant as the query condition, and a maximum of one record matches in the table. Because it is a constant, you only need to read it once.

3). eq_reg
There can be at most one matching result, which is generally accessed through a primary key or a unique index. It usually appears in the connection query statement.

4). ref
The index reference query of the driven table in the join statement. This value indicates that all rows with matched index values are used.

6). index

Full Index tree scanned

7). all
Full table scan results are the most unsatisfactory.

5. possible_keys
If no index is available for queries, it is displayed as null. The index adjustment is very important for content optimization.

6. key

Index selected from possible_keys

7. key_len

The key_len column displays the key length determined by mysql. if the key is null, the length is null. The length of the index used. the shorter the index, the better.

8. ref
The list is filtered by a constant const or a field in a table.

9. rows
The number of records in the result set is estimated based on the statistical information collected by the system.

10. extra

Extra: query the Extra details of each step, which may be the following:

1). Distinct: Search for the distinct value. Therefore, when mysql finds the first matching result, it will stop querying the value and convert it to the query of other values. FullscanonNULLkey: subquery

An optimization method in the query is mainly used when null values cannot be accessed through the index;

2). ImpossibleWHEREnoticedafterreadingconsttables: MySQLQueryOptimizer identifies impossible results by collecting statistics;

3). Notables: the Query statement uses FROMDUAL or does not contain any FROM Clause;

4). Notexists: in some left connections, the optimization method used by MySQLQueryOptimizer to change the composition of the original Query can partially reduce the number of data accesses;

5 ). rangecheckedforeachrecord (indexmap: N): According to the description in the MySQL official manual, if MySQLQueryOptimizer does not find any available indexes, 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.

6). Selecttablesoptimized away: when we use some aggregate functions to access a field with an index, MySQLQueryOptimizer will directly locate the required data row to complete the entire query through the index. Of course, the premise is that there is no GROUPBY operation in the Query. For example, when MIN () or MAX () is used;

7). Usingfilesort: when our Query contains the ORDERBY operation and the index cannot be used to complete the sorting operation, MySQLQueryOptimizer has to select the corresponding sorting algorithm.

8). Usingindex: you only need to obtain all the required data from the Index instead of from the table;

9 ). usingindexforgroup-by: data access is the same as Usingindex. you only need to read the required data. when the GROUPBY or DISTINCT clause is used in the Query, if the grouping field is also in the index, the information in Extra will be Usingindexforgroup-;

10). Usingtemporary: when MySQL must use a temporary table in some operations, Usingtemporary will appear in the Extra information. It is common in operations such as GROUPBY and ORDERBY.

11). Usingwhere: if we do not read all the data in the table, or we can retrieve all the required data through indexes, the Usingwhere information will appear;

12). Usingwherewithpushedcondition: this is a message that only appears in the NDBCluster storage engine. you also need to enable the ConditionPushdown optimization function to be used. The control parameter is engine_condition_pushdown.

Appendix:

CREATE TABLE `item` (  `i_id` int(11) NOT NULL,  `i_im_id` int(11) DEFAULT NULL,  `i_name` varchar(24) DEFAULT NULL,  `i_price` decimal(5,2) DEFAULT NULL,  `i_data` varchar(50) DEFAULT NULL,  PRIMARY KEY (`i_id`)) ENGINE=InnoDB DEFAULT CHARSET=latin1 ;CREATE TABLE `orders` (  `o_id` int(11) NOT NULL,  `o_d_id` tinyint(4) NOT NULL,  `o_w_id` smallint(6) NOT NULL,  `o_c_id` int(11) DEFAULT NULL,  `o_entry_d` datetime DEFAULT NULL,  `o_carrier_id` tinyint(4) DEFAULT NULL,  `o_ol_cnt` tinyint(4) DEFAULT NULL,  `o_all_local` tinyint(4) DEFAULT NULL,  PRIMARY KEY (`o_w_id`,`o_d_id`,`o_id`),  KEY `idx_orders` (`o_w_id`,`o_d_id`,`o_c_id`,`o_id`),  CONSTRAINT `fkey_orders_1` FOREIGN KEY (`o_w_id`, `o_d_id`, `o_c_id`) REFERENCES `customer` (`c_w_id`, `c_d_id`, `c_id`)) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATETABLE 'item '( 'I _ id' int (11) NOTNULL, 'I _ im_id' int (11) DEFAULTNULL, 'I _ name' varchar (24) DEFAULTNULL, 'I _ price' decimal (5, 2) DEFAULTNULL, 'I _ data' varchar (50) DEFAULTNULL, PRIMARYKEY ('I _ id ') ) ENGINE = innodbdefacharcharset = latin1; CREATETABLE 'orders '( 'O _ id' int (11) NOTNULL, 'O _ d_id' tinyint (4) NOTNULL, 'O _ w_id 'smallint (6) NOTNULL, 'O _ c_id' int (11) DEFAULTNULL, 'O _ entry_d 'datetimeDEFAULTNULL, 'O _ carrier_id 'tinyint (4) DEFAULTNULL, 'O _ ol_cnt 'tinyint (4) DEFAULTNULL, 'O _ all_local 'tinyint (4) DEFAULTNULL, PRIMARYKEY ('o _ w_id ', 'O _ d_id', 'O _ id '), KEY 'idx _ orders '('o _ w_id', 'O _ d_id ', 'O _ c_id', 'O _ id '), CONSTRAINT 'fkey _ orders_1 'FOREIGNKEY ('o _ w_id', 'O _ d_id ', 'O _ c_id') REFERENCES 'customer' ('c _ w_id ', 'c _ d_id', 'c _ id ') ) ENGINE = innodbdefacharcharset = latin1;

Article from: http://www.bitsCN.com/database/201307/230048.html