面试八股文 - 数据库

此文章主要参考了 小林coding,此处主要是针对面试的一些总结。

基础-执行一条 select 语句发生的步骤

MySQL 执行流程

可以看到， MySQL 的架构共分为两层：Server 层和存储引擎层，

• Server 层负责建立连接、分析和执行 SQL。MySQL大多数的核心功能模块都在这实现，主要包括连接器，查询缓存、解析器、预处理器、优化器、执行器等。另外，所有的内置函数（如日期、时间、数学和加密函数等）和所有跨存储引擎的功能（如存储过程、触发器、视图等。）都在Server 层实现。
• 存储引擎层负责数据的存储和提取。支持 InnoDB、MyISAM、Memory 等多个存储引擎，不同的存储引擎共用一个 Server层。现在最常用的存储引擎是 InnoDB，从 MySQL 5.5 版本开始， InnoDB 成为了 MySQL的默认存储引擎。我们常说的索引数据结构，就是由存储引擎层实现的，不同的存储引擎支持的索引类型也不相同，比如 InnoDB 支持索引类型是B+树 ，且是默认使用，也就是说在数据表中创建的主键索引和二级索引默认使用的是 B+ 树索引。

事务的特性：原子性、一致性、隔离性、持久性。

原子性（Atomicity）：事务作为一个整体被执行，包含在其中的对数据库的操作要么全部被执行，要么都不执行。

一致性（Consistency）：事务应确保数据库的状态从一个一致状态转变为另一个一致状态。一致状态的含义是数据库中的数据应满足完整性约束。

隔离性（Isolation）：多个事务并发执行时，一个事务的执行不应影响其他事务的执行。

持久性（Durability）：已被提交的事务对数据库的修改应该永久保存在数据库中。

联合主键：设置多个字段同时为主键（PRIMARY KEY(Name, Age)）

复合主键：多个主键联合形成一个主键组合。（成绩表中的学号、课程标号）

数据库查询10-20行内容：select * from stu limit 10, 10;

查找135开头的电话：select * from table where tel like ‘135%’;

left join, right join和inner join的影响性能的因素。

主键和外键

主键和外键的区别：

1. 主键是能确定一条记录的唯一标识，比如，一条记录包括身份正号，姓名，年龄。
   身份证号是唯一能确定你这个人的，其他都可能有重复，所以，身份证号是主键。
2. 外键用于与另一张表的关联。是能确定另一张表记录的字段，用于保持数据的一致性。

主键查询

二级索引查询

会先检二级索引中的 B+Tree 的索引值（商品编码，product_no），找到对应的叶子节点，然后获取主键值，然后再通过主键索引中的 B+Tree 树查询到对应的叶子节点，然后获取整行数据。这个过程叫「回表」，也就是说要查两个 B+Tree 才能查到数据。

为什么mysql用主键去查询会很快？

主键查询只用查询一次b+树，用二级索引会查两次b+树

为什么 MySQL InnoDB 选择 B+tree 作为索引的数据结构？

前面已经讲了 B+Tree 的索引原理，现在就来回答一下 B+Tree 相比于 B 树、二叉树或 Hash 索引结构的优势在哪儿？

1、B+Tree vs B Tree

B+Tree 只在叶子节点存储数据，而 B 树 的非叶子节点也要存储数据，所以 B+Tree 的单个节点的数据量更小，在相同的磁盘 I/O 次数下，就能查询更多的节点。

另外，B+Tree 叶子节点采用的是双链表连接，适合 MySQL 中常见的基于范围的顺序查找，而 B 树无法做到这一点。

2、B+Tree vs 二叉树

对于有 N 个叶子节点的 B+Tree，其搜索复杂度为O(logdN)，其中 d 表示节点允许的最大子节点个数为 d 个。

在实际的应用当中， d 值是大于100的，这样就保证了，即使数据达到千万级别时，B+Tree 的高度依然维持在 3~4 层左右，也就是说一次数据查询操作只需要做 3~4 次的磁盘 I/O 操作就能查询到目标数据。

而二叉树的每个父节点的儿子节点个数只能是 2 个，意味着其搜索复杂度为 O(logN)，这已经比 B+Tree 高出不少，因此二叉树检索到目标数据所经历的磁盘 I/O 次数要更多。

