mysql where group by 顺序_什么是MySQL的执行计划（Explain关键字）？

最新推荐文章于 2024-06-05 12:08:18 发布
最新推荐文章于 2024-06-05 12:08:18 发布
阅读量323 收藏
点赞数
文章标签： mysql where group by 顺序
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_33883104/article/details/113630791
版权

什么是Explain

Explain被称为执行计划，在语句之前增加 explain 关键字，MySQL 会在查询上设置一个标记，模拟MySQL优化器来执行SQL语句，执行查询时，会返回执行计划的信息，并不执行这条SQL。（注意，如果 from 中包含子查询，仍会执行该子查询，将结果放入临时表中）。

Explain可以用来分析SQL语句和表结构的性能瓶颈。通过explain的结果，可以了解到如数据表的查询顺序、数据查询操作的操作类型、哪些索引可以被命中、哪些索引实际会命中、每个数据表有多少行记录被查询等信息。

Explain命令扩展

explain extended

在explain的基础上提供一些额外的查询信息，在explian extended执行以后，通过show warnings命令可以得到优化后的查询语句，可以看出优化器做了哪些工作，还可以通过某些数据估算表连接的行数。

explain partitions

用于分析使用了分区的表，会显示出可能用到的分区。

两点重要提示

1. Explain结果是基于数据表中现有数据的。

2. Explain结果与MySQL版本有很大的关系，不同版本的优化器的优化策略不同。

本文示例使用的数据库表

4c2b0b864f5060b5ef677ddf79eb6c5b.png

Explain命令(关键字)

explain简单示例

mysql>explain select * from t_user;

11f21c4abb1ae2a531e89387cffe99c7.png

在查询中的每个”表”会输出一行，这里的“表”的意义非常广泛，不仅仅是数据库表，还可以是子查询、一个union 结果等。

explain结果列说明

【id列】

id列是一个有顺序的编号，是查询的顺序号，有几个 select 就显示几行。id的顺序是按 select 出现的顺序增长的。id列的值越大执行优先级越高越先执行，id列的值相同则从上往下执行，id列的值为NULL最后执行。

【select_type列】

select_type列的值标明查询的类型：

1）simple：表明当前行对应的select是简单查询，不包含子查询和union

2）primary：表明当前行对应的select是复杂查询中最外层的 select

3）subquery：表明当前行对应的select是包含在 select 中的子查询（不在 from 子句中）

4）derived：表明当前行对应的select是包含在 from 子句中的子查询。

MySQL会创建一个临时表来存放子查询的查询结果。用如下的语句示例说明：

explain select (select 1 fromt_user where user_id=1) from (select * from t_group where group_id=1) tmp;

4dca9ae549be87dabc957768b204b0ed.png

*注意，在资料收集过程中，发现不同版本的MySQL表现不一致，经反复对比，5.7及以后版本的输出如下：

86fdf37de42b51f9da8ba6ee3b6a7f5e.png

很显然，MySQL在这方面进行了优化.

*注意，MySQL不同版本Explain表现差异很大，有些场景，从语句层面看，是要使用到索引，但经过优化器分析，结合表中现有数据，如果MySQL认为全表扫描性能更优，则会使用全表扫描。

5）union：表明当前行对应的select是在 union 中的第二个和随后的 select

6）union result：表明当前行对应的select是从 union 临时表检索结果的 select

explain select 1 union all select 2 fromdual;

b080cb3aa32bceb81d75498f5cc98286.png

MySQL5.7及以后同样做了优化

4fb0aee3c1ab5f33a19b169f92424e5e.png

【table列】

table列的结果表明当前行对应的select正在访问哪个表。当查询的<from>子句中有子查询时，table列是 <derivedN> 格式，表示当前的select依赖 id=N结果行对应的查询，要先执行 id序号=N 的查询。当存在 union 时，UNION RESULT 的 table 列的值为<unionN1,N2>，N1和N2表示参与 union 的select 行的id序号。

【type列】

type列的结果表明当前行对应的select的关联类型或访问类型，也就是优化器决定怎么查找数据表中的行，以及查找数据行记录的大概范围。该列的取值优化程度的优劣，从最优到最差依次为：null>system> const > eq_ref > ref > range > index > ALL。一般来说，要保证查询达到range级别，最好达到ref。

1）null，MySQL优化器在优化阶段分解查询语句，在优化过程中就已经可以得到结果，那么在执行阶段就不用再访问表或索引。

explain select min(user_id) from t_user;

f816587f4832a7f2d4ac35969457afe8.png

