Mysql基础4（深入理解mvcc）

深碍√TFBOYSˉ_ 2022-03-17 09:05 190阅读 0赞

> **文章最前**： 我是Octopus，这个名字来源于我的中文名--章鱼；我热爱编程、热爱算法、热爱开源。所有源码在我的个人[github][] ；这博客是记录我学习的点点滴滴，如果您对 Python、Java、AI、算法有兴趣，可以关注我的动态，一起学习，共同进步。

**相关文章：**

1.  [Mysql基础1(索引)][Mysql_1]
2.  **[Mysql基础2（引擎，体系结构，查询机制））][Mysql_2]**
3.  [Mysql基础3（事务，锁，mvcc）][Mysql_3_mvcc]
4.  [Mysql基础4（深入理解mvcc）][Mysql_4_mvcc]

--------------------

**目录**

mysql中mvcc逻辑流程-插入：

mysql中mvcc版本控制案例：

undolog

Redolog：

Redolog补充知识点：

--------------------

MVCC（多版本并发控制）：并发访问数据库时，对正在事务内处理的数据做多版本的管理，以达到用来避免写操作的阻塞，从而引发读操作的并发问题。

### mysql中mvcc逻辑流程-插入： ###

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3p5MzQ1MjkzNzIx_size_16_color_FFFFFF_t_70][]

mysql中mvcc逻辑流程-删除

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3p5MzQ1MjkzNzIx_size_16_color_FFFFFF_t_70 1][]

mysql中mvcc逻辑流程--修改

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3p5MzQ1MjkzNzIx_size_16_color_FFFFFF_t_70 2][]

mysql中mvcc逻辑流程--查询

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3p5MzQ1MjkzNzIx_size_16_color_FFFFFF_t_70 3][]

### mysql中mvcc版本控制案例： ###

**数据准备：**insert into teacher(name,age) value ('seven',18) ;  
                  insert into teacher(name,age) value ('qing',20) ;

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3p5MzQ1MjkzNzIx_size_16_color_FFFFFF_t_70 4][]  
mvcc的版本控制案例1(1,2,3,4,2)：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3p5MzQ1MjkzNzIx_size_16_color_FFFFFF_t_70 5][]

mvcc版本控制案例二（3,4,1,2）

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3p5MzQ1MjkzNzIx_size_16_color_FFFFFF_t_70 6][]

### **undolog** ###

** **undo:意为取消，以撤销作为目的，返回指定某个状态的操作

undo log指事务开始之前，在操作任何数据之前，首先将操作的数据备份到一个地方（undo log）

undolog视为了实现事务的原子性而出现的产物

undolog实现事务原子性：

事务处理过程中如果出现了错误或者用户执行了rollback语句，mysql可以利用undo log中的备份将数据恢复到事务开始之前的状态。

undolog实现多版本并发控制：

事务未提交之前，Undo保存了未提交之前的版本数据，undo中的数据可作为数据旧版快照供其他并发事务进行快照读。

undolog

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3p5MzQ1MjkzNzIx_size_16_color_FFFFFF_t_70 7][]

当前读和快照读：

###                            ![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3p5MzQ1MjkzNzIx_size_16_color_FFFFFF_t_70 8][]                  **Redolog：** ###

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3p5MzQ1MjkzNzIx_size_16_color_FFFFFF_t_70 9][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3p5MzQ1MjkzNzIx_size_16_color_FFFFFF_t_70 10][]

### Redolog补充知识点： ###

指定Redo log记录在\{datadir\}/ib\_logfile1&ib\_logfile2 可通过innodb\_log\_group\_home\_dir配置指定目录存储；

一旦事务成功提交且数据持久化落盘之后，此时redo log中的对应事务数据记录就失去了意义，所以redo log的写入是日志循环写入的：

指定redo log日志文件组中的数量innodb\_log\_files\_in\_group默认为2

指定redo log每一个日志文件最大存储量innodb\_log\_file\_size默认48M

指定redo log在cache/buffer中buffer池大小innodb\_log\_buffer\_size默认16M

Redo buffer持久化redo log的策略，Innodb\_flush\_log\_at\_trx\_commit:

取值0每秒提交redo buffer-->redo log os cache -->flush cache to disk\[可能丢失一秒内的事务数据\]

取值1默认值，每次事务提交执行redo buffer--->redo log os cache--->flush cache to disk \[最安全，性能最差的方式\]

取值2每次事务提交执行Redo buffer -->Redo log Os cache再每一秒执行--->flush cache to disk 操作

**4.配置优化：**

**           mysql服务器参数类型**

**                 ** 基于参数的作用域：

**                   全局参数：**set global autocommit=on/off

**   会话参数：**（会话参数不单独设置则会采用全局参数）set session autocommit=on/off

注意：

全局参数的设定对于已经存在的会话无法生效

会话参数的设定随着会话的销毁而生效

全局类的统一配置建议配置在默认设置文件中，否则重启服务会导致配置失效

寻找配置文件：

