MySQL 运维 - 浅谈事务

它是一个不可分割的工作逻辑单元
适用于多用户同时操作的数据库系统
通过整体性保证数据一致性

一、事务的四大特性(ACID)

① 原子性

作为完整的操作，其中的各元素是不可分的
所有元素必须作为整体提交或回滚
如出现失败则整个失败

当撤销请求和不执行操作时会触发回滚rollback

② 一致性

当事务完成时，和之前的结果相对应
当事务开始前，存储的数据处于一致状态
正在进行的事务，数据可能会不一致
当事务完成时，数据必须再次回到已知的一致状态

在事务开始之前，不会被其他操作影响[理想化]，但在数据量庞大时并无法完全保证不影响

③ 隔离性

无法保证在同时间只有一个事务在执行
对数据进行修改的所有并发事务之间相互隔离
通过间接影响的数据，去影响另一个事务的结果

④ 持久性

不管系统是否发生故障，事务处理的结果都是永久的

比如将数据存入磁盘

二、事物之间相互影响(间接的)

① 脏读

一个事务读取了另一个事务未提交的数据
而这个数据是有可能会回滚的

② 幻读

对一个表的数据进行修改的时候，如出现其他事务
它会假装没发生这个事情，只把一开始的事务执行下去

营业员给客人结账的时候，土匪来打劫，但是营业员和客人没有理会，一切照常运行着，土匪走伤心的走了

③ 丢失更新

两个事务同时读取并修改了内容却不知有对方，导致前者的数据被后者的数据覆盖了

两名维修工被国家安排修理铁路和拆除铁路，两名维修工并不知道有对方，就这样不断的重复着拆除和安装

三、事务的四大隔离

read uncommitted [RU]
读取尚未提交的数据 ：不解决脏读
read committed [RC]
读取已经提交的数据 ：可以解决脏读
repeatable read [RR]
重读读取：可以解决脏读 和 不可重复读 —mysql默认的
serializable
串行化：可以解决 脏读 不可重复读 和 虚读——相当于锁表

RR重复读取是mysql默认的事务隔离机制

① 查询全局事务隔离级别

show global variables like '%isolation%';

② 查询会话事务隔离级别

show session variables like '%isolation%';

③ 设置全局事务隔离级别

select @@global.tx_isolation;

set global transaction isolation level read committed;
select @@global.tx_isolation;

④ 设置会话事务级别隔离

select @@session.tx_isolation;

set session transaction isolation level read committed;
select @@session.tx_isolation;

四、事务的操作

MySQL的事务在默认情况下是自动提交、执行操作，会直接影响到数据，这样不安全
为了防止出错我们也可以关闭这个自动提交功能
将相同的内容提交到内存里面来执行手动提交
这个时候做的修改是在内存中修改，并未接触磁盘

① 事务控制语句

begin 或 start transaction 或 set autocommit=0(禁止自动提交) 开始一个事务

表示以后的操作没有commit提交，都算为临时
临时的数据存储在内存中，不会对源数据进行修改

commit 或 commitwork 提交事务

只有触发commit之后，才会触发I/O流进行写入

rollback 或 rollback work回滚

如果在commit提交后数据出现错误，可以执行回滚
会撤销所有未提交的修改

savepoint identifier 存档[快照]

一个事务可以有多个存档点
savepoint a delete savepoint b
可以rollback a

举例：回滚指定操作

rollback to [savepoint] xx;

② 测试提交事务

用户A
开始事务并向内存写入数据

begin;
insert into user values(1);

用户B
查看表

select * from user;

用户A
将内容写入

commit

用户B
再次查看

③ 测试回滚事务

begin;

insert into user values(100);

rollback;

④ 使用set设置控制事务

set autocommit=0;						#禁止自动提交
set autocommit=1;						#开启自动提交，MySQL默认为1
show variables like 'autocommit';		#查看MySQL中的autocommit值

举例：

#禁止自动提交
set autocommit=0;
create table user(id int(10));
insert into user values(200);
select * from user;

#退出数据库重写登录查看
quit
mysql -u root -p
use bbs;
select * from user;

五、存储引擎

MySQL中的数据使用各种不同的存储技术存放在
不同格式的文件中，各个技术都有不同的存储机制索引技巧等
存储引擎将数据存储在文件系统中

① 常用的存储引擎

MyISAM 更注重快速读取，不支持事务
InnoDB 更注重重写，支持事务

读写分离，一台用于读，一台用于写，分担压力
存储引擎处于文件系统之上[类似于linux xfs ext4]
负责I/O操作和数据存储

② MyISAM

5.5之前默认的存储引擎，前身ISAM[读大于写]

• 不支持事务所以访问速度快且对完整性没有要求
• 不会占用大量的内存资源
• 不支持容错

存储成的文件格式cd /usr/local/mysql/data/
.frm存储表 .MYD数据文件 .MYI索引文件

MyISAM管理非事务表，是ISAM的扩展格式
提供ISAM里所没有的索引和字段管理的大量功能
使用表锁机制优化多并发的读写操作
提供告诉存储和检索[为了读取速度]以及全文搜索能力

MyISAM支持的存储格式
静态表[默认]
规则易恢复，占用空间多余动态表

动态表
占用空间较难恢复

压缩表
由myisachk工具创建，因单独压缩所以占用空间最小

③ Innodb

• 支持事务 支持4个事务隔离级别
• 行级锁定 但是全表扫描任然会表级锁定
• 读写阻塞与事务隔离级别相关
• 具有非常高效的缓存特性，能缓存索引和数据
• 表与主键以簇的方式存储
• 支持分区、表空间，类似oracle数据库
• 支持外键约束，5.5以前不支持全文检索，5.5以后支持全文检索 对硬件资源要求比较高的场合

update table set a=1 where user like "%Arahc%";

InnoDB 中不保存表的行数，需要扫描一遍整个表
当count(*)语句包含where条件时MyISAM也需要扫描整个表

对于自增长的字段，InnoDB中必须包含只有该字段的索引
清空整个表时会一行一行删速度慢，MyISAM会整个删除并重建

⑤ 查看系统支持的引擎

show engines

⑥ 查看指定表使用的存储引擎

方法一

show table status from 库名 where name '表名'

举例：

show table status from bbs where name='user'\G;

方法二

show create table 表名;

⑦ 修改存储引擎

方法一

alter table 表名 engine=MyISAM;

举例：

alter table user engine=MyISAM

方法二

vim /etc/my.cnf
default-storage-engine=INNODB

此方法只对修改了配置文件并重启的mysql新建的表生效

方法三

create table 表名 (字段1 数据类型) engine=MyISAM;

举例：

create table user (id int(10)) engine=innodb;

六、总结

① MyISAM引擎和Innodb引擎的区别

MyISAM引擎	Innodb引擎
不支持事务	支持事务[四个级别]
表级锁定	行级锁定
读取速度快	读取速度一般
服务器资源开销小	服务器资源开销大
数据难恢复	数据易恢复
支持全文索引 不支持外键约束	支持外键约束 不支持全文索引
只对索引缓存 不对数据缓存	可以高效的缓存索引和数据