MySQL基础(三)

事务

事务是一个完整的业务逻辑单元，不可再分

如银行转账，从A账户向B账户转账1000，需要执行两条update语句

update t_act set balance  = balance - 1000 where actno = 'act-001';
update t_act set balance  = balance + 1000 where actno = 'act-002';

以上两条DML语句必须同时成功，或者同时失败，不允许出现一条成功，一条失败，要想保证同时成功或者失败，不允许出现一条成功，一条失败

⚠️  和事务相关的语句只有DML语句：insert、delete、update

事务的存在是为了保证数据的完整性和安全性

⚠️  假设所有的业务都能使用一条DML语句搞定，就不需要事务机制了，但是实际情况是一个事务通常由多条DML语句共同联合完成 

 

事务流程：事务开始 -> 执行记录到历史操作中 ->提交事务commit -> 同步到硬盘上

                                                                           ->回滚事务rollback ->清除历史操作

 

事务的特性ACID

• 原子性：事务是最小的工作单元
• 一致性：事务必须保证多条DML语句同时成功或者同时失败
• 隔离性：事务A与事务B之间具有隔离
• 持久性：持久性说的是最终数据必须持久化到硬盘文件中，事务才算真的结束

事务之间的隔离性

第一级别：读未提交(read uncommitted) 读的时候对方事务还没来得及提交，我们当前事务读到对方还未提交的事务，数据还在缓存中，不稳定，现象称为脏读

第二级别：读已提交(read committed) 对方事务提交之后的数据我方可以读取到，解决了脏读的问题，新问题是不可重复读，

第三级别：可重复读(repeatable read) 解决了不可重复读的问题，但是存在问题：幻读《高性能MySQL》：

所谓幻读，指的是当某个事务在读取某个范围内的记录时，另外一个事务又在该范围内插入了新的记录，当之前的事务再次读取该范围的记录时，会产生幻行。InnoDB存储引擎通过多版本并发控制（MVCC）解决了幻读的问题

第四级别：序列化读/串行话读(Serializable)：解决了所有问题，但是效率低

为什么要解决幻读？ 因为要保证高并发数据库系统中，事务和事务的隔离性以及数据的一致性

幻读和不可重复读的区别：幻读侧重于结果集的范围发生了变化(多了几行或者少了几行)而不可重复读侧重于读到的值发生了变化

在MySQL InnoDB 中，默认为 Repeatable 级别，InnoDB 中使用 next-key locking + mvcc多版本并发控制 (当前读和快照读)的策略来避免幻读现象的产生，所以在实例中，我们发现常规的幻读在RR中不会再出现了，要模拟幻读，可以将数据库隔离级别设置为可重复读（生产环境中默认的隔离级别），然后事务A进行select操作，这个时候事务B进行update或者insert操作并且提交了，而事务A在拿到数据查看完之后提交，提交之后再查看发现莫名其妙的和之前自己查的不一样，而且多出了一两条数据。这才是生产环境中遇到的幻读

快照读：当执行select操作是Innodb默认会执行快照读，会记录下这次select后的结果，之后select 的时候就会返回这次快照的数据，即使其他事务提交了不会影响当前select的数据，这就实现了可重复读了

当前读：对于会对数据修改的操作(update、insert、delete)都是采用当前读的模式

InnoDB有三种行锁的算法：Record Lock记录锁(单行记录上锁)和Gap Lock间隙锁(锁定一个范围，但不包括记录本身，目的，是为了防止同一事务的两次当前读，出现幻读的情况)，next-key lock就是记录锁+间隙锁

锁机制隔离级别的实现： RU：事务读取的时候不加锁 RC：事务读取的时候加行级共享锁，读到加锁，读完释放(不是事务结束释放)

RR：事务读取时加行级共享锁，事务结束后释放 SE：事务读取时加表级共享锁，直到事务结束，才会释放

有关mvcc和MySQL锁相关的内容后面会陆续补上

参考(第一个链接的脏读例子很不错)

https://blog.csdn.net/zzti_erlie/article/details/88080822

https://segmentfault.com/a/1190000016566788?utm_source=tag-newest

 

索引

索引相当于一本书的目录，通过目录可以快速找到对应的资源

数据库查询一张表有两种检索方式：全表扫描和根据索引检索

索引提高检索的原理是因为缩小了扫描的范围

索引虽然可以提高检索效率，但也不是随便可以添加，因为索引也是数据库的对象，也需要维护，但维护是有成本的，比如表中的数据经常被修改就不适合添加索引，因为一旦修改，索引就要重新排序，进行维护

什么时候创建索引？

1. 数据量庞大，根据客户需求以及线上的条件
2. 该字段很少DML操作
3. 字段经常出现在where子句中

⚠️  主键和具有unique约束的字段自动添加索引

创建索引

create index 索引名 on 表名(字段名)；

drop index 索引名 on 表名；

create index emp_sal_index on emp(sal)

索引底层的数据结构 B+树

通过B+树缩小扫描范围，底层进行了排序，分区，索引会携带数据在表中的物理地址，最终通过索引检索到数据之后，获得了关联的物理地址，通过物理地址定位表中的数据，效率是最高的。

select ename from emp where ename = 'SMITH'

通过索引转化为

select ename from emp where 物理地址 = 0x3;

索引的分类

• 单一索引：给单个字段添加索引
• 复合索引：给多个字段联合起来添加一个索引
• 主键索引：主键上会自动添加索引
• 唯一索引：有unique约束的字段上会自动添加索引

索引什么时候失效？模糊查询的时候，第一个通配符使用的是%，这时候索引是失效的

 

视图view

视图是站在不同角度看数据，同一张表的数据，通过不同角度去看待

创建和删除视图

create view myview as select empno,ename from emp;

drop view myview;

⚠️  只有DQL语句才能以视图对象的方式创建出来

对视图进行增删查改，会影响到原表数据，可以对视图进行CRUD操作

视图的作用 视图可以隐藏表的实现细节，保密级别高的系统，数据库只对外提供相关的视图，程序员只对视图对象进行CRUD