面试 --- Mysql -- 1 基础知识

** 主键和外键有什么区别？ **

主键（主码） 主键用于唯一标识一个元组，不能重复，不能为空。一个表只有一个主键。

外键（外码） 外键用于与其他表 建立联系 ，为另外一个表的主键， 可以重复，可以为空值。 一个表可以 带有多个外键。

** 数据库 范式 **

第一范式
表中的 字段 不能再被 分割 ，也就是 这个字段 只能有一个值，不能再被分为多个其他字段。
第一范式 是所有 关系型 数据库的 最基本要求。
关系型数据库中的创建 一定 要满足 第一范式。

第二范式

第二范式 在 第一范式 的 基础之上， 增加了 非主属性 对 码 的 部分 函数依赖。

第二范式 在 第一范式 的 基础上 增加了 一个 列 ，这个列 称为 主键 ，非主属性都依赖于主键。

函数依赖 ： 在一张表中 在属性 X 确定的 情况下 ，必定知道 属性 Y 的 值
称为 Y 函数依赖于 X (X->Y)

部分函数依赖 ： 如果 X -> Y ，并且 存在 X 的 真子集 XO , XO ->Y,
就称 Y对X部分函数依赖。

	比如学生基本信息表 R 中（学号，身份证号，姓名）当然学号属性取值是唯一的，在 R 关系中，（学号，身份证号）->（姓名），（学号）->（姓名），（身份证号）->（姓名）；所以姓名部分函数依赖与（学号，身份证号）；

完全函数依赖 ：在一个关系中 ，非主属性 数据项 依赖于 全部 关键字 称之为 完全函数依赖。

	比如学生基本信息表 R（学号，班级，姓名）假设不同的班级学号有相同的，班级内学号不能相同，在 R 关系中，（学号，班级）->（姓名），但是（学号）->(姓名)不成立，（班级）->(姓名)不成立，所以姓名完全函数依赖与（学号，班级）；

传递函数依赖 ： 在关系模式 R(U) 中 ， 设 X,Y,Z 是 U 的 不同属性子集，
如果 X确定 Y ，Y确定 Z ，且 X 不包含 Y ，Y不确定X ，（X∪Y）∩Z=空集合，则称 Z 传递依赖 于 X 。
传递函数依赖会导致数据冗余和异常。传递函数依赖的 Y 和 Z 子集往往同属于某一个事物，因此可将其合并放到一个表中。

	比如在关系 R(学号 ,姓名, 系名，系主任)中，学号 → 系名，系名 → 系主任，所以存在非主属性系主任对于学号的传递函数依赖。

第三范式

第三范式 是在 第二范式 的 基础上 ，消除了 非主属性对码的 传递依赖。
也就是 表中的 所有数据元素 不但 能 唯一地 被 主关键字所标识，而且 它们之间还必须相互独立 ， 不存在其他的函数关系。
符合 第三范式 要求 的 数据库设计 ， 基本解决了数据冗余过大，插入异常 ， 修改异常 ，删除异常等问题。
	
比如在关系 R(学号 ,姓名, 系名，系主任)中，学号 → 系名，系名 → 系主任，所以存在非主属性系主任对于学号的传递函数依赖，所以该表的设计，不符合 3NF 的要求。

总结

1NF：属性不可再分。
2NF：1NF 的基础之上，消除了非主属性对于码的部分函数依赖。
3NF：3NF 在 2NF 的基础之上，消除了非主属性对于码的传递函数依赖 。

** 什么 是 存储过程？ **

可以把存储过程 看成是 一些 Sql 语句的集合 ，中间 增加了点 逻辑控制语句。

存储过程在业务比较复杂的时候是非常实用的，比如很多时候我们完成一个操作可能需要写一大串 SQL 语句，这时候我们就可以写有一个存储过程，这样也方便了我们下一次的调用。存储过程一旦调试完成通过后就能稳定运行，另外，使用存储过程比单纯 SQL 语句执行要快，因为存储过程是预编译过的。

存储过程在互联网公司应用不多，因为存储过程难以调试和扩展，而且没有移植性，还会消耗数据库资源。

** drop delete turncate 区别 **

drop : 直接将表删除， 再删除表的 时候 使用。
drop table 表名；

turncate： 只删除表中的 所有数据，在插入数据的时候 自增长 id 又会从 1开始， 清空表数据的时候用。
truncate table 表名；

delete ： 删除某一列的数据，如果不加 where 子句和truncate table 表名作用类 似。
delete from 表名 where 列名=值。

