MySQL Optimizer-工欲善其事, its prerequisite explain

Transferred from: http://www.cnblogs.com/magialmoon/archive/2013/11/23/3439042.html

MySQL official manual description of the explain naming document: Https://dev.mysql.com/doc/refman/5.7/en/explain-output.html#explain_select_type

The recent slow contact with MySQL, understanding how to optimize it is also imminent, say 工欲善其事, its prerequisite. Recently I was going to get to know the next few tools that are often used to optimize MySQL. Today we will briefly introduce the next explain.

Environment ready MySQL version:

Create a test table

CREATE TABLE people (    ID bigint auto_increment primary key,    ZipCode char (+) NOT null default ',    address Varch AR (+) NOT null default ' ',    LastName char (+) NOT null default ' ',    FirstName char (+) NOT null default ',    bi Rthdate Char (TEN) NOT null default '); CREATE TABLE People_car (    people_id bigint,    plate_number varchar (+) NOT null default ',    engine_number varchar (+) NOT null default ' ',    lasttime timestamp);

Inserting test data

Insert into people (zipcode,address,lastname,firstname,birthdate) VALUES (' 230031 ', ' Anhui ', ' Zhan ', ' Jindong ', ' 1989-09-15 '), (' 100000 ', ' Beijing ', ' Zhang ', ' San ', ' 1987-03-11 '), (' 200000 ', ' Shanghai ', ' Wang ', ' Wu ', ' 1988-08-25 ') Insert into People_car (people_id,plate_number,engine_number,lasttime) VALUES (1, ' A121311 ', ' 12121313 ', ' 2013-11-23 : 21:12:21 '), (2, ' B121311 ', ' 1s121313 ', ' 2011-11-23:21:12:21 '), (3, ' C121311 ', ' 1211sas1 ', ' 2012-11-23:21:12:21 ')

Create an index to test

ALTER TABLE people add key (Zipcode,firstname,lastname);

EXPLAIN Introduction

Start with one of the simplest queries:

Query-1Explain select zipcode,firstname,lastname from people;

The explain output results in a total of id,select_type,table,type,possible_keys,key,key_len,ref,rows and extra columns.

ID (Query ID)

Query-2Explain select ZipCode from (SELECT * from people a) b;

The ID is used to sequentially identify the SELELCT statement in the entire query, and with this simple nested query you can see that the higher the ID statement is executed. The value may be null if this line is used to describe the union result of another row, such as the Union statement:

Query-3Explain select * from people where zipcode = 100000 Union SELECT * from people where zipcode = 200000;

Select_type (query type)

The type of SELECT statement can be in the following categories.

Simple (easy query)

The simplest select query does not use a union or subquery. See Query-1.

PRIMARY (primary key query)

In a nested query, it is the outermost SELECT statement, which is the topmost SELECT statement in the Union query. See Query-2 and Query-3.

Union (Union query)

The second and subsequent SELECT statements in the Union. See Query-3.

DERIVED (query result as temporary table for outer query)

The SELECT statement from the FROM clause in the derived table SELECT statement. See Query-2.

Union result (query result union)

The result of a union query. See Query-3.

DEPENDENT UNION (Query results are merged and provided for outer use)

As the name implies, you first need to meet the conditions of the Union, and the second and subsequent SELECT statements in the Union, and the statement relies on the external query.

Query-4Explain select * from people where ID in  (SELECT ID from people where zipcode = 100000 Union SELECT ID from people where zipcode = 200000);

The Query-4 select ID from people where ZipCode = 200000 Select_type is DEPENDENT UNION. You may be surprised that this statement does not depend on external queries.

Here, incidentally, the MySQL optimizer optimizes the in operator, and the optimizer optimizes the uncorrelated subquery in in to a correlated subquery (see here for correlated subquery).

SELECT ... from T1 WHERE t1.a in (SELECT b from T2);

A statement like this would be rewritten like this:

SELECT ... from T1 where EXISTS (SELECT 1 from t2 where t2.b = t1.a);

So Query-4 is actually rewritten like this:

Query-5Explain select * from people o where exists  (select ID from people where ZipCode = 100000 and id = o.i D Union Select ID from people where ZipCode = 200000 and  id = o.id);

digression : Sometimes the MySQL optimizer's too "smart" approach can cause a significant loss in the performance of a subquery statement that contains in (). See the "high-performance MySQL third edition"6.5.1 associated subqueries section .

