【mysql】---group/order by、文件排序

一、group by关键字

Group by实质还是先排序后再分组，遵循索引的左前缀。
(如果没合适的索引)先扫描整个表并创建一个新的临时表，然后按照group by指定的列进行排序，这样临时表的每个组的所有行是连续的，然后使用该临时表来找到组并执行聚合函数(分组)。

1.1、group by实际例子

t_content表的索引如下
PRIMARY KEY (`id`),
  KEY `idx_content_time` (`create_time`) USING BTREE,
  KEY `idx_content_status` (`status`) USING BTREE,
  KEY `idx_content_user_id` (`user_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='内容';

[SQL]
SELECT * from t_content GROUP BY sort;
受影响的行: 0
时间: 45.078s

-----按照sort排序，但sort没索引，所以使用了“Using temporary; Using filesort”。这是创建临时表和使用fileSort排序。
[SQL]
SELECT * from t_hg_content GROUP BY status;
受影响的行: 0
时间: 9.478s

-----按照status排序，status有索引，使用的是索引排序。

1.2、group by查询慢的原因

分组字段不在同一个表中；
分组字段没有建索引；
分组字段导致索引没有起作用；
分组字段中使用聚合函数导致索引不起作用。

1.3、松散索引和紧凑索引

CREATE TABLE `t_hg_p2` (
  `id` bigint(11) NOT NULL COMMENT '序号',
  `a` varchar(12) DEFAULT NULL COMMENT 'a',
  `b` varchar(12) DEFAULT NULL COMMENT 'b',
  `c` varchar(12) DEFAULT NULL COMMENT 'c',
  `d` bigint(11) NOT NULL COMMENT 'd',
  PRIMARY KEY (`id`),
  KEY `idx_a_b_c` (`a`,`b`,`c`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='t_hg_p2表';

1.3.1、松散索引

当MySQL 完全利用索引扫描来实现GROUP BY 的时候，并不需要扫描所有满足条件的索引键即可完成操作得出结果。
利用松散索引扫描实现group by，至少满足下面条件：
A：Group by条件字段必须在同一个索引中饭最前面的连续位置；
在使用GROUP BY 的同时，只能使用MAX 和MIN 这两个聚合函数【新版本支持更多】；
B：如果引用到了该索引中GROUP BY 条件之外的字段条件的时候，必须以常量形式存在；

如：
SELECT a,b from t_hg_p2 GROUP BY a,b;

-----只是用到了联合索引的一个连续部分。

1.3.2、紧凑索引

在扫描索引的时候，读取所有满足条件的索引键，然后再根据读取的数据来完成GROUP BY 操作得到相应结果。【完整的使用联合索引】
SELECT a,c from t_hg_p2 where b=‘1212’ GROUP BY a,c;

—group by中虽然有差距，但where条件有b=’1212’覆盖，且又满足“最左前缀”原则，使用紧凑索引。
SELECT a,c from t_hg_p2 where a=‘1212’ GROUP BY b,c;

----group by不是以a开始，但where条件提供了a的常量，所以满足“最左前缀”原则，使用紧凑索引。

1.3.3、为什么松散索引效率高

因为在没有WHERE 子句，也就是必须经过全索引扫描的时候， 松散索引扫描需要读取的键值数量与分组的组数量一样多，也就是说比实际存在的键值数目要少很多。而在WHERE 子句包含范围判断式或者等值表达式的时候， 松散索引扫描查找满足范围条件的每个组的第1 个关键字，并且再次读取尽可能最少数量的关键字。

二、order by关键字

2.1、order by实际例子

看具体的例子：

CREATE TABLE `t_hg_p2` (
  `id` bigint(11) NOT NULL COMMENT '序号',
  `a` varchar(12) DEFAULT NULL COMMENT 'a',
  `b` varchar(12) DEFAULT NULL COMMENT 'b',
  `c` varchar(12) DEFAULT NULL COMMENT 'c',
   `d` bigint(11) NOT NULL COMMENT 'd',
  PRIMARY KEY (`id`),
  KEY `idx_a_b_c` (`a`,`b`,`c`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='t_hg_p2表';

—下面使用了索引最左原则
SELECT * from t_hg_p2 ORDER BY a;
SELECT * from t_hg_p2 ORDER BY a,b;
SELECT * from t_hg_p2 ORDER BY a,b,c;
SELECT * from t_hg_p2 ORDER BY a desc,b desc,c desc;

—使用了有序索引排序
SELECT * from t_hg_p2 where a=‘1’ ORDER BY b,c;
—使用了有序索引排序
SELECT * from t_hg_p2 where a=‘1’ and b=‘22’ ORDER BY c;
—使用了有序索引排序，后面order by将b和c承接起来了
SELECT * from t_hg_p2 where a=‘1’ and b>‘22’ ORDER BY b,c;
—b断了，使用了fileSort排序
SELECT * from t_hg_p2 where a=‘1’ and b>‘22’ ORDER BY c;

----不能使用索引进行排序
SELECT * from t_hg_p2 ORDER BY a asc,b desc,c desc; —只用到a索引
SELECT * from t_hg_p2 where d=100 ORDER BY b,c; —没用索引
SELECT * from t_hg_p2 where a=‘22’ ORDER BY c; ----只用到了a索引，丢失b索引
SELECT * from t_hg_p2 where a in(‘22’,‘433’) ORDER BY b,c; ----in也是范围查询

三、文件排序(fileSort排序)

如果没使用索引，那么Mysql会采用fileSort排序。fileSort排序有两种排序：单路排序和双路排序。

单路排序
从磁盘读取查询需要的所有列，按照order by列在buffer对它们进行排序，然后扫描排序后的列表进行输出，它的效率更快一些，避免了第二次读取数据。并且把随机IO变成了顺序IO,但是它会使用更多的空间，因为它把每一行都保存在内存中了。
双路排序
两次扫描磁盘，最终得到数据，读取行指针和order by列，对他们进行排序，然后扫描已经排序好的列表，按照列表中的值重新从列表中读取对应的数据输出。

3.1、单路排序和双路排序选择

加大max_length_for_sort_data参数设置
如果希望 ORDER BY 操作的效率尽可能的高，一定要注意max_length_for_sort_data 参数的设置。如果 max_length_for_sort_data更大，则使用单路排序优化后的算法，反之使用双路排序算法。
原因：在sort_buffer中，单路排序比双路排序要多占用很多空间，因为单路排序是把所有字段都取出, 所以有可能取出的数据的总大小超出了sort_buffer的容量，导致每次只能取sort_buffer容量大小的数据，进行排序（创建tmp文件，多路合并），排完再取取sort_buffer容量大小，再排……从而多次I/O。
去掉不必要的返回字段
如果内存不充裕，且参数设置也难，那么多余的返回字段会造成内存不足，造成mysql不得不将数据分成很多段，然后排序。
增大sort_buffer_size参数设置
为了让MySQL尽量减少在排序过程中对须要排序的数据进行分段，因为分段会造成 MySQL 不得不使用临时表来进行交换排序。

*-------因此，order by 和select 配合是不好的。

3.2、什么时候发生fileSort排序

t_content表的索引如下

PRIMARY KEY (`id`),
  KEY `idx_content_time` (`create_time`) USING BTREE,
  KEY `idx_content_status` (`status`) USING BTREE,
  KEY `idx_content_user_id` (`user_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='内容';

查询索引和order by的字段不是同一个字段。
例如： SELECT * from t_hg_p2 where d>100 ORDER BY a,b,c;

[SQL]
SELECT * from t_content order BY sort;
受影响的行: 0
时间: 45.078s

-----按照sort排序，但sort没索引，所以使用了“Using temporary; Using filesort”。这是创建临时表和使用fileSort排序。

3.3、什么时候发生索引排序

查询索引和order by的字段是同一个字段。
[SQL]
SELECT * from t_hg_content order BY status;
受影响的行: 0
时间: 9.478s

-----按照status排序，status有索引，使用的是索引排序。