order by 执行过程

先了解一下 order by 执行过程：

  1. 初始化 sort_buffer，查询所需的字段
  2. 从索引 create_at 找到满足条件的主键 id，回表查询出四个字段值存入 sort_buffer
  3. 从索引处继续查询满足 create_at 条件记录，继续执行步骤 2
  4. 对 sort_buffer 中的数据按照 create_at 排序
  5. 排序成功后取出符合 limit 条件的记录返回客户端

按照 create_at 排序可能在内存中完成，也可能需要使用外部排序，取决于排序所需的内存和参数 sort_buffer_size。

sort_buffer_size 是 mysql 为排序开辟的内存。如果排序数据量小于 sort_buffer_size，排序会在内存中完成。

如果数据量过大，内存放不下，则会利用磁盘临时文件排序。

order by 索引失效举例

offset 10000 时查看执行计划 key Extra 得知，没有使用磁盘临时文件排序；当把 offset 调整到 1000000 时查看执行计划，发现了 Using filesort。

Using filesort：表示在索引之外，需要额外进行外部的排序动作，性能必将受到严重影响

应该结合与之相对应的业务逻辑避免常规 limit offset。 

如果设计初期能够预料到库表的数据增长，理应构思合理的重构优化方式，比如 ES 配合查询、表分区、分库分表等解决方式。

关于深分页优化参考：MySQL深分页优化。