Shulou(Shulou.com)05/31 Report--

This article mainly introduces the example analysis of the MySQL implementation plan, which has a certain reference value, interested friends can refer to, I hope you can learn a lot after reading this article, let the editor take you to understand it.

What is the execution plan?

The execution plan, simply put, is the performance of SQL when it is executed in the database, and is usually used in SQL performance analysis, optimization and other scenarios.

one。 What can the execution plan tell us?

How SQL uses indexes

Execution order of join query

Query scanned data function

two。 Execute the contents of the plan

The output of the SQL execution plan may be multiple lines, each representing an operation on a database object

1. ID column

If the data in the ID column is a set of numbers, it indicates the order in which the SELECT statements are executed; if it is NULL, this row of data is the result set produced by the UNION operation performed by the other two SQL statements

When the ID value is the same, it means that the execution order of SQL is executed from top to bottom as shown.

When the ID value is different, the higher the ID value, the higher the priority, and the earlier it is executed.

Demo

You can see that the above execution plan returns three rows of results, and the value of the id column can be regarded as the sequence number of the SELECT operation in SQL.

Since there is only one SELECT in the above SQL, the id is all 1, so we have to read the execution plan from top to bottom

According to our SQL statement, we will think that the execution order is a _ c, but we can find that Mysql does not perform table association operations in the order written in SQL.

The order in which the table is executed is a ~ c ~ ~ b, because the MySQL optimizer adjusts the actual order of the table associations based on the statistics of the indexes in the table.

2. SELECT_ type column

If the query PRIMARY query with value meaning SIMPLE does not include subquery or UNION operation contains any subquery, then the outermost query is marked as the subquery in the PRIMARYSUBQUERYSELECT list DEPENDENT SUBQUERY the second or subsequent query whose UNIONUnion operation depends on external results has the value unionDEPENDENT UNION when UNION is used as the subquery, the result set DERIVED produced by the select_type value UNION RESULTUNION of the second or second query appears in the FROM clause

3. TABLE column

The following results are included:

The name of the table in which the data row is output. If the table has an alias, the alias is displayed.

The result set generated by ID querying union for MMagin N

/: the result produced by a query with ID N

4. PARTITIONS column:

Query which partition the matching records come from

For partition tables, display the partition ID of the query

For non-partitioned tables, displayed as NULL

5. TYPE column

The order of performance from highest to lowest is as follows:

Value meaning system this is a special case of the const join type. It is used when the query table has only one row and only one matching row in the const table, such as a query for a primary key or a unique index. This is the most efficient join method: eq_ref unique index or primary key index query. For each index key, only one record in the table matches the ref non-unique index lookup. Returning all rows matching a single value ref_or_null is similar to a query of type ref, but a query index_merge for a column of NULL values is appended. This join type indicates that index merging optimization method range index range scanning is used, which is common in between, >,

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.