数据库常见面试题

存储引擎

InnoDB（默认）

存储引擎的对比

MYISAM被MangoDB替代了
MEMORY被Redis替代了

索引

是一种高效获取数据的数据结构

索引结构

二叉树，红黑树（都不合适）

B树

插入超过5个数，会从中间分裂

B+树

所有的元素都会出现在叶子节点，形成单向链表
非叶子节点只是起到索引数据的作用

mysql中的B+树

哈希索引

总结

索引分类

回表查询：先走二级索引拿到主键id值，然后再拿这一行的数据

思考题

索引创建

B+tree中主键和指针分别占多少空间

在B+树中，主键通常存储在叶子节点中，而非叶子节点只存储索引信息。这是B+树的一个特性
取决主键的数据类型吧，指针取决系统是多少位吧

常见面试题

红黑树说一下（Red-Black Tree）

map和set的底层数据结构
是一种自平衡的二叉搜索树，它在插入和删除节点时通过一系列规则来保持树的平衡，从而保证了较好的搜索、插入和删除性能。
红黑树具有以下特性：

1. 节点颜色：每个节点被标记为红色或黑色。
2. 根节点：根节点是黑色的。
3. 叶子节点（NIL节点）：叶子节点是特殊的黑色节点，也被称为NIL节点。它们不存储任何实际数据，仅用作树的边界。
4. 节点颜色规则：
   • 每个红色节点的子节点都是黑色的。
   • 从根节点到叶子节点的每条路径上，黑色节点的数量相同。
5. 插入规则：当插入一个新节点时，根据规则进行调整，以确保红黑树的性质不被破坏。插入操作通常包括旋转和颜色变换。
6. 删除规则：当删除一个节点时，根据规则进行调整，以保持红黑树的平衡和性质。删除操作可能需要进行节点的旋转和颜色变换。
   红黑树的自平衡性质使得其在各种操作下具有良好的平均和最坏情况的时间复杂度。红黑树的查找、插入和删除操作的时间复杂度都是O(log n)，其中n是树中节点的数量。这使得红黑树在需要高效的搜索、插入和删除操作的场景中得到广泛应用，如数据库索引、集合实现等。

数据库索引为什么不用红黑树

B+树的有序性使得顺序遍历更加高效
B+树结构通常更符合关系数据库的索引结构
红黑树在某些场景中也有其优势，比如更平衡的性能特征，适用于动态插入和删除的场景

数据库三大范式

1. 第一范式（1NF）：
   • 第一范式要求每个关系中的每个属性都是原子的，即不可再分。
   • 这意味着每个属性不能包含多个值或多个值的集合。每个属性应该具有单一的数据类型。
   • 1NF的目标是消除重复的数据，并确保每个属性包含一个值。
2. 第二范式（2NF）：
   • 第二范式要求每个非主属性都完全依赖于关系的候选键（主键）。
   • 它要求关系中的每个属性都与整个候选键相关，而不仅仅与部分候选键相关。
   • 2NF的目标是消除部分依赖，确保数据不会因为部分候选键的改变而引起冗余。
3. 第三范式（3NF）：
   • 第三范式要求每个非主属性都不传递依赖于关系的候选键。
   • 这意味着关系中的每个属性应该直接依赖于候选键，而不是依赖于其他非主属性。
   • 3NF的目标是消除传递依赖，确保数据的一致性和避免冗余

表和表的join方式

inner join：返回两个表中具有相同键值的行
left join：返回左表的所有行，以及右表中的匹配行，没有匹配行，右表的列包含NULL值
right jojn：返回右表的所有行，以及左表中的匹配行，没有匹配行，左表的列包含NULL值

事务

一组SQL语句的执行单元，它要么完全执行，要么完全不执行

1. 原子性（Atomicity）： 不可分割，要么全部执行，要么全部不执行。如果事务中的任何一步操作失败，整个事务将被回滚（Rollback），即撤销之前的所有操作。
2. 一致性（Consistency）： 前后一致
3. **隔离性（Isolation）：**事务之间彼此隔离
4. 持久性（Durability）： 事务被提交，改变是永久性的，断电也在

BEGIN TRANSACTION;  -- 开始事务

-- 在这里执行一系列SQL语句，可能包括INSERT、UPDATE、DELETE等操作
IF (/* 检查是否满足提交条件 */) BEGIN
    COMMIT;  -- 提交事务
END ELSE BEGIN
    ROLLBACK;  -- 回滚事务
END;
1
2
3
4
5
6
7
8

触发器

