innoDB引擎解析

导语：这篇博客是《MySQL技术内幕：InnoDB存储引擎》一书的读书笔记，由于此书较旧（2009年出版),对应mysql5.4版本，因此一些特性可能已经不是最新的，后续有时间会酌情修改。

一、体系结构

1.1 线程

show engine innodb status;


BACKGROUND THREAD  
-----------------
srv_master_thread loops: 4 srv_active, 0 srv_shutdown, 63448 srv_idle
srv_master_thread log flush and writes: 0

FILE I/O
--------
I/O thread 0 state: waiting for i/o request (insert buffer thread)
I/O thread 1 state: waiting for i/o request (log thread)
I/O thread 2 state: waiting for i/o request (read thread)
I/O thread 3 state: waiting for i/o request (read thread)
I/O thread 4 state: waiting for i/o request (read thread)
I/O thread 5 state: waiting for i/o request (read thread)
I/O thread 6 state: waiting for i/o request (write thread)
I/O thread 7 state: waiting for i/o request (write thread)
I/O thread 8 state: waiting for i/o request (write thread)
I/O thread 9 state: waiting for i/o request (write thread)

上面展示了 master thread 以及 4个IO主要线程是 insert buffer thread, log thread, read thread, write thread

事实上，还有lock thread (锁监控线程), 错误监控线程

1.2 内存

InnoDB内存包括： buffer pool(缓冲池), redo log buffer, additional memory pool

MySQL [test]> show variables like 'innodb_buffer_pool_size';
+-------------------------+-----------+
| Variable_name           | Value     |
+-------------------------+-----------+
| innodb_buffer_pool_size | 134217728 |
+-------------------------+-----------+

MySQL [test]> show variables like 'innodb_log_buffer_size';
+------------------------+----------+
| Variable_name          | Value    |
+------------------------+----------+
| innodb_log_buffer_size | 16777216 |
+------------------------+----------+

buffer pool使用最多的内存，用来存放各种数据的缓存，innoDB把数据库文件按页(16K/页)读取到buffer pool，按照LR算法淘汰。

如果需要修改数据库文件，则先修改buffer pool中的页，然后按照一定频率将buffer pool的dirty page flush到文件。

BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 137363456
Dictionary memory allocated 404218
Buffer pool size   8191
Free buffers       7237
Database pages     950
Old database pages 370
Modified db pages  0
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 1, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 803, created 147, written 218
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: 950, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]

如上，buffer pool共有8191个buffer frame，每个buffer frame 16K，所以上面的buffer pool大小为 16K*8191 ~= 128M

frre buffer：空闲的buffer frame

buffer pool缓存的数据类型：

索引页、数据页、undo页、insert buffer、adaptive hash index、lock info、data dictionary等


①日志缓冲会先将redo log写到log buffer，然后再以一定频率flush到redo_log file，一般情况频率为一秒一次，因此尽量保证每秒的事务小于log buffer大小

②innodb通过堆进行内存管理，需要分配内存时，先从额外内存池申请，不够的话从buffer pool申请；每个buffer pool中的frame buffer以及对应的buffer control block会存在一个对象，这些对象记录了注入LRU、lock、wait_time等信息，这些对象的内存从额外内存池申请。

1.3 master thread

master thread是优先级最高的线程，内部由下面几个循环组成，master thread会根据数据库运行状态在几个循环中进行切换

1. loop 主循环
2. background loop 后台循环
3. flush loop 刷新循环
4. suspend loop 暂停循环

1.3.1 loop 主循环操作

void master_thread() {
	loop:
		for(int i = 0; i < 10; i++)
		{
			// do thing once per 1s();
		}
		// do thing once per 10s();
		goto loop;
}

1.3.1.1 对于每秒操作

1. 刷新日志缓冲到磁盘，即便事务还没提交，因此即便是很大的事务commit的时候，时间也是很快
2. 当前一秒，发生IO小于5次，InnoDB认为当前IO压力较小，则会合并插入缓冲
3. InnoDB判断当前缓冲池脏页的比例(buf_get_modified_ratio_pct)是否大于配置(innodb_max_dirty_pages_pct)，若超过，则会刷新脏页
4. 当前如果没有

