MySQL - order by和 group by 优化初探

短命女 2023-03-04 11:27 60阅读 0赞

### 文章目录 ###

*  生猛干货
 *  DB Version
 *  Table
 *  数据量
 *  案例一 ：explain select \* from employees where name = 'LiLei' and position = 'dev' order by age
 *  案例二： explain select \* from employees where name = 'LiLei' order by position
 *  案例三：explain select \* from employees where name = 'LiLei' order by age , position
 *  案例四：explain select \* from employees where name = 'LiLei' order by position , age
 *  案例五：explain select \* from employees where name = 'LiLei' and age = 18 order by position , age ;
 *  案例六：explain select \* from employees where name = 'LiLei' order by age asc , position desc ;
 *  案例七：explain select \* from employees where name in ('HanMeiMei' , 'LiLei') order by age , position ;
 *  案例八： explain select \* from employees where name > 'HanMeiMei' order by name ;
 *  group by 优化
 *  小结
 *  搞定MySQL

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lhbmdzaGFuZ3dlaQ_size_16_color_FFFFFF_t_70]

--------------------

# 生猛干货 #

[带你搞定MySQL实战，轻松对应海量业务处理及高并发需求，从容应对大场面试][MySQL]

--------------------

# DB Version #

mysql> select version();
    +-----------+
    | version() |
    +-----------+
    | 5.7.28    |
    +-----------+
    1 row in set
    
    mysql>

--------------------

# Table #

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='员工记录表';

两个索引

1.  主键索引
2.  二级索引 KEY `idx_name_age_position` (`name`,`age`,`position`) USING BTREE

重点就是这个二级索引 ，记号了哈。

--------------------

# 数据量 #

mysql> select count(1) from employees ;
    +----------+
    | count(1) |
    +----------+
    |   100002 |
    +----------+
    1 row in set
    
    mysql>

--------------------

# 案例一 ：explain select \* from employees where name = ‘LiLei’ and position = ‘dev’ order by age #

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

先想一下这个order by 会不会走索引 ？

![在这里插入图片描述][20200803201718563.png]

会走索引

原因呢 ？

脑海中要有这个联合索引在MySQL底层的B+Tree的数据结构 ， 索引 排好序的数据结构。

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lhbmdzaGFuZ3dlaQ_size_16_color_FFFFFF_t_70 1]

name = ‘LiLei’ and position = ‘dev’ order by age

**name 为 LiLei , name 确定的情况下， age 肯定是有序的 ，age 有序不能保证position 有序**

所以 这个order by age 是可以走索引的

继续分析下这个explain

order by 走的索引 是不会体现在key\_len上的, 这个74 = 3 \* 24 + 2 ， 是计算的name 。 最左匹配原则 ，中间字段不能断，因此查询用到了name索引。

但是 Extra直接里面可以看出来 Using index condition ，说明age索引列用在了排序过程中 。 如果没有走索引的话，那就是 Using FileSort 了

接下来继续看几个例子，加深理解，**重点是脑海中的 索引B+Tree结构**

--------------------

# 案例二： explain select \* from employees where name = ‘LiLei’ order by position #

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

想一想，这个order by 会走索引吗？

我们来看下索引 KEY `idx_name_age_position` (`name`,`age`,`position`) USING BTREE

再来看下查询SQL

where  name = 'LiLei' order by position ;

name = LiLei , name 值能确定下来， 符合最左匹配原则 所以查询会走索引 ， 用了联合索引中的name字段， key len = 74 . 所以 Using index condition

order by position , 在索引中 中间缺失了age , 用position ，跳过了age , 那索引树能是有序的吗？ 肯定不是。。。所以 position肯定不是排好序的 ， 无法走索引排序，因此 Extra信息 有 Using filesort

来看下执行计划

正如分析~

有感觉了吗？ 再来看一个

--------------------

# 案例三：explain select \* from employees where name = ‘LiLei’ order by age , position #

这个SQL和案例二的很相似 ， 仅仅在排序的时候在前面多了一个age字段参与排序 ， 那分析分析 order by 会走索引吗

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

**时刻不要那个索引树** ，来分析一下

name = LiLei , name 固定，结合 建立的索引， 最左原则，所以查询肯定会走联合索引中的部分索引 name .

在name都是LiLei 的情况下 ， order by age , position 结合索引树 ，age和position用于排序 也是有序的，应该不会走using filesort

