MySQLcount(*)很慢

Server version: 5.7.24-log MySQL Community Server (GPL)

select count(*) from api_runtime_log;

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

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

簡單介紹下原理：

• 聚簇索引：每一個 InnoDB 儲存引擎下的表都有一個特殊的索引用來儲存每一行的資料，稱為聚簇索引（通常都為 主鍵），聚簇索引實際儲存了 B-Tree 索引和行資料，所以大小實際上約等於為表資料量
• 二級索引：除了聚集索引，表上其他的索引都是二級索引，索引中僅僅儲存了對應索引列及主鍵列

那麼如何解決呢？

答案就是：建二級索引。

• 走聚集索引：從第一頁翻到最後一頁，知道總頁數；

• 走二級索引：通過目錄直接知道總頁數。

建立二級索引後，再次執行 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)

測試過程如下：

測試結果如下：

查詢當前記憶體緩衝區狀態，結果為空證明不快取測試表資料。

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

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

About Me