数据库面试总结

• 关系数据库设计理论

1. 异常

以下的学生课程关系的函数依赖为 {Sno, Cname} -> {Sname, Sdept, Mname, Grade}，键码为 {Sno, Cname}。也就是说，确定学生和课程之后，就能确定其它信息。

1. 冗余数据：例如 学生-2 出现了两次。
2. 修改异常：修改了一个记录中的信息，但是另一个记录中相同的信息却没有被修改。
3. 删除异常：删除一个信息，那么也会丢失其它信息。例如删除了 课程-1 需要删除第一行和第三行，那么 学生-1 的信息就会丢失。
4. 插入异常：例如想要插入一个学生的信息，如果这个学生还没选课，那么就无法插入

2. 范式

范式理论是为了解决以上提到四种异常。

高级别范式的依赖于低级别的范式，1NF 是最低级别的范式。

1. 第一范式：属性不可分

2. 第二范式：每个非主属性完全依赖于键码(可以通过分解满足)

以上学生课程关系中，{Sno, Cname} 为键码，有如下函数依赖：
Sno -> Sname, Sdept
Sdept -> Mname
Sno, Cname-> Grade

Grade 完全函数依赖于键码，它没有任何冗余数据，每个学生的每门课都有特定的成绩。

Sname, Sdept 和 Mname 都部分依赖于键码，当一个学生选修了多门课时，这些数据就会出现多次，造成大量冗余数据。

3. 第三范式：非主属性不传递函数依赖于键码

• 事务

事务指的是满足ACID特性的一组操作，可以通过commit提交一个事务，也可以使用rollback进行回滚.

• ACID

1. 原子性： 事务被视为不可分割的最小单元，事务的所有操作要么全部提交成功，要么全部失败回滚
2. 一致性：数据库在事务执行前后保持一致性状态。在一致性状态下，所有事务对一个数据的读取结果都是相同的（表示事务完成后，符合逻辑运算）
3. 隔离性：一个事物所做的修改在最终提交以前，对其他事务是不可见的
4. 持久性：一旦事务提交，则其所做的修改将会永久保存到数据库中.即使系统发横崩溃，事务执行的结果也不会丢失.

注意：这几个特性不是一种平级关系

1. 只有满足一致性，事务的执行结果才是正确的
2. 在无并发的情况下，事务串行执行，隔离性一定能够满足.此时只要能满足原子性，就一定满足一致性.
3. 在并发的情况下，多个事务并行执行，事务不仅要满足原子性，还需要满足隔离性，才能满足一致性
4. 事务满足持久性是为了能应对数据库崩溃的情况
   

• 并发一致性问题

在并发环境下，事务的隔离性很难保证，因此会出现很多并发一致性问题

1. 丢失修改：T1 和 T2 两个事务都对一个数据进行修改，T1 先修改，T2 随后修改，T2 的修改覆盖了 T1 的修改
   
2. 读脏数据：T1 修改一个数据，T2 随后读取这个数据。如果 T1 撤销了这次修改，那么 T2 读取的数据是脏数据。
   
3. 不可重复度：T2 读取一个数据，T1 对该数据做了修改。如果 T2 再次读取这个数据，此时读取的结果和第一次读取的结果不同。
   
4. 幻影读：T1 读取某个范围的数据，T2 在这个范围内插入新的数据，T1 再次读取这个范围的数据，此时读取的结果和和第一次读取的结果不同。
   
   产生并发不一致性问题主要原因是破坏了事务的隔离性，解决方法是通过并发控制来保证隔离性。并发控制可以通过封锁来实现，但是封锁操作需要用户自己控制，相当复杂。数据库管理系统提供了事务的隔离级别，让用户以一种更轻松的方式处理并发一致性问题。

• 事务的隔离级别