这时的函数min，在索引列user_id中选取最小值，可以直接查找索引来完成，不需要执行时再访问数据表。

2）const和system：const出现在用 primary key（主键） 或 unique key（唯一键） 的所有列与常数比较时，优化器对查询进行优化并将其部分查询转化成一个常量。最多有一个匹配行，读取1次，速度非常快。而system是const的特例，表中数据只有一条匹配时为system。此时可以用explain extended+show warnings查看执行结果。

explain extended select * from (select * from t_user where user_id = 1) tmp;

show warnings;

15e87a67d1b3ced933a01de37d0a084f.png

231cbf0d4f3333f12766332af069e9a5.png

MySQL5.7及以后版本优化后：

d66fdf24425497e1717073ff9cb6553a.png

b85bafa197d9dbb04a1adb7559f3e829.png

3）eq_ref：primary key（主键）或 unique key（唯一键） 索引的所有构成部分被join使用 ，只会返回一条符合条件的数据行。这是仅次于const的连接类型。

explain select * from t_group_user gu left join t_group g ong.group_id = gu.group_id;

1c686982718147e2fd9f23b245a070e8.png

4) ref：与eq_ref相比，ref类型不是使用primary key（主键） 或 unique key（唯一键）等唯一索引，而是使用普通索引或者联合唯一性索引的部分前缀，索引和某个值相比较，可能会找到符合条件的多个数据行。

1. 如下示例，使用的group_name是普通索引

explain select * from t_group where group_name= 'group1';

9d50ad4382ab0d86307cb8af3604aa9c.png

2.关联表查询

explain select g.group_id from t_group gleft join t_group_user gu on gu.group_id = g.group_id;

bb65e265f6f680eb776ee01bbc424f18.png

5）range：出现在 in(),between ,> ,<, >= 等操作符中。使用一个索引来查询给定范围的行。

6）index：扫描全表索引（index是从索引中读取的,所有字段都有索引，而all是从硬盘中读取），比ALL要快。

explain select * from t_group;

d0ee9e4d80c09a1a09cb6f30dcb5191b.png

7）all：即全表扫描，需要从头到尾去查找所需要的行。一般这种情况下这需要增加索引来进行查询优化了

explain select * from t_user;

20758e9636e9321bdb27cdf5a2c60064.png

【possible_keys列】

这一列的结果表明查询可能使用到哪些索引。但有些时候也会出现出现possible_keys 列有结果，而 后面的key列显示 null 的情况，这是因为此时表中数据不多，优化器认为查询索引对查询帮助不大，所以没有走索引查询而是进行了全表扫描。

如果possible_keys列的结果是null，则表明没有相关的索引。这时，可以通过优化where子句，增加恰当的索引来提升查询性能。

【key列】

这一列表明优化器实际采用哪个索引来优化对该表的访问。如果没有使用索引，则该列是 null。

【key_len列】

这一列表明了在索引里使用的字节数，通过这个值可以大致估算出具体使用了联合索引中的前几个列。

key_len计算规则这里不再赘述，不同的数据类型所占的字节数是不一致的。

【ref列】

这一列表明了在key列记录的索引中，表查找值所用到的列或常量，常见的有：const（常量），字段名，如user.user_id

【rows列】

这一列表明优化器大概要读取并检测的行数。跟实际的数据行数大部分情况是不一致的。

【Extra列】

顾名思义，这一列表明的是额外信息,这一列的取值对优化SQL非常有参考意义。常见的重要取值如下：

1）using index：所有被查询的字段都是索引列(称为覆盖索引),并且where条件是索引的前导列，出现这样的结果，是性能高的表现。

explainselect group_id,group_name from t_group;

05cb307c607af9ab5d9012f913a393e5.png

2）using where：被查询的列未被索引覆盖，where条件也并非索引的前导列，表示 MySQL 执行器从存储引擎接收到查询数据,再进行“后过滤”（Post-filter）。所谓“后过滤”，就是先读取整行数据，再检查此行是否符合 where 句的条件，符合就留下，不符合便丢弃。

explain select * from t_user whereuser_name='user1';

054176ae07e5e06394c9e8e7ae2498a7.png

3）using where Using index：被查询的列被索引覆盖，并且where条件是索引列之一但是不是索引的前导列，也就是没有办法直接通过索引来查询到符合条件的数据

explain select * from t_group where group_name = 'group1';

99256133ca30beb61284f83c8892e549.png

4）null：被查询的列没有被索引覆盖，但where条件是索引的前导列，此时用到了索引，但是部分列未被索引覆盖，必须通过“回表查询”来实现，不是纯粹地用到了索引，也不是完全没用到索引

