MySQL存储引擎及事务、索引，视图，三范式的简单介绍

我哪会这个啊

已于 2022-02-15 16:56:27 修改

阅读量265

点赞数

于 2021-01-04 18:28:43 首次发布

本文链接：https://blog.csdn.net/qq_45243783/article/details/112172646

版权

数据库专栏收录该内容

16 篇文章 0 订阅

订阅专栏

1.什么是存储引擎
存储引擎这个名字只在mysql中存在（oracle中有对应的机制，但是不叫做存储引擎，oracle中没有特殊的名字，就是表的存储方式）
2.mysql中之处很多的存储引擎，每一个存储引擎都对应了一种不同的存储方式，每一个存储引擎都有自己的优缺点，需要在适合的时机选择合适的存储引擎
常见的存储引擎
MyISAM :
– MYISAM这种存储引擎不支持事务，是最常用的存储引擎，但是这种存储引擎不是默认的，MYISAM残采用三个文件组织一张表
----- xxx.frm(存储格式的文件)
----xxx.MYD(存储表中数据的文件)
—xxx.MYI(存储表中索引的文件)
优点：可被压缩，节省存储空间，并且可以转换为只读表，提高检索效率
缺点：不支持事务

 Engine: InnoDB(MYSQL中的默认存储引擎)
 Support: DEFAULT
 comment：支持事务，行级锁，外键
 优点：支持事务，行级锁，外键等，这种存储引擎的数据的安全得到保障。
 表的结构存储在xxx.frm文件中
 数据存储在tablespace这样的表空间中（逻辑概念），因此无法被压缩，无法转换成只读，这种InnoDB存储引擎在mysql数据库崩溃之后自动提供恢复机制，支持级联删除和级联更新
   Engine:MEMORY
   缺点： 不支持事务，数据容易丢失，因为所有的数据和索引都是存储在内存当中的
   优点：查询速度最快
   以前叫做HEPA引擎

什么是事务？

一个事务是一个完整的业务逻辑单元，不可再分。
比如：银行账户转账，从A账户向B账户转账10000元，需要执行两条update语句；
update t_act set balance = balance - 10000 where actno = ‘act-001’;
update t_act set balance = balance + 10000 where actno = ‘act-002’;
以上两条DML语句必须同时成功，或者同时失败，不允许出现一条成功，一条失败
要想要保证上面两条DML语句同时成功或者同时失败，那摩就需要使用数据库的“事务机制”

2.和事务相关的语句只有：DML语句（insert,delete,update）
为什么？因为他们这三个语句都是和数据库表当中的“数据”相关的，事务的存在是为了保证数据的安全性，完整性，假设所有的业务都能使用1条DML语句搞定，那摩就不需要事务机制了，但是实际情况不是这样的，通常一个业务需要多条DML语句共同联合完成
3.事务的执行原理
在这里插入图片描述
3.事务的特性
事务包括4大特性：ACID
A:原子性：事务是最小的工作单元，不可再分
B:一致性：事务必须保证多条DML语句同时成功或者同时失败
I:隔离性：事务A和事务B之间具有隔离性
D:持久性：持久性说的是最终数据必须持久化到硬盘文件中，事件才算成功的结束

关于事务之间的隔离性：
事务隔离性存在隔离级别，理论上存在隔离级别包括4个：
第一级别：读未提交（read uncommited）
     对方事务还没有提交，我们当前事务可以读取到对方未提交的数据
     读未提交存在脏读（Dirty Read）现象：表示读取到了脏的数据
第二级别：读已提交（read committed）
          对方事务提交之后的数据我方可以读取到，这种隔离级别解决了：脏读现象没有了
          读已提交存在的问题是：不可重复读
第三级别：可重复读（repeatable read）
         这种隔离级别解决了：不可重复读问题
         这种隔离级别存在的问题是：读取都的数据是幻像
第四级别：序列化读/串行化读
       解决了所有问题
       效率低。需要事务排队
       **oracle数据库默认的隔离级别是：读已提交，mysql数据库默认的隔离级别是：可重复读**

mysql中的事务是支持自动提交的，只要执行一条DML，则提交一次关闭自动提交机制： start transaction，在事务提交回滚之后，他是会自动关闭，想要再次开启事务，需要重新执行 start transaction命令

4.索引
什么是索引？有什么用？
–索引就相当于一本书的目录，通过目录可以快速的找到相对应的资源，在数据库方面，查询一张表的时候有两种检索方式：
—第一种检索方式：全表扫描
—第二种方式：根据索引检索（效率很高）

