- ubuntu12.04环境下使用kvm ioctl接口实现最简单的虚拟机
- Ubuntu 通过无线网络安装Ubuntu Server启动系统后连接无线网络的方法
- 在Ubuntu上搭建网桥的方法
- ubuntu 虚拟机上网方式及相关配置详解
CFSDN坚持开源创造价值,我们致力于搭建一个资源共享平台,让每一个IT人在这里找到属于你的精彩世界.
这篇CFSDN的博客文章MySQL group by语句如何优化由作者收集整理,如果你对这篇文章有兴趣,记得点赞哟.
在MySQL中,新建立一张表,该表有三个字段,分别是id,a,b,插入1000条每个字段都相等的记录,如下:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
|
mysql> show
create
table
t1\G
*************************** 1. row ***************************
Table
: t1
Create
Table
:
CREATE
TABLE
`t1` (
`id`
int
(11)
NOT
NULL
,
`a`
int
(11)
DEFAULT
NULL
,
`b`
int
(11)
DEFAULT
NULL
,
PRIMARY
KEY
(`id`),
KEY
`a` (`a`)
) ENGINE=InnoDB
DEFAULT
CHARSET=utf8
1 row
in
set
(0.00 sec)
mysql>
select
*
from
t1 limit 10;
+
----+------+------+
| id | a | b |
+
----+------+------+
| 1 | 1 | 1 |
| 2 | 2 | 2 |
| 3 | 3 | 3 |
| 4 | 4 | 4 |
| 5 | 5 | 5 |
| 6 | 6 | 6 |
| 7 | 7 | 7 |
| 8 | 8 | 8 |
| 9 | 9 | 9 |
| 10 | 10 | 10 |
+
----+------+------+
10
rows
in
set
(0.00 sec)
|
当我们执行下面包含group by的SQL时,查看执行计划,可以看到:
1
2
3
4
5
6
7
|
mysql> explain
select
id%10
as
m,
count
(*)
as
c
from
t1
group
by
m limit 10;
+
----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+----------------------------------------------+
| id | select_type |
table
| partitions | type | possible_keys |
key
| key_len | ref |
rows
| filtered | Extra |
+
----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+----------------------------------------------+
| 1 | SIMPLE | t1 |
NULL
|
index
|
PRIMARY
,a | a | 5 |
NULL
| 1000 | 100.00 | Using
index
; Using
temporary
; Using filesort |
+
----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+----------------------------------------------+
1 row
in
set
, 1 warning (0.00 sec)
|
最后面有:
为了更好的理解这个group by语句的执行过程,我画一个图来表示:
对照上面这个表,我们不难发现,这个group by的语句执行流程是下面这样的:
a、首先创建内存临时表,内存表里有两个字段m和c,主键是m;m是id%10,而c是统计的count(*) 个数 。
b、扫描表t1的索引a,依次取出叶子节点上的id值,计算id%10的结果,记为x;此时如果临时表中没有主键为x的行,就插入一个记录(x,1);如果表中有主键为x的行,就将x这一行的c值加1; 。
c、遍历完成后,再根据字段m做排序,得到结果集返回给客户端。(注意,这个排序的动作是group by自动添加的。) 。
如果我们不想让group by语句帮我们自动排序,可以添加上order by null在语句的末尾,这样就可以去掉order by之后的排序过程了。如下:
1
2
3
4
5
6
7
|
mysql> explain
select
id%10
as
m,
count
(*)
as
c
from
t1
group
by
m
order
by
null
;
+
----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+------------------------------+
| id | select_type |
table
| partitions | type | possible_keys |
key
| key_len | ref |
rows
| filtered | Extra |
+
----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+------------------------------+
| 1 | SIMPLE | t1 |
NULL
|
index
|
PRIMARY
,a | a | 5 |
NULL
| 1000 | 100.00 | Using
index
; Using
temporary
|
+
----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+------------------------------+
1 row
in
set
, 1 warning (0.00 sec)
|
可以看到,explain最后面的using filesort字样已经不见了。再来看下结果:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
|
mysql>
select
id%10
as
m,
count
(*)
as
c
from
t1
group
by
m;
+
------+-----+
| m | c |
+
------+-----+
| 0 | 100 |
| 1 | 100 |
| 2 | 100 |
| 3 | 100 |
| 4 | 100 |
| 5 | 100 |
| 6 | 100 |
| 7 | 100 |
| 8 | 100 |
| 9 | 100 |
+
------+-----+
10
rows
in
set
(0.00 sec)
mysql>
select
id%10
as
m,
count
(*)
as
c
from
t1
group
by
m
order
by
null
;
+
------+-----+
| m | c |
+
------+-----+
| 1 | 100 |
| 2 | 100 |
| 3 | 100 |
| 4 | 100 |
| 5 | 100 |
| 6 | 100 |
| 7 | 100 |
| 8 | 100 |
| 9 | 100 |
| 0 | 100 |
+
------+-----+
10
rows
in
set
(0.00 sec)
|
当我们不加order by null的时候,group by会自动为我们进行排序,所以m=0的记录会在第一条的位置,如果我们加上order by null,那么group by就不会自动排序,那么m=0的记录就在最后面了.
我们当前这个语句,表t1中一共有1000条记录,对10取余,只有10个结果,在内存临时表中还可以放下,内存临时表在MySQL中,通过tmp_table_size来控制.
1
2
3
4
5
6
7
8
|
mysql> show variables
like
"%tmp_table%"
;
+
----------------+----------+
| Variable_name | Value |
+
----------------+----------+
| max_tmp_tables | 32 |
| tmp_table_size | 39845888 |
+
----------------+----------+
2
rows
in
set
, 1 warning (0.00 sec)
|
当我们的结果足够大,而内存临时表不足以保存的时候,MySQL就会使用磁盘临时表,整个访问的速度就变得很慢了。那么针对group by操作,我们如何优化?
01 。
group by优化之索引 。
从上面的描述中不难看出,group by进行分组的时候,创建的临时表都是带一个唯一索引的。如果数据量很大,group by的执行速度就会很慢,要想优化这种情况,还得分析为什么group by 需要临时表?
这个问题其实是因为group by的逻辑是统计不同的值出现的次数,由于每一行记录做group by之后的结果都是无序的,所以就需要一个临时表存储这些中间结果集。如果我们的所有值都是排列好的,有序的,那情况会怎样呢?
例如,我们有个表的记录id列是:
0,0,0,1,1,2,2,2,2,3,4,4, 。
当我们使用group by的时候,就直接从左到右,累计相同的值即可。这样就不需要临时表了.
上面的结构我们也不陌生,当我们以在某个数据列上创建索引的时候,这个列本身就是排序的,当group by是以这个列为条件的时候,那么这个过程就不需要排序,因为索引是自然排序的。为了实现这个优化,我们给表t1新增一个列z,如下:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
|
mysql>
alter
table
t1
add
column
z
int
generated always
as
(id % 10),
add
index
(z);
Query OK, 0
rows
affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql>
select
z
as
m,
count
(*)
as
c
from
t1
group
by
z;
+
------+-----+
| m | c |
+
------+-----+
| 0 | 100 |
| 1 | 100 |
| 2 | 100 |
| 3 | 100 |
| 4 | 100 |
| 5 | 100 |
| 6 | 100 |
| 7 | 100 |
| 8 | 100 |
| 9 | 100 |
+
------+-----+
10
rows
in
set
(0.00 sec)
mysql> explain
select
z
as
m,
count
(*)
as
c
from
t1
group
by
z;
+
----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------------+
| id | select_type |
table
| partitions | type | possible_keys |
key
| key_len | ref |
rows
| filtered | Extra |
+
----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | t1 |
NULL
|
index
| z | z | 5 |
NULL
| 1000 | 100.00 | Using
index
|
+
----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------------+
1 row
in
set
, 1 warning (0.00 sec)
|
我们新增字段z,z的值是id%10之后的值,并且创建索引,再通过group by对这个z列进行分组,可以看到,结果中已经没有临时表了.
所以,使用索引可以帮助我们去掉group by依赖的临时表 。
02 。
group by优化---直接排序 。
如果我们已经知道表的数据量特别大,内存临时表肯定不足以容纳排序的时候,其实我们可以通过告知group by进行磁盘排序,而直接跳过内存临时表的排序过程.
其实在MySQL中是有这样的方法的:在group by语句中加入SQL_BIG_RESULT这个提示(hint),就可以告诉优化器:这个语句涉及的数据量很大,请直接用磁盘临时表。当我们使用这个语句的时候,MySQL将自动利用数组的方法来组织磁盘临时表中的字段,而不是我们所周知的B+树。关于这个知识点,这里给出官方文档的介绍:
SQL_BIG_RESULT or SQL_SMALL_RESULT can be used with GROUP BY or DISTINCT to tell the optimizer that the result set has many rows or is small, respectively. For SQL_BIG_RESULT, MySQL directly uses disk-based temporary tables if they are created, and prefers sorting to using a temporary table with a key on the GROUP BY elements. For SQL_SMALL_RESULT, MySQL uses in-memory temporary tables to store the resulting table instead of using sorting. This should not normally be needed. 。
整个group by的处理过程将会变成:
a、初始化sort_buffer,确定放入一个整型字段,记为m; 。
b、扫描表t1的索引a,依次取出里面的id值, 将 id%100的值存入sort_buffer中; 。
c、扫描完成后,对sort_buffer的字段m做排序(如果sort_buffer内存不够用,就会利用磁盘临时文件辅助排序); 。
d、排序完成后,就得到了一个有序数组。类似0,0,0,1,1,2,2,3,3,3,4,4,4,4这样 。
e、根据有序数组,得到数组里面的不同值,以及每个值的出现次数.
昨天的文章中我们分析了union 语句会使用临时表,今天的内容我们分析了group by语句使用临时表的情况,那么MySQL究竟什么时候会使用临时表呢?
MySQL什么时候会使用内部临时表?
1、如果语句执行过程可以一边读数据,一边直接得到结果,是不需要额外内存的,否则就需要额外的内存,来保存中间结果; 。
2、如果执行逻辑需要用到二维表特性,就会优先考虑使用临时表。比如union需要用到唯一索引约束, group by还需要用到另外一个字段来存累积计数.
以上就是MySQL group by语句如何优化的详细内容,更多关于MySQL group by优化的资料请关注我其它相关文章! 。
原文链接:https://cloud.tencent.com/developer/article/1639458 。
最后此篇关于MySQL group by语句如何优化的文章就讲到这里了,如果你想了解更多关于MySQL group by语句如何优化的内容请搜索CFSDN的文章或继续浏览相关文章,希望大家以后支持我的博客! 。
目前我正在构建相当大的网络系统,我需要强大的 SQL 数据库解决方案。我选择 Mysql 而不是 Postgres,因为一些任务需要只读(MyISAM 引擎)而其他任务需要大量写入(InnoDB)。
我在 mysql 中使用如下命令。当它显示表格数据时,它被格式化为一个非常干净的表格,间距均匀且 |作为列分隔符。 SELECT * FROM TABLE_NAME; 当我从 CLI 运行命令时,如下
我知道这个问题之前已经被问过好几次了,我已经解决了很多问题,但到目前为止没有任何效果。 MySQL 试图将自身安装到的目录 (usr/local/mysql) 肯定有问题。关于我的错误的奇怪之处在于我
以下是我的 SQL 数据结构,我正在尝试如下两个查询: Select Wrk_ID, Wrk_LastName, Skill_Desc from Worker, Skill where
我们有一个本地 mysql 服务器(不在公共(public)域上),并希望将该服务器复制到我们拥有的 google 云 sql 实例。我的问题是:1.这可能吗?2.我们的本地服务器只能在本地网络上访问
我有一个表(test_table),其中一些字段值(例如字段 A、B 和 C)是从外部应用程序插入的,还有一个字段(字段 D),我想从现有表(store_table)插入其值,但在插入前者(A、B 和
我想创建一个 AWS RDS 实例,然后使用 terraform 管理数据库用户。因此,首先,我创建了一个 RDS 实例,然后使用创建的 RDS 实例初始化 mysql 提供程序,以进一步将其用于用户
当用户在我的网站上注册时,他们会在我的一个数据库中创建自己的表格。该表存储用户发布的所有帖子。我还想做的是也为他们生成自己的 MySql 用户——该用户仅有权从他们的表中读取、写入和删除。 创建它应该
我有一个关于 ColdFusion 和 Mysql 的问题。我有两个表:PRODUCT 和 PRODUCT_CAT。我想列出包含一些标记为:IS_EXTRANET=1 的特殊产品的类别。所以我写了这个
我想获取 recipes_id 列的值,以获取包含 ingredient_id 的 2,17 和 26 条目的值。 假设 ingredient_id 2 丢失则不获取记录。 我已经尝试过 IN 运算符
在 Ubuntu 中,我通常安装两者,但 MySQL 的客户端和服务器之间有什么区别。 作为奖励,当一个新语句提到它需要 MySQL 5.x 时,它是指客户端、服务器还是两者兼而有之。例如这个链接ht
我重新访问了我的数据库并注意到我有一些 INT 类型的主键。 这还不够独特,所以我想我会有一个指导。 我来自微软 sql 背景,在 ssms 中你可以 选择类型为“uniqeidentifier”并自
我的系统上有 MySQL,我正在尝试确定它是 Oracle MySQL 还是 MySQL。 Oracle MySQL 有区别吗: http://www.oracle.com/us/products/m
我是在生产 MySQL 中运行的应用程序的新维护者。之前的维护者已经离开,留下的文档很少,而且联系不上了。 我面临的问题是执行以下请求大约需要 10 秒: SELECT COUNT(*) FROM `
我有两个位于不同机器上的 MySQL 数据库。我想自动将数据从一台服务器传输到另一台服务器。比方说,我希望每天早上 4:00 进行数据传输。 可以吗?是否有任何 MySQL 内置功能可以让我们做到这一
有什么方法可以使用 jdbc 查询位于 mysql 根目录之外的目录中的 mysql 表,还是必须将它们移动到 mysql 根目录内的数据库文件夹中?我在 Google 上搜索时没有找到任何东西。 最
我在 mysql 数据库中有两个表。成员和 ClassNumbers。两个表都有一个付费年份字段,都有一个代码字段。我想用代码数字表中的值更新成员表中的付费年份,其中成员中的代码与 ClassNumb
情况:我有 2 台服务器,其中一台当前托管一个实时 WordPress 站点,我希望能够将该站点转移到另一台服务器,以防第一台服务器出现故障。传输源文件很容易;传输数据库是我需要弄清楚如何做的。两台服
Phpmyadmin 有一个功能是“复制数据库到”..有没有mysql查询来写这个函数?类似于将 db A 复制到新的 db B。 最佳答案 首先创建复制数据库: CREATE DATABASE du
我有一个使用 mySQL 作为后端的库存软件。我已经在我的计算机上对其进行了测试,并且运行良好。 当我在计算机上安装我的软件时,我必须执行以下步骤: 安装 mySQL 服务器 将用户名指定为“root
我是一名优秀的程序员,十分优秀!