目录
6.7.2 通过修改 /etc/my.cnf 配置文件 来修改存储引擎
6.7.3 通过 create table 创建表时指定存储引擎
一、事务的概念
- 事务是一种机制、一个操作序列,包含了一组数据库操作命令,并且把所有的命令作为一个 整体一起向系统提交或撤销操作请求,即这一组数据库命令要么都执行,要么都不执行。
- 事务是一个不可分割的工作逻辑单元,在数据库系统上执行并发操作时,事务是最小的控制单元。
- 事务适用于多用户同时操作的数据库系统的场景,如银行、保险公司及证券交易系统等等。
- 事务通过事务的整体性以保证数据的一致性。
- 事务能够提高在向表中更新和插入信息期间的可靠性。
数据库是一个公司的核心资产,关系型数据库是可靠的,就是因为有事务的存在。
二、事务的ACID特点
ACID,是指在可靠数据库管理系统(DBMS) 中,事务(transaction)应该具有的四个特性:原子性(Atomicity) 、一致性(Consistency )、隔离性(Isolation) 、持久性(Durability) 。这是可靠数据库所应具备的几个特性。
2.1 原子性(Atomicity)
原子性:事务是一个不可再分割的工作单位,事务中的操作要么都发生,要么都不发生。
- 事务是一个完整的操作,事务的各元素是不可分的。
- 事务中的所有元素必须作为一个整体提交或回滚。
- 如果事务中的任何元素失败,则整个事务将失败。
2.2 一致性(Consistency)
一致性:指在事务开始之前和事务结束以后,数据库的完整性约束没有被破坏。
- 当事务完成时,数据必须处于一致状态 。
- 在事务开始前,数据库中存储的数据处于一致状态。
- 在正在进行的事务中,数据可能处于不一致的状态。
- 当事务成功完成时,数据必须再次回到E知的一致状态。
2.3 隔离性(Isolation)
隔离性:指在并发环境中,当不同的事务同时操纵相同的数据时,每个事务都有各自的完整数据空间。
- 对数据进行修改的所有并发事务是彼此隔离的,表明事务必须是独立的,它不应以任何方式依赖于或影响其他事务。
- 修改数据的事务可在另一个使用相同数据的事务开始之前访问这些数据,或者在另一一个使用相同数据的事务结束之后访问这些数据。
- 也就是说并发访问数据库时,一个用户的事务不被其他事务所干扰,各并发事务之间数据库是独立的。
2.4 持久性(Durability)
持久性:在事务完成以后,该事务所对数据库所作的更改便持久的保存在数据库之中,并不会被回滚。
- 指不管系统是否发生故障,事务处理的结果都是永久的。
- 一旦事务被提交,事务的效果会被永久地保留在数据库中。
三、事务之间的相互影响
一个事务的执行不能被其他事务干扰,事务之间的相互影响分为几种,分别为脏读、不可重复读、幻读、丢失更新。
3.1 脏读(读取未提交数据)
脏读指的是读到了其他事务未提交的数据,未提交意味着这些数据可能会回滚,也就是可能最终不会存到数据库中,也就是不存在的数据,读到了不一定最终存在的数据,这就是脏读。
脏读是读到了别的事务回滚前的脏数据。
比如事务B执行过程中修改了数据x,在未提交前,事务A读取了数据x,而事务B却回滚了,这样事务A就形成了脏数读。
也就是说,当前事务读到的数据是别的事务想要修改的,但是没有修改成功的数据。
3.2 不可重复读
指在一个事务内,多次读同一数据。 在这个事务还没有结束时,另外一个事务也访问该同一数据。那么,在第一个事务中的两次读数据之间,由于第二个事务的修改,那么第一个事务两次读到的的数据可能是不一样的。这样就发生了在一一个事务内两次读到的数据是不一样的,因此称为是不可重复读。( 即不能读到相同的数据内容)
3.3 幻读
一个事务对一个表中的数据进行了修改,这种修改涉及到表中的全部数据行。同时,另一个事务也修改这个表中的数据,这种修改是向表中插入一行新数据。 那么,操作前一个 事务的用户会发现表中还有没有修改的数据行,就好象发生了幻觉一样 。
3.4 丢失更新
两个事务同时读取同一条记录,A先修改记录,B也修改记录(B不知道A修改过),B提交数据后B的修改结果覆盖了A的修改结果。
可以通过添加事务的隔离级别解决上述的一致性问题。
四、事务的隔离级别
4.1 隔离级别
事务的隔离级别决定了事务之间可见的级别。
MySQL事务支持如下四种隔离,用以控制事务所做的修改,并将修改通告至其它并发的事务:
未提交读(Read Uncommitted(RU)):
允许脏读,即允许一个事务可以看到其他事务未提交的修改。
提交读(Read Committed (RC)):
允许一个事务只能看到其他事务已经提交的修改,未提交的修改是不可见的。防止脏读。
可重复读(Repeatable Read(RR)):——mysql默认的隔离级别
确保如果在一个事务中执行两次相同的SELECT语句,都能得到相同的结果,不管其他事务是否提交这些修改。可以防止脏读和不可重复读。
串行读(serializable):——相当于锁表
完全串行化的读,将一个事务与其他事务完全地隔离。每次读都需要获得表级共享锁,读写相互都会阻塞。可以防止脏读,不可重复读取和幻读,(事务串行化)会降低数据库的执行效率。
4.2 查询全局事务隔离级别
show global variables like '%isolation%'; 方法一
SELECT @@global.tx_isolation; 方法二
注释:
like:表示模糊查询。
'%isolation%':表示包含isolation字符串。
'isolation%':以isolation字符串开头。
'%isolation':以isolation字符串结尾。
4.3 查询会话事务隔离级别
session variables 指会话变量。
show session variables like '%isolation%'; 方法一
SELECT @@session.tx_isolation; 方法二
SELECT @@tx_isolation; 方法三
4.4 设置全局事务隔离级别
设置全局事务隔离级别:
全局级别设置之后,当前会话需要退出重新进入才会生效。
set global transaction isolation level 隔离级别; 永久生效
set global transaction isolation level read committed; #将全局事务隔离级别设置为提交读(RC)
4.5 设置会话事务隔离级别:
会话事务隔离级别只对当前连接有效,退出连接后失效。在其他终端连接无效。
再次连接后会恢复为全局事务的隔离级别。
set session transaction isolation level 隔离级别;
五、事务控制语句
- BEGIN 或 START TRANSACTION: 显式地开启一个事务。
- COMMIT 或 COMMITWORK: 提交事务,并使已对数据库进行的所有修改变为永久性的。
- ROLLBACK 或 ROLLBACK WORK: 回滚会结束用户的事务,并撤销正在进行的所有未提交的修改。
- SAVEPOINT S1: 使用SAVEPOINT 允许在事务中创建一个回滚点,一个事务中可以有多个SAVEPOINT;“S1”代表回滚点名称。
- ROLLBACK TO [SAVEPOINT] S1: 把事务回滚到标记点。
5.1 测试begin和commit(开始事务和提交事务)
BEGIN: 显式地开启一个事务。
COMMIT: 提交事务,并使已对数据库进行的所有修改变为永久性的。
create user 'aaa'@'localhost' identified by '123456';
创建一个新用户,来测试
grant select on wangwu.* to 'aaa'@'localhost' identified by '123456';
授予用户查看的权限
5.2 测试回滚事务
5.3 测试多点回滚
5.4 使用 set 设置控制事务
在mysql中执行单独的命令会立即生效,是因为Mysql默认开启自动提交。
使用 set 设置控制事务:
set AUTOCOMMIT=0; 禁止自动提交(仅针对当前会话)
set AUTOCOMMIT=1; 开启自动提交(仅针对当前会话),Mysql默认为1
set global AUTOCOMMIT=0; 禁止自动提交(针对全局事务)
set global AUTOCOMMIT=1; 开启自动提交(针对全局事务),Mysql默认为1
show variables like 'AUTOCOMMIT'; 查看当前会话的AUTOCOMMIT值
show global variables like 'AUTOCOMMIT'; 查看全局事务的AUTOCOMMIT值
- 如果没有开启自动提交,当前会话连接的mysql的所有操作都会当成一个事务直到你输入
rollback; 或 commit;
当前事务才算结束。当前事务结束前新的mysql连接时无法读取到任何当前会话的操作结果。 - 如果开起了自动提交,mysql 会把每个sql 语句当成一个事务,然后自动的commit。
- 当然无论开启与否,
begin; commit | rollback;
都是独立的事务。
5.4.1 关闭自动提交
因为我们此时关闭了自动提交,所以需要执行commit提交命令才行
对当前会话设置关闭自动提交,退出后重新进入,系统又会自动开启(因为全局事务默认开启自动提交,如果全局设置为关闭自动提交,则重新连接后会是关闭状态):
六、Mysql存储引擎
6.1 存储引擎的概念
什么是存储引擎
- MySQL中的数据用各种不下同的技术存储在文件中,每一种技术都使用不同的存储机制、索引技巧、锁定水平并最终提供不同的功能和能力,这些不同的技术以及配套的功能在MySQL中称为存储引擎。
- 存储引擎是MySQL将数据存储在文件系统中的存储方式或者存储格式。
- 存储引擎是MySQL数据库中的组件,负责执行实际的数据I/O操作。
- MySQL系统中,存储引擎处于文件系统之上,在数据保存到数据文件之前会传输到存储引擎,之后按照各个存储引擎的存储格式进行存储。
MySQL常用的存储引擎
- MylSAM
- InnoDB
注意:一个表只能使用一个存储引擎,一个库中不同的表可以使用不同的存储引擎。
6.2 MyISAM引擎介绍
-
MylSAM不支持事务,也不支持外键约束,只支持全文索引,数据文件和索引文件是分开保存的。
-
访问速度快,对事务完整性没有要求。
-
MylSAM适合查询、插入为主的应用。
-
MylSAM在磁盘.上存储成三个文件,文件名和表名都相同,但是扩展名分别为:
-
.frm文件存储表结构的定义
-
数据文件的扩展名为.MYD (MYData)
-
索引文件的扩展名是.MYI (MYIndex)
-
6.2.1 MylSAM的特点
表级锁定形式,数据在更新时锁定整个表。
数据库在读写过程中相互阻塞:
- 会在数据写入的过程阻塞用户数据的读取
- 也会在数据读取的过程中阻塞用户的数据写入
数据单独写入或读取,速度过程较快且占用资源相对少。
6.2.2 MyISAM表支持3种不同的存储格式
静态(固定长度)表
静态表是默认的存储格式。静态表中的字段都是非可变字段,这样每个记录都是固定长度的,这种存储方式的优点是存储非常迅速,容易缓存,出现故障容易恢复;缺点是占用的空间通常比动态表多。
动态表
动态表包含可变字段,记录不是固定长度的,这样存储的优点是占用空间较少,但是频繁的更新、删除记录会产生碎片,需要定期执行OPTIMIZE TABLE语句或myisamchk-r命令来改善性能,并且出现故障的时候恢复相对比较困难(因为会产生磁盘碎片,而且存储空间不是连续的)。
压缩表
压缩表由 myisamchk 工具创建,占据非常小的空间,因为每条记录都是被单独压缩的,所以只有非常小的访问开支。(压缩的过程中会占用CPU性能)
6.2.3 MyISAM使用的生产场景举例
- 公司业务不需要事务的支持
- 单方面读取或写入数据比较多的业务
- MylSAM存储引擎数据读写都比较频繁场景不适合(因为读写是互相阻塞的)
- 使用读写并发访问相对较低的业务
- 数据修改相对较少的业务
- 对数据业务-致性要求不是非常高的业务
- 服务器硬件资源相对比较差(MyISAM占用资源相对少)
6.3 InnoDB存储引擎
6.3.1 InnoDB介绍
-
支持事务,支持4个事务隔离级别
-
MySQL从5.5.5版本开始,默认的存储引擎为InnoDB
-
读写阻塞与事务隔离级别相关
-
能非常高效的缓存索引和数据
-
表与主键以簇的方式存储 BTREE
-
支持分区、表空间,类似oracle数据库
-
支持外键约束,5.5前不支持全文索引,5.5后支持全文索引
-
对硬件资源要求还是比较高的场合
-
行级锁定,但是全表扫描仍然会是表级锁定,如
-
update table set a=1 where user like '%zhang%';
-
6.3.2 InnoDB的特点
-
InnoDB中不保存表的行数,如
select count(*) from table;
时,InnoDB需要扫描一遍整个表来计算有多少行,但是MyISAM只要简单的读出保存好的行数即可。需要注意的是当count(*)语句包含where条件时MyISAM也需要扫描整个表。 -
对于自增长的字段,InnoDB 中必须包含只有该字段的索引,但是在MyISAM表中可以和其他字段一起建立组合索引。
-
delete清空整个表时,InnoDB 是一行一 行的删除,效率非常慢。MyISAM则会重建表。
6.3.3 死锁
MyISAM:表级锁定
Innodb:行级锁定
死锁的产生
- 高并发的情况容易产生死锁。
当两个请求分别访问/读取2行记录,同时又需要读取对方的记录数据,因为(行锁的限制)而造成了阻寨的现象
解决死锁
① 查询线程信息,直接杀死线程,直接kill id就可以。
show processlist; 查看死锁情况 kill 6;杀死死锁
② 等待,可以调整数据的资源,给予mysql更多的内存资源,线程资源,让产生死锁的线程能够冲破释放锁。
如何尽可能避免死锁
1、使用更合理的业务逻辑,以固定的顺序访问表和行。
2、大事务拆小。大事务更倾向于死锁,如果业务允许,将大事务拆小。
3、在同一个事务中,尽可能做到一次锁定所需要的所有资源,减少死锁概率。
4、降低隔离级别。如果业务允许,将隔离级别调低也是较好的选择,比如将隔离级别从RR调整为RC,可以避免掉很多因为gap锁造成的死锁。
5、为表添加合理的索引。如果不使用索引将会为表的每一行记录添加上锁,死锁的概率大大增加。
6.4 企业选择存储引擎依据
业务场景如果并发量大,读写的并发量大,那我们建议使用innoDB
如果单独的写入或是插入单独的查询,那我们建议使用没有INNODB
表级锁: 开销小,加锁快:不会出现死锁:锁定粒度大,发生锁冲突的概率最高,并发度最低;
行级锁: 开销大,加锁慢:会出现死锁;锁定粒度最小,发生锁冲突的概率最低,并发度也最高;
MyISAM不支持事务,也不支持外键约束,只支持全文索引,数据文件和索引文件是分开保存的需要考虑每个存储引擎提供了哪些不同的核心功能及应用场景
双方的区别:
支持的字段和数据类型
所有引擎都支持通用的数据类型
但不是所有的引擎都支持其它的字段类型,如二进制对象
锁定类型:不同的存储引擎支持不同级别的锁定
表锁定: MyISAM支持
行锁定: InnoDB支持
索引的支持
建立索引在搜索和恢复数据库中的数据时能显著提高性能
不同的存储弓|擎提供不同的制作索引的技术
有些存储引擎根本不支持索引
事务处理的支持
提高在向表中更新和插入信息期间的可靠性
根据企业业务是否要支持事务选择存储引擎
6.5 查看系统支持的存储引擎
show engines;
6.6 查看表使用的存储引擎
show table status from 库名 where name='表名'\G
或
show create table 表名; 或 show create table 表名\G 查看表的存储引擎
6.7 修改报表的存储引擎
6.7.1 通过 alter table 修改表的存储引擎
alter table account engine=myisam; 修改表的存储引擎
show create table account\G 查看
6.7.2 通过修改 /etc/my.cnf 配置文件 来修改存储引擎
vim /etc/my.cnf
...............
[mysqld]
default-storage-engine=INNODB
...............
systemctl restart mysqld.service
原有的表不会有变化,而此时后面新建的表会发生改变
6.7.3 通过 create table 创建表时指定存储引擎
create table 表名(字段1 数据类型,...) engine=MyISAM;
create table wangwu.account2 (id int,name char(5)) engine=myisam;