目录

  • 前言
  • 利用联合索引
  • 关注where 条件命中索引
  • 非必要情况，不要 asc desc 混用
  • 文件排序 filesort
  • 促进 filesort 的全局配置


前言

order by 优化的方向：

  1. 尽可能让查询优化器选择仅走索引就能完成。
  2. 如果不能通过索引能完成排序，则会使用到文件排序(filesort) ，如果文件大小合适，可以进行内存级别的filesort。
  3. 数据量大到一定级别，内存级别的 filesort 不够用，需要磁盘配合进行内存交换，这种排序的速度也是很慢的，可以重点优化。
  4. 最差的情况下是连文件排序(filesort)都不使用，是优化的重点对象。

官网 ：order by 优化

利用联合索引

SELECT * FROM t1
  ORDER BY key_part1, key_part2;
  • 以上语句的索引失效隐患
    select * 表示除了全表扫描，会存在回表的现象，回表次数多了，效率会降低。当查询优化器觉得多次回表还不如全表扫描的时候，则会让查询走全表扫描。联合索引的优势就被浪费掉了
SELECT pk, key_part1, key_part2 FROM t1
  ORDER BY key_part1, key_part2;
  • 尽可能使用
SELECT  key_part1, key_part2 FROM t1
  ORDER BY key_part1, key_part2;

以上两个语句都可以利用联合索引，（id也是联合索引的一部分，也可以避免回表）

并不是说 select *一定会使索引失效，下文将讲使用select * 的情况下尽量让排序走索引

关注where 条件命中索引

  • 等值命中联合索引的一部分
SELECT * FROM t1
  WHERE key_part1 = constant
  ORDER BY key_part2;
  • 范围命中
SELECT * FROM t1
  WHERE key_part1 > constant
  ORDER BY key_part1 

SELECT * FROM t1
  WHERE key_part1 = constant1 AND key_part2 > constant2
  ORDER BY key_part2;

范围命中，由查询优化器觉得范围查询 + 部分联合索引的效率是否高于全表扫描。增大了走索引的可能性

  • 使用不同索引进行排序，对查询只有少部分增益
    区别与联合索引 （key_part1 , key part2），如果建立key1 和 key2 两个独立列的索引。由于两个索引在不同的b+树上，以下的排序不能用 b+ 树完成，但是可以用b+ 树完成部分数据的筛选，从而进行filesort。
SELECT * FROM t1 ORDER BY key1, key2;

非必要情况，不要 asc desc 混用

In some cases, MySQL cannot use indexes to resolve the ORDER BY, although it may still use indexes to find the rows that match the WHERE clause. Examples:

  • The query mixes ASC and DESC:
SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 ASC;

官方提醒我们不要混用 asc 和 desc

  • 值得注意的是 order by last_update_time , name desc 会被翻译成 order by last_update_time asc, name desc , 这是个潜在的混用场景。

文件排序 filesort

  • 官方鼓励我们让order by 走索引，如果没法走，也争取走 filesort。换言之，可以把Using temporary 优化成 Using filesort
  • Using temporary 的情况列举为：

促进 filesort 的全局配置

  • max_length_for_sort_data
    这个配置的意思是，超过这个length，mysql就乐于用内存/磁盘 参与到排序中。
    减少这个值的副作用是，可能会增大整个mysql的内存活动率
  • sort_buffer_size
    调大这个值，可以减少内存交换的开销，让足够多的数据都往内存放。

网上查到的说法是 filesort 用的是快排，这个参数调大，间接让 partition 每次merge都有较宽裕的空间

  • read_rnd_buffer_size
    调大这个值，单次读入内存的行数会增多，间接加速 filesort