truncate 和不带 where 子句的 delete、以及 drop 都会删除表内的数据，但是 truncate 和 delete 只删除数据不删除表的结构(定义)，执行 drop 语句，此表的结构也会删除，也就是执行 drop 之后对应的表不复存在。

truncate 和 drop 属于 DDL 语句 ， delete 属于 DML语句。
truncate 和 drop 操作成功后， 原数据 不放在 rollback segement 中，不能回滚，操作不触发 tirgger；
而delete 操作成功后， 原数据 会放在 rollback segement中 ，事务提交后才生效。

DML 是 数据库操作语言 ，是对 数据库中 表记录的 操作 ，主要是 表记录的 插入 更新 删除 和 查询 。

DDL 是 数据库定义语言， 是对数据库 内部 对象 进行 创建 和删除 修改 的 操作语言 。

与DML 语言 最大的 不同在于 DML 是对 表内部 数据的 操作 ，不涉及 表定义 结构的修改 ，更不会涉及其他对象。

执行速度 ： drop - truncate - delete

MyISM 和 InnoDB 区别

1. InnoDB 支持 事务 ，而 MyISM 不支持 事务。
   这是 Mysql 将 默认 存储引擎 从 MyISM 变成 InnoDB 的重要原因。

2. InnoDB 支持外键 ，而 MyISM 不支持。
   对于 一个 包含 外键 的 InnoDB 表 转为 MyISM 的话 会失败。

3. InnoDB 是 聚集索引 ， 而 MyISM 是 非 聚集索引。
   聚簇索引的文件 存放在 主键索引 的 叶子节点上， 因此 InnoDB 必须要有主键， 通过 主键索引 效率很高。 但辅助索引需要两次查询，先查询到主键，然后在通过主键查询到数据。
   因此，主键不应该太大，太大会导致其他索引也很大。
   而MyISM 是 非聚集索引， 数据文件 是分离的。 索引保存的是数据文件的指针。 主键索引和辅助索引是独立的。

4. InnoDB 不保存 表的具体行数， 执行
   select count（*） from table 时候需要 全表查询 。
   而 MyISM 用 一个变量 保存了 整个表的行数，
   执行这条语句的 时候 只需要 读出该变量就可以，速度很快。

5. InnoDB 最小的 锁粒度 是 行级锁。而 MyISM 的 最小 锁粒度 是表级锁。
   一个更新语句 会锁住 整张表， 导致其他查询 和更新 都会被 阻塞，因此并发访问受限。
   这也是 Mysql 将 默认 存储引擎 从 MyISM 变成 InnoDB 的重要原因。

InnoDB 存储引擎的锁的算法有三种：

Record lock：记录锁，单个行记录上的锁
Gap lock：间隙锁，锁定一个范围，不包括记录本身
Next-key lock：record+gap 临键锁，锁定一个范围，包含记录本身

** 如何 选择 引擎 ？ **

1. 是否支持事务。 支持的话选择 InnoDB。
2. 表中大多数是 读查询操作 ， 可以选择 MyISM ，如果 读写 频繁的话，选择InnoDB.
3. 系统崩溃后, MyISM 恢复很难，能接受就选用 ，不能就选 InnoDB。

** 模糊查询 **

页面 搜索的时候 尽量别使用 左查询 和 全模糊查询 ，如果需要的 话 可以走 搜索引擎 来解决。

因为 索引文件 具有 B-Tree 的 最左前缀匹配特性 ， 如果 左边的值未确定 ，那么无法 使用此索引。

** 外键和级联 **

不得使用 外键 和 级联 ， 一切外键 概念 都要在 应用层 解决。

以 学生表为例， 学生表 的 student_id 是主键 ，而 成绩表 student_id 是外键 ， 如果 更新 学生表中的student_id ， 同时 触发 成绩表中 student_id 更新 ，
即为 级联更新。 外键 与 级联 更新 适用于 单机低并发 ， 不适合 分布式 ，高并发群 ， 级联更新 是 强阻塞 ， 存在 数据更新 风暴 的 风险， 外键也 影响 数据库 插入速度。

** 为什么 不要 使用 外键 ？**

1. 增加了复杂性。（外键约束）
2. 对分库 分表 不友好 。 因为 分库分表下 外键 是 无法生效的。

外键 也有好处 ： 1. 保证了 数据库数据的一致性 2. 级联操作方便，减轻了程序代码量。

关于@Transactional注解

@Transactional事务不要滥用。事务会影响数据库的QPS，另外使用事务的地方需要考虑各方面的回滚方案，包括缓存回滚、搜索引擎回滚、消息补偿、统计修正等。