视图事务索引

视图索引与事务

本次授课主要讲解如下内容：

索引（了解）

视图（了解）

事务（重点，必须掌握）

第1回 索引

索引是存放在模式（schema） 中的一个数据库对象，索引的作用就是提高对表的检索查询速度，

​ 字典（按笔画，按偏旁。。。。 检索，依据，特征的）

索引是通过快速访问的方法来进行快速定位数据，从而减少了对磁盘的读写操作。

​ （如何能够快速的定位数据呢？）

索引是数据库的一个对象，它不能独立存在，必须对某个表对象进行依赖。（寄生虫）

​

创建索引方式：

　　自动：当表上定义主键约束、唯一、外键约束时，该表会被系统自动添加上索引，也就是说主键默认就是索引的效果。

　　手动：手动在相关表或列上增加索引，提高查询速度。

删除索引方式：

　　自动：当表对象被删除时，该表上的索引自动被删除

　　手动：手动删除指定表对象的相关列上的索引

索引类似于书籍的目录，可以快速定位到相关的数据，一个表可以有多个索引。

​ 全文索引，可以用来匹配数据，类似于正则了。

​ 数据库也可以使用正则（压力就很大）。

//创建索引 需要指明是哪个表上的索引，因为它依赖于具体的表

// index_product_name

create index idx_temp_name on temp(name);

​ 联合索引。

//删除索引 指明表理由同上。

drop index idx_temp_name on temp;

索引用于快速找出在某个列中有一特定值的行。不使用索引，MySQL必须从第1条记录开始然后读完整个表直到找出相关的行，还需要考虑每次读入数据页的IO开销。而如果采取索引，则可以根据索引指向的页以及记录在页中的位置，迅速地读取目标页进而获取目标记录。

索引虽好，不要多搞。

几个原则：

1 尽量唯一（定位来讲更加快，假如这个一列的值可以唯一的话）

2 老是要排序或者联合的字段或者查询条件 where condition sno = order by

3 不要搞太多（只是辅助手段，不要建上瘾）

4 如果你老不用就不要建了，浪汇表情。

深入来讲，索引策略，真实业务，任何涉及脱离业务没有任何意义。

一切脱离业务的设计，都是耍流氓。

-- 公司有DBA，一般就它会设计好的，如果是你设计，设计完了可以咨询一下。

-- 索引虽然对查询的效率提升比较大，但是如果数据也频繁更新，那么索引本身也有物理介质的存在

​ 这个索引自身也要维护更新，（反而不见得好了）。

第2回 视图

view1

抽象

物理层

逻辑层

视图层

概念：

依赖一个基表

通俗的讲，视图就是一条SELECT语句执行后返回的结果集。所以我们在创建视图的时候，主要的工作就落在创建这条SQL查询语句上。

视图是一个虚表，并不是具体的具有物理介质的表。

特点：

视图是对若干张基本表的引用，一张虚表，查询语句执行的结果，不存储具体的数据（基本表数据发生了改变，视图也会跟着改变）；

反过来如果对视图进行插入操作会影响基表吗？有没有什么限制呢？

Can not insert into join view 'shop.v_hus_wife' without fields list

连接查询搞不得。

多重视图。

作用：

方便操作，特别是查询操作，减少复杂的SQL语句，增强可读性；

更加安全，数据库授权命令不能限定到特定行和特定列，但是通过合理创建视图，可以把权限限定到行列级别；

老板想看xx 员工想看yy

场景：

权限控制的时候，不希望用户访问表中某些含敏感信息的列，比如 工资....

关键信息来源于多个复杂关联表，可以创建视图提取我们需要的信息，简化操作；

select

from t1 , t2 ,t3

left join

right on

where ....

v_ss.*

v_ss

否则每次都要这么写，嗯 。。。。 有点痛苦

下面我们使用经典的用户 、 课程 以及一张中间表作为说明：

需求：

​ 查询小王同志上的所有的课程相关的信息

​ 我们的sql怎么写呢？

​ SELECT

​ uc.id AS id ,

​ u.name AS username,

​ c.name AS coursename

​ FROM

​ USER u

​ LEFT JOIN user_course uc ON (u.id=uc.userid)

​ LEFT JOIN course c ON (uc.courseid=c.id)

​ WHERE

​ u.name = '小王';

t tb_

如果使用我们的视图怎么搞呢？

CREATE VIEW v_user_course AS (

​ SELECT

​ uc.id AS id ,

​ u.name AS username,

​ c.name AS coursename

​ FROM

​ USER u

​ LEFT JOIN user_course uc ON (u.id=uc.userid)

​ LEFT JOIN course c ON (uc.courseid=c.id)

);

然后下次我们查小王，小张都很轻松了。