数据库触发器（Database Trigger）是一种特殊的存储过程，它在数据库管理系统中自动执行或触发的一系列操作。触发器通常与特定的数据库表相关联，它们在表上的某些事件（例如插入、更新、删除）发生时被自动触发执行。数据库触发器（Database Trigger）是一种特殊的存储过程，它在数据库管理系统中自动执行或触发的一系列操作。触发器通常与特定的数据库表相关联，它们在表上的某些事件（例如插入、更新、删除）发生时被自动触发执行。

数据库触发器的主要目的是在特定的数据库操作发生时，自动执行一些额外的操作或逻辑，以确保数据的完整性、一致性或应用特定的业务规则。触发器通常用于以下几个方面：

1. 数据完整性： 触发器可以用于在插入、更新或删除数据之前或之后执行检查，确保数据满足预定的完整性约束。
2. 日志记录： 触发器可以用于记录特定表上的操作，以用于审计、监控或调试目的。
3. 派生数据的计算： 触发器可以用于在表上的某些事件发生时，计算和更新派生数据，以避免手动维护。
4. 业务规则的强制执行： 触发器可以用于执行特定的业务规则，以确保满足应用程序的需求。

数据库触发器通常有两种类型：

1. BEFORE触发器（BEFORE Trigger）： 在触发事件之前执行。通常用于在实际的数据修改发生之前进行检查或准备工作。
2. AFTER触发器（AFTER Trigger）： 在触发事件之后执行。通常用于在实际的数据修改发生之后执行其他操作，如日志记录。

下面是一个简单的示例，演示了一个触发器的使用。假设有一个名为 Orders 的表，我们想在插入新订单时，自动更新与该订单相关的客户的最后订单日期：

CREATE TRIGGER UpdateLastOrderDate
ON Orders
AFTER INSERT
AS
BEGIN
    UPDATE Customers
    SET LastOrderDate = GETDATE()
    WHERE Customers.CustomerID IN (SELECT CustomerID FROM INSERTED);
END;
1
2
3
4
5
6
7
8
9

在这个例子中，当在 Orders 表中插入新数据时，触发器 UpdateLastOrderDate 会在插入后自动执行，并更新与插入订单相关的客户的最后订单日期。这是一个简单的示例，实际上触发器可以包含更复杂的逻辑。
数据库触发器（Database Trigger）是一种特殊的存储过程，它在数据库管理系统中自动执行或触发的一系列操作。触发器通常与特定的数据库表相关联，它们在表上的某些事件（例如插入、更新、删除）发生时被自动触发执行。

1. BEFORE触发器（BEFORE Trigger）： 在触发事件之前执行。通常用于在实际的数据修改发生之前进行检查或准备工作。
2. AFTER触发器（AFTER Trigger）： 在触发事件之后执行。通常用于在实际的数据修改发生之后执行其他操作，如日志记录。

索引：B+树说一下

Innodb 存储引擎的索引是 B+ 树结构


这条语句使用了主键索引查询 id 号为 5 的商品。查询过程是这样的，B+Tree 会自顶向下逐层进行查找：

• 将 5 与根节点的索引数据 (1，10，20) 比较，5 在 1 和 10 之间，所以根据 B+Tree的搜索逻辑，找到第二层的索引数据 (1，4，7)；
• 在第二层的索引数据 (1，4，7)中进行查找，因为 5 在 4 和 7 之间，所以找到第三层的索引数据（4，5，6）；
• 在叶子节点的索引数据（4，5，6）中进行查找，然后我们找到了索引值为 5 的行数据。

正是因为 B+ 树的索引是有序存放的，所以我们可以通过类似于二分查找算法快速找到对应的数据。

B+树具体实现？

答：B+树它是一种树形结构，在这棵树里它是能支持多个节点的（多叉树）。也就是相比于平衡二叉树来说，它一层能够存储的节点数量是更多的。B+树它比较特殊的地方是在于，除了叶子节点以外，它存的基本上都是路径信息， index 信息。只有在最后一层页节点才会存储具体的数据信息。

B+和红黑树，B树的区别

答：

• 红黑树，它其实也是二叉树的一种，它叶节点只有 red 和 black 两种类型。所以相比于B+数来说，它整个树的结构，深度高度是会更高的。对于查询来说，高度更高意味着就是查的效率会更低这样子。所以B+树会比红黑树更加的矮胖，它一层能存储的数据会更多一点。
• B树呢，也是个多叉树，但是在一些数据扫描的情况下， b 树可能会有一个回溯的过程，但是B+树就可以直接的通过遍历的方式来查找。

