mysql调优-MVCC

MVCC：

Multiversion concurrency control (多 多 版本并发 控制)
普通话解释：
并发访问(读或写)数据库时，对正在事务内处理的数据做多版本的管理。以达到用来避免写操作的堵塞，从而引发读操作的并发问题。

当我们创建表完成后，mysql会自动为每个表添加 数据版本号（最后更新数据的事务id）db_trx_id 删除版本号 db_roll_pt （数据删除的事务id） 事务id由mysql数据库自动生成，且递增。

MVCC逻辑流程-插入：
如下图,同一事务中插入两条记录,记录的 数据版本号为事务id,删除版本号为null

MVCC逻辑流程-删除：
一个事务（假设id为22）中执行删除语句，会更新数据的删除版本号为 当前事务id 22

MVCC逻辑流程-修改：
可以理解为，当一个事务中 修改一条记录时， 是先复制该数据，新数据数据版本号为当前事务id,删除版本号为 null 。然后更新 原来数据的删除版本号为 当前事务id。如下：

MVCC逻辑流程-查询：
查询同时满足以下条件的 记录
1、查找数据版本号，早于（小于等于）当前事务id的数据行。 这样可以确保事务读取的数据是事务之前已经存在的。或者是当前事务插入或修改的。
2、查找删除版本号为null 或者大于当前事务版本号的记录。 这样确保取出来的数据在当前事务开启之前没有被删除。

根据MVCC业务流程分析如下案例的查询结果
测试1：
tx1: begin
select * from users where id =1 ;
tx2: begin
update users set lastUpdate=now() where id =1; （不提交）
tx1:
select * from users where id =1;
分析:
假设tx1 事务id为1，tx2 事务id 大于1 当 tx2 执行了数据更新后， tx1再次执行查询也不会查询出 tx2更新后的结果。
（因为mvcc查询只能查询出 版本id小于当前事务id 的记录）

测试2：
tx1: set session autocommit=off;
update users set lastUpdate=now() where id =1;
在未做commit/rollback 操作之前，在其他的事务我们能不能进行对应数据的查询（特别是加上了X 锁的数据）
tx2: select * from users where id > 1;
select * from users where id = 1;
当tx2查询时 事务id大于tx1 的事务id,所以会查询出tx1更新后的结果。 很明显 tx1还没有提交 tx2 按理说不应该查询出tx1 更新的结果。这样就出现了脏读。 实际执行中，查询结果也不会查询出 tx1更新后的结果。为什么呢？因为mysql 公国undo Log 实现了快照查询，这种情况会直接从快照中获取数据。普通的select 都是从快照中获取数据(如果数据在其他事务中被修改了)。如下文：
问题：
快照中的数据保存多久？
既然有了快照，为什么还需要mvcc?
删除测试中 测试1是从mvcc中获取数据，一旦发现数据被其他事务修改，且为提交则从快照中读取。只能这么解释了。

Undo Log

Undo Log 是什么：
undo 意为取消，以撤销操作为目的，返回指定某个状态的操作
undo log 指事务开始之前， 在操作任何数据之前, 首先将 需操作的 数据备份到一个地方 (Undo Log)
UndoLog 本来是为了实现事务的原子性而出现的产物
Undo Log 实现事务 原子性 ：
事务处理过程中 如果出现了错误或者用户执行了 ROLLBACK 语句,Mysql 可以利用Undo Log 中的备份
将数据恢复到事务开始之前的状态
后来 UndoLog 在Mysql innodb 存储引擎中也用来实现多版本并发控制
Undo log 实现多版本并发控制：
事务未提交之前，Undo 保存了未提交之前的版本数据，Undo 中的数据可作为数据旧版本快照供其他并发事务进行快照读

快照读：
SQL 读取的 数据 是快照版本，也就是历史版本 ， 普通的SELECT 就是快照读
innodb 快照读，数据的读取将由 cache( 原本数据) + undo( 事务修改过的数据) 两部分组成

当前读：
SQL 读取的 数据 是最新版本 。通过锁机制来保证读取的数据无法通过其他事务进行修改
UPDATE 、DELETE 、INSERT 、SELECT … LOCK IN SHARE MODE 、SELECT … FOR UPDATE 都是当前读

innodb 快照读通过mvcc 解决幻读问题 ,当前读 通过 锁 临建锁 解决幻读问题。undo log 是实现mvcc 的一个方式，快照版本放在undo里边

Redo Log

Redo Log 是什么：
Redo ，顾名思义就 是 重做。以恢复操作为目的，重现操作 ；
Redo log 指事务中 操作 的 任何数据,将 最新的 数据备份到一个地方 (Redo Log)
Redo log 的持久：
不是随着事务的提交才写入的，而是在事务的执行过程中，便开始写入redo buffer 中，然后按照一定的策略写入到磁盘redo log 中，事务提交时只要数据提交到了redo log 中就意味着 事务提交成功（而不是完全更新到磁盘表数据中，因为提交到表数据是随机io 而写入redolog是顺序io 效率更高）。具体的redo buffer 写入到 redo log 策略可以进行配置
RedoLog 是为了实现事务的持久性而出现的产物
Redo Log 实现事务 持久性 ：
防止在发生故障的时间点，尚有脏页未写入磁盘，在重启mysql 服务的时候，会根据redo log 进行重做，从而达到事务的 未入磁盘数据进行 持久 化

