数据库-面试常见问题_面试常见的问题数据库-CSDN博客

optimize 阶段，也就是优化阶段，即PG的查询计划器；优化器主要负责将 SQL 查询语句的执行方案确定下来，比如在表里面有多个索引的时候，优化器会基于查询成本的考虑，来决定选择使用哪个索引。
execute 阶段，也就是执行阶段，即实际读取数据的执行器；在执行的过程中，执行器就会和存储引擎交互了，交互是以记录为单位的。

1 事务

1.1 什么是事务？MySQL默认是什么级别？

事务（Transaction）是计算机中的一个概念，特别是在数据库管理系统中。它主要是指访问并可能更新数据库中各种数据项的一个程序执行单元。用形如begin transaction和end transaction语句（或函数调用）来界定。事务由事务开始（begin transaction）和事务结束（end transaction）之间执行的全体操作组成。在计算机中，事务是恢复和并发控制的基本单位。

事务应该具有四个属性，通常称为ACID特性：

原子性（Atomicity）：事务是一个不可分割的工作单位，事务中包括的操作要么都做，要么都不做。
一致性（Consistency）：事务必须是使数据库从一个一致性状态变到另一个一致性状态。
隔离性（Isolation）：多个事务并发执行时，一个事务的执行不应影响其他事务。
持久性（Durability）：一旦事务提交，则其结果就是永久性的，即使系统崩溃也不会丢失。

MySQL支持四种事务隔离级别，从低到高分别是：READ UNCOMMITTED（读未提交）（脏读）、READ COMMITTED（读提交）（不可重复读）、REPEATABLE READ（可重复读）和SERIALIZABLE（序列化）。MySQL默认为RR。

隔离级别越高，越能保证数据的完整性和一致性，但是对并发性能的影响也越大，鱼和熊掌不可兼得啊。对于多数应用程序，可以优先考虑把数据库系统的隔离级别设为 Read Committed，它能够避免脏读取，而且具有较好的并发性能。尽管它会导致不可重复读、幻读这些并发问题，在可能出现这类问题的个别场合，可以由应用程序采用悲观锁或乐观锁来控制。

1.2 MVCC是为了解决什么，怎么做的？

MVCC，全称Multi-Version Concurrency Control，即多版本并发控制，是一种并发控制的方法，主要用在数据库管理系统中，实现对数据库的并发访问。其目的是为了提高数据库的并发性能，同时保证事务的隔离效果，实现非阻塞的并发读操作。

在早期的数据库系统中，只有读读之间可以并发，读写、写读、写写都需要阻塞。引入多版本控制后，只有写写之间相互阻塞，其他三种操作都可以并行，这样大幅度提高了数据库的并发度。

MVCC的实现通常包括以下几个关键组件：

事务标识：每个事务被分配一个唯一的事务标识（Transaction ID）。事务标识可以是递增的数字或其他唯一标识符。
数据版本：数据库中的每个数据记录都会包含多个版本，每个版本都有一个时间戳或者事务标识，用于标识该版本的有效期。例如，某个数据记录可能有多个版本，其中最新的版本是由当前活动事务创建的，而旧版本则由已提交的事务创建。
读操作：当一个事务执行读操作时，在数据版本链中选择与该事务兼容的最新版本，即该版本在事务开始之前已经提交或由该事务创建。
写操作：当一个事务执行写操作时，会创建一个新的数据版本，并将事务标识或时间戳与该版本关联。新版本中的数据仅对当前事务可见，对其他事务不可见，直到当前事务提交。
并发控制：MVCC通过在读操作和写操作中使用事务标识或时间戳来判断数据的可见性和一致性。

1.3 事务的实现原理？

使用了数据锁，日志，MVCC等机制来实现。

InnoDB 引擎通过什么技术来保证事务的这四个特性的呢？

持久性是通过 redo log （重做日志）来保证的；
原子性是通过 undo log（回滚日志）来保证的；
隔离性是通过 MVCC（多版本并发控制）或锁机制来保证的；
一致性则是通过持久性+原子性+隔离性来保证；

1.4 请介绍数据库日志？

是什么？

