MySQL这样学才叫了解！【4】

十一、事务

11.1、什么是事务

一个事务是一个i完整的业务逻辑单元，不可再分。事务可以保证多个操作原子性，要么全成功，要么全失败。对于数据库来说事务保证批量的DML要么全成功，要么全失败。

和事务相关的语句只有DML语句，因为他们这三个语句都是和数据库表中的数据相关的。事务的存在是为了保证数据的完整性、安全性。

11.2、开启事务的原理

假设我们完成一个操作，需要先执行一条insert，然后再执行一条update，最后执行一条delete，在Mysql中执行流程可以这么理解：

11.3、事务的特征

事务具有四个特征ACID

原子性（Atomicity）

事务是最小的工作单元，不可再分。整个事务中的所有操作，必须作为一个单元全部完成（取消）。

一致性（Consistency）

事务必须保证多条DML语句同时成功或者同时失败。

隔离性(Isolation)

一个事务不会影响其他事务的运行。

持久性(Durability)

最终该事务对数据库所作的更改将持久地保存在硬盘文件之中，事务才算成功。

MySQL事务默认情况下是自动提交的，可以通过命令来改成手工提交。

start transaction;

11.4、隔离性详解

11.4.1、并发访问可能导致的问题

一个事务开始读取了某行数据，但是另外一个事务已经更新了此数据但没有能够及时提交，这就出现了脏读取。

在同一个事务中，同一个读操作对同一个数据的前后两次读取产生了不同的结果，这就是不可重复读。

幻像读是指在同一个事务中以前没有的行，由于其他事务的提交而出现的新行。幻读强调的是前后读的行数不一样。

11.4.2、隔离级别

InnoDB 实现了四个隔离级别，用以控制事务所做的修改，并将修改通告至其它并发的事务。隔离级别从低往高依次是：

读未提交（READ UMCOMMITTED）

读已提交（READ COMMITTED）

可重复读（REPEATABLE READ） MySQL默认

串行化（SERIALIZABLE）

对方的事务还没有提交，我们当前事务可以读取到对方未提交的数据。这种隔离级别是最低的，读为未提交存在脏读现象，表示堵到了脏数据。

对方事务提交之后的数据我们才可以读到，这种隔离级别解决了脏读现象，但是却出现了不可重复读现象。

这个级别是oracle的默认隔离级别。

我们无法看到已提交的事务了，这种隔离级别虽然解决了不可重复读的问题，但是却带来了幻读的问题。比方说一个线程删除了数据库中的所有数据，但是我们依然读取的是原来的数据，读到的是数据库的备份。

MySQL的默认级别。

将一个事务与其他事务完全地隔离。两个事务不可以并发，线程之间需要排队，也叫作序列化。虽然很安全，但是性能很低且客户的体验不好。

十二、索引

12.1、什么是索引

索引相当于一本书的目录，通过目录可以快速找到对应的资源。索引被用来快速找出在一个列上用一特定值的行，索引可以有效地缩小扫描的范围。添加索引是给某个字段或者是某些字段添加的。

在数据库方面，查询一张表的时候有两种检索方式：

全表扫描

根据索引检索（效率高）

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

12.2、什么时候需要创建索引

数据量庞大。

该字段很少的DML操作（因为字段进行修改操作，索引也需要维护）。

该字段经常出现在where子句中（经常根据哪个字段查询）

注意：主键和具有unique约束的字段会自动添加索引，根据主键查询的效率高，尽量根据主键索引，我们可以查询sql语句的执行计划。他的底层是B+Tree。

explain select * from emp where SAL = 1500;

type字段的值时ALL表示是全表扫描（没有添加索引）。rows表示搜索了14条数据。

12.3、添加索引

-- 给emp表的sal字段添加一个索引，名称为emp_sal_index create index emp_sal_index on emp(sal); -- 语法格式 create index 索引名称 on 表名(字段名)

12.4、查看索引

-- 查看索引的语法 show index from emp; -- 语法格式 show index from 表名;

12.5、删除索引

-- 删除索引的语法 drop index 索引名称 on 表名;

