Mysql Using FileSort问题

柔情只为你懂 2021-12-11 06:09 284阅读 0赞

问题：明明order by的字段建立了索引，结果还是Using FileSort？

Using filesort表示在索引之外，需要额外进行外部的排序动作。导致该问题的原因一般和order by有者直接关系，一般可以通过合适的索引来减少或者避免。

explain SELECT \* FROM table\_item WHERE user\_id = 2 ORDER BY item\_id LIMIT 0, 5  
user\_id 和 item\_id 分别建立一个索引，对此语句MySQL选择了 user\_id索引，那么 item\_id 的索引没有起到任何用处。当排序时记录数较多，内存中的排序 buffer满了，只能 Using filesort 进行外部排序。  
解决方式为对这两列建立组合索引。

explain SELECT \* FROM table\_item WHERE user\_id = 2 and user\_age > 20 ORDER BY item\_id LIMIT 0, 5  
建立组合索引（user\_id, user\_age, item\_id），看似非常完美。但由于user\_age规则不是确定值，使用该组合索引的话实际上需要先按索引找出一个个user\_age下的东西后再对其进行排序，仍然会filesort。  
解决方式是使用组合索引（user\_id, item\_id），对排序好的item\_id再过滤到不满足user\_age > 20的条目，不会出现filesort。

explain SELECT \* FROM table\_item WHERE user\_id = 2 ORDER BY item\_attr desc, item\_id LIMIT 0, 5  
建立了组合索引（user\_id, item\_attr, item\_id)，因为item\_attr是降序而item\_id是升序，从而仍然需要外部排序。  
如果item\_attr和item\_id都是升序或者都是降序，则不会出现filesort；建立组合索引（user\_id, item\_attr desc, item\_id)现象依旧。  
Mysql5.1 在线文档“13.1.4. CREATE INDEX语法”中提到：“一个index\_col\_name规约可以以ASC或DESC为结尾。这些关键词将来可以扩展，用于指定递增或递减索引值存 储。目前，这些关键词被分析，但是被忽略；索引值均以递增顺序存储。”  
可以看出无法通过修改索引来避免这个filesort，只能是如果可能的话修改查询的排序条件。

总结：**主要的是，oder by 的字段要与where条件中的字段一致（也可以是联合索引）。**

下面是我的实践：

### 建立联合索引前： ###

![1264791-20190707200727554-1541898692.png][]

![1264791-20190707200738078-989834767.png][]

### 建立联合索引后： ###

![1264791-20190707200756503-872813794.png][]

![1264791-20190707200801501-1108682060.png][]

![1264791-20190707200806796-2069041234.png][]

转载于:https://www.cnblogs.com/jikuanhao/p/11147391.html

[1264791-20190707200727554-1541898692.png]: /images/20211211/db9a03c391b041008f06a89e06d39259.png
[1264791-20190707200738078-989834767.png]: /images/20211211/172fdc85172f412a9a31b5f1beb138a8.png
[1264791-20190707200756503-872813794.png]: /images/20211211/e217a135f1844593b1fafbda2aa34199.png
[1264791-20190707200801501-1108682060.png]: /images/20211211/f5728fd0ab4947de9878e99b268444be.png
[1264791-20190707200806796-2069041234.png]: /images/20211211/40fd0ad61f824379bd421a24b6f36498.png