Mysql

最新推荐文章于 2023-07-22 18:14:59 发布

啊这就离谱

最新推荐文章于 2023-07-22 18:14:59 发布

阅读量187

点赞数

分类专栏：面试

本文链接：https://blog.csdn.net/JennyTheArtist/article/details/108528068

版权

面试专栏收录该内容

9 篇文章 0 订阅

订阅专栏

数据库的三大范式

第一范式（1NF）：强调列的原子性，即数据库表的每一列都是不可分割的原子数据项
例如下表就不符合第一范式：

实际上，1NF是所有关系型数据库的最基本要求，你在关系型数据库管理系统（RDBMS），例如SQL Server，Oracle，MySQL中创建数据表的时候，如果数据表的设计不符合这个最基本的要求，那么操作一定是不能成功的。也就是说，只要在RDBMS中已经存在的数据表，一定是符合1NF的。如果我们要在RDBMS中表现表中的数据，就得设计为下表的形式：

存在问题：
1.数据冗余：每一名学生的学号、姓名、系名、系主任这些数据重复多次。每个系与对应的系主任的数据也重复多次。
2.插入异常：假如学校新建了一个系，但是暂时还没有招收任何学生（比如3月份就新建了，但要等到8月份才招生），那么是无法将系名与系主任的数据单独地添加到数据表中去的。
3.修改异常：假如李小明转系到法律系，那么为了保证数据库中数据的一致性，需要修改三条记录中系与系主任的数据。
4.删除异常：假如将某个系中所有学生相关的记录都删除，那么所有系与系主任的数据也就随之消失了（一个系所有学生都没有了，并不表示这个系就没有了）。
第二范式：2NF在1NF的基础之上，消除了非主属性对于码的部分函数依赖。
即：如果码包含两个主属性（学号，课名），则其他非主属性不可只完全依赖于其中一个主属性（如姓名完全依赖于学号这就是不满足第二范式）
进行拆分后，可以满足第二范式：

现在我们来看一下，进行同样的操作，是否还存在着之前的那些问题？
1.数据冗余是否减少了？
学生的姓名、系名与系主任，不再像之前一样重复那么多次了。——有改进
2.插入一个尚无学生的新系的信息。
因为学生表的码是学号，不能为空，所以此操作不被允许。——无改进
3.李小明转系到法律系
只需要修改一次李小明对应的系的值即可。——有改进
4.删除某个系中所有的学生记录
该系的信息仍然全部丢失。——无改进

总结：使用2NF仍然存在插入异常和删除异常的问题。

第三范式：3NF在2NF的基础之上，消除了非主属性对于码的传递函数依赖。

由此可见，符合3NF要求的数据库设计，基本上解决了数据冗余过大，插入异常，修改异常，删除异常的问题。当然，在实际中，往往为了性能上或者应对扩展的需要，经常做到2NF或者1NF，但是作为数据库设计人员，至少应该知道，3NF的要求是怎样的。

BCNF范式
要了解 BCNF 范式，那么先看这样一个问题：
若：
1.某公司有若干个仓库；
2.每个仓库只能有一名管理员，一名管理员只能在一个仓库中工作；
3.一个仓库中可以存放多种物品，一种物品也可以存放在不同的仓库中。每种物品在每个仓库中都有对应的数量。

已经满足3NF的条件，但是仍存在问题：
1.先新增加一个仓库，但尚未存放任何物品，是否可以为该仓库指派管理员？——不可以，因为物品名也是主属性，根据实体完整性的要求，主属性不能为空。
2.某仓库被清空后，需要删除所有与这个仓库相关的物品存放记录，会带来什么问题？——仓库本身与管理员的信息也被随之删除了。
3.如果某仓库更换了管理员，会带来什么问题？——这个仓库有几条物品存放记录，就要修改多少次管理员信息
造成问题的原因：存在着主属性对于码的部分函数依赖与传递函数依赖。
解决办法就是要在 3NF 的基础上消除主属性对于码的部分与传递函数依赖：
仓库（仓库名，管理员）
库存（仓库名，物品名，数量）

ACID

