八股文|数据库

数据库中的概念

元组 ： 元组（tuple）是关系数据库中的基本概念，关系是一张表，表中的每行（即数据库中的每条记录）就是一个元组，每列就是一个属性。 在二维表里，元组也称为行。
码 ：码就是能唯一标识实体的属性，对应表中的列。
候选码 ：若关系中的某一属性或属性组的值能唯一的标识一个元组，而其任何、子集都不能再标识，则称该属性组为候选码。例如：在学生实体中，“学号”是能唯一的区分学生实体的，同时又假设“姓名”、“班级”的属性组合足以区分学生实体，那么{学号}和{姓名，班级}都是候选码。
主码 : 主码也叫主键。主码是从候选码中选出来的。一个实体集中只能有一个主码，但可以有多个候选码。
外码 : 外码也叫外键。如果一个关系中的一个属性是另外一个关系中的主码则这个属性为外码。
主属性 ：候选码中出现过的属性称为主属性。比如关系 工人（工号，身份证号，姓名，性别，部门）. 显然工号和身份证号都能够唯一标示这个关系，所以都是候选码。工号、身份证号这两个属性就是主属性。如果主码是一个属性组，那么属性组中的属性都是主属性。
非主属性：不包含在任何一个候选码中的属性称为非主属性。比如在关系——学生（学号，姓名，年龄，性别，班级）中，主码是“学号”，那么其他的“姓名”、“年龄”、“性别”、“班级”就都可以称为非主属性。

数据库三大范式

1NF：属性不能再被分割，所有关系型数据库创建的表一定满足1NF
2NF：在1NF的基础上，消除非主属性对于码的部分函数依赖，非主属性都依赖于主键。(学号，身份证)——>姓名，学号——>姓名，身份证——>姓名，所以姓名部分依赖于(学号，身份证)
3NF：在2NF的基础上，消除非主属性对于码的传递函数依赖。学号——>系名，系名——>系主任，系名和系主任则可以单独建表，消除数据冗余。

为什么不推荐使用外键与级联

外键触发的更新是级联更新，级联更新是强阻塞，存在数据库更新风暴的风险，而且外键影响数据库的插入速度，不适合分布式、高并发集群，适用于单机低并发场景。

drop、delete和truncate的区别

• drop(丢弃数据): drop table 表名 ，直接将表都删除掉，在删除表的时候使用。
• truncate (清空数据) : truncate table 表名 ，只删除表中的数据，再插入数据的时候自增长 id 又从 1 开始，在清空表中数据的时候使用。
• delete（删除数据） : delete from 表名 where 列名=值，删除某一行的数据，如果不加 where子句和truncate table 表名作用类似。

truncate 和 drop 属于DDL(数据定义语言)语句，操作立即生效，原数据不放到 rollback segment 中，不能回滚，操作不触发 trigger。而delete 语句是 DML (数据库操作语言)语句，这个操作会放到 rollback segement 中，事务提交之后才生效。

数据库设计通常分为哪几步

1. 需求分析 : 分析用户的需求，包括数据、功能和性能需求。
2. 概念结构设计 : 主要采用 E-R 模型进行设计，包括画 E-R 图。
3. 逻辑结构设计 : 通过将 E-R 图转换成表，实现从 E-R 模型到关系模型的转换。
4. 物理结构设计 : 主要是为所设计的数据库选择合适的存储结构和存取路径。
5. 数据库实施 : 包括编程、测试和试运行
6. 数据库的运行和维护 : 系统的运行与数据库的日常维护。

MySQL 字符编码集

• utf8 ： utf8编码只支持1-3个字节 。 在 utf8 编码中，中文是占 3 个字节，其他数字、英文、符号占一个字节。但emoji 符号占 4 个字节，一些较复杂的文字、繁体字也是 4 个字节。
• utf8mb4 ： UTF-8 的完整实现，正版！最多支持使用4 个字节表示字符，因此，可以用来存储 emoji 符号。

排序与分页

使用ORDER BY子句排序，ASC升序，DESC降序
SELECT * FROM employees ORDER BY salary；
使用LIMIT子句限制查询记录数，并实现分页
LIMIT 偏移量，行数
分页公式：SELECT * FROM table LIMIT (PageNo - 1)*PageSize,PageSize;

MyISAM和InnoDB 的区别

• MySQL 5.5 之前，MyISAM 引擎是 MySQL 的默认存储引擎。虽然，MyISAM的性能还行，各种特性也还不错（比如全文索引、压缩、空间函数等）。但是，MyISAM不支持事务和行级锁，而且最大的缺陷就是崩溃后无法安全恢复。5.5 版本之后，MySQL 引入了InnoDB（事务性数据库引擎），MySQL 5.5 版本后默认的存储引擎为 InnoDB。
• MyISAM只有表级锁(table-level locking)，而 InnoDB 支持行级锁(row-level locking)和表级锁,默认为行级锁。 MyISAM 不提供事务支持。InnoDB提供事务支持，具有提交(commit)和回滚(rollback)事务的能力。
• MyISAM 不支持外键，而 InnoDB 支持外键。
• MyISAM 不支持数据库异常崩溃后的安全恢复，而 InnoDB 支持。
• MyISAM 不支持MVCC，而 InnoDB 支持。