3、B+Tree vs Hash

Hash 在做等值查询的时候效率贼快，搜索复杂度为 O(1)。

但是 Hash 表不适合做范围查询，它更适合做等值的查询，这也是 B+Tree 索引要比 Hash 表索引有着更广泛的适用场景的原因。

什么是联合索引？

通过将多个字段组合成一个索引，该索引就被称为联合索引。

redis

redis定义

Redis 是一种基于内存的数据库，对数据的读写操作都是在内存中完成，因此读写速度非常快，常用于缓存，消息队列、分布式锁等场景。

简述redis事务

1. 定义：redis事务是一个单独的隔离操作：事务中的所有命令都会序列化、按顺序地执行。事务在执行的过程中，不会被其他客户端发送来的命令请求所打断。
2. Redis 事务中有 Multi、Exec 和 discard 三个指令，在 Redis 中，从输入 Multi
   命令开始，输入的命令都会依次进入命令队列中，但不会执行，直到输入 Exec 后，Redis
   会将之前的命令队列中的命令依次执行。而组队的过程中可以通过 discard 来放弃组队。
3. redis 不支持事务回滚机制，但是它会检查每一个事务中的命令是否错误。

redis事务的乐观锁和悲观锁是怎样的？

①悲观锁：每次去拿数据的时候都认为别人会修改，所以每次在拿数据的时候都会上锁，这样别人想拿这个数据就会被阻塞，直到它拿到锁。
②乐观锁：每次去拿数据的时候都认为别人不会修改，所以不会上锁，但是在更新的时候会判断一下在此期间别人有没有去更新这个数据，可以使用版本号等机制。乐观锁适用于多读的应用类型，这样可以提高吞吐量

redis的持久化操作RDB

**定义：**在指定时间间隔内，将内存中的数据集快照写入磁盘中，其实际工作过程是，先创建一个子进程，将数据集写入一个临时文件，写入成功后，再替换掉之前的文件，用二进制压缩存储。

优点：
1、方便持久化和备份
2、RDB创建子进程完成写操作，而主进程继续处理命令，IO最大化、保证redis的高性能
3、当数据集较大时，RDB的骑当效率比AOF更高

缺点：
1、数据安全性低，因为RDB是隔一段时间才进行持久化，如果在这个间隔期间发生了故障，那么这期间的数据将会丢失。
2、当数据集较大时，可能会导致服务器停止服务几百毫秒

redis的持久化操作AOF

**①定义：**用日志的形式记录服务器所处理的每一个写、删除操作，查询操作不记录，用文本的方式进行记录，可以打开文件看到详细的操作记录。在重新启动redis时，redis会根据日志文件的内容将指令从前到后执行一次。

②优点：
1、AOF有三种同步策略，数据安全性比RDB高。
2、AOF通过append模式写文件，即使中途服务器宕机也不会破坏已存在的内容
3、AOF有rewrite模式，定期对AOF文件进行重写，来达到压缩的目的

③缺点：
AOF文件比RDB文件大，且恢复速度慢，运行效率比RDB低，不适用于数据集大的时候
若AOF和RDB同时开启，系统会默认取AOF中的数据

redis的过期键删除策略

我们可以设置redis中key的过期时间，若不设置，默认永不过期。Redis的过期策略就是当key过期时，redis如何处理。
①惰性过期：只有当访问了一个key时，才会判断该key是否有过期，过期则清除。该策略可以最大化节省CPU资源，但可能存在大量过期的key，未被再次访问，无法清除，而占用内存。
②定期过期：每隔一定时间，会扫描expires字典中一定数量的key，若过期则清除。该策略可以使cpu和内存资源达到平衡的效果
【redis中expires字典会存储所有设置了过期时间的key】
Redis中同时使用了惰性过期和定期过期两种过期策略

Redis为什么快？

