MySQL事务

事务是mysql执行的最小单元

MySQL事务

1、在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务。

2、事务处理可以用来维护数据库的完整性，保证成批的 SQL 语句要么全部执行，要么全部不执行。

3、事务用来管理 insert,update,delete 语句。

 

事务命令

mysql> start transaction;#手动开启事务

mysql> insert into t_user(name) values('pp');

mysql> commit/rollback;

 

事务的特征

1、原子性

2、一致性

3、隔离性

4、持久性

 

MySQL的事务隔离级别

1、读未提交：read uncommitted

2、读已提交：read committed

3、可重复读：repeatable read

4、串行化：serializable

1、脏读：当前事务(A) 中可以读到其他事务(B)未提交的数据（脏数据）

时间顺序	转账事务	取款事务
1		开始事务
2	开始事务
3		查询账户余额为 2000 元
4		取款 1000 元，余额被更改为 1000 元（未提交）
5	查询账户余额为 1000 元（产生脏数据）
6		取款操作发生未知错误，事务回滚， 余额变更为 2000 元
7	转入 2000 元，余额被更改为 3000 元 （脏读 1000+2000）
8	提交事务
备注	按照正常逻辑此时账户应该为 4000 元

2、不可重复读：在事务A中先后两次读取同一个数据，两次读取的结果不一样（脏读与不可重复读的区别在于：前者读到的是其他事务未提交的数据，后者读到的是其他事务已提交的数据）。

时间顺序	事务 A	事务 B
1	开始事务	开始事务
2	第一次查询，小明的年龄为 20 岁
3		开始事务
4	其他操作
5		更改小明的年龄为 30 岁
6		提交事务
7	第二次查询, 小明的年龄为 30 岁
备注	按照正常逻辑，事务 A 前后两次读取到的数据应该一致

3、幻读：在事务A 中按照某个条件先后两次查询数据库，两次查询结果的条数不同（不可重复读与幻读的区别可以通俗的理解为：前者是数据变了，后者是数据的行数变了）

时间顺序	事务 A	事务 B
1	开始事务	开始事务
2	第一次查询，数据总量为 100 条
3		开始事务
4	其他操作
5		新增 100 条数据
6		提交事务
7	第二次查询, 数据总量为 200 条, 新增的数据为幻读
备注	按照正常逻辑，事务 A 前后两次读取到的数据总量应该一致

MySQL备份与恢复

备份

mysqldump -h主机名 -P端口 -u用户名 -p密码 [-R] 数据库名 > 文件名.sql

mysqldump -uroot -p123456 -R lingsheng >/home/milo/lingsheng.dump -R备份存储过程,不加不备份存储过程

 

拷贝到其他计算机

scp -P22 root@192.168.2.107:/home/milo/lingsheng.dump /home/dump

 

恢复

mysql -h主机名 -P端口 -u用户名 -p密码 数据库名 < 文件名.sql

mysql -uroot -p123456 lingsheng < /home/milo/lingsheng.dump

 

MySQL分库分表

分库

第一种：磁盘读IO瓶颈，热点数据太多，数据库缓存放不下，每次查询时会产生大量的IO，降低查询速度 -> 分库

和垂直分表。

第二种：网络IO瓶颈，请求的数据太多，网络带宽不够 -> 分库。

 

垂直分表

以 字段为依据，按照字段的活跃性，将 表中字段拆到不同的 表（主表和扩展表）中。

 

水平分表

以 字段为依据 ，按照一定策略（hash、range等），将一个 表中的数据拆分到多个 表中。

 

MySql优化（优化效果递减）

1、SQL语句及索引的优化

2、数据库表结构的优化

3、系统配置的优化

4、硬件的优化