1. 未提交读：事务中的修改，即使灭有提交，对其他事务也是可见的
2. 提交读：一个事物只能读取已经提交的事务所做的修改。换句话说，一个事务所作的修改在提交之前对其他事务是不可见的
3. 可重复度：保证在同一个事务中多次读取同样数据的结果是一样的
4. 可串行化：强制事务串行执行

低级别隔离支持更高并发处理

• 封锁

1. 封锁粒

Mysql中提供了两种封锁粒度：行级锁以及表级锁
应该尽量只锁定需要修改的那部分数据，而不是所有的资源。锁定的数据量越少，发生锁争用的可能就越小，系统的并发程度就越高

但是加锁需要消耗资源，锁的各种操作(获取锁 释放锁 检查锁状态)都会增加系统开销.因此封锁粒度越小，系统开销就越大

在选择封锁粒度时，需要在锁开销和并发程度之间做一个权衡

2. 封锁类型
   1. 排他锁：其他事务对该资源不可读不可改，独占资源
   2. 共享锁：对同一个资源共享同一个锁，允许多并发事务进行读操作.
   3. 悲观锁：读数据会上锁，别人无法读
   4. 乐观锁：读数据不会上锁，但更新时会判断有无在此期间修改
   5. 行锁：锁定整个行数据
   6. 表锁：锁定整个表数据

• 多版本并发控制

多版本并发控制（Multi-Version Concurrency Control, MVCC）是 MySQL 的 InnoDB 存储引擎实现隔离级别的一种具体方式，用于实现提交读和可重复读这两种隔离级别。而未提交读隔离级别总是读取最新的数据行，无需使用 MVCC。可串行化隔离级别需要对所有读取的行都加锁，单纯使用 MVCC 无法实现。

版本号

系统版本号：是一个递增的数字，每开始一个新的事务，系统版本号就会自动递增。

事务版本号：事务开始时的系统版本号。

隐藏的列


MVCC 在每行记录后面都保存着两个隐藏的列，用来存储两个版本号：

创建版本号：指示创建一个数据行的快照时的系统版本号；

删除版本号：如果该快照的删除版本号大于当前事务版本号表示该快照有效，否则表示该快照已经被删除了。

• 关系型数据库和非关系型数据库

关系性 优点：易于理解，二维表结构使用方便，SQL语言操作方便，易于维护，可用于复杂查询，支持事务

缺点： 读写性能差，表结构固定，不支持高并发读写需求

非关系型 优点：读写性能高 基于键值对存储，容易扩展

缺点：不支持事务 butigongSQL支持

• 存储引擎

1. MYISAM:不支持外键 索引和数据是分开的，只支持表级锁，无事务->适用于数据量小 读多写少 并发不高
2. INNODB(默认):支持外键，数据本身就是索引文件，还支持行级锁，支持事务的

事务：InnoDB 是事务型的，可以使用 Commit 和 Rollback 语句。

并发：MyISAM 只支持表级锁，而 InnoDB 还支持行级锁。

外键：InnoDB 支持外键。

备份：InnoDB 支持在线热备份。

崩溃恢复：MyISAM 崩溃后发生损坏的概率比 InnoDB 高很多，而且恢复的速度也更慢。

其它特性：MyISAM 支持压缩表和空间数据索引。

• 索引(离散度越高越适用 查找 排序 分组多的合适)

索引是在存储引擎层实现的，而不是在服务器层实现的，所以不同存储引擎具有不同的索引类型和实现。
索引是

什么是索引？

索引是对数据库表中一列或多列的值进行排序的一种结构，

索引就是数据结构！
进一步说该数据结构中存储了这张表中某一列的所有值，就是说索引是基于数据表中的某一列创建的。再啰嗦一遍：一个索引是由表中某一列上的数据组成，并且这些数据存储在某个数据结构中。最后一遍：索引就是数据结构

B+Tree原理

B Tree 指的是 Balance Tree，也就是平衡树。平衡树是一颗查找树，并且所有叶子节点位于同一层

