MySQL入门门门🚪[第三篇]（完）

存储引擎

存储引擎是mysql中特有术语，实际上是一张表存储或组织数据的方式,不同的存储引擎表存储方式不一样

怎么给表添加/指定存储引擎？

建表的时候可以在建表语句最后的小括号右边，用engine=[目标存储引擎] 指定存储引擎

show create table [表名]; 会展示出表的创建语句，建这张表时小括号右边没有添加任何参数，默认会加上系统版本的设置

default charset=utf8 指定字符编码，mysql8.x版本之前，默认字符编码方式是utf8，由于我用的是8.x版本的mysql，默认的字符集已变为utf8mb4（mb4=more bitys 4，可以用4个字节存储），兼容utf8

查看当前mysql版本支持哪些存储引擎

show engines;

执行后偶可以看到support列为yes表示当前版本支持，默认使用标识default的为InnoDB，No为不支持的存储引擎，一共有9大存储引擎，不同版本的支持程度不同

下面介绍3种常见的存储引擎

MyISAM

管理的表具有以下特征，使用三个文件表示每个表

• 格式文件：存储表结构的定义 mytable.frm
• 数据文件：存储表行的呢绒 mytable.MYD
• 索引文件：存储表上的索引 mytable.MYI 索引就是一本书的目录，缩小扫描范围，提高查询效率
• 可以被转换为压缩，只读表来节省空间，是一种优势，但不支持事务，安全性低
• 对于一张表来说，只要有主键，或者加有unique约束的字段上会自动创建索引

InnoDB

重量级的存储引擎，支持事务，支持数据库崩溃后自动恢复机制，非常安全

• 默认的存储引擎
• 每个InnoDB表在数据库目录中以.frm格式文件表示
• InnoDB表空间tablespace被用于存储表的内容（表空间是一个逻辑名称，表空间存储数据和索引）
• 提供一组用来记录事务性活动的日志文件
• 用COMMIT提交，SAVEPOINT及ROLLBACK回滚，支持事务处理
• 在MySQL服务器崩溃后提供自动恢复
• 多版本（MVCC）和行级锁定
• 支持外健及引用完整性，包括级联删除和更新

MEMORY

数据存储在内存中，行的长度固定，这俩特定使得MEMORY存储引擎非常快

• 在数据库目录内，每个表均以.frm格式的文件表示
• 表数据及索引被存储在内存中（目的是快，查询快）
• 表级锁机制
• 不能把包含TEXT或BLOB字段
• 以前被称为HEAP引用
• 不安全，关机后数据丢失，因为都存在内存中
• 不需要和硬盘交互速度快

事务 transaction

一个事务其实就是一个完整都业务逻辑，假设转账事务，从A账户向B账户中转账1w元

1. 将A账户-1w（update）
2. B账户+1w（update） 只有这两步骤都完成了，才是一个完整的业务逻辑，这是一个最小工作单元，要么同时成功，要么同时失败，不可再拆分，才能保证💰是正确交易。

只有DML语句才会有事务，其他没有（insert delete update）

本质上，一个事务就是多条DML语句同时成或者同时失败

事务是如何实现的

InnoDB引擎提供来一组用来记录事务性活动对日志文件，还是以转账为例子

1. 事务开始了
2. 将A账户-1w
3. B账户+1w
4. 事务结束了

在事务对执行过程中，每一条DML的操作都会记录到事务性活动的日志文件中，在事务的执行过程中，我们可以提交事务，也可以回滚事务

提交事务 commit

清空事务性活动的日志文件，将数据全部彻底持久化到数据库表中，标志着事务结束，并且是全部成功的技术

回滚事务 rollback

将之前所有的DML操作全部撤销，清空事务性活动的日志文件，标志着事务的结束，并且是一种全部失败的结束（回滚只能回滚到上一次的提交点）

实操

mysql中默认情况是支持自动提交事务的（自动提交），每执行一条DML语句，提交一次

如何关闭mysql的自动提交机制？

show variables like 'autocommit' 查看是否开启自动事务，value显示为on就是开启了自动提交

输入start transaction 开启事务，相当于关闭自动提交机制，自动提交实际上是不符合业务开发习惯的，为了保证数据的安全，必须要求同时成功后再提交事务

set autocommit = 'off';也可以关闭自动提交事务

如果是在navicat软件中，可以执行set autocommit = 0;关闭session级的自动提交事务，简单理解为当前窗口即可

在下图的commit示例中，关闭了自动提交事务后，指定delete语句后再进行查询，可以看到表中数据还在，说明事务仍未被提交 接着手动提交事务，再进行查询，此时已经无法查出id为16的数据，说明只有提交后数据才会产生真正的变更 事务提交后，又变回了自动提交

rollback演示

当冇事发生，在实际开发中可能一个service处理了一堆DML，但执行到中间时抛出异常，这时候就需要执行rollback进行回滚了