①redis是基于内存存储实现的数据库，相比于数据存在磁盘的mysql，可以省去磁盘io的消耗。
②redis支持多种数据类型和数据结构，合理的数据结构使得程序执行速度快
③实现了I/O多路复用：一个线程可以监视多个文件句柄；一旦某个文件句柄就绪，就能够通知应用程序进行相应的读写操作；而没有文件句柄就绪时,就会阻塞应用程序，交出cpu
【文件句柄在文件I/O中，要从一个文件读取数据，应用程序首先要调用操作系统函数并传送文件名，并选一个到该文件的路径来打开文件。该函数取回一个顺序号，即文件句柄】
④redis实单线程模型，避免了CPU不必要的上下文切换和竞争锁的消耗。
⑤redis构建了虚拟内存机制，即把冷数据从内存交换到磁盘中，腾出内存空间用于热数据，节约系统调用时间

Redis 数据类型以及使用场景分别是什么？

String 类型的应用场景：缓存对象、常规计数、分布式锁、共享 session 信息等。
List 类型的应用场景：消息队列（但是有两个问题：1. 生产者需要自行实现全局唯一 ID；2. 不能以消费组形式消费数据）等。
Hash 类型：缓存对象、购物车等。
Set 类型：聚合计算（并集、交集、差集）场景，比如点赞、共同关注、抽奖活动等。
Zset 类型：排序场景，比如排行榜、电话和姓名排序等。
BitMap（2.2 版新增）：二值状态统计的场景，比如签到、判断用户登陆状态、连续签到用户总数等；
HyperLogLog（2.8 版新增）：海量数据基数统计的场景，比如百万级网页 UV 计数等；
GEO（3.2 版新增）：存储地理位置信息的场景，比如滴滴叫车；
Stream（5.0 版新增）：消息队列，相比于基于 List 类型实现的消息队列，有这两个特有的特性：自动生成全局唯一消息ID，支持以消费组形式消费数据。

穿透

在Redis缓存和数据库中都找不到相关的数据。也就是说这是个非法的查询，假设客户端发出了大量非法的查询 比如id是负的 ，每次这个查询都需要去Redis和数据库中查询，可能会导致数据库崩溃
解决缓存穿透的要点，不让非法数据到数据库中查询
解决方案：
①过滤非法查询：在后台服务中过滤非法查询，直接不让他落到Redis服务上。比如id<=0就直接断言。
②缓存空对象：如果他的查询数据是合法的，但是确实Redis和MySql中都没有，那么我们就在Redis中储存一个空对象，这样下次客户端继续查询的时候就能在Redis中返回了。但是，如果客户端一直发送这种恶意查询，就会导致Redis中有很多这种空对象，浪费很多空间
③采用布隆过滤器：它实际上是一个很长的二进制向量 (位图) 和一系列随机映射函数（哈希函数）。布隆过滤器可以用于检索一个元素是否在一个集合中。它的优点是空间效率和查询时间都远远超过一般的算法，缺点是有一定的误识别率和删除困难

击穿

缓存击穿和缓存雪崩类似，也是因为Redis中key过期导致的。只不过缓存击穿是某一个热点的key过期导致的。当有一个热点数据突然过期时，就会导致突然有大量的请求直接落到数据库上，导致数据库崩溃。
解决缓存击穿的要点，不让热点key突然过期
解决方案：
①预先设置热门数据：在 redis 高峰访问之前，把一些热门数据提前存入到 redis 里面，加大这些热门数据 key 的时长。
②实时调整：现场监控哪些数据热门，实时调整 key 的过期时长。
③使用redis分布式锁：让一时间只有一个相同请求落到MySql上，反正都是查询同一个信息，之后的其他请求就可以去Redis中找了。

雪崩

Redis中的缓存数据是有过期时间的，当在同一时间大量的缓存同时失效时，由于大量请求无法在redis缓存中处理，就会发送到数据库，导致数据库压力激增，可能会导致数据库崩溃，从而导致整个系统崩溃。
解决缓存雪崩的关键：避免Redis的缓存在短时间内大量的过期
解决方案：
①根据业务需要来合理的设置过期的时间
②使用redis分布式锁：让一时间只有一个相同请求落到MySql上，反正都是查询同一个信息，之后的其他请求就可以去Redis中找了。

③使用锁或队列：用加锁或者队列的方式来保证不会有大量的线程对数据库一次性进行读写，从而避免失效时大量的并发请求落到底层存储系统上，该方法不适用高并发情况。

