面試官:請說一下Mysql中count(1)、count(*)以及count(列)的區別?

来源:https://www.cnblogs.com/coderacademy/p/17994558
-Advertisement-
Play Games

本文深度解析MySQL的COUNT(1), COUNT(*),COUNT(列)計數方式,強調COUNT(*)的廣泛應用與InnoDB存儲引擎的優化。通過性能比較,揭示COUNT(id)在索引下的性能,通過技術細節揭示MySQL查詢優化器的工作原理,最終總結適用場景,為讀者提供計數方式選擇的指導。 ...


近期在Review項目代碼時,發現同事們在查詢MySQL行數時存在多樣的方式,有的使用COUNT(1), 有的用COUNT(id), 還有人選擇了COUNT(*)。這混雜的選擇引發了我的思考。當然這三種count的方式也是眾說紛紜,其中最大的分歧點就是COUNT(*)COUNT(1)查詢性能上,有人覺得COUNT(*)需要轉換為COUNT(1),所以COUNT(1)得速度更快。究竟這三種計數方式之間有何區別,它們的背後原理是怎樣的呢?

COUNT()含義

在《高性能Mysql》一書第236頁中是這麼解釋COUNT的作用的:

COUNT()是一個特殊的函數,有兩種非常不同的作用:它可以統計某個列值的數量,也可以統計行數。在統計列值時要求列值是非空的(不統計NULL)。如果在COUNT()的括弧中指定了列或者列的表達式,則統計的就是這個表達式有值的結果數”。因為很多人對 NULL理解有問題,所以這裡很容易產生誤解。
COUNT()的另一個作用是統計結果集的行數。當MySOL確認括弧內的表達式值不可能為空時,實際上就是在統計行數。最簡單的就是當我們使用COUNT()的時候,這種情況下通配符並不會像我們猜想的那樣擴展成所有的列,實際上,它會忽略所有的列而直接統計所有的行數。
我們發現一個最常見的錯誤就是,在括弧內指定了一個列卻希望統計結果集的行數。如果希望知道的是結果集的行數,最好使用COUNT(*),這樣寫意義清晰,性能也會很好

由此我們也可以大概總結COUNT函數的種方式如下:

  • COUNT(1): 此查詢返回的是結果集中的行數,不關心具體的列內容,因此使用常數1。
    在很多資料庫系統中,這種方式被優化為與 SELECT COUNT(*) 相同的性能水平,因為資料庫引擎通常忽略括弧內的內容。

  • COUNT(*):統計整個表的行數,不考慮是否有NULL值。
    通常優於 COUNT(id),因為它不需要關心具體的列,且現代資料庫引擎會對其進行特殊優化。

  • COUNT(列) :統計指定列非空值的數量。需要考慮是否有NULL值
    此種方式取決於列是否有索引。如果 列有索引,資料庫引擎可能會利用索引進行快速計數。如果沒有索引,或者有大量NULL值,性能可能較差,因為需要掃描整個表。

區別

1、Mysql5.7

MySql 5.7官方文檔中是這麼介紹COUNT(expr)函數的

COUNT(expr)
Returns a count of the number of non-NULL values of expr in the rows retrieved by a SELECT statement. The result is a BIGINT value.
If there are no matching rows, COUNT() returns 0.
mysql> SELECT student.student_name,COUNT(*)
FROM student,course
WHERE student.student_id=course.student_id
GROUP BY student_name;
COUNT(*) is somewhat different in that it returns a count of the number of rows retrieved, whether or not they contain NULL values.

For transactional storage engines such as InnoDB, storing an exact row count is problematic. Multiple transactions may be occurring at the same time, each of which may affect the count.

InnoDB does not keep an internal count of rows in a table because concurrent transactions might “see” different numbers of rows at the same time. Consequently, SELECT COUNT(*) statements only count rows visible to the current transaction.

Prior to MySQL 5.7.18, InnoDB processes SELECT COUNT(*) statements by scanning the clustered index. As of MySQL 5.7.18, InnoDB processes SELECT COUNT(*) statements by traversing the smallest available secondary index unless an index or optimizer hint directs the optimizer to use a different index. If a secondary index is not present, the clustered index is scanned.

