MySQL高级——视图&事务(第五讲)

最新推荐文章于 2024-09-15 20:24:46 发布

屿你有关

最新推荐文章于 2024-09-15 20:24:46 发布

阅读量80

点赞数

分类专栏： Python数据库优化文章标签： mysql

本文链接：https://blog.csdn.net/weixin_51158247/article/details/113850638

版权

Python数据库优化专栏收录该内容

11 篇文章 0 订阅

订阅专栏

在这里插入图片描述

一.视图

视图介绍

视图(View)是一种虚拟存在的表，对于使用视图的用户来说基本上是透明的。视图并不在数据库中实际存在，行和列数据来自定义视图的查询中使用的表，并且是在使用视图时动态生成的。
就像小学时表演，从每个班级凑出几个人来组成一个新的班级。表演结束之后，回到自己原本的班级。这个新班级并不存在于这个学校中，只是一个给别人看的虚拟班级。

定义视图

create view 视图名称 as select 语句

create view v_ar as select * from areas;

查看视图

查看表的时候会把视图表也列出来

show tables;

使用视图

select * from v_ar;

删除视图

drop view v_ar;

视图案例

select provinces.id,provinces.`province`,cities.`city` from provinces inner join cities on provinces.`provinceid` = cities.provinceid having provinces.`province` = '黑龙江省';

SELECT * FROM areas AS p INNER JOIN areas AS c ON p.`id`=c.`pid` 
HAVING p.`name`='哈尔滨';

视图的作用

• 简单：提高了重用性，就像一个函数。
• 安全：提高了安全性能，可以针对不同的用户，设定不同的视图。
• 数据独立：一旦视图的结构确定了，可以屏蔽表结构变化对用户的影响，源表增加列对视图没有影响；源表修改列名，则可以通过修改视图来解决，不会造成对访问者的影响

视图的修改

有下列内容之一，视图不能做修改

select子句中包含distinct
select字句中包含组函数
select语句中包含group by子句
selecy语句红包含order by子句
where子句中包含相关子查询
from字句中包含多个表
如果视图中有计算列，则不能更新
如果基表中有某个具有非空约束的列未出现在视图定义中，则不能做insert操作。

二.事务

为什么要有事务

    为什么要有事务？
    事务广泛的运用于订单系统、银行系统等多种场景
    例如：
    你扫付款码给老板100元，那么将会发生这几件事：

检查你的钱包有>100的钱
从你的钱包里扣除100
老板的钱包加100

    正常流程走下来，你的钱包扣掉100，老板的钱包加100。
    那如果系统故障了呢?你的钱包扣了100,老板也没有收到100。
    以上的案例中，隐藏着一个前提条件：A扣钱和B加钱，要么同时成功，要么同时失败。事务的需求就在于此

事务的介绍

所谓事务,它是一个操作序列，这些操作要么都执行，要么都不执行，它是一个不可分割的工作单位

例如，银行转帐工作：从一个帐号扣款并使另一个帐号增款，这两个操作要么都执行，要么都不执行。所以，应该把他们看成一个事务。事务是数据库维护数据一致性的单位，在每个事务结束时，都能保持数据一致性

假如这个银行有两张表，一张是支付表，一张是储蓄表。现在要从支付表转200到储蓄表里，要经过这些事情：

检查账户余额>=200
支付表中减去200
储蓄表中加200

上述三个步骤的操作必须打包在一个事务中，任何一个步骤失败，则必须回滚所有的步骤。

事务四大特性(简称ACID)

原子性(Atomicity)
一致性(Consistency)
隔离性(Isolation)
持久性(Durability)

原子性（atomicity）
一个事务必须被视为一个不可分割的最小工作单元，整个事务中的所有操作要么全部提交成功，要么全部失败回滚，对于一个事务来说，不可能只执行其中的一部分操作，这就是事务的原子性
一致性（consistency）
数据库总是从一个一致性的状态转换到另一个一致性的状态。（在前面的例子中，一致性确保了，即使在执行第三、四条语句之间时系统崩溃，支票账户中也不会损失200美元，因为事务最终没有提交，所以事务中所做的修改也不会保存到数据库中。）
隔离性（isolation）
通常来说，一个事务所做的修改在最终提交以前，对其他事务是不可见的。（在前面的例子中，当执行完第三条语句、第四条语句还未开始时，此时有另外的一个账户汇总程序开始运行，则其看到支票帐户的余额并没有被减去200美元。）
持久性（durability）
一旦事务提交，则其所做的修改会永久保存到数据库。（此时即使系统崩溃，修改的数据也不会丢失。）

事务命令

注意：表的引擎类型必须是innodb类型才可以使用事务，这是mysql表的默认引擎

开启事务

开启事务后执行修改命令，变更会维护到本地缓存中，而不维护到物理表中

begin;
或者
start transaction;

提交事务

• 将缓存中的数据变更维护到物理表中

