在 InnoDB 下的 COUNT(*)...

MyISAM 在讀取時不會有其他人可以寫入,所以 COUNT(*) 很容易被處理。而 InnoDB 的 COUNT(*) 在不同的 transaction 裡可能會不一樣,如果硬要處理的話會變得複雜,所以目前 InnoDB 的作法是 scan 一次 (出自「Limits on InnoDB Tables」):

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. To process a SELECT COUNT(*) FROM t statement, InnoDB scans an index of the table, which takes some time if the index is not entirely in the buffer pool. If your table does not change often, using the MySQL query cache is a good solution. To get a fast count, you have to use a counter table you create yourself and let your application update it according to the inserts and deletes it does. If an approximate row count is sufficient, SHOW TABLE STATUS can be used. See Section 14.3.14.1, "InnoDB Performance Tuning Tips".

在「Easy SELECT COUNT(*) with split()」這篇提到 InnoDB 下 COUNT(*) 這件事情,當你不需要絕對精確的值時,可以利用 INFORMATION_SCHEMA.TABLES 或是 SHOW TABLE STATUS 的資訊來判斷。

One thought on “在 InnoDB 下的 COUNT(*)...”

Leave a Reply

Your email address will not be published. Required fields are marked *