week09_day02_事务&&索引

程序员文章站 2022-05-09 13:52:43

...

对昨天内容的总结：

总结：
范式：
	键和属性
		a. 超键, 候选键, 主键, 外键
		b. 主属性, 非主属性
	范式：
		分类：1NF, 2NF, 3NF, BCNF, 4NF, 5FN
		1NF：表中的属性不可再分
		2NF：非主属性完全依赖于候选键
		3NF：非主属性直接依赖于候选键
		BCNF：主属性完全并且直接依赖于候选键
	
复杂查询
	连接查询
		a. 交叉连接	cross join
			对两张表做笛卡尔乘积
			
		b. 等值连接
			1) natural join
			2) using
			3) on 

		c. 非等值连接
			
		d. 内连接
			
		e. 外连接
			1) 左外连接 left [outer] join 
			2) 右外连接 right [outer] join 
			3) 全外连接 full [outer] join
			
		f. 自连接
	
	联合查询
		关键字：union, union all
		注意事项：
			a. 结果集的列数要保持一致
			b. 对应的字段，它们的数据类型和含义应该保持一致
			c. union会去重, union all不会去重
		
	子查询
		a. 非关联子查询
		b. 关联子查询
		c. 标量子查询 （查询结果集只有一行一列，可以把结果集看成一个值）
		d. 子查询往往会和一些关键字一起使用：exists, not exists, in, some(any) all
		e. 子查询当作计算字段

·················································································································································································································

构成单一逻辑工作单元的操作集合，我们称为事务 (transaction)。
即使有故障，数据库系统也必须保证事务的正确执行——要么执行整个事务，要么属于该事务的操作一个也不执行。

事务的基本操作：

START TRANSACTION / BEGIN：开启一个事务，标记事务的起点
COMMIT：提交事务，表示事务成功被执行。
ROLL BACK：回滚事务，回滚到初始状态或者回滚点。
SAVEPOINT：回滚点
RELEASE SAVEPOINT：删除回滚点
SET TRANSACTION: 设置隔离级别

注意事项：

START TRANSACTION 标志事务的开始，在 MySQL 中可以用 set autocommit = 0 替代。
结束事务的情况只有两种：
a. COMMIT：表示事务成功被执行，结束事务。
b. 发生故障：结束事务, 不管有没有设置回滚点，都会回到事务开启前的状态。
ROLLBACK：不表示发生故障, 它也是一个数据库操作，属于事务的一部分。表示回滚事务，回滚到事务开启前的状态，或者中间的某个回滚点。要想 rollback 生效，必须要 commit。

事务的性质——ACID

原子性（Atomicity）原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。无论是操作系统崩溃，还是计算机停止运行，这项要求都要成立。
一致性（Consistency）事务作为一个原子性操作，它从一个一致性的数据库状态开始运行，事务结束时，数据库的状态必须再次是一致的。
隔离性（Isolation）尽管多个事务可能并发执行，但系统保证，对于任何一对事务Ti和Tj ,在Ti看来， Tj要么在Ti开始之间已经完成，要么在Ti完成之后才开始执行。因此，每个事务都感觉不到系统中有其他事务在并发地执行。
持久性（Durability）一个事务成功完成后，它对数据库的改变必须是永久的，即使出现系统故障。

ACID 可以说是事务的四大特性，在这四个特性中，原子性是基础，隔离性是手段，一致性是约束条件，而持久性是我们的目的。

并发执行时可能引发的问题

一、脏写
week09_day02_事务&&索引
这个现象，我们称之为脏写。
两个事务并发地对同一项数据进行写操作。

脏写是指当多个事务并发写同一数据时，先执行的事务所写的数据会被后写的数据覆盖。

脏写会导致更新丢失。就好像先提交的事务根本没有执行一样。
给用户的感觉就是T2这个事务根本没有执行一样