数据库日志使用WAL机制来实现，即提前写日志。在运行时写WAL日志，在提交时进行提交。当数据库崩溃时，使用redo日志来恢复未及时刷盘的数据页，使用undo日志来撤销一些没有提交的事务。Redo Log 应该采用 Physical Log 或者 Physiological Log。

为什么需要？

因为我们需要保证事务的原子性和持久性，也就是 ACID 中的「A」和「D」。

怎么做的？

使用经典的ARIES算法来实现。

1.5 为什么要Checkpoint？什么是ARIES？请介绍数据库恢复过程？

1.5.1 为什么需要？

我们通过记录 Log 来帮助数据库可以从挂掉的状态中恢复，然而长时间运行的数据库的 Log 会不断增加，制造出两个问题：

Recovery 期间需要解析并处理全量 Log，恢复越来越慢。
Log 累积过多会占用大量磁盘空间。

Checkpoint 用来解决这两个问题。

1.5.2 是什么？

每次做 Checkpoint，就好像在 Log 中打了一个标记，我们认为这个标记之前的所有 Log 都不会被再用到，Recovery 期间可以直接跳过。这些 Log 甚至可以直接删掉。

1.5.3 如何实现？

通常有 Consistent Checkpoint 和 Fuzzy Checkpoint 两个方案。

Consistent Checkpoint 是最朴素的实现，步骤如下：

禁止新事务启动，等待正在执行的事务结束（其实就是停服）。
遍历 Buffer Pool，将所有 Dirty Page 刷盘。
记录一条 Checkpoint Log。
允许所有事务正常执行（恢复服务）。

Recovery 直接从 Checkpoint Log 开始，之前的 Log 一律跳过。

Fuzzy Checkpoint 使用 ARIES动态管理来实现。

1.5.4 请简要说明ARIES 日志恢复方法？

ARIES日志恢复方法是一种数据库恢复技术，其核心在于通过事务日志来恢复数据库的一致性和完整性。以下是ARIES日志恢复方法的主要步骤和特点：

分析阶段：此阶段通过分析日志来确定哪些事务已经提交，哪些事务正在活动，以及哪些事务被中断。这是恢复过程的基础，为后续的恢复操作提供了必要的信息。
重做阶段：在重做阶段，ARIES算法会根据分析阶段得到的信息，通过重放日志中的redo操作来恢复那些被中断的事务所做的修改。这确保了这些修改能够正确地应用到数据库中。重做操作会从最旧的LSN（日志序列号）开始，并顺序执行redo操作，直到达到最新的LSN。
撤销阶段：撤销阶段的主要任务是撤销未提交的事务的修改，以保证数据库的一致性。这通过反向操作（undo）来实现，具体的撤销操作包括将脏页的内容恢复到最新的LSN之前的状态，并将撤销相关的日志记录写入到LSN之后。撤销操作的顺序也是基于redo日志中的LSN。

2 锁

2.1 数据库锁的粒度怎么分的？

数据库锁的粒度主要根据对数据库中的数据对象进行加锁的程度来划分，它直接影响到数据库并发控制的效率和性能。常见的锁粒度包括以下几种：

行级锁（Row-level Locking）：

是粒度最小的锁。事务在操作数据的过程中，锁定一行或多行的数据，其他事务不能同时处理这些行的数据。行级锁占用的数据资源最小，所以在事务的处理过程中，允许其它事务操作同一表的其他数据。行级锁能大大减少数据库操作的冲突。其加锁粒度最小，但加锁的开销也最大。行级锁分为共享锁和排他锁。
开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。

页级锁（Page-level Locking）：

是对数据页进行加锁，数据页是数据库存储数据的最小单位。相比表级锁，页级锁的并发性能有所提升，因为不同用户可以同时读取同一个表中不同的数据页。
开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般；

表级锁（Table-level Locking）：

- 是对整个表进行加锁，当一个用户对表进行读写操作时，其他用户对该表的读写操作都会被阻塞。表级锁具有简单、粗粒度的特点，因此在并发量较小的情况下，效果较好。不过，当并发量增大时，表级锁的性能会显著下降，因为它会导致较高的阻塞和资源争用。

