MySQL之事务与存储引擎

MrHOPE
132 阅读10分钟

简介

事务的概念

  • 事务是一种机制、一个操作序列，包含了一组数据库操作命令，并且把所有的命令作为一个整体一起向系统提交或撤销操作请求，即这一组数据库命令要么都执行，要么都不执行。
  • 是一个不可分割的工作逻辑单元，在数据库系统上执行并发操作时，事务是最小的控制单元
  • 适用于多用户同时操作的数据库系统的场景，如银行、保险公司及证券交易系统等等
  • 通过事务的整体性以保证数据的一致性

存储引擎的概念

  • mysql中的数据用各种不同的技术存储在文件中，每一种技术都使用不同的存储机制、索引技巧、锁定水平并最终提供不同的功能和能力，这些不同的技术以及配套的功能在mysql中称为存储引擎。
  • 存储引擎是mysql将数据存储在文件系统中的存储方式或者存储格式
  • mysql数据库中的组件，负责执行实际的数据i/o操作
  • mysql系统中，存储引擎处于文件系统之上，在数据保存到数据文件之前会传输到存储引擎，之后按照各个存储引擎的存储格式存储

一、 事务的特性

事务四大特性（ACID）

ACID，是指在可靠数据库管理系统（DBMS）中，事务(transaction)应该具有的四个特性：原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）、持久性（Durability）。这是可靠数据库所应具备的几个特性。

原子性（Atomicity）

指事务是一个不可再分割的工作单位，事务中的操作要么都发生，要么都不发生。

  • 事务时一个完整的操作，事务的元素是不可分的
  • 事务中的所有元素必须作为一个整体提交或者回滚
  • 如果事务中的任何元素失败，则整个事务将失败

一致性（Consistency）

指在事务开始之前和事务结束以后，数据库的完整性约束没有被破坏

  • 当事务完成时，数据必须处于一致状态
  • 在事务开始前，数据库中存储的数据处于一致状态
  • 在正在进行的事务中，数据可能处于不一致的状态
  • 当事务成功完成时，数据必须再次回到已知的一致状态

隔离性（Isolation）

指在并发环境中，当不同的事务同时操纵相同的数据时，每个事务都有各自的完整数据空间。

  • 对数据进行修改的所有并发事务是彼此隔离的，表明事务必须是独立的，它不应以任何方式依赖于或影响其他事务
  • 修改数据的事务可在另一个使用相同数据的事务开始之前访问这些数据，或者在另一个使用相同数据的事务结束后开始访问这些数据

持久性（Durability）

指在事务处理结束后结果是永久保留的。

  • 指不管系统是否发生故障，事务处理的结果都是永久的
  • 一旦事务被提交，事务的效果会永久的保留在数据库中

总结：在事务管理中，原子性是基础，隔离性是手段，一致性是目的，持久性是结果。

二、事务间的相互影响

#脏读 ：一个事务读取了另一个事务未提交的数据，而这个数据是有可能回滚的。

1.png

#不可重复读： 指在一个事务内，多次读同一数据。 在这个事务还没有结束时，另外一个事务也访问该同一数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改，那么第一个事务两次读到的的数据可能是不一样的。这样就发生了在一一个事务内两次读到的数据是不一样的，因此称为是不可重复读。( 即不能读到相同的数据内容)

2.png

#幻读 ：一个事务对一个表中的数据进行了修改，这种修改涉及到表中的全部数据行。同时，另一个事务也修改这个表中的数据，这种修改时向表中插入一行新的数据。那么，操作前一个事务的用户会发现表中还没有修改的数据行，就好像发生了幻觉一样。\

3.png

#丢失更新 ：两个事务同时读取了同一条记录，A先修改记录，B也修改记录（B不知道A修改过），B提交数据后B的修改结果覆盖了A的修改结果。

4.png

可以通过添加事务的隔离级别解决上述的一致性问题。

事务的4大隔离

  • read uncommitted： 读取尚未提交的数据，不解决脏读 RU
  • read committed：读取已经提交的数据，可以解决脏读 RC
  • repeatable read：重复读取：可以解决脏读和不可重复读–mysql默认的 RR
  • serializable：串行化，可以解决脏读不可重复读和幻读，（事务串行化）会降低数据库执行效率——相当于锁表

三、事务控制语句

Mysql事务默认时自动提交的，当sql语句提交时事务便自动提交

PS：我门日常使用sql语句进行操作之所以可以执行成功，是因为mysql实现了自动提交、执行操作。而我们也可以关闭这个’自动提交功能’，也就是说我们只能手动提交，而我们提交前，我们做的修改是在内存中修改的，并不会提交到磁盘，一旦提交到磁盘，那就表示对数据库中的数据进行了修改，mysql会把我们修改的对象（表）提交一份副到内存中，方便我们进行修改，改完之后执行’提交’，就可以直接修改实际数据。

#开启事务
begin或者start transaction或者set autocommit=0（禁止自动提交）;
#提交事务，并使已对数据库进行的修改变成永久性的
commit或者commitwork
PS：执行了commit后，才会促发I/O流进行写入
#回滚会结束用户的事务，并撤销正在进行的所有未提交的修改
rollback或者rollback work