mysql --help 寻找配置文件的位置和加载顺序

全局配置文件配置：

最大连接数配置：max\_connnections

系统句柄数配置：/etc/security/limits.conf   ulimit -a

mysql句柄数配置：/usr/lib/systemd/system/mysqld.service

常见全局配置文件配置：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3p5MzQ1MjkzNzIx_size_16_color_FFFFFF_t_70 11][]

mysql内存参数配置：

每一个connection内存参数配置：

sort\_buffer\_size    connection排序缓冲区大小

建议256k(默认值)->2m之内

当查询语句中有需要文件排序功能时，马上为connection分配配置的内存大小

join\_buffer\_size connection关联查询缓冲区大小

建议256K(默认值)->1M之内

当查询语句中有关联查询时，马上分配配置大小的内存用这个关联查询，所有有可能在一个查询语句中会分配很多个关联查询缓冲区

上述配置4000连接占用内存：

4000\*（0.256m+0.256m）=2G

Mysql内存参数配置：

Innodb\_buffer\_pool\_size

Innodb buffer/cache的大小（默认128M）

Innodb\_buffer\_pool:

数据缓存

索引缓存

缓存数据

内部结构

大的缓冲池可以减少多次磁盘I/O访问相同的表数据以提高性能

参考计算公式：

Innodb\_buffer\_pool\_size=(总物理内存-系统运行所用-connection所用)\*90%

**             mysql其他参数配置：**

wait\_timeout:服务器关闭非交互连接之前等待活动的秒数

innodb\_open\_files:限制Innodb能打开的表的个数

innodb\_write\_io\_threads

innodb\_read\_io\_threads

innodb使用后台线程处理innodb缓冲区数据项上的读写I/O请求

数据库表的设计

第一范式：字段具有原子性，不可再分，没一列只有一个单一的值，不可再划分；

第二范式：要求实体完全依赖于主键，每一行都是主键能进行区分。

第三范式：满足第三范式必须满足第二范式，每一个表都不包含其他表已经包含的非主键信息。

**             数据库表的设计：**

**             满足第一范式设计将为表建立大量的列**

数据从磁盘到缓冲区，缓冲区脏页到磁盘进行持久的过程中，列的数量过多会导致性能下降，过多的列影响转换和持久的性能；

**             过分的满足第三范式造成了大多的表关联：**

表的关联操作将带来额外的内存和性能开销；

**              使用innodb引擎的外键关系进行数据的完整性保证**

外键表中数据的修改会导致innodb引擎对外键约束进行检查，就带来了额外的开销

补充三大范式的例子

[github]: https://github.com/zhangyu345293721
[Mysql_1]: https://blog.csdn.net/zy345293721/article/details/87868179
[Mysql_2]: https://blog.csdn.net/zy345293721/article/details/87872793
[Mysql_3_mvcc]: https://blog.csdn.net/zy345293721/article/details/87884373
[Mysql_4_mvcc]: https://blog.csdn.net/zy345293721/article/details/87891718
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3p5MzQ1MjkzNzIx_size_16_color_FFFFFF_t_70]: /images/20220317/5b4a2a6332c641cc9ea792dceb4a3760.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3p5MzQ1MjkzNzIx_size_16_color_FFFFFF_t_70 1]: /images/20220317/4b920cff1aa941b9a00fff6ddc11506f.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3p5MzQ1MjkzNzIx_size_16_color_FFFFFF_t_70 2]: /images/20220317/657e232e72a445359931a504cd68ac34.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3p5MzQ1MjkzNzIx_size_16_color_FFFFFF_t_70 3]: /images/20220317/f088d97093e6415098aeee0e6ccaf24d.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3p5MzQ1MjkzNzIx_size_16_color_FFFFFF_t_70 4]: /images/20220317/7093fe71a2a84b64ac5a4a4791fde7e1.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3p5MzQ1MjkzNzIx_size_16_color_FFFFFF_t_70 5]: /images/20220317/d650eb0dd489413d862a4dfd49164baf.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3p5MzQ1MjkzNzIx_size_16_color_FFFFFF_t_70 6]: /images/20220317/c63036836cb84103a314322f98621a1d.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3p5MzQ1MjkzNzIx_size_16_color_FFFFFF_t_70 7]: /images/20220317/1ae6b0bad26d4b5283f139b76a374988.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3p5MzQ1MjkzNzIx_size_16_color_FFFFFF_t_70 8]: /images/20220317/1e803062df4e4c08b1cbdc2b9a2896a0.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3p5MzQ1MjkzNzIx_size_16_color_FFFFFF_t_70 9]: /images/20220317/4e152d2eb7d8426dbd0819bd8d0ebe91.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3p5MzQ1MjkzNzIx_size_16_color_FFFFFF_t_70 10]: /images/20220317/9b9f9de223a24e758f157106883b51b5.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3p5MzQ1MjkzNzIx_size_16_color_FFFFFF_t_70 11]: /images/20220317/8de9380f7dd84adb971c641275f15049.png