二、脏读
week09_day02_事务&&索引
你会发现 T2 计算的结果是 1900，这肯定是不正确的，数据不一致了！

这种现象我们称之为脏读。

如果一个事务A向数据库写数据，但该事务还没提交或终止，另一个事务B就看到了事务A写入数据库的数据，这个现象我们称为脏读。

即读取到了未提交的数据

三、不可重复读
week09_day02_事务&&索引
不可重复读是指：一个事务有对同一个数据项的多次读取，但是在某前后两次读取之间，另一个事务更新该数据项，并且提交了。在后一次读取时，感知到了提交的更新。这个现象我们称为不可重复读。

一个事务至少读两次数据，一个事务写数据。在前后两次读取之间，感知到了另一个事务提交的更新。

可能有同学觉得不可重复读，读取的是已提交的数据，那么就是“正确的”数据，不应该是一个问题。
这时，我们回顾以下隔离性的定义，你会发现写数据的事务对读数据的事务，确实产生了影响！也就是说没有做到完全隔离。

有些情况下，不可重复读确实不是什么大的问题。但是，在有些业务场景下，我们应该避免不可重复读。

四、幻读
week09_day02_事务&&索引
一个事务需要进行前后两次统计，在这两次统计期间，另一个事务插入了新的符合统计条件的记录，并且提交了。导致前后两次统计的数据不一致。这种现象，我们称之为幻读。

分清不可重复读和幻读：
不可重复读是数据项的更新过后读取数据，幻读是数据项的增加过后读取数据
从这个事务的视角来看，平白无故多了几条数据。就像产生了幻觉一样。

前后多次读取，统计的数据不一致。

既然并发执行事务可能会引发这么多问题，那么我们怎么应对呢？
数据库提供了不同的隔离级别来应对不同的问题。

隔离级别

SQL 标准规定了四种隔离级别，分别为

读未提交（read uncommitted）
允许读取未提交的数据。

读已提交（read committed）
只允许读取已提交数据，但不要求可重复读。比如，在事务两次读取一个数据项期
间，另一个事务更新了该数据项并提交。

可重复读（repeatable read）
只允许读取已提交数据，而且在一个事务两次读取一个数据项期间，其他事务不得更新该数据。但该事务不要求与其他事务可串行化。比如，在两次统计查询中，另一个事务可以插入一些记录，当这些记录中有符合查询条件的，那么就会产生幻读。

可串行化（serializable）
看起来事务就好像是串行执行的一样。一个事务结束后，另一个事务才开始执行。

隔离性依次增高。
read uncommitted  read committed  repeatable read  serializable

以上所有隔离级别都不允许脏写(dirty write)，即如果有一个数据项已经被另一个尚未提交或中止的事务写入，则该事务不能对该数据项执行写操作。

week09_day02_事务&&索引

我们可以通过下面命令，查看和设置 MySQL 的隔离级别。

select @@[session|global.]transaction_isolation;
set [session|global] transaction isolation level read uncommitted.

注意：
MySQL 支持4种隔离级别，默认为 RR (repeatable read);
Oracle 只支持 read committed 和 serializable 两种隔离级别，默认为 read committed.

############################### 事务 #######################################
# 事务的基本操作
# 开启事务  start transaction、begin、set autocommit=0
# 提交事务  commit
# 设置回滚点 savepoint
# 删除回滚点 release savepoint
# 回滚点 rollback(回滚到事务最初始的状态)、rollback to sp;(回滚到回滚点sp)
# 设置事务的隔离级别：set transaction


# 查看auto_commit后，发现其默认值为1，也就是说每条sql语句如insert...就是一个事务
# 每次都会默认提交
select @@autocommit;
# 当set autocommit = 0;后，会把set autocommit = 0;到commit之间的sql看成一个事务。
set autocommit = 0;   
use nba;
select * from player;
commit;
# 以下三行又是一个事务
##
##
commit;