Processing SELECT COUNT(*) statements takes some time if index records are not entirely in the buffer pool. For a faster count, create a counter table and let your application update it according to the inserts and deletes it does. However, this method may not scale well in situations where thousands of concurrent transactions are initiating updates to the same counter table. If an approximate row count is sufficient, use SHOW TABLE STATUS.

InnoDB handles SELECT COUNT(*) and SELECT COUNT(1) operations in the same way. There is no performance difference.

For MyISAM tables, COUNT(*) is optimized to return very quickly if the SELECT retrieves from one table, no other columns are retrieved, and there is no WHERE clause. For example:
mysql> SELECT COUNT(*) FROM student;
This optimization only applies to MyISAM tables, because an exact row count is stored for this storage engine and can be accessed very quickly. COUNT(1) is only subject to the same optimization if the first column is defined as NOT NULL.

從官方文檔中我們可以看出mysql官方對COUNT函數的解釋:

  • COUNT(expr) 返回由 SELECT 語句檢索的行中 expr 的非 NULL 值的數量,結果為 BIGINT 值。如果沒有匹配的行,COUNT() 返回 0。

  • COUNT(*) 有所不同,它返回所檢索的行數的計數,無論它們是否包含 NULL 值。

  • 對於事務性存儲引擎(如 InnoDB),存儲準確的行數是有問題的。因為多個事務可能同時影響計數,InnoDB 不會保留表中行的內部計數。SELECT COUNT(*) 只會計算當前事務可見的行。

  • 在 MySQL 5.7.18 之前,InnoDB 通過掃描聚集索引處理 SELECT COUNT(*) 語句。從 MySQL 5.7.18 開始,除非索引或優化器提示指示使用其他索引,InnoDB 會通過遍歷最小的可用二級索引來處理 SELECT COUNT(*) 語句。如果沒有二級索引,則將掃描聚集索引。

  • 處理 SELECT COUNT(*) 語句可能花費一些時間,如果索引記錄沒有完全在緩衝池中。為了更快的計數,可以創建一個計數器表,並根據插入和刪除操作進行更新。然而,在成千上萬的併發事務更新同一計數器表的情況下,該方法可能無法很好地擴展。如果粗略的行數足夠,可以使用SHOW TABLE STATUS

  • InnoDB 處理 SELECT COUNT(*)SELECT COUNT(1) 操作的方式相同,沒有性能差異。

  • 對於 MyISAM 表,COUNT(\*) 在從一個表中檢索、沒有檢索其他列、沒有 WHERE 子句的情況下可以快速返回,因為 MyISAM 存儲了準確的行數。COUNT(1) 只有在第一列被定義為 NOT NULL 時才能進行相同的優化。

2、Mysql 8.0

Mysql8.0的文檔中對COUNT(expr)的解釋是這樣

COUNT(expr) [over_clause]

Returns a count of the number of non-NULL values of expr in the rows retrieved by a SELECT statement. The result is a BIGINT value.

If there are no matching rows, COUNT() returns 0.COUNT(NULL)returns 0.

This function executes as a window function if over_clause is present. over_clause is as described in Section 12.20.2, “Window Function Concepts and Syntax”.

mysql> SELECT student.student_name,COUNT(*) FROM student,course WHERE student.student_id=course.student_id GROUP BY student_name;
COUNT(*) is somewhat different in that it returns a count of the number of rows retrieved, whether or not they contain NULL values.

For transactional storage engines such as InnoDB, storing an exact row count is problematic. Multiple transactions may be occurring at the same time, each of which may affect the count.

InnoDB does not keep an internal count of rows in a table because concurrent transactions might “see” different numbers of rows at the same time. Consequently, SELECT COUNT(*) statements only count rows visible to the current transaction.

As of MySQL 8.0.13, SELECT COUNT(*) FROM tbl_name query performance for InnoDB tables is optimized for single-threaded workloads if there are no extra clauses such as WHERE or GROUP BY.

