对于具有 5 亿行的 InnoDB 表（在单独的 NVMe 驱动器上），SELECT COUNT(*)大约需要 3 分钟。
SHOW ENGINE INNODB STATUS\G显示ROW OPERATIONS大约 2M reads/s，这与查询花费的时间一致。
它还显示FILE I/O大约 3,000 次读取/秒。这与 read from 类似iostat，也显示了大约 50MB/s 的读取速度。
NVMe 具有更多从磁盘读取数据的能力。
我想知道这里的瓶颈是什么？它仍然是I/O，还是 MySQL 处理？
例子
我做了一个基本的可重复测试。
CREATE TABLE test
(
id int(11) unsigned NOT NULL AUTO_INCREMENT,
Number int(11) unsigned NOT NULL,
PRIMARY KEY(id)
) ENGINE=InnoDB
INSERT INTO test (Number) SELECT * FROM seq_1_to_500000000;
Query OK, 500000000 rows affected (20 min 2.846 sec)
Records: 500000000 Duplicates: 0 Warnings: 0
SELECT COUNT(*) FROM test;
+-----------+
| COUNT(*) |
+-----------+
| 500000000 |
+-----------+
1 row in set (1 min 20.234 sec)
重启后MySQL
innodb_buffer_pool_load_at_startup=OFF
innodb_buffer_pool_dump_at_shutdown=OFF
query_cache_type=0
query_cache_size=0
在没有任何其他活动的情况下，我得到了
SELECT COUNT(*) FROM test;
+-----------+
| COUNT(*) |
+-----------+
| 500000000 |
+-----------+
1 row in set (1 min 13.245 sec)
关键问题：在典型的 NVMe 上运行此查询是最快的吗？
配置：
- 50GB 缓冲池。该
ibd文件为 17.7GB。 - CPU 为 16/32 核/线程。
innodb_io_threads没有效果。我尝试了 4（默认）和 64（最大）。
和
SHOW GLOBAL STATUS LIKE 'innodb_buffer_pool%';
+-----------------------------------------+-------------+
| Variable_name | Value |
+-----------------------------------------+-------------+
| Innodb_buffer_pool_dump_status | |
| Innodb_buffer_pool_load_status | |
| Innodb_buffer_pool_resize_status | |
| Innodb_buffer_pool_load_incomplete | OFF |
| Innodb_buffer_pool_pages_data | 874527 |
| Innodb_buffer_pool_bytes_data | 14328250368 |
| Innodb_buffer_pool_pages_dirty | 0 |
| Innodb_buffer_pool_bytes_dirty | 0 |
| Innodb_buffer_pool_pages_flushed | 0 |
| Innodb_buffer_pool_pages_free | 2351473 |
| Innodb_buffer_pool_pages_made_not_young | 0 |
| Innodb_buffer_pool_pages_made_young | 0 |
| Innodb_buffer_pool_pages_misc | 0 |
| Innodb_buffer_pool_pages_old | 322843 |
| Innodb_buffer_pool_pages_total | 3226000 |
| Innodb_buffer_pool_pages_lru_flushed | 0 |
| Innodb_buffer_pool_read_ahead_rnd | 682843 |
| Innodb_buffer_pool_read_ahead | 0 |
| Innodb_buffer_pool_read_ahead_evicted | 0 |
| Innodb_buffer_pool_read_requests | 56901439 |
| Innodb_buffer_pool_reads | 874396 |
| Innodb_buffer_pool_wait_free | 0 |
| Innodb_buffer_pool_write_requests | 515 |
+-----------------------------------------+-------------+
23 rows in set (0.001 sec)
关于表的附加信息
SHOW TABLE STATUS LIKE 'test';
+------+--------+---------+------------+-----------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+------------------+-----------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment | Max_index_length | Temporary |
+------+--------+---------+------------+-----------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+------------------+-----------+
| test | InnoDB | 10 | Dynamic | 499216334 | 25 | 12859736064 | 0 | 0 | 7340032 | 500000001 | 2022-07-29 00:10:49 | 2022-07-29 00:32:52 | NULL | utf8_general_ci | NULL | | | 0 | N |
+------+--------+---------+------------+-----------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+------------------+-----------+
1 row in set (0.001 sec)
EXPLAIN FORMAT=JSON SELECT COUNT(*) FROM test;
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| {
"query_block": {
"select_id": 1,
"table": {
"table_name": "test",
"access_type": "index",
"key": "PRIMARY",
"key_length": "4",
"used_key_parts": ["id"],
"rows": 499216334,
"filtered": 100,
"using_index": true
}
}
} |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.000 sec)
SHOW VARIABLES LIKE 'innodb_io%';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| innodb_io_capacity | 32000 |
| innodb_io_capacity_max | 64000 |
+------------------------+-------+
简单的问题；不是一个简单的答案。在我闲逛的时候忍受我...
SELECT COUNT(*) FROM tbl将使用“最小”索引。请提供SHOW CREATE TABLE，以便我们讨论细节。还提供SHOW TABLE STATUS，以便我们讨论尺寸。假设选择
INDEX的是磁盘上的 4GB，将其除以 16K 以获得需要读取的 256K 块。一些块可能已经在缓存中。唉，这些数字与您提供的任何内容都不匹配。另一种查看工作量的方法是在 SELECT 之前和之后运行它：
然后减去数字。有些是字节数；有些是块数。两者之间有16K的系数，应该很明显看出哪个是哪个。有问题的可能是
Innodb_buffer_pool_pages_data. 再次执行该过程；看看这个数字是如何变化的。到目前为止，我还没有直接回答你的问题。让我们看看我能不能再靠近一点……
COUNT(*)。]每秒速率 = RPS
建议为您的 my.cnf [mysqld] 部分考虑以尽量减少瓶颈
可能还有其他机会需要考虑。