Using temporary and using filesort analysis

mysql> show create table t1;+-------+----------------------------------| table  | Create Table                                              +-------+----------------------- ---------| t1    | create table  ' t1 '   (  ' id '  int (11)  NOT NULL DEFAULT  ' 0 ',  ' age '  int (one)  DEFAULT NULL,  ' name '   varchar  DEFAULT NULL, PRIMARY KEY  (' id '), key  ' age_1 '   (' age ')  using btree)  engine=innodb default charset=utf8 |+-------+--------------------- ------------1 row in set  (0.00 sec) 1  test Order by using the index, sort by primary key ID, and go to primary key index. Primary key index B+tree, leaf node (stored row data) is indexed by primary keyOrganized in order. mysql> desc select * from t1 order by id;+----+-------------+------ -+-------+---------------+---------+---------+------+------+-------+| id | select_type |  table | type  | possible_keys | key     |  key_len | ref  | rows | extra |+----+-------------+-------+----- --+---------------+---------+---------+------+------+-------+|  1 | simple       | t1    | index | NULL           | primary | 4       |  null |    9 | null  |+----+-------------+-------+-------+-- -------------+---------+---------+------+------+-------+1 row in set  (0.00 sec) # Sort by the normal index of age, and go for a full table scan. He'sSo the normal index is not gone because it is less expensive to sort by filesort. If you go to the normal index, go to the two-level index b+tree, find the corresponding primary key keys, and then go to the primary key index number, find the leaf node primary key value corresponding to the row Records. mysql> desc select * from t1 order by age;+----+-------------+----- --+------+---------------+------+---------+------+------+----------------+| id | select_type  | table | type | possible_keys | key  | key_len |  ref  | rows | Extra           |+----+-------------+-------+------+---------------+------+---------+------+------+----------------+|   1 | simple      | t1    | all   | NULL          | NULL |  null    | null |    9 | using filesort | +----+-------------+-------+------+---------------+------+---------+------+------+----------------+1 row in set  (0.00 sec) Does not allow the query optimizer to select itself, forcing the index to be used. Mysql> desc select * from t1 force index (age)  order by  age;+----+-------------+-------+-------+---------------+-------+---------+------+------+-------+| id  | select_type | table | type  | possible_keys | key    | key_len | ref  | rows | extra |+----+-------- -----+-------+-------+---------------+-------+---------+------+------+-------+|  1 |  simple      | t1    | index | null           | age_1 | 5        | null |    9 | null  |+----+-------------+----- --+-------+---------------+-------+---------+------+------+-------+1 row in set  (0.00 sec) # Sort mysql> desc select * from t1 order by name;+----+--------with no indexed fields -----+-------+------+---------------+------+---------+------+------+----------------+| id |  Select_type | table | type | possible_keys | key  | key _len | ref  | rows | extra           |+----+-------------+-------+------+---------------+------+---------+------+------+--------------- -+|  1 | simple      | t1    |  all  | null          | null |  null    | null |    9 | using filesort  |+----+-------------+-------+------+---------------+------+---------+------+------+----------------+1 row in set  ( 0.00 SEC) ========================================================================= If Select column The column in   is indexed and does not need to be returned to the table,  direct Using index. #查询id字段, the ID is the primary key, Useing index, and does not return to the table. mysql> desc select id from t1 order by age;+----+-------------+---- ---+-------+---------------+-------+---------+------+------+-------------+| id | select_type  | table | type  | possible_keys | key   |  key_len | ref  | rows | extra       |+-- --+-------------+-------+-------+---------------+-------+---------+------+------+-------------+|  1  | simple      | t1    | index |  null          | age_1 | 5       | null  |    9 | using index |+----+-------------+-------+-------+------------ ---+-------+---------+------+------+-------------+1 row in set  (0.00 sec) #查询age字段, Normal index, Useing index, does not return table. mysql> desc select age from t1 order by age;  +----+----- --------+-------+-------+---------------+-------+---------+------+------+-------------+| id |  select_type | table | type  | possible_keys | key    | key_len | ref  | rows | Extra        |+----+-------------+-------+-------+---------------+-------+---------+------+------+------------- +|  1 | simple      | t1    | index | null          | age_1 |  5       | null |    9 | using  index |+----+-------------+-------+-------+---------------+-------+---------+------+------+-------- -----+1 row in set  (0.00 SEC) ======================================================== =================2 group by  Test mysql> desc select * from t1  group by id;+----+-------------+-------+-------+---------------+---------+---------+------+------+-- -----+| id | select_type | table | type  | possible_keys  | key     | key_len | ref  | rows |  extra |+----+-------------+-------+-------+---------------+---------+---------+------+------+------ -+|  1 |&nbsp Simple      | t1    | index | primary, age_1 | primary | 4       | null |     9 | null  |+----+-------------+-------+-------+---------------+--------- +---------+------+------+-------+1 row in set  (0.00 sec) mysql> desc  select * from t1 group by age;+----+-------------+-------+-------+-------------- -+-------+---------+------+------+-------+| id | select_type | table | type   | possible_keys | key   | key_len | ref  |  rows | extra |+----+-------------+-------+-------+---------------+-------+---------+----- -+------+-------+|  1 | simple      | t1     | index | age_1         | age_1 | 5        | null |    9 | null  |+ ----+-------------+-------+-------+---------------+-------+---------+------+------+-------+1 row in  set  (0.00 sec) #如果排序字段没有索引, extra  will appear  using temporary; using filesort# The default group by  is automatically sorted by the field that follows by, which increases system consumption and allows you to cancel the default sort. See below Experiment mysql> desc select * from t1 group by name ;+----+------- ------+-------+------+---------------+------+---------+------+------+---------------------------------+|  id | select_type | table | type | possible_keys | key   | key_len | ref  | rows | Extra                            |+----+-------------+-------+------+------------ ---+------+---------+------+------+---------------------------------+|  1 | simple       | t1    | ALL  | NULL           | NULL | NULL    |  null |    9 | using temporary; using filesort |+----+-- -----------+-------+------+---------------+------+---------+------+------+---------------------------------+1  row in set  (0.00 sec) mysql> desc select * from t1  group by name order by null;+----+-------------+-------+------+---------------+------ +---------+------+------+-----------------+| id | select_type | table |  Type | possiBle_keys | key  | key_len | ref  | rows | extra            |+----+-------------+-------+------+-------- -------+------+---------+------+------+-----------------+|  1 | simple       | t1    | ALL  | NULL           | null | null    | null |     9 | using temporary |+----+-------------+-------+------+---------- -----+------+---------+------+------+-----------------+1 row in set  (0.00 sec) # Default sort mysql>  select * from t1 group by name;+----+------+--------Not removed +| id | age  | name   |+----+------+--------+|  7  |    0 | aaaa   | |   1 |    0 | kenney | |   5 |    0 | tttt   |+----+------+--------+3  rows in set  (0.00 sec) #去掉默认排序后mysql >  select * from t1  group by name order by null;    +----+------+--------+| id  | age  | name   |+----+------+--------+|  1 |     0 | kenney | |   5 |    0 | tttt   | |   7 |    0 | aaaa   |+----+------+--------+3  rows in set  (0.00 SEC) Note: As far as possible by the index key to sort, so the efficiency will be very high.   We will also find that in the ordered statements there are using filesort, which literally might be understood as: using files for sorting or sorting in files. In fact, this is not true, it is a misleading word.   When we try to sort a field that doesn't have an index, it's filesoft. It has nothing to do with the file and is actually a quick insideQuick Sort. Execution Plan keyword Resolution: The extra field using filesortmysql requires an additional pass to find out how rows are retrieved in sorted order. Using index retrieves column information from a table by using only the information in the index tree without requiring further searching to read the actual rows. Using temporary in order to resolve the query, MySQL needs to create a temporary table to accommodate the results. In the Type field ref for each row from the preceding table, all rows with matching index values will read from this table that all is completely non-indexed, and the performance is very poor. Index 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.

This article is from the "Deep Mountain" blog, please make sure to keep this source http://kenneyzhou.blog.51cto.com/12427643/1889429

Using temporary and using filesort analysis