InnoDB processes SELECT COUNT(*) statements by traversing the smallest available secondary index unless an index or optimizer hint directs the optimizer to use a different index. If a secondary index is not present, InnoDB processes SELECT COUNT(*) statements by scanning the clustered index.

Processing SELECT COUNT(*) statements takes some time if index records are not entirely in the buffer pool. For a faster count, create a counter table and let your application update it according to the inserts and deletes it does. However, this method may not scale well in situations where thousands of concurrent transactions are initiating updates to the same counter table. If an approximate row count is sufficient, use SHOW TABLE STATUS.

InnoDB handles SELECT COUNT(*) and SELECT COUNT(1) operations in the same way. There is no performance difference.

For MyISAM tables, COUNT(*)is optimized to return very quickly if the SELECT retrieves from one table, no other columns are retrieved, and there is no WHERE clause. For example:

mysql> SELECT COUNT(*) FROM student;
This optimization only applies to MyISAM tables, because an exact row count is stored for this storage engine and can be accessed very quickly. COUNT(1) is only subject to the same optimization if the first column is defined as NOT NULL.

從mysql8.0的文檔中我們可以看出mysql8.0對COUNT的解釋

  • COUNT(expr) 返回在由 SELECT 語句檢索的行中 _expr_ 的非 NULL 值的數量,結果為 BIGINT 值。如果沒有匹配的行,COUNT() 返回 0。COUNT(NULL) 也返回 0。

  • COUNT(*) 有所不同,它返回所檢索的行數的計數,無論它們是否包含 NULL 值。

  • 對於事務性存儲引擎(如 InnoDB),存儲準確的行數是有問題的,因為多個事務可能同時影響計數。InnoDB 不會保留表中行的內部計數。SELECT COUNT(*) 只計算當前事務可見的行。

  • 在 MySQL 8.0.13 及以後版本,對於 InnoDB 表,執行 SELECT COUNT(*) FROM tbl_name 查詢性能在沒有額外子句(如 WHERE 或 GROUP BY)的情況下進行了優化,特別適用於單線程工作負載。

  • InnoDB 處理 SELECT COUNT(*) 語句的方式:

    • 通過遍歷最小可用二級索引,除非指示使用其他索引。
    • 如果沒有二級索引,InnoDB 通過掃描聚集索引來處理 SELECT COUNT(*) 語句。
  • 處理 SELECT COUNT(*) 語句可能花費一些時間,如果索引記錄沒有完全在緩衝池中。為了更快的計數,可以創建一個計數器表,讓應用程式根據插入和刪除操作進行更新。但是,這種方法在數千個併發事務同時對同一計數器表進行更新的情況下可能不會很好地擴展。如果粗略的行數足夠,可以使用 SHOW TABLE STATUS

  • InnoDB 對待 SELECT COUNT(*)SELECT COUNT(1) 操作的方式相同,沒有性能差異。

  • 對於 MyISAM 表,COUNT(*) 在從一個表中檢索、沒有檢索其他列、沒有 WHERE 子句的情況下可以快速返回,因為 MyISAM 存儲了準確的行數。COUNT(1) 只有在第一列被定義為 NOT NULL 時才能進行相同的優化。

結合Mysql5.7與Mysql8.0的文檔我們可以看出兩個版本對COUNT的支持的差異:

  • MySQL 8.0 優化了 InnoDB 表的 SELECT COUNT(*) 查詢性能: 在 MySQL 8.0.13 及以後版本,對於 InnoDB 表,執行 SELECT COUNT(*) 查詢的性能進行了優化,特別適用於單線程工作負載。這是 MySQL 5.7 文檔中未包含的新特性。
  • MyISAM 表的優化說明:
    MySQL 8.0 文檔中強調了 MyISAM 表在執行 COUNT(*) 時的優化情況,即在從一個表中檢索、沒有檢索其他列、沒有 WHERE 子句的情況下可以快速返回。MySQL 5.7 文檔中也提到了 MyISAM 表的優化,但對於 COUNT(*) 的具體優化情況沒有細節。

