Open7
MySQL 9.1でselect countは速くなっているのか？
Matsukura Yuki背景
以下の記事を見つけました。
問題
手元のRDBはAuroraではないですが、手元で運用しているMySQL 9.1.0において、550万レコードあるテーブルのselect countが遅い。
計測
550万件が2.1秒かかる。遅い。
Server version: 9.1.0 MySQL Community Server - GPL
MySQL [corporation]> select count(*) from corporations;
+----------+
| count(*) |
+----------+
| 5494084 |
+----------+
1 row in set (2.102 sec)
MySQL [corporation]> select count(id) from corporations;
+-----------+
| count(id) |
+-----------+
| 5494084 |
+-----------+
1 row in set (2.183 sec)
MySQL [corporation]> show table status where Name = 'corporations' \G
*************************** 1. row ***************************
Name: corporations
Engine: InnoDB
Version: 10
Row_format: Dynamic
Rows: 5255557
Avg_row_length: 228
Data_length: 1198522368
Max_data_length: 0
Index_length: 1232781312
Data_free: 7340032
Auto_increment: 7637086
Create_time: 2024-07-05 08:08:03
Update_time: NULL
Check_time: NULL
Collation: utf8mb4_general_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.002 sec)
環境
自宅サーバで実行しているのですが、そんなに環境は遅くないと思うんですけどね。
% cat /proc/cpuinfo
11th Gen Intel(R) Core(TM) i7-11390H @ 3.40GHz
% sudo hdparm -Tt /dev/nvme0n1p1
/dev/nvme0n1p1:
Timing cached reads: 41134 MB in 2.00 seconds = 20603.49 MB/sec
Timing buffered disk reads: 1074 MB in 0.43 seconds = 2513.22 MB/sec
同じハードウェアで、MySQL 9.1.0のインスタンスで160万レコードで試してみたところ、速そう。
（500万レコードを準備するのが大変なのでちょっと少ないですが。）
Server version: 9.1.0 MySQL Community Server - GPL
mysql> select count(*) from table1;
+----------+
| count(*) |
+----------+
| 1600000 |
+----------+
1 row in set (0.20 sec)
では、なぜ運用しているデータでは遅いのか不思議です。
optimize tableをしてみました。（実際にはrecreate + analyze）
MySQL [corporation]> optimize table corporations;
+--------------------------+----------+----------+-------------------------------------------------------------------+
| Table | Op | Msg_type | Msg_text |
+--------------------------+----------+----------+-------------------------------------------------------------------+
| corporation.corporations | optimize | note | Table does not support optimize, doing recreate + analyze instead |
| corporation.corporations | optimize | status | OK |
+--------------------------+----------+----------+-------------------------------------------------------------------+
2 rows in set (24 min 24.936 sec)
24%ほど高速化しました。
MySQL [corporation]> select count(id) from corporations;
+-----------+
| count(id) |
+-----------+
| 5494084 |
+-----------+
1 row in set (1.612 sec)
indexがメモリに乗り切っていない可能性がある。
デフォルトのinnodb_buffer_pool_sizeは128MB。
MySQL [corporation]> SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
+-------------------------+-----------+
| Variable_name | Value |
+-------------------------+-----------+
| innodb_buffer_pool_size | 134217728 |
+-------------------------+-----------+
1 row in set (0.003 sec)
1GBに増やしてみる。
MySQL [corporation]> SET GLOBAL innodb_buffer_pool_size = 1*1024*1024*1024;
Query OK, 0 rows affected (0.001 sec)
再度、countを実行。
MySQL [corporation]> select count(id) from corporations;
+-----------+
| count(id) |
+-----------+
| 5494084 |
+-----------+
1 row in set (0.501 sec)
最初から比較すると77%高速化した。
Matsukura Yukiinnodb_buffer_pool_sizeが寄与していることが判明したので、最適な値を調査してみる。
indexのサイズがどの程度なのかを調べてみる。
MySQL [corporation]> SELECT
-> index_name,
-> ROUND(stat_value * @@innodb_page_size / 1024 / 1024, 2) AS size_mb
-> FROM mysql.innodb_index_stats
-> WHERE database_name = 'corporation'
-> AND table_name = 'corporations'
-> AND stat_name = 'size'
-> ORDER BY size_mb DESC;
+-----------------------------------------------------+---------+
| index_name | size_mb |
+-----------------------------------------------------+---------+
| PRIMARY | 1191.00 |
| index_corporations_on_name | 400.00 |
| index_corporations_on_prefecture_name_and_city_name | 259.00 |
| index_corporations_on_corporate_number | 239.00 |
| index_corporations_on_post_code | 209.98 |
| FTS_DOC_ID_INDEX | 114.70 |
+-----------------------------------------------------+---------+
6 rows in set (0.001 sec)
一番大きいのがPRIMARYキーの1.2GBです。
小さいのは、数百MBです。この小さめのindexを使ってクエリーを投げてみます。
MySQL [corporation]> select count(name) from corporations;
+-------------+
| count(name) |
+-------------+
| 5494084 |
+-------------+
1 row in set (0.560 sec)
MySQL [corporation]> explain select count(name) from corporations;
+----+-------------+--------------+------------+-------+---------------+----------------------------+---------+------+---------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------------+------------+-------+---------------+----------------------------+---------+------+---------+----------+-------------+
| 1 | SIMPLE | corporations | NULL | index | NULL | index_corporations_on_name | 1022 | NULL | 5409961 | 100.00 | Using index |
+----+-------------+--------------+------------+-------+---------------+----------------------------+---------+------+---------+----------+-------------+
1 row in set, 1 warning (0.001 sec)
innodb_buffer_pool_sizeは1GBで設定してあるので、メモリに乗り切っているはずなのに遅いです。
次に、innodb_buffer_pool_sizeを2GBに設定して実行してみます。
MySQL [corporation]> SET GLOBAL innodb_buffer_pool_size = 2*1024*1024*1024;
Query OK, 0 rows affected (0.001 sec)
MySQL [corporation]> select count(id) from corporations;
+-----------+
| count(id) |
+-----------+
| 5494084 |
+-----------+
1 row in set (0.496 sec)
innodb_buffer_pool_sizeを2GBに増加しても変化なしです。
Matsukura Yuki次に、適切にindexが使われているのかを確認してみます。
MySQL [corporation]> EXPLAIN FORMAT=JSON SELECT COUNT(id) FROM corporations\G
*************************** 1. row ***************************
EXPLAIN: {
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "591586.38"
},
"table": {
"table_name": "corporations",
"access_type": "index",
"key": "index_corporations_on_corporate_number",
"used_key_parts": [
"corporate_number"
],
"key_length": "8",
"rows_examined_per_scan": 5409961,
"rows_produced_per_join": 5409961,
"filtered": "100.00",
"using_index": true,
"cost_info": {
"read_cost": "50590.28",
"eval_cost": "540996.10",
"prefix_cost": "591586.38",
"data_read_per_join": "82G"
},
"used_columns": [
"id"
]
}
}
}
1 row in set, 1 warning (0.001 sec)
なぜかprimaryではなく、index_corporations_on_corporate_numberが使われています。
強制的にPRIMARYを使うようにヒントを与えます。
MySQL [corporation]> SELECT COUNT(id) FROM corporations FORCE INDEX (PRIMARY);
+-----------+
| COUNT(id) |
+-----------+
| 5494084 |
+-----------+
1 row in set (1.359 sec)
MySQL [corporation]>
MySQL [corporation]> SELECT COUNT(id) FROM corporations FORCE INDEX (PRIMARY);
+-----------+
| COUNT(id) |
+-----------+
| 5494084 |
+-----------+
1 row in set (0.151 sec)
MySQL [corporation]>
MySQL [corporation]>
MySQL [corporation]> SELECT COUNT(id) FROM corporations FORCE INDEX (PRIMARY);
+-----------+
| COUNT(id) |
+-----------+
| 5494084 |
+-----------+
1 row in set (0.143 sec)
MySQL [corporation]>
MySQL [corporation]>
MySQL [corporation]> SELECT COUNT(id) FROM corporations FORCE INDEX (PRIMARY);
+-----------+
| COUNT(id) |
+-----------+
| 5494084 |
+-----------+
1 row in set (0.140 sec)
0.14秒で返ってくるようになりました。
PRIMARYキーを使うようになったということは、innodb_buffer_pool_sizeが影響するはずなので検証します。
1GBに設定してみます。
MySQL [corporation]> SET GLOBAL innodb_buffer_pool_size = 1*1024*1024*1024;
Query OK, 0 rows affected (0.006 sec)
MySQL [corporation]> SHOW STATUS WHERE Variable_name='InnoDB_buffer_pool_resize_status';
+----------------------------------+----------------------------------------------------+
| Variable_name | Value |
+----------------------------------+----------------------------------------------------+
| Innodb_buffer_pool_resize_status | Completed resizing buffer pool at 241120 1:56:47. |
+----------------------------------+----------------------------------------------------+
1 row in set (0.005 sec)
MySQL [corporation]> SELECT COUNT(id) FROM corporations FORCE INDEX (PRIMARY);
+-----------+
| COUNT(id) |
+-----------+
| 5494084 |
+-----------+
1 row in set (0.928 sec)
MySQL [corporation]> SELECT COUNT(id) FROM corporations FORCE INDEX (PRIMARY);
+-----------+
| COUNT(id) |
+-----------+
| 5494084 |
+-----------+
1 row in set (0.925 sec)
MySQL [corporation]> SELECT COUNT(id) FROM corporations FORCE INDEX (PRIMARY);
+-----------+
| COUNT(id) |
+-----------+
| 5494084 |
+-----------+
1 row in set (0.922 sec)
遅くなりました。メモリを大量に使うようです。なので、MySQL9.1のOptimizerはメモリサイズを優先して小さいindexを使ってちょっと時間をかける方向で探索しに行っていることが判明しました。
Matsukura Yukiということは元の設定でPRIMARYキーを使った場合はどのくらいかを見てみます。
innodb_buffer_pool_sizeをデフォルトの128MBに戻してPRIMARYキーを使ってselect countを実行してみます。
MySQL [corporation]> SET GLOBAL innodb_buffer_pool_size = 128*1024*1024;
Query OK, 0 rows affected (0.001 sec)
MySQL [corporation]> SELECT COUNT(id) FROM corporations FORCE INDEX (PRIMARY);
+-----------+
| COUNT(id) |
+-----------+
| 5494084 |
+-----------+
1 row in set (0.717 sec)
MySQL [corporation]> SELECT COUNT(id) FROM corporations FORCE INDEX (PRIMARY);
+-----------+
| COUNT(id) |
+-----------+
| 5494084 |
+-----------+
1 row in set (0.710 sec)
MySQL [corporation]> SELECT COUNT(id) FROM corporations FORCE INDEX (PRIMARY);
+-----------+
| COUNT(id) |
+-----------+
| 5494084 |
+-----------+
1 row in set (0.749 sec)
0.7秒なのでまぁ許容範囲。
indexヒント無しで実行してみる。
MySQL [corporation]> SELECT COUNT(id) FROM corporations;
+-----------+
| COUNT(id) |
+-----------+
| 5494084 |
+-----------+
1 row in set (1.644 sec)
MySQL [corporation]> SELECT COUNT(id) FROM corporations;
+-----------+
| COUNT(id) |
+-----------+
| 5494084 |
+-----------+
1 row in set (1.640 sec)
MySQL [corporation]> SELECT COUNT(id) FROM corporations;
+-----------+
| COUNT(id) |
+-----------+
| 5494084 |
+-----------+
1 row in set (1.644 sec)
1.6秒かかる。
オンラインで提供するサービスだとこの時間はちょっと遅いのでアプリケーション側から force indexを使うか、countをキャッシュする。