2.2 锁的类型有哪些？

从锁的类别上来讲，有共享锁和排他锁。

共享锁: 又叫做读锁。当用户要进行数据的读取时，对数据加上共享锁。共享锁可以同时加上多个。
排他锁: 又叫做写锁。当用户要进行数据的写入时，对数据加上排他锁。排他锁只可以加一个，他和其他的排他锁，共享锁都相斥。

2.3 乐观锁和悲观锁是什么？

观并发控制（乐观锁）和悲观并发控制（悲观锁）是并发控制主要采用的技术手段。

悲观锁：假定会发生并发冲突，屏蔽一切可能违反数据完整性的操作。在查询完数据的时候就把事务锁起来，直到提交事务。实现方式：使用数据库中的锁机制。
乐观锁：假设不会发生并发冲突，只在提交操作时检查是否违反数据完整性。在修改数据的时候把事务锁起来，通过version的方式来进行锁定。实现方式：乐一般会使用版本号机制或CAS算法实现。

2.3.1 两种锁的使用场景

从上面对两种锁的介绍，我们知道两种锁各有优缺点，不可认为一种好于另一种。乐观锁适用于写比较少的情况下（多读场景），即冲突真的很少发生的时候，这样可以省去了锁的开销，加大了系统的整个吞吐量。

但如果是多写的情况，一般会经常产生冲突，这就会导致上层应用会不断的进行retry，这样反倒是降低了性能，所以一般多写的场景下用悲观锁就比较合适。

3 其它问题

3.1 sql注入是什么意思？

SQL注入其实就是恶意用户通过在表单中填写包含SQL关键字的数据来使数据库执行非常规代码的过程。简单来说，就是SQL代码伪装成字段数据，来让后端执行数据里的SQL语句。SQL注入说白了，就是通过把SQL命令插入到WEB表单提交或输入域名或页面请求的查询字符串，最终达到欺骗服务器执行恶意的SQL命令。

conn = sqlite3.connect('test.db')
插入包含注入代码的信息：name = "Robert');DROP TABLE students;--"  # 这里把SQL语句伪装到name中，让后台误运行
query = "INSERT INTO students (name) VALUES ('%s')" % (name)

conn.executescript(query)检视已有的学生信息：cursor = conn.execute("SELECT id, name from students")
print('IDName')
for row in cursor:
    print('{0}{1}'.format(row[0], row[1]))

conn.close()

3.1.1 如何防止SQL注入？

为了防止SQL注入，可以采取以下措施（面试的时候记不住就简单记几个就行。）：

使用参数化查询或预处理语句：

这是防止SQL注入的最有效方法。它要求你使用占位符来代替SQL语句中的变量，并在执行语句之前将变量值绑定到这些占位符上。例如，在Python的SQLite库中，你可以使用?作为占位符，在MySQL的Python库中，可以使用%s。这样做的好处是，变量值会被正确地转义，从而防止恶意SQL代码的插入。

限制用户输入：

验证和清理用户输入是非常重要的。确保输入符合预期的格式和长度。使用白名单验证法，即只接受预定义的、已知的输入值，而不是尝试排除所有可能的恶意输入。

最小权限原则：

数据库连接应使用具有最小必要权限的数据库用户。避免使用具有管理员权限的数据库用户来连接应用程序。

数据库信息隐藏：

不要将详细的数据库错误信息直接显示给用户。这可以防止攻击者获取有关数据库结构的有用信息。
使用自定义的错误页面或日志记录来处理错误。

综上所述，防止SQL注入需要多方面的策略和技术。最重要的是，始终保持对安全性的关注，并持续更新和改进你的防御措施。

3.2 为什么要分库分表？

分表：解决一个表数据太多的情况。单表数据量太大，会极大影响你的 sql执行的性能，到了后面你的 sql 可能就跑的很慢了。一般来说，就以我的经验来看，单表到几百万的时候，性能就会相对差一些了，你就得分表了。

分库：为了解决并发的情况。分库就是你一个库一般我们经验而言，最多支撑到并发 2000，一定要扩容了，而且一个健康的单库并发值你最好保持在每秒 1000 左右，不要太大。那么你可以将一个库的数据拆分到多个库中，访问的时候就访问一个库好了。

