MySQL的簡單COUNT查詢（無WHERE子句）

    在SQL查詢中一個比較常見查詢應當是COUNT操作。對於帶WHERE子句的查詢沒太多可說的，有索引就用索引，沒有索引——嘿嘿，累壞機器啊。
    而不帶WHERE子句簡單COUNT查詢的，對於不同儲存引擎則有一些不同。

 

    假設一個需求：
    我們有一個表存放了大量使用者的資訊。某一個頁面需要顯示使用者資料列表。我們自然不能一下子把所有資料一次性SELECT出來放到頁面上，因此需要支援翻頁，比如每頁只顯示100個。當然這個很容易通過LIMIT實現。但是，頁面支援翻頁以後，就引出另一個需求，我們需要顯示當前是第幾頁以及一共有多少頁。這就需要知道目前使用者的總數，我們也許就需要執行這樣一個簡單的COUNT查詢SQL：SELECT COUNT(*) FROM t_userinfo

 

    簡單COUNT查詢在不同儲存引擎上的對比

    最初的想法是：對於這種沒有WHERE子句的COUNT查詢，MySQL應當可以迅速返回一個值，而無須去數一共多少行。
    在兩種引擎上EXPLAIN以後，發現不完全如此。
    對於InnoDB引擎，EXPLAIN結果如下

mysql> EXPLAIN SELECT COUNT(*) FROM t_userinfo;
+----+-------------+------------------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table            | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+-------------+------------------+-------+---------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | t_userinfo       | index | NULL          | PRIMARY |       4 | NULL |  481 | Using index |
+----+-------------+------------------+-------+---------------+---------+---------+------+------+-------------+

    很明顯，因為t_userinfo裡面有索引，所以InnoDB使用了索引，但即使如此，還是遍曆了索引以後才返回結果。

    對於MyISAM引擎，EXPLAIN結果如下

mysql> EXPLAIN SELECT COUNT(*) FROM t_userinfo;
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                        |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
|  1 | SIMPLE      | NULL  | NULL | NULL          | NULL |    NULL | NULL | NULL | Select tables optimized away |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+

     這個輸出的結果裡略微有點奇怪，Extra列輸出了"Select tables optimized away"語句。這個在MySQL的手冊裡面沒有任何提及，不過看其他各列的資料大概能猜到意思：SELECT操作已經最佳化到不能再最佳化了（MySQL根本沒有遍曆表或索引就返回資料了）。

    在MySQL官方網站翻到兩段相關的描述，印證了上述觀點。原文如下：

    For explains on simple count queries (i.e. explain select count(*) from people) the extra section will read "Select tables optimized away." This is due to the fact that MySQL can read the result directly from the table internals and therefore does not need to perform the select.

     …… ……

    SELECT count(*) is so common so it is partly optimized away.
    If you are using MyISAM or HEAP tables it reads the information directly from the table information which is lightning fast and is actually the information that show table status displays.
    But for InnoDB tables it actually has to perform an index scan on the primary index which can take a while depending on size of table, innodb_buffer_size, hardware etc.

 

    相關建議

    那麼，如果你的COUNT查詢只打算在MyISAM或HEAP引擎上執行，直接使用上面的語句沒有效能問題，無需特殊的最佳化。而如果在InnoDB上執行，或者你不確定以後會不會在InnoDB上執行的話，也許需要考慮一下其中的效能問題。

    如果不是要求很精確的統計（比如本文最初提出的需求），一個建議是使用如下的語句：

SHOW TABLE STATUS LIKE 't_userinfo';

    對於MyISAM或者HEAP引擎，該查詢返回的行數是準確值；對於InnoDB，該查詢返回的行數是一個近似值（事實上，對於InnoDB，該查詢返回的各欄位值絕大多數是近似值）。

    對於某個案例，InnoDB引擎下SHOW TABLE STATUS查詢返回行數是519；而SELECT COUNT(*)返回行數是476。由於該表中有一個自增欄位Fid。SELECT MAX(Fid)返回的結果是488。
    對於另一個樣本，InnoDB引擎下SHOW TABLE STATUS查詢返回行數是487；而SELECT COUNT(*)返回行數是480。由於該表中有一個自增欄位Fid。SELECT MAX(Fid)返回的結果是490。
    可以看到，基本上這個值是很接近的。想瞭解為什麼產生這樣的近似值，可以閱讀InnoDB相關文檔。

    當然，由於"SHOW TABLE STATUS"語句是MySQL特有的語句，而不是標準SQL語句。出於某些考量，這個方案無法接受，那麼為了效能另一個建議是建立一個計數表，存放各種COUNT計數。
    一個較通用的計數表欄位如下：

{table_name, where_clause, group_clause, count}