事务

mysql的事务是什么

在数据库中，事务（Transaction）是一组操作单元，这些操作单元要么全部执行成功，要么全部执行失败。事务是保证数据库一致性的重要机制之一，它可以将一系列的操作看作一个整体，从而保证数据库的完整性和正确性。
事务具有四个特性，即ACID：

• 原子性（Atomicity）：事务中的所有操作要么全部执行成功，要么全部执行失败，不会出现部分执行的情况。
• 一致性（Consistency）：事务执行前后数据库的状态是一致的，即数据库中的约束和规则都得到了保持。
• 隔离性（Isolation）：多个事务并发执行时，相互之间不会影响彼此的执行结果。
• 持久性（Durability）：事务执行完成后，对数据库所作的修改将被永久保存到数据库中。

MySQL是一种常见的关系型数据库，支持事务的机制。在MySQL中，事务可以
通过使用事务控制语句（Transaction Control Statements）来进行管理，包括以下三个语句：

• START TRANSACTION：开始一个事务。
• COMMIT：提交一个事务，使之生效。
• ROLLBACK：回滚一个事务，使之失效。

在MySQL中，事务默认是关闭的，需要通过设置autocommit参数为0来启用事务。启用事务后，可以通过执行SQL语句来进行事务操作

什么场景使用

有多个操作，并且想要这个操作是原子性，要不这多个操作一起成功，要不就一起失败。这样的场景会需要事务。

我们在转账操作前先开启事务，等所有数据库操作执行完成后，才提交事务，对于已经提交的事务来说，该事务对数据库所做的修改将永久生效，如果中途发生中断或错误，那么该事务期间对数据库所做的修改将会被回滚到没执行该事务之前的状态。

假如我们现在对已经插入了一条数据，我们接下来去修正另外一条数据。被查入的这条数据在这个时候能够被查询到吗？

要看数据库的隔离级别，如果它的隔离级别比较高，这个事务没有完成之前，他对数据的修改，另一个事务是没有办法感知到的。但是如果他事务的隔离级别比较低，是可以能够查到的。

一般有哪些隔离级别

读者答：大概有 4 种隔离级别读未提交，读已提交，可重复读和串行化情况。
补充：
SQL 标准提出了四种隔离级别来规避这些现象，隔离级别越高，性能效率就越低，这四个隔离级别如下：

• 读未提交（read uncommitted），指一个事务还没提交时，它做的变更就能被其他事务看到；
• 读提交（read committed），指一个事务提交之后，它做的变更才能被其他事务看到；
• 可重复读（repeatable read），指一个事务执行过程中看到的数据，一直跟这个事务启动时看到的数据是一致的，MySQL InnoDB 引擎的默认隔离级别；
• 串行化（serializable ）；会对记录加上读写锁，在多个事务对这条记录进行读写操作时，如果发生了读写冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续执行；
• 

视图说一下

含义：虚拟表，和普通表一样使用
行和列的数据来自自定义视图查询中使用的表，通过表动态生成的数据
只保存sql逻辑，不保存查询结果
场景：
1）多个地方用到同样的查询结果
2）该查询结果使用的sql语句较复杂

视图和表的对比：
创建语法的关键字 是否实际占用物理空间 使用
视图 create view 只是保存了sql逻辑 增删改查，一般不能增删改
表 create table 保存了数据 增删改查

存储过程和函数

:::info
含义：一组预先编译好的sql语句的集合，理解成批处理语句
1、提高代码的重用性
2、简化操作
3、减少了编译次数并且减少了和数据库服务器的连接次数，提高了效率
:::

存储过程

一、创建语法

CREATE PROCEDURE 存储过程名(参数列表)
BEGIN
       存储过程体(一组合法的SQL语句)
END
1
2
3
4

注意：
1、参数列表包含三部分
参数模式 参数名 参数类型
举例：IN stuname VARCHAR(20)

参数模式

IN:该参数可以作为输入,也就是该参数需要调用方传入值
OUT:该参数可以作为输出,也就是该参数可以作为返回值
INOUT:该参数即可以作为输入又可以作为输出，也就是该参数既需要传入值，又可以返回值

2、如果存储过程体仅仅只有一句话,BEGIN END 可以省略
存储过程体中的每条SQL语句的结尾要求必须加分号
存储过程的结尾可以使用delimiter重新设置
语法：
DELIMITER 结束标记
案例：DELIMITER $

二、调用语法

CALL 存储过程名(实参列表);

函数