do_thing_once_per_second()
{
			flush(log buffer); // even if transaction not committed
		if (last_second_io < 5) 
			merge(insert buffer)
		if (buf_get_modified_ratio_pct > innodb_max_dirty_page_pct)  
			flush(dirty_page)
		if (no user activity)  
			goto background_loop
}

1.3.1.2 对于每10秒的操作

1. 若过去10秒IO小于200次，刷新100个脏页到磁盘
2. 合并做多5个插入缓冲(与每秒操作不一样，这个合并必定会发生)
3. 将日志缓冲刷新到磁盘
4. 删除无用的undo页（full purge），在执行update、delete这些操作时，原先的行会被标记为删除，但是因为一致性读(consistent read)，需要保留这些行版本的信息。因此，在full purge的过程中，InnoDB会判断被标记删除的行，是否可以删除，若可以则将其删除。InnoDB在full purge删除时,每次最多删除20个undo页。
5. 刷新100个或者10个脏页到磁盘
6. 产生一个检查点，也称模糊检查点(fuzzy checkpoint),checkpoint时不会把所有buffer pool的脏页写进磁盘，而是把最老日志序列号(oldest LSN)写进磁盘

do_thing_onece_per_10second()
{
	if (last_10s_io < 200)
		flush(innodb_buffer_pool.dirty_page, max(100)) // 刷新做多100个脏页
	merge(insert_buffer, max(5))    // 合并最多5个插入缓存
	flush(log_buffer)
	full_purge
	if (buf_get_modified_ratio_pct > 70%)
		flush(dirty_page, max(100))
	else
		flush(dirty_page, max(10))
	do fuzzy checkpoint
	goto loop
	background loop:
		do_background_loop
	goto loop
}

1.3.2 background loop操作

当前没有用户活动(数据库空闲或关闭)时，会进入到background loop

1. 删除无用undo页
2. 合并插入缓冲
3. 有事件的话，跳回到主循环
4. 空闲的话，跳转到flush loop

do_background_loop()
{
	do full purge
	merge(insert_buffer)
	if not idle:
		goto loop
	else:
		goto flush loop
}

1.3.3 flush loop操作

Flush loop的操作，就是不断刷新脏页，知道脏页比例低于配置阈值,直到低于阈值，则调到悬挂循环

do_flush_loop()
{
	flush(100, dirty_page)
	if (buf_get_modified_ratio_pct > innodb_mac_dirty_pages_pct)
  	goto flush loop
  goto suspend loop
}

1.3.4 suspend loop操作

悬挂循环就是挂起线程，直到有事件唤醒,则跳转到主循环

do_suspend_loop()
{
	suspend_thread()
	waiting event
	goto loop;
}

1.4 关键特性

InnoDB关键特定包括

1. 插入缓冲
2. double write
3. adaptive hash index自适应哈希索引

1.4.1 插入缓冲 insert buffer

注: mysql8.0官方文档，将其称为change buffer https://dev.mysql.com/doc/refman/8.0/en/innodb-change-buffer.html

​ 事实上，insert buffer叫做change buffer更合适，因为DML操作(insert,update,delete)都会产生change buffer,为了保持一致，后面将仍叫insert buffer。

​

九、参考与修改历史

references:

《MySQL技术内幕：InnoDB存储引擎》

内容	版本	修改时间
InnoDB体系架构	v1.0	2019.9.10

select f_city_name,total,overtime,overtime/total*100 from (select a.f_city_code,a.total,a.overtime,b.f_city_name from (select f_city_code,sum(f_total) as total,sum(f_overtime_code_total) as overtime from t_order_data where f_city_code like '53%' and f_date >'20200908' group by f_city_code) as a inner join t_city b on a.f_city_code=b.f_city_code ) as data;