网站建设行业,wordpress中文4.8,写软文,企业logo设计平台一、事务
1.事务特性及保证事务特性的原理
原子性#xff1a;当前事务的操作要么全部成功#xff0c;要么全部失败。原子性由undo log实现#xff0c;undo log记录了每次操作之前的数据版本#xff0c;如果某一操作失败#xff0c;可以根据undo log回滚到最初状态。一致…一、事务
1.事务特性及保证事务特性的原理
原子性当前事务的操作要么全部成功要么全部失败。原子性由undo log实现undo log记录了每次操作之前的数据版本如果某一操作失败可以根据undo log回滚到最初状态。一致性事务执行前后变化一致。由其它三个特性和正确逻辑来保证。隔离性在事务执行时他们内部的操作不能互相干扰。隔离性由Mysql的各种锁和MVCC机制实现MVCC机制是多版本并发控制专门用来保证并发事务时读已提交和读未提交隔离性。具体是通过一致性视图 read view和undo 版本链通过一致性算法比对规则来保证。持久性一旦事务提交它对数据库的改变就是永久的。持久性由redo log实现。
关于readview和可见性算法的原理解释 readview和可见性算法其实就是记录了sql查询那个时刻数据库里提交和未提交所有事务的状态。 要实现RR隔离级别事务里每次执行查询操作readview都是使用第一次查询时生成的readview也就是都是以第一次查询时当时数据库里所有事务提交状态来比对数据是否可见当然可以实现每次查询的可重复读的效果了。 要实现RC隔离级别事务里每次执行查询操作readview都会按照数据库当前状态重新生成readview也就是每次查询都是跟数据库里当前所有事务提交状态来比对数据是否可见当然实现的就是每次都能查到已提交的最新数据效果了。
2.事务隔离级别及存在问题
隔离级别脏读不可重复读幻读读未提交可能可能可能读已提交不可能可能可能可重复读不可能不可能可能串行化不可能不可能不可能
读未提交Read uncommitted一个事务可以读到另一个事务还未提交的内容读已提交Read committed一个事务可以读到另一个事务已经提交的内容可重复读Repeatableread一旦事务开启每次读取的内容都是相同的串行化Serializable通过加锁的方式让操作数据库的事务按顺序执行
3.事务问题定位
-- 查看当前数据库的事务隔离级别:
show variables like tx_isolation;
-- 设置事务隔离级别
set tx_isolationREPEATABLE-READ;
-- 查询执行时间超过1秒的事务
select *from information_schema.innodb_trx where TIME_TO_SEC(timediff(now(),trx_started))1;
-- 强制结束事务
kill 事务对应的线程id(trx_mysql_thread_id字段)二、锁
1.锁按性能分
乐观锁不会对操作的数据直接加锁而是通过操作前比较版本和每次操作之后修改版本实现。适用于读多写少的场景写数据较多时会造成cpu空转效率降低。悲观锁对数据操作前先获取锁避免其它线程操作干扰读锁和写锁都是悲观锁。
-- 读锁
select * from T where id1 lock in share mode
-- 写锁
select * from T where id1 for update意向锁意向锁主要是mysql为了提高加表锁的效率自己加的。当需要给一个表加表锁时需要逐行判断有没有行锁有了意向锁当有事务给表的数据增加了行锁或者排他锁时会给表设置一个标识。这样再加表锁时就无需逐行判断。
2.锁按粒度分
表锁针对于整个表加锁。开销小加锁快。一般用在整张表迁移的场景
-- 手动增加表锁
lock table 表名称 read(write),表名称2 read(write);
-- 查看表上加过的锁
show open tables;
-- 删除表锁
unlock tables;行锁针对某一行数据加锁开销大加锁慢会出现死锁。 注意 行锁是加在索引上不是加在整行上。如果索引失效或者没有针对索引加锁会造成行锁升级为表锁RR级别会升级为表锁RC级别不会升级。 问题为什么行锁升级表锁针对RR级别。因为RR级别为了保证可重复读扫描过的数据就不允许其它事务修改或者间隙内被其它事务插入数据幻读从从而导致数据不一致所以Mysql的做法是把所有扫描过的索引和间隙都加锁
-- 怎对隔离级别为 RR
‐‐where条件里的name字段无索引 所以会升级为表锁
select * from account where name lilei for update; 3.间隙锁、临键锁为了解决RR级别下的幻读问题
间隙锁锁的是两个值之间的空隙。间隙锁是针对RR级别才生效临键锁除了锁主两个值的间隙还会锁主两个值本身
4.锁问题分析
-- 查看系统上的行锁的争夺情况
show status like innodb_row_lock%;-- 查看事务
select * from INFORMATION_SCHEMA.INNODB_TRX;
-- 查看锁8.0之后需要换成这张表performance_schema.data_locks
select * from INFORMATION_SCHEMA.INNODB_LOCKS;
-- 查看锁等待8.0之后需要换成这张表performance_schema.data_lock_waits
select * from INFORMATION_SCHEMA.INNODB_LOCK_WAITS; -- 释放锁trx_mysql_thread_id可以从INNODB_TRX表里查看到
kill trx_mysql_thread_id-- 查看锁等待详细信息
show engine innodb status; 三、日志
1.redo日志 1.redo日志作用如果事务提交成功buffer pool 里的数据还没来得及写入磁盘此时系统宕机了就可以用redo log来恢复磁盘文件ibd里的数据。 2.redo日志写过程事务提交时先把日志写在redo log buffer中调用操作系统函数 write写道文件系统的page cache,然后调用操作系统函数的fsync持久化到磁盘文件。 3.重要参数
-- 这个参数控制 redo log 的写入策略
innodb_flush_log_at_trx_commit0每次事务提交都只把redo log日志写在redo log buffer中1每次事务提交把redolog日志直接持久化到磁盘2每次事务提交把redolog日志写到操作系统的page cach中# 查看innodb_flush_log_at_trx_commit参数值
show variables like innodb_flush_log_at_trx_commit;
# 设置innodb_flush_log_at_trx_commit参数值(也可以在my.ini或my.cnf文件里配置)
set global innodb_flush_log_at_trx_commit1; 2.binlog日志
binlog 作用binlog是二进制日志保存了所有执行过的修改操作语句。如果mysql数据库意外停止可以用来恢复数据库数据。
# 查看binlog相关参数
show variables like %log_bin%;log_binbinlog日志是否打开状态
log_bin_basename是binlog日志的基本文件名后面会追加标识来表示每一个文件binlog日志文件会滚动增加
log_bin_index指定的是binlog文件的索引文件这个文件管理了所有的binlog文件的目录。
sql_log_binsql语句是否写入binlog文件ON代表需要写入OFF代表不需要写入。如果想在主库上执行一些操作但不复制到slave库上可以通过修改参数sql_log_bin来实现。比如说模拟主从同步复制异常。开启binlog 打开binlog功能需要修改配置文件my.ini(windows)或my.cnf(linux)然后重启数据库。在配置文件中的[mysqld]部分增加如下配置:
# log-bin设置binlog的存放位置可以是绝对路径也可以是相对路径这里写的相对路径则binlog文件默认会放在data数据目录下
log-binmysql-binlog
# Server Id是数据库服务器id随便写一个数都可以这个id用来在mysql集群环境中标记唯一mysql服务器集群环境中每台mysql服务器的id不能一样不加启动会报错
server-id1
# 其他配置
binlog_format row # 日志文件格式下面会详细解释
expire_logs_days 15 # 执行自动删除距离当前15天以前的binlog日志文件的天数 默认为0 表示不自动删除
max_binlog_size 200M # 单个binlog日志文件的大小限制默认为 1GBbinlog写入磁盘机制 binlog写入磁盘机制主要通过 sync_binlog 参数控制默认值是 0
0每次提交事务都只写到page cach1每次提交都会写到磁盘N:每次提交事务只写道 page cach,积累N个后写到磁盘 binlog日志格式 用参数 binlog_format 可以设置binlog日志的记录格式。STATEMENT基于SQL语句的复制每一条会修改数据的sql都会记录到master机器的bin-log中这种方式日志量小。但是对于一些执行过程中才能确定结果的函数比如UUID()、SYSDATE()等函数如果随sql同步到slave机器去执行则结果跟master机器执行的不一样ROW基于行的复制日志中会记录成每一行数据被修改的形式。这种方式日志量较大性能不如StatementMIXED:混合模式复制实际就是前两种模式的结合。在Mixed模式下如果sql里有函数或一些在执行时才知道结果的情况会选择Row其它情况选择Statement推荐使用这一种
查看binlog日志文件
# 查看bin-log二进制文件命令行方式不用登录mysql
mysqlbinlog --no-defaults -v --base64-outputdecode-rows D:/user/mysql-5.7.25-winx64/data/mysql-binlog.000001 # 查看bin-log二进制文件带查询条件
mysqlbinlog --no-defaults -v --base64-outputdecode-rows D:/user/mysql-5.7.25-winx64/data/mysql-binlog.000001 start-datetime2023-01-21 00:00:00 stop-datetime2023-02-01 00:00:00 start-position5000 stop-position20000使用binlog日志恢复被删除数据
# 1.先执行刷新日志的命令生成一个新的binlog文件mysql-binlog.000008后面我们的修改操作日志都会记录在最新的这个文件里
flush logs;
# 2.执行两条插入语句
INSERT INTO test.account (id, name, balance) VALUES (4, zhuge, 666);
INSERT INTO test.account (id, name, balance) VALUES (5, zhuge1, 888);
# 3.假设现在误操作执行了一条删除语句把刚新增的两条数据删掉了
delete from account where id 3;
# 4.查看binlog日志文件
mysqlbinlog --no-defaults -v --base64-outputdecode-rows D:/dev/mysql-5.7.25-winx64/data/mysql-binlog.000002
# 5.找到第一条sql BEGIN前面的文件位置标识 at 219(这是文件的位置标识)再找到第二条sql COMMIT后面的文件位置标识 at 701
mysqlbinlog --no-defaults --start-position219 --stop-position701 --databasetest D:/user/mysql-5.7.25-winx64/data/mysql-binlog.000002 | mysql -uroot -p123456 -v test以上步骤执行完被删除数据被恢复 dump备份 如果要全量数据同步或者备份直接使用dump将全量数据保存下来。
mysqldump -u root 数据库名备份文件名; #备份整个数据库
mysqldump -u root 数据库名 表名字备份文件名; #备份整个表mysql -u root test 备份文件名 #恢复整个数据库test为数据库名称需要自己先建一个数据库test3.undo日志
undo日志主要用来记录每一步修改操作前的数据方便事务执行失败后回滚。
innodb_undo_directory设置undo log文件所在的路径。该参数的默认值为./即innodb数据文件存储位置目录下ibdata1文件就是undo log存储的位置。
innodb_undo_logs: 设置undo log文件内部回滚段的个数默认值为128。
innodb_undo_tablespaces: 设置undo log文件的数量这样回滚段可以较为平均地分布在多个文件中。设置该参数后会在路径innodb_undo_directory看到undo为前缀的文件。
