Mysql Optimized Index-Usingfilesort bitsCN.com
Mysql Optimized Index -- Using filesort
When Using Explain to analyze SQL statements, it is often found that Using filesort appears in some statements in the Extra column. according to the description of mysql official documentation:
Reference
MySQL must do an extra pass to find out how to retrieve the rows in sorted order. the sort is done by going through all rows according to the join type and storing the sort key and pointer to the row for all rows that match the WHERE clause.
The translation on the Chinese manual is awkward:
Reference
"Mysql requires an additional pass to find out how to retrieve rows in order, by browsing all rows based on the join type and saving the sorting keywords and row pointers for all rows matching the where clause, the keywords are sorted and the rows are retrieved in the sorting order."
In general, Using filesort is a slow external sorting in Mysql. if it can be avoided, we can optimize indexes to avoid Using filesort, this increases the speed.
Here is a simple example:
Create table 'testing '(
'Id' int (10) unsigned not null auto_increment,
'Room _ number' int (10) unsigned not null default '0 ',
Primary key ('id '),
KEY 'room _ number' ('room _ number ')
) ENGINE = MyISAM default charset = latin1
Write a stored procedure askwan and insert 0.1 million pieces of test data
Mysql> DELIMITER $
Drop procedure if exists 'askwan '. 'askwan' $
Create procedure 'askwan '. 'askwan '()
BEGIN
DECLARE v int default 1;
WHILE v <100000;
DO
Insert into testing VALUES (v, v );
SET v = v + 1;
End while;
END $
Mysql> DELIMITER;
Mysql> CALL askwan ();
Query OK, 1 row affected (13.21 sec)
OK. The data is ready. start the experiment.
Based on the table information created in the preceding example, I have created two indexes, one primary key id and one room_number column index.
Now let's look at an SQL statement,
SELECT id FROM testing WHERE fig = 1000 order by id;
Analyze
Mysql> explain select id FROM testing WHERE room_number = 1000 order by id;
+ ---- + ------------- + --------- + ------ + --------------- + ------------- + --------- + ------- + ------ + ------------------------------- +
| Id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+ ---- + ------------- + --------- + ------ + --------------- + ------------- + --------- + ------- + ------ + ------------------------------- +
| 1 | SIMPLE | testing | ref | room_number | 4 | const | 1 | Using where; Using filesort |
+ ---- + ------------- + --------- + ------ + --------------- + ------------- + --------- + ------- + ------ + ------------------------------- +
1 row in set (0.00 sec)
Using filesort appears and the room_number column index is used. However, the index used here is for the room_number condition after the WHERE clause, and the last sorting is based on the id, this is what the manual says, "an additional sort "!, Then there will be Using filesort. based on a previous article I have written, I will create a composite index room_number_id.
Alter table testing add index room_number_id (room_number, id );
Let's analyze it.
Mysql> explain select id FROM testing WHERE room_number = 1000 order by id;
+ ---- + ------------- + --------- + ------ + ---------------------------- + ---------------- + --------- + ------- + ------ + ------------------------ +
| Id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+ ---- + ------------- + --------- + ------ + ---------------------------- + ---------------- + --------- + ------- + ------ + ------------------------ +
| 1 | SIMPLE | testing | ref | room_number, room_number_id | 4 | const | 1 | Using where; |
+ ---- + ------------- + --------- + ------ + ---------------------------- + ---------------- + --------- + ------- + ------ + ------------------------ +
1 row in set (0.00 sec)
Now Using filesort is missing.
Summary:
1. generally, there are order by statements. when the index is improperly added, Using filesort may occur. at this time, we need to optimize the SQL statements and indexes. However, this is not to say that the emergence of Using filesort is a serious problem. this is not the case. the example here is extreme, and it is almost impossible to have such a foolish query, optimization, or non-optimization, it depends on whether it affects business performance.
2. from the above we can see the Union index, which can also be called a multi-column index, for example, key ('A1', 'A2 ', 'A3', 'A4, the general idea of sorting is to first sort by A1, A1 is the same, and then A2, and so on. in this way, for (A1), (A1, A2), (A1, A2, a3) indexes are valid, but the indexes such as (A2, A3) are invalid.
BitsCN.com