MySQL事务

事务是逻辑上的一组操作，要么都执行，要么都不执行。事务的ACID特性：

• 原子性（Atomicity） ： 事务是最小的执行单位，不允许分割。事务的原子性确保动作要么全部完成，要么完全不起作用；
• 一致性（Consistency）： 执行事务前后，数据保持一致，例如转账业务中，无论事务是否成功，转账者和收款人的总额应该是不变的；
• 隔离性（Isolation）： 并发访问数据库时，一个用户的事务不被其他事务所干扰，各并发事务之间数据库是独立的；
• 持久性（Durability）： 一个事务被提交之后。它对数据库中数据的改变是持久的，即使数据库发生故障也不应该对其有任何影响。

数据库事务的实现原理：

以 MySQL 的 InnoDB 引擎为例：MySQL InnoDB 引擎使用 redo log(重做日志) 保证事务的持久性，使用 undo log(回滚日志) 来保证事务的原子性，通过 锁机制、MVCC 等手段来保证事务的隔离性，保证了事务的持久性、原子性和隔离性之后，一致性才能得到保障。

并发事务来的问题

多个事务并发运行，经常会操作相同的数据来完成各自的任务。并发虽然是必须的，但可能会导致以下的问题：
 脏读（Dirty read）: 当一个事务正在访问数据并且对数据进行了修改，而这种修改还没有提交到数据库中，这时另外一个事务也访问了这个数据，然后使用了这个数据。因为这个数据是还没有提交的数据，那么另外一个事务读到的这个数据是“脏数据”，依据“脏数据”所做的操作可能是不正确的。
 丢失修改（Lost to modify）: 指在一个事务读取一个数据时，另外一个事务也访问了该数据，那么在第一个事务中修改了这个数据后，第二个事务也修改了这个数据。这样第一个事务内的修改结果就被丢失，因此称为丢失修改。 例如：事务 1 读取某表中的数据 A=20，事务 2 也读取 A=20，事务 1 修改 A=A-1，事务 2 也修改 A=A-1，最终结果 A=19，事务 1 的修改被丢失。
 不可重复读（Unrepeatable read）: 指在一个事务内多次读同一数据。在这个事务还没有结束时，另一个事务也访问该数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改导致第一个事务两次读取的数据可能不太一样。这就发生了在一个事务内两次读到的数据是不一样的情况，因此称为不可重复读。
 幻读（Phantom read）: 幻读与不可重复读类似。它发生在一个事务（T1）读取了几行数据，接着另一个并发事务（T2）插入了一些数据时。在随后的查询中，第一个事务（T1）就会发现多了一些原本不存在的记录，就好像发生了幻觉一样，所以称为幻读。

事务隔离级别有哪些

SQL 标准定义了四个隔离级别：

• READ-UNCOMMITTED(读取未提交)： 最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读、幻读或不可重复读。
• READ-COMMITTED(读取已提交)： 允许读取并发事务已经提交的数据，可以阻止脏读，但是幻读或不可重复读仍有可能发生。
• REPEATABLE-READ(可重复读)： 对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改，可以阻止脏读和不可重复读，但幻读仍有可能发生。
• SERIALIZABLE(可串行化)： 最高的隔离级别，完全服从 ACID 的隔离级别。所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰，也就是说，该级别可以防止脏读、不可重复读以及幻读。

MySQL索引

索引是一种用于快速查询和检索数据的数据结构。常见的索引结构有: B 树， B+树和 Hash。索引的作用相当于目录。
索引的优缺点：
优点 ：
 使用索引可以大大加快 数据的检索速度（大大减少检索的数据量）, 这也是创建索引的最主要的原因。
 通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。
缺点 ：
 创建索引和维护索引需要耗费许多时间。当对表中的数据进行增删改的时候，如果数据有索引，那么索引也需要动态的修改，会降低 SQL 执行效率。
 索引需要使用物理文件存储，也会耗费一定空间。