explain select * from t_user where user_id='1';

05e386467a392af67262033ec4891a0e.png

5）using index condition：与using where类似，查询的列不完全被索引覆盖，where条件中是一个前导列的范围；这种情况未能通过示例显现，可能跟MySQL版本有关系。

6） using temporary：这表明需要通过创建临时表来处理查询。出现这种情况一般是要进行优化的，用索引来优化。创建临时表的情况：distinct，group by，orderby，子查询等

explain select distinct user_name from t_user;

19098e5a271c11f3b820024e76e2c704.png

explain select distinct group_name fromt_group; --group_name是索引列

4b54c4ddc61784923dfda0cd9868712d.png

7) usingfilesort：在使用order by的情况下出现，mysql会对结果使用一个外部索引排序，而不是按索引次序从表里读取行。此时mysql会根据连接类型浏览所有符合条件的记录，并保存排序关键字和行指针，然后排序关键字并按顺序检索行信息。这种情况下要考虑使用索引来优化的。

explain select * from t_user orderby user_name;

f39781429f281bcacb9779e6ddd80c2b.png

explain select * from t_group order bygroup_name; --group_name是索引列

b943ef9f1d1d99663cb4ebaf51c82010.png

查询优化建议

结合前面的描述，首先看 type列的结果，如果有类型是 all 时，表示预计会进行全表扫描（fulltable scan）。通常全表扫描的代价是比较大的，建议创建适当的索引，通过索引检索避免全表扫描。

再来看下 Extra 列的结果，如果有出现 Using temporary 或者 Using filesort 则要多加关注：

Using temporary，表示需要创建临时表以满足需求，通常是因为GROUP BY的列没有索引，或者GROUP BY和ORDER BY的列不一样，也需要创建临时表，建议添加适当的索引。

Using filesort，表示无法利用索引完成排序，也有可能是因为多表连接时，排序字段不是驱动表中的字段，因此也没办法利用索引完成排序，建议添加适当的索引。

Using where，通常是因为全表扫描或全索引扫描时（type 列显示为 ALL 或index），又加上了WHERE条件，建议添加适当的索引。

索引使用情况分析

数据库表

主键索引：demo_id

联合索引：c1,c2,c3

fa7538547209253090225c62a76df095.png

实例说明

实例一：

explain select * from t_demo where c1='d1'and c2='d2' and c3='d3';

explain select * from t_demo where c2='d2'and c1='d1' and c3='d3';

explain select * from t_demo where c3='d3'and c1='d1' and c2='d3';

8f8dd6ea19db8978f7d0bc2cab1efa0b.png

几个Sql表现一致

type=ref,ref=const,const,const

执行常量等值查询时，改变索引列的顺序并不会更改explain的执行结果，优化器会进行优化，推荐按照索引顺序列编写sql语句。

实例二：

explain select * from t_demo where c1='d1'and c2>'d2' and c3='d3';

1587969d449d6fabc60633b23bb8217c.png

explain select * from t_demo where c1='d1'and c3>'d3' and c2='d2';

167c2f9ba049ebaf4204478df7ca3d04.png

第一个例子范围右侧索引失效，使用到了两个索引。

第二个例子，由于优化器优化的原因，使用到了全部的三个索引。

实例三：

explain select * from t_demo wherec1>'c' and c2='d2' and c3='d3';

142f370cb097fcc23512b4f3ff6d3c31.png

explain select * from t_demo wherec1>'e' and c2='d2' and c3='d3';

fb40e681a05c1f57a172300ab5c6e804.png

从上面两个实例可以发现，同样使用最左的索引列范围查询，有些情况未用到索引，做了全表扫描（第一个例子）；有些情况使用到了索引（第二个例子）。

经反复验证，发现如下规律（不一定可靠），也可能与数据的第一行或最小值相关。

1. 跟存储的数据有关

2. 在大于条件下，如果条件数据小于列数据，则索引无效；如果条件数据大于列数据，则索引有效；

在设计查询条件时，请注意规避。

针对第一个例子，可以采用覆盖索引的方式优化。

实例四：

explain select * from t_demo where c1='d1'and c2='d2' order by c3;

8a5affaeb689f502d5a0291c198bba1c.png

explain select * from t_demo where c1='d1'order by c3;

7b554bf1a05c7d78c45a2ea1cb3eca82.png

explain select * from t_demo where c1='d1'and c3='d3' order by c2;

dd208431d88418b461f6d59a16c2cf6f.png

order by排序使用到索引和没使用到索引的情况

