MySQL optimization to do his work well, must first sharpen his tools of EXPLAIN

Recommended for you: Get network issues from WhatsUp Gold. Not end users.

Recently, slowly contact with MySQL, how to optimize it is imminent to understand the words, to do his work well, must first sharpen his tools. I want to know MySQL frequently used tools to optimize several recent. Today we introduce EXPLAIN.

The preparation of environment

MySQL version:

Create table test

CREATE TABLE people(
    id bigint auto_increment primary key,
    zipcode char(32) not null default '',
    address varchar(128) not null default '',
    lastname char(64) not null default '',
    firstname char(64) not null default '',
    birthdate char(10) not null default ''
);

CREATE TABLE people_car(
    people_id bigint,
    plate_number varchar(16) not null default '',
    engine_number varchar(16) not null default '',
    lasttime timestamp
);

Insertion of 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 index used to test

alter table people add key(zipcode,firstname,lastname);

EXPLAIN introduction

First from the most simple query began:

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

EXPLAIN ID, output select_type, table, type, possible_keys, key, key_len, ref, rows and Extra columns.

id

Query-2
explain select zipcode from (select * from people a) b;

ID is used to sequence identification the entire query in SELELCT statement, through the simple nested query can see the ID statement executes more more. This value may be NULL, if this line is used to illustrate the results are combined with other lines, such as the UNION statement:

Query-3
explain select * from people where zipcode = 100000 union select * from people where zipcode = 200000;


select_type

Type of SELECT statement, can have the following.

SIMPLE

The most simple SELECT queries, do not use UNION or sub query. See Query-1.


PRIMARY

In a nested query is a SELECT statement that the outermost layer, in the UNION query is a SELECT statement in the front. See Query-2 and Query-3.

UNION

SELECT statements in UNION second and later. See Query-3.


DERIVED

Derived tables in the FROM clause of the SELECT statement in the SELECT statement. See Query-2.


UNION RESULT

The result of an UNION query. See Query-3.


DEPENDENT UNION

As the name implies, first of all need to satisfy the UNION condition, the SELECT statement and the UNION second and later, at the same time the statement is dependent on external query.

Query-4
explain select * from people where id in  (select id from people where zipcode = 100000 union select id from people where zipcode = 200000 );

Query-4 select id from people where zipcode = 200000 select_typeDEPENDENT UNION. You might wonder this statement does not depend on external query.

Here by the way to optimize the IN operator MySQL optimizer, the optimizer will IN uncorrelated subquery into a correlated subquery (about correlated subquery.Here).

SELECT ... FROM t1 WHERE t1.a IN (SELECT b FROM t2);

Such a statement would be rewritten in such:

SELECT ... FROM t1 WHERE EXISTS (SELECT 1 FROM t2 WHERE t2.b = t1.a);

So Query-4 was rewritten in such:

Query-5
explain select * from people o where exists  (select id from people where zipcode = 100000 and id = o.id union select id from people where zipcode = 200000  and id = o.id);


SUBQUERY

The first SELECT statement in the query subsystem.

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


DEPENDENT SUBQUERY

And DEPENDENT UNION relative to UNION. See Query-5.

In addition to the above several common select_type and some other here is not one one introduced, different versions of the MySQL are not the same.


table

This line information display is about a table. Sometimes it is not the real table name.

Query-7
explain select * from (select * from (select * from people a) b ) c;

We can see if the alias name is specified on the show alias.

<derivedN>N is the ID value, the ID value that the step of the operation of the corresponding.

Also<unionM,N>This type, appear in UNION statements, see Query-4.

Be careful: MySQL treats these tables and table, but these "temporary table" is no index.


type

The type column is very important, is used to illustrate the between tables is how to carry out related operation, have not used the index. MySQL "association" is a word than common sense to broad, MySQL believes that any query is a "relation", and not just a query requires two tables is related, so we can understand how MySQL access list. Following several major categories.

const

When it is determined that only has one line matching, MySQL optimizer will read it and read only once before in the query, therefore very fast. Const will only use the constants and the primary key or unique index were compared, and it is more all the index field. The people table has a primary key index in ID, in the (zipcode, firstname, LastName) has a two level index. So the Query-8 type is const and Query-9 is not:

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

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

Note the Query-10 cannot use the const table, although is the primary key, it will only return a result.

Query-10
explain select * from people where id >2;

system

This is a special case of the const connection type, table only a line to meet the conditions.

Query-11
explain select * from (select * from people where id = 1 )b;

<derived2>Is a const table and only one record.


eq_ref

The eq_ref type is in addition to the const the best connection type, it is used in all parts of an index is connected with the UNIQUE or PRIMARY KEY and index.

Note that InnoDB and MyISAM engine at this point a little difference. InnoDB when the data quantity is small compared to type All. We created above people and people_car are InnoDB by default table.

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

We create two MyISAM table people2 and people_car2.:

CREATE TABLE people2(
    id bigint auto_increment primary key,
    zipcode char(32) not null default '',
    address varchar(128) not null default '',
    lastname char(64) not null default '',
    firstname char(64) not null default '',
    birthdate char(10) not null default ''
)ENGINE = MyISAM;

CREATE TABLE people_car2(
    people_id bigint,
    plate_number varchar(16) not null default '',
    engine_number varchar(16) not null default '',
    lasttime timestamp
)ENGINE = MyISAM;
Query-13
explain select * from people2 a,people_car2 b where a.id = b.people_id;

I think this is InnoDB a result of the performance tradeoffs.