B+ Tree 是基于 B Tree 和叶子节点顺序访问指针进行实现，它具有 B Tree 的平衡性，并且通过顺序访问指针来提高区间查询的性能。一个节点中的 key 从左到右非递减排列，如果某个指针的左右相邻 key 分别是 keyi 和 keyi+1，且不为 null，则该指针指向节点的所有 key 大于等于 keyi 且小于等于 keyi+1。

使用B+树 不用红黑树和B树

1. 红黑树增加|删除会频繁调正，保证红黑树特性
2. B树 查询性能不稳定 每个节点都保存数据，树很高
3. B+树：
   1. 中间节点不保存数据只用来索引，数据都保存在叶子节点
   2. 叶子节点包含全部元素和指针，且子小而大顺序排列
   3. 所有中间节点元素都同时存储在叶子节点中

Mysql索引

1. B+Tree索引：大多数Mysql存储引擎的默认索引类型
2. 哈希索引:能以O(1)时间进行查找
   1. 无法用于排序与分组；
   2. 只支持精确查找，无法用于部分查找和范围查找。
3. 全文索引:用于查找文本中的关键词，而不是直接比较是否相等。

索引优点

1. 减少了服务器需要扫描的数据行数
2. 帮助服务器避免进行排序和分组
3. 将随机I/O->顺序I/O (B+树索引有序，相邻的数据都存储在一起)

索引优化

1. 独立的列：在进行查询时，索引列不能是表达式的一部分，也不能是函数的参数，否则无法使用索引
2. 多列索引：在需要使用多个列作为条件进行查询时，使用多列索引比使用多个单列索引性能更好。例如下面的语句中，最好把 actor_id 和 film_id 设置为多列索引。

SELECT film_id, actor_ id FROM sakila.film_actor
WHERE actor_id = 1 AND film_id = 1;

3. 索引列的顺序：让选择性最强的索引列放在前面

SELECT COUNT(DISTINCT staff_id)/COUNT(*) AS staff_id_selectivity,
COUNT(DISTINCT customer_id)/COUNT(*) AS customer_id_selectivity,
COUNT(*)
FROM payment;

4. 前缀索引：对于 BLOB、TEXT 和 VARCHAR 类型的列，必须使用前缀索引，只索引开始的部分字符
5. 覆盖索引：索引包含所有需要查询的字段的值。

存储过程

一组为了完成特定功能的SQL 语句集，存储在数据库中，经过第一次编译后再次调用不需要再次编译，
用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。

优点如下：
1.执行效率高.
2.安全性能好。
3.对于一些场合非常容易实现需求。
缺点如下：
1.可维护性比较差。
2.可读性也差。

如何优化Mysql

1. sql语句及索引优化
   1. 选取适用属性 减少定义字段宽度
   2. 使用join代替子查询
   3. 锁定表 优化事务处理
   4. 事务处理
   5. 建立索引
2. 数据库表结构的优化
3. 系统配置的优化
4. 硬件的优化
   如何设计一个高并发？
5. 数据库优化(合理事务隔离级别 sql语句 索引优化)
6. 使用缓存
7. 分布式数据库 缓存
8. 负载均衡

• 主从复制

主要涉及三个线程：binlog 线程、I/O 线程和 SQL 线程。

binlog 线程 ：负责将主服务器上的数据更改写入二进制日志（Binary log）中。
I/O 线程 ：负责从主服务器上读取二进制日志，并写入从服务器的中继日志（Relay log）。
SQL 线程 ：负责读取中继日志，解析出主服务器已经执行的数据更改并在从服务器中重放（Replay）。

• 读写分离

主服务器处理写操作以及实时性要求比较高的读操作，而从服务器处理读操作。

读写分离能提高性能的原因在于：

主从服务器负责各自的读和写，极大程度缓解了锁的争用；
从服务器可以使用 MyISAM，提升查询性能以及节约系统开销；
增加冗余，提高可用性。

读写分离常用代理方式来实现，代理服务器接收应用层传来的读写请求，然后决定转发到哪个服务器。