Mysql -- execution plan introduction _ MySQL

Mysql-execution plan introduction 1. Preface

As a programmer, it is inevitable to deal with databases. In general, we are not DBA, but we have to write a lot of SQL, so SQL efficiency has become a big problem. In addition to mastering certain optimization skills, we still need to have a lot of experience on SQL efficiency optimization. but here we can analyze the SQL through the execution plan to quickly find the optimization place, this is a very good way to introduce to you, most of me is translated, the original address: http://dev.mysql.com/doc/refman/5.6/en/explain-output.html

II. description of the output columns of the execution plan:

Column Meaning

id	TheSELECTIdentifier
select_type	TheSELECTType
table	The table for the output row
partitions	The matching partitions
type	The join type
possible_keys	The possible indexes to choose
key	The index actually chosen
key_len	The length of the chosen key
ref	The columns compared to the index
rows	Estimate of rows to be examined
filtered	Percentage of rows filtered by table condition
Extra	Additional information

●id:

The query identifier, indicating the order in which the select statement is executed (PS: The larger the number is, the execution is given priority ). If this row is the result of merging with other rows, this value can be null. For example, the UNION keyword is used to merge the results of multiple select statements.

●select_type: The type of each select statement.

select_type Meaning

SIMPLE	SimpleSELECT(Not usedUNIONOr subquery (PS: single table query ))
PRIMARY	Select at the outermost layer is used as the primary query. (PS: contains subqueries, but not complex)
UNION	Queries from the second or the select statement after the union statement.
DEPENDENT UNION	The select statement after the second or union statement is used as the union query and depends on the external query.
UNION RESULT	The result set comes from union,...
SUBQUERY	The first query is a subquery.
DEPENDENT SUBQUERY	The first query is a subquery dependent on external queries.
DERIVED	Subqueries in the from query statement (derived, nested). (PS: recursively operate these subqueries)
MATERIALIZED	(Atomization) subquery (PS: Is a subquery a view ?)
UNCACHEABLE SUBQUERY	The subquery results cannot be cached, and each row of the external query must be overwritten (analyzed ).
UNCACHEABLE UNION	Second or in UNIONSelect after query, which is a non-cacheable query

●Table: Output the table used (PS: contact by id)

●Type: Connection type, an important analysis method, which is sorted by the best to the worst:

System: The table only has one row (= system table), a special case of const

Const: The table query result can only be one row at most. because there is only one row, the optimization part of this query is usually a constant. For example, query by primary key id = 1.

For example:

SELECT * FROM tbl_name WHERE primary_key=1;

Eq_ref:The row read from the current table is combined with the rows in all the preceding tables. this is the best connection type except for const and system, it is used to connect all primary keys or non-null indexes with unique indexes. Common = operator comparison index

For example:

SELECT * FROM ref_table,other_table WHERE ref_table.key_column=other_table.column;

Ref:Used to connect a non-unique index scan. You can use the >=<> operator for indexed columns.

For example:

SELECT * FROM ref_table WHERE key_column=expr; SELECT * FROM ref_table,other_table WHERE ref_table.key_column=other_table.column;

SELECT * FROM ref_table,other_tableWHERE ref_table.key_column_part1=other_table.columnAND ref_table.key_column_part2=1;

FUlltext: Full-text search is used.

Ref or null: the connection method is like ref, but it contains null Values. this connection type is mainly used for subqueries.

For example:

SELECT * FROM ref_table WHERE key_column=expr OR key_column IS NULL;

Index_merge: Index merge optimization (PS: Condition of multiple index conditions for condition merge optimization)

My version is low and does not appear .., the http://dev.mysql.com/doc/refman/5.6/en/index-merge-optimization.htmlSELECT * FROM tbl_name WHERE key1 = 10 OR key2 = 20; SELECT * FROM tbl_nameWHERE (key1 = 10 OR key2 = 20) AND non_key = 30; SELECT * FROM t1, t2WHERE (t1.key1 IN (1, 2) OR t1.key2 LIKE 'value % ') AND t2.key1 = t1.some _ col; SELECT * FROM t1, t2WHERE t1.key1 = 1AND (t2.key1 = t1.some _ col OR t2.key2 = t1.some _ col2 );