Eq_ref can be used to using the = operator is indexed column. Comparison of value expressions can be constant or a read on the table in front of the columns of the table. If the correlation index was also a primary key, it will become a better const.:

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


ref

This type of unlike eq_ref, it only uses the index in the operation associated leftmost prefix, or index is not UNIQUE and PRIMARY KEY. Ref can be used for = or <=> column is indexed operator.

In order to explain us to rebuild the above people2 and people_car2 table, still use MyISAM but not assigned to ID primary key. Then we respectively to ID and people_id to establish the non unique index.

reate index people_id on people2(id);
create index people_id on people_car2(people_id);

Then perform the following query:

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

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

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

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

See the above Query-15, Query-16 and Query-17, Query-18 we found that MyISAM treatment on ref types and different strategies.

For the ref type, the implementation of three statements were the same as above in InnoDB.


fulltext

The link is to use a full-text index of. The index we used are generally B tree, here is not to illustrate.


ref_or_null

This type is similar to Ref. But MySQL will do an additional search contains NULL column operation. In solving the optimization the connection types often used in sub query. (seeHere).

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

Query-20
explain select * from people2 where id = 2 or id is not null;

Note that Query-20 use is not the ref_or_null, but also InnnoDB the performance is not the same (a large quantity of data cases to be verified).


index_merger

The connection types that use the index combined with optimization method. In this case, the key column contains the index list, key_len contains the longest key elements using the index. A combined optimization indexHere.


unique_subquery

This type of replacement the following forms of IN subquery ref:

value IN (SELECT primary_key FROM single_table WHERE some_expr)

Unique_subquery is an index lookup function, can completely replace the subquery, higher efficiency.


index_subquery

The connection type is similar to unique_subquery. Can replace the IN sub queries, but only suitable for non unique index in the form of sub query:

value IN (SELECT key_column FROM single_table WHERE some_expr)

range

Retrieve only the given range, the use of a index to select the row. The key column shows the use of which index. Key_len contains the longest key elements using the index. In the type ref as NULL. When using =, <>, > > = <,;,;, <, NULL, <= IS; =>, BETWEEN or the IN operator, using constant comparison key column, you can use the range:

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


Note: in my tests found that only ID is a primary key or unique index type to range.

Here by the selective MySQL use the same range to represent the range query and query list.

explain select * from people where id >1;

 explain select * from people where id in (1,2);

But in fact these two cases MySQL how to use the index is very different:

We're not picky: this two kinds of access efficiency are different. For range query, other index range of MySQL cannot use the column after column, but for "query" several equivalent conditions is not the limit.

——From "high performance MySQL version third


index

The connection types with the same ALL, but only the index tree is scanning. This is usually faster than ALL, because the index file is usually higher than the small data file. Effects of this type usually is to tell us whether a query using the index of the sort operation.

Query-22
explain select * from people order by id;

As for the cases of what MySQL will sort the index, and so have the time to carefully study. The most typical is the order by is followed by a primary key.


ALL

One way the slowest, namely a full table scan.


Overall: performance on several types of connections is descending (system> const), different versions of MySQL, different storage engines even data different performance may not be the same.


possible_keys

The possible_keys column indicates that MySQL can use any index to find the row in the table.


key

The key column shows the actual decision to use MySQL key (Suo Yin). If there is no choice index, the key is NULL. To enforce the use of MySQL or ignore the possible_keys columns in the index, the use of FORCE INDEX, USE INDEX or IGNORE INDEX in the query.


key_len

The key_len column shows the key length is determined using the MySQL. If the key is NULL, the length is NULL. The use of the length of the index. Without loss of accuracy in case of short length, the more the better .


ref

The ref column shows which columns or constant and key is selected from the table row.


rows

The rows column shows MySQL thinks it must check the execution of the query rows. Note that this is an estimate.


Extra

Extra is another very important column EXPLAIN output, this column shows some detailed information about MySQL in the query process, a lot of information, just a few key introduction.

Using filesort

MySQL there are two ways to generate ordered results, through the sorting operation or use the index, when the Using filesort Extra MySQL the use of the latter, but note that although the name is filesort but does not mean that is used to sort the file, as long as possible sorting is done in memory. Sorting faster using index in most cases, so at this time also need to consider the optimization of query.


Using temporary

Note the use of a temporary table, generally see it that query optimization, even if not avoid using temporary table should also try to avoid the use of hard disk temporary tables.


Not exists

Optimization of MYSQL LEFT JOIN, once it finds matching LEFT JOIN standard, is no longer the search.


Using index

The query is the coverage of the index, which is a good thing. MySQL directly from the index filtration unwanted records and returns the results hit. This is the MySQL services layer to complete, but without the back table query log.


Using index condition

This is the new features of MySQL 5.6 out, called the "index terms push". Simple point is MySQL the original is unable to perform such as the like operating in the index, but now you can, thus reducing unnecessary IO operations, but can only be used in the two level index, detailsHere.


Using where

Use the WHERE clause to restrict what is with the next table matching or is returned to the user.

Be careful: The Extra column Using where said the MySQL server will return after the storage engine service layer and application of WHERE filter.


Output EXPLAIN basic introduced, it has an extended command called EXPLAIN EXTENDED, is combined with the SHOW WARNINGS command to see some more information. A useful comparison can be seen after the reconstruction of SQL MySQL optimizer.


Ok,EXPLAIN is here, but these contents are on-line, but his actual practice will be more impressive. The next section will introduce SHOW PROFILE.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download

Posted by Cassandra at December 03, 2013 - 12:38 PM