Mysql 事务及存储引擎

事务

概念

事务是一个不可分割的数据库操作序列，也是数据库并发控制的基本单位，其执行的结果必须使数据库从一种一致性状态变到另一种一致性状态。事务是逻辑上的一组操作，要么都执行，要么都不执行

特性ACID

• 原子性（Atomicity）
  事务是一个不可再分割的工作单位，事务中的操作要么都发生，要么都不发生

• 一致性（Consistency）
  执行事务前后，数据保持一致，多个事务对同一个数据读取的结果是相同的

• 隔离性（Isolation）
  并发访问数据库时，一个用户的事务不被其他事务所干扰，各并发事务之间数据库是独立的

• 持久性（Durability）
  一个事务被提交之后。它对数据库中数据的改变是持久的，即使数据库发生故障也不应该对其有任何影响

事务之间的影响情况

• 脏读
  某个事务已更新一份数据，另一个事务在此时读取了同一份数据，由于某些原因，前一个RollBack了操作，则后一个事务所读取的数据就会是不正确的

• 不可重复读
  在一个事务的两次查询之中数据不一致，这可能是两次查询过程中间插入了一个事务更新的原有的数据

• 幻读
  在一个事务的两次查询中数据笔数不一致，例如有一个事务查询了几列数据，而另一个事务却在此时插入了新的几列数据，先前的事务在接下来的查询中，就会发现有几列数据是它先前所没有的

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

隔离级别

为了达到事务的四大特性，数据库定义了4种不同的事务隔离级别，由低到高依次为Read uncommitted、Read committed、Repeatable read、Serializable，这四个级别可以逐个解决脏读、不可重复读、幻读这几类问题

READ-UNCOMMITTED(读取未提交)
最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读、幻读或不可重复读

READ-COMMITTED(读取已提交)
允许读取并发事务已经提交的数据，可以阻止脏读，但是幻读或不可重复读仍有可能发生

REPEATABLE-READ(可重复读)
对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改，可以阻止脏读和不可重复读，但幻读仍有可能发生

SERIALIZABLE(可串行化）
最高的隔离级别，完全服从ACID的隔离级别。所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰，也就是说，该级别可以防止脏读、不可重复读以及幻读

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 级别；
会话
set session transaction isolation level 级别；

控制语句

BEGIN;   开启一个事务
COMMIT;  提交一个事务
ROLLBACK;回滚事务，撤销所有未提交的修改
SAVEPOINT BACK1; 创建一个回滚点，一个事务可用有多个回滚点，'back1'回滚点名称
ROLLBACK TO 回滚点名; 把事务回滚到标记点

SET AUTOCOMMIT=0;					禁止自动提交
SET AUTOCOMMIT=1;					开启自动提交，Mysql默认为1
SHOW VARIABLES LIKE 'AUTOCOMMIT';	查看Mysql中的AUTOCOMMIT值

存储引擎

概念

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

常用的Mysql存储引擎主要有，MyISAM 和 InnoDB 两种

MyISAM 和 InnoDB 的区别

-	MyISAM	Innodb
存储结构	每张表被存放在三个文件：frm-表格定义、MYD(MYData)-数据文件、MYI(MYIndex)-索引文件	所有的表都保存在同一个数据文件中（也可能是多个文件，或者是独立的表空间文件），InnoDB表的大小只受限于操作系统文件的大小，一般为2GB
存储空间	MyISAM可被压缩，存储空间较小	InnoDB的表需要更多的内存和存储，它会在主内存中建立其专用的缓冲池用于高速缓冲数据和索引
文件格式	数据和索引是分别存储的，数据.MYD，索引.MYI	数据和索引是集中存储的，.ibd
记录存储顺序	按记录插入顺序保存	按主键大小有序插入
外键	不支持	支持
事务	不支持	支持
哈希索引	不支持	支持
全文索引	支持	不支持
锁支持	（锁是避免资源争用的一个机制，MySQL锁对用户几乎是透明的） 表级锁定	行级锁定、表级锁定，锁定力度小并发能力高
索引的实现方式	B+树索引，myisam 是堆表	B+树索引，Innodb 是索引组织表

查看存储引擎

show engines;

查看表的存储引擎

show table status from 库名 where name=‘表名’\G

show create table 表名\G

修改存储引擎

直接修改

alter table 表名 engine=MyISAM;

建表时指定存储引擎

create table 表名(字段 类型,…) engine=MyISAM;

修改 /etc/my.cnf 配置文件，指定默认存储引擎

default-storage-engine

vim /etc/my.cnf
…
[mysqld]
…
default-storage-engine=引擎类型

重启服务后，建立的表就是默认的存储引擎

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