事务隔离级别

上面解释了事务的执行后，再认识下事务隔离级别。比如A教室和B教室的那堵墙就是隔离级别，墙越厚隔离级别越高

1. 读未提交：read uncommitted 最低级别，未提交就能读到
   • 数据A可以读取到事务B未提交的数据，会存在脏读现象，读到了脏数据（Dirty data），一般都是理论上的，大多数数据库的隔离级别都是读已提交开始

2. 读已提交：read commiteed 事务提交之后能读到
   • 事务A只能读到事务B提交之后的数据，解决了脏读现象，存在不可重复读取数据
     1. 就是在事务开启之后，第一次读到了10条数据，当前事务还没有结束
     2. 此时可能别的事务插入了10条数据
     3. 第二次再读取的时候，读到的数据是20条，条数不一样，称为不可重复读取，每次读到的数据是绝对的真实
   • oracle的默认隔离级别

3. 可重复读：repeatable read 其他事务提交之后也读不到最新的数据，永远读到的都是事务刚开始的数据

   • 事务A开启之后，不管多久，只要当前事务没有被提交，每次在事务A中读取到的数据都是一致的，即使事务B已经将数据进行修改并提交了事务，事务A读到的不会受影响，解决了不可重复读数据的问题，存在的问题是可能幻影读，每次读取的数据都是幻想，不一定真实，mysql默认的隔离级别是可重复读

4. 序列化/串行化：serializable 最高的

   • 效率最低，解决所有问题，这种隔离级别表示事务排队，不能并发，事务同步，每次读取到的数据都是最真实的

查看当前的事务隔离级别 mysql版本低于8执行select @@tx_isolation;

如果mysql版本是8以上，则需要这个select @@transaction_isolation;

设置事务的隔离级别set global transaction isolation level read uncommitted;，其中read uncommitted是隔离级别

演示下序列化级别的事务（其他事务的操作同理）

设置事务隔离级别 退出mysql再进入，查询事务级别 开多一个终端执行开启第二个事务演示排队，可以看到打4顺序的地方一直在卡住，它在等待着左边窗口的事务A结束（commit或者rollback） 由于当前只有两个事务在排队，所以当事务A结束后，事务B立马就查询出了数据

总结事务的特性

1. 原子性：事务是最小的工作单元
2. 一致性：同时成功或者失败
3. 离性性：A事务和B事务之间具有一定的隔离（多线程并发访问同一张表）
4. 持久性：事务最终结束的一个保障，事务提交，就相当于将没有保存到硬盘上的数据进行保存

索引 index

索引是在数据库的表字段上添加的，目的是提高查询效率存在的一种机制。
一张表的一个字段可以添加一个索引，也可以多个字段联合起来添加索引，索引类似字典的目录，有了这个目录就是可以进行缩小范围查询，不需要从第一页翻到最后一页。

select * from t_user where id = 3;
执行这条sql语句时，如果说id字段没有添加索引，那么mysql会进行全表扫描，将字段上的每一个值都进行一次比对，效率相对较慢

但如果id字段设置了索引，那么会进行排序，排序的数据结构为B-Tree（自平衡二叉树）,遵循着左小右大规则存放，采用中序遍历方式取数据，大概长下面这样，查询条件是id=3，4比3小往⬅️走，此时到2，3比2大往➡️走，命中结果，比全表扫描的方式明显要更快

索引的规则

1. 在任意数据库中，主键上会自动添加索引对象，在mysql中，如果有unique约束，则也会自动添加索引
2. 在任何数据库中，任意一张表的任何一行记录，都在硬盘上拥有自己的物理存储编号
3. 在mysql当中，索引是一个单独的对象，不同的存储引擎存放的形式不同，👆有说到存储引擎，但不管哪种都是以B-Tree的形式存在

索引的思考

不应该随便添加索引，索引本身是需要进行维护的，如果索引太多反而会增加系统的压力，导致性能下降

1. 经常出现在where的后面的字段，可以考虑添加
2. 字段很少进行DML（增删改）操作可以考虑，因为DML后索引要进行重新排序
3. 建议通过主键和拥有unique约束的字段进行查询，效率较高

索引的创建和删除

上面说了主键和unique约束会自动创建索引外，可以手动的进行索引的创建

• 创建索引 create index [索引名] on [表名(字段名)];
• 删除索引 delete index [存在的索引名) on [表名];
• 查看表的索引 show index from [表名] from [数据库名]
• 查看一条sql语句的执行是否使用了索引检索 explain [sql语句]

实操演示下有索引和没有索引的区别，已知id为主键，age没有索引

简单的讲解几个字段的意义，type为all时则表示全表扫描，key表示使用了什么字段进行索引检索，没有索引则为null，rows则表示mysql执行查询时认为需要检索的行数，由此可得出，带了索引的查询比没有带索引的查询效率要快得多，可自行进行索引的添加及删除，再通过explain来分析sql的效率

