mysql order/group by过程解析，排序与索引，联合索引中的范围查询，大量数据带条件的分页

素颜马尾好姑娘i 2022-04-05 12:18 420阅读 0赞

一条SQL的执行实际上可以分为三步。

1.得到数据 利用 where 条件

2.处理数据

3.返回处理后的数据

比如语句：

<table> 
 <tbody> 
  <tr> 
   <th> <p>select city,name,age from t where city='杭州' order by name limit 1000,100&nbsp;;</p> </th> 
  </tr> 
 </tbody> 
</table>

第一步：根据where条件和统计信息生成执行计划，得到数据。

第二步：将得到的数据排序。

当执行处理数据（order by）时，数据库会先查看第一步的执行计划，看order by 的字段是否在执行计划中利用了索引。如果是，则可以利用索引顺序而直接取得已经排好序的数据。如果不是，则排序操作。

第三步：返回排序后的数据。

实际上第二步中mysql的排序算法一共存在两种：

*  全字段排序：获取select的全部字段放入内存，并且利用内存和磁盘根据name进行排序，然后从排好序的结果中获取指定的条数。如果查询要返回的字段很多的话，那么内存里面要放的字段数太多，这样内存里能够同时放下的行数很少，要分成很多个临时文件，排序的性能会很差。
 *  rowid排序：逐行获取满足条件的记录，但是此时仅仅获取id和name到内存中，并且进行排序，先获取满足条件记录对应的id，然后再根据id获取对应记录的city,name,age等具体信息；这种方式避免了大量数据在内存中排序的问题，但需要两次查表。

全字段排序: 不需要回表，可以减少磁盘的访问 innodb表首选。  
rowid排序: 需要回表，所以会增加磁盘的访问量，内存中的临时表首选，因为无需回表，直接从内存中取。  
强制使用rowid排序的方法：SET max\_length\_for\_sort\_data = 比select字段的定义总长度小;max\_length\_for\_sort\_data，是 MySQL 中专门控制用于排序的行数据的长度的一个参数。它的意思是，如果单行的长度超过这个值，MySQL 就认为单行太大，会使用rowid排序。

但是无论是上面那种方式，在内存中排序总是不好的，会占用内存，影响性能，而且速度比较慢，比较好的方式是避免mysql的内存排序，让order by的字段使用索引即可。

### 查询mysql中一条排序语句的排序方案的方法和参数解析 ###

/\* 打开 optimizer\_trace，只对本线程有效 \*/  
SET optimizer\_trace='enabled=on';  
/\* @a 保存 Innodb\_rows\_read 的初始值 \*/  
select VARIABLE\_VALUE into @a from performance\_schema.session\_status where variable\_name = 'Innodb\_rows\_read';

/\* 执行语句 \*/  
select \* from dau\_baseinfo order by complete\_time limit 1000;

