理解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