第十章：第8节 MySQL进阶篇——order by 与索引
更新于：2023-08-12 15:13:14
从数据库里获取多行数据，这些数据都是有默认的排序规则的。根据前面所学，InnoDB表主键使用的是聚集索引，InnoDB表里的数据都是按照主键排序的。select时如果没有order by子句，获取的数据都是按照主键排序的。
“user_info”是一个InnoDB表。
楠神把ID为8的记录主键ID改成233，因为InnoDB的主键是聚集索引，修改主键了就会重新排序。
“user_info2”是一个MyISAM表，同样的数据。
MyISAM表的主键不是聚集索引，数据是按照插入顺序（或者数据的物理地址顺序）排序的，修改了主键并不影响它原先的排序。所以说，MyISAM表不修改主键数据，看似也是按主键排序。
从MySQL获取数据时，如果对排序没有要求时，我们直接用默认的排序。可实际应用中，经常还有排序的需求。比如网上商城检索功能，会对搜到的商品按时间、价格、人气、销量排序。那就要求我们select语句中必须有order by子句了。
排序是很费资源的，假如数据量庞大且无序，排序时间会占去很多，直接影响数据的获取效率。排序操作一般都是在内存里进行的，对于数据库来说是一种CPU的消耗，由于现在CPU的性能增强，对于普通的几十条或上百条记录排序对系统的影响也不会很大。但是当查询的记录集增加到上百万条以上时，那就需要注意是否一定要这么做了。大记录集排序不仅增加了CPU开销，而且可能会由于内存不足发生硬盘排序的现象，当发生硬盘排序时性能会急剧下降。
所以，select语句中order by子句要尽量用到索引，避免Extra中出现“using filesort”情况。
还是“test_tab”表
虽然组合索引“invest_uid”的长度是5，看似只用到了“invest_uid”、“status”两列，但“Extra”没有出现“Using filesort”，这说明“order by repayment_time”也用到了组合索引“invest_uid”。
where条件中去掉“status”，因为违背了组合索引的“最左前缀匹配”，“order by repayment_time”就用不到组合索引“invest_uid”了。
把
KEY `invest_uid` (`invest_uid`,`status`,`repayment_time`)
改成
KEY `invest_uid` (`invest_uid`,`repayment_time`)
order by repayment_time又能用到索引了。
要让order by使用索引，通常需要建聚集索引和组合索引。
上面用的是组合索引，order by是可以用到索引的。我们继续往下讨论：
看上图，两张表“user_info”是InnoDB表，order by使用了主键索引（聚集索引）。“user_info2”是MyISAM表，虽然也是按主键排序，order by却没有使用主键索引。再看这条SQL语句：
“invest_id”在“test_tab”表中建了普通索引，order by排序时也没有用到索引。这到底为什么呢？
其实，order by排序用到索引是有前提条件的。分以下三种情况：
【1、order by 字段，字段已建聚集索引。】
【2、select只查询索引字段，order by 索引字段会用到索引，要不然就是全表排列。】
select invest_id from test_tab;
上面的select语句，虽然没有where子句，只查询“invest_id”字段时是可以用到“invest_id”索引的（此时用的就是覆盖索引），后面加上order by子句，它也可以用到“invest_id”索引。
【3、order by 字段出现在where条件中时，order by会利用索引。】
order by用到索引了。不过像这种范围查询MySQL很容易不使用索引的，假如where子句中没有使用索引，那order by子句也不会使用索引。
根据楠神的经验，有排序需求的时候，where子句中通常会有其他字段作为条件的，这几个字段比较适合建一个组合索引。
分析：为什么只有order by 字段出现在where条件中时,才会利用到该字段的索引？
这要说到数据库如何取到我们需要的数据了。
一条SQL实际上可以分为三步：
第一步：根据where条件和统计信息生成执行计划，得到数据。
第二步：将得到的数据排序。
当执行处理数据（order by）时，数据库会先查看第一步的执行计划，看order by 的字段是否在执行计划中利用了索引。如果是，则可以利用索引顺序而直接取得已经排好序的数据。如果不是，则排序操作。
第三步：返回排序后的数据。
额外了解：
当MySQL不能使用索引进行排序时，就会利用自己的排序算法(快速排序算法)在内存(sort buffer)中对数据进行排序，如果内存装载不下，它会将磁盘上的数据进行分块，再对各个数据块进行排序，然后将各个块合并成有序的结果集（实际上就是外排序）。对于filesort，MySQL有两种排序算法。
(1) 两遍扫描算法(Two passes)
实现方式是先将须要排序的字段和可以直接定位到相关行数据的指针信息取出，然后在设定的内存（通过参数sort_buffer_size设定）中进行排序，完成排序之后再次通过行指针信息取出所需的Columns。
注：该算法是4.1之前采用的算法，它需要两次访问数据，尤其是第二次读取操作会导致大量的随机I/O操作。另一方面，内存开销较小。
(2) 一次扫描算法(single pass)
该算法一次性将所需的Columns全部取出，在内存中排序后直接将结果输出。
注：从 MySQL 4.1 版本开始使用该算法。它减少了I/O的次数，效率较高，但是内存开销也较大。如果我们将并不需要的Columns也取出来，就会极大地浪费排序过程所需要的内存。
在 MySQL 4.1 之后的版本中，可以通过设置 max_length_for_sort_data 参数来控制 MySQL 选择第一种排序算法还是第二种。当取出的所有字段总大小大于 max_length_for_sort_data 的设置时，MySQL 就会选择使用第一种排序算法，反之，则会选择第二种。
为了尽可能地提高排序性能，我们自然更希望使用第二种排序算法，所以在 Query 中仅仅取出需要的 Columns 是非常有必要的。
继续拓展知识：
Innodb表的数据是按照聚簇索引的顺序排列的，在Innodb表建一个普通单列索引，普通索引下是有可能存在重复的key。
看上图：invest_id是普通单列索引，有三个值，使用主键排序，order by使用到了索引。
从这个示例中我们可以获取到一个信息，那就是在普通的索引中相同的key下数据是按照主键排序的。
如果是普通的组合索引呢？同样适用，前提是组合索引中的字段要全部被用到。
来自其他地方的示例：
KEY `borrow_id` (`borrow_id`,`status`)
MyISAM表普通索引中相同的key下数据是按照物理插入顺序排序的，通过普通索引获取的数据，order by主键排序使用不到索引。