Subquery

The first SELECT statement in a subquery.

query-6explain Select * from people  where id =  (select id from people where zipcode = 100000);

DEPENDENT subquery

and dependent union are the same relative to union. See Query-5.

In addition to the above several common select_type there are some other here is not introduced, different MySQL version is not the same.

Table

This line of information is about which table is displayed. Sometimes it's not a real table name.

Query-7Explain select * FROM (SELECT * FROM (SELECT * from people a) b) C;

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

<derived N > n is the ID value that refers to the result of that step of the ID value.

and <unionm,n> This type, which appears in the Union statement, see Query-4.

Note : MySQL treats these tables like normal tables, but these "temporary tables" are not indexed.

Type (Access table mode)

The type column is important to describe how the table is associated with the table and whether the index is used. MySQL "Association" is more broadly than in general, MySQL thinks that any query is an "association", and not only a query requires two tables is called Association, so you can also understand how MySQL access to the table. There are several categories below.

Const

When it is determined that there will be at most one row, the MySQL optimizer reads it before the query and reads it only once, so it is very fast. Const is used only when comparing constants and primary keys or unique indexes, and all indexed fields are compared. The People table has a primary key index on the ID and a level two index at (zipcode,firstname,lastname). So the type of Query-8 is const and Query-9 is not:

Query-8Explain select * from people where id=1;

Query-9Explain select * from people where zipcode = 100000;

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

Query-10Explain select * from people where ID >2;

System

This is a special case of the const connection type, where only one row of the table satisfies the condition.

Query-11Explain select * FROM (SELECT * from people where id = 1) b;

<derived2> is already a const table and has only one record.

EQ_REF (Index association Row Equality operation)

The Eq_ref type is the best connection type except for const, which is used in all parts of an index to be joined and the index is unique or primary KEY.

It is important to note that the InnoDB and MyISAM engines are somewhat different at this point. InnoDB when the amount of data is smaller, the type will be all. The people and people_car that we created above are InnoDB tables by default.

Query-12Explain select * from people a,people_car b where a.id = b.people_id;

We created two MyISAM tables People2 and People_car2 to try:

