MySQL filesort优化案例一则

最新推荐文章于 2024-08-29 18:06:26 发布

转载最新推荐文章于 2024-08-29 18:06:26 发布 · 117 阅读

文章标签：

#数据库

本文通过一个具体的案例探讨了MySQL中的filesort现象及其优化方法。通过对SQL查询进行结构调整和索引优化，成功避免了额外的排序操作，显著提高了查询效率。

今天遇到一个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查询实现方式。

先看表结构信息：

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:上面表结构中有些索引并不是线上实际存在的，这是做测试用临时添加的。

然后业务有如下查询：

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发现优化器也是这么选择的：

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()的语法来强制走目标索引:

explain SELECT a, content FROM tbxxxxx use index(idx_status_rate_release) 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      | tbxxxxxx | ref  | idx_status_rate_release | idx_status_rate_release | 1       | const | 1057094 | Using where | 
+----+-------------+-----------+------+-------------------------+-------------------------+---------+-------+---------+-------------+
1 row in set (0.00 sec)

从执行计划可以看出，此时没有了filesort这个阶段，因为默认status, rate这个索引前缀就是按照rate来排序的，因此正好可以利用索引的数据有序来达到最终的order by rate效果。细心的朋友可能会发现在这个执行计划中估算扫描的行数是上一个执行计划的2倍，多扫描这么多行开销不大吗（这也是为什么当初第一反应优化这条SQL时会考虑优化索引来减少扫描的行数）？事实上证明这个开销相对于filesort来说已经很小，有两点可以证明：profile的分析显示99%的时间是耗在sorting result，第二，在线上一个从库上测试第二个执行计划查询时间为0.1s左右，而第一个执行计划的查询时间在3s左右。虽然说一般来说并不建议使用use index这种语法，后期的不确定性较大，但是既然提供这种语法就有它自己的理由，用好了自然有优势，先这么将就着吧。另外这里需要说明的是：(status, rate, releaseTime)这个索引中只有rate是必须得， status由于只有两个取值，筛选效果很不明显， releaseTime用不到（ICP后可能能用到）

最后是两点小感悟：1.MySQL的优化器还是那么有点坑爹，有时候不靠谱。2.有时候优化一个SQL还是不能完全凭直觉，平常的经验，profile这种命令可以完全将一个SQL查询执行过程中各个阶段的开销都统计出来，这样我们就有了针对一个SQL的优化关键点，这样才能做不到不盲目，从而高效快速的优化。