order by导致离线消息MySQL压力剧增

2019独角兽企业重金招聘Python工程师标准>>>

离线消息MySQL压力很大，大量的sql语句都在执行
select gmt_create, type, from_id, content from im_offlinemsg_200811 where to_id = 'cntaobao???????'and (expire_time is null or expire_time >now())
order by msg_id;
这样的操作。

但是从我们的性能统计来看，所有的order by都是在内存中执行的，没有在硬盘上执行。

将排序改到应用层排序，整个性能立马提高了很多。于是新的疑问就来了，难道mysql的排序性能这么差吗？

在http://forge.mysql.com/wiki/MySQL_Internals_Algorithms这里发现了mysql的排序算法原理。

mysql的filesort算法有两种，一种是最初的算法，在MySQL 4.1以前只有这种算法，另外一种是改进的filesort算法，它出现在
MySQL 4.1以后(blob和text类型的字段不能采用这种改进算法)

最初的算法流程如下(简单起见，假设数据小于等于sort_buffer_size，详细可以见上面的url)：
1.读取所有的满足条件的数据，只包含sort key和row pointer两种数据
2.在buffer中执行qsort排序
3.排完序后，再根据row pointer去读取相应的行数据

从中可以看出，每次排序都需要读两次表，而根据row pointer去读表往往都是随机离散读的，所有其开销非常大。

改进后的算法是：
1.读取所需要的数据，包含sort key,row pointer和查询所需要访问的字段
2.根据sort key排序
3.按排序后的顺序读取数据，由于sort_buffer_size中包含了所需要的字段，因此不需要再回表了，可以直接返回结果给客户端。

很明显，这种改进的方法对sort_buffer_size的需求也大大增加，所以为了防止性能下降，mysql增加了一个参数max_length_for_sort_data
当第一步中除了sort key以外的字段内容大于max_length_for_sort_data这个参数时，mysql将采用第一种排序算法。

该参数默认为1024个字节，而在我们的离线消息中内容较大，所以mysql就采用了第一种排序算法，结果每次都要回表，所以性能很差。
将max_length_for_sort_data该参数调大，性能得到了很大的提高。

人人都说mysql简单，其实在DB层面没有简单的事情，任何一个细小的东西里面都大有文章可讲啊。
另外http://forge.mysql.com/wiki 这个wiki上面的内容很不错，强烈推荐一下。

转载于:https://my.oschina.net/javaeye/blog/28496