百萬數據 mysql count（*）優化

一、故事背景
有一張 500w 左右的表做 select count(*) 速度特別慢。

二、原 SQL 分析
Server version: 5.7.24-log MySQL Community Server (GPL)

SQL 如下，僅僅就是統計 api_runtime_log 這張表的行數，一條簡單的不能再簡單的 SQL：

select count(*) from api_runtime_log;

我們先去運行一下這條 SQL，可以看到確實運行很慢，要 40 多秒左右，確實很不正常~

mysql> select count(*) from api_runtime_log;
+----------+
| count(*) |
+----------+
| 5718952 |
+----------+
1 row in set (42.95 sec)

我們再去看下表結構，看上去貌似也挺正常的~存在主鍵，表引擎也是 InnoDB，字元集也沒問題。

CREATE TABLE `api_runtime_log` (
`BelongXiaQuCode` varchar(50) DEFAULT NULL,
`OperateUserName` varchar(50) DEFAULT NULL,
`OperateDate` datetime DEFAULT NULL,
`Row_ID` int(11) DEFAULT NULL,
`YearFlag` varchar(4) DEFAULT NULL,
`RowGuid` varchar(50) NOT NULL,
......
`apiid` varchar(50) DEFAULT NULL,
`apiname` varchar(50) DEFAULT NULL,
`apiguid` varchar(50) DEFAULT NULL,
PRIMARY KEY (`RowGuid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

三、執行計劃
通過執行計劃，我們看下是否可以找到什麼問題點。

mysql> explain select count(*) from api_runtime_log \G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: api_runtime_log
partitions: NULL
type: index
possible_keys: NULL
key: PRIMARY
key_len: 152
ref: NULL
rows: 5718952
filtered: 100.00
Extra: Using index
 
可以看到，查詢走的是 PRIMARY，也就是主鍵索引。貌似也沒有什麼問題，走索引了呀！那麼是不是真的就沒問題呢？

四、原理
為了找到答案，通過 Google 查找 MySQL 下 select count(*) 的原理，找到了答案。這邊省略過程，直接上結果。
簡單介紹下原理：

聚簇索引：每一個 InnoDB 存儲引擎下的表都有一個特殊的索引用來保存每一行的數據，稱為聚簇索引（通常都為主鍵），聚簇索引實際保存了 B-Tree 索引和行數據，所以大小實際上約等於為表數據量
二級索引：除了聚集索引，表上其他的索引都是二級索引，索引中僅僅存儲了對應索引列及主鍵列
在 InnoDB 存儲引擎中，count(*) 函數是先從記憶體中讀取數據到記憶體緩衝區，然後進行掃描獲得行記錄數。這裡 InnoDB 會優先走二級索引；如果同時存在多個二級索引，會選擇key_len 最小的二級索引；如果不存在二級索引，那麼會走主鍵索引；如果連主鍵都不存在，那麼就走全表掃描！

這裡我們由於走的是主鍵索引，所以 MySQL 需要先把整個主鍵索引讀取到記憶體緩衝區，這是個從磁碟讀寫到記憶體的過程，而且主鍵索引基本等於整個表數據量（10GB+），所以非常耗時！

那麼如何解決呢？

答案就是：建二級索引。

因為二級索引只包含對應的索引列及主鍵列，所以體積非常小。在 select count(*) 的查詢過程中，只需要將二級索引讀取到記憶體緩衝區，只有幾十 MB 的數據量，所以速度會非常快。

舉個形象的比喻，我們想知道一本書的頁數：

走聚集索引：從第一頁翻到最後一頁，知道總頁數；
走二級索引：通過目錄直接知道總頁數。
五、驗證
創建二級索引後，再次執行 SQL 及查看執行計劃。

mysql> create index idx_rowguid on api_runtime_log(rowguid);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> select count(*) from api_runtime_log;
+----------+
| count(*) |
+----------+
| 5718952 |
+----------+
1 row in set (0.89 sec)

mysql> explain select count(*) from api_runtime_log \G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: api_runtime_log
partitions: NULL
type: index
possible_keys: NULL
key: idx_rowguid
key_len: 152
ref: NULL
rows: 5718952
filtered: 100.00
Extra: Using index
1 row in set, 1 warning (0.00 sec)
 
可以看到添加二級索引後，確實速度明顯變快，而且執行計劃也變成了走二級索引。至此這個問題其實已經解決了，就是由於表上缺少二級索引導致。

六、深入測試
為了進一步驗證上述的推論，所以就做瞭如下的測試。

測試過程如下：

通過 sysbench 創建了一張 500W 的測試表 sbtest1，表上僅僅包含一個主鍵索引，表大小為 1125MB；
調整部分 MySQL 參數，重啟 MySQL，保證目前 innodb buffer pool (記憶體緩衝區) 中為空，不緩存任何數據；
執行 select count(*)，理論上走主鍵索引，查看當前記憶體緩衝區中緩存的數據量（理論上會緩存整個聚簇索引）；
在測試表 sbtest1 上添加二級索引，索引大小為 55MB；
再次重啟 MySQL，保證記憶體緩衝區為空；
再次執行 select count(*)，理論上走二級索引；
再次查看記憶體緩衝區中緩存的數據量（理論上只會緩存二級索引）。
測試結果如下：

1. 聚簇索引

查詢當前記憶體緩衝區狀態，結果為空證明不緩存測試表數據。

mysql> select * from sys.innodb_buffer_stats_by_table where object_schema = 'test';
Empty set (1.92 sec)

mysql> select count(*) from test.sbtest1;
+----------+
| count(*) |
+----------+
| 5188434 |
+----------+
1 row in set (5.52 sec)
 
再次查看記憶體緩衝區，發現緩存了 sbtest1 表上 1G 多的數據，基本等於整個表數據量。

mysql> select * from sys.innodb_buffer_stats_by_table where object_schema = 'test' \G;
*************************** 1. row ***************************
object_schema: test
object_name: sbtest1
allocated: 1.08 GiB
data: 1.01 GiB
pages: 71081
pages_hashed: 0
pages_old: 28119
rows_cached: 5189798
 
最後我們再來看下執行計劃，確實走的是主鍵索引，放在最後執行是為了避免影響緩衝區。

mysql> explain select count(*) from test.sbtest1 \G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: sbtest1
partitions: NULL
type: index
possible_keys: NULL
key: PRIMARY
key_len: 4
ref: NULL
rows: 5117616
filtered: 100.00
Extra: Using index
 
2. 二級索引

創建二級索引 idx_id，查看 sbtest1 表上主鍵索引與二級索引的數據量。

mysql> create index idx_id on sbtest1(id);
Query OK, 0 rows affected (12.97 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> SELECT sum(stat_value) pages ,index_name ,
(round((sum(stat_value) * @@innodb_page_size)/1024/1024)) as MB
FROM mysql.innodb_index_stats
WHERE table_name = 'sbtest1'
AND database_name = 'test'
AND stat_description = 'Number of pages in the index'
GROUP BY index_name;
+-------+------------+------+
| pages | index_name | MB |
+-------+------------+------+
| 72000 | PRIMARY | 1125 |
| 3492 | idx_id | 55 |
+-------+------------+------+
 
重啟 MySQL，再次查看緩衝區同樣為空，證明沒有緩存測試表上的數據。

mysql> select * from sys.innodb_buffer_stats_by_table where object_schema = 'test';
Empty set (1.49 sec)

mysql> select count(*) from test.sbtest1;
+----------+
| count(*) |
+----------+
| 5188434 |
+----------+
1 row in set (2.92 sec)
 
再次查看記憶體緩衝區，發現僅僅緩存了 sbtest1 表上的 50M 數據，約等於二級索引的數據量。

mysql> select * from sys.innodb_buffer_stats_by_table where object_schema = 'test' \G;
*************************** 1. row ***************************
object_schema: test
object_name: sbtest1
allocated: 49.48 MiB
data: 46.41 MiB
pages: 3167
pages_hashed: 0
pages_old: 1575
rows_cached: 2599872
 
最後確認下執行計劃，確實走的是二級索引。

mysql> explain select count(*) from test.sbtest1 \G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: sbtest1
partitions: NULL
type: index
possible_keys: NULL
key: idx_id
key_len: 4
ref: NULL
rows: 5117616
filtered: 100.00
Extra: Using index
 
七、案例總結
從上述這個測試結果可以看出，和之前的推論基本吻合。

如果 select count(*) 走的是主鍵索引，那麼會緩存整個表數據，大量查詢時間會花費在讀取表數據到緩衝區。

如果存在二級索引，那麼只需要讀取索引頁到緩衝區即可，速度自然快。

另：項目上由於磁碟性能層次不齊，所以當遇上這種情況時，性能較差的磁碟更會放大這個問題；一張超級大表，統計行數時如果走了主鍵索引，後果可想而知

八、優化建議
此次測試過程中我們僅僅模擬是百萬數據量，此時我們通過二級索引統計表行數，只需要讀取幾十 M 的數據量，就可以得到結果。

那麼當我們的表數據量是上千萬，甚至上億時呢。此時即便是最小的二級索引也是 幾百 M、過 G 的數據量，如果繼續通過二級索引來統計行數，那麼速度就不會如此迅速了。

這個時候可以通過避免直接 select count(*) from table 來解決，方法較多，例如：

使用 MySQL 觸發器 + 統計表實時計算表數據量；
使用 MyISAM 替換 InnoDB，因為 MyISAM 自帶計數器，壞處就不多說了；
通過 ETL 導入表數據到其他更高效的異構環境中進行計算；
升級到 MySQL 8 中，使用並行查詢，加快檢索速度。
當然，什麼時候 InnoDB 存儲引擎可以直接實現計數器的功能就好了！
————————————————
版權聲明：本文為CSDN博主「MariaOzawa」的原創文章，遵循CC 4.0 BY-SA版權協議，轉載請附上原文出處鏈接及本聲明。
原文鏈接：https://blog.csdn.net/MariaOzawa/article/details/115603713