MySQL Optimization-when you want to do something better, you must first use the tool to EXPLAIN

Recently, I have been getting familiar with MySQL and it is imminent to understand how to optimize it. To do this, you must first sharpen the tool. Recently, I plan to learn about several frequently used tools to optimize MySQL. Today, I will give a brief introduction to EXPLAIN.

Content navigation

• Id
• Select_type
• Table
• Type
• Possible_keys
• Key
• Key_len
• Ref
• Rows
• Extra

 

Prepare the MySQL version in the environment:

Create test table

  auto_increment  ()    ()    ()    ()    ()     ()    ()    

Insert Test Data

 ,,,,,,,,,,,, ,,,,,,,,,)

Create an index for testing

  people  (zipcode,firstname,lastname);

 

Description

Start with a simple query:

Query-1 zipcode,firstname,lastname  people;

The EXPLAIN output result contains the id, select_type, table, type, possible_keys, key, key_len, ref, rows, and Extra columns.

Id

 zipcode  (   people a) b;

IDs are used to sequentially identify the SELELCT statements in the entire query. The preceding simple nested query shows that the statement with a larger id is executed first. This value may be NULL. If this row is used to indicate the UNION results of other rows, for example, the UNION statement:

   people  zipcode       people  zipcode  ;

 

Select_type

The types of SELECT statements can be.

SIMPLE

The simplest SELECT query does not use UNION or subquery. See.

 

PRIMARY

The nested query is the SELECT statement at the outermost layer, and the SELECT statement at the beginning of the UNION query. See and.

UNION

The second and subsequent SELECT statements in UNION. See.

 

DERIVED

SELECT statement in the FROM clause in the SELECT statement of the derived table. See.

 

UNION RESULT

The result of a UNION query. See.

 

DEPENDENT UNION

As the name implies, the first statement must meet the UNION condition and the second and subsequent SELECT statements in the UNION statement, and the statement depends on external queries.

   people  id   ( id  people  zipcode     id  people  zipcode   );

In Query-4, the select_type of select id from people where zipcode = 200000 isDEPENDENT UNION. You may be surprised that this statement does not depend on external queries.

The MySQL optimizer optimizes the IN operator. The optimizer optimizes the uncorrelated subquery IN into a correlated subquery (for more information about correlated subquery, see here ).

 ...  t1  t1.a  ( b  t2);

A statement like this will be rewritten as follows:

 ...  t1   (   t2  t2.b  t1.a);

So it is actually rewritten as follows:

   people o    ( id  people  zipcode    id  o.id   id  people  zipcode     id  o.id);

Digress: Sometimes, if the MySQL optimizer is too "smart", the performance of subquery statements containing IN () IN the WHERE condition may be greatly reduced. SeeHigh-performance MySQL 36.5.1 associate subquerySection 1.

 

SUBQUERY

The first SELECT statement in the subquery.

   people   id   ( id  people  zipcode  );

 

DEPENDENT SUBQUERY

Similar to dependent union. See.

In addition to the common select_type mentioned above, we will not describe them here. Different MySQL versions are also different.

 

Table

The information displayed in this row is about which table. Sometimes it is not a real table name.

   (   (   people a) b ) c;

You can see the alias displayed if an alias is specified.

<DerivedN>N is the id value, which is the result of the operation corresponding to this id value.

And<UnionM, N>This type appears in the UNION statement, as shown in.

Note:: MySQL treats these tables like normal tables, but these "temporary tables" do not have any indexes.

 

Type

The type column is important to describe how tables are associated with each other and whether indexes are used. In MySQL, the word "join" is broader than that in the general sense. MySQL considers that any query is "join" at a time, not only two tables are required for a query, therefore, we can understand how MySQL accesses tables. There are mainly the following categories.

Const

