Shulou(Shulou.com)06/01 Report--

Extra mainly has that kind of situation: Using index, Using filesort, Using temporary, Using where

Needless to say, Using where uses the where filter.

Data preparation:

CREATE TABLE `tblog` (`id` int (11) NOT NULL auto_increment, `title` varchar (50) default NULL, `typeId` int (11) default NULL, `a`int (11) default '0mm, PRIMARY KEY (`id`), KEY `blog` (`title`, `typeId`) ENGINE=InnoDB DEFAULT CHARSET=utf8

1 、 Using index

Indicates that an overlay index is used in the query to avoid scanning the data rows of the table.

Mysql > EXPLAIN select title from t_blog +-+ | id | select_type | table | type | possible_keys | key | key _ len | ref | rows | Extra | +-+ | 1 | SIMPLE | t _ Blog | index | NULL | index_1 | 158 | NULL | 7 | Using index | +-+ 1 row in set

It is known that the title field is part of the index_1 index. The previous sql only queries the title field, scans only the index file, not all the data rows of the table. In the extra column, Using index appears.

Mysql > EXPLAIN select * from t_blog +-- + | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | + -+ | 1 | SIMPLE | t_blog | ALL | NULL | 7 | +- -+ 1 row in set

In the previous statement, in addition to querying the indexed fields, the unindexed field [a] was also queried, resulting in scanning the data rows of the table, so there was no Using index in the extra column.

When there is only Using index and no Using where, it means that the index is used to read data. Take the first sql as an example.

When Using index and Using where appear at the same time, it indicates that the index is used for the lookup action, for example:

Mysql > EXPLAIN select title from t_blog where title = 'java' +-+-+ | id | select_type | Table | type | possible_keys | key | key_len | ref | rows | Extra | +- -+ | 1 | SIMPLE | t_blog | ref | index_1 | index_1 | 153 | const | 1 | Using where Using index | +-+ 1 row in set

2 、 Using filesort

Using filesort usually appears in order by, and when you try to sort a field that is not an index, mysql automatically sorts that field, a process called "file sorting".

Mysql > EXPLAIN select * from t_blog order by title +-+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | | Extra | +-- + | 1 | SIMPLE | t_blog | index | NULL | index_1 | 158 | | | NULL | 7 | | +-+ 1 row in set |

It is known that title is the first column index in the index_1 index, so the index takes effect when used alone, and the file sort is not generated when sorting according to the index.

Mysql > EXPLAIN select * from t_blog order by typeId +-- + | id | select_type | table | type | possible_keys | key | key_len | Ref | rows | Extra | +-+ | 1 | SIMPLE | t_blog | ALL | | NULL | 7 | Using filesort | +-+ 1 row in set |

Although typeId is the second column of the index_1 index, the index is invalid because the first column is missing. When sorting, it is impossible to sort by index, so mysql will sort automatically, resulting in file sorting.

Mysql > EXPLAIN select * from t_blog order by a +-- + | id | select_type | table | type | possible_keys | key | key_len | Ref | rows | Extra | +-+ | 1 | SIMPLE | t_blog | ALL | | NULL | 7 | Using filesort | +-+ 1 row in set |

There is no index on field a, so it is not possible to sort by index when sorting, resulting in a file sort.

What happens to Using filesort: when sorting, it is impossible to sort by index, and the mysql optimizer can only sort by itself, which greatly degrades performance and is undesirable.

3 、 Using temporary

Indicates that a temporary table was generated during the query to save the intermediate results. Mysql uses temporary tables when sorting query results, which is common in group by.

The essence of group by is to sort before grouping. Like order by, group by and index are closely related.

An attempt to group a field without an index results in a temporary table:

Mysql > EXPLAIN select title from t_blog group by typeId +- -+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +- -- + | 1 | SIMPLE | t_blog | index | NULL | index_1 | 158 | NULL | 7 | Using index Using temporary Using filesort | +- -+ 1 row in set

Grouping an indexed field does not produce a temporary table:

Mysql > EXPLAIN select title from t_blog group by title,typeId +-+ | id | select_type | table | type | possible_keys | key | key _ len | ref | rows | Extra | +-+ | 1 | SIMPLE | t _ Blog | index | NULL | index_1 | 158 | NULL | 7 | Using index | +-+ 1 row in set

Temporary tables are not generated when the fields of the order by clause and the group by clause are the same:

Mysql > explain select * from t_blog b left join t_type t on b.typeId = t.id group by b.id order by b.id +-+ | id | select_type | table | type | possible_keys | key | | key_len | ref | rows | Extra | + + | 1 | SIMPLE | b | index | NULL | PRIMARY | 4 | NULL | 7 | | 1 | SIMPLE | t | eq_ref | PRIMARY | PRIMARY | 4 | blog.b.typeId | 1 | | +- -+-+ 2 rows in set

A temporary table is generated when the fields of the order by clause and the group by clause are different:

Mysql > explain select * from t_blog b left join t_type t on b.typeId = t.id group by b.id order by b.title +-- + | id | select_type | Table | type | possible_keys | key | key_len | ref | rows | Extra | +- +-+ | 1 | SIMPLE | b | index | NULL | index_1 | 158 | NULL | 7 | Using temporary | 1 | SIMPLE | t | eq_ref | PRIMARY | PRIMARY | 4 | blog.b.typeId | 1 | | + -+ 2 rows in set

When using left join at that time, if both the order by clause and the group by clause come from the slave table, a temporary table will be generated:

Mysql > explain select * from t_blog b left join t_type t on b.typeId = t.id group by t.id order by t.id +- -+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | + -+-+ | 1 | SIMPLE | b | ALL | NULL | 7 | Using temporary Using filesort | | 1 | SIMPLE | t | eq_ref | PRIMARY | PRIMARY | 4 | blog.b.typeId | 1 | +-+ -+-+ 2 rows in setmysql > explain select * from t_blog b left join t_type t on b.typeId = t.id group by t.id order by t.name +- -+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | + -+-+ | 1 | SIMPLE | b | ALL | NULL | 7 | Using temporary Using filesort | | 1 | SIMPLE | t | eq_ref | PRIMARY | PRIMARY | 4 | blog.b.typeId | 1 | +-+ -+-+ 2 rows in set

The appearance of Using temporary means that the intermediate result of temporary table storage is produced and the temporary table is finally deleted, which is a performance-consuming process.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.