为什么不使用Hash作为索引的数据机构

 Hash 冲突问题 ：我们上面也提到过Hash 冲突了，不过对于数据库来说这还不算最大的缺点。
 Hash 索引不支持顺序和范围查询(Hash 索引不支持顺序和范围查询是它最大的缺点： 假如我们要对表中的数据进行排序或者进行范围查询，那 Hash 索引可就不行了。如：SELECT * FROM tb1 WHERE id<500;Hash结构就需要每一个都进行Hash计算

B 树& B+树两者有何异同

 B树的所有节点既存放键(key) 也存放数据(data)，而 B+树只有叶子节点存放key和data，其他内节点只存放 key。
 B树的叶子节点都是独立的;B+树的叶子节点有一条引用链指向与它相邻的叶子节点。
 B树的检索的过程相当于对范围内的每个节点的关键字做二分查找，可能还没有到达叶子节点，检索就结束了。而 B+树的检索效率就很稳定了，任何查找都是从根节点到叶子节点的过程，叶子节点的顺序检索很明显。

MyISAM和InnoDB实现索引的异同

MyISAM 引擎和 InnoDB 引擎都是使用 B+Tree 作为索引结构。MyISAM 引擎中，B+Tree 叶节点的 data 域存放的是数据记录的地址。在索引检索的时候，首先按照 B+Tree 搜索算法搜索索引，如果指定的 Key 存在，则取出其 data 域的值，然后以 data 域的值为地址读取相应的数据记录。这被称为“非聚簇索引”。
InnoDB 引擎中，其数据文件本身就是索引文件。相比 MyISAM，索引文件和数据文件是分离的，其表数据文件本身就是按 B+Tree 组织的一个索引结构，树的叶节点 data 域保存了完整的数据记录。这个索引的 key 是数据表的主键，因此 InnoDB 表数据文件本身就是主索引。这被称为“聚簇索引（或聚集索引）”

二级索引(辅助索引)

二级索引又称为辅助索引，是因为二级索引的叶子节点存储的数据是主键。也就是说，通过二级索引，可以定位主键的位置。唯一索引，普通索引，前缀索引等索引属于二级索引。
 唯一索引(Unique Key) ：唯一索引也是一种约束。唯一索引的属性列不能出现重复的数据，但是允许数据为 NULL，一张表允许创建多个唯一索引。 建立唯一索引的目的大部分时候都是为了该属性列的数据的唯一性，而不是为了查询效率。
 普通索引(Index) ：普通索引的唯一作用就是为了快速查询数据，一张表允许创建多个普通索引，并允许数据重复和 NULL。
 前缀索引(Prefix) ：前缀索引只适用于字符串类型的数据。前缀索引是对文本的前几个字符创建索引，相比普通索引建立的数据更小， 因为只取前几个字符。
 全文索引(Full Text) ：全文索引主要是为了检索大文本数据中的关键字的信息，是目前搜索引擎数据库使用的一种技术。Mysql5.6 之前只有 MYISAM 引擎支持全文索引，5.6 之后 InnoDB 也支持了全文索引。

聚簇索引VS非聚簇索引

聚集索引：即索引结构和数据一起存放的索引。主键索引属于聚集索引。
非聚集索引：即索引结构和数据分开存放的索引。二级索引属于非聚集索引。非聚集索引的叶子节点并不一定存放数据的指针，因为二级索引的叶子节点就存放的是主键，根据主键再回表查数据。

聚簇索引优缺点

优点：聚集索引的查询速度非常的快，因为整个 B+树本身就是一颗多叉平衡树，叶子节点也都是有序的，定位到索引的节点，就相当于定位到了数据
缺点：依赖于有序的数据 ：因为 B+树是多路平衡树，如果索引的数据不是有序的，那么就需要在插入时排序，如果数据是整型还好，否则类似于字符串或 UUID 这种又长又难比较的数据，插入或查找的速度肯定比较慢。更新代价大 ： 如果对索引列的数据被修改时，那么对应的索引也将会被修改，而且聚集索引的叶子节点还存放着数据，修改代价肯定是较大的，所以对于主键索引来说，主键一般都是不可被修改的。

非聚簇索引的优缺点

优点：更新代价比聚集索引要小 。非聚集索引的更新代价就没有聚集索引那么大了，非聚集索引的叶子节点是不存放数据的
缺点：跟聚集索引一样，非聚集索引也依赖于有序的数据。可能会二次查询(回表) ，这应该是非聚集索引最大的缺点，当查到索引对应的指针或主键后，可能还需要根据指针或主键再到数据文件或表中查询。

覆盖索引

如果一个索引包含（或者说覆盖）所有需要查询的字段的值，我们就称之为“覆盖索引”。

MySQL三大日志

MySQL日志包括错误日志、查询日志、慢查询日志、事务日志、二进制日志几大类。
比较重要的是二进制日志binlog(归档日志)、事务日志redo log(重做日志)和undo log(回滚日志)。
redo log
InnoDB独有，让MySQL拥有崩溃恢复的能力。

MySQL的查询策略

选取-投影-连接
如：SELECT id,name FROM student WHERE gender = ‘女’;
这个SELECT查询先根据WHERE语句进行 选取 ，而不是将表全部查询出来以后再进行gender过滤。 这个SELECT查询先根据id和name进行属性 投影 ，而不是将属性全部取出以后再进行过滤，将这两个查询条件 连接 起来生成最终查询结果。
MySQL的三层架构
连接层：客户端和服务器端建立连接，客户端发送 SQL 至服务器端；
SQL 层（服务层）：对 SQL 语句进行查询处理；与数据库文件的存储方式无关；
存储引擎层：与数据库文件打交道，负责数据的存储和读取。
MySQL查询流程