8.mysql InnoDB引擎

理解innoDB引擎底层原理

表空间 ->段 ->区->页-行

每个ibd都是一个表空间

自适应hash

mysql> show variables like '%hash_index%';
+----------------------------------+-------+
| Variable_name                    | Value |
+----------------------------------+-------+
| innodb_adaptive_hash_index       | ON    | -- 开启了hash索引
| innodb_adaptive_hash_index_parts | 8     |
+----------------------------------+-------+
2 rows in set (0.01 sec)

日志缓冲区

查看日志缓冲区的大小
mysql> show variables like '%log_buffer_size%%';
+------------------------+----------+
| Variable_name          | Value    |
+------------------------+----------+
| innodb_log_buffer_size | 16777216 |
+------------------------+----------+
1 row in set (0.00 sec)
查看日志缓冲区启动的时间
mysql> show variables like '%flush_log%';
+--------------------------------+-------+
| Variable_name                  | Value |
+--------------------------------+-------+
| innodb_flush_log_at_timeout    | 1     |
| innodb_flush_log_at_trx_commit | 1     |
+--------------------------------+-------+
2 rows in set (0.00 sec)

磁盘结构

On-Disk structures

系统表空间
mysql>  show variables like '%data_file_path%';
+----------------------------+------------------------+
| Variable_name              | Value                  |
+----------------------------+------------------------+
| innodb_data_file_path      | ibdata1:12M:autoextend | 系统表空间
| innodb_temp_data_file_path | ibtmp1:12M:autoextend  |
+----------------------------+------------------------+
2 rows in set (0.00 sec)

File-Per-table

独立表空间

每张表都会创建一个表空间 后缀是.ibd

mysql>  show variables like '%file_per_table%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_file_per_table | ON    |
+-----------------------+-------+
1 row in set (0.00 sec)

通用表空间

创建表空间

创建表空间
mysql> create tablespace ts_test add datafile 'mytest.ibd' engine = innodb;
Query OK, 0 rows affected (0.09 sec)

选择数据库创建一个表空间
mysql> use testit;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

^[[A^[[ADatabase changed
mysql> create table a(id int primary key auto_increment,name varchar(10)) engine =innodb tablespace ts_test;
Query OK, 0 rows affected (0.04 sec)

 
[root@localhost ~]# find / -name mytest.ibd
/var/lib/mysql/mytest.ibd

 

撤销表空间

默认创建了两个

临时表空间

双写缓冲区

重做日志

后台线程

1.Master thread

2.IO Thread

FILE I/O
--------
I/O thread 0 state: waiting for completed aio requests (insert buffer thread)
I/O thread 1 state: waiting for completed aio requests (log thread)
I/O thread 2 state: waiting for completed aio requests (read thread)
I/O thread 3 state: waiting for completed aio requests (read thread)
I/O thread 4 state: waiting for completed aio requests (read thread)
I/O thread 5 state: waiting for completed aio requests (read thread)
I/O thread 6 state: waiting for completed aio requests (write thread)
I/O thread 7 state: waiting for completed aio requests (write thread)
I/O thread 8 state: waiting for completed aio requests (write thread)
I/O thread 9 state: waiting for completed aio requests (write thread)
Pending normal aio reads: [0, 0, 0, 0] , aio writes: [0, 0, 0, 0] ,
 ibuf aio reads:, log i/o's:
Pending flushes (fsync) log: 0; buffer pool: 0
2298 OS file reads, 2688 OS file writes, 1700 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 2 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 9 buffer(s)
Hash table size 34679, node heap has 1 buffer(s)
Hash table size 34679, node heap has 2 buffer(s)
Hash table size 34679, node heap has 1 buffer(s)
Hash table size 34679, node heap has 2 buffer(s)
Hash table size 34679, node heap has 1 buffer(s)
0.00 hash searches/s, 0.00 non-hash searches/s
---
LOG
---
Log sequence number          442255604
Log buffer assigned up to    442255604
Log buffer completed up to   442255604
Log written up to            442255604
Log flushed up to            442255604
Added dirty pages up to      442255604
Pages flushed up to          442255604
Last checkpoint at           442255604
Log minimum file id is       134
Log maximum file id is       135
612 log i/o's done, 0.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 0
Dictionary memory allocated 728465
Buffer pool size   8192
Free buffers       5736
Database pages     2438
Old database pages 879
Modified db pages  0
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 556, not young 218
0.00 youngs/s, 0.00 non-youngs/s
Pages read 2257, created 263, written 1521
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: 2438, 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=1793, Main thread ID=140098721007360 , state=sleeping
Number of rows inserted 26, updated 8, deleted 0, read 209
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
Number of system rows inserted 206, updated 544, deleted 68, read 413198
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
 

3.Purge Thread

4.Page Cleaner Thread

事务原理

在redolog中会记录更新的数据 然后如果刷新错误 就把redolog刷新到磁盘中 然后如果数据同步到了磁盘中 就把ib_logfile给重新刷新

undo log

当前读 可以读到最新的版本

快照读

在事务中 第一次select 查询数据就会拍摄一个快照 当之后的查询都会使用第一次select查询的数据

实现原理

事务2

事务3

事务4