数据库相关八股-持续更新

原创已于 2025-03-26 22:13:35 修改 · 913 阅读

6 ·

本内容遵循CC 4.0 BY-SA版权协议

GEO检测

于 2025-03-05 16:49:25 首次发布

本文仅供自己复习使用，参考资料来自小林coding（索引常见面试题 | 小林coding）

脏读，不可重复读和幻读

1.什么是脏读，不可重复读，幻读？

通俗解释：

📌 1. 脏读（Dirty Read）

💡 读取了别人的“草稿”，但他又改了或撤回了！

📍 场景

你去银行查余额，银行员工告诉你账户里有 1000 元。
与此同时，另一个银行员工正在处理你的贷款申请，他临时给你加了 500 元贷款，此时你的账户变成 1500 元，但这个操作还没提交（事务未完成）。
然后你查询时看到 1500 元，但过了一会儿，贷款审批失败，银行撤回了这 500 元，你的账户又变回 1000 元。

💣 你看到了一个从未真正存在过的余额（1500 元）！

📍 发生的原因

事务 A 读取了事务 B 未提交的数据，但事务 B 最后回滚了，导致 A 读取到了无效数据。

📍 解决方案

READ COMMITTED（提交读） 或更高隔离级别（如 REPEATABLE READ）。
事务 B 提交后，事务 A 才能读取，防止读取未提交的数据。

📌 2. 不可重复读（Non-Repeatable Read）

💡 你两次查询看到的内容不一样！

📍 场景

你去超市买牛奶，发现货架上有 10 瓶牛奶。
你想着“等会儿买”，过了一会儿你再来看时，发现货架上只剩 6 瓶了，因为别的顾客拿走了 4 瓶。

💣 你两次查询，看到的结果不同，数据不一致！

📍 发生的原因

事务 A 两次读取同一数据，但中间事务 B 修改了该数据并提交，导致 A 读取到不同的结果。

📍 解决方案

REPEATABLE READ（可重复读） 隔离级别。
事务 A 不能读取事务 B 提交的数据，确保同一事务内的查询结果一致。

📌 3. 幻读（Phantom Read）

💡 你查阅了一遍数据，过一会儿再看，发现多了/少了新的数据！

📍 场景

你在图书馆查“数据库原理”类书籍，发现馆里有 5 本相关书籍，你打算借阅其中 3 本。
你回去办借阅手续时，再次查询，却发现馆里突然多了 2 本新书！

💣 你之前查到的是 5 本，现在却变成了 7 本！数据“幻变”了！

📍 发生的原因

事务 A 查询数据时，事务 B 插入/删除了数据，并提交，导致事务 A 再次查询时，数据变了（新数据“幻”出来了）。

📍 解决方案

SERIALIZABLE（可串行化） 隔离级别。
使用 Next-Key Lock（间隙锁），锁住数据范围，防止插入/删除操作。

2.不可重复读和幻读的区别是什么？

两者的关键区别在于：

不可重复读（Non-Repeatable Read）：“同一行数据” 被修改，两次查询的结果不同。
幻读（Phantom Read）：“查询到的行数” 发生了变化，即 新增/删除了数据。

一条sql语句的执行顺序：

在SQL中，SELECT、FROM、WHERE等语句的执行顺序并不是按照它们在查询中的书写顺序进行的。SQL查询的实际执行顺序如下：

1. FROM

作用：确定查询的数据来源（表或视图）。
执行过程：首先，SQL引擎会从FROM子句中指定的表或视图中读取数据。如果有多个表（如使用了JOIN），则会根据JOIN条件将这些表连接起来。

2. WHERE

作用：过滤数据，只保留满足条件的行。
执行过程：在FROM子句确定了数据来源后，WHERE子句会对这些数据进行筛选，只保留满足条件的行。

3. GROUP BY

作用：将数据按指定的列进行分组。
执行过程：如果查询中有GROUP BY子句，SQL引擎会根据指定的列对数据进行分组。通常与聚合函数（如COUNT、SUM、AVG等）一起使用。

4. HAVING

作用：对分组后的数据进行过滤。
执行过程：HAVING子句用于过滤GROUP BY分组后的数据，只保留满足条件的分组。

5. SELECT