When it is determined that there can be at most one row of matching, the MySQL optimizer will read it before the query and only read it once, so it is very fast. Const is only used to compare a constant with a primary key or a unique index and compare all index fields. The people table has a primary key index on the id and a secondary index on (zipcode, firstname, lastname. Therefore, the type is const rather:

   people  id;

   people  zipcode  ;

Note that the following Query-10 cannot use the const table. Although it is also a primary key, only one result is returned.

   people  id ;

System

This is a special case of the const connection type. Only one row of the table meets the conditions.

   (   people  id   )b;

<Derived2> it is already a const table with only one record.

 

Eq_ref

The eq_ref type is the best connection type except const. It is used to join all parts of an index and the index is UNIQUE or primary key.

Note that the InnoDB and MyISAM engines differ in this regard. When the InnoDB data volume is small, the type will be All. The people and people_car created above are InnoDB tables by default.

   people a,people_car b  a.id  b.people_id;

Let's create two MyISAM tables people2 and people_car2:

  auto_increment  ()    ()    ()    ()    ()     ()    ()     MyISAM;

   people2 a,people_car2 b  a.id  b.people_id;

I think this is a result of InnoDB's performance trade-off.

Eq_ref can be used to compare indexed columns with the = Operator. The comparison value can be a constant or an expression that uses the column of the table read before the table. If the index used for association is just a primary key, it will become a better const:

   people2 a,people_car2 b  a.id  b.people_id  b.people_id  ;

 

Ref

This type is different from eq_ref. It only uses the leftmost prefix of the index for association operations, or the index is not UNIQUE or primary key. Ref can be used for indexed columns using the = or <=> operator.

To demonstrate that we recreate the tables people2 and people_car2 above, we still use MyISAM but do not specify the primary key for the id. Then we create a non-unique index for the id and eagle_id respectively.

reate  people_id   people_id  people_car2(people_id);

Then execute the following query:

   people2 a,people_car2 b  a.id  b.people_id  a.id  ;

Query-16   people2 a,people_car2 b  a.id  b.people_id  a.id  ;

Query-17   people2 a,people_car2 b  a.id  b.people_id;

   people2  id  ;

Looking at the preceding Query-15, Query-16, Query-17, and Query-18, we found that MyISAM has different processing policies for the ref type.

For the ref type, the results of executing the above three statements on InnoDB are completely consistent.

 

Fulltext

Full-text index is used for the link. Generally, the indexes we use are all B-trees, which are not illustrated here.

 

Ref_or_null

This type is similar to ref. However, MySQL performs an additional search operation that contains the NULL column. The optimization of this join type is often used in solving subqueries. (For details, see here ).

Query-19 explain    people2  id    id  ;

   people2  id    id   ;

Note that Query-20 does not use ref_or_null, and the performance of InnnoDB is different this time (verification is required when the data volume is large ).

 

Index_merger

The join type indicates that the index merge optimization method is used. In this case, the key column contains the list of indexes used, and key_len contains the longest key element of the index used. For more information about index merge optimization, see here.

 

Unique_subquery

This type replaces the ref of the in subquery IN the following format:

value  ( primary_key  single_table  some_expr)

Unique_subquery is an index lookup function that can replace subqueries completely, improving efficiency.

 

Index _Subquery

The join type is similar to unique_subquery. An IN subquery can be replaced by an IN subquery, but it is only applicable to non-unique indexes IN the following forms:

value  ( key_column  single_table  some_expr)

 

Range

Only retrieve rows in a given range and use an index to select rows. The key column shows the index used. Key_len contains the longest key element of the index used. In this type, the ref column is NULL. When using the =, <>,>,> =, <, <=, is null, <=>, BETWEEN, or IN operator, you can use the range:

   people  id    id  ;

Note: In my test, we found that the type is range only when the id is a primary key or a unique index.

By the way, MySQL uses the same range to represent range queries and list queries.

explain    people  id ;

 explain    people  id  (,);

But in fact, there is a big difference in how MySQL uses indexes in these two cases:

We are not picky: The two access efficiency is different. For range condition queries, MySQL cannot use other index columns behind the range columns, but there is no such restriction for "multiple equality condition queries.

-- From the high-performance MySQL Third Edition

 

Index

The join type is the same as that of ALL except that the index tree is scanned. This is usually faster than ALL because index files are usually smaller than data files. This type usually tells us whether to use indexes for sorting.

   people   id;

MySQL uses indexes for sorting under any circumstances, so it will take time to study it carefully. The most typical is that order by is followed by the primary key.

 

ALL

The slowest method is full table scan.

 

In general, the performance of the above connection types is decreasing sequentially (system> const). Performance of different MySQL versions, different storage engines, and even different data volumes may be different.

 

Possible_keys

The possible_keys column specifies which index MySQL can use to find rows in the table.

 

Key

The key column displays the keys (indexes) actually determined by MySQL ). If no index is selected, the key is NULL. To FORCE MySQL to USE or IGNORE the indexes in the possible_keys column, use force index, use index, or ignore index in the query.

 

Key_len

The key_len column displays the key length determined by MySQL. If the key is NULL, the length is NULL. The length of the index used. The shorter the length, the better.

 

Ref

The ref column shows which column or constant is used together with the key to select rows from the table.

 

Rows

The rows column displays the number of rows that MySQL considers to be required for query execution. Note that this is an estimate.

 

Extra

Extra is another very important column in The EXPLAIN output. This column displays some detailed information about MySQL during the Query Process, which contains a lot of information. Select only a few key points for introduction.

Using filesort

MySQL can generate ordered results in two ways. By sorting or Using indexes, when Using filesort appears in Extra, it indicates that MySQL uses the latter, however, although it is called filesort, it does not mean that files are used for sorting, as long as the sorting may be completed in the memory. In most cases, index sorting is faster, so you should also consider optimizing the query.

 

Using temporary

The temporary table is used. Generally, it indicates that the query needs to be optimized. Even if you cannot avoid using the temporary table, try to avoid using the temporary hard disk table.

 

Not exists

MYSQL optimizes left join. Once it finds a row that matches the left join standard, it no longer searches.

 

Using index

It indicates that the query overwrites the index, which is a good thing. MySQL filters unwanted records directly from the index and returns hit results. This is done at the MySQL service layer, but you do not need to go back to the table to query records.

 

Using index condition

This is a new feature of MySQL 5.6, called "index conditional push ". Simply put, MySQL cannot perform operations such as like on indexes, but now it can, which reduces unnecessary IO operations, but can only be used on secondary indexes, for details, click here.

 

Using where

Note:: Using where in the Extra column indicates that the MySQL server returns the storage engine to the service layer and then applies WHERE filter.

 

The output of EXPLAIN is basically finished. It also has an EXTENDED command called explain extended, which can be used in combination with the show warnings command to see more information. It is useful to see the SQL statement reconstructed by the MySQL optimizer.

 

OK. Here is the explanation. In fact, all these contents are available online, but I will be more impressed by my actual drills. The next section describes show profile, slow query logs, and some third-party tools.