您好,欢迎访问代理记账网站
移动应用 微信公众号 联系我们

咨询热线 -

电话 15988168888

联系客服
  • 价格透明
  • 信息保密
  • 进度掌控
  • 售后无忧

MySQL事务与存储引擎

文章目录

  • 一、MySQL 事务
    • 1、事务的概念
    • 2、事务的ACID特点
      • 1.原子性
      • 2.一致性
      • 3.隔离性
          • 事务之间的相互影响分为几种,分别为:
          • Mysql 及事物隔离级别:
      • 4.持久性
    • 3、事务控制语句
    • 4、使用 set 设置控制事务
  • 二、MySQL 存储引擎
    • 1、存储引擎概念
        • MySQL常用的存储引擎
    • 2、MyISAM 表支持 3 种不同的存储格式:
    • 3、控制语句


一、MySQL 事务

1、事务的概念

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

简单来说事务,它是一个操作序列,这些操作要么都执行,要么都不执行,它是一个不可分割的工作单位。

2、事务的ACID特点

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

1.原子性

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

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

比如甲给乙转帐100元钱的时候只执行了扣款语句,就提交了,此时如果突然断电,A账号已经发生了扣款,B账号却没收到加款,在生活中就会引起纠纷。这种情况就需要事务的原子性来保证事务要么都执行,要么就都不执行。

2.一致性

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

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

案例:
对银行转帐事务,不管事务成功还是失败,应该保证事务结束后表中A和B的存款总额跟事务执行前一致。

3.隔离性

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

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

(1)==脏读:==一个事务读取了另一个事务未提交的数据,而这个数据是有可能回滚的。
(2)==不可重复读:==一个事务内两个相同的查询却返回了不同数据。这是由于查询时系统中其他事务修改的提交而引起的。
(3)==幻读:==一个事务对一个表中的数据进行了修改,这种修改涉及到表中的全部数据行。同时,另一个事务也修改这个表中的数据,这种修改是向表中插入一行新数据。那么,操作前一个事务的用户会发现表中还有没有修改的数据行,就好象发生了幻觉一样。
(4)==丢失更新:==两个事务同时读取同一条记录,A先修改记录,B也修改记录(B不知道A修改过),B提交数据后B的修改结果覆盖了A的修改结果。

Mysql 及事物隔离级别:

(1)read uncommitted : 读取尚未提交的数据 :不解决脏读
(2)read committed:读取已经提交的数据 :可以解决脏读
(3)repeatable read:重读读取:可以解决脏读 和 不可重复读 —mysql默认的
(4)serializable:串行化:可以解决 脏读 不可重复读 和 虚读—相当于锁表

mysql默认的事务处理级别是 repeatable read ,而Oracle和SQL Server是 read committed 。

事务隔离级别脏读不可重复读幻读第一类丢失更新第二类丢失更新
read uncommitted允许允许允许禁止允许
read committed禁止允许允许 禁止允许
repeatable read禁止禁止允许禁止 禁止
serializable禁止禁止禁止禁止 禁止

查询全局事务隔离级别

show global variables like '%isolation%';
SELECT @@global.tx_isolation;

在这里插入图片描述

查询会话事务隔离级别:

show session variables like '%isolation%';
SELECT @@session.tx_isolation; 
SELECT @@tx_isolation;

在这里插入图片描述

设置全局事务隔离级别:

set global transaction isolation level read committed;

在这里插入图片描述

设置会话事务隔离级别:

set session transaction isolation level read committed;

在这里插入图片描述

4.持久性

在事务完成以后,该事务所对数据库所作的更改便持久的保存在数据库之中,并不会被回滚。

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

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

3、事务控制语句

BEGIN 或 START TRANSACTION 显式地开启一个事务。

COMMIT 或 COMMIT WORK:提交事务,并使已对数据库进行的所有修改变为永久性的。

ROLLBACK 或 ROLLBACK WORK:回滚会结束用户的事务,并撤销正在进行的所有未提交的修改。

SAVEPOINT S1:使用 SAVEPOINT 允许在事务中创建一个回滚点,一个事务中可以有多个 SAVEPOINT;“S1”代表回滚点名称。

ROLLBACK TO [SAVEPOINT] S1:把事务回滚到标记点。

案例:测试事务控制语句
1、创建表和数据
在这里插入图片描述
在这里插入图片描述
在这里插入图片描述

