在大型 MySQL InnoDB 表上進行全計數查詢真的那麼慢嗎？

Question

我們有一個包含數百萬個條目的大表。完整計數非常慢，請參考下面的程式碼。這對於 MySQL InnoDB 表來說很常見嗎？難道就沒有辦法加速這個過程嗎？ 即使使用查詢緩存，它仍然“慢”。 我還想知道，為什麼具有 2.8 mio 條目的“通信”表的計數比具有 4.5 mio 條目的“事務”表的計數慢。

我知道使用 where 子句會更快。我只是想知道糟糕的表現是否正常。

我們使用 Amazon RDS MySQL 5.7 和 m4.xlarge（4 個 CPU、16 GB RAM、500 GB 儲存）。我也已經嘗試過使用更多 CPU 和 RAM 的更大實例，但查詢時間沒有太大的變化。

mysql> SELECT COUNT(*) FROM transaction;
+----------+
| COUNT(*) |
+----------+
|  4569880 |
+----------+
1 row in set (1 min 37.88 sec)

mysql> SELECT COUNT(*) FROM transaction;
+----------+
| count(*) |
+----------+
|  4569880 |
+----------+
1 row in set (1.44 sec)

mysql> SELECT COUNT(*) FROM communication;
+----------+
| count(*) |
+----------+
|  2821486 |
+----------+
1 row in set (2 min 19.28 sec)

#

Answer 1

這是使用支援 多版本並發控制 (MVCC)。

InnoDB 允許您的查詢在交易中隔離，而不會阻塞正在讀取和寫入資料行的其他並發客戶端。這些並發更新不會影響您的事務的資料視圖。

但是，考慮到在進行計數時許多行正在新增或刪除，表中的行數是多少？答案是模糊的。

您的交易不應能夠「檢視」交易開始後所建立的行版本。同樣，即使其他人請求刪除行，您的事務也應該對行進行計數，但他們是在您的事務開始後這樣做的。

答案是，當您執行SELECT COUNT(*) 或任何其他類型的需要檢查多行的查詢時，InnoDB 必須訪問每一行，查看該行的目前版本對資料庫的事務視圖可見，並在可見時對其進行計數。

在不支援交易或並發更新的資料表（例如 MyISAM）中，儲存引擎將行總數保留為資料表的元資料。此儲存引擎無法支援多個執行緒同時更新行，因此總數不太模糊。因此，當您從MyISAM 表請求SELECT COUNT(*) 時，它只會傳回記憶體中的行數（但如果您執行SELECT COUNT(*) 則這沒有用） 使用WHERE 子句按某些條件對行的某些子集進行計數，因此在這種情況下它必須實際對它們進行計數）。

總的來說，大多數人認為 InnoDB 對並發更新的支援非常值得，而且他們願意犧牲 SELECT COUNT(*) 的最佳化。

Answer 2

除了比爾所說的之外...

最小索引

InnoDB 選擇「最小」索引來執行 COUNT(*)。可能所有communication的索引都大於transaction的最小索引，因此存在時間差。在判斷索引的大小時，請將 PRIMARY KEY 欄位與任何輔助索引一起包含在內：

PRIMARY KEY(id),   -- INT (4 bytes)
INDEX(flag),       -- TINYINT (1 byte)
INDEX(name),       -- VARCHAR(255) (? bytes)

為了測量大小，PRIMARY KEY 很大，因為它包含（由於群集）表的所有欄位。 INDEX(flag) 是「5 個位元組」。 INDEX(name) 平均可能有幾十個位元組。 SELECT COUNT(*) 將明確選擇 INDEX(flag)。

顯然交易有一個「小」索引，但通訊沒有。

TEXT/BLOG 列有時會「不記錄」儲存。因此，它們不計入 PK 指數的大小。

查詢快取

如果「查詢快取」打開，查詢的第二次運行可能比第一次快得多。但這只是在表格沒有變化的情況下發生的。由於對錶的任何更改都會使該表的所有 QC 條目失效，因此 QC 在生產系統中很少有用。我所說的「更快」是指大約 0.001 秒；不是 1.44 秒。

1m38s 和 1.44s 之間的差異可能是由於 buffer_pool（InnoDB 的通用快取區域）中快取的內容所致。第一次運行可能在 RAM 中找不到任何「最小」索引，因此它執行了大量 I/O，花費 98 秒來獲取該索引的所有 450 萬行。第二次運行發現所有資料都緩存在 buffer_pool 中，因此它以 CPU 速度運行（無 I/O），因此速度快得多。

夠好

在這種情況下，我根本質疑執行 COUNT(*) 的必要性。請注意您如何說“2.8 mio 條目”，就好像 2 個有效數字“足夠好”一樣。如果您在 UI 上向使用者顯示計數，這不是「夠好」嗎？如果是這樣，效能的解決方案是每天計數一次並將其儲存在某個地方。這將允許立即存取“足夠好”的值。

還有其他技術。一是使用活動代碼或某種形式的總表來保持計數器更新。

向其丟硬體

#

您已經發現更改硬體沒有幫助。

• 98 的運行速度與 RDS 的任何 I/O 產品的運行速度一樣快。
• 1.44s 的運行速度與任何 RDS CPU 的運行速度一樣快。
• MySQL（及其變體）每次查詢不會使用多個 CPU。
• 您有足夠的RAM，因此整個「小」索引都可以放入buffer_pool 中，直到您的第二次SELECT COUNT(*)..（RAM 太少會導致第二次運行非常慢） .)