MySQL - 视图，事务和索引

---

一、视图

1. 问题

对于复杂的查询，往往是有多个数据表进行关联查询而得到，如果数据库因为需求等原因发生了改变，为了保证查询出来的数据与之前相同，则需要在多个地方进行修改，维护起来非常麻烦。

解决办法：定义视图。

2. 视图是什么

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

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

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

3. 定义视图

create view 视图名称 as select 语句;

例如：create view goods_view as (select goods.id as id,goods.name as name,goods_cates.name as type from goods left outer join goods_cates on goods_cates.id=goods.cate_id);

4. 查看视图

查看表会将所有的视图也列出来 show tables;

5. 使用视图

视图的用途就是查询 select * from v_stu_score;

6. 删除视图

drop view 视图名称;

例：drop view v_stu_sco;

7. 视图的作用

• 提高了重用性，就像一个函数

• 对数据库重构（改了字段名），却不影响程序的运行

• 提高了安全性能，可以对不同的用户

• 让数据更加清晰

二、事务

1. 定义

事务广泛的运用于订单系统、银行系统等多种场景。所谓事务，它是一个操作序列，这些操作要么都执行，要么都不执行，它是一个不可分割的工作单位。

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

事务四大特性（简称 ACID）

• 原子性（Atomicity）：一个事务必须被视为一个不可分割的最小工作单元，整个事务中的所有操作要么全部提交成功，要么全部失败回滚，对于一个事务来说，不可能只执行其中的一部分操作，这就是事务的原子性

• 一致性（Consistency）：数据库总是从一个一致性的状态转换到另一个一致性的状态。

• 隔离性（Isolation）→ 针对高并发重要：通常来说，一个事务所做的修改在最终提交以前，对其他事务是不可见的。

• 持久性（Durability）→ 磁盘上真实的发生了变化：一旦事务提交，则其所做的修改会永久保存到数据库。（此时即使系统崩溃，修改的数据也不会丢失。）

可以用 START TRANSACTION 语句开始一个事务，然后要么使用 COMMIT 提交将修改的数据持久保存，要么使用 ROLLBACK 撤销所有的修改。事务 SQL 的样本如下：

• 事务开启：start transaction;

• select balance from checking where customer_id = 10233276;

• update checking set balance = balance - 200.00 where customer_id = 10233276;

• update savings set balance = balance + 200.00 where customer_id = 10233276;

• commit;

2. 事务命令

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

• 查看表的创建语句，可以看到 engine=innodb

-- 选择数据库
use jing_dong;
-- 查看goods表
show create table goods;

• 开启事务，命令如下：begin; 或者 start transaction; 。开启事务后执行修改命令，变更会维护到本地缓存中，而不维护到物理表中。

• 提交事务，命令如下：commit; 。将缓存中的数据变更维护到物理表中。

• 回滚事务，命令如下：rollback; 。放弃缓存中变更的数据。

注意：

• 修改数据的命令会自动的触发事务，包括 insert、update、delete

• 而在 SQL 语句中有手动开启事务的原因是：可以进行多次数据的修改，如果成功一起成功，否则一起会滚到之前的数据

1）回滚

• 连接：终端 1 select * from goods_cates;

• 增加数据：

终端 2：开启事务，插入数据

begin;
insert into goods_cates(name) values('游戏机');

终端 2：查询数据，此时有新增的数据 select * from goods_cates;

• 查询：终端 1 查询数据，发现并没有新增的数据 select * from goods_cates;

• 回滚：终端 2 完成回滚 rollback;

• 查询：终端 1 查询数据，发现没有新增的数据 select * from goods_cates;

2）提交

• 连接：终端 1 查询商品分类信息 select * from goods_cates;

• 增加数据

终端 2 开启事务，插入数据

begin;
insert into goods_cates(name) values('游戏机');

终端 2 查询数据，此时有新增的数据 select * from goods_cates;

• 查询：终端 1 查询数据，发现并没有新增的数据 select * from goods_cates;

• 提交：终端 2 完成提交 commit;