Atomicity（原子性）：一个事务中的所有操作，或者全部完成，或者全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被rollback到事务开始前的状态，就像这个事务从来没有执行过一样。即，事务不可分割、不可约简。
Consistency（一致性）：在事务开始前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设约束、触发器、级联回滚等。
Isolation（隔离性）：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致的数据不一致
Durability（持久性）：事务处理结束后，对数据的修改就是永久的，即使系统故障也不会丢失。

数据库事务的隔离级别

未提交读（read-uncommitted）：最低隔离级别。事务未提交前，就可被其他事务读取。会出现脏读、幻读、不可重复读。
提交读（read-committed）：一个事务提交后才能被其他事务读取到。会造成幻读、不可重复读。
可重复读（repeatable）：默认级别。保证每次读取同一个数据时，其值都和事务开始时候的内容一致。会造成幻读。
序列化（serializable）：代价最高最可靠的隔离级别。能防止脏读、幻读、不可重复读。

修改隔离级别：
SET session TRANSACTION ISOLATION LEVEL Serializable;（参数可以为：Read uncommitted，Read committed，Repeatable，Serializable）

脏读：一个事务能读取到另一个事务还未提交的数据
不可重复读：在一个事务内，多次读同一数据。
幻读：由于一个事务修改了数据，另一个事务内多次查询返回的结果集不一样，好像产生了幻觉。

不可重复读和幻读的区别：

不可重复读：可以这样理解是两次查询同一组记录，这一组记录的数据内容不一样，比如原先id=5的数据的name=5事务2修改为name=6,事务1再次查询时发现变成6了。针对的是具体数据。幻读针对的查询出来的数据这个整体，比如第一次按name=lisi查询记录有5条都是lisi，第二次再查询，可能变为6或者4或者别的。就好像第一次查询出现幻觉一样。
幻读和不可重复读都是指的一个事务范围内的操作受到其他事务的影响了。只不过,幻读的重点在插入和删除，不可重复读重点在修改。

char和varchar的区别

char(n)：固定长度类型。优点：效率高。缺点：占用空间。适用场景：固定长度，例如储存密码的md5值
varchar(n)：可变长度（定义的n是其最大长度），存储的值是每个值占用的字节再加上一个用来记录其长度的字节的长度。

从空间上考虑varchar比较合适，从效率上考虑char比较合适，二者使用需要权衡。

float和double的区别

float最多可以存储8位十进制数，并在内存中占4字节
double最多可以存储16位的十进制数，并在内存中占8字节

MyISAM、InnoDB

一张自增表中总共17条数据，删除了最后2条数据，重启MySQL数据库，又插入了一条数据，此时id是几？

表类型如果是MyISAM，id是18
表类型如果是InnoDB，id是15

原因：InnoDB表只会把自增主键的最大id记录在内存中，所以重启之后会导致最大id丢失

InnoDB引擎：提供了对数据库acid事务的支持，提供了行级锁和外键的约束。它的设计的目标就是处理大数据容量的数据库系统。mysql运行的时候，InnoDB会在内存中建立缓冲池，用于缓冲数据和索引。但是该引擎不支持全文搜索，同时也启动得比较慢。它不会保存表的行数，使用select count(*) frome table指令时需要进行全表扫描。由于锁的粒度小，写操作时不会锁定全表，所以在并发度较高的场景下使用会提升效率。
MylASM引擎：不提供事务的支持，也不提供行级锁和外键。因此当执行插入和更新语句时，即执行写操作的时候需要锁定这个表，所以执行效率会降低。与innodb不同的是，myiasm引擎会保存表的行数。

MyISAM、InnoDB区别：

InnoDB支持事务，MyISAM不支持
InnoDB支持外键和行级锁，MyISAM不支持
MyISAM必须依靠操作系统来管理读取与写入的缓存，而InnoDB则是有自己的读写缓存管理机制。（InnoDB不会将被修改的数据页立即交给操作系统）因此在某些情况下，InnoDB的数据访问会比MyISAM更有效率。
系统崩溃后，MyISAM恢复起来更困难

内连接、外连接

内连接（inner join）：把匹配的关联数据显示出来
左外连接（left join）：把左边的表全部显示出来，右边的表显示符合条件的数据
右外连接（right join）：把右边的表全部显示出来，左边的表显示符合条件的数据
全外连接（full join）：左右表全部显示，没有的项目补null
mysql中没有全外连接，使用union把左右外连接的结果合起来：

