数据库的存储引擎和事务

系统管理员 2023-09-28 23:54 32阅读 0赞

**Mysql默认使用INNODB存储引擎**

数据库中的各表均被（在创建表时）指定的存储引擎来处理。

服务器可用的引擎依赖于以下因素：MySql的版本、服务器在开发时如何被配置、启动选项

为了解当前服务器中有哪些存储引擎可用，可使用show engines\\g语句：

![a9b8b17b1c0f4cdfaa3095f81f716d6c.png][]

①在创建表时，可使用ENGINE选项为CREATE TABLE语句显式指定存储引擎。CREATE TABLE TABLENAME (NO INT) ENGINE = MyISAM;

②如果在创建表时没有显式指定存储引擎，则该表使用当前默认的存储引擎

③默认的存储引擎可在my.ini配置文件中使用default-storage-engine选项指定。

④现有表的存储引擎可使用ALTER TABLE语句来改变：ALTER TABLE TABLENAME ENGINE = INNODB;

⑤为确定某表所使用的存储引擎，可以使用SHOW CREATE TABLE或SHOW TABLE STATUS语句：

mysql> show create table emp\\g

mysql> show table status like 'emp' \\g

**常用存储引擎**

MyISAM存储引擎

MyISAM存储引擎是MySQL最常用的引擎。

*  它管理的表具有以下特征：
    
     *  使用三个文件表示每个表：
        
         *  格式文件 — 存储表结构的定义（mytable.frm）
         *  数据文件 — 存储表行的内容（mytable.MYD）
         *  索引文件 — 存储表上索引（mytable.MYI）
     *  灵活的AUTO\_INCREMENT字段处理
     *  可被转换为压缩、只读表来节省空间

InnoDB存储引擎

*  InnoDB存储引擎是MySQL的缺省引擎。
 *  它管理的表具有下列主要特征：
    
     *  每个InnoDB表在数据库目录中以.frm格式文件表示
     *  InnoDB表空间tablespace被用于存储表的内容
     *  提供一组用来记录事务性活动的日志文件
     *  用COMMIT(提交)、SAVEPOINT及ROLLBACK(回滚)支持事务处理
     *  提供全ACID兼容
     *  在MySQL服务器崩溃后提供自动恢复
     *  多版本（MVCC）和行级锁定
     *  支持外键及引用的完整性，包括级联删除和更新

MEMORY存储引擎

*  使用MEMORY存储引擎的表，其数据存储在内存中，且行的长度固定，这两个特点使得MEMORY存储引擎非常快。
 *  MEMORY存储引擎管理的表具有下列特征：
    
     *  在数据库目录内，每个表均以.frm格式的文件表示。
     *  表数据及索引被存储在内存中。
     *  表级锁机制。
     *  不能包含TEXT或BLOB字段。
 *  MEMORY存储引擎以前被称为HEAP引擎。

**选择合适的存储引擎**

*  MyISAM表最适合于大量的数据读而少量数据更新的混合操作。MyISAM表的另一种适用情形是使用压缩的只读表。
 *  如果查询中包含较多的数据更新操作，应使用InnoDB。其行级锁机制和多版本的支持为数据读取和更新的混合操作提供了良好的并发机制。
 *  可使用MEMORY存储引擎来存储非永久需要的数据，或者是能够从基于磁盘的表中重新生成的数据。

**事务**

事务可以保证多个操作原子性，要么全成功，要么全失败。对于数据库来说事务保证批量的DML要么全成功，要么全失败。事务具有四个特征ACID。

1、原子性（Atomicity）整个事务中的所有操作，必须作为一个单元全部完成（或全部取消）。

2、一致性（Consistency）在事务开始之前与结束之后，数据库都保持一致状态。

3、隔离性(Isolation) 一个事务不会影响其他事务的运行。

4、持久性(Durability) 在事务完成以后，该事务对数据库所作的更改将持久地保存在数据库之中，并不会被回滚。

事务中存在一些概念：

1.  事务（Transaction）：一批操作（一组DML）
2.  开启事务（Start Transaction）
3.  回滚事务（rollback）
4.  提交事务（commit）
5.  SET AUTOCOMMIT：禁用或启用事务的自动提交模式

当执行DML语句是其实就是开启一个事务。关于事务的回滚需要注意：只能回滚insert、delete和update语句，不能回滚select（回滚select没有任何意义），对于create、drop、alter这些无法回滚。事务只对DML有效果。