#代码题

组合两个表

表: Person

±------------±--------+
| 列名 | 类型 |
±------------±--------+
| PersonId | int |
| FirstName | varchar |
| LastName | varchar |
±------------±--------+
personId 是该表的主键（具有唯一值的列）。
该表包含一些人的 ID 和他们的姓和名的信息。

表: Address

±------------±--------+
| 列名 | 类型 |
±------------±--------+
| AddressId | int |
| PersonId | int |
| City | varchar |
| State | varchar |
±------------±--------+
addressId 是该表的主键（具有唯一值的列）。
该表的每一行都包含一个 ID = PersonId 的人的城市和州的信息。

编写解决方案，报告 Person 表中每个人的姓、名、城市和州。如果 personId 的地址不在 Address 表中，则报告为 null 。

以 任意顺序 返回结果表。

结果格式如下所示。

示例 1:

输入:
Person表:
±---------±---------±----------+
| personId | lastName | firstName |
±---------±---------±----------+
| 1 | Wang | Allen |
| 2 | Alice | Bob |
±---------±---------±----------+
Address表:
±----------±---------±--------------±-----------+
| addressId | personId | city | state |
±----------±---------±--------------±-----------+
| 1 | 2 | New York City | New York |
| 2 | 3 | Leetcode | California |
±----------±---------±--------------±-----------+
输出:
±----------±---------±--------------±---------+
| firstName | lastName | city | state |
±----------±---------±--------------±---------+
| Allen | Wang | Null | Null |
| Bob | Alice | New York City | New York |
±----------±---------±--------------±---------+
解释:
地址表中没有 personId = 1 的地址，所以它们的城市和州返回 null。
addressId = 1 包含了 personId = 2 的地址信息

select FirstName, LastName, City, State
from Person left join Address
on Person.PersonId = Address.PersonId;

第二高的薪水

Employee 表：
±------------±-----+
| Column Name | Type |
±------------±-----+
| id | int |
| salary | int |
±------------±-----+
在 SQL 中，id 是这个表的主键。
表的每一行包含员工的工资信息。

查询并返回 Employee 表中第二高的薪水 。如果不存在第二高的薪水，查询应该返回 null(Pandas 则返回 None) 。

查询结果如下例所示。

示例 1：

输入：
Employee 表：
±—±-------+
| id | salary |
±—±-------+
| 1 | 100 |
| 2 | 200 |
| 3 | 300 |
±—±-------+
输出：
±--------------------+
| SecondHighestSalary |
±--------------------+
| 200 |
±--------------------+
示例 2：

输入：
Employee 表：
±—±-------+
| id | salary |
±—±-------+
| 1 | 100 |
±—±-------+
输出：
±--------------------+
| SecondHighestSalary |
±--------------------+
| null |
±--------------------+

分数排名

表: Scores

±------------±--------+
| Column Name | Type |
±------------±--------+
| id | int |
| score | decimal |
±------------±--------+
在 SQL 中，id 是该表的主键。
该表的每一行都包含了一场比赛的分数。Score 是一个有两位小数点的浮点值。

查询并对分数进行排序。排名按以下规则计算:

分数应按从高到低排列。
如果两个分数相等，那么两个分数的排名应该相同。
在排名相同的分数后，排名数应该是下一个连续的整数。换句话说，排名之间不应该有空缺的数字。
按 score 降序返回结果表。

查询结果格式如下所示。

示例 1:

输入:
Scores 表:
±—±------+
| id | score |
±—±------+
| 1 | 3.50 |
| 2 | 3.65 |
| 3 | 4.00 |
| 4 | 3.85 |
| 5 | 4.00 |
| 6 | 3.65 |
±—±------+
输出:
±------±-----+
| score | rank |
±------±-----+
| 4.00 | 1 |
| 4.00 | 1 |
| 3.85 | 2 |
| 3.65 | 3 |
| 3.65 | 3 |
| 3.50 | 4 |
±------±-----+

SELECT
S1.score,
(
SELECT
COUNT(DISTINCT S2.score)
FROM
Scores S2
WHERE
S2.score >= S1.score
) AS ‘rank’
FROM
Scores S1
ORDER BY
S1.score DESC;