MySQL之事务与存储引擎

MySQL之事务与存储引擎

前言：

事务，它可以说是一个操作的序列，也就是操作的先后。在里面的操作要么是执行，要么就不执行，它的作用不可或缺。而存储，他的存储格式有三种1是静态固定长度表，2是动态表，3是压缩表

一、事务概述

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

二、事务的ACID特点

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

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

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

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

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

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

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

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

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

2.1、事务与事务之间的影响

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

不可重复读: 一个事务内两个相同的查询却返回了不同数据。这是由于查询时系统中其他事务修改的提交而引起的。

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

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

三、事务的隔离

3.1、MySQL对事务的隔离级别作用的范围分为两种：

全局级：对所有的会话有效

会话级：只对当前的会话有效

3.2、MySQL数据库在多个客户端并发地访问一个表时，可能会出现下面的一致性问题：

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

不可重复读: 一个事务内两个相同的查询却返回了不同数据。这是由于查询时系统中其他事务修改的提交而引起的。

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

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

3.3、MySQL事务支持如下四种隔离，用以控制事务所做的修改，并将修改通告至其它并发的事务:

(1)未提交读(Read Uncommitted):

允许脏读，即允许一个事务可以看到其他事务未提交的修改。

(2)提交读(Read Committed):

允许一个事务只能看到其他事务已经提交的修改，未提交的修改是不可见的。

(3)可重复读(Repeated Read): ---mysql默认的隔离级别

确保如果在一个事务中执行两次相同的SELECT语句，都能得到相同的结果，不管其他事务是否提交这些修改。

(4)串行读(Serializable): ---相当于锁表

完全串行化的读，将一个事务与其他事务完全地隔离。每次读都需要获得表级共享锁，读写相互都会阻塞。

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

四、案例

4.1、查询全局事务隔离级别

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

4.2、查询会话事务隔离级别

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

4.3、设置全局事务隔离级别

set global transaction isolation level read committed;

4.4、设置会话事务隔离级别

set session transaction isolation level read committed;

五、事务的控制语句及案例

BEGIN 或 START TRANSACTION：显式地开启一个事务。COMMIT 或 COMMIT WORK：提交事务，并使已对数据库进行的所有修改变为永久性的。ROLLBACK 或 ROLLBACK WORK：回滚会结束用户的事务，并撤销正在进行的所有未提交的修改。SAVEPOINT S1：使用 SAVEPOINT 允许在事务中创建一个回滚点，一个事务中可以有多个SAVEPOINT；“S1”代表回滚点名称。ROLLBACK TO [SAVEPOINT] S1：把事务回滚到标记点。

5.1、案例

create database ys;use ys;create table yr(id int(10) primary key not null,name varchar(40),money double); insert into yr values(1,'lp',200); insert into yr values(2,'wf',2222); select * from yr;

5.2、测试提交事务

begin;update yr set money= money - 100 where name='wf';select * from yr; commit;quit mysql -u root -puse ys;select * from yr;

5.3、测试回滚事务

begin;update yr set money= money + 100 where name='lp';select * from yr; rollback;quitmysql -u root -puse ys;select * from yr;

5.4、测试多点回滚

begin;update yr set money= money + 100 where name='lp';select * from yr;SAVEPOINT S1;update yr set money= money + 100 where name='wf';select * from yr;SAVEPOINT S2;insert into yr values(3,'zzh',120); select * from yr;ROLLBACK TO S1;select * from yr;

5.5、使用 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 ys;select * from yr;SET AUTOCOMMIT=0;SHOW VARIABLES LIKE 'AUTOCOMMIT';update yr set money= money + 100 where name='wf';select * from yr;quit mysql -u root -puse ys;select * from yr;

六、MySQL存储引擎

1、MylSAM表支持3种不同的存储方式

静态(固定长度)表

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

动态表

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

压缩表

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

2、存储引擎概念

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

3、MyISAM的特点

(1)MyISAM不支持事务，也不支持外键约束，只支持全文索引，数据文件和索引文件是分开保存的

(2)访问速度快，对事务完整性没有要求

MyISAM 适合查询、插入为主的应用

(3)MyISAM在磁盘上存储成三个文件，文件名和表名都相同，但是扩展名分别为：

.frm 文件存储表结构的定义

数据文件的扩展名为 .MYD (MYData)

索引文件的扩展名是 .MYI (MYIndex)

(4)表级锁定形式，数据在更新时锁定整个表

数据库在读写过程中相互阻塞:

会在数据写入的过程阻塞用户数据的读取

也会在数据读取的过程中阻塞用户的数据写入

数据单独写入或读取，速度过程较快且占用资源相对少

4、lnnoDB

(1)、支持事务，支持4个事务隔离级别

MySQL从5.5.5版本开始，默认的存储引擎为lnnoDB,之前默认为myisam

(2)、读写阻塞与事务隔离级别相关

读写阻塞与事务隔离级别相关

能非常高效的缓存索引和数据

表与主键以簇的方式存储

支持分区、表空间，类似oracle数据库

支持外键约束，5.5前不支持全文索引，5.5后支持全文索引

(3)、对硬件资源要求还是比较高的场合

行级锁定，但是全表扫描仍然会是表级锁定，如

update table set a=1 where user like ‘%lic%’;

(4)、InnoDB 中不保存表的行数

如 select count() from table; 时，InnoDB 需要扫描一遍整个表来计 算有多少行，但是 MyISAM 只要简单的读出保存好的行数即可。需要注意的是，当 count()语句包含 where 条件时 MyISAM 也需要扫描整个表。对于自增长的字段，InnoDB 中必须包含只有该字段的索引，但是在 MyISAM 表中可以和其他字段一起建立组合索引。

5、查看系统支持的存储引擎

show engines;

6、查看表使用的存储引擎

show table status from 库名 where name='表名'\G； show table status from ys where name='yr'\G

方法一

use 库名;show create table 表名; 例：use ys;show create table yr;

方法二

7、修改存储引擎

7.1、通过alter table修改

use 库名;alter table 表名 engine=MyISAM; 例：use ys;alter table yr engine=myisam;show create table yr\G

7.2 通过修改 /etc/my.cnf 配置文件

退出数据库 quitvim /etc/my.cnf[mysqld]default-storage-engine=INNODB systemctl restart mysqld.service修改/etc/my.cnf配置文件，指定默认存储引擎并且重启use ysshow create table yr\G

7.3、通过 create table 创建表时指定存储引擎

use 库名;create table 表名(字段1 数据类型,...) engine=MyISAM; 例：mysql -u root -puse ys;create table wtb(id int,name char) engine=myisam;show table status from ys where name='wtb'\G;

七、总结

1、学习事务管理的ACID特点，简洁的说就是原子性是基础，隔离性是手段，一致性是目的，持久性是结果

2、myisam和InnoDB引擎的特点及区别

3、数据表引擎的修改及查看方式，

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。