Mysql之EXPLAIN显示using filesort

时间:2023-11-25 18:12:44

索引使用经验:

1. 一条 SQL 语句只能使用 1 个索引 (5.0-),MySQL 根据表的状态,选择一个它认为最好的索引用于优化查询

2. 联合索引,只能按从左到右的顺序依次使用

Using where; Using filesort

当EXPLAIN中的extra中出现Useing filesort时说明语句性能不好,需要优化。

Using filesort 是一种速度很慢的外部排序。

即使order by 后的字段加了索引,也可能出现Using filesort,因为有可能索引定义不当,MySQL 没有用到索引

例如:

explain SELECT * FROM table_item WHERE user_id = 2 ORDER BY item_id LIMIT 0, 5

user_id 和 item_id 是 2 个索引,MySQL执行这条语句时候 选择了 user_id,那么 item_id 的索引没有起到任何用处,所以,当我要排序的时候,由于记录数较多,内存中的排序 buffer 满了,只能 Using filesort 进行外部排序,因此每次查询要从磁盘读取几十 M 的数据,太慢了。
修改表结构,删除 user_id 和 item_id 的 INDEX 索引,建立一个名为 user_item 的联合 UNIQUE 索引,顺序是先 user_id 后 item_id,再 EXPLAIN,这回只有 Using where 了。
再刷新页面,观察任务管理器,mysqld-nt.exe 只读取了 2K 的数据,页面咔的一下就出来了…

Mysql之EXPLAIN显示using filesort

只有在order by 数据列的时候才可能会出现using filesort,而且如果你不对进行order by的这一列设置索引的话,无论列值是否有相同的都会出现using filesort。因此,只要用到order by 的这一列都应该为其建立一个索引。

例如:

SELECT * FROM DB.TB WHERE ID=2222 AND FID IN (9,8,3,13,38,40) ORDER BY INVERSE_DATE LIMIT 0, 5
里面建立的索引为一个三列的多列索引:IDX(ID,FID ,INVERSE_DATE) 。INVERSE_DATE这个是时间的反向索引。

对于这个sql我当时最开始认为应该是个优化好的状态,应该没有什么纰漏了,结果一explain才发现竟然出现了:Using where; Using filesort。

为什么呢,后来经过分析才得知,原来在多列索引在建立的时候是以B-树结构建立的,因此建立索引的时候是先建立ID的按顺序排的索引,在相同ID的情况下建立FID按 顺序排的索引,最后在FID 相同的情况下建立按INVERSE_DATE顺序排的索引,如果列数更多以此类推。有了这个理论依据我们可以看出在这个sql使用这个IDX索引的时候只是用在了order by之前,order by INVERSE_DATE 实际上是using filesort出来的。。汗死了。。因此如果我们要在优化一下这个sql就应该为它建立另一个索引IDX(ID,INVERSE_DATE),这样就消除了using filesort速度也会快很多。问题终于解决了。