select * from v_user_course where username = '小王'

select * from v_user_course where username = '小张'

需要注意的是不能再这种联合多张基表的视图上进行增删改。

实际上视图最大的作用就是用来查的，不要搞事。

第3回 事务

在 MySQL 中只有使用了 Innodb （引擎）数据库引擎的数据库或表才支持事务。

3.1 什么是事务

事务原名Transaction，是一个最小的不可再分割的工作单元。

通常一个事务是描述一个阶段性的完整业务。典型的就是银行的转账业务。这个业务内的操作必须同生共死。

涉及具体数据的增删改的自然就DML语言，所以只有DML语句也有事务的概念。

事务处理可以用来维护数据库的完整性，保证成批的 SQL 语句要么全部执行，要么全部不执行。

事务用来管理 insert,update,delete 语句

3.2 事务四特性

一般来说，事务是必须满足4个条件（ACID）： Atomicity（原子性，不可分割）、Consistency（一致性、稳定性）、Isolation（隔离性：隔离级别）、Durability（可靠性、持久性）

1、 事务的原子性Atomicity：一组事务，要么成功；要么撤回。

为什么叫原子性，质子 分子 夸克 弦理论 string。

事务概念很早提出来了，当年认识到的还只有原子。

2、一致性 (Consistency)
事务执行的结果必须是使数据库从一个一致性状态变到另一个一致性状态。因此当数据库只包含成功事务提交的结果时，就说数据库处于一致性状态。如果数据库系统 运行中发生故障，有些事务尚未完成就被迫中断，这些未完成事务对数据库所做的修改有一部分已写入物理数据库，这时数据库就处于一种不正确的状态，或者说是 不一致的状态。 数据库的恢复，容灾，专门数据库的维护人员。

3、隔离性(Isolation)
一个事务的执行不能其它事务干扰。即一个事务内部的操作及使用的数据对其它并发事务是隔离的，并发执行的各个事务之间不能互相干扰。

​ 隔离级别：

​ 读未提交 read uncommited

​ 读已提交 read commited

​ 可重复读 repeatble read

​ 串行化 serializable

​ 级别从上往下隔离级别越高。

4、持续性 (Durability)
也称永久性，指一个事务一旦提交，它对数据库中的数据的改变就应该是永久性的。接下来的其它操作或故障不应该对其执行结果有任何影响。

3.2.1 隔离性重点讲解

read uncommited

事务A和事务B，事务A未提交的数据，事务B可以读取到。

这些数据可以称之为脏数据，这种读也叫作脏读。 dirty read.最低级别的隔离，这种级别一般是理论存在，数据库默认的隔离级别一般都高于它。

read commited（最多Oracle默认隔离级别）

​ 事务A和事务B，事务A提交的数据，事务B才能读取到。明显它的隔离级别高于read uncommited

​ 这样事务B读取的数据就是正确的数据，可以避免脏数据。

​ 比较安全，但是也会导致“不可重复读取”，在同一个事务当中读取的数据不一致。

repeatble read（MYSQL默认隔离级别）

​ 事务A事务B，可以避免脏数据，可以重复读，事务A提交之后的数据，事务B读取不到，事务B是可重复读取的数据。这种隔离级别高于read commited，对方提交的数据读取不到，它可以避免“不可重复读”，有什么用呢？比如银行总账统计需求。它存在幻读问题。

serializble

​ 最高隔离级别，事务A事务B，事务A在操作数据库中的数据的时候，事务B只能等待A。

可以完成可重复读，也可以避免幻读，使用很少，因为就好比堵车，相当于一秒刀，压根不考虑并发。

。

3.3 处理方式

MYSQL 事务处理主要有两种方法：

默认开启了事务自动提交。

在外面书写DML的时候，我们的数据假如没有提交事务的话，那么它的数据只是写在内存当中，并没有持久化，我们操作只有提交的时候才会统一写入数据库具体的物理介质当中。

1、用 BEGIN, ROLLBACK, COMMIT来实现

BEGIN 开始一个事务

ROLLBACK 事务回滚

COMMIT 事务确认

2、改变 MySQL 的自动提交模式:

1

start transaction;

2

SET AUTOCOMMIT=OFF 禁止自动提交

SET AUTOCOMMIT=ON 开启自动提交

SET AUTOCOMMIT=0 禁止自动提交

SET AUTOCOMMIT=1 开启自动提交

该修改只对当前会话有效。

注意sqlyog当中事务自动提交了，这里的事务验证我们需要回到我们的cmd界面来完成。

3.4 转账案例

start transaction;

update account set money = money – 50 where name =’san’;

update aacount set money = money + 50 where name = ‘si’;

commit;

jdbc的就会，如果遇到异常我们就回滚。