数据库面试资料总结

数据库

事务

1. 事务的概念：可以看作一个单元或一系列SQL语句的集合
2. 事务的四大特性：原子性，一致性、隔离性、持久性
   1. 原子性：所有的操作要么都被执行，要么都不被执行
   2. 一致性：每个单独的事务，即不和其他事务发生并发的条件下，必须保证数据库的一致性
   3. 隔离性：事务都是独立的，它不受其它并发事务的影响
   4. 持久性：即使在事务存入磁盘之前发生故障，事务处理的结果也能被永久地保持住
3. 数据库事务常用的三条语句：
   1. transaction begin
   2. transaction commit
   3. rollback transaction

---

数据库的隔离级别

1. 脏读（读未提交）：事务B读取了事务A还没有提交的数据（WR冲突）

   事务B读取了事物A已经修改，但还没有提交的数据。

2. 不可重复读（读提交）：两次事务读取的数据不一致（RW冲突）

3. 幻读（可重复读）：事务A修改了数据，事务B也修改了数据（WW冲突）

4. 严格的两阶段加锁（2PL）:

   1. 如果事务T希望读某个数据，那么应该首先申请该对象的共享锁（如果已经有排他锁也可以读数据）。
   2. 事务结束时，所有的锁都会被释放掉。

---

数据库的范式

函数依赖：如果两个元组在属性X上相同，那么它们在属性Y上也相同。

1. 第一范式：数据库表中每一列都是不可再分的基本数据列，即同一列中不能有多个值。

   1. 每个字段只能存放单一值
   2. 每笔记录都能利用唯一的主键加以识别

2. 第二范式：第二范式建立在第一范式的基础上，非主属性完全依赖于码（消除部分依赖），如：

   $(Name,Subject)\to Department$，但是$Name\to Department$

3. 第三范式：如果对$R$上的每个函数依赖$X\to A$，下面的一个条件成立（消除传递依赖）：

   1. $A\in X$，即$X\to A$是平凡依赖
   2. $X$是超码
   3. $A$是$R$的码的一部分

4. BC-范式：对于每个关系模式$R$上成立的函数依赖$X\to A$，下面的条件中有一个成立

   1. $A\in X$是平凡依赖
   2. $X$是一个超码

---

MySQL

1. Explain语句能够定位执行效率差的SQL，同时知道SQL的执行计划（如全表扫描还是索引扫描）

   type： join的类型，避免全盘扫描（ALL）

   possible_key：可能用到的索引

   key：实际用到的索引

   rows：估计每次需要扫描的行数

2. profile用来分析sql的分布消耗情况

3. show status语句检查table_locks_waited（值较高则存在比较严重的表级锁争用情况）和table_locks_immediate来查看锁的使用情况。

4. MySQL在写时加锁实现序列化。

---

悲观锁和乐观锁

1. 悲观锁：每次修改数据的时候，都会担心别的线程会修改数据，所有每次在操作的时候都会给数据加锁。
2. 乐观锁：默认别的线程不会修改数据，所以不会上锁，每次在修改数据的时候，去判断别的程序在此期间有没有修改数据。
3. 在写操作较多的时候，使用悲观锁，在读操作较多的时候，使用乐观锁。
4. 排他锁：别的程序既不能修改，也不能读取数据。
5. 共享锁：别的程序不能修改数据，但是可以读取数据。

---

建索引的原则

1. 最左前缀匹配原则，如$a=1$,$b=1$,$c>1$,$d=1$，MySQL会一直向右检索，直到遇到范围查询，如$d=1$是用不到索引的。
2. =和in可以乱序，如$a=1,b=1,c=1$建立索引可以任意顺序，MySQL的查询优化器可以帮助优化为可查询形式。
3. 尽量选择区分度高的列作为索引，区分度的计算公式=列中不同元素/总元素。
4. 索引列不能参与计算，否则检索时所有元素都需要参与计算，成本很高。
5. 尽量扩展索引，不要新建索引。
6. 在经常使用where子句的列上建立索引，加快条件判断的效率。
7. 复合索引，一个索引里面包含多个字段，但是不一定每次检索都是用所有的索引

---

索引的优点

1. 通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。
2. 可以大大加快数据的检索速度。
3. 可以加快表和表之间的连接。

---

B树和B+树

1. B+树的非叶子结点不保存关键字数据指针，只进行数据索引，层数更浅，效率更高。
2. B+树保留了父节点所有关键字记录的指针，数据必须要到叶节点才能访问，检索过程稳定。
3. B+树全节点遍历更快。

---

MySQL的优化

1. MySQL语句优化

   1. 使用limit对查询结果进行优化
   2. 避免select *，将需要的字段列出来
   3. 使用连接来代替子查询
   4. 拆分大的delete或insert语句

2. 选择合适的数据类型

   1. 使用可存下数据的最小数据类型
   2. 使用简单的数据类型
   3. 使用合理的字段长度
   4. 尽可能使用NOT NULL定义字段
   5. 尽量少用text