3.3 MySQL数据如何实现同步备份？

MySQL使用主从同步备份。主从同步使得数据可以从一个数据库服务器复制到其他服务器上，在复制数据时，一个服务器充当主服务器（master），其余的服务器充当从服务器（slave）。

因为复制是异步进行的，所以从服务器不需要一直连接着主服务器，从服务器甚至可以通过拨号断断续续地连接主服务器。通过配置文件，可以指定复制所有的数据库，某个数据库，甚至是某个数据库上的某个表。

为什么要备份？

通过增加从服务器来提高数据库的性能。在主服务器上执行写入和更新，在从服务器上向外提供读功能，可以动态地调整从服务器的数量，从而调整整个数据库的性能。
提高数据安全。因为数据已复制到从服务器，从服务器可以终止复制进程，所以，可以在从服务器上备份而不破坏主服务器相应数据
数据备份。一般我们都会做数据备份，可能是写定时任务，一些特殊行业可能还需要手动备份，有些行业要求备份和原数据不能在同一个地方，所以主从就能很好的解决这个问题，不仅备份及时，而且还可以多地备份，保证数据的安全

3. 4 如何实现MySQL的读写分离？

其实很简单，就是基于主从复制架构，简单来说，就搞一个主库，挂多个从库，然后我们就单单只是写主库，然后主库会自动把数据给同步到从库上去。

3.5 PG数据库内核查询处理？

Parser，Analyzer, Rewriter, Planner, Executor

3.6 Mysql的MVCC和Postgresql的MVCC具体实现对比？

3.6.1 PG的MVCC

1、事务快照是一个数据集，用于存储有关单个事务的某个时间点所有事务是否处于活动状态的信息。在这里，活动事务意味着它正在进行中或尚未开始。用于在判断数据的可见性，用于在事务发生时的事务可见性规则判断。一般来说在根据事务的设定等级来判断事务快照的设置。即在read commited 和repeatable read之间进行选择。

保存了三种信息：

最早仍处于活动状态的 txid）：所有更早的事务要么被提交并可见，要么回滚并失效。

第一个尚未分配的 txid：所有大于或等于此值的 txid 在快照发生时尚未启动，因此不可见。

快照时的活动事务 ID 列表：该列表仅包括 xmin 和 xmax 之间的活动 txid。

2、读操作（SELECT）的执行过程：

获取事务的快照（Snapshot）：在执行SELECT语句的事务开始时，会获取一个快照，用于记录当前数据库状态。
查询可见数据版本：使用事务的快照，查询操作会检查每个数据行的版本链，并根据可见性规则确定哪些数据版本对于该事务是可见的。
读取数据：根据查询的结果，事务只会看到在它启动时间之前已经提交的数据版本，这样可以保证事务的数据视图是一致性的。
处理并发修改：如果其他事务正在对数据行进行修改，而这些修改版本对于当前查询的事务不可见，数据库会返回旧版本的数据给该事务。这样，读操作不会受到并发写操作的干扰。

3、版本数据在哪？

PG不使用undo log来保存旧版本数据，而是将更新操作变为删除和插入操作，重新创建一个数据行保存到数据页里面。这些数据行具有版本信息，用于实现MVCC。

update = delete + insert

这样一直插入数据势必会导致数据膨胀，所以，PG有Vacuum机制来定期清理这些没用的数据。通常，不需要的元组在 PostgreSQL 中称为死元组。死元组最终应从页面中删除。清理死元组称为 VACUUM 处理，

3.6.2 Mysql的MVCC原理

1、undo log

与 redo log 记录的是物理页的修改不同，undo log 记录的是逻辑日志。当 delete 一条记录时，undo log 中会记录一条对应的 insert 记录，反之亦然，当 update 一条记录时，它记录一条对应相反的 update 记录，如果 update 的是主键，则是对先删除后插入的两个事件的反向逻辑操作的记录。这样，在事务回滚时，我们就可以从 undo log 中反向读取相应的内容，并进行回滚，同时，我们也可以根据 undo log 中记录的日志读取到一条被修改后数据的原值。