#开启、提交事务
begin;
update test1 set address='云南' where name='张三';
commit;

select * from test1;

#事务回滚
begin;
update test1 set address='山东' where name='王五';
rollback;

select * from test1;

#多点回滚
begin;
update test1 set address='上海' where name='王五';
savepoint s1;
update test1 set address='苏州' where name='张三';
savepoint s2;
insert into test1 values(3,'C','赵六');
select * from test1;
rollback to s1;
select * from test1;
commit;

使用set 设置控制事务

set autocommit=0； #禁止自动提交
set autocommit=1； #开启自动提交，mysql默认为1
show variables like ‘autocommit’； #查看mysql中的autocommit值

存储引擎

常用的存储引擎

1.MyISAM
2.InnoDB

一、MyISAM

  • MyISAM存储引擎使Mysql关系数据库系统5.5版本之前默认的存储引擎，前身是ISAM
  • ISAM是一个定义明确且经历时间考验的数据表格管理方法，在设计之时就考虑到数据库被查询的次数要远大于更新的次数

1.ISAM的特点

  • ISAM执行读取操作的速度很快
  • 不支持事务处理
  • 不占用大量的内存和存储资源
  • 不能够容错
  • 使用create table 创建时，默认使用的是MyISAM存储引擎

2.MyISAM的特点

  • myISAM不支持事务，也不支持外键
  • 访问速度快
  • 对事物完整性没有要求
  • MyISAM在磁盘上存储成三个文件
    {.frm文件存储定义 }
    { 数据文件的拓展名为.MYD（MYData) }
    { 索引文件的拓展名是.MYI(MYindex) }

3.MyISAM适用的工作场景

（1）公司业务不需要事务的支持
（2）单方面读取或写入数据比较多的业务
（3）MyISAM存储引擎数据读写都比较频繁场景不适合
（4）使用读写并发访问相对较低的业务
（5）数据修改相对较少的业务
（6）对数据业务一致性要求不是非常高的业务
（7）服务器硬件资源相对比较差

4.MyISAM支持的存储格式

  1. 静态表 静态表是默认的存储格式。静态表中的字段都是非可变字段，这样每个记录都是固定长度的。这种存储方式的优点是存储非常迅速，容易缓存，出现故障容易恢复；

  2. 动态表 动态表包含可变字段，记录不是固定长度的。
    这样存储的有带你石占用空间较少，但是频繁的更新、删除记录会产生碎片，需要定期执行OPTIMIZE TABLE语句或者myisamchk -r命令来改变性能。并且出现故障的时候恢复比较困难。

  3. 压缩表 压缩表有myisamchk工具创建，占据非常小的空间，因为每条记录都是被单独压缩的，所以只有非常小的访问开支。

二、Innodb

Innodb特点 （1）支持事务，支持4个事务隔离级别
（2）行级锁定，但是全表扫描仍然会是表级锁定
（3）读写阻塞与事务隔离级别有关
（4）具有非常高效的缓存性能，能缓存索引，也能缓存数据
（5）表与主键以簇的方式存储
（6）支持分区、表空间、类似oracle数据库
（7）支持外键约束，5.5以前不支持全文索引，5.5版本后会用到全文索引
（8）对硬件资源的要求还是比较高的场合

示例:
update table set a=1 where user like "%zhang%";
InnoDB中不保存表的行数,如'select count(*) from table;'时InnoDB需要扫描一遍整个表来计算有多少行,但是myISAM只要简单的读出保存好的行数就行。
需要注意的是当count(*)语句包含where条件时MyISAM也需要扫描整个表

对于自增长的字段，InnoDB中必须包含只有该字段的索引
EXTRA：清空整个表时，InnoDB是一行一行的删除，效率非常慢。MyISAM则会重建表

innodb 存储引擎的数据库在磁盘上存储成三个文件
db.opt(表属性文件)
表名.frm(表结构文件)
表名.ibd(表数据元数据)

#查询系统支持的存储引擎
show engines;

#查看指定表使用的存储引擎
#方法一：
show table status from 库名 where name='表名'\G
#方法二：
show create table [库名.]表名

#修改存储引擎
#方式一(alter)
alter table 表名 engine=MyISAM;
#方式二(配置文件):
vim /etc/my.cnf
[mysqld]
default-storage-engine=INNODB

systemctl restart mysqld
PS:此方法只对修改了配置文件并重启mysql服务后新创建的表有效，已存在的表不会变更。

#方式三(create):
create table status from school where name='Test';

1.DDL:create table/view/index/SYN/SLUSTER
2.DML:update、delete、insert into
3.DCL：grant、revoke、deny(拒绝授权)、rollback、commit等
4.DQL：select from where

DDL：数据定义语言,用于创建数据库对象，如库、表、索引等
DML：数据操纵语言,用于对表中的数控进行管理
DQL：数据查询语言,用于从数据表中查找符合条件的数据记录
DCL：数据查询语言,用于设置或者更改数据库用户或者角色权限
avatar
文章
阅读
粉丝