3. 选择合适的索引列

   1. 查询频繁的列，在where，group by，order by， on从句中出现的列
   2. 长度小的列
   3. 离散度大的列

4. SHOW [SESSION|GLOBAL] STATUS LIKE ‘%STATUS_NAME%’;

   1. com_select：查询次数
   2. connections：连接次数
   3. uptime：数据库运行时间
   4. handler_read：索引的使用情况

   显示系统变量：SHOW VARIABLES LIKE ‘%Variable_name%’;

   显示存储引擎状态：SHOW ENGINE INNODB STATUS；

   EXPLAIN ：

   1. type：连接状况
   2. possible_keys：可能用到的索引
   3. key：实际使用的索引

   PROFILE：

   1. SET PROFILING=ON;
   2. SHOW profiles; 查看SQL语句的运行时间
   3. SHOW processlist; 查看所有用户的当前连接
   4. PROCEDURE ANALYSE() 对现有表给出优化建议
   5. OPTIMIZE TABLE table_name; 回收闲置的数据库空间
   6. REPAIR TABLE table_name; 修复被破坏的表
   7. CHECK TABLE table_name; 检查表是否有错误

---

MySQL引擎

MyISAM	InnoDB
不支持事务	支持事务
不支持外健	支持外健
用变量保存全表的行数，不需要扫描全表	不保存具体的行数，需要扫描全表
支持表锁	支持表锁和行锁
支持全文索引，查询效率较高	不支持全文索引

扫描全表行数的操作：

select count(*) from table

表锁，每次操作都要锁住整张表，不会发生死锁，但并发性差；

行锁，每次操作锁住一行，有发生死锁的可能，但是发生锁冲突的概率低，并发性好。

InnoDB基于索引来执行行锁，否则执行表锁

---

MySQL 练习

1. 编写一个 SQL 查询，满足条件：无论 person 是否有地址信息，都需要基于上述两表提供 person 的以下信息：

   SELECT p.FirstName, p.LastName, addr.City, addr.State
   FROM Person p LEFT JOIN Address addr
   ON p.PersonId = addr.PersonId
   

   LEFT JOIN 返回左列中所有记录和右列中连结记录，即右列中可能会有NULL

2. 编写一个 SQL 查询，获取 Employee 表中第二高的薪水（Salary） 。

   SELECT MAX(E.Salary) as SecondHighestSalary
   FROM Employee E
   WHERE E.Salary < (SELECT MAX(EM.Salary) FROM Employee EM)
   

3. 给定 Employee 表，编写一个 SQL 查询，该查询可以获取收入超过他们经理的员工的姓名。在上面的表格中，Joe 是唯一一个收入超过他的经理的员工。

   SELECT p.Email AS Email
   FROM Person p
   GROUP BY p.Email
   HAVING COUNT(*) > 1
   

4. 某网站包含两个表，Customers 表和 Orders表。编写一个 SQL 查询，找出所有从不订购任何东西的客户。

   SELECT c.Name as Customers
   FROM Customers c
   WHERE c.Id not in (SELECT O.CustomerId FROM Orders O, Customers CC WHERE O.CustomerId = CC.Id)
   

5. 编写一个 SQL 查询，来删除 Person 表中所有重复的电子邮箱，重复的邮箱里只保留 Id 最小 的那个。

   DELETE P1 
   FROM Person P1, Person P2
   WHERE P1.Email = P2.Email and P1.Id > P2.Id
   

6. 给定一个 Weather 表，编写一个 SQL 查询，来查找与之前（昨天的）日期相比温度更高的所有日期的 Id。

   SELECT W1.Id
   FROM Weather AS W1, Weather AS W2  
   WHERE DATEDIFF(W1.RecordDate, W2.RecordDate) = 1 AND W1.Temperature > W2.Temperature
   

7. 有一个courses 表 ，有: student (学生) 和 class (课程)。

   请列出所有超过或等于5名学生的课。

   SELECT C.class
   FROM Courses C
   GROUP BY C.class
   HAVING COUNT(DISTINCT(C.student)) >= 5
   

8. 给定一个 salary 表，如下所示，有 m = 男性 和 f = 女性 的值。交换所有的 f 和 m 值（例如，将所有 f 值更改为 m，反之亦然）。要求只使用一个更新（Update）语句，并且没有中间的临时表。

   UPDATE Salary S SET S.sex = (
       CASE 
           WHEN S.sex = 'm' THEN 'f' 
           WHEN S.sex = 'f' THEN 'm' 
       END)
   

---

如何选择引擎

1. 当需要支持事务的时候，选择InnoDB
2. 如果表中大部分都是只读查询，选择MyISAM，如果表中有频繁的读写操作，选择InnoDB.
3. 系统崩溃以后，MyISAM的恢复比较困难，看接收程度。

---