指定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 todisk 操作 (建议取值)

配置优化：

基于参数的作用域：
全局参数
set global autocommit = ON/OFF;
会话参数( 会话参数不单独设置则会采用全局参数)
set session autocommit = ON/OFF;
注意：
全局参数的设定对于已经存在的会话无法生效
会话参数的设定随着会话的销毁而失效
全局类的统一配置建议配置在默认配置文件中，否则重启服务会导致配置失效

统一配置要修改配置文件可以通过 mysql --help 寻找配置文件的位置和加载顺序
如下 在windows 环境 执行 mysql --help 会显示出配置文件加载的位置和顺序

linux 下可以通过如下命令 查看 mysql --help | grep -A 1 'Default options are read from the following
files in the given order

全局配置文件配置
最大连接数配置
max_connections 影响mysql并性能的重要指标 默认 151
系统句柄数配置
linux 下 limit -a 命令可以查看系统的文件句柄数限 openfiles
可以再 在 /etc/security/limits.conf 文件中进行修改
mysql 句柄数配置
在 /usr/lib/systemd/system/mysqld.service 文件中进行修改 limitnofile
连接数<mysql 句柄数<系统句柄数

**常见全局配置文件配置 **
port = 3306
socket = /tmp/mysql.sock
basedir = /usr/local/mysql mysql 安装目录
datadir = /data/mysql 数据存放目录
pid-file = /data/mysql/mysql.pid
user = mysql
bind-address = 0.0.0.0
max_connections=2000 最大连接数
lower_case_table_names = 0 # 表名区分大小写 0
server-id = 1 集群中的服务id号 一个集群中不能重复
tmp_table_size=16M
transaction_isolation = REPEATABLE-READ 数据库隔离级别 rr
ready_only=1 主从复制中 从库 配置 ，如果不是管理员 只能读

mysql 内存参数配置
每一个connection 内存参数配置：
sort_buffer_size connection 排序缓冲区大小 建议256K( 默认值)-> 2M 之内 当查询语句中有需要文件排序功能时，马上为connection 分配配置的内存大小
join_buffer_size connection 关联查询缓冲区大小 建议256K( 默认值)-> 1M 之内 当查询语句中有关联查询时，马上分配配置大小的内存用这个关联查询，所以有可能在一个查询语句中会分配很多个关联查询缓冲区

上述配置4000 连接占用内存：
4000*(0.256M+0.256M) = 2G

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_read_io_threads /innodb_write_io_threads
innodb使用后台线程处理innodb缓冲区数据页上的读写 I/O(输入输出)请求

innodb_lock_wait_timeout InnoDB事务在被回滚之前可以等待一个锁定的超时秒数
https://www.cnblogs.com/wyy123/p/6092976.html 常见配置的帖子

数据库表设计
第一范式（ 1NF）：
字段具有原子性,不可再分。 所有关系型数据库系统都满足第一范式）数据库表中的字
段都是单一属性的， 不可再分；
第二范式（ 2NF）：
要求实体的属性完全依赖于主键。 所谓完全依赖是指不能存在仅依赖主键一部分的属性，
如果存在， 那么这个属性和主关键字的这一部分应该分离出来形成一个新的实体， 新实体与原
实体之间是一对多的关系。为实现区分通常需要为表加上一个列，以存储各个实例的惟一标识。
简而言之， 第二范式就是属性完全依赖主键。
第三范式（ 3NF）：
满足第三范式（ 3NF） 必须先满足第二范式（ 2NF）。 简而言之， 第三范式（ 3NF）
要求一个数据库表中不包含已在其它表中已包含的非主键信息。
简单一点 ：
1 ， 每一列只有一个 单一的 值 ，不可再拆分
2 ， 每一行都 有主键能进行 区分
3 ， 每一个表都不包含其他表已经包含的非 主键

三范式存在的问题：
充分的满足第一范式设计将为表建立太量的列
数据从磁盘到缓冲区，缓冲区脏页到磁盘进行持久的过程中，列的数量过多会导致性能下降。过多的列影响转换和持久的性能
过分的满足第三范式化造成了太多的表关联 表的关联操作将带来额外的内存和性能开销

使用innodb 引擎的外键关系进行数据的完整性保证
外键表中数据的修改会导致Innodb引擎对外键约束进行检查，就带来了额外的开销

所以现在不会严格遵守三个范式，经常会冗余的保存字段，同时基本不会使用数据库自身的外键，只使用逻辑外键