看人们如何使用COUNT(*)和计数(col),它看起来就像大多数人认为他们是同义词和只使用他们怎么样,虽然性能上有很大差异,甚至查询结果。同时,我们发现不同执行MyISAM和InnoDB引擎。
请注意:所有测试申请MySQL版本8.0.30,在后台,我跑每个查询三到五倍,以确保所有人都完全缓存在缓冲池(InnoDB)或文件系统(MyISAM)。
计数函数Innodb引擎:
让我们来看看以下系列的例子InnoDB引擎:
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20.
21
22
|
创建
表
count_innodb
(
id
int
(
10
)
无符号
不
零
AUTO_INCREMENT
,
val_with_nulls
int
(
11
)
默认的
零
,
val_no_null
int
(
10
)
无符号
不
零
,
主
关键
idx
(
id
)
)
引擎
=
InnoDB
默认的
字符集
=
latin1
;
(
mysql
)
>
选择
数
(
*
)
从
count_innodb
;
+
- - -
- - -
- - -
- - -
- - -
+
|
数
(
*
)
|
+
- - -
- - -
- - -
- - -
- - -
+
|
10000000
|
+
- - -
- - -
- - -
- - -
- - -
+
1
行
在
集
(
0.38
证券交易委员会
)
(
mysql
)
>
选择
数
(
val_no_null
)
从
count_innodb
;
+
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
+
|
数
(
val_no_null
)
|
+
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
+
|
10000000
|
+
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
+
1
行
在
集
(
0.38
证券交易委员会
)
|
在这个InnoDB引擎,我们可以看到它需要一些时间COUNT (*)和计数(val_no_null)表的行,进一步我们将看到,MyiSAM InnoDB表相比更快得到答案的感觉COUNT (*)。
但是为什么我们不能只缓存的实际数量的行吗?InnoDB不保持一个内部表的行数,因为并发事务可能会“看到”在同一时间不同数量的行。因此,SELECT COUNT(*)语句只对当前事务数行可见。顺便说一下,我们可以使用模式信息立即得到大约在问题表的行数:
|
1
2
3
4
5
6
7
|
(
mysql
)
>
选择
table_rows
从
information_schema
.tables
在哪里
table_name
=
“count_innodb”
;
+
- - -
- - -
- - -
- - -
- - -
- - -
+
|
TABLE_ROWS
|
+
- - -
- - -
- - -
- - -
- - -
- - -
+
|
9980586
|
+
- - -
- - -
- - -
- - -
- - -
- - -
+
1
行
在
集
(
0.00
证券交易委员会
)
|
正如你所看到的并不是精确的行数。然而,有时一个粗略的计算可能就足够了。
让我们来看看数(val_with_nulls);
|
1
2
3
4
5
6
7
|
(
mysql
)
>
选择
数
(
val_with_nulls
)
从
count_innodb
;
+
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - - - - -
+
|
数
(
val_with_nulls
)
|
+
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - - - - -
+
|
9990001
|
+
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - - - - -
+
1
行
在
集
(
2.14
证券交易委员会
)
|
在那里,你可以看到我们有差异的结果COUNT (*)vs计数(val_with_nulls)
为什么?因为val_with_nulls不是定义为列非空可以有一些空值,所以MySQL必须执行表扫描。这也是为什么第二个查询结果是不同的
所以COUNT (*)和计数(col)查询不仅可以大量的性能差异,还问不同的问题。
让我们有另一轮的疑问,让我们看看InnoDB成功地做COUNT (*),计数(val_no_null),计数(val_with_nulls)用同样的在哪里条款:
|
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
33
34
35
36
37
|
(
mysql
)
>
选择
数
(
*
)
从
count_innodb
在哪里
id
<
1000000
;
+
- - -
- - -
- - -
- - -
- - -
+
|
数
(
*
)
|
+
- - -
- - -
- - -
- - -
- - -
+
|
980000年
|
+
- - -
- - -
- - -
- - -
- - -
+
1
行
在
集
(
0.30
证券交易委员会
)
(
mysql
)
>
解释
选择
数
(
*
)
从
count_innodb
在哪里
id
<
1000000克
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
1。
行
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
select_type
:
简单的
表
:
count_innodb
类型
:
范围
possible_keys
:
主
关键
:
主
行
:
1955802
过滤后的
:
100.00
额外的
:
使用
在哪里
;
使用
指数
(
mysql
)
>
选择
数
(
val_no_null
)
从
count_innodb
在哪里
id
<
1000000
;
+
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
+
|
数
(
val_no_null
)
|
+
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
+
|
980000年
|
+
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
+
1
行
在
集
(
0.33
证券交易委员会
)
(
mysql
)
>
解释
选择
数
(
val_no_null
)
从
count_innodb
在哪里
id
<
1000000克
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
1。
行
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
select_type
:
简单的
表
:
count_innodb
类型
:
范围
possible_keys
:
主
关键
:
主
行
:
2013804
过滤后的
:
100.00
额外的
:
使用
在哪里
|
我们可以看到查询的性能是对这两种情况下,只有10%的差别,如果你仔细注意解释COUNT (*)查询,你会注意到使用索引。这意味着MySQL可以只使用索引,不碰其他表数据,这可能足以获得巨大的表的行数。
你可能想要使用列已经索引加速巨大的表的查询。
我们会有什么惊喜与计数(val_with_nulls) ?让我们来看看:
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20.
21
22
23
|
(
mysql
)
>
选择
数
(
val_with_nulls
)
从
count_innodb
在哪里
id
<
1000000
;
+
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - - - - -
+
|
数
(
val_with_nulls
)
|
+
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - - - - -
+
|
970001年
|
+
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - - - - -
+
1
行
在
集
(
0.33
证券交易委员会
)
(
mysql
)
>
解释
选择
数
(
val_with_nulls
)
从
count_innodb
在哪里
id
<
1000000克
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
1。
行
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
id
:
1
select_type
:
简单的
表
:
count_innodb
分区
:
零
类型
:
范围
possible_keys
:
主
关键
:
主
key_len
:
4
裁判
:
零
行
:
1955802
过滤后的
:
100.00
额外的
:
使用
在哪里
1
行
在
集
,
1
警告
(
0.00
证券交易委员会
)
|
没有惊喜;我们可以看到查询的性能是相当甚至所有COUNT(*),计数(val_with_nulls)计数(val_with_nulls)。
计数函数MyISAM引擎:
现在让我们来看看COUNT()函数MyISAM引擎:
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20.
21
22
|
创建
表
count_myisam
(
id
int
(
10
)
无符号
不
零
,
val_with_nulls
int
(
11
)
默认的
零
,
val_no_null
int
(
10
)
无符号
不
零
,
关键
idx
(
id
)
)
引擎
=
MyISAM
默认的
字符集
=
utf8mb4
核对
=
utf8mb4_0900_ai_ci
;
(
mysql
)
>
选择
数
(
*
)
从
count_myisam
;
+
- - -
- - -
- - -
- - -
- - -
+
|
数
(
*
)
|
+
- - -
- - -
- - -
- - -
- - -
+
|
10000000
|
+
- - -
- - -
- - -
- - -
- - -
+
1
行
在
集
(
0.00
证券交易委员会
)
(
mysql
)
>
选择
数
(
val_no_null
)
从
count_myisam
;
+
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
+
|
数
(
val_no_null
)
|
+
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
+
|
10000000
|
+
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
+
1
行
在
集
(
0.00
证券交易委员会
)
|
闪电的速度,我们所看到的!
因为这是MyISAM表,我们有缓存的表内的行数,这是MyISAM引擎是如何工作的。这就是为什么它可以立即回答COUNT (*)和计数(val_no_null)查询。
请注意发动机的区别:InnoDB事务引擎,MyISAM事务性存储引擎。
|
1
2
3
4
5
6
7
|
(
mysql
)
>
选择
数
(
val_with_nulls
)
从
count_myisam
;
+
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - - - - -
+
|
数
(
val_with_nulls
)
|
+
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - - - - -
+
|
9990001
|
+
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - - - - -
+
1
行
在
集
(
14.18
证券交易委员会
)
|
但当谈到计数(val_with_nulls)MyISAM表我们可以看到这是一个慢InnoDB 7倍;巨大的差异。同时,我们可以看到相同的行为计数(val_with_nulls),因为零值显然不会被考虑。MySQL优化器很好地在这种情况下,做一个全表扫描仅当它是必要的,因为列可以为空。
现在,让我们尝试更多查询MyISAM表使用WHERE子句:
|
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
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
|
(
mysql
)
>
选择
数
(
*
)
从
count_myisam
在哪里
id
<
1000000
;
+
- - -
- - -
- - -
- - -
- - -
+
|
数
(
*
)
|
+
- - -
- - -
- - -
- - -
- - -
+
|
1001237
|
+
- - -
- - -
- - -
- - -
- - -
+
1
行
在
集
(
0.41
证券交易委员会
)
(
mysql
)
>
解释
选择
数
(
*
)
从
count_myisam
在哪里
id
<
1000000
\
G
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
1。
行
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
select_type
:
简单的
表
:
count_myisam
类型
:
范围
possible_keys
:
idx
关键
:
idx
行
:
1041561
过滤后的
:
100.00
额外的
:
使用
在哪里
;
使用
指数
(
mysql
)
>
选择
数
(
val_no_null
)
从
count_myisam
在哪里
id
<
1000000
;
+
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
+
|
数
(
val_no_null
)
|
+
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
+
|
1001237
|
+
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
+
1
行
在
集
(
2.55
证券交易委员会
)
(
mysql
)
>
解释
选择
数
(
val_no_null
)
从
count_myisam
在哪里
id
<
1000000
\
G
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
1。
行
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
select_type
:
简单的
表
:
count_myisam
类型
:
范围
possible_keys
:
idx
关键
:
idx
行
:
1041561
过滤后的
:
100.00
额外的
:
使用
指数
条件
;
使用
MRR
(
mysql
)
>
选择
数
(
val_with_nulls
)
从
count_myisam
在哪里
id
<
1000000
;
+
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - - - - -
+
|
数
(
val_with_nulls
)
|
+
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - - - - -
+
|
1000281
|
+
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - - - - -
+
1
行
在
集
(
2.55
证券交易委员会
)
(
mysql
)
>
解释
选择
数
(
val_with_nulls
)
从
count_myisam
在哪里
id
<
1000000
\
G
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
1。
行
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
select_type
:
简单的
表
:
count_myisam
类型
:
范围
possible_keys
:
idx
关键
:
idx
行
:
1041561
过滤后的
:
100.00
额外的
:
使用
指数
条件
;
使用
MRR
|
正如您可以看到的,即使你有一个在哪里条款,性能COUNT (*)和计数(col)可以明显不同。事实上,这个例子显示了一个五倍性能差异,因为所有的数据适合在内存中(为你的信息,因为它是MyISAM引擎,缓存数据的文件系统缓存级别)。IO-bound工作负载,你经常可以看到甚至100倍性能差异。
COUNT(*)查询可以使用覆盖指数尽管计数(col)不能。当然,您可以扩展指数(val_with_nulls id)又让查询索引覆盖,但我只会使用这个解决方案,如果你不能改变查询(即,它是一个第三方应用程序)或当列名的情况下查询是有原因的,和你需要一个非空值。
值得注意的是在这种情况下,优化器MySQL不做好优化查询。人们可以注意到(val_no_null)列不是零,所以计数(val_no_null)是一样的COUNT (*),所以查询可以运行作为一个index-covered查询。它不,查询执行行读取。
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
|
(
mysql
)
>
改变
表
count_myisam
下降
关键
idx
,
添加
关键
idx
(
id
,
val_with_nulls
)
;
查询
好吧
,
10000000
行
影响
(
1
最小值
38.71
证券交易委员会
)
记录
:
10000000
重复的
:
0
警告
:
0
(
mysql
)
>
选择
数
(
val_with_nulls
)
从
count_myisam
在哪里
id
<
1000000
;
+
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - - - - -
+
|
数
(
val_with_nulls
)
|
+
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - - - - -
+
|
1000281
|
+
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - - - - -
+
1
行
在
集
(
0.42
证券交易委员会
)
(
mysql
)
>
选择
数
(
*
)
从
count_myisam
在哪里
id
<
1000000
;
+
- - -
- - -
- - -
- - -
- - -
+
|
数
(
*
)
|
+
- - -
- - -
- - -
- - -
- - -
+
|
1000762
|
+
- - -
- - -
- - -
- - -
- - -
+
1
行
在
集
(
0.56
证券交易委员会
)
|
正如您可以看到的,扩展索引有助于提高计数(val_with_nulls)查询null值相比7倍左右计数(val_with_nulls)没有索引。但同时,你可以看到COUNT (*)慢变成0,6倍,可能是因为该指数变成了大约两倍的时间在这里。
最后,我想消除一些错觉(1)数(0)和计数。
|
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
33
34
35
36
37
|
(
mysql
)
>
选择
数
(
1
)
从
count_innodb
在哪里
id
<
1000000
;
+
- - -
- - -
- - -
- - -
- - -
+
|
数
(
1
)
|
+
- - -
- - -
- - -
- - -
- - -
+
|
980000年
|
+
- - -
- - -
- - -
- - -
- - -
+
1
行
在
集
(
0.30
证券交易委员会
)
(
mysql
)
>
选择
数
(
0
)
从
count_innodb
在哪里
id
<
1000000
;
+
- - -
- - -
- - -
- - -
- - -
+
|
数
(
0
)
|
+
- - -
- - -
- - -
- - -
- - -
+
|
980000年
|
+
- - -
- - -
- - -
- - -
- - -
+
1
行
在
集
(
0.30
证券交易委员会
)
(
mysql
)
>
解释
选择
数
(
1
)
从
count_innodb
在哪里
id
<
1000000克
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
1。
行
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
select_type
:
简单的
表
:
count_innodb
类型
:
范围
possible_keys
:
主
关键
:
主
行
:
1955802
过滤后的
:
100.00
额外的
:
使用
在哪里
;
使用
指数
(
mysql
)
>
解释
选择
数
(
0
)
从
count_innodb
在哪里
id
<
1000000克
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
1。
行
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
select_type
:
简单的
表
:
count_innodb
类型
:
范围
possible_keys
:
主
关键
:
主
行
:
1955802
过滤后的
:
100.00
额外的
:
使用
在哪里
;
使用
指数
|
正如你所看到的,查询的性能和解释都是一样的,它其实并不重要你什么号码将括号内COUNT()函数。可以是任何你想要的数量,它将完全等于COUNT (*)通过性能和实际产出的查询。
我认为它应该是“计数(val_no_null)”,而不是计数(val_with_nulls)
你好,Zonglei盾。好,谢谢你。