在这里插入图片描述
2、测试begin和commit(开始事务和提交事务)
在这里插入图片描述
在这里插入图片描述
3、测试事务未提交的回滚

在这里插入图片描述4、测试创建回滚点并进行回滚

在这里插入图片描述

在这里插入图片描述

4、使用 set 设置控制事务

SET AUTOCOMMIT=0; 禁止自动提交

SET AUTOCOMMIT=1; 开启自动提交,Mysql默认为1

SHOW VARIABLES LIKE ‘AUTOCOMMIT’; 查看Mysql中的AUTOCOMMIT值

  • 如果没有开启自动提交,当前会话连接的mysql的所有操作都会当成一个事务直到你输入rollback|commit;当前事务才算结束。当前事务结束前新的mysql连接时无法读取到任何当前会话的操作结果。

  • 如果开起了自动提交,mysql会把每个sql语句当成一个事务,然后自动的commit。
    在这里插入图片描述

  • 当然无论开启与否,begin; commit|rollback; 都是独立的事务。

二、MySQL 存储引擎

1、存储引擎概念

MySQL中的数据用各种不同的技术存储在文件中,每种技术都使用不同的存储机制、索引技巧、锁定水平并最终提供不同的功能和能力,这些不同的技术以及配套的功能在MySQL中称为存储引擎

存储引擎是MySQL将数据存储在文件系统中的存储方式或者存储格式

MySQL常用的存储引擎

MyISAM

MyISAM不支持事务,也不支持外键约束,支持全文索引,数据文件和索引文件是分开保存的
访问速度快,对事务完整性没有要求
MyISAM适合查询、插入为主的应用
MyISAM在磁盘.上存储成三个文件,文件名和表名都相同,但是扩展名分别为:
.frm文件存储表结构的定义
数据文件的扩展名为.MYD(MYData)
索引文件的扩展名是.MYI(MYIndex)
表级锁定形式,数据在更新时锁定整个表
数据库在读写过程中相互阻塞
会在数据写入的过程阻塞用户数据的读取
也会在数据读取的过程中阻塞用户的数据写入
数据单独写入或读取,速度过程较快且占用资源相对少

InnoDB

支持事务,支持4个事务隔离级别 MySQL从5.5.5版本开始,默认的存储引擎为InnoDB 读写阻塞与事务隔离级别相关
读写阻塞与事务隔离级别相关 能非常高效的缓存索引和数据 表与主键以簇的方式存储 支持分区、表空间,类似Oracle数据库
支持外键约束,5.5前不支持全文索引,5.5后支持全文索引 对硬件资源要求还是比较高的场合
行级锁定,但是全表扫描仍然会是表级锁定,如updata table set a=1 where user like ‘%zhang%’
InnoDB中不保存表的行数,如select count() from
table;时,InnoDB需要扫描一遍整个表来计算有多少行,但MyISAM只要简单的读出保存号的行数即可,需要注意的是,当count()语句包含where条件时MyISAM也需要扫描整个表
对于自增长的字段,InnoDB中必须包含只有该字段的索引,但是在MyISAM表中可以和其他字段一起建立组合索引
清空整个表时,InnoDB是一行一行的删除,效率非常慢,MyISAM则会重建表

2、MyISAM 表支持 3 种不同的存储格式:

静态(固定长度)表
静态表是默认的存储格式。静态表中的字段都是非可变字段,这样每个记录都是固定长度的,这种存储方式的优点是存储非常迅速,容易缓存,出现故障容易恢复;缺点是占用的空间通常比动态表多。

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

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

3、控制语句

查看系统支持的存储引擎

show engines;

在这里插入图片描述

查看表使用的存储引擎
方法一:

show table status from 库名 where name='表名'\g

在这里插入图片描述

方法二:

use 库名;
show create table 表名;

修改存储引擎
1.通过 alter table 修改

use 库名;
alter table 表名 engine=MyISAM;

在这里插入图片描述

在这里插入图片描述

2.通过修改 /etc/my.cnf 配置文件,指定默认存储引擎并重启服务

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

systemctl restart mysql.service

注意:此方法只对修改了配置文件并重启mysql服务后新创建的表有效,已经存在的表不会有变更。

3.通 过 create table 创建表时指定存储引擎

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

在这里插入图片描述


分享:

低价透明

统一报价,无隐形消费

金牌服务

一对一专属顾问7*24小时金牌服务

信息保密

个人信息安全有保障

售后无忧

服务出问题客服经理全程跟进