注意：rollback，或者commit后事务就结束了。

**事务的提交与回滚**

1、创建表

create table user(
    id int (11) primary key not null auto_increment ,
         username varchar(30),
    password varchar(30)
    )  ENGINE=InnoDB DEFAULT CHARSET=utf8;

![555d956881604f8a9677e03b6764cf52.png][]

2、查询表中数据

select * from user;

![05fa8257fa3c4fbca3f4ce3088ad17e9.png][]

3、开启事务

start transaction;

![d9ca1d5de42e4898a2c7f565d67ebd40.png][]

4、插入数据

insert into user (username,password) values ('zhangsan','123');

![49dd318079d44c34bba049fa5519aabd.png][]

5、查询数据

select * from user;

![17b366a176024ed2ac499278e0c4aa95.png][]

6、修改数据

update user set username='lili' where id = 1;

![92e5960b1acb4378a35d125e1c1152d2.png][]

7、查看数据

select * from user;

![2572b85c053e4314bbf247e38ce135fd.png][]

8、回滚事务

rollback;

![83acd180d16a427f8c8400a5c75c27ea.png][]

9、查看数据

select * from user;

![bf8c3539d9964d1899b1aabbd62bbcdb.png][]

#### 自动提交模式 ####

*  自动提交模式用于决定新事务如何及何时启动。
 *  启用自动提交模式：
    
     *  如果自动提交模式被启用，则单条DML语句将缺省地开始一个新的事务。
     *  如果该语句执行成功，事务将自动提交，并永久地保存该语句的执行结果。
     *  如果语句执行失败，事务将自动回滚，并取消该语句的结果。
     *  在自动提交模式下，仍可使用START TRANSACTION语句来显式地启动事务。这时，一个事务仍可包含多条语句，直到这些语句被统一提交或回滚。
 *  禁用自动提交模式：
    
     *  如果禁用自动提交，事务可以跨越多条语句。
     *  在这种情况下，事务可以用COMMIT和ROLLBACK语句来显式地提交或回滚。
 *  自动提交模式可以通过服务器变量AUTOCOMMIT来控制。
 *  例如：

mysql> SET AUTOCOMMIT = OFF；

mysql> SET AUTOCOMMIT = ON；

或

mysql> SET SESSION AUTOCOMMIT = OFF；

mysql> SET SESSION AUTOCOMMIT = ON；

show variables like '%auto%'; -- 查看变量状态

#### 事务的隔离级别 ####

**隔离级别**

事务的隔离级别决定了事务之间可见的级别。

当多个客户端并发地访问同一个表时，可能出现下面的一致性问题：

*  脏读取（Dirty Read）一个事务开始读取了某行数据，但是另外一个事务已经更新了此数据但没有能够及时提交，这就出现了脏读取。
 *  不可重复读（Non-repeatable Read）在同一个事务中，同一个读操作对同一个数据的前后两次读取产生了不同的结果，这就是不可重复读。
 *  幻像读（Phantom Read）幻像读是指在同一个事务中以前没有的行，由于其他事务的提交而出现的新行。

**四个隔离级别**

*  InnoDB 实现了四个隔离级别，用以控制事务所做的修改，并将修改通告至其它并发的事务：
    
     *  读未提交（READ UMCOMMITTED）允许一个事务可以看到其他事务未提交的修改。
     *  读已提交（READ COMMITTED）允许一个事务只能看到其他事务已经提交的修改，未提交的修改是不可见的。
     *  可重复读（REPEATABLE READ）确保如果在一个事务中执行两次相同的SELECT语句，都能得到相同的结果，不管其他事务是否提交这些修改。 该隔离级别为InnoDB的缺省设置。
     *  串行化（SERIALIZABLE） 【序列化】将一个事务与其他事务完全地隔离。

例:A可以开启事物,B也可以开启事物

A在事物中执行DML语句时,未提交

B不以执行DML,DQL语句

**隔离级别与一致性问题的关系**

![0bc8f559af4d49c1b97e9b504ef2c4b8.png][] **设置服务器缺省隔离级别**

通过修改配置文件设置

