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

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

1.得到数据 利用 where 条件

2.处理数据

3.返回处理后的数据

比如语句：

select city,name,age from t where city='杭州' order by name limit 1000,100 ;

第一步：根据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字段不能走索引，那么应该怎么处理呢？

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

范围查询指的是使用了in，like "ab%",between and ,><的字段。

如果(a，b，c)是联合索引，where a='a' and b>'b' and c='c',那么c实际不会走索引。即联合索引中生效的只有a和b字段

参考：

https://www.cnblogs.com/silyvin/p/9251297.html