MySQL基础 | 青训营笔记

秋白听雨
110 阅读10分钟

这是我参与「第三届青训营 -后端场」笔记创作活动的第4篇笔记

1. 数据库设计范式

为了建立冗余较小、结构合理的数据库，设计数据库时必须遵循一定的规则。在关系型数据库中这种规则就称为范式。范式是符合某一种设计要求的总结。要想设计一个结构合理的关系型数据库，必须满足一定的范式。在实际开发中最为常见的设计范式有三个：

  • 第一范式：任何一张表都应该有主键，并且每一个字段原子性不可再分。
  • 第二范式：建立在第一范式的基础之上，所有非主键字段完全依赖主键，不能产生部分依赖。

设计案例1：多对多关系？三张表，关系表两外键。

t_student学生表

sno（pk）sname
1001张三
1002李四
1003王五

t_teacher讲师表

tno（pk）tname
001王老师
002赵老师

t_student_teacher_relation 学生讲师关系表

id（pk）sno（fk）tno（fk）
11001001
21002002
31003001
41001002

●第三范式：建立在第二范式的基础之上，所有非主键字段直接依赖主键，不能产生传递依赖。 建立在第二范式基础上的,非主键字段不能传递依赖于主键字段.(不要产生传递依赖)

image.png 设计案例2：一对多关系？两张表，多的表加外键。

班级t_class

cno（pk）cname
01一年一班
02一年二班
03一年三班

学生 t_student

sno（pk）snameclassno（fk）
1001张三01
1002李四02
1003王五03
1004六六03

提醒：在实际的开发中，以满足客户的需求为主，有的时候会拿空间换时间，即允许冗余数据来提高执行速度。

设计案例3：一对一关系。

  • 方式一：主键共享

t_user_login 用户登录表

id（pk）usernamepassword
1zs123
2ls456

t_user_detail 用户详细信息表

id（pk+fk）realnametel
1张三111111
2李四222222
  • 方式二：外键唯一

t_user_login 用户登录表

id（pk）usernamepassword
1张三123
2李四456

t_user_detail 用户详细信息表

id（pk）realnameteluserid（fk+unique）
1张三111111
2李四222222

2. 存储引擎

2.1. 完整的建表语句

