Mysql基础4(深入理解mvcc)
MVCC(多版本并发控制):并发访问数据库时,对正在事务内处理的数据做多版本的管理,以达到用来避免写操作的阻塞,从而引发读操作的并发问题。
mysql中mvcc逻辑流程-插入:
mysql中mvcc逻辑流程-删除
mysql中mvcc逻辑流程--修改
mysql中mvcc逻辑流程--查询
mysql中mvcc版本控制案例:
数据准备:insert into teacher(name,age) value ('seven',18) ;
insert into teacher(name,age) value ('qing',20) ;
mvcc的版本控制案例1(1,2,3,4,2):
mvcc版本控制案例二(3,4,1,2)
undolog
undo:意为取消,以撤销作为目的,返回指定某个状态的操作
undo log指事务开始之前,在操作任何数据之前,首先将操作的数据备份到一个地方(undo log)
undolog视为了实现事务的原子性而出现的产物
undolog实现事务原子性:
事务处理过程中如果出现了错误或者用户执行了rollback语句,mysql可以利用undo log中的备份将数据恢复到事务开始之前的状态。
undolog实现多版本并发控制:
事务未提交之前,Undo保存了未提交之前的版本数据,undo中的数据可作为数据旧版快照供其他并发事务进行快照读。
undolog
当前读和快照读:
Redolog:
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
常见全局配置文件配置:
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引擎对外键约束进行检查,就带来了额外的开销
补充三大范式的例子