实例五：

explain select * from t_demo where c1='d1'and c4='d4' order by c1,c2;

15ae3ea41fb37cc034151010622d39b7.png

条件列包含没有索引的列，出现了Using filesort

实例六：

explain select * from t_demo where c1='d1'and c4='d4' group by c1,c2;

76a4912f49b2103d25d6df105591c69a.png

性能非常差的场景，同时出现了Using temporary和Using filesort

总结

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

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

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

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

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

4. group by与order by很类似，都是先排序后分组，遵照索引创建顺序的最佳左前缀法则。

喜欢本文的朋友，欢迎关注公众号 程序员小灰，收看更多精彩内容

谈国平
关注
Hive执行计划分析之group by执行计划分析
进击的数据小白
09-01 894
基础数据 我们使用电商网站用户行为数据进行测试，数据格式如下: -- 用户行为数据 CREATE TABLE `tmp.log_user_behavior`( `user_id` string COMMENT '用户id', `item_id` string COMMENT '商品id', `category_id` string COMMENT '商品分类id', `behavior` string COMMENT '行为', `ts` date COMMENT '行为发生时间') P
MySql执行计划(Explain关键字详解)
最新发布
qq_45525848的博客
08-31 1047
MySql执行计划(Explain关键字详解)
mysql命令where_MySQL WHERE 子句:语法及案例剖析、从命令提示符中读取数据
weixin_31688465的博客
01-19 124
MySQL WHERE 子句我们知道从 MySQL 表中使用 SQL SELECT 语句来读取数据。如需有条件地从表中选取数据，可将 WHERE 子句添加到 SELECT 语句中。语法以下是 SQL SELECT 语句使用 WHERE 子句从数据表中读取数据的通用语法：SELECT field1, field2,...fieldN FROM table_name1, table_name2...[...
mysql ： group by 和 where
crslee的专栏
03-17 1056
where 是对数据集进行筛选； group by 是对结果集进行聚集； 也就是说 group by 是 在 where 筛选之后 进行聚集。 所以 group by 必须 跟在where 的 后面
mysql的查询、子查询及连接查询
weixin_34355715的博客
08-31 326
一、mysql查询的五种子句 where子句（条件查询）：按照“条件表达式”指定的条件进行查询。 group by子句（分组）：按照“属性名”指定的字段进行分组。group by子句通常和count()、sum()等聚合函数一起使用。 having子句（筛选）：有group by才能having子句，只有满足“条件表达式”中指定的条件的才能够输出。 order by子句（排序）：按照“属性...
MySQL中的where，group by，order by和having的区别。
小宝的博客
10-22 3985
目录 前言 where 和having group by和order by 一些特殊案例 前言 这是我今天上数据库课时思考的group和order的的区别，然后今天看一个大佬博客的时候发现where和group一起用我刚开始以为写错了，我就去查了一下，就有了新的收获，我在网上找了一些，然后总结出来的，也有借用一些其他大佬的博客，我也希望这个能带给大家帮助！！！ where 和having 首先我们可以先从下面的一个例子来了解where和having： select sno,.
【mysql知识点整理】— order by 、group by 出现Using filesort原因详解
12-14
在一个SQL查询中，执行顺序通常是这样的：`FROM` - `WHERE` - `GROUP BY` - `HAVING` - `SELECT` - `ORDER BY`。当`ORDER BY`和`GROUP BY`同时存在时，`GROUP BY`先于`ORDER BY`执行，这意味着数据首先按`GROUP BY`...
MySQL优化GROUP BY（松散索引扫描与紧凑索引扫描）
12-15
MySQL中的GROUP BY语句用于对数据进行分组和聚合，通常与聚合函数（如COUNT、SUM、AVG等）一起使用。在处理大数据量时，优化GROUP BY操作至关重要，因为这直接影响查询性能。MySQL提供了几种优化策略，其中两种是...
mysql 执行计划 cost_SQL优化 MySQL版 -分析explain SQL执行计划与Extra
weixin_39794734的博客
02-27 872
注：此文章必须有一定的MySQL基础，或观看执行计划入门篇传送门：终于总结到哦SQK执行计划的最后一个知识点了：ExtraExtra有以下几个值，它们都非常重要，它们表示你的SQL语句的最终性能，以下将介绍它的几种值，每个值都代表你的SQL语句的缺陷：1.Using filesort主要出现在 order by 排序、复合索引跨列；order by 排序出现原因：查询a表，却根据b表排序，例如：s...
MySQL走不走索引使用Explain关键字？
Do_a_layman的博客
11-30 737
2.2. table2.3：type字段详细2.4：key字段详细2.5：key_len字段详细2.6：rows字段详细2.7：extra字段详细MySQL 提供了一个 EXPALIN 命令，可以用于对 SELECT 语句的执行计划进行分析，并详细的输出分析结果，供开发人员进行针对性的优化。我们想要查询一条sql有没有用上索引，有没有全表查询，这些都可以通过explain这个命令来查看。
sql case when用法_跟飞哥学编程：SQL入门-9：单表SELECT进阶
weixin_39620279的博客
11-26 203
我们前面学习了分组（GROUP），也知道了GROUP的一个重大局限：只能获取分组之后的统计信息，无法获取“分组”过后的“组内”信息（为此，我们还使用了ORDER BY模拟这种分组效果）。但我们面对更复杂的需求可能就束手无策了，比如：根据Student的Age进行分组，获取每个年龄段的前三位成绩最好的同学。为此，T-SQL为我们提供了窗口查询功能，其用法如下所示：SELECT 查询结果如下所示：可以...
【MySQL】窗口函数原理，与where、group by关系
A-Itfuture的博客
06-05 1805
首先，你需要定义一个窗口，它决定了窗口函数将作用的数据范围。窗口可以基于行号、分区键或排序键来定义。
mysql----group by、where以及聚合函数需要注意事项
weixin_42972832的博客
08-04 3664
mysql----group by、where以及聚合函数需要注意事项
MYSQL中group by分组查询的用法详解（where和having的区别）！
m0_61227789的博客
01-28 1330
这篇文章主要介绍了MYSQL中分组查询 group by的用法，用具体实例介绍了其特性，并比较了where 和 having的区别！
mysql中 WHERE 和GROUP BY 的联合使用
chishichuo8062的博客
08-17 3268
where group by联合使用 select 列a,聚合函数 from 表名 where 过滤条件 group by 列a having 过滤条件group by 字句也和where条件语句结合在一起使用。当结合在一起时，where在前，group by 在后。即先对select x...
【sql】mysql分组查询group by的案例和原理
weixin_44823875的博客
05-30 8293
因为如果group by 的过程因为内存临时表放不下数据，从而用到磁盘临时表的话，是比较耗时的。并不是所有场景都适合加索引的，如果碰上不适合创建索引的场景，如果你的需求并不需要对结果集进行排序，可以使用order by null。就是把需要排序的字段，放到sort buffer，排完就返回。2-分组后筛选：数据源是分组后的结果集 ，用having，放在group by后面，因为在分组后进行筛选。（1）如果是全字段排序，需要查询返回的字段，都放入sort buffer，根据排序字段排完，直接返回。
mysql中group by与where等对结果集的处理顺序
热门推荐
qq_45060236的博客
05-09 1万+
可以 注意：where肯定在group by 之前。 一，group by 字句可以和where条件语句结合在一起使用。 当结合在一起时，where在前，group by 在后。 即先对select xx from xx的记录集合用where进行筛选，然后再使用group by 对筛选后的结果进行分组 ...
MySQL中的where，group by，order by和having的区别，2024年最新java面试知识点
2401_83977357的博客
04-09 1144
互联网大厂比较喜欢的人才特点：对技术有热情，强硬的技术基础实力；主动，善于团队协作，善于总结思考。无论是哪家公司，都很重视高并发高可用技术，重视基础，所以千万别小看任何知识。面试是一个双向选择的过程，不要抱着畏惧的心态去面试，不利于自己的发挥。同时看中的应该不止薪资，还要看你是不是真的喜欢这家公司，是不是能真的得到锻炼。其实我写了这么多，只是我自己的总结，并不一定适用于所有人，相信经过一些面试，大家都会有这些感触。下面有部分截图希望能对大家有所帮助。一个人可以走的很快，但一群人才能走的更远。
Hive – Group By 的实现 explain分析
zj360202的专栏
08-07 3174
准备数据 SELECT uid, SUM(COUNT) FROM logs GROUP BY uid; hive> SELECT * FROM logs; a 苹果 5 a 橙子 3 a 苹果 2 b 烧鸡 1   hive> SELECT uid, SUM(COUNT) FROM logs GROUP BY uid;
深入解析MySQL Explain命令与执行计划
Mysql Explain 是MySQL数据库管理系统中一个强大的工具，用于分析SQL查询的执行计划，帮助开发人员理解和优化查询性能。它通过提供关于查询如何被解析和执行的详细信息，使得优化查询变得更直观。 **语法与使用** ...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值