事务处理&表引擎&索引

一、事务处理

事务是由一些SQL语句组成的可执行单元，提交事务后，事务中的语句如果有一条执行失败，所有的语句全都回滚。

（一）事物的特性（ACID）

1.原子性：事务中的语句有一条执行失败，所有的语句全都回滚。

2.一致性：当事务执行后，数据库应该从一个一致性状态进入到另一个一致性状态。

3.隔离性：事务和事务之间是隔离的

4.持久性：事务一旦提交，就更新数据库了，无法进行回滚。

（二）使用SQL语句实现事务处理

MySQL数据库中，事务是自动提交的

-- 设置事务自动提交为手动提交
set autocommit = 0;
-- 开启一个新的事务
start transaction;
-- 设置保存点，状态点
savepoint a1;
insert into student(sno,sname)
values(25,'tom3');
savepoint a2;
insert into class(id,cname)
values(8,'一年五班');
savepoint a3;
-- 回滚 到事务的最初状态
-- rollback ;
-- 回滚 到事务的a2状态
rollback to a2;
-- 提交事务
commit;

（三）事务隔离级别

对于同时运行的多个事务, 当这些事务访问数据库中相同的数据时, 如果没有采取必要的隔离机制, 就会导致各种并发问题:

•脏读: 对于两个事务 T1, T2, T1 读取了已经被 T2 更新但还没有被提交的字段. 之后, 若 T2 回滚, T1读取的内容就是 临时且无效的.

•不可重复读: 对于两个事务 T1, T2, T1 读取了一个字段, 然后 T2 更新了该字段. 之后, T1再次读取同一个字段, 值就 不同了.

•幻读: 对于两个事务 T1, T2, T1 从一个表中读取了一个字段, 然后 T2 在该表中插入了一些新的行. 之后, 如果 T1 再 次读取同一个表, 就会多出几行.

 Mysql 支持 4 中事务隔离级别. Mysql 默认的事务隔离级别为: REPEATABLE READ

select @@tx_isolation : 查询当前连接的隔离级别

set session transaction isolation level read committed : 设置当前连接的隔离级别

set global transaction isolation level read committed : 设置全局的隔离级别

二、MySQL数据库的表引擎

MyISAM:读取数据时效率高，不支持事务处理

Innodb:支持事务处理

区别：

1.InnoDB：支持事务操作，MyISAM不支持事务操作

2.InnoDB支持外键操作，MyISAM不支持外键

3.InnoDB不保存表的总行数，在执行select count(0) from table 需要进行全文检索计算总行数，MyISAM会将表的总行数存储到一个变量中，再执行count(0)直接获得变量的值，速度快；但是如果是带where条件的count(0),如果没有索引MyISAM还是要做全文检索。

4. InnoDB在MySQL5.7以后才支持全文索引，MyISAM也支持全文索引

5. InnoDB支持表、行(默认)级锁，而MyISAM支持表级锁

6. InnoDB是聚集索引，使用B+Tree作为索引结构，数据文件是和（主键）索引绑在一起的（表数据文件 本身就 是按B+Tree组织的一个索引结构），必须要有唯一约束，通过唯一索引效率很高。但是辅助索引 需要两次查 询，先查询到主键，然后再通过主键查询到数据。因此，主键不应该过大，因为主键太大， 其他索引也都会 很大。 MyISAM是非聚集索引，也是使用B+Tree作为索引结构，索引和数据文件是分离的，索引保存的是数据文件 的指针。主键索引和辅助索引是独立的。

temp（MEMORY）表引擎：存储的数据是在内存中存储的，不具备持久化的特性。通常一些临时表的表引擎 使用temp

表引擎的操作：

查询mysql数据库中的所有表引擎：

show engines;

 在创建表时，指定表引擎

create table 表名（

        .......

）engine MyISAM

三、全文索引

全文索引用于匹配查询，检索效率高于like语句

创建全文索引

create fulltext 索引名 on 表名（列名） 

create fulltext index index_student3 on student(address)

使用全文索引 检索字符串长度大于等于4的内容。

在MySQL 5.7支持ngram解析器，检索中文

create fulltext index index_student3 on student(address) with parser ngram

词和词之间使用数字和符号或空格分隔的

select address from student where match(address) AGAINST('黑龙江')

全文检索有两种模式分别是：自然检索模式和boolean检索模式，默认是自然检索模式，boolean模式可以配合一 些符号进行检索 ，例如要检索的内容 描述 包含中国但不包含北京

使用boolean模式 检查包含中国但不包含内径的address

select address from student where match(address) AGAINST('+中国 -北京' in boolean MODE)