索引为什么可以提高检索效率呢？
其实最根本的原理是缩小了扫描的范围
索引虽然可以提高检索效率，但是不能随意的添加索引，因为索引也是数据库当中的对象，也需要数据库不断的维护，是有维护成本的，比如，表中的数据经常被修改，这样就不适合添加索引，因为数据一旦修改，索引需要重新排序，进行维护
添加索引是给某一个字段，或者说某些字段添加索引。
select ename,sal,from emp where ename = ‘SMITH’;
当ename字段上没有添加索引的时候，以上sql语句会进行全表扫描，扫描ename字段中所有的值，当ename字段上添加索引的时候，以上sql语句会根据索引扫描，快速定位
怎么创建索引对象？怎么删除索引对象？
创建索引对象：
create index 索引名称 on 表名（字段名）;
删除索引对象：
drop index 索引名称 on 表名；
什么时候考虑给字段添加索引？（满足什么条件）
*数据量庞大，（根据用户的需求，根据线上的环境）
*该字段很少的DML操作。（因为字段进行修改操作，索引也需要维护）
*该字段经常出现在where子句中（经常根据哪个字段查询）

注意：主键和具有unique约束的字段会自动添加索引。
根据主键查询效率较高，尽量根据主键检索

关于sql执行分析可以看这篇讲解：explain执行计划
查看sql语句的执行计划
mysql> explain select ename ,sal,from emp where sal = 5000;
在这里插入图片描述

索引底层采用的数据结构是：B + Tree
索引的实现原理？
—通过B Tree缩小扫描范围，底层索引进行了排序，分区，索引会携带数据在表中的“物理地址”，最终通过索引检索到数据之后，获取到关联的物理地址，通过物理地址定位表中的数据，效率是最高的。
select ename from emp where ename = ‘SMITH’;
通过索引转换为：
select ename fro emp where 物理地址 = ‘0x3’
原理图如下所示
索引原理图
4.索引的分类？
—单一索引：给多个字符添加索引
—复合索引：给多个字段联合起来添加1个索引
—主键索引：有unique约束的字段上会自动添加索引
—唯一索引：有unique约束的字段上会自动添加索引
…
索引什么时候会失效?
select ename from emp where ename like ‘%A%’;
模糊查询的时候，第一个通配符使用的是%，这个时候索引是失效的，因为第一个字符无法匹配，索引对象不知道怎么去定位。
在这里插入图片描述
2.条件中用or，即使其中有条件带索引，也不会使用索引查询（这就是查询尽量不要用or的原因，用in吧）

注意：使用or，又想索引生效，只能将or条件中的每个列都加上索引
3.对于多列索引，不是使用的第一部分，则不会使用索引
在这里插入图片描述

4.如果列类型是字符串，那一定要在条件中将数据使用引号引用起来，否则不会使用索引
在这里插入图片描述
5.如果MySQL预计使用全表扫描要比使用索引快，则不使用索引（比如数据库表中只有一条数据）

视图(View)
什么是视图？
站在不同的角度去看到数据。（同一张表的数据，通过不同的角度去看待）。

怎么创建视图？怎么删除视图？
create view myview as select empno,ename from emp
注意：只有对DQL语句才能以视图的方式创建出来

5.3 对视图进行增删改查，会影响到原表数据。（通过视图影响原表数据的，不是直接操作的原表）可以对视图进行crud操作
在这里插入图片描述

7.数据库设计三范式（重点）
什么是设计范式？
–设计表的依据，按照这个三范式设计的表不会出现数据冗余

三范式都是哪些？
–第一范式：任何一张表都应该有主键，并且每一个字段原子性不可再分
–第二范式：建立在第一范式的基础之上，所有非主键字段完全依赖主键，不能产生部分依赖，如下图所示
在这里插入图片描述
第三范式：建立在第二范式的基础上，所有非主键字段直接依赖主键，不能产生传递依赖

案例：一对多，两张表，多表加外键

注意：
在实际的开发中，以满足客户的需求为主，有的时候会拿冗余换取执行速度（多表联查扫描的记录是笛卡尔积，单表是表的记录有多少条记录，就查询多少条，大不了记录多了加个索引）
8.一对一怎么设计表
一对一有两种设计方案，1.主键共享
在这里插入图片描述
2.外键唯一