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

This article mainly explains how to use the MYSQL performance analyzer EXPLAIN, the content is clear, interested friends can learn, I believe it will be helpful after reading.

How to use it:

EXPLAIN SELECT * FROM user

Environment and data preparation

-- View MySQL version SELECT VERSION ();-- what storage engine SHOW ENGINES; MySQL provides-- View default storage engine SHOW VARIABLES LIKE'% storage_engine%'

Output result:

Id: the output is an integer that identifies the order in which the entire SQL is executed. If the id is the same, the execution id from top to bottom is different; the higher the id value, the higher the execution priority and the first execution; if the row refers to the union result of other rows, the value can be NULL

Select_type: [query type]

SIMPLE: a simple SELECT query with no UNION or subqueries, including single-table or multi-table JOIN queries

PRIMARY: outermost select query, common in subqueries or UNION queries, the outermost query is identified as PRIMARY

The second or subsequent SELECT of the UNION:UNION operation does not depend on the result set of the external query (the external query refers to the SELECT corresponding to PRIMARY)

The second or subsequent SELECT of the DEPENDENT UNION:UNION operation, depending on the result set of the external query

The result of UNION RESULT:UNION (no such result if it is UNION ALL)

SUBQUERY: the first SELECT query in the subquery that does not depend on the result set of the external query

DEPENDENT SUBQUERY: the first select query in the subquery, depending on the knot of the external query

DERIVED: derived tables (temporary tables), which are common in cases where there are subqueries in the FROM clause

Note: a new feature for Derived table has been made in MySQL5.7, which allows you to merge the child tables in the eligible Derived table with the tables of the parent query for direct JOIN, thus simplifying the execution plan and improving the execution efficiency; by default, this feature in MySQL5.7 is on, so by default, the execution plan of the above SQL should look like this

MATERIALIZED: materialized subqueries, a new select_type introduced by MySQL5.6, mainly to optimize subqueries in FROM or IN clauses. For more details, please see: Optimizing Subqueries with Materialization

UNCACHEABLE SUBQUERY: for the outer main table, subqueries cannot be cached and need to be evaluated each time

UNCACHEABLE UNION: similar to UNCACHEABLE SUBQUERY, except in the UNION operation

SIMPLLE, PRIMARY, SUBQUERY and DERIVED will encounter more in the actual work. You can understand these four. As for the others, just check the information when you encounter them.

Table: shows which table the corresponding row is accessing (aliases will show aliases), and there will also be values like, (here 2Jing 3, 2, 2 refer to the values of the id column)

Partitions: query the matching partition. For non-partitioned tables, the value is NULL. Partitions are not used in most cases, so we don't need to pay attention to this column.

Type:

Association type or access type, which indicates how MySQL decides how to find qualified rows in the table, which is an important basis for us to judge whether the query is efficient or not. For a complete introduction, please see: explain-join-types

System: this table has only one row (= system table) and is a special case of type const

Const: when it is determined that only one row matches, the mysql optimizer reads it before the query and reads it only once, very fast. Used in situations where there is constant bright value comparison in primary key or unique indexes

Eq_ref: for each row from the previous table, at most one eligible record is returned from that table. Used when the index used by a join is a PRIMARY KEY or UNIQUE NOT NULL index, which is very efficient

Ref: index access, also known as index lookup, returns all rows that match a single value. This type usually occurs in multi-table JOIN queries for non-UNIQUE or non-PRIMARY KEY queries, or for queries that use the leftmost prefix rule index, in other words, if JOIN cannot select a single row based on keywords, use ref

Fulltext: used when using full-text indexing, which is generally not needed and will be replaced by specialized search services (solr, elasticsearch, etc.)

Ref_or_null: similar to ref, but with lines that can specifically search for NULL

This is a prerequisite if the weapon column has an index and the weapon column has NULL

Index_merge: this access type uses the index merge optimization method

This is also conditional. Both id and weapon columns have single-column indexes. If index_merge appears, and this type of SQL is used more frequently later, you can consider replacing single-column indexes with combined indexes, which is more efficient.

Unique_subquery: similar to the eq_ref access method for driven tables in two table joins, unique_subquery is used if the query optimizer decides to transform the IN subquery into an EXISTS subquery in some query statements that contain an IN subquery, and the subquery can use a primary key or unique index for equivalent matching

Index_subquery:index_subquery is similar to unique_subquery, except that a table in a subquery is accessed using a normal index

Range: use the index to retrieve a given range of rows, when using =, >, > =,

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.