Unique_subquery: Refer to ref to process subqueries.

For example:

value IN (SELECT primary_key FROM single_table WHERE some_expr)

Index_subquery: This is similar to unique_subquery. it replaces a subquery with a non-unique index.

For example:

value IN (SELECT key_column FROM single_table WHERE some_expr)

Range:Only the rows within the range are retrieved, and only the indexes are used to query the rows. The following Key indicates the index you used:

For example:

SELECT * FROM tbl_nameWHERE key_column = 10;SELECT * FROM tbl_nameWHERE key_column BETWEEN 10 and 20;SELECT * FROM tbl_nameWHERE key_column IN (10,20,30);SELECT * FROM tbl_nameWHERE key_part1 = 10 AND key_part2 IN (10,20,30);

Index: The index connection type is the same as that of ALL. apart from the index scanning of the tree, there are two types of indexes: 1. The index tree is convenient, 2. no index tree is available, that is, ALL is the same.

All:Full table scan is usually the worst query.

●Extra: Contains additional information about mysql resolution queries.

Distinct: mysql queries different rows. when it finds a match with the current row, it no longer searches.

FirstMatch (tbl_name): The semi-join FirstMatch join shortcutting strategy is usedtbl_name.

Full scan on NULL key: the query analyzer cannot use a failed policy of the current index.

Impossible HAVING: The where condition is always false. no rows can be filtered.

Impossible WHERE noticed after reading const tables: similar to the preceding

LooseScan: an index is used to scan a subquery table. you can select a single value from the value group of each subquery.

Not exists: mysql optimizes left join queries,

For example:

SELECT * FROM t1 left join t2 ON t1.id = t2.id WHERE t2.id is null; assume that t2.id IS defined as not null. in this case, Mysql scans t1 and uses t1.id to search for rows in t2, if Mysql finds a matched row in t2, it indicates that t2.id cannot be null, so it does not scan the remaining rows with the same id. In other words, each row in t1, mysql performs a query in T2.

Using filesort: sorting by indexes, such as file sorting, cannot be completed.

Using index: uses the index tree to scan the results. you do not need to scan all the results.

Using temporary: use a temporary table to store the result set.GROUP BYAndORDER BY.

Using where: use where to restrict the matching rows in the next table or return them to the client, unless you want to obtain all rows in the or checklist, if the extra value is not Using where and the connection type is not all or index, you may have some errors in your query.

Using join buffer:

Using MRR: complicated,

Reference: http://dev.mysql.com/doc/refman/5.6/en/mrr-optimization.html

And: http://blog.csdn.net/zbszhangbosen/article/details/7463394

●Key: The key list indicates the index you actually use. if not, it is null.

●Key len: This column is the length of the key used by Mysql. if not, it is null. the document prompts this value to determine which part of the multiple-part key is used.

●Rows: Indicates the number of rows scanned by Mysql statements.

●Possible_keys: Indicates the row data that mysql finds, which one of the indexes is in indexes. If an index exists in the fields involved in the query, the index is listed, but not necessarily used for query. If it is null, there is no relevant index. To improve performance, you can check the WHERE clause to see if some fields are referenced or if the fields are not suitable for indexing.

Summary:

1. the translation is too bad... so far, I have made my own understandings later. I suggest you read the original article, and there are many 5.6 + changes. I have never encountered any changes. sorry.

2. these simple descriptions only provide a way for you to analyze SQL statements, and remind you not to blindly judge the efficiency based on SQL statements. of course, if you have a wealth of experience, let alone ~. ~ Learn from new users.

3. if you need more details, you can use show profile to see more detailed information and higher accuracy. There are also some practical applications that have not been summarized, I will introduce it later.

4. sorry, please point out the incorrect dog blood. thank you.

Finally, let's share a good mysql address:

Http://www.mysqlab.net/

Http://www.mysqlpub.com/