SELECT * FROM person LEFT JOIN card ON person.cardid=card.id
UNION
SELECT * FROM person RIGHT JOIN card ON person.cardid=card.id;

索引

没有索引的时候，查找数据需要进行全表扫描。
索引是满足某种特定查找算法的数据结构，而这些数据结构会以某种方式指向数据，从而实现高效查找数据。
使用索引的全部意义就是通过减少一张表中需要查询的记录/行的数目来加快搜索的速度。
mysql中是用B+树实现的，B+树的搜索效率，可以达到二分法的性能。

索引分类
单值索引：一个索引只包含单个列，一个表可以有多个单列索引
唯一索引：索引列的值必须唯一，但允许有空值
复合索引：一个索引包含多个列

基本语法

创建：
CREATE [UNIQUE] INDEX indexName ON mytable(columnname(length))
ALTER mytable ADD [UNIQUE] INDEX [indexName] ON (columnname(length))
删除：DROP INDEX [indexName] ON mytable
查看：SHOW INDEX FROM table_name
四种添加索引的方式：
ALTER TABLE tbl_name ADD PRIMARY KEY(column_list)添加一个主键，意味着索引是惟一的，且非NULL
ALTER TABLE tbl_name ADD UNIQUE index_name(column_list)唯一索引（除NULL外，NULL可能出现多次）
ALTER TABLE tbl_name ADD INDEX index_name(column_list)添加普通索引，索引值可出现多次
ALTER TABLE tbl_name ADD FULLTEXT index_name(column_list)全文索引

索引结构
BTree索引：使用B+树，非叶子结点并不真正存储数据，只有叶子结点存储。B+树查询3次就可以查询上百万数据。
Hash索引
full-text全文索引
R-Tree索引

哪些情况需要创建索引
1.主键自动建立唯一索引
2.频繁作为查询条件的字段应该建立索引
3.查询中与其他表关联的字段，外键关系建立索引
4.频繁更新的字段不适合创建索引，每次更新不但要更新记录还会更新索引
5.where条件里用不掉的字段不建立索引
6.单键/组合索引的选择问题（高并发条件下倾向创建组合索引）
7.查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度
8.查询中统计或者分组字段

查看mysql的索引是否满足需求：
使用explain：explain select * from table where type=1可以查看是如何执行sql语句的

行锁和表锁

MyISAM只支持表锁，InnoDB支持表锁和行锁，默认为行锁。

表级锁：开销小，加锁快，不会出现死锁。锁定粒度大，发生锁冲突的概率最高，并发质量低。
行级锁：开销大，加锁慢，会出现死锁。锁粒度小，发生锁冲突的概率小，并发度最高。

乐观锁和悲观锁

乐观锁：每次去拿数据的时候都认为别人不会修改，所以不会上锁。但在提交更新的时候会判断一下在此期间别人有没有去更新这个数据。
悲观锁：每次去拿数据的时候都认为别人会修改，所以每次在拿数据的时候都会上锁，这样别人想拿这个数据就会阻止，直到这个锁被释放。

数据库的乐观锁需要自己实现：在表里添加一个version字段，每次修改成功值加1，这样每次修改的时候先对比一下，自己拥有的version和数据库现在的version是否一致，如果不一致就不修改。

问题排查

使用show processlist命令查看当前所有连接信息
使用explain命令查询sql语句执行计划
开启慢查询日志，查看慢查询的sql

性能优化

为搜索字段创建索引
避免使用select *，列出需要查询的字段
垂直分割分表
选择正确的存储引擎

啊这就离谱

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
Mysql

目录数据库的三大范式ACID数据库事务的隔离级别char和varchar的区别float和double的区别MyISAM、InnoDB内连接、外连接索引的实现数据库的三大范式第一范式：强调列的原子性，即数据库表的每一列都是不可分割的原子数据项第二范式：要求实体的属性完全依赖于主关键字第三范式：任何非主属性不依赖于其他非主属性ACIDAtomicity（原子性）：一个事务中的所有操作，或者全部完成，或者全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被rollback到事务开
复制链接

扫一扫