MySQL 8.0 在性能優化方面對於 InnoDB 表的 SELECT COUNT(*) 查詢進行了特別的關註,而且在 MyISAM 表的優化方面進行了詳細的說明。其他方面,兩個版本在COUNT()函數的解釋和使用上基本保持一致。

基於此我們明白,其實COUNT(*)COUNT(1)其實是一樣的,在性能上並沒有差異。

那這兩種方式與COUNT(列)的差異呢?從以上《高性能Mysql》以及Mysql官方文檔中我們知道,COUNT(列)是統計非空列的行數,它也會遍歷整張表,然後會對列對應的值做非空判斷,非空的欄位進行個數累加。當然這是列為主鍵索引時的操作。如果列不為主鍵索引時,那麼查詢時還需要進行回表操作,再根據主鍵獲取數據,此時無疑是增加了一次IO,在性能上其實是不如COUNT(*)COUNT(1)的。那麼我們就可以知道,按照效率來看,count(*) = count(1) > count(主鍵) > count(非主鍵列)

使用建議

  • 如果你想知道一張表的大概行數,我們可以直接使用show table status命令或者咱們使用的一些mysql客戶端Navicat或者datagrip都可以辦到。

  • 如果你想獲取一張表的確切行數時,我可以是優先使用使用 COUNT(*) 獲取行數,這樣寫法清晰,性能較好,尤其對於 InnoDB 表的優化更為明顯。我們要避免使用 COUNT(列) 統計行數,除非你真的需要統計該列非空值的數量,否則容易產生誤解。

篇幅有限,深入驗證將在後續文章中介紹。

本文已收錄於我的個人博客:碼農Academy的博客,專註分享Java技術乾貨,包括Java基礎、Spring Boot、Spring Cloud、Mysql、Redis、Elasticsearch、中間件、架構設計、面試題、程式員攻略等