我们来看下执行计划

![在这里插入图片描述][20200804100556429.png]

--------------------

# 案例四：explain select \* from employees where name = ‘LiLei’ order by position , age #

再分析一个，和案例上也很像。 把 order by的排序顺序 调整一下，我们来分析一下 order by会不会走索引

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

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lhbmdzaGFuZ3dlaQ_size_16_color_FFFFFF_t_70 2]

咦 ， 执行计划中有 using filesort

为什么呢？

**看看我们二级索引的建立的字段顺序 ， 创建顺序为name,age,position，但是排序的时候age和position颠倒位置了， 那排好序的特性肯定就无法满足了，那你让MySQL怎么走索引？**

--------------------

# 案例五：explain select \* from employees where name = ‘LiLei’ and age = 18 order by position , age ; #

这个order by 会走索引吗？

![在这里插入图片描述][20200804155352325.png]

**走了dx\_name\_age\_position 索引中的 name 和 age , order by 其实也走了索引，你看extra中并没有 using filesort ,因为age为常量，在排序中被MySQL优化了，所以索引未颠倒，不会出现Using filesort**

--------------------

# 案例六：explain select \* from employees where name = ‘LiLei’ order by age asc , position desc ; #

![在这里插入图片描述][20200804155813933.png]

我们可以看到虽然排序的字段列与建立索引的顺序一样， order by默认升序排列，而SQL中的 position desc变成了降序排列，导致与索引的排序方式不同，从而产生Using filesort。

Note: Mysql8以上版本有降序索引可以支持该种查询方式。

--------------------

# 案例七：explain select \* from employees where name in (‘HanMeiMei’ , ‘LiLei’) order by age , position ; #

对order by 来讲 ，多个相等的条件也是 范围查询。 既然是范围查询， 可能对于每个值在索引中是有序的，但多个合并在一起，就不是有序的了，所以 using filesort .

--------------------

# 案例八： explain select \* from employees where name > ‘HanMeiMei’ order by name ; #

MySQL自己内部有一套优化机制，且数据量不同、版本不一样，结果也可能有差异

**一般情况下， 联合索引第一个字段用范围不一定会走索引 ， 可以采用 覆盖索引进行优化，避免回表带来的性能开销 。**

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lhbmdzaGFuZ3dlaQ_size_16_color_FFFFFF_t_70 3]

--------------------

# group by 优化 #

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

--------------------

# 小结 #

*  MySQL支持两种方式的排序filesort和index，Using index是指MySQL扫描索引本身完成排序
 *  order by满足两种情况会使用Using index  
    A: order by语句使用索引最左前列。  
    B: 使用where子句与order by子句条件列组合满足索引最左前列
 *  尽量在索引列上完成排序，遵循索引建立（索引创建的顺序）时的最左前缀法则
 *  如果order by的条件不在索引列上，就会产生Using filesort
 *  能用覆盖索引尽量用覆盖索引

--------------------

# 搞定MySQL #

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lhbmdzaGFuZ3dlaQ_size_16_color_FFFFFF_t_70 4]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lhbmdzaGFuZ3dlaQ_size_16_color_FFFFFF_t_70]: /images/20230208/bdd80f84047647f78c339a38e9cc3454.png
[MySQL]: https://marketing.csdn.net/poster/3766
[20200803201718563.png]: /images/20230208/1fc2467f0ed849bf999c0cee3aa19899.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lhbmdzaGFuZ3dlaQ_size_16_color_FFFFFF_t_70 1]: /images/20230208/362aeef9e813494d996ced1b9aa69c0d.png
[20200804100556429.png]: /images/20230208/d8affd3186014d4bbd9d0c406d51780c.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lhbmdzaGFuZ3dlaQ_size_16_color_FFFFFF_t_70 2]: /images/20230208/b0b1cf699303416b9d44422638d297c2.png
[20200804155352325.png]: /images/20230208/26f15d21f12d4c8d9a4d68436273ff64.png
[20200804155813933.png]: /images/20230208/28591d4a483a45ab96c0aaaef02add99.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lhbmdzaGFuZ3dlaQ_size_16_color_FFFFFF_t_70 3]: /images/20230208/0427ad21fc84422093071746436ef8b0.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lhbmdzaGFuZ3dlaQ_size_16_color_FFFFFF_t_70 4]: /images/20230208/b27b17bf923a4baa8fc32bc0f63a890b.png