04-事务控制(TCL)

事务

要么都成功，要么都失败

-- A转账B
-- B到账A
-- 只有A转账成功且B到账成功，该事件才算结束
-- 若一方不成功，则该事务不成功

事务特性(ACID)

名称	描述
原子性（Atomicity）	事务是不可分割的，事务的操作要么都发生，要么都不发生
一致性（Consistency）	事务前后数据的完整性必须保持一致
隔离性（Isolation）	多个用户并发访问数据库，为每个用户开启的事务，不能被其他事务操作数据干扰，多个并发事务之间要相互隔离。
持久性（Durability）	事务一旦被提交则不可逆，被持久化到数据库，接下来即使数据库发生故障也不应该对其有任何影响

事务并发导致的问题

名称	描述
脏读	一个事务读取另一个事务未提交的数据
不可重复读	一个事务读取表的某行数据，多次读取结果不同
虚读(幻读)	一个事务内读取到别的事务插入的数据，导致前后读取内容不一致

隔离级别

事务隔离级别	脏读	不可重复读	幻读
读未提交	是	是	是
读已提交	否	是	是
可重复读	否	否	是
串行化	否	否	否

读未提交

一个事务读取到其他事务未提交的数据

查询不加锁，一致性最差。

问题：脏读、不可重复读、幻读

读已提交

一个事务只能读取到其他事务已经提交的数据

大多数流行数据库的默认事务隔离级别

问题：不可重复读、幻读

可重复读

事务执行过程可读取到其他事务已提交的新插入的数据，但不能读取其他事务对数据的修改，就是多次读取同一记录的结果相同

MySQL默认的隔离级别

问题：幻读

串行化

事务只能一个接着一个地执行,并在执行过程中完全看不到其他事务对数据的更新

缺点：并发能力差，最严格的事务隔离

完全符合ACID原则，但对性能影响较大

执行事务过程

1. 关闭自动提交

2. 如果正常执行完毕提交事务

3. 如果有异常则回滚事务

自动提交

-- 关闭
SET autocommit=0;
SET autocommit=FALSE;
-- 开启
SET autocommit=1;
SET autocommit=TRUE;

事务开启

-- 标记事务的开始，从这个后的sql都在同一个事务内
START TRANSACTION

其他操作

SAVEPOINT 保存点名; -- 设置事务保存点
ROLLBACK TO SAVEPOINT 保存点名; -- 回滚到保存点
RELEASE SAVEPOINT 保存点名; -- 撤销保存点

索引

1. 提高查询速度

2. 确保数据唯一性

3. 加速表和表之间的连接 , 实现表与表之间的参照完整性

4. 分组和排序进行数据检索 , 可减少分组和排序时间

5. 全文检索字段进行搜索优化

原则

• 索引不是越多越好，小数据量的表不需要加索引

• 不要对经常变动的数据增加索引

• 索引一般加在经常要查询的列上

分类

主键索引

PRIMARY KEY

唯一的标识，主键不可重复，只有一个列作为主键

• 最常见的索引类型，不允许为空值

• 确保数据记录的唯一性

• 确定特定数据记录在数据库中的位置

普通索引

KEY / INDEX

默认的，快速定位特定数据

• index 和 key 关键字都可以设置常规索引

• 加在查询找条件的字段

• 不宜添加太多常规索引,影响数据增删改

唯一索引

UNIQUE KEY

与普通索引类似，不同是：索引列值必须唯一，允许空值

与主键索引区别：主键索引只有一个、唯一索引可有多个

全文索引

FULLText

快速定位特定数据

• 特定的数据库引擎下才有：MyISAM

• 只用于CHAR , VARCHAR , TEXT数据列类型

• 适合大型数据集

创建

-- 直接创建
CREATE INDEX 索引名 ON 表名(字段名);
-- 修改表结构创建
ALTER TABLE 表名 ADD INDEX 索引名(字段名);

删除

-- 方法一
DROP INDEX 索引名 ON 表名;
-- 方法二
ALTER TABLE 表名 DROP INDEX 索引名；

查询

-- 方法一
show indexes from 表名;
-- 方法二
show keys from 表名;

失效

• 查询的数据量大于总量的1/3

• 对条件作运算

• 对条件作数据类型转换

• 模糊查询