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

This article is to share with you about mysql query optimization explain command is how, the editor feels very practical, so share with you to learn, I hope you can learn something after reading this article, do not say much, follow the editor to have a look.

There are many methods of mysql query optimization, and explain is one of the most commonly used checking methods. Explain translation is interpretation, that is, look at the query interpretation plan of the mysql statement. From the interpretation plan, we can clearly see whether the explained statement is reasonably used in the index, how many rows are scanned, and whether it touches the reasons that affect the slow query, such as full table scanning, using temporary tables and so on.

It is easy to use, such as

Explain select * from user...

After implementation, there will be a table to explain the plan, the meaning can refer to the following parameters, for these interpretation plans, we can be used as the corresponding optimization.

The id mysql query identifier, or serial number.

Select_type query type

Simple: simple select query, excluding union and subquery

Primary: outermost select query

Union: indicates that this query is the second or subsequent query of union

The second or subsequent query statement in dependent union:union, depending on the external query

Results of union result:union

Subquery: the first select in the subquery

Dependent subquery: the first select in a subquery, depending on the outer query, that is, the subquery depends on the results of the outer query.

All tables queried by table.

Type connection type, an important item, from which we can see whether it is efficient or not.

The performance from good to bad is as follows:

System: there is only one piece of data in the table, which is a special const type

Const: an equivalent query scan for a primary key or unique index that returns at most one row of data. Const query is very fast because it can be read only once.

Eq_ref: this type usually appears in multi-table join queries, indicating that for each result of the previous table, only one row of the latter table can be matched, and the comparison operation of the query is usually =, so the query is more efficient.

Ref: this type usually appears in multi-table join queries for non-unique or non-primary key indexes, or for queries that use leftmost prefix rule indexes

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.

Ref_or_null: similar to the ref method, except that the comparison of null values is added. It is not used much in practice.

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

Index_subquery: secondary index or in constant list is used for in form subquery. The subquery may return duplicate values. You can use the index to deduplicate the subquery.

Index_merge: indicates that the query uses more than two indexes, and finally takes intersection or union. Common and,or conditions use different indexes, which is officially sorted after ref_or_null, but in fact, due to reading all indexes, the performance may not be as good as range most of the time.

Range: indicates that the index range query is used to obtain part of the data records in the table through the index field range. This type usually appears 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.