Shulou(Shulou.com)06/01 Report--

Explain can analyze how many records a select statement will query, how it will be queried, and the execution order of a complex select, so that you can understand the performance of select statements and how the query is executed.

Such as: select clause and from clause, execute the from clause first

Ps: the version of mysql on our server is 5.1.73. MySQL 5.6 explain can explain update, insert, etc.

Step 1: insert a large amount of data first, because the result of explain is related to the actual data in the database.

Delimiter $$drop procedure if exists addoplist;create procedure addoplist (in mpoint int, in mproductid int, in mnum int) begindeclare id int;declare maid int;declare msid int;declare mpid int;set id=0;while id= ((select max (aid) from tbl_roles)-(select min (aid) from tbl_roles) * rand () + (select min (aid) from tbl_roles) limit 1 Insert table_oplist (optype, aid, sid, pid, optime, rid, point, freeze, productid, device) values (1, maid, msid, mpid, UNIX_TIMESTAMP (), 0, mpoint, 0, mproductid, concat (qwerwqrqwrwdxcvzxvdfge, round (rand () * 1000)); set id=id+1;end while;end$$delimiter; call addoplist (30, 1010, 300000)

Step 2: what useful information does expalin explain? example: explain select count (*) from table_oplist where device= "qwerwqrqwrwdxcvzxvdfge52"

+-+-

| | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+-+-

| | 1 | SIMPLE | tbl_oplist | ref | oplist_device | oplist_device | 131 | const | 307 | Using where; Using index |

+-+-

1 > the rows column is the mysql estimate of the number of rows to be read in order to find the required rows; this estimate may not be accurate and does not reflect the role of limit; when the result has multiple rows, the values of all rows columns are multiplied to roughly estimate the number of rows to be read in the entire query

Rows is related to the actual total number of rows in the database

2 > type column is the most important column in my opinion.

ALL: scan the whole table by row; when limit is used in the query, it is not the whole table, but a few limit are found and no longer scanned; or "Using distinct/not exists" is displayed in the Extra column (not seen yet)

Index: scan the whole table in indexed order instead of by row; when the Extra column shows "Using index", it means that the index is overwritten and only the data of the index is scanned instead of scanning the whole table by index

Range: compared to index, it is a range index scan, not necessarily but usually occurs in select statements with comparative characters such as between or where clause with >; in the case of in () clause or or list (not clear at present)

Ref: index access, where the index compares to the reference value and returns all matching rows; it may find multiple rows that match the criteria; lookup + scan; occurs only when a non-unique index or a non-unique index prefix is used; ref_or_null also falls into this category (do a second lookup in the result of the initial search to find the NULL entry)

Eq_ref: returns at most one record that meets the criteria, which can be seen when looking up using a primary key or a unique index

Const, system: mysql uses this type when it can optimize a part of a query and convert it to a constant; for example, when the primary key is placed in the where clause to select the primary key of the row, it will be converted to a constant

NULL: mysql decomposes the query statement during the optimization phase, without even having to access the table or index during the execution phase

Ref diagram

Mysql > create index oplist_device on table_oplist (device)

Mysql > explain select * from table_oplist where device= ""

+-- +

| | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+-- +

| | 1 | SIMPLE | tbl_oplist | ref | oplist_device | oplist_device | 131 | const | 4 | Using where |

+-- +

When there is no index:

Mysql > explain select distinct aid from table_oplist where device= ""

+-+-

| | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+-+-

| | 1 | SIMPLE | tbl_oplist | ALL | NULL | NULL | NULL | NULL | 5180 | Using where; Using temporary |

+-+-

Const diagram

Mysql > explain select * from table_account where account= "test02222"

+-- +

| | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+-- +

| | 1 | SIMPLE | tbl_account | const | PRIMARY | PRIMARY | 66 | const | 1 |

+-- +

3 > select_type

Simple: simple select, excluding subqueries and union

Primary: if the query contains any complex subsections, the outermost select is marked primary

Derived: the corresponding table column is

When the explain output select_type is derived, it indicates the beginning of a nesting range, and if the following id is small, the nesting is over.

Subquery: select clause

Union: the second or subsequent select statement in union

Union result: results of union

4 > table

Represents the table that the corresponding row is accessing; when there is a subquery or union in the from clause, the table column becomes complex

When there is a subquery in the from clause, the table column is, and N is the id of the next row in the explain output

5 > key

This column shows which index is optimized to minimize query cost; it does not necessarily appear in possible_keys

6 > ref

This column shows the columns or constants used to find values in the index of the key column record. A value of null only indicates that nothing is used.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.