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

This article mainly introduces you to understand the results of the MySQL implementation plan, what is involved, from the theoretical knowledge, there are many books and documents for your reference, from the perspective of practical significance, accumulated years of practical experience can be shared with you.

The explain results show that

Select_type type description SIMPLE simple SELECT (no UNION or subqueries, etc.) PRIMARY main query, that is, the second or subsequent SELECT statement in the outermost SELECTUNIONUNION, the second or subsequent SELECT statement in DEPENDENT UNIONNION, depends on the result of the external query UNION RESULTUNION, the first SELECT in the first SELECTDEPENDENT subquery subquery, and on the SELECT of the external query SUBQUERY export table (a subquery of the FROM clause)

Table

Output the table name of the result set

Partitions

Which partitions are used when partitions exist

Type

Which category is used by the connection and whether the index is used or not is one of the key items in using the Explain command to analyze performance bottlenecks. The common types of access are as follows, sorted by performance, from top to bottom, from bad to good. In general, make sure that the query reaches at least range level, preferably ref, otherwise performance problems may occur. The type value example shows that ALL full table scan generally does not have where condition or where condition does not use index query statement index index full scan generally query statement range index range scan is commonly used for =, between and other operations ref non-unique index scan uses non-unique index or unique index prefix scan, returns rows of records matching a single value eq_ref unique index scan is similar to ref The difference is that the index used is a unique index. For each index key value, only one record in the table matches const,system. At most one matching row onst/system appears in a query based on the primary key primary key or unique index unique index. NULL does not scan the table or index select 1 from dual.

Possible_keys

Column indicates which index MySQL can use to find rows in the table

Key

Displays the key (index) that MySQL actually decides to use. If no index is selected, the key is NULL

Key_len

Displays the key length that MySQL decides to use. If the key is NULL, the length is NULL. The length of the index used. The shorter the length, the better without losing accuracy.

Ref

Displays which column or constant is used to select rows from the table with key.

Rows

Displays the number of rows that MySQL thinks it must check when executing the query.

Filtered

The percentage (percentage) of the number of records left to satisfy the query after the data returned by the storage engine is filtered in the server layer

Extra

Contains details of the query resolved by MySQL and is one of the key reference items. The extra element states that once MYSQL finds a row that matches the row union, Distinct no longer searches Not existsMYSQL optimized LEFT JOIN, and once it finds a row that matches the LEFT JOIN standard, it no longer searches Rangechecked for eachRecord and does not find an ideal index, so for each combination of rows from the previous table, MYSQL checks which index is used and uses it to return rows from the table. This is one of the slowest joins that use an index. Using filesort indicates that MySQL requires extra sort operations. It cannot be sorted through the index order. Using index represents index coverage, and does not return to the table. The query Using temporary usually occurs on ORDER BY of different column sets, rather than on GROUP BY. Using where indicates that a return table query is performed to query what is a return table.

To put it simply, after the database finds the row of the specified record according to the index, it also needs to fetch data from the data block again according to rowid. For example, in such an execution plan, the index is scanned first, and then rowid is used to retrieve the data that cannot be provided in the index, that is, the table is returned. "return to the table" generally refers to the "TABLE ACCESS BY INDEX ROWID" shown in the execution plan. For example, although only the columns in the index are queried, you need to go back to the table to filter out other rows.

How to avoid returning the meter?

Put the required fields in the index. When querying, you can avoid returning the table. But don't try to avoid returning the watch, it costs too much. Not all fields are put in all.

Back to the table and MySQL index implementation is related, interested friends can learn more about the principle of B + tree, refer to the following article "from B tree, B + tree, B * tree to R tree".

How to get rownum by MySQL

Rownum,mysql, which is different from oracle, does not provide such fields directly, but you can add pseudo-self-increasing sequences as variables. The syntax format is

SELECT @ rownum:=@rownum+1 AS rownum, table_name.* FROM (SELECT @ rownum:=0) r, table_name

Read the above to understand the results of the MySQL implementation plan introduction, hoping to bring some help to everyone in the practical application. Due to the limited space in this article, there will inevitably be deficiencies and areas that need to be supplemented. You can continue to pay attention to the industry information section and will update your industry news and knowledge regularly. If you need more professional answers, you can contact us on the official website for 24-hour pre-sales and after-sales to help you answer questions at any time.

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.