commit;

回滚事务

• 放弃缓存中变更的数据

rollback;

保存点

如果你开启了一个事务，并且已经敲了很多语句，忽然发现上一条语句有点问题，你只好使用ROLLBACK语句来让数据库状态恢复到事务执行之前的样子，然后一切从头再来，总有一种一夜回到解放前的感觉。所以设计数据库的大叔们提出了一个保存点（英文：savepoint）的概念，就是在事务对应的数据库语句中打几个点，我们在调用ROLLBACK语句时可以指定会滚到哪个点，而不是回到最初的原点。

SAVEPOINT 保存点名称;

当我们想回滚到某个保存点时，可以使用下边这个语句（下边语句中的单词WORK和SAVEPOINT是可有可无的）：

ROLLBACK [WORK] TO [SAVEPOINT] 保存点名称;

不过如果ROLLBACK语句后边不跟随保存点名称的话，会直接回滚到事务执行之前的状态。
如果我们想删除某个保存点，可以使用这个语句：

RELEASE SAVEPOINT 保存点名称;

注意

修改数据的命令会自动的触发事务，包括insert、update、delete
而在SQL语句中有手动开启事务的原因是：可以进行多次数据的修改，如果成功一起成功，否则一起会滚到之前的数据

三.事务隔离级别

介绍

隔离级别（ISOLATION LEVEL） 隔离性其实比想象要复杂。在SQL中定义了四种隔离的级别，每一种隔离级别都规定了一个事务中的修改，哪些是在事务内和事务间是可见的，哪些是不可见的。较低级别的隔离通常来说能承受更高的并发，系统的开销也会更小。
在SQL中定义了四种隔离级别，每一种在我们的事务中，有四个隔离级别，分别是：

READ UNCOMMITTED（未提交读）
READ COMMITTED（读已提交）
REPEATABLE READ（可重复读）
SERIALIZABLE（可串行化）

查看当前事物级别

SELECT @@tx_isolation;

设置mysql的隔离级别

set session transaction isolation level 设置事务隔离级别

READ UNCOMMITTED（未提交读）

在READ UNCOMMITTED级别，事务的修改，即使没有提交，对别的事物也是可见的。其他事务可以读取到这个事务未提交的数据，这就是脏读（Dirty Read）。这个级别是最低的，所以也会导致很多的问题，但他的性能却是最优的，不过缺乏了其他更高的级别的很多好处，所以这种级别很少在实际中应用。

SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

READ UNCOMMITTED实践开启两个MySQL SESSION，并将MySQL的默认隔离级别设置为READ UNCOMMITTED

SELECT @@tx_isolation;
SET SESSTION TRANSATION ISOLATION LEVEL READ UNCOMMITTED
START TRANSATION;
UPDATE zhifu SET MONEY=900 WHERE ID=1;

SELECT @@tx_isolation;
SET SESSTION TRANSATION ISOLATION LEVEL READ UNCOMMITTED
START TRANSATION;
SELECT * FROM ZHIFU;

这时候我们开启了两个事务，但是会发现第二个事务查询的时候可以查询到第一个事务做出的修改。此时两个事务都是未提交状态，这就是脏读。

READ COMMITTED(读已提交)

大多数数据库系统默认的隔离级别都是READ COMMITTED，但是MySQL不是。“读已提交”简单定义：一个事务只能看到已经提交了的事务修改结果。也就是说一个事务再提交之前，对别的事务都是不可读的，因此在同一个事物中，两次查询的结果可能不一样。所以这种级别有时候也叫做不可重复读(NONREPEATABLE READ)

READ COMMITTED(读已提交)实践

session_1	session_2
开启事务	开启事务
查询表结果
	修改表数据
查询表结果
	提交表数据
查询表结果

REPEATABLE READ(可重复读)

这个隔离级别是MySQL系统默认的隔离级别，该级别解决了脏读的问题，它可以保证在同一个事物中，多次查询相同的语句结果是一致的。但是该级别会产生幻行(Phantom Row)的问题。MySQL的InnoDB引擎通过多并发控制（MVCC，Multiversion Concurrency Controller）解决了幻读的问题。

REPEATABLE READ(可重复读)实践

session_1	session_2
开启事务	开启事务
查询表结果
	修改表数据
查询表结果
	提交表数据
查询表结果

幻行：例如在事务二中插入并提交一条id为2的记录，但是在事务一中是看不到这个id为2的记录的，此时再插入一条id为2的记录的时候会报错。

SERIALIZABLE（可串行化）

这是最高的事务隔离级别，它会强制事务串行，避免前面说的幻读问题。简单来说，它会在读取的每一行数据上都加锁，所以导致大量的锁等待还有超时的问题，所以实际很少会用到这个隔离级别，只有在非常需要保证数据的一次性并且可以接受没有并发的情况下，才会考虑使用这个隔离级别。
SERIALIZABLE(可串行化)实践