深入理解MySQL COUNT函数:避免常见陷阱与性能优化

1,801 阅读5分钟

写本篇文章的最初原因是因为一个线上事故,分页统计结果少了几K的数据,吓死个人。

COUNT函数在MySQL中是一种非常重要的聚合函数,用于计算满足特定条件的行数。它可以用于统计查询结果的行数、计算某个列的非重复值数量以及计算满足特定条件的行数等。

COUNT函数无疑是非常重要的,用错所导致的影响也是相当巨大,本文将详细介绍一下COUNT的四大陷阱和性能优化

graph LR
A(COUNT函数)
B(陷阱)
C(性能)
D(COUNT vs COUNT column)
E(COUNT和NULL值)
F(COUNT和DISTINCT)
G(多表联接的COUNT)
H(其他方式)
I(COUNT和索引)

A ---> B
A ---> C
B ---> D
B ---> E
B ---> F
B ---> G
C ---> H
C ---> I

style B fill:#FFC0CB,stroke:#FFC0CB,stroke-width:2px
style C fill:#FFA07A,stroke:#FFA07A,stroke-width:2px
style D fill:#FFFFE0,stroke:#FFFFE0,stroke-width:2px
style E fill:#98FB98,stroke:#98FB98,stroke-width:2px
style F fill:#B2FFFF,stroke:#B2FFFF,stroke-width:2px
style G fill:#ADD8E6,stroke:#ADD8E6,stroke-width:2px
style H fill:#E6E6FA,stroke:#E6E6FA,stroke-width:2px
style I fill:#EEDD82,stroke:#EEDD82,stroke-width:2px

一、陷阱一:COUNT(*) VS COUNT(column)

在SQL查询中,COUNT(*)和COUNT(column)是用于计算行数的两种常见方式。它们之间存在一些陷阱和区别。

1.1 COUNT(*)

语法

SELECT COUNT(*) FROM table_name;

COUNT(*)用于计算整个表中的行数,包括所有的行,无论是否包含NULL值。

由于不需要指定具体的列名,因此它更简洁和方便。

陷阱:当使用COUNT(*)时,数据库引擎需要遍历整个表来计算行数,可能会导致性能问题,特别是对于大型表而言。

1.2 COUNT(column)

语法

SELECT COUNT(column) FROM table_name;

COUNT(column)用于计算指定列中非NULL值的行数。

可以指定具体的列名,只计算该列中非NULL值的行数。

陷阱:当使用COUNT(column)时,如果指定的列包含NULL值,那么这些NULL值将不会被计算在内。因此,如果你需要计算包括NULL值的行数,应该使用COUNT(*)而不是COUNT(column)

image.png

前面已经说清楚了,COUNT(*) VS COUNT(column)的陷阱以及COUNTNULL的关系,下面罗列一些常见情况和对应的结果解释:

  • 如果使用COUNT(*)COUNT(column)时,查询结果集中没有匹配的行,则COUNT函数的返回值为0,即使列中包含NULL值。

  • 如果使用COUNT(column)时,查询结果集中只有NULL值的行,则COUNT函数的返回值为0,因为它只计算非NULL值的行数。

  • 如果使用COUNT(*)时,查询结果集中只有NULL值的行,则COUNT函数的返回值为包含NULL值的行数。

  • 如果使用COUNT(*)COUNT(column)时,查询结果集中既有NULL值的行,又有非NULL值的行,则COUNT函数的返回值将包括非NULL值的行数。

二、COUNT和DISTINCT

COUNT和DISTINCT是两个常用的聚合函数,用于计算行数和去重操作。

DISTINCT关键字用于对查询结果进行去重操作。

它可以应用于SELECT语句中的一个或多个列,返回唯一的值,去除重复的行。

语法如下

-- 对多个列进行去重
SELECT DISTINCT column1, column2 FROM table_name; 

-- 对满足条件的列进行去重
SELECT DISTINCT column FROM table_name WHERE condition; 

需要注意的是,COUNT和DISTINCT可以结合使用,以计算去重后的行数。例如,使用COUNT(DISTINCT column)可以计算某一列的去重后的值的数量。

语法如下

-- 计算某列的去重后的值的数量
SELECT COUNT(DISTINCT column) FROM table_name; 

image.png

三、多表连接的COUNT

  • 在多表联接查询中,如果你想要计算符合条件的行数,可以使用COUNT函数结合联接操作来实现。

先来看一个案例:

SELECT
	COUNT(*) AS row_count 
FROM
	x_user AS u
	JOIN x_user_extend AS e ON u.id = e.uid 
WHERE
	1 = 1;

在上面的案例中,我们使用了COUNT(*)函数来计算满足连接条件和其他条件的行数。x_userx_user_extend通过列id进行连接。最后,使用AS关键字给计算结果取了一个别名row_count。注意:上面COUNT()将计算满足条件的行数,无论在联接后的结果中是否存在重复的行。

  • 如果你只想计算去重后的行数,可以使用COUNT(DISTINCT column)来代替COUNT(),其中column是一个具有唯一值的列。

上案例:

SELECT
	COUNT(DISTINCT u.id) AS row_count 
FROM
	x_user AS u
	JOIN x_user_extend AS e ON u.id = e.uid 
WHERE
	1 = 1;

在上面的案例中,我们使用COUNT(DISTINCT table1.column)来计算去重后的行数。

最后,小结一下

image.png

四、性能问题

  • 在没有索引的情况下,COUNT()操作可能会非常慢,尤其是当表中的数据量很大时。这是因为MySQL需要扫描整个表来计算行数。

  • 对于COUNT(*),MySQL 8.0.13及以后的版本对InnoDB表进行了优化,提高了单线程工作负载下的查询性能。

特别注意:InnoDB引擎会尝试使用数据量较小的非聚簇索引来优化COUNT()查询。如果没有合适的索引,查询可能会使用全表扫描,导致性能下降。

五、总结

了解了这些“坑”之后,为了避免这些“坑”,建议在设计数据库时考虑使用合适的索引,以及在可能的情况下,使用COUNT(DISTINCT column)来确保统计的准确性。

同时,了解不同存储引擎的特性和MySQL版本的优化也很重要。

在处理大量数据时,考虑使用其他方法,如定期的批处理或使用缓存策略,来减轻数据库的负担。

希望本文对您有所帮助。如果有任何错误或建议,请随时指正和提出。

同时,如果您觉得这篇文章有价值,请考虑点赞和收藏。这将激励我进一步改进和创作更多有用的内容。

感谢您的支持和理解!