作用：选择要返回的列。
执行过程：在SELECT子句中，SQL引擎会选择要返回的列。如果使用了聚合函数或表达式，这些计算也会在此阶段进行。

6. DISTINCT

作用：去除重复的行。
执行过程：如果查询中有DISTINCT关键字，SQL引擎会在SELECT之后去除重复的行。

7. ORDER BY

作用：对结果集进行排序。
执行过程：在ORDER BY子句中，SQL引擎会根据指定的列对结果集进行排序。默认是升序（ASC），也可以指定降序（DESC）。

8. LIMIT / OFFSET

作用：限制返回的行数或跳过指定的行数。
执行过程：LIMIT用于限制返回的行数，OFFSET用于跳过指定的行数。通常用于分页查询。

示例

SELECT department, AVG(salary) AS avg_salary
FROM employees
WHERE hire_date > '2020-01-01'
GROUP BY department
HAVING AVG(salary) > 50000
ORDER BY avg_salary DESC
LIMIT 10;

执行顺序

FROM employees：从employees表中读取数据。
WHERE hire_date > '2020-01-01'：过滤出hire_date大于2020-01-01的行。
GROUP BY department：按department列分组。
HAVING AVG(salary) > 50000：过滤出平均工资大于50000的部门。
SELECT department, AVG(salary) AS avg_salary：选择department和平均工资列。
ORDER BY avg_salary DESC：按平均工资降序排序。
LIMIT 10：只返回前10行

数据库的索引

索引的分类

数据库的索引可以从很多不同的角度进行分析，我们从四个角度分析

按数据结构分类：B+Tree索引，Hash索引，Full-Text索引

按物理存储分类：聚簇索引（主键索引），二级索引（辅助索引）

按字段特性分类：主键索引，唯一索引，普通索引，前缀索引

按字段个数分：单列索引，联合索引

按数据结构分：

InnoDB是在mysql5.5之后的默认的存储引擎，B+树也是 MYSQL存储引擎最多的索引类型。

B+Tree是一种多叉树，非叶子节点只存放索引，叶子节点才存放数据，而且每个节点里的数据都是按主键顺序存放的，然后叶子节点之间形成了一个双向链表，这里不多说

为什么数据库采用B+树的存储引擎？

因为数据库的索引和数据都是存储在磁盘里的，每当读取一个节点都是一个磁盘操作，B+树存储千万级别的数据只需要3-4层高度就可以满足，这意味着从千万级别的表查询目标数据最最多只需要3-4次磁盘IO，所以B+树相比于B树和二叉树来说，最大的优势在于查询效率很高，因为即使在数据量很大的情况下，查询一个数据的磁盘IO依然维持在3-4次。

这里说一个概念：覆盖索引以及回表

刚才的主键索引存的是实际的数据节点。二级索引存储的则是主键值，而不是实际数据。

假如有一个sql语句：

select * from product where product_no='002'

假如这里把product_no设置为二级索引，就会构造这样的一颗索引树：

当查到002的时候，查出的不是实际数据，而是主键id，这时会回到刚才的B+树，并且根据查到的主键id重新查找数据，这个过程就叫做回表，也就是说要查两个B+树才能查到数据。

所以这里给出一个解决方案：覆盖索引；

select id from product where product_no = '0002';

在这条语句中，查的是主键id，就是索引存的内容，这种在二级索引的B+tree中就能查到结果的过程就覆盖索引，只需查一个B+tree。

B+tree的优势在哪里：

1.B+树vsB树

B+数只在叶子节点存储数据，B树的非叶子节点也存储数据，所以B➕树单个节点的数据量更小，在相同的磁盘IO下，能查询更多的节点。B+树有双链表，适合MySQL中的范围查找，B树无法做到这一点。

2.B+树vs二叉树

B+树在实际的应用中，度是大于100的，这就保证了千万级别数据，B+树的高度依旧在3-4层，也就是说一次查询只需要做3-4次的磁盘IO操作就能查询到目标数据。二叉树就不行。

3.B+树vsHash

Hash只能做等值查询O（1），但是做不了范围查询，所以B+Tree的使用范围更广

按物理存储分类

从物理存储看，索引就分为两种，聚簇索引（主键索引）和辅助索引（二级索引）