12.6、索引的原理

索引底层采用的数据结构是B+Tree，通过B+Tress缩小扫描范围，底层索引进行排序、分区，索引会携带在表中的物理地址，最终通过索引检索到数据之后，获取到关联的物理地址，通过物理地址定位到表中的数据，效率是最高的（不走表，走硬盘）。

select ename from emp where ename = 'SMITH';

通过索引sql语句会转换

select ename from emp where 物理地址 = '索引检索到的物理地址'

12.7、索引的分类

单一索引：给打那个字段添加索引。

复合索引：给多个字段联合起来添加索引。

主键索引：主键上会自动添加索引。

唯一索引：有unique约束的字段上会自动添加索引。

12.8、索引的失效

在模糊查询的时候，如果第一个通配符使用的是%，这个索引会失效，因为他不知道一开始匹配的字符是什么。

十三、视图

13.1、什么是视图

视图是一种根据查询（也就是SELECT表达式）定义的数据库对象，用于获取想要看到和使用的局部数据。所以他也称为虚拟表。

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

我们可以对视图进行增删改查，会影响到原表的数据，通过视图来影响原表数据的，并不是直接操作原表。只有DQL语句才可以以视图对象的方式创建出来。

13.2、创建视图

-- 语法格式 create view 视图名 as select语句 -- 示范 create view myview as select empo,ename from emp;

13.3、修改视图

-- 语法格式 update 视图名 set 列名 = '值' where 条件;

13.4、删除视图

-- 语法格式 delete from 视图名 where 条件; -- 示范 delete from myview where empo = '12134';

13.5、视图的作用

视图可以隐藏表的实现细节，保密级别比较高的系统，数据库只对外提供相关的视图，面向视图对象进行CRUD。

十四、数据库设计三范式

设计范式是设计表的依据，按照这三个范式设计的表不会出现数据冗余。但是在实际开发中，根据客户的需求，可能会拿数据冗余来换取执行速度，拿空间换时间。

14.1、第一范式

任何一张表都应该有主键，且每一个字段原子性不可再分。

14.2、第二范式

建立在第一范式的基础上，所有非主键字段完全依赖于主键，不能产生部分依赖。

典型的例子就是解决多对多的问题上，遇到多对多的时候，背口诀：多对多？三张表，关系表两外键

14.3、第三范式

建立在第二范式的基础上，所有非主键字段直接依赖主键，不能产生传递依赖。

典型的例子就是一对多，遇到一对多问题的时候背口诀：一对多？两张表，多的表加外键。

14.4、一对一关系的设计方案

14.4.1、主键共享

t_user_login 用户登录表

t_user_detail 用户详细信息表

14.4.2、外键唯一

t_user_login 用户登录表

t_user_detail 用户详细信息表

视图

-- 语法格式 delete from 视图名 where 条件; -- 示范 delete from myview where empo = '12134';

13.5、视图的作用

视图可以隐藏表的实现细节，保密级别比较高的系统，数据库只对外提供相关的视图，面向视图对象进行CRUD。

十四、数据库设计三范式

设计范式是设计表的依据，按照这三个范式设计的表不会出现数据冗余。但是在实际开发中，根据客户的需求，可能会拿数据冗余来换取执行速度，拿空间换时间。

14.1、第一范式

任何一张表都应该有主键，且每一个字段原子性不可再分。

14.2、第二范式

建立在第一范式的基础上，所有非主键字段完全依赖于主键，不能产生部分依赖。

典型的例子就是解决多对多的问题上，遇到多对多的时候，背口诀：多对多？三张表，关系表两外键

14.3、第三范式

建立在第二范式的基础上，所有非主键字段直接依赖主键，不能产生传递依赖。

典型的例子就是一对多，遇到一对多问题的时候背口诀：一对多？两张表，多的表加外键。

14.4、一对一关系的设计方案

14.4.1、主键共享

t_user_login 用户登录表

t_user_detail 用户详细信息表

14.4.2、外键唯一

t_user_login 用户登录表

t_user_detail 用户详细信息表

MySQL 数据库

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。