/\* 查看 OPTIMIZER\_TRACE 输出 \*/  
\#注意 \\G用来将结果旋转90度，方便展示查询的结果，只能在mysql的命令行中使用，在navicat中会识别不了  
SELECT \* FROM \`information\_schema\`.\`OPTIMIZER\_TRACE\` \\G;

/\* @b 保存 Innodb\_rows\_read 的当前值 \*/  
select VARIABLE\_VALUE into @b from performance\_schema.session\_status where variable\_name = 'Innodb\_rows\_read';

/\* 计算 Innodb\_rows\_read 差值 \*/  
select @b-@a;

SELECT \* FROM \`information\_schema\`.\`OPTIMIZER\_TRACE\` \\G;执行结果的尾部一部分内容如下：  
"filesort\_execution": \[  
            \],  
            "filesort\_summary": \{  
              "rows": 1001,  
              "examined\_rows": 36478,  
              "number\_of\_tmp\_files": 0,  
              "sort\_buffer\_size": 124128,  
              "sort\_mode": "<sort\_key, additional\_fields>"  
            \}  
          \}  
number\_of\_tmp\_files 中看到是否使用了临时文件。  
number\_of\_tmp\_files 表示的是，排序过程中使用的临时文件数。你一定奇怪，为什么需要 12 个文件？内存放不下时，就需要使用外部排序，外部排序一般使用归并排序算法。可以这么简单理解，MySQL将需要排序的数据分成 12 份，每一份单独排序后存在这些临时文件中。然后把这 12 个有序文件再合并成一个有序的大文件。如果 sort\_buffer\_size 超过了需要排序的数据量的大小，number\_of\_tmp\_files 就是 0，表示排序可以直接在内存中完成。否则就需要放在临时文件中排序。sort\_buffer\_size 越小，需要分成的份数越多，number\_of\_tmp\_files 的值就越大。

examined\_rows=4000，表示参与排序的行数是 4000 行。  
sort\_mode 里面的 packed\_additional\_fields 的意思是，排序过程对字符串做了“紧凑”处理。即使 name 字段的定义是 varchar(16)，在排序过程中还是要按照实际长度来分配空间的。sort\_mode 变成了 <sort\_key, rowid>，表示参与排序的只有 name 和 id 这两个字段。

临时表--内存表和磁盘临时表的选择  
tmp\_table\_size 这个配置限制了内存临时表的大小，默认值是 16M。如果临时表大小超过了 tmp\_table\_size，那么内存临时表就会转成磁盘临时表。磁盘临时表使用的引擎默认是 InnoDB，是由参数 internal\_tmp\_disk\_storage\_engine 控制的。当使用磁盘临时表的时候，对应的就是一个没有显式索引的 InnoDB 表的排序过程。

排序方式  
归并排序和优先队列排序算法  
是否使用临时文件：sort\_buffer\_size，排序数据量的最大值，单位字节；如果排序的数据量小于sort\_buffer\_size，则会直接在内存中排序；否则只能使用磁盘临时文件来进行排序  
number\_of\_tmp\_files 表示的是，排序过程中使用的临时文件数。内存放不下时，就需要使用外部排序，外部排序一般使用归并排序算法。可以这么简单理解，MySQL 将需要排序的数据分成 多 份，每一份单独排序后存在这些临时文件中。然后把这 多个有序文件再合并成一个有序的大文件。  
如果 sort\_buffer\_size 超过了需要排序的数据量的大小，number\_of\_tmp\_files 就是 0，表示排序可以直接在内存中完成。此时归并还是优先队列排序不一定，如果你需要所有的顺序，肯定是归并，如下面的sql：  
select word from words order by rand() limit 1000;  
如果是仅仅要其中的三条，则会使用优先队列排序的方式。如下sql：  
select word from words order by rand() limit 3;

优先队列排序算法（假设1000行记录里面取最小的三个值）

*  对于这 10000 个准备排序的 (R,rowid)，先取前三行，构造成一个堆；（对数据结构印象模糊的同学，可以先设想成这是一个由三个元素组成的数组）
 *  取下一个行 (R’,rowid’)，跟当前堆里面最大的 R 比较，如果 R’小于 R，把这个 (R,rowid) 从堆中去掉，换成 (R’,rowid’)；
 *  重复第 2 步，直到第 10000 个 (R’,rowid’) 完成比较。

归并排序算法：

*  同时遍历多个文件的记录，取最大的那个文件的记录，然后指针后移，继续遍历，直到遍历完全。

### Order by子句使用索引： ###

1、ORDER BY的索引优化。如果一个SQL语句形如：

SELECT \[column1\],\[column2\],…. FROM \[TABLE\] ORDER BY \[sort\];

在\[sort\]这个栏位上建立索引就可以实现利用索引进行order by 优化。

2、WHERE + ORDER BY的索引优化，形如：

SELECT \[column1\],\[column2\],…. FROM \[TABLE\] WHERE \[columnX\] = \[value\] ORDER BY \[sort\];

建立一个联合索引(columnX,sort)来实现order by 优化。

注意：如果columnX对应多个值，如下面语句就无法利用索引来实现order by的优化，但是columnX走索引

SELECT \[column1\],\[column2\],…. FROM \[TABLE\] WHERE \[columnX\] IN (\[value1\],\[value2\],…) ORDER BY\[sort\];

3、WHERE+ 多个字段ORDER BY

SELECT \* FROM \[table\] WHERE uid=1 ORDER x,y LIMIT 0,10;

建立索引(uid,x,y)实现order by的优化,比建立(x,y,uid)索引效果要好得多。

下面语句应该怎么建索引？

select a,b,c,d from tableA where a='122' and b ='33' and c>12 order by d

select \* from tableA where (a='122' || b ='33')  and c>12 order by d

1order by子句如果在合适的联合索引中，则整体会走联合索引

explain select id,d,c,e from real\_time\_test  where b='100000563699' order by   d

针对以上sql，如果有联合索引b，d和单独的索引b；则此时会优先走该联合索引

2 针对 a='122' and b ='33'，如果对 a和b分别建立索引则 仅仅会走a索引或b索引；如果以a,b顺序建立联合索引，则会走联合索引，性能更高

为什么a和b分别建立索引时不能2个索引都用上，可能很多人也不知道为什么，其实道理很简单，每个索引在数据库中都是一个索引树，其数据节点存储了指向实际

数据的指针，如果用一个索引来查询，其原理就是从索引树上去检索，并获得这些指针，然后去取出数据，试想，如果你通过一个索引，得到过滤后的指针，这时，你的另一个条件索引如果再过滤一遍，将得到2组指针的集合，如果这时候取交集，未必就很快，因为如果每个集合都很大的话，取交集的时候，等于扫描两个集合，效率会很低，所以没法用2个索引。

但是 如果是 a='122' || b ='33'，在大数据量情形下，如果对 a和b分别建立索引，会走两个索引

3区间查询是否会走索引？

一般会走索引，但是，如果是（a,b,c）联合索引，条件如 a='a' and b>'b' order by c,那么此时仅仅a,b字段走索引，c字段不会走索引。

4order by子句是否会走索引？

a）当order by 字段出现在where条件中时，会利用索引而无需排序操作。

b）当where条件+orderby刚好是联合索引时，此时会走联合索引，order by也会走索引

c）当where条件+orderby虽然是联合索引时，但是条件时范围的时候，此时只有范围会走联合索引，order by不走索引

下面分析上面两条sql建立索引的建议：

首先因为c是范围查询，所以不要再对d单独建立索引，即使是联合索引，也不应该包括d；

然后第二条语句建议对a，b分别建立索引比较合适，而且这样第一条语句也是可以走索引的，所以建议对ab分别建立索引。

虽然对order by增加字段才是正确的方式，但是如果where中查询条件和情形众多，或者因为别的原因order by字段不能走索引，那么应该怎么处理呢？

### 联合索引对范围查询的支持 ###