为什么即使整个数据都在缓冲池中，InnoDB对全表扫描的速度却如此缓慢？

Question

环境

• AMD Ryzen 9 5950X，128 AMD 3200 AMD双通道
• 3GB/s+读写的数据中心NVMe SSD
• MariaDB 10.6.3 x64
• Windows 2019 (不过，Debian上也有同样的问题)
• 专用机器，不运行其他任务。

my.ini

[mysqld]
default-storage-engine=INNODB
log-output=NONE
general-log=0
general_log_file="mariadb.log"
slow-query-log=0
query_cache_type=OFF
query_cache_size=0
innodb_buffer_pool_size=64G

DDL

CREATE TABLE testinnodb
(
    a INTEGER NOT NULL, b INTEGER NOT NULL, c INTEGER NOT NULL,
    i FLOAT NOT NULL, j FLOAT NOT NULL, k FLOAT NOT NULL,
    x CHAR(20) NOT NULL, y CHAR(20) NOT NULL, z CHAR(20) NOT NULL
) ENGINE=InnoDB;

MyISAM和内存的相同架构。

表中满是10M行随机数据，由此产生的数据大小：

InnoDB: 1.0GB

MyISAM: 810 MB

内存： 867 MB

SQL

SELECT * FROM testinnodb WHERE c=1;
SELECT * FROM testmyisam WHERE c=1;
SELECT * FROM testmemory WHERE c=1;

InnoDB: 2.4 s！

MyISAM: 0.3s

内存： 0.2s

查询多次运行，但性能保持不变。“解释”为所有三个查询提供相同的输出(简单，使用WHERE)。

考虑到硬件、MyISAM和内存的性能，这显然不是I/O问题。

缓冲池的64 in也足以将所有表保存在内存中。

数据必须位于缓冲池中，因为禁用了innodb_buffer_pool_load_at_startup，查询将在第一次运行时使用4.2秒，在随后运行时将使用2.4s。

innodb_buffer_pool_bytes_data在第一次运行后将增长超过1GB，因此看起来整个数据实际上都在缓冲池中。

innodb_buffer_pool_read_requests确实在每次执行时增加了大约1000万。

为什么使用InnoDB从缓冲池(即RAM)读取数据比使用MyISAM (即从MyISAM读取相同数据)慢10倍？

我需要帮助了解发生了什么。这肯定是不对的？我尝试过使用DB配置(例如innodb_old_blocks_time=0、innodb_read_io_threads=32和innodb_write_io_threads=32)，但这并没有改变什么。

我知道使用索引会改善情况，但这不是问题所在。

如果您需要一些状态变量进行调试，请告诉我，我是InnoDB新手，所以我不确定在这里发布的相关内容是什么。

启动和两次查询SHOW ENGINE InnoDB STATUS;表后的InnoDB输出

=====================================
Per second averages calculated from the last 50 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 0 srv_active, 0 srv_shutdown, 50 srv_idle
srv_master_thread log flush and writes: 50
----------
SEMAPHORES
----------
------------
TRANSACTIONS
------------
Trx id counter 20001586
Purge done for trx's n:o < 20001583 undo n:o < 0 state: running
History list length 14
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION (000002727BD64108), not started 
0 lock struct(s), heap size 1128, 0 row lock(s)
--------
FILE I/O
--------
Pending flushes (fsync) log: 0; buffer pool: 0
71726 OS file reads, 2 OS file writes, 2 OS fsyncs
0.00 reads/s, 16413 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2, 0 merges
merged operations:
 insert 0, delete mark 0, delete 0
discarded operations:
 insert 0, delete mark 0, delete 0
0.00 hash searches/s, 0.00 non-hash searches/s
---
LOG
---
Log sequence number 8881303100
Log flushed up to   8881303100
Pages flushed up to 8881303100
Last checkpoint at  8881303088
0 pending log flushes, 0 pending chkp writes
4 log i/o's done, 0.08 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 68753031168
Dictionary memory allocated 424846000
Buffer pool size   4147712
Free buffers       4075870
Database pages     71842
Old database pages 26539
Modified db pages  0
Percent of dirty pages(LRU & free pages): 0.000
Max dirty pages percent: 90.000
Pending reads 0
Pending writes: LRU 0, flush list 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 71711, created 131, written 0
1434.19 reads/s, 2.62 creates/s, 0.00 writes/s
Buffer pool hit rate 996 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 71842, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 read views open inside InnoDB
Process ID=0, Main thread ID=0, state: sleeping
Number of rows inserted 0, updated 0, deleted 0, read 20000000
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 399992.00 reads/s
Number of system rows inserted 0, updated 0, deleted 0, read 0
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================

Answer 1

InnoDB与MySQL或MariaDB服务器的SQL解释器之间的接口有相当多的开销。

在InnoDB中，每个访问都必须由缓冲池页面锁存保护。一个小型事务对象将跟踪获取的锁存。基本上，对于每个获取的行，InnoDB将启动一个迷你事务，在缓冲池中查找B树页页，获取页面锁存器，复制数据，最后提交迷你事务并释放页面锁存。

在此基础上有几个优化，但这是不够的，最好是实现MDEV 16232，以便允许一个迷你事务在整个范围扫描中持续存在。这样，我们只有在进入下一页时才会获取和释放页面闩锁。

在范围扫描中，持久化光标(btr_pcur_t)将存储当前位置。当游标位置在下一个迷你事务开始时恢复时(以获取下一个记录)，将尝试乐观恢复，前提是指向缓冲池页的旧指针仍然有效。

InnoDB还实现了预取缓冲区。在4次下一次记录读取操作之后，InnoDB将一次将8条记录复制到缓冲区，在一个小事务中。随后的请求将从这个缓冲区中得到满足。这个机制将被MDEV 16232冗余，并作为实现的一部分被删除。

实现MDEV 16232还将通过消除获取显式记录锁的需要来加快更新和删除操作。如果在删除或更新行的整个过程中一直保持页面锁存，那么只要不存在冲突，就可以依赖隐式锁定，就像在INSERT情况下一样。