这二者的区别刚才已经讲到了，主键索引中B+树存放的是实际数据，所有表数据都存放在主键索引的叶子节点中，辅助索引存储的是主键值，而不是实际数据。

能够通过覆盖索引解决辅助索引的回表问题。

按字段特性分类

从字段特性可以分为主键索引，唯一索引，普通索引和前缀索引。

主键索引就是建立在主键字段上的索引，通常在创建表的时候一起创建，一张表最多只有一个主键索引，索引列不准有空值。

唯一索引（UNIQUE INDEX）建立在UNIQUE字段上的索引，一张表可以有多个唯一索引，索引列的值必须唯一，但是允许有空值。

普通索引就是建立在普通字段上的索引，既不要字段为主键，也不要字段为UNIQUE。

前缀索引，是指对字符类型字段的前几个字符创建索引，不是在整个字段上创建的索引，前缀索引可以创建在字段类型为char，varchar，binary的列上。

使用前缀索引可以减少索引占用的存储空间，提升查询效率。

按字段个数分类

可以分为两种，一是单列索引，二是联合索引（复合索引）

联合索引由多个字段组成，这里假设由两个字段组成一个联合索引（product_no，name）那么在B+树中就会先按照左边的列进行排序，再按照右边的列进行排序，B+树结构如下：

所以，在使用联合索引的时候存在最左匹配原则，也就是按照最左优先的方式进行索引的匹配，所以在使用联合索引的时候要注意最左原则，配合查询字段设计查询语句。

索引下推

假设有一张表 users，结构如下：

CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    age INT,
    city VARCHAR(50),
    INDEX idx_name_city (name, city)
);

执行以下查询：

没有索引下推的情况：

存储引擎使用索引 idx_name_city 找到所有 name = 'Alice' 的记录。
将这些记录返回给服务器层。
服务器层再根据 city = 'Beijing' 条件进行过滤。

有索引下推的情况：

存储引擎使用索引 idx_name_city，同时应用 name = 'Alice' 和 city = 'Beijing' 条件。
只返回满足这两个条件的记录给服务器层。

什么时候需要/不需要索引？

索引既有优点，也有缺点，在带来速度的同时也带来了很大的空间占用，创建索引和维护索引都需要时间，而且每次增删改都要对索引进行动态维护。

适用索引的情况：

1.字段有唯一性限制的

2.经常用于where查询条件的字段，如果不是一个字段，可以创建联合索引。

3.经常用于Group by 和 order by的字段。

不适用索引的情况：

1.where条件，group by，order by里面用不到的字段。

2.字段中存在大量的重复数据，比如性别字段，只有男女。

3.表数据太少的时候

4.经常更新的字段不用创建索引。经常修改就要频繁的重建索引。

索引优化的方法

1.前缀索引优化（把某个字段的字符串的前几个字符建立索引）

2.覆盖索引优化（query的字段都能在索引的叶子节点中找到，防止回表）

3.主键索引最好自增（防止页分裂，造成大量的内存碎片）

采用自增主键就会一个一个加

4.索引最好设置为not null（节省内存）

5.防止索引实效（模糊查询，计算，函数，违背最左前缀）

数据库的日志

Mysql日志包括错误日志，查询日志，事务日志，二进制日志等等。

其中最重要的还是二进制日志（binlog）和事务日志redo log（重做日志）和回滚日志（undo log）

redo log

redo log（重做日志）是 InnoDB 存储引擎独有的，它让 MySQL 拥有了崩溃恢复能力。

比如 MySQL 实例挂了或宕机了，重启时，InnoDB 存储引擎会使用 redo log 恢复数据，保证数据的持久性与完整性。

redolog存的是什么：

每条 redo 记录由“表空间号+数据页号+偏移量+修改数据长度+具体修改的数据”组成

mysql中的数据以页为单位，每当查询一条记录就会从磁盘中把页加载出来，放入到Buffer Pool中，后续的查询都是在Pool中找，在更新表数据的时候，如果发现pool中有数据，就直接在pool中更新。然后会把在某个数据页上做了什么修改记录到重做日志缓存（redo log buffer）中，然后定期刷盘到redo log中。

