百萬資料 mysql count(*)優化

魁哥2020 發表於 2022-05-15
MySQL

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