*  可以在my.ini文件中使用transaction-isolation选项来设置服务器的缺省事务隔离级别。
 *  该选项值可以是：
    
     *  READ-UNCOMMITTED
     *  READ-COMMITTED
     *  REPEATABLE-READ
     *  SERIALIZABLE
 *  例如：

\[mysqld\]

transaction-isolation = READ-COMMITTED

通过命令动态设置隔离级别

*  隔离级别也可以在运行的服务器中动态设置，应使用SET TRANSACTION ISOLATION LEVEL语句。
 *  其语法模式为：

SET \[GLOBAL | SESSION\] TRANSACTION ISOLATION LEVEL <isolation-level>

其中的<isolation-level>可以是：

*   *  READ UNCOMMITTED
     *  READ COMMITTED
     *  REPEATABLE READ
     *  SERIALIZABLE
 *  例如： SET TRANSACTION ISOLATION LEVEL **REPEATABLE READ**;

**隔离级别的作用范围**

*  事务隔离级别的作用范围分为两种：
    
     *  全局级：对所有的会话有效
     *  会话级：只对当前的会话有效
 *  例如，设置会话级隔离级别为READ COMMITTED ：

mysql> SET TRANSACTION ISOLATION LEVEL READ COMMITTED；

或：

mysql> SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED；

*  设置全局级隔离级别为READ COMMITTED ：

mysql> SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED；

**查看隔离级别**

*  服务器变量tx\_isolation（包括会话级和全局级两个变量）中保存着当前的会话隔离级别。
 *  为了查看当前隔离级别，可访问tx\_isolation变量：
    
     *  查看会话级的当前隔离级别：

mysql> SELECT @@tx\_isolation;

或：

mysql> SELECT @@session.tx\_isolation;

*   *  查看全局级的当前隔离级别：

mysql> SELECT @@global.tx\_isolation;

**并发事务与隔离级别示例**

read uncommitted(未提交读) --脏读(Drity Read)：

<table> 
 <tbody> 
 <tr> 
 <td style="border-color:#000000;vertical-align:top;width:213.05pt;"> 会话一 </td> 
 <td style="border-color:#000000;vertical-align:top;width:213.05pt;"> 会话二 </td> 
 </tr> 
 <tr> 
 <td style="border-color:#000000;vertical-align:top;width:213.05pt;"> mysql&gt; prompt s1&gt; </td> 
 <td style="vertical-align:top;width:213.05pt;"> mysql&gt; use bjpowernode </td> 
 </tr> 
 <tr> 
 <td style="border-color:#000000;vertical-align:top;width:213.05pt;"> s1&gt;use bjpowernode </td> 
 <td style="vertical-align:top;width:213.05pt;"> mysql&gt; prompt s2&gt; </td> 
 </tr> 
 <tr> 
 <td style="border-color:#000000;vertical-align:top;width:213.05pt;"> s1&gt;create table tx ( id int(11), num int (10) ); </td> 
 <td style="vertical-align:top;width:213.05pt;"> </td> 
 </tr> 
 <tr> 
 <td style="border-color:#000000;vertical-align:top;width:213.05pt;"> s1&gt;set global transaction isolation level read uncommitted; </td> 
 <td style="vertical-align:top;width:213.05pt;"> </td> 
 </tr> 
 <tr> 
 <td style="border-color:#000000;vertical-align:top;width:213.05pt;"> s1&gt;start transaction; </td> 
 <td style="vertical-align:top;width:213.05pt;"> </td> 
 </tr> 
 <tr> 
 <td style="border-color:#000000;vertical-align:top;width:213.05pt;"> </td> 
 <td style="vertical-align:top;width:213.05pt;"> s2&gt;start transaction; </td> 
 </tr> 
 <tr> 
 <td style="border-color:#000000;vertical-align:top;width:213.05pt;"> s1&gt;insert into tx values (1,10); </td> 
 <td style="vertical-align:top;width:213.05pt;"> </td> 
 </tr> 
 <tr> 
 <td style="border-color:#000000;vertical-align:top;width:213.05pt;"> </td> 
 <td style="vertical-align:top;width:213.05pt;"> s2&gt;select * from tx; </td> 
 </tr> 
 <tr> 
 <td style="border-color:#000000;vertical-align:top;width:213.05pt;"> s1&gt;rollback; </td> 
 <td style="vertical-align:top;width:213.05pt;"> </td> 
 </tr> 
 <tr> 
 <td style="border-color:#000000;vertical-align:top;width:213.05pt;"> </td> 
 <td style="vertical-align:top;width:213.05pt;"> s2&gt;select * from tx; </td> 
 </tr> 
 </tbody> 