# 回滚
set autocommit = 0;
#
# 
savepoint sp1;
#
#
rollback to sp1;
#
#
release savepoint sp1;
commit;

# 举个栗子：
use friend;
show tables;
select * from boys;

# 如果我们在rollback to sp1;这行执行完后，关闭了mysql（演示故障发生的情形），
# 打开mysql查看数据，发现boys表中的数据没变
# 如果我们在commit;这行执行完后，关闭了mysql（演示故障发生的情形），
# 打开mysql查看数据，发现boys表中的数据少了bid=5的记录。
# 同时bid = 3的记录的gid 并没有更新，因为rollback to sp1;
set autocommit=0;
delete from boys where bid=5;
savepoint sp1;
update boys set gid = 30 where bid = 3;
rollback to sp1;
commit;

# 验证ACID中的一致性：即事务中的操作必须满足表的约束条件
select * from boys;
show index from boys;
set autocommit=0;
update boys set gid=3 where bid = 3;
# 事务中的操作必须满足表的约束条件，以下这行sql就无法执行，违反了主键唯一的原则
# 报错：Error Code: 1062. Duplicate entry '1' for key 'PRIMARY'	0.015 sec
# 这一行执行虽然出错了，但不叫发生故障
update boys set bid=1 where bid = 3;
commit;


# 事务的特性 (ACID)
# a.原子性 b.一致性 c.隔离性 d.持久性

# 并发执行事务可能会出现的问题
# a.脏写  b.脏读 c.不可重复读 d.幻读 

# 隔离级别
-- 						脏写	脏读	不可重复读		幻读
-- read uncommitted		×		√			√			√
-- read committed		×		×			√			√
-- repeatable read		×		×			×			√
-- serializable			×		×			×			×

# 查看隔离级别 
#当前会话（会话简单理解就是客户端连接服务器）的隔离级别，也就是这个会话中所有事务的默认隔离级别
select @@transaction_isolation;  
select @@session.transaction_isolation;  #当前会话的隔离级别，和上一句sql一个意思
select @@global.transaction_isolation; # 查看全局的隔离级别, 下一个会话的默认隔离级别。

# 设置隔离级别 
set transaction isolation level read uncommitted; # 设置下一个事务的隔离级别
set session transaction isolation level read uncommitted; # 设置会话的隔离级别 
set global transaction isolation level read uncommitted; # 设置全局的隔离级别 

# read uncommitted
set session transaction isolation level read uncommitted;
select @@transaction_isolation;
# 脏写(×)
set autocommit = 0;
use friend;
select * from boys;
update boys set gid = gid + 1 where bid = 1;
commit;
# 脏读(√)
begin;
select * from boys;
commit;
# 不可重复读(√)
begin;
select * from boys where bid = 1;
select * from boys where bid = 1;
commit;
# 幻读(√)
begin;
select * from boys;
select * from boys;
commit;

# read committed
set session transaction isolation level read committed;
# 脏写(×)
# 脏读(×)
# 不可重复读(√)
begin;
select * from boys;
select * from boys where bid = 1;
select * from boys where bid = 1;
commit;
# 幻读(√)
begin;
select * from boys;
select * from boys;
commit;

# repeatable read
set session transaction isolation level repeatable read;
# 脏写(×)
# 脏读(×)
# 不可重复读(×)
begin;
select * from boys where bid = 1;
select * from boys where bid = 1;
commit;
# 幻读(MySQL RR隔离级别下不会出现幻读, 但是在其它的DBMS中可能出现幻读现象)
begin;
select * from boys;
select * from boys;
commit;

# 注：MySQL的不可重读读隔离级别比较特殊, 可以防止大多数的幻读出现。底层实现：gap lock + nextKey lock

# serializable
# 脏写(×)
# 脏读(×)
# 不可重复读(×)
# 幻读(×)

索引

MySQL 逻辑架构
week09_day02_事务&&索引

大体来说，MySQL可以分为 Server 层和存储引擎层。