刷盘时机

事务提交：当事务提交时，log buffer 里的 redo log 会被刷新到磁盘（可以通过innodb_flush_log_at_trx_commit参数控制，后文会提到）。
log buffer 空间不足时：log buffer 中缓存的 redo log 已经占满了 log buffer 总容量的大约一半左右，就需要把这些日志刷新到磁盘上。
事务日志缓冲区满：InnoDB 使用一个事务日志缓冲区（transaction log buffer）来暂时存储事务的重做日志条目。当缓冲区满时，会触发日志的刷新，将日志写入磁盘。
Checkpoint（检查点）：InnoDB 定期会执行检查点操作，将内存中的脏数据（已修改但尚未写入磁盘的数据）刷新到磁盘，并且会将相应的重做日志一同刷新，以确保数据的一致性。
后台刷新线程：InnoDB 启动了一个后台线程，负责周期性（每隔 1 秒）地将脏页（已修改但尚未写入磁盘的数据页）刷新到磁盘，并将相关的重做日志一同刷新。
正常关闭服务器：MySQL 关闭的时候，redo log 都会刷入到磁盘里去。

总之，InnoDB 在多种情况下会刷新重做日志，以保证数据的持久性和一致性。

innodb_flush_log_at_trx_commit参数：

参数为0:每次事务提交不刷盘，性能最高，不安全，mysql宕机会丢失一秒内的事务。

参数为1:每次提交事务都刷盘，性能最低，最安全。

参数为2:事务提交只把log buffer的redo log内容写入page cache，page cache专门用来缓存文件。性能和安全性介于二者之间。mysql挂了不会丢失事务，但是如果宕机就会丢失近1秒的事务。

默认为1，不会丢失任何数据。

另外，InnoDB 存储引擎有一个后台线程，每隔1 秒，就会把 redo log buffer 中的内容写到文件系统缓存（page cache），然后调用 fsync 刷盘。

还有一种情况，当 redo log buffer 占用的空间即将达到 innodb_log_buffer_size 一半的时候，后台线程会主动刷盘。

日志文件组

这个知识点类似于redis的repl_backlog ，就是说硬盘上存储的 redo log 日志文件不只一个，而是以一个日志文件组的形式出现的，每个的redo日志文件大小都是一样的。

结构如下，采用了环形数组：

有两个指针：

write pos 是当前记录的位置，一边写一边后移
checkpoint 是当前要擦除的位置，也是往后推移

每次刷盘 redo log 记录到日志文件组中，write pos 位置就会后移更新。

如果 write pos 追上 checkpoint ，表示日志文件组满了，这时候不能再写入新的 redo log 记录，MySQL 得停下来，清空一些记录，把 checkpoint 推进一下。

mysql8.0.30之后日志文件组的文件数默认为32个，每个日志文件大小为20971520

binlog

redo log 它是物理日志，记录内容是“在某个数据页上做了什么修改”，属于 InnoDB 存储引擎。

而 binlog 是逻辑日志，记录内容是语句的原始逻辑，类似于“给 ID=2 这一行的 c 字段加 1”，属于MySQL Server 层。

不管是什么存储引擎，只要发生表的数据更新，都会产生binlog日志。binlog会记录所有涉及更新数据的逻辑操作，并且是顺序写。

MySQL的数据备份，主备，主主，主从都离不开binlog，需要依靠binlog来同步数据，保证数据的一致性。

binlog日志有三种格式，可以通过binlog_format参数指定

statement模式：记录sql原文：update T set update_time=now() where id=1

有问题：now（）获得的事系统当前时间，写一个now（）怎么知道当时是什么时候

row模式：

row格式记录的内容看不到详细信息，要通过mysqlbinlog工具解析出来。

update_time=now()变成了具体的时间update_time=1627112756247，条件后面的@1、@2、@3 都是该行数据第 1 个~3 个字段的原始值（假设这张表只有 3 个字段）。

这样能保证数据的一致性，但是需要更大的容量来记录，比较占用空间。

mixed模式：前两者的混合，如果这条sql不会引起数据不一致就用statement，否则就用row

binlog写入机制

事务执行过程中，先把日志写到binlog cache，事务提交的时候，再把binlog cache写到 binlog 文件中。

