一、故事背景
有一張 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