CREATE TABLE dept(
	DEPTNO int(2) NOT NULL,
	DNAME varchar(14) DEFAULT NULL,
	LOC varchar(13) DEFAULT NULL,
	PRIMARY KEY (DEPTNO)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

建表的时候可以指定存储引擎，也可以指定字符集。Mysql默认使用的存储引擎是InnoDB方式，默认采用的字符集是UTF8.

2.2. 什么是存储引擎？

存储引擎这名字只有在mysql中存在，存储引擎其实就是指表的存储方式。（oracle中有相应的机制，但是不叫做存储引擎，Oracle中没有特殊的名字，就是”表的存储方式”）

Mysql支持很多存储引擎，每一个存储引擎都对应了一种不同的存储方式。每一个存储引擎都有自己的优缺点，需要在合适的时机选择合适的存储引擎。

2.3. 常见存储引擎

2.3.1 MyISAM存储引擎

MyISAM是mysql最常用的存储引擎，但是这种存储引擎不是默认的，MyISAM 这种存储引擎不支持事务。

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

  • 格式文件-存储表结构的定义（mytable.frm）
  • 数据文件-存储表中的数据（mytable.MYD）
  • 索引文件-存储表上索引（mytable.MYI）

优点：可被压缩，节省存储空间。并且可以转换为只读表，提高检索效率。

缺点：不支持事务。

2.3.2 InnoDB存储引擎

InnoDB存储引擎是mysql的缺省引擎。

表的结构存储在xxx.frm文件中，数据存储在tablespace这样的表空间中（逻辑概念），无法被压缩，无法转换成只读。这种InnoDB存储引擎在MYSQL数据库崩溃之后提供自动恢复机制，InnoDB支持级联删除和级联更新。

优点：支持事务、行级锁、外键等，这种存储引擎数据的安全得到保障。在实际开发中，使用的最多，事务、安全、重量级的引擎。

2.3.3 MEMORY存储引擎

使用MEMORY存储引擎的表，其数据存储在内存中，且行的长度固定，这两个特点使得MEMORY存储引擎查询速度非常快。MEMORY存储引擎以前被称为HEAP引擎。

缺点：不支持事务，数据容易丢失，因为数据存储在内存中。

优点：查询速度最快。

2.3.4 总结

  • MyISAM表最适合于大量的数据读而少量数据更新的混合操作。MyISAM表的另一种适合情形是使用压缩的只读表。
  • 如果查询中包含较多的数据更新操作，应使用InnoDB。其行级锁机制和多版本的支持为数据读取和更新的混合操作提供了良好的并发机制。
  • 可使用MEMORY存储引擎来存储非永久需要的数据，或者是能够从基于键盘的表中重新生成的数据。

3. 事务

3.1. 事务概述

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

比如：银行账户转账，从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语句同时成功或者同时失败，那么就需要使用数据库的“事务机制”。事务可以保证多个操作原子性，要么全成功，要么全失败。和事务相关的语句只有：DML语句（insert delete update），因为它们这三个语句都适合数据库表当中的“数据”相关的。事务的存在是为了保证数据的完整性，安全性。假设所有的业务的都能使用一条DML语句搞定则不需要事务机制。

但实际情况不是这样的，通常一个“事儿（事务、业务）”需要多条DML语句共同联合完成。

3.2. 事务原理

假设一个事儿，需要先执行一条insert，再执行一条update，最后执行一条delete。

开启事务机制：

  • 执行insert语句-->insert...（这个执行成功之后，把这个执行记录到数据库的操作历史当中，并不会向文件中保存一条数据，不会真正的修改硬盘上的数据。）
  • 执行update语句--->update...（这个执行也是记录一下历史操作，不会真正的修改硬盘上的数据）
  • 执行delete语句--->delete...（这个执行也是记录一下历史操作【记录到缓存】，不会真正的修改硬盘上的数据）

提交事务或者回滚事务（事务结束）

当提交事务时，把以上这些操作全部真正执行，数据发生更改；当回滚事务时，相当于取消这些操作，操作不会被执行，数据不会被更改。所以，提交或者回滚事务时，一个事务才真正的结束。

3.3. 事务四大特性

对于数据库来说事务保证批量的DML要么全成功，要么全失败。事务具有四个特征ACID

  • 原子性（Atomicity）

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

  • 一致性（Consistency）

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

  • 隔离性（Isolation）

事务A和事务B之间隔离，一个事务不会影响其他事务的运行。

  • 持久性（Durability）

持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来即使数据库发生故障也不应该对其有任何影响。

3.4. 事务的隔离性

事务隔离性存在隔离级别，理论上隔离级别包括四个：

  • 第一级别：读未提交（read uncommitted）

对方事务还没有提交，我们当前事务可以读取到对方未提交的数据。

读未提交存在脏读现象（dirty read）：表示读到了脏数据，这个数据不稳定，根本不在硬盘文件上，只存在于缓存中。

  • 第二级别：读已提交（read committed）

对方事务提交之后的数据我方可以读取到。这种隔离级别解决了：脏读现象。读已提交存在的问题是：不可重复读。

  • 第三级别：可重复读（repeatable read）

这种隔离级别解决了：不可重复读问题。

  • 第四级别：序列化读/串行化读（serializable）

解决了所有问题。效率第，需要事务排队。

Oracle数据库默认的隔离级别是：读已提交。

Mysql 数据库默认的隔离级别是：可重复读。

4. 索引

4.1. 什么是索引？有什么用？

索引就相当于一本书的目录，通过目录可以快速地找到对应的资源。在数据库方面，查询一张表的时候有两种检索方式：

  • 第一种方式：全表扫描
  • 第二种方式：根据索引检索（效率很高）

索引为什么可以提高检索效率呢？

其实最根本的原理是缩小了扫描的范围。索引虽然可以提高检索效率，但是不能随意的添加索引，因为索引也是数据库当中的对象，也需要数据库不断地维护，是有维护成本的。比如，表中的数据也经常被修改这样就不适合添加索引，因为数据一旦修改，索引需要重新排序，进行维护。

添加索引是给某一个字段，或者说某些字段添加索引。

Select ename ,sal from emp where ename=’smith’;

当ename字段上没有添加索引的时候，以上sql语句会进行全表扫描，扫描enamel字段中的所有的值。

当enamel字段上添加索引的时候，以上sql语句会根据索引扫描，快速定位。

4.2. 创建、删除索引对象

创建索引对象：

Create index 索引名称 on 表名（字段名）;

删除索引对象：

Drop index 索引名称 on 表名;

4.3. 添加索引的情形

  • 数据量庞大。
  • 该字段很少的DML操作。（因为字段进行修改操作，索引也需要维护）
  • 该字段经常出现在where字句中。（经常根据某个字段查询，则这个字段可用考虑设置索引）

注意：主键和具有unique约束的字段自动会添加索引，所以，优先选择主键或unique字段查询。

4.4. 索引原理

知乎-快速理解索引原理

CSDN-MySQL索引原理

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

索引什么时候失效？

Select ename from emp where ename like '%A%';

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

avatar
文章
阅读
粉丝