因为一个事务的 binlog 不能被拆开，无论这个事务多大，也要确保一次性写入，所以系统会给每个线程分配一个块内存作为binlog cache。

我们可以通过binlog_cache_size参数控制单个线程 binlog cache 大小，如果存储内容超过了这个参数，就要暂存到磁盘（Swap）。

write是写到pageCache，fsync才是持久化到磁盘。

参数sync_binlog：

为0的时候每次事务提交都只write，fsync的时机由磁盘决定。

为1的时候每次提交事务都会执行fsync，如同redo日志刷盘流程一样。

也可以采用折中的方式：设置为n，每次提交事务都write，累计n个事务后才fsync。

`两阶段提交`

binlog和redolog是一起使用的，在执行更新语句过程，会记录 redo log 与 binlog 两块日志，以基本的事务为单位，redo log 在事务执行过程中可以不断写入，而 binlog 只有在提交事务时才写入，所以 redo log 与 binlog 的写入时机不一样。

如果二者的逻辑不一致，会出现问题：

我们以update语句为例，假设id=2的记录，字段c值是0，把字段c值更新成1，SQL语句为update T set c=1 where id=2。

假设执行过程中写完 redo log 日志后，binlog 日志写期间发生了异常，会出现什么情况呢？

由于 binlog 没写完就异常，这时候 binlog 里面没有对应的修改记录。因此，之后用 binlog 日志恢复数据时，就会少这一次更新，恢复出来的这一行c值是0，而原库因为 redo log 日志恢复，这一行c值是1，最终数据不一致。

所以InnoDB引擎采用两阶段提交的方案：

把redo log 的写入拆成了两个步骤prepare和commit，这就是两阶段提交。

这样，写入binlog异常时也不会有影响，MYSQL在根据redo log日志恢复数据时，发现redo log还处于prepare阶段，并且没有对应的binlog日志，就会回滚该事务。

UndoLog

顾名思义，undo log是一种用于撤销回退的日志，在事务没提交之前，MySQL会先记录更新前的数据到 undo log日志文件里面，当事务回滚时或者数据库崩溃时，可以利用 undo log来进行回退。

undolog的作用：

1.提供回滚操作（实现事务的原子性）

我们在进行数据更新操作的时候，不仅会记录redo log，还会记录undo log，如果因为某些原因导致事务回滚，那么这个时候MySQL就要执行回滚（rollback）操作，利用undo log将数据恢复到事务开始之前的状态。

如我们执行下面一条删除语句：

delete from user where id = 1;

那么此时undo log会记录一条对应的insert 语句【反向操作的语句】，以保证在事务回滚时，将数据还原回去。

再比如我们执行一条update语句：

update user set name = "李四" where id = 1;   ---修改之前name=张三

此时undo log会记录一条相反的update语句，如下：

update user set name = "张三" where id = 1;

如果这个修改出现异常，可以使用undo log日志来实现回滚操作，以保证事务的一致性。

2.提供多版本并发控制（MVCC）

在MySQL数据库InnoDB存储引擎中，用undo Log来实现多版本并发控制(MVCC)。当读取的某一行被其他事务锁定时，它可以从undo log中分析出该行记录以前的数据版本是怎样的，从而让用户能够读取到当前事务操作之前的数据【快照读】。

快照读：

SQL读取的数据是快照版本【可见版本】，也就是历史版本，不用加锁，普通的SELECT就是快照读。

当前读：

SQL读取的数据是最新版本。通过锁机制来保证读取的数据无法通过其他事务进行修改UPDATE、DELETE、INSERT、SELECT … LOCK IN SHARE MODE、SELECT … FOR UPDATE都是当前读。

undo log的存储机制

InnoDB存储引擎中，undo log是采用分段(segment)的方式进行存储的。rollback segment称为回滚段，每个回滚段中有1024个undo log segment。在MySQL5.5之前，只支持1个rollback segment，也就是只能记录1024个undo操作。在MySQL5.5之后，可以支持128个rollback segment，分别从resg slot0 - resg slot127，每一个resg slot，也就是每一个回滚段，内部由1024个undo segment 组成，即总共可以记录128 * 1024个undo操作。

undo log存了什么信息？