• 查询：终端 1 查询，发现有新增的数据 select * from goods_cates;

3）脏写、脏读、不可重复读和幻读

脏写、脏读、不可重复读和幻读是数据库事务中常见的数据一致性问题，了解它们有助于更好地管理并发事务。

参考文章：【大白话讲解脏写、脏读、不可重复读和幻读】

• 脏写就是：两个事务没提交的状况下，都修改同一条数据，结果一个事务回滚了，把另外一个事务修改的值也撤销了，所谓脏写就是两个事务没提交状态下修改同一个值。

• 脏读就是一个事务修改了一条数据的值，结果还没提交呢，另外一个事务就读到了你修改的值，然后你回滚了，人家事务再次读，就读不到了，即人家事务读到了你修改之后还没提交的值，这就是脏读。

• 不可重复读，针对的是已经提交的事务修改的值，被你事务给读到了，你事务内多次查询，多次读到的是别的已经提交的事务修改过的值，这就导致不可重复读。

• 幻读就是：你一个事务用一样的 SQL 多次查询，结果每次查询都会发现查到一些之前没看到过的数据。注意，幻读特指的是你查询到了之前查询没看到过的数据。

	脏写	脏读	不可重复读	幻读
read uncommitted	×	√	√	√
read committed	×	×	√	√
repeatable read	×	×	×	√
serializable	×	×	×	×

三、索引

1. 定义

当数据库中数据量很大时，查找数据会变得很慢。优化方案：索引 → 就是数据结构（有序），B+ 树索引，哈希索引。

2. 索引是什么

索引是一种特殊的文件（InnoDB 数据表上的索引是表空间的一个组成部分)，它们包含着对数据表里所有记录的引用指针。更通俗的说，数据库索引好比是一本书前面的目录，能加快数据库的查询速度。

3. 索引目的

索引的目的在于提高查询效率，可以类比字典，如果要查 “mysql” 这个单词，我们肯定需要定位到 m 字母，然后从下往下找到 y 字母，再找到剩下的 sql 。如果没有索引，那么你可能需要把所有单词看一遍才能找到你想要的。

4. 索引原理

除了词典，生活中随处可见索引的例子，如火车站的车次表、图书的目录等。它们的原理都是一样的，通过不断的缩小想要获得数据的范围来筛选出最终想要的结果，同时把随机的事件变成顺序的事件，也就是我们总是通过同一种查找方式来锁定数据。

数据库也是一样，但显然要复杂许多，因为不仅面临着等值查询，还有范围查询（>、<、between、in）、模糊查询（like）、并集查询（or）等等。

• 红黑树：不同节点的存在磁盘的不同位置，访问 7 个节点就需要 2 次磁盘 I/O

• 哈希索引：因为需要连续的磁盘空间来存哈希表，找到连续的较大磁盘空间的难度较大（磁盘碎片严重）

• B+ 索引

5. 索引的使用

• 查看索引：show index from 表名;

• 创建索引：

  • 如果指定字段是字符串，需要指定长度，建议长度与定义字段时的长度一致
  • 字段类型如果不是字符串，可以不填写长度部分

create index 索引名称 on 表名(字段名称(长度))
create unique index 索引名称 on 表名(字段名称(长度))

• 删除索引： drop index 索引名称 on 表名;

注意：

• 当一列是没有区分度，不适合建索引；

• 主键索引不仅仅是 B+ 树索引，还是聚集索引（索引值和行数据存在一起，行数据直接就在叶子结点上）；

• 一个表中只能有一个聚集索引（主键默认就是聚集索引），其他索引都是非聚集索引。

6. 注意

• 要注意的是，建立太多的索引将会影响更新和插入的速度，因为它需要同样更新每个索引文件。对于一个经常需要更新和插入的表格，就没有必要为一个很少使用的 where 字句单独建立索引了，对于比较小的表，排序的开销不会很大，也没有必要建立另外的索引。

• 建立索引会占用磁盘空间。

• 索引提高了查询效率，修改效率，删除效率；降低了一点新增效率。