区别：
存储过程:可以有0个返回，也可以有多个返回，适合做批量插入，批量更新
函数:有且仅有1个返回，适合做处理数据后返回一个结果

一、创建语法

CREATE FUNCTION 函数名（参数列表） RETURNS 返回类型
BEGIN()
       函数体
END
1
2
3
4

注意：
1.参数列表 包含两部分
参数名 参数类型
2.函数体：肯定会有return语句，如果没有会报错
如果return语句没有放在函数体的最后也不报错，但不建议return 值；
3.函数体中仅有一句话，则可以省略begin end
4.使用delimiter语句设置结束标记

二、调用语法

SELECT 函数名(参数列表)

锁

锁的认识

1）计算机协调多个进程或线程并发访问某一资源的机制。
2）在数据库中，除传统计算资源（CPU、RAM、I/O等）的争抢，数据也是一种供多用户共享的资源。
如何保证数据并发访问的一致性，有效性，是所有数据库必须要解决的问题。
锁冲突也是影响数据库并发访问性能的一个重要因素，因此锁对数据库尤其重要。
3）锁的缺点
加锁是消耗资源的，锁的各种操作，包括获得锁、检测锁是否已解除、释放锁等 ，都会增加系统的开销。
4）例子
淘宝双十一活动，当天的人流量是千万及亿级别的，但商家的库存是有限的。
系统为了保证商家的商品库存不发生超卖现象，会对商品的库存进行锁控制。当有用户正在下单某款商品最后一件时，系统会立马对该件商品进行锁定，防止其他用户也重复下单，直到支付动作完成才会释放（支付成功则立即减库存售罄，支付失败则立即释放）

锁的类型

表锁

1. 读锁（read lock），也叫共享锁（shared lock）：针对同一份数据，多个读操作可以同时进行而不会互相影响（select）。
2. 写锁（write lock），也叫排他锁（exclusive lock）：当前操作没完成之前，会阻塞其它读和写操作（update、insert、delete）。

存储引擎默认锁** MyISAM**

特点

1. 对整张表加锁
2. 开销小
3. 加锁快
4. 无死锁
5. 锁粒度大，发生锁冲突概率大，并发性低

结论

1. 读锁会阻塞写操作，不会阻塞读操作
2. 写锁会阻塞读和写操作
   3.MyISAM的读写锁调度是写优先，这也是MyISAM不适合做写为主表的引擎，因为写锁以后，其它线程不能做任何操作，大量的更新使查询很难得到锁，从而造成永远阻塞。

行锁

1. 读锁（read lock），也叫共享锁（shared lock），允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁
2. 写锁（write lock），也叫排他锁（exclusive lock，允许获得排他锁的事务更新数据，阻止其他事务取得相同数据集的共享锁和排他锁
3. 意向共享锁（IS），一个事务给一个数据行加共享锁时，必须先获得表的IS锁
4. 意向排它锁（IX），一个事务给一个数据行加排他锁时，必须先获得该表的IX锁

存储引擎默认锁：InnoDB

特点

1. 对一行数据加锁
2. 开销大
3. 加锁慢
4. 会出现死锁
5. 锁粒度小，发生锁冲突概率最低，并发性高

事务并发带来的问题

1. 更新丢失
   解决：让事务变成串行操作，而不是并发的操作，即对每个事务开始—对读取记录加排他锁
2. 脏读
   解决：隔离级别为Read uncommitted
3. 不可重读
   解决：使用Next-Key Lock算法来避免
4. 幻读
   解决：间隙锁（Gap Lock）

页锁

开销、加锁时间和锁粒度介于表锁和行锁之间，会出现死锁，并发处理能力一般

乐观锁与悲观锁

悲观锁

1. 假定会发生并发冲突，屏蔽一切可能违反数据完整性的操作
2. 实现机制：表锁、行锁等
3. 实现层面：数据库本身
4. 适用场景：并发量大

乐观锁

1. 假设不会发生并发冲突，只在提交操作时检查是否违反数据完整
2. 实现机制：提交更新时检查版本号或者时间戳是否符合
3. 实现层面：业务代码
4. 适用场景：并发量小

数据库Redis

缓存一致性问题

1)数据丢失，redis是内存数据库，存在服务器宕机或数据丢失的风险，可以使用持久化机制，如RDB快照和AOF日志，定期将数据写入磁盘
2）缓存数据一致性问题，后端数据更改，使缓存数据失效，以免返回过期数据

并发场景下怎么优化

1）数据同步问题，等待复制确认或使用异步复制来提高性能
2）竞态条件问题，redis操作是原子性的，使用事务或者乐观锁