正是依赖 undo log，innodb 实现了 ACID 中的 C – Consistency 即一致性。

2、MVCC的实现

MVCC 全称是 multiversion concurrency control，即多版本并发控制，是 innodb 实现事务并发与回滚的重要功能。具体的实现是，在数据库的每一行中，添加额外的三个字段：

DB_TRX_ID – 记录插入或更新该行的最后一个事务的事务 ID

DB_ROLL_PTR – 指向改行对应的 undolog 的指针

DB_ROW_ID – 单调递增的行 ID，他就是 AUTO_INCREMENT 的主键 ID

快照读：通过数据列的undo log指针以及事务快照来查看数据的可见性，从而在undo log中查到最新的可以读到的数据

4 MySQL的索引

一文搞懂MySQL索引（清晰明了）-CSDN博客

4.1 Innodb 支持的索引数据结构有哪几种？

4.1.1 B+树

B+树与B树的区别。为什么Innodb用B+树索引而不是B树索引？

- 1. B+树的非叶子节点不保存实际数据，只保存索引数据，所以一页page保存了更多的索引数据，空间利用率高，可以减少存储硬件的IO操作。
  2. B+树的叶子节点保存数据，而且在叶子节点中设计了链表来连接数据，可以非常有效的支持范围查询。因为它可以直接遍历叶子节点链表来获取范围内的所有记录。而B树则需要进行每一层的递归遍历。相邻的元素可能在内存中不相邻，所以缓存命中性没有B+树好。

4.1.2 可扩展hash表索引

Hash索引仅仅能满足"=",“IN"和”"查询，不能使用范围查询。也不支持任何范围查询，例如WHERE price > 100

4.2 什么是主键索引与辅助索引的区别？

主键索引：一张表只能有一个主键索引，不允许重复、不允许为 NULL；

ALTER TABLE TableName ADD PRIMARY KEY(column_list);

唯一索引：数据列不允许重复，允许为 NULL 值，一张表可有多个唯一索引，索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。

CREATE UNIQUE INDEX IndexName ON TableName(字段名(length)); ALTER TABLE TableName ADD UNIQUE (column_list);

普通索引：一张表可以创建多个普通索引，一个普通索引可以包含多个字段，允许数据重复，允许 NULL 值插入；

CREATE INDEX IndexName ON TableName(字段名(length));

在聚簇索引之外创建的索引（不是根据主键创建的）称之为辅助索引，辅助索引访问数据总是需要二次查找。辅助索引叶子节点存储的不再是行数据记录，而是主键值。首先通过辅助索引找到主键值，然后到主键索引树中通过主键值找到数据行。

按照索引的列的多少来分类？

单例索引：一个索引只包含一个列，一个表可以有多个单例索引。
组合索引：一个组合索引包含两个或两个以上的列。查询的时候遵循 mysql 组合索引的 “最左前缀”原则，即使用 where 时条件要按照建立索引的时候字段的排列方式放置索引才会生效。

4.3 什么是聚簇索引与非聚簇索引的区别？

聚簇索引（clustered index）不是单独的一种索引类型，而是一种数据存储方式，即叶子索引页保存了行数据。这种存储方式是依靠B+树来实现的，根据表的主键构造一棵B+树且B+树叶子节点存放的都是表的行记录数据时，方可称该主键索引为聚簇索引。聚簇索引也可理解为将数据存储与索引放到了一块页，找到索引页也就找到了数据。

非聚簇索引：数据和索引是分开的，B+树叶子节点数据页存放的不是数据表的行记录，而是指向具体数据页的页指针。

虽然InnoDB和MyISAM存储引擎都默认使用B+树结构存储索引，但是只有InnoDB的主键索引才是聚簇索引，InnoDB中的辅助索引以及MyISAM使用的都是非聚簇索引。每张表最多只能拥有一个聚簇索引。

MyISAM也使用B+Tree作为索引结构，但具体实现方式却与InnoDB截然不同。MyISAM使用的都是非聚簇索引。MyISAM表的索引和数据是分开存储的，.MYD表数据文件 .MYI表索引文件。

聚簇索引优点：

