mysql 学习18 Inoodb 引擎底层结构和原理

该章节 以理解为主

逻辑存储结构

架构

内存结构

        

查看 show variables like "%hash_index%"， 可以看到这个值是

show variables like "%hash_index%";

innodb_adaptive_hash_index   ON

磁盘结构

        

后台线程 - 内存结构中的数据是如何刷新到磁盘的呢？这里就又引出了 后台线程

show engine innodb status; 查看当前引擎状态。

=====================================
2025-02-25 11:17:45 0x1bf4 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 2 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 1 srv_active, 0 srv_shutdown, 1998 srv_idle
srv_master_thread log flush and writes: 0
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 4
OS WAIT ARRAY INFO: signal count 4
RW-shared spins 0, rounds 0, OS waits 0
RW-excl spins 0, rounds 0, OS waits 0
RW-sx spins 0, rounds 0, OS waits 0
Spin rounds per wait: 0.00 RW-shared, 0.00 RW-excl, 0.00 RW-sx
------------
TRANSACTIONS
------------
Trx id counter 21513
Purge done for trx's n:o < 21511 undo n:o < 0 state: running but idle
History list length 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 283403828911232, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 283403828910456, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 283403828909680, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
--------
FILE I/O
--------
I/O thread 0 state: wait Windows aio (insert buffer thread)
I/O thread 1 state: wait Windows aio (log thread)
I/O thread 2 state: wait Windows aio (read thread)
I/O thread 3 state: wait Windows aio (read thread)
I/O thread 4 state: wait Windows aio (read thread)
I/O thread 5 state: wait Windows aio (read thread)
I/O thread 6 state: wait Windows aio (write thread)
I/O thread 7 state: wait Windows aio (write thread)
I/O thread 8 state: wait Windows aio (write thread)
I/O thread 9 state: wait Windows aio (write thread)
Pending normal aio reads: [0, 0, 0, 0] , aio writes: [0, 0, 0, 0] ,
 ibuf aio reads:, log i/o's:, sync i/o's:
Pending flushes (fsync) log: 0; buffer pool: 0
962 OS file reads, 229 OS file writes, 42 OS fsyncs
0.00 reads/s, 0 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
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 1 buffer(s)
Hash table size 34679, node heap has 3 buffer(s)
0.00 hash searches/s, 0.00 non-hash searches/s
---
LOG
---
Log sequence number          20347192
Log buffer assigned up to    20347192
Log buffer completed up to   20347192
Log written up to            20347192
Log flushed up to            20347192
Added dirty pages up to      20347192
Pages flushed up to          20347192
Last checkpoint at           20347192
14 log i/o's done, 0.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 136974336
Dictionary memory allocated 361949
Buffer pool size   8191
Free buffers       7106
Database pages     1081
Old database pages 419
Modified db pages  0
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 939, created 142, written 165
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 1081, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
0 read views open inside InnoDB
Process ID=6008, Main thread ID=6188 , state=sleeping
Number of 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
Number of system rows inserted 0, updated 315, deleted 0, read 4657
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================

事务原理

redo log 就是保证其 持久性的。

如下的图是没有redo log 时，最后一步，当update 或者delete 时，将数据从磁盘弄到内存，在bufferpool 中改动完成后，红色部分就是改动后的数据，我们叫脏数据，在事务commit之后，需要将 数据再写回 磁盘，在没有 redo log 的时候，如果写回磁盘的时候发生了错误，示例图如下：

有了redo log，会将update delete 后的数据 写到redo log buffer 中，这里包含了数据页变化，然后再commit后，会将  redo log buffer中的数据写入 磁盘结构 ib_logfile0/1 中，然后再某一个实际，从buffer pool 写入 磁盘结构 xxx.idb的时候，如果出错，就可以通过 ib_logfile0/1 恢复

那么这里还有一个问题：commit 的时候，也是从 内存结构写到 磁盘结构呀，为什么这么麻烦呢？

实际上这是有区别的， redolog buffer是顺序记录 改动了什么，如果直接从buffer pool 写入到xxx.idb 大概率不是顺序的，比如要更新第386条信息，要删除123条信息等，性能会很差。而redolog是顺序的记录改动了啥，例如 386 是第一个，123是第二个。性能会优越的多。

这种方法也称为： write-ahead logging

mvcc

怎么查看 这个三个或者两个 隐藏字段呢？

在linux 下进入到安装 mysql 的目录，一般是 /var/lib/mysql，然后再进入到某一个数据库中

使用命令 ibd2sdi tb_sanguo_user.ibd

{
                "name": "DB_TRX_ID",
                "type": 10,
                "is_nullable": false,
                "is_zerofill": false,
                "is_unsigned": false,
                "is_auto_increment": false,
                "is_virtual": false,
                "hidden": 2,
                "ordinal_position": 10,
                "char_length": 6,
                "numeric_precision": 0,
                "numeric_scale": 0,
                "numeric_scale_null": true,
                "datetime_precision": 0,
                "datetime_precision_null": 1,

总结