Server 层包括连接器、查询缓存、解析器、优化器和执行器等，涵盖了 MySQL 大多数核心服务功能。

存储引擎层负责数据的存储和提取。其架构模式是插件式的，支持 InnoDB、MyISAM、Mermory 等多个存储引擎。

接下来, 我们从一条最简单的 SQL 来看下 MySQL 是如何工作的。
SELECT * FROM t WHERE id = 1;

MySQL 是如何工作的？

连接器
当你在客户端输入 mysql –u $user –p $pwd 连接 mysql 的时候，接待你的就是连接器。连接器的作用就是和客户端建立连接（TCP三次握手）、获取权限（后面的操作都是基于这次获取的权限进行的）、维持和管理连接（如果客户端和服务器端长时间没有进行交互的话，连接就会断开）。

查询缓存
建立连接后，就可以执行select语句了。首先MySQL会去查看查询缓存，看下之前是否已经执行过这条查询语句。如果命中缓存，就直接返回。否则就扔给解析器。

解析器
MySQL需要知道你想做什么，因此我们就来到了解析器。解析器会做词法分析和语法分析。词法分析主要是分析每个词的含义，语法分析会判断你写的 SQL 语句是否满足 SQL 语法。

优化器
经过解析器，MySQL就知道你想做什么了。但是在开始执行之前，还需要经过优化器的处理。优化器会优化你的SQL语句。生成最终的执行方案 (execution plan)。然后进入执行器阶段。

执行器
执行器首先会判断你对这张表有没有相应的权限。如果没有，就报错。如果有，就调用相应的存储引擎接口，执行语句。然后将结果集返回给客户端。

作业：

# 在 student, sc, course, teacher 表中完成下列查询：
use stu_db;
# student: sid, sname, sage, sgender
# teacher: tid, tname
# course: cid, cname, tid
# sc: sid, cid, score
# a. 查询平均成绩大于等于 60 分的同学信息
# 1).查询平均成绩大于等于 60 分的sid
select sid from sc 
group by sid
having avg(score) > 60;
# 2).
select * from student 
where sid in(select sid from sc 
			group by sid
			having avg(score) > 60
);
# 老师写法：
select student.*, avg(score) as avg_score
from student
join sc using(sid)
group by sid having avg_score > 60;
            
# b. 查询有成绩的学生信息
select * from student
join sc using(sid);
# 老师写法：
select *
from student
where sid in (
select distinct sid 
    from sc
    where score is not null
);

# c. 查询选修「张三」老师课的同学信息，课程编号和课程分数
select * from student
where sid in(select sid from student
			join sc using(sid)
			join course using(cid)
			join teacher using(tid)
			where tname<=>'张三'
);
# 老师写法：
select *
from student
join sc using(sid)
where cid in (
select cid
from teacher
join course using(tid)
where tname='张三'
);

# d. 查询没有选修所有课程的学生信息
select * 
from student
where sid in (
select sid
    from sc
    group by sid having count(cid) < (
		select count(cid)
        from course
    )
);

# e. 查询 '01' 课程不及格同学的信息，并按照课程分数降序排列
select * from student 
join sc using(sid)
where cid = '01' and score < 60
group by score desc;

# f. 查询各学生的年龄，按年份来算
select sid, sname, year(sage) from student;

week09_day02_事务&&索引

揭秘SQL Server 2014有哪些新特性(3)-可更新列存储聚集索引

RR与RC隔离级别下索引和锁的测试脚本示例代码

浅析SQL Server中包含事务的存储过程

深入分析MSSQL数据库中事务隔离级别和锁机制

SQLSERVER中忽略索引提示

SQL Server的事务操作隔离模式介绍

JS算法题之查找数字在数组中的索引位置

torrent是什么格式的文件（torrentkitty磁力搜索引擎使用）

Oracle建立二进制文件索引的方法

索引选择性