Mysql-事务与存储引擎

2021年09月16日 阅读数:3
这篇文章主要向大家介绍Mysql-事务与存储引擎,主要内容包括基础应用、实用技巧、原理机制等方面,希望对大家有所帮助。

Mysql-事务与存储引擎

 

 

 

 

 

 

 

                1、MySQL 事物mysql

                2、MySQL 存储引擎sql

                3、MyISAM适用的生产场景举例数据库

                4、InnoDB特色vim

                5、InnoDB适用生产场景分析缓存

                6、企业选择存储引擎依据服务器

 

 

 

 

 

 

 

1、MySQL 事物

1.事务的概念session

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

说白了,所谓事务,它是一个操做序列,这些操做要么都执行,要么都不执行,它是一个不可分割的工做单位。oracle

2.事务的ACID特色高并发

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

1)原子性

●原子性:指事务是一个不可再分割的工做单位,事务中的操做要么都发生,要么都不发生。
事务是一个完整的操做,事务的各元素是不可分的。
事务中的全部元素必须做为一个总体提交或回滚。
若是事务中的任何元素失败,则整个事务将失败。

案例:
A给B转账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

查询全局事务隔离级别

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.事务控制语句

BEGINSTART TRANSACTION:显式地开启一个事务。
COMMITCOMMIT WORK:提交事务,并使已对数据库进行的全部修改变为永久性的。
ROLLBACKROLLBACK WORK:回滚会结束用户的事务,并撤销正在进行的全部未提交的修改。
SAVEPOINT S1:使用 SAVEPOINT 容许在事务中建立一个回滚点,一个事务中能够有多个 SAVEPOINT;“S1”表明回滚点名称。
ROLLBACK TO [SAVEPOINT] S1:把事务回滚到标记点

案例:
use gxs;
create table account(  
id int(10) primary key not null,  
name varchar(40),  
money double  
);

insert into account values(1,'A',1000);  
insert into account values(2,'B',1000);  

#测试提交事务
begin;
update account set money= money - 100 where name='A';
commit;
quit

mysql -u root -p
use gxs;
select * from account;

#测试回滚事务
begin;
update account set money= money + 100 where name='A';
rollback;

mysql -u root -p
use gxs;
select * from account;

#测试多点回滚
begin;
update account set money= money + 100 where name='A';
SAVEPOINT S1;
update account set money= money + 100 where name='B';
SAVEPOINT S2;
insert into account values(3,'C',1000);

select * from account;
ROLLBACK TO S1;
select * from account;

例:

测试提交事务

 

 

 

 

测试回滚事务

测试多点回滚

 

 

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; 都是独立的事务。 use gxs; select * from account; SET AUTOCOMMIT=0; update account set money= money + 100 where name='B'; select * from account; quit mysql -u root -p use gxs; select * from account;

2、MySQL 存储引擎

1.存储引擎概念

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

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

• MySQL经常使用的存储引擎
▷MyISAM
▷InnoDB

• MySQL数据库中的组件,负责执行实际的数据/O操做

• MySQL系统中,存储引擎处于文件系统之上,在数据保存到数据文件以前会传输到存储引擎,以后按照各个存储引擎的存储格式进行存储

2.MyISAM的特色介绍

• MyISAM不支持事务,也不支持外键约束,只支持全文索引,数据文件和索引文件是分开保存的
• 访问速度快,对事务完整性没有要求
• MyISAM适合查询、插入为主的应用
• MyISAM在磁盘上存储成三个文件,文件名和表名都相同,可是扩展名分别为:

▷.frm文件存储表结构的定义
▷数据文件的扩展名为.MYD (MYData)
▷索引文件的扩展名是.MYI (MYIndex)

• 表级锁定形式,数据在更新时锁定整个表
• 数据库在读写过程当中相互阻塞
▷会在数据写入的过程阻塞用户数据的读取
▷也会在数据读取的过程当中阻塞用户的数据写入
• 数据单独写入或读取,速度过程较快且占用资源相对少
• MyIAM支持的存储格式
▷静态表
▷动态表
▷压缩表

3、MyISAM适用的生产场景举例

• 公司业务不须要事务的支持
• 单方面读取或写入数据比较多的业务
• MyISAM存储引擎数据读写都比较频繁场景不适合
• 使用读写并发访问相对较低的业务
• 数据修改相对较少的业务
• 对数据业务-致性要求不是很是高的业务
• 服务器硬件资源相对比较差

4、InnoDB特色

• 支持事务,支持4个事务隔离级别
• MySQL从5.5.5版本开始,默认的存储引擎为InnoDB
• 读写阻塞与事务隔离级别相关
• 能很是高效的缓存索引和数据
• 表与主键以簇的方式存储
• 支持分区、表空间,相似oracle数据库
• 支持外键约束,5.5前不支持全文索引,5.5后支持全文索引
• 对硬件资源要求仍是比较高的场合
• 行级锁定,可是全表扫描仍然会是表级锁定,如

update table set a=1 where user like '%zhang%';

• InnoDB中不保存表的行数,如select count(* ) from table;时,InnoDB须要扫描一遍整个表来计算有多少行,可是MyISAM只要简单的读出保存好的行数便可。须要注意的是,当count(*)语句包含where条件时MyISAM也须要扫描整个表。

• 对于自增加的字段,InnoDB 中必须包含只有该字段的索引,可是在MyISAM表中能够和其余字段一块儿创建组合索引。

•清空整个表时,InnoDB是- -行一 行的删除,效率很是慢。MyISAM则会重建表。

5、InnoDB适用生产场景分析

• 业务须要事务的支持
• 行级锁定对高并发有很好的适应能力,但需确保查询是经过索引来完成
• 业务数据更新较为频繁的场景
▷ 如:论坛,微博等
• 业务数据一致性要求较高
▷如:银行业务
• 硬件设备内存较大,利用InnoDB较好的缓存能力来提升内存利用率,减小磁盘I0的压力

6、企业选择存储引擎依据

• 须要考虑每一个存储引擎提供了哪些不一样的核心功能及应用场景

• 支持的字段和数据类型
▷全部引擎都支持通用的数据类型
▷但不是全部的引擎都支持其它的字段类型,如二进制对象

• 锁定类型:不一样的存储引擎支持不一样级别的锁定
▷表锁定: MyISAM 支持
▷行锁定: InnoDB 支持

索引的支持
•创建索引在搜索和恢复数据库中的数据时能显著提升性能
•不一样的存储弓|擎提供不一样的制做索引的技术
•有些存储弓|擎根本不支持索引

事务处理的支持
•提升在向表中更新和插入信息期间的可靠性
•可根据企业业务是否要支持事务选择存储引擎
MyISAM 表支持 3 种不一样的存储格式

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

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

(3)压缩表
压缩表由 myisamchk 工具建立,占据很是小的空间,由于每条记录都是被单独压缩的,因此只有很是小的访问开支。

查看系统支持的存储引擎

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;

例:

经过 alter table 修改