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 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')
alter table people add key(zipcode,firstname,lastname);
start with a simple query:
Query-1 explain select zipcode,firstname,lastname from people;
explain output results id, select_type, table, type, possible_keys, key, key_len, ref, rows, and extra colum &.
Query-2 explain select zipcode from (select * from people a) b;
a id is used in order to identify the selelct statements in the entire query, and through this simple nested query, you can see the greater id statement execution. this value may be null if this row is used to indicate union results of other rows, such as union statements:
Query-3 explain select * from people where zipcode = 100000 union select * from people where zipcode = 200000;
the type of select statement can have the following.
the simplest select query, not a union or query. query-1 .
in a nested query, the outermost select statement, the fi & t select statement in the union query. query-2 query-3 .
the second and subsequent select statements in union. query-3 .
a select statement in a from clause in a derived table select statement. query-2 .
results of a union query. query-3 .
as the name suggests, you first need to satisfy the condition of union, as well as the second and subsequent select statements in union, while the statement relies on external queries.
Query-4 explain select * from people where id in (select id from people where zipcode = 100000 union select id from people where zipcode = 200000 );
select type for select id from people where zipcode = 200000 in query 4 dependent union you may be surprised that this statement doesn't depend on external queries.
here's how optimizes the optimization of the in operator, optimizing the uncorrelated subquery in in to a correlated subquery ( see 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);
so. query-4 is written in this way:
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);
: sometimes the mysql optimizer is too"smart"practice that results in a large loss of performance in where terms that contain in ( ). you can see. high performance mysql third edition 6. 5. 1 association subquery section .
fi & t select statement in subquery.
Query-6 explain select * from people where id = (select id from people where zipcode = 100000);
same as dependent union relative union. query-5 .
in addition to some of these common select types, there are some other things that aren't covered here, and different mysql versions are different.
this line of information is about which table. sometimes it isn't a real table name.
Query-7 explain select * from (select * from (select * from people a) b ) c;
you can see an alias that's displayed if an alias is specified.
N > n is the id value, the result of the action that corresponds to the id value.
and. <union m, n > this type appears in the union statement, as shown in the query-4 .
: mysql treats these ordinary tables, but these"temporary tables"don't have any indexes.
a type column is important to illustrate how is associated with an associated operation, with an index. in mysql, the word"association"is much wider than a general meaning, and mysql assumes that any query is"association"and not just a query requires two tables. there are several categories.
the mysql optimizer reads it before the query and reads only once, so it's very fast when determining the most than one line. const will only be used when comparing constants and primary keys or unique indexes, and to compare all index fields. people have a primary key index on id, and there's a secondary index in ( zipcode, firstname, lastname ). so, query-8 the type is const. query-9 it isn't:
Query-8 explain select * from people where id=1;
Query-9 explain select * from people where zipcode = 100000;
note that the following query 10 cannot also use const table, even if it's a primary key, and only returns a result.
Query-10 explain select * from people where id >2;
this is a special case of the const connection type, which is a row for the table.
Query-11 explain select * from (select * from people where id = 1 )b;
a <derived2> is already a const table and there's only one record.
eq ref type is the best type of connection except const, which is used in all parts of an index and the index is unique or primary key.
it's important to note innodb and myisam engines. hsm is a small amount of data. type will be all. the people and people that we created above are by default.
Query-12 explain select * from people a,people_car b where a.id = b.people_id;
let's create two people2 table 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'm going to be a result of innodb performance balancing.
eq ref can be used to compare an indexed column with an operator. a comparison value can be a constant or an expression that uses a table that's read earlier in the table. if the index used is exactly the primary key, then it becomes a better const:
Query-14 explain select * from people2 a,people_car2 b where a.id = b.people_id and b.people_id = 1;
this type is different from eq ref, which is used by the prefix of the index, or the index isn't unique and primary key. a ref can be used to use an indexed column with an index with an = or <=> operator.
in order to illustrate we the above people2 and people car2 tables, is still used but id specifies primary key. and then we set up a index for id and people id.
reate index people_id on people2(id); create index people_id on people_car2(people_id);
then execute 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;
look at query 15, query 16, and query 17, and query 18 we found that the processing on the ref type has different strategies.
for the ref type, execute the above three statements with the exact same results.
links are performed using indexes. generally, the index we use is a, which isn't illustrated.
this type is similar to ref. but mysql will do an extra search that contains a null column. optimization of this join type often used in a query. ( see here for example ).
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 isn't ref or null, and innnodb isn't the same for the same time ( in case of large data ).
the join type represents the use of an index merge optimization method. the key column contains the list of indexes used, the key len contains the maximum key element used by the index used for the key. about index merge optimization see here.
it replaces the ref of the following form of in subquery:
value IN (SELECT primary_key FROM single_table WHERE some_expr)
unique subquery is an index lookup function that can completely replace and higher efficiency.
this join type is similar to the unique_subquery can replace 在 , but only a index in the following form of subquery:
value IN (SELECT key_column FROM single_table WHERE some_expr)
retrieves only rows of a given range, using an index to select rows. key column shows which index is used. key len contains the longest key element of the index used. in this type ref column is null. range can be used by using a co & tant when using =, <>,>,> =, <, <=, is null, <=>, between, or in operator.
Query-21 explain select * from people where id = 1 or id = 2;
note in my tests: it's found that the type is range only if id is a primary key or a unique index.
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 (1,2);
but in fact, mysql has a great difference in how to use indexes in both cases:
we aren't: these two access efficiency is different. for range conditional queries, mysql cannot use other index columns after a range column, but there's no limit for"multiple equivalent criteria queries".
from high performance mysql third edition
the join type is the same as all except that the index tree is scanned. it's usually faster than all because the index file is usually smaller than the data file. this type of general purpose is to tell us whether the query is sorted using an index.
Query-22 explain select * from people order by id;
as for what happens, mysql uses indexes to sort, and so on and more. most typically, order by is followed by a primary key.
one of the slowest ways, scan.
overall: the performance of the above types of connection types is ( system> const ), different mysql versions, different storage engines, and even.
the possible keys column indicates which index mysql can use to find rows in the table.
key column shows the key ( index ) that the mysql actually decides to use. if no index is selected, the key is null. to force mysql to use or ignore indexes in the possible keys column, use force index in the query, use index, or ignore index.
key len column displays the key length used by mysql. if the key is null, the length is null. length of the index used. in case of loss of accuracy, the shorter length.
ref column displays which column or co & tant is used key select a row from the table.
the number of rows that the column column displays when it thinks that it performs a query. note that this is a prediction value.
extra is another important column in the explain output, which displays some of the details of mysql in the query process, including many information, and only a few highlights.
mysql has two ways to produce ordered results, by sorting operations or using indexes. using filesort shows that mysql uses the latter, but note that although filesort is. most of the time using index sorting is faster, so generally consider optimizing queries.
note that a temporary table is used, and it's generally seen that the query needs to be optimized, even if you avoid the use of temporary tables.
mysql optimizes left join, once it finds a row that matches the left join standard, no longer searches.
it's a good thing to tell the query that the index is covered. mysql filters the unnecessary records directly from the index and returns the results of the hit. this is the mysql service layer, but no table table query records are required.
it's. 5.6 the new feature is called"index condition push". it's a simple point. MySQL it isn't on the index. it can't be performed. like this is done, but it's now possible, so that it reduces unnecessary. io action, but only on secondary index, details here.
a where clause is used to limit which rows match the next table or return to users.
additional columns appear using where indicates that the mysql server returns the storage engine back to the service layer after the where condition filtering.
this is basically an extended command that explains an extended command called explain extended, mainly combining the show, command to see more information. a more useful is to see the sql optimizer refactoring after you see it.
ok, explain that it's right here, and it's all on the web, but it's much more impressive. in the next section of , will introduce show profile, slow query logs, and some third-party tools.