数据访问更快，
聚簇索引对于主键的排序查找和范围查找速度非常快

缺点：

插入速度严重依赖于插入顺序，按照主键的顺序插入是最快的方式，否则将会出现页分裂，严重影响性能。因此，对于InnoDB表，我们一般都会定义一个自增的ID列为主键（主键列不要选没有意义的自增列，选经常查询的条件列才好，不然无法体现其主键索引性能）
更新主键的代价很高，因为将会导致被更新的行移动。因此，对于InnoDB表，我们一般定义主键为不可更新。
二级索引访问需要两次索引查找，第一次找到主键值，第二次根据主键值找到行数据。

InnoDB辅助索引的访问需要两次索引查找，第一次从辅助索引树找到主键值，第二次根据主键值到主键索引树中找到对应的行数据。

MyISM使用的是非聚簇索引，表数据存储在独立的地方，这两棵（主键和辅助键）B+树的叶子节点都使用一个地址指向真正的表数据。由于索引树是独立的，通过辅助键检索无需访问主键的索引树。

假想一个表如下图存储了4行数据。其中Id作为主索引，Name作为辅助索引。图示清晰的显示了聚簇索引和非聚簇索引的差异。

在有辅助索引的查询时，聚集索引与非聚集索引的区别。

4.4 MySQL的索引下推（ICP）

五分钟搞懂MySQL索引下推_执行计划如何知道索引下推-CSDN博客

索引下推是指 Index Condition Pushdown。

索引下推(Index Condition Pushdown，简称ICP)，是MySQL5.6版本的新特性，它能减少回表查询次数，提高查询效率。

索引下推的下推其实就是指将部分上层（服务层）负责的事情，交给了下层（引擎层）去处理。

4.5 Mysql 的回表是什么意思？

在MySQL中，当我们提到“回表”时，我们通常是指在使用了非主键索引进行查询后，MySQL需要再次回到主键索引树中查找完整的行数据的过程。这通常发生在非覆盖索引的查询中。当你查询用的是where id=x 时，那只需要扫描一遍主键索引，然后拿到相应数据，但是如果是查询的普通索引的话，那么会先扫描一次普通索引，拿到主键值，然后再去扫主键索引，拿到所需要的数据，这个过程叫做回表。

假设我们有一个名为users的表，该表有以下结构：

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

这里，id是主键，而age是一个非主键索引。

MySQL会首先使用idx_age索引来查找所有age为25的记录。但是，这个索引只包含age的值和对应的主键id。如果我们只需要age或id，那么MySQL可以直接从索引中获取这些值，而不需要再回表。但在这个查询中，我们使用了SELECT *，这意味着我们需要获取users表中的所有列。

因此，对于idx_age中找到的每一个id，MySQL都需要回到主键索引树中，根据这个id查找完整的行数据。这就是所谓的“回表”。

为了减少回表操作并提高查询性能，可以考虑以下策略：

使用覆盖索引：确保查询中需要的所有列都在索引中。这样，MySQL就可以直接从索引中获取所有需要的数据，而无需回表。覆盖索引（Covering Index）是一种特殊的索引类型，它包含了查询所需的所有数据列，因此，当执行查询时，数据库系统可以直接从索引中获取所需的数据，而无需再回表到原始数据中去获取额外的数据。换句话说，如果一个索引包含了查询所需要的所有列，那么该索引就被称为覆盖索引。使用覆盖索引可以显著提高查询性能，因为它减少了数据库系统需要执行的操作次数，避免了不必要的磁盘I/O操作。
优化查询：只选择你真正需要的列，而不是使用SELECT *。
考虑表的设计：有时，重新设计表结构或添加适当的索引可以大大提高查询性能。

5 真·其它问题

1、b+树索引如何保存在磁盘中

对与面向磁盘的关系型数据库来说，基本存储单位是page。page又包含一些元信息页，索引页，数据页。索引页一般只存储索引数据，数据页保存具体的表格行信息。一般来说，索引页的数量远少于数据页。读取数据时，如果根据索引来读取，只需要读取对应的索引页到内存，最后再把目标数据读到内存，即可经过少量IO来获取数据。