您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • 轉載:【PostgreSQL架構】PostgreSQL的最佳群集高可用性方案-騰訊雲開發者社區-騰訊雲 (tencent.com) 如果您的系統依賴PostgreSQL資料庫並且您正在尋找HA的集群解決方案,我們希望提前告知您這是一項複雜的任務,但並非不可能實現。 我們將討論一些解決方案,您可以從中 ...
  • 1 月 31 日,InfoQ 極客傳媒合作伙伴年度盛典在北京舉辦,圍繞“有被 Q 到”主題精彩展開。GreatSQL受邀參會,與諸多IT行業伙伴相聚一堂,共築開發者生態,共話技術前沿與商業創新。 會議期間,InfoQ 為全國技術行業做出突出貢獻的企業和項目進行了頒獎。GreatSQL 開源資料庫社區 ...
  • SQL的相關語法記錄 【連接】 相關內容參考: 一張圖看懂 SQL 的各種 join 用法_51CTO博客_sql join用法 SQL FULL OUTER JOIN 關鍵字 | 菜鳥教程 (runoob.com) 一文講懂SQL外連接OUTER JOIN - 知乎 (zhihu.com) INN ...
  • 2024年1月2日,北京白鯨開源科技有限公司(以下簡稱"白鯨開源")榮幸宣佈,白鯨開源旗下產品 WhaleStudio V2.4 已成功通過與麒麟軟體有限公司旗下的銀河麒麟高級伺服器操作系統產品的相容性測試。 麒麟軟體有限公司的銀河麒麟高級伺服器操作系統(飛騰版)V10和銀河麒麟高級伺服器操作系統( ...
  • 本文介紹瞭如何通過子查詢優化深度分頁查詢,以減少回表操作帶來的性能損耗。傳統的深度分頁查詢往往會面臨嚴重的性能問題,尤其在處理大量數據時更是如此。 ...
  • 本文介紹了在雲上環境的雙集群(不跨Region不跨VPC)後臺手動部署並使用細粒度容災的主要步驟,使得用戶能快速方便得搭建起細粒度容災。 ...
  • MySQL Shell 8.0.32 for GreatSQL編譯二進位包 構建MySQL Shell 8.0.32 for GreatSQL 0. 寫在前面 之前已經寫過一篇前傳 MySQL Shell 8.0.32 for GreatSQL編譯安裝,最近再次編譯MySQL Shell二進位包時, ...
  • 北京時間2024年2月20日,中國領先的開源技術公司,白鯨開源科技有限公司(以下簡稱"白鯨開源")榮幸宣佈,該公司獲得了第六屆 "年度金猿季大型主題策劃活動" 頒發的 "2023大數據產業年度最具投資價值" 獎項。這一殊榮是對白鯨開源在大數據領域取得的卓越成就和突出貢獻的認可。 金猿季推動產業升級 ...
一周排行
    -Advertisement-
    Play Games
  • 在C#中使用SQL Server實現事務的ACID(原子性、一致性、隔離性、持久性)屬性和使用資料庫鎖(悲觀鎖和樂觀鎖)時,你可以通過ADO.NET的SqlConnection和SqlTransaction類來實現。下麵是一些示例和概念說明。 實現ACID事務 ACID屬性是事務處理的四個基本特征, ...
  • 我們在《SqlSugar開發框架》中,Winform界面開發部分往往也用到了自定義的用戶控制項,對應一些特殊的界面或者常用到的一些局部界面內容,我們可以使用自定義的用戶控制項來提高界面的統一性,同時也增強了使用的便利性。如我們Winform界面中用到的分頁控制項、附件顯示內容、以及一些公司、部門、菜單的下... ...
  • 在本篇教程中,我們學習瞭如何在 Taurus.MVC WebMVC 中進行數據綁定操作。我們還學習瞭如何使用 ${屬性名稱} CMS 語法來綁定頁面上的元素與 Model 中的屬性。通過這些步驟,我們成功實現了一個簡單的數據綁定示例。 ...
  • 是在MVVM中用來傳遞消息的一種方式。它是在MVVMLight框架中提供的一個實現了IMessenger介面的類,可以用來在ViewModel之間、ViewModel和View之間傳遞消息。 Send 接受一個泛型參數,表示要發送的消息內容。 Register 方法用於註冊某個對象接收消息。 pub ...
  • 概述:在WPF中,通過EventHandler可實現基礎和高級的UI更新方式。基礎用法涉及在類中定義事件,併在UI中訂閱以執行更新操作。高級用法藉助Dispatcher類,確保在非UI線程上執行操作後,通過UI線程更新界面。這兩種方法提供了靈活而可靠的UI更新機制。 在WPF(Windows Pre ...
  • 概述:本文介紹了在C#程式開發中如何利用自定義擴展方法測量代碼執行時間。通過使用簡單的Action委托,開發者可以輕鬆獲取代碼塊的執行時間,幫助優化性能、驗證演算法效率以及監控系統性能。這種通用方法提供了一種便捷而有效的方式,有助於提高開發效率和代碼質量。 在軟體開發中,瞭解代碼執行時間是優化程式性能 ...
  • 概述:Cron表達式是一種強大的定時任務調度工具,通過配置不同欄位實現靈活的時間規定。在.NET中,Quartz庫提供了簡便的方式配置Cron表達式,實現精準的定時任務調度。這種靈活性和可擴展性使得開發者能夠根據需求輕鬆地制定和管理定時任務,例如每天備份系統日誌或其他重要操作。 Cron表達式詳解 ...
  • 概述:.NET提供多種定時器,如System.Windows.Forms.Timer適用於UI,System.Web.UI.Timer用於Web,System.Diagnostics.Timer用於性能監控,System.Threading.Timer和System.Timers.Timer用於一般 ...
  • 問題背景 有同事聯繫我說,在生產環境上,訪問不了我負責的common服務,然後我去檢查common服務的health endpoint, 沒問題,然後我問了下異常,timeout導致的System.OperationCanceledException。那大概率是客戶端的問題,會不會是埠耗盡,用ne ...
  • 前言: 在本篇 Taurus.MVC WebMVC 入門開發教程的第四篇文章中, 我們將學習如何實現數據列表的綁定,通過使用 List<Model> 來展示多個數據項。 我們將繼續使用 Taurus.Mvc 命名空間,同時探討如何在視圖中綁定並顯示一個 Model 列表。 步驟1:創建 Model ...