CREATE TABLE people2 (    ID bigint auto_increment primary key,    ZipCode char (+) NOT null default ',    address Varc Har (+) NOT null default ' ',    LastName char (+) NOT null default ' ',    FirstName char (+) NOT null default ' ',    b Irthdate Char (TEN) NOT null default ') ENGINE = MyISAM; CREATE TABLE people_car2 (    people_id bigint,    plate_number varchar (+) NOT null default ',    engine_number varchar (+) NOT null default ' ',    lasttime timestamp) ENGINE = MyISAM;

Query-13explain SELECT * from People2 a,people_car2 b where a.id = b.people_id;

I think this is a result of InnoDB's performance tradeoff.

Eq_ref can be used for indexed columns that use the = operator comparison. The comparison value can be a constant or an expression that uses a column of a table that was read earlier in the table. If the index used by the association is just another primary key, it becomes a better const:

Query-14Explain select * from People2 a,people_car2 b where a.id = b.people_id and b.people_id = 1;

Ref (Index Association row non-equality operation)

This type differs from eq_ref in that it uses only the leftmost prefix of the index for the association operation, or that the index is not unique and primary KEY. Ref can be used with indexed columns that use the = or <=> operator.

To show that we re-establish the People2 and PEOPLE_CAR2 tables above, we still use MyISAM but do not specify primary key for the ID. We then set up a non-unique index for the ID and people_id, respectively.

reate index people_id on people2 (ID), CREATE index people_id on people_car2 (people_id);

Then execute the following query:

Query-15Explain select * from People2 a,people_car2 b where a.id = b.people_id and a.id > 2;

Query-16Explain select * from People2 a,people_car2 b where a.id = b.people_id and a.id = 2;

Query-17Explain select * from People2 a,people_car2 b where a.id = b.people_id;

Query-18Explain select * from people2 where id = 1;

Looking at the query-15,query-16 and query-17,query-18 above, we found that there are different strategies for MyISAM processing on ref types.

For ref types, executing the above three statements above the INNODB results in exactly the same way.

Fulltext

Links are made using a full-text index. In general, we use the index is B-tree, here is not an example to illustrate.

Ref_or_null

This type is similar to ref. But MySQL will do an extra search for operations that contain NULL columns. The optimization of the join type is often used in the resolution subquery. (see here).

Query-19mysql> explain select * from people2 where id = 2 or ID is null;

Query-20Explain select * from people2 where id = 2 or ID was not null;

Note that Query-20 is not using Ref_or_null, and Innnodb This performance is not the same (data volume is still to be verified).

Index_merger

The join type represents the use of the index merge optimization method. In this case, the key column contains the list of indexes used, and Key_len contains the longest key element for the index used. About index merge optimization look here.

Unique_subquery

This type replaces the ref of the in subquery in the following form:

Value in (SELECT primary_key from single_table WHERE some_expr)

Unique_subquery is an index lookup function that can completely replace a subquery and be more efficient.

Index_subquery

The join type is similar to unique_subquery. You can replace in subqueries, but only for non-unique indexes in the following form of subqueries:

Value in (SELECT key_column from single_table WHERE some_expr)

Range

Retrieves only the rows for a given range, using an index to select rows. The key column shows which index is used. The Key_len contains the longest key element of the index being used. In this type, the ref column is null. When using the =, <>, >, >=, <, <=, is NULL, <=>, between, or in operators, you can use range when comparing key columns with constants:

Query-21Explain select * from people where id = 1 or id = 2;

Note In my test: found that only the ID is a primary key or a unique index, the type will be range.

Here, by the way. MySQL uses the same range to represent scope queries and list queries.

Explain select * from people where ID >1;

Explain select * from people where ID in (at);

But in fact, there's a big difference between how MySQL uses indexes in both cases:

We are not picky: the two types of access efficiency are different. For range criteria queries, MySQL cannot use the other index columns that follow the range column, but there is no limit to "multiple equal-condition queries".

--from the third edition of high-performance MySQL

Index

The join type is the same as all except that only the index tree is scanned. This is usually faster than all, because the index file is usually smaller than the data file. The usual function of this type is to tell us whether the query uses an index to sort operations.

Query-22Explain select * from people the Order by ID;

As for when MySQL will use the index to sort, and so on time to study carefully. The most typical is the primary key followed by the order by.

All

One of the slowest ways, the full table scan.

Overall: The performance of the above types of connections is decremented (system>const), different MySQL versions, different storage engines, and even different data volumes may behave differently.

Possible_keys

The Possible_keys column indicates which index MySQL can use to find rows in the table.

Key

The key column shows the keys (indexes) that MySQL actually decides to use. If no index is selected, the key is null. To force MySQL to use or ignore the indexes in the Possible_keys column, use the forces Index, using index, or ignore index in the query.

Key_len

The Key_len column shows the key lengths that MySQL decides to use. If the key is null, the length is null. The length of the index to use. Without loss of accuracy, the shorter the better.

Ref

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

Rows

The Rows column shows the number of rows that MySQL must check when it executes the query. Note that this is a pre-valuation.

Extra

Extra is another very important column in the explain output, which shows some of the details of MySQL in the query process, with a lot of information, and only a few highlights to choose from.

Using Filesort

There are two ways MySQL can generate ordered results, either by sorting operations or by using an index, when a using Filesort in extra shows that MySQL uses the latter, but note that although it is called filesort, it does not mean that the file is used for sorting, As long as the possible sort is done in memory. Indexing is faster in most cases, so it's generally time to consider optimizing queries.

Using Temporary

The description uses the temporary table, generally see that it indicates that the query needs to be optimized, even if the use of temporary tables to avoid the use of hard disk temporary table.

NOT EXISTS

MySQL optimizes the left join, and once it finds a row that matches the left join standard, it no longer searches.

Using Index

It is a good thing to note that the query is covered by the index. MySQL filters unwanted records directly from the index and returns the hit results. This is done by the MySQL service layer, but there is no need to return the table query records.

Using Index Condition

This is the new feature of MySQL 5.6, called "Index conditional Push". The simple point is that MySQL was originally unable to perform operations such as like in the index, but now it is possible, which reduces unnecessary IO operations, but can only be used on the level two index, details point here.

Using where

A WHERE clause is used to restrict which rows will match the next table or are returned to the user.

Note : The extra column appears using where to indicate that the MySQL server returns the storage engine to the service layer and then applies the Where Condition filter.

Explain the output of the basic introduction, it also has an extended command called explain EXTENDED, mainly combined with the show warnings command to see some more information. One of the more useful is to see the MySQL optimizer sql after refactoring.

MySQL Optimizer-工欲善其事, its prerequisite explain