MySQL filesort优化案例一则(一)

MySQL filesort优化案例一则

MySQL中filesort是什么意思？官方手册定义：

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 keys then are sorted and the rows are retrieved in sorted order。

一般来说如果有你的SQL查询语句中有order by且没有合适的索引时，通过EXPLAIN query可以在Extra列查看到Using filesort字样，当然一般来说此时也代表你需要去优化它了，无论是通过优化索引还是改变SQL查询实现方式。

先看表结构信息：

[sql] 
CREATE TABLE `tbxxxx` (  
  `id` int(10) unsigned NOT NULL auto_increment COMMENT '  id',  
  `a` varchar(20) default NULL COMMENT '  id',  
  `base62_id` varchar(10) default NULL COMMENT '  base62_id',  
  `userid` varchar(20) default NULL COMMENT '  id',  
  `category` int(5) unsigned default NULL COMMENT '    id',  
  `rate` decimal(10,2) NOT NULL default '0.00' COMMENT '  ',  
  `status` enum('Y','N') NOT NULL default 'Y' COMMENT '  ',  
  `releaseTime` datetime default NULL COMMENT '      ',  
  `createTime` datetime default NULL COMMENT '      ',  
  `content` text,  
  PRIMARY KEY  (`id`),  
  UNIQUE KEY `a` (`a`),  
  KEY `releaseTime` (`releaseTime`),  
  KEY `rate` (`rate`),  
  KEY `crr` (`category`,`rate`,`releaseTime`),  
  KEY `idx_c_r_rate` (`category`,`releaseTime`,`rate`),  
  KEY `idx_status_rt_rate` (`status`,`releaseTime`,`rate`),  
  KEY `idx_status_rate_release` (`status`,`rate`,`releaseTime`)  
) ENGINE=MyISAM AUTO_INCREMENT=3346255 DEFAULT CHARSET=utf8 COMMENT='     '  
1 row in set (0.15 sec)  

ps:上面表结构中有些索引并不是线上实际存在的，这是做测试用临时添加的。

然后业务有如下查询：

[sql] 
SELECT a, content FROM tbxxxxx  WHERE `status`='Y' and releaseTime > '2013-07-08 11:00:00' ORDER BY rate DESC LIMIT 0, 10  

对于这类型的查询可能第一反应是建立一个(status, releaseTime, rate)的复合索引， 然后通过EXPLAIN发现优化器也是这么选择的：

[html] 
explain SELECT a, content FROM tbxxxxx  WHERE `status`='Y' and releaseTime > '2013-07-08 11:00:00' ORDER BY rate DESC LIMIT 0, 10;  
  
+----+-------------+-----------+------+--------------------------------------------------------+--------------------+---------+-------+--------+-----------------------------+  
| id | select_type | table     | type | possible_keys                                          | key                | key_len | ref   | rows   | Extra                       |  
+----+-------------+-----------+------+--------------------------------------------------------+--------------------+---------+-------+--------+-----------------------------+  
|  1 | SIMPLE      | tbxxxxx | ref  | releaseTime,idx_status_rt_rate,idx_status_rate_release | idx_status_rt_rate | 1       | const | 531837 | Using where; Using filesort |   
+----+-------------+-----------+------+--------------------------------------------------------+--------------------+---------+-------+--------+-----------------------------+  
1 row in set (0.15 sec)  

上面的key_len=1是能理解的，因为MySQL 5.6以前没有ICP，所releaseTime这种范围查询是无法利用索引。status占一个字节。但是从Extra中我们可以发现由于order by rate导致了filesort。那么这个索引该怎么调整才能避免filesort呢？其实在一刚开始没想到得一定去优化这个filesort的开销（后来证明这个案例中得filesort占了99%的开销），而是想着怎么去优化扫描的行数，使筛选的得到的行更少，这样可以减少回表带来的开销。但是通过打开profile之后，发现了问题最关键的地方：

上面的图标可以显示结果集排序占了绝大部分的时间开销。那么此时问题就变成了该怎么来优化掉这个可恶的filesort，于是添加了一个(status, rate, releaseTime)字段的索引，测试发现优化器依然默认会走(status, releaseTime, rate)这个复合索引， 于是只能通过用use index()的语法来强制走目标索引:

[sql] 
explain SELECT a, content FROM tbxxxxx use inde