MySQL-Order by 与 Group by优化

目录

1、MySQL排序方式

2、实例SQL

3、常见排序CASE

3.1 case1

3.2 case2

3.3 case3

3.4 case4

3.5 case5

3.6 case6

3.7 case7

3.8 case8

4、优化总结

---

1、MySQL排序方式

需要知道前提知识(Extra列)Explain工具介绍

        MySQL支持两种方式的排序 filesort 和 index ，Using index是指MySQL扫描索引本身完成排序；using filesort外部排序，数据较小时从内存中排序，否则需要在磁盘中完成。index效率高，filesort效率低。

2、实例SQL

CREATE TABLE `employees` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(24) NOT NULL DEFAULT '' COMMENT '姓名',
  `age` int(11) NOT NULL DEFAULT '0' COMMENT '年龄',
  `position` varchar(20) NOT NULL DEFAULT '' COMMENT '职位',
  `hire_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入职时间',
  PRIMARY KEY (`id`),
  KEY `idx_name_age_position` (`name`,`age`,`position`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='员工记录表';

INSERT INTO `qxb`.`employees`(`id`, `name`, `age`, `position`, `hire_time`) VALUES (1, 'LiLei', 22, 'manager', '2022-09-19 15:19:37');
INSERT INTO `qxb`.`employees`(`id`, `name`, `age`, `position`, `hire_time`) VALUES (2, 'HanMeimei', 23, 'dev', '2022-09-19 15:19:37');
INSERT INTO `qxb`.`employees`(`id`, `name`, `age`, `position`, `hire_time`) VALUES (3, 'Lucy', 23, 'dev', '2022-09-19 15:19:37');

更具索引使用长度可以计算出索引中都用了那些复合索引列；

上面SQL中可以看出有一个idx_name_age_position 复合索引

如果用到了name字段，则长度为74；

用到了name + age 长度为78；

用到了name + age + position 长度为140；

3、常见排序CASE

3.1 case1

explain select * from employees where name = 'LiLei' and position = 'dev' order by age;

分析：

        利用最左前缀法则：中间字段不能断，因此查询用到了name索引，从key_len=74也能看出；age索引列用在排序过程中，因为Extra字段里没有using filesort。

3.2 case2

explain select * from employees where name = 'LiLei' order by position;

分析：

        从explain的执行结果来看：key_len=74，查询使用了name索引。由于用了position进行排序，跳过了age，出现了Using filesort，所以排序没有用到position这个索引列。

3.3 case3

explain select * from employees where name = 'LiLei' order by age,position;

 

分析：

        查找只用到索引name；age和position用于排序，无Using filesort。

3.4 case4

explain select * from employees where name = 'LiLei' order by position,age;

 

分析：

        和Case 3中explain的执行结果一样，但是出现了Using filesort，因为索引的创建顺序为name、age、position，但是排序的时候age和position颠倒位置了，所以age和position没有被用到索引排序中。

3.5 case5

explain select * from employees where name = 'LiLei' and age = 18 order by position,age;

 

分析：

        与Case 4对比，在Extra中并未出现Using filesort，因为age为常量，在排序中被优化，所以索引未颠倒，不会出现Using filesort。

3.6 case6

explain select * from employees where name = 'LiLei' order by age asc,position desc;

分析：

        虽然排序的字段列与索引顺序一样，且order by默认升序，这里position desc变成了降序，导致与索引的排序方式不同，从而产生Using filesort。Mysql8以上版本有降序索引可以支持该种查询方式。

3.7 case7

explain select * from employees where name in('LiLei','a') order by age,position;

分析：

        sql都没有走索引，索引排序也无法索引排序。

3.8 case8

explain select * from employees where name > 'a' order by age,position;

也没有走索引

4、优化总结

1、MySQL支持两种方式的排序filesort和index，Using index是指MySQL扫描索引本身完成排序。index效率高，filesort效率低。

2、order by满足两种情况会使用Using index。

        1) order by语句使用索引最左前列。

        2) 使用where子句与order by子句条件列组合满足索引最左前列。

3、尽量在索引列上完成排序，遵循索引建立（索引创建的顺序）时的最左前缀法则。

4、如果order by的条件不在索引列上，就会产生Using filesort。

5、能用覆盖索引尽量用覆盖索引

6、group by与order by很类似，其实质是先排序后分组，遵照索引创建顺序的最左前缀法则。对于groupby的优化如果不需要排序的可以加上order by null禁止排序。注意，where高于having，能写在where中的限定条件就不要去having限定了。