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

The following mainly brings you the explain execution plan optimization sql statement in MySQL. I hope these contents can bring you practical use, which is also the main purpose of this article that I edit the explain execution plan optimization sql statement in MySQL. All right, don't talk too much nonsense, let's just read the following.

Detailed explanation of mysql explain implementation plan

1), the larger the number of the id column, the first to execute. If the number is the same, then it will be executed from top to bottom. The table in the id column null is that this is a result set, so you do not need to use it for query.

2), the common select_ type lists are:

A:simple: represents a simple select query that does not require union operations or does not contain subqueries. When there is a join query, the outer query is simple, and there is only one

B:primary: a select that requires a union operation or contains a subquery. The select_type of the outermost unit query is primary. And there's only one.

There are two select queries connected by C:union:union. The first query is a dervied derived table. Except for the first table, the second subsequent table select _ type is union.

D:dependent union: like union, it appears in union or union all statements, but this query is affected by external queries

E:union result: contains the result set of union. In union and union all statements, because it does not need to participate in the query, the id field is null

F:subquery: except for the subquery contained in the from sentence, the subquery that appears elsewhere may be subquery

G:dependent subquery: similar to dependent union, it means that the query of this subquery will be affected by external table query

Subqueries that appear in H:derived:from sentences, also known as derived tables, may be called inline views or nested select in other databases

3), table

If the query table name is displayed, if the query uses an alias, then the alias is shown here. If the operation on the data table is not involved, it is shown as null. If it is shown in angle brackets, it means that this is a temporary table, and the N after it is the id in the execution plan, indicating that the result is generated by this query. If it is enclosed in angle brackets, it is also a temporary table, indicating that the result comes from the result set of the union query whose id is MJN.

4), type

From good to bad: system,const,eq_ref,ref,fulltext,ref_or_null,unique_subquery,index_subquery,range,index_merge,index,ALL, except all, all other type can use index, except index_merge, other type can only use one index.

A:system: there is only one row of data or an empty table in the table and can only be used with myisam and memory tables. If it is an Innodb engine table, the type column is usually all or index in this case

B:const: when using a unique index or primary key, the return record must be the equivalent where condition of a row of records, the type is usually const. Other databases are also called unique index scans

C:eq_ref: when it appears in a query plan to join a table, the driven table returns only one row of data, which is the primary key or unique index of the second table, and must be not null. When the unique index and primary key are multiple columns, eq_ref will appear only if all columns are used for comparison.

D:ref: unlike eq_ref, which requires join order, and no primary key and unique index, it may occur as long as it is retrieved using equality conditions, and it is common for equivalent lookups to be used with secondary indexes. Or in a multi-column primary key or unique index, using a column other than the first column as an equivalent lookup may also appear. In short, an equivalent lookup that returns data that is not unique may occur.

E:fulltext: full-text index retrieval, it should be noted that the priority of full-text index is very high. If full-text index and general index exist at the same time, mysql will choose to use full-text index first regardless of cost.

F:ref_or_null: similar to the ref method, except that the comparison of null values is added. It doesn't use much in practice.

G:unique_subquery: used for in formal subqueries in where. The subqueries return unique values that are not duplicated

H:index_subquery: secondary indexes or lists of in constants are used for in form subqueries. Subqueries may return duplicate values, and you can use indexes to de-duplicate subqueries.

I:range: index range scanning, commonly used in >

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.