MySQL optimization-to do what you want, you must first sharpen the tool's EXPLAIN_MySQL

MySQL optimization-to do what you want, you must first sharpen the tool's EXPLAIN MySQLexplain

BitsCN.com

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.

Prepare the MySQL version in the environment:

Create Test table

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);

Insert 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 for testing

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

Description

Start with a simple query:

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

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

Id

Query-2explain select zipcode from (select * from 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:

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

Select_type

The types of SELECT statements can be.

SIMPLE

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

PRIMARY

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

UNION

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

DERIVED

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

UNION RESULT

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

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.

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

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 ).

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

A statement like this will be rewritten as follows:

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

Therefore, Query-4 is actually rewritten as follows:

Query-5explain 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 subquery.

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

DEPENDENT SUBQUERY

Similar to dependent union. See Query-5.

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.

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

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

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

And M, N> This type appears in the UNION statement, see Query-4.

Type

The type column is very important. it is used to describe how tables are joined and whether indexes are used. There are mainly the following types.

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 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 cannot use the const table. although it is also a primary key, only one result is returned.

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

System

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

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

It is already a const table and has 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.

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

Let's create two MyISAM tables 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-13explain select * from people2 a,people_car2 b where 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:

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

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 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 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-19mysql> explain select * from people2 where id = 2 or id is null;

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

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 IN (SELECT primary_key FROM single_table WHERE 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 IN (SELECT key_column FROM single_table WHERE 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:

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

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

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.

Query-22explain select * from people order by 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.

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.

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.

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.

BitsCN.com