索引的失效

举例几种失效的场景

1. 当where查询的模糊匹配以%开头，此时索引会失效，应当尽量避免
2. 当使用or关键字时，只有or左右两边的字段都拥有索引才会使用索引就行检索；其中一边有索引另一边没有也会使索引失效，这也是or不建议使用的原因

3. 使用复合索引（多个字段的联合索引）时，没有使用左侧的字段进行查询，索引也会失效

用age和gender字段添加联合索引

分别使用age和gender进行查询，可以看到使用左边的字段age时，索引检索生效，使用右边的字段gender时索引就失效了

4. 在where中索引列参加了运算（加减乘除），索引会失效

5. 在where中索引列使用了函数（如uppper），索引会失效

使索引失效的不止这些情况，但这里列举的一些场景也应该进行避免

索引的分类

• 单一索引：一个字段上添加索引
• 复合索引：两个字段及以上的字段上添加索引
• 主键索引：主键上添加索引
• 唯一性索引：拥有unique约束的字段添加索引 如果字段的唯一性弱，则索引意义不大

视图

视图可以理解为从其他视角去看数据，对视图对象进行的增删查改，会导致原表被操作

实际开发中，如果有一条非常复杂的SQL，且使用的场景非常多，那么可以将这条sql以视图的形式创建，在需要编写这段复杂sql的地方进行替换使用，简化了开发，当作table一样使用，视图不存在内存而是存在磁盘上，也就是不删除则不会消失

视图的创建只能是DQL语句

• 创建视图 create view as [dql语句]
• 删除视图 drop view [视图名] 演示视图的简单使用 有一条sql作用是联表查询出学生信息和对应的班级信息，利用这条sql的查询结果创建一个视图，可以看成这个使用就是这条sql的查询结果集，这里视图的名称为scv

后续的使用，当成一个表用即可

对视图进行修改，视图也有对应的类型约束。对视图修改后再进行原表的查询，可以看到修改被同步了

数据库设计范式

第一范式：任意一张表必须有主键，每个字段原子性不可再分

下面这张表的问题在于，1没主键，2存在字段可拆分

姓名	联系方式
罗三翔	13112345678，xx.qq.com

优化后

id	姓名	电话	邮箱
2	罗三翔	13112345678	xx.qq.com

第二范式；在第一范式基础上，要求所有非主键字段完全依赖主键，不要产生部分依赖

学生id	学生姓名	老师id	老师姓名
1	罗三翔	t1	王五
1	罗三翔	t2	李四
2	陈唐	t2	李四

这里的设计中，学生依赖了学生id，老师依赖了老师id，显然产生了部分依赖，造成了数据的冗余重复，学生罗三翔重复了，老师李四重复了，此时正确的优化应该是拆表，学生表，老师表，学生老师关系表

学生表

学生id	学生姓名	老师id	老师姓名
1	罗三翔	t1	王五
1	罗三翔	t2	李四
2	陈唐	t2	李四

老师表

老师id（主键）	老师姓名
t1	王五
t2	李四
t2	李四

学生老师关系表

id（主键）	学生id（外键）	老师id（外键）
1	1	t1
2	1	t2
3	2	t2

第三范式：在第二范式基础上，要求所有非主键字段直接依赖主键，不要产生传递性依赖

学生id（主键）	学生姓名	班级id	班级
1	陈	c1	一年一班
2	王	c1	一年一班
3	李	c2	一年二班
班级依赖了班级id，班级id又依赖了学生id，产生了所谓的传递依赖，造成了班级数据的冗余，这种情况下应该拆分成两张表，班级表和学生表（班级视角上典型的一对多）

班级表

班级id（主键）	班级
c1	一年一班
c1	一年一班
c2	一年二班

学生表

学生id（主键）	学生姓名	班级id（外键）
1	陈	c1
2	王	c1
3	李	c2

一对一关系

如果一张表的数据过于庞大，此时应该对表进行拆分，实际开发勿用明文密码存储

用户表

用户id（主键）	密码	...还有很多字段
1	123456	...
2	123456	...
3	123456	...

将大表拆分

用户登录表

用户id（主键）	密码
1	123456
2	123456
3	123456

用户信息表

id（主键）	姓名	用户id（外键+unique约束）
1	陈	1
2	王	2
3	李	3

这里的唯一约束很重要，因为是一对一关系，所以不可能有两条数据外键一样的

范式总结

• 多对多，三张表，关系表有俩外键
• 一对多，两张表，多的表加外键
• 一对一，外键唯一

切记表和表链接对次数越多，效率越低（笛卡尔积）

数据库设计三范式是理论上的对，实际和理论有偏差，目的是为了客户对需求，有时候会拿空间换速度

入门篇完 ：）

• MySQL入门门门🚪[第一篇]（附Docker使用教程）
• 第二篇🚪