MySQL分区表SELECT查询处理逻辑及排序机制问询

咱们先从非分区表的情况说起，帮你建立参照对比：
如果是一张非分区表my_table，并且在a列上建了单列索引idx_a，当你执行下面这个查询时：

SELECT *  
FROM my_table  
ORDER BY a DESC  
LIMIT 100;

查询优化器会直接利用idx_a索引的有序性——从索引的末尾（也就是a值最大的一端）开始扫描，只要读到100行就停止，完全不用管表总共有多少数据，效率很高。

接下来看你提到的分区表场景：假设my_partition_table是按主键分区的，同样执行这个查询：

SELECT *  
FROM my_partition_table  
ORDER BY a DESC  
LIMIT 100;

你已经通过EXPLAIN确认了没有用到文件排序，但因为a列的数据分散在所有分区里，肯定好奇MySQL是怎么处理排序和取数的对吧？

其实MySQL在这里会用分区级有序扫描+内存堆合并的思路来处理，具体步骤大概是这样的：

• 首先，MySQL会对每个分区单独处理：如果每个分区里a列有可用的索引（比如和非分区表一样的idx_a），它会在每个分区里从索引末尾开始扫描，先取出一批符合ORDER BY a DESC的行（不会只取100，因为要跨分区比较大小）；如果分区里没有对应索引，那每个分区内部会先完成排序，得到分区内的有序数据集。
• 然后，MySQL会把所有分区返回的有序数据集，放到一个**优先级队列（堆结构）**里进行合并。这个堆会始终维护当前已读取数据中a值最大的前N个（这里N就是LIMIT指定的100）。
• 最后，当堆里的元素达到100个，或者所有分区的相关数据都扫描、比较完之后，就可以直接取出堆里的100行作为结果返回了。

这里还有几个需要注意的点：

• 虽然没有用到文件排序（filesort），但排序工作其实是分散到了每个分区内部，再加上内存里的堆合并，整体开销比非分区表要高一些，但远优于全表扫描后再全局排序。
• 如果你的分区键和a列完全无关，MySQL没办法跳过任何分区，必须遍历所有分区来获取数据进行合并。
• 如果a列和分区键有一定关联（比如分区键是范围型，a列也是有序的），MySQL可能会优化分区的扫描顺序，但本质还是要处理所有涉及的分区。

备注：内容来源于stack exchange，提问作者bbang