</table>

read committed(已提交读)

<table> 
 <tbody> 
 <tr> 
 <td style="border-color:#000000;vertical-align:top;width:213.05pt;"> 会话一 </td> 
 <td style="border-color:#000000;vertical-align:top;width:213.05pt;"> 会话二 </td> 
 </tr> 
 <tr> 
 <td style="border-color:#000000;vertical-align:top;width:213.05pt;"> s1&gt; set global transaction isolation level read committed; </td> 
 <td style="vertical-align:top;width:213.05pt;"> </td> 
 </tr> 
 <tr> 
 <td style="border-color:#000000;vertical-align:top;width:213.05pt;"> s1&gt;start transaction; </td> 
 <td style="vertical-align:top;width:213.05pt;"> </td> 
 </tr> 
 <tr> 
 <td style="border-color:#000000;vertical-align:top;width:213.05pt;"> </td> 
 <td style="vertical-align:top;width:213.05pt;"> s2&gt;start transaction; </td> 
 </tr> 
 <tr> 
 <td style="border-color:#000000;vertical-align:top;width:213.05pt;"> s1&gt;insert into tx values (1,10); </td> 
 <td style="vertical-align:top;width:213.05pt;"> </td> 
 </tr> 
 <tr> 
 <td style="border-color:#000000;vertical-align:top;width:213.05pt;"> s1&gt;select * from tx; </td> 
 <td style="vertical-align:top;width:213.05pt;"> </td> 
 </tr> 
 <tr> 
 <td style="border-color:#000000;vertical-align:top;width:213.05pt;"> </td> 
 <td style="vertical-align:top;width:213.05pt;"> s2&gt;select * from tx; </td> 
 </tr> 
 <tr> 
 <td style="border-color:#000000;vertical-align:top;width:213.05pt;"> s1&gt;commit; </td> 
 <td style="vertical-align:top;width:213.05pt;"> </td> 
 </tr> 
 <tr> 
 <td style="border-color:#000000;vertical-align:top;width:213.05pt;"> </td> 
 <td style="vertical-align:top;width:213.05pt;"> s2&gt;select * from tx; </td> 
 </tr> 
 </tbody> 
</table>

repeatable read(可重复读)

[a9b8b17b1c0f4cdfaa3095f81f716d6c.png]: https://img-blog.csdnimg.cn/a9b8b17b1c0f4cdfaa3095f81f716d6c.png
[555d956881604f8a9677e03b6764cf52.png]: https://img-blog.csdnimg.cn/555d956881604f8a9677e03b6764cf52.png
[05fa8257fa3c4fbca3f4ce3088ad17e9.png]: https://img-blog.csdnimg.cn/05fa8257fa3c4fbca3f4ce3088ad17e9.png
[d9ca1d5de42e4898a2c7f565d67ebd40.png]: https://img-blog.csdnimg.cn/d9ca1d5de42e4898a2c7f565d67ebd40.png
[49dd318079d44c34bba049fa5519aabd.png]: https://img-blog.csdnimg.cn/49dd318079d44c34bba049fa5519aabd.png
[17b366a176024ed2ac499278e0c4aa95.png]: https://img-blog.csdnimg.cn/17b366a176024ed2ac499278e0c4aa95.png
[92e5960b1acb4378a35d125e1c1152d2.png]: https://img-blog.csdnimg.cn/92e5960b1acb4378a35d125e1c1152d2.png
[2572b85c053e4314bbf247e38ce135fd.png]: https://img-blog.csdnimg.cn/2572b85c053e4314bbf247e38ce135fd.png
[83acd180d16a427f8c8400a5c75c27ea.png]: https://img-blog.csdnimg.cn/83acd180d16a427f8c8400a5c75c27ea.png
[bf8c3539d9964d1899b1aabbd62bbcdb.png]: https://img-blog.csdnimg.cn/bf8c3539d9964d1899b1aabbd62bbcdb.png
[0bc8f559af4d49c1b97e9b504ef2c4b8.png]: https://img-blog.csdnimg.cn/0bc8f559af4d49c1b97e9b504ef2c4b8.png