数据库原理

kenger 收录于 Find JOB

2024-03-12 约 13628 字预计阅读 28 分钟

事务

事务指的是满足 ACID 特性的一组操作，可以通过 Commit 提交一个事务，也可以使用 Rollback 进行回滚。

ACID

1. 原子性（Atomicity）

事务被视为不可分割的最小单元，事务的所有操作要么全部提交成功，要么全部失败回滚。

回滚可以用回滚日志（Undo Log）来实现，回滚日志记录着事务所执行的修改操作，在回滚时反向执行这些修改操作即可。

2. 一致性（Consistency）（事务前后一直）

数据库在事务执行前后都保持一致性状态。在一致性状态下，所有事务对同一个数据的读取结果都是相同的。

一个有意思的说法：AID都是手段，C是目的。再结合上这个回答言简意赅的表达。一致性究竟是什么就很清晰了，一致性是指：数据库状态与真实业务状态相一致。为了达成一致性，需要在事务中保证原子性、隔离性和持久性

分布式理论CAP理想的一致性模型是：当一条更新出现时，所有的观察者都能“看到”这个更新。

3. 隔离性（Isolation）（事务之间隔离）

一个事务所做的修改在最终提交以前，对其它事务是不可见的。

4. 持久性（Durability）

**一旦事务提交，则其所做的修改将会永远保存到数据库中。**即使系统发生崩溃，事务执行的结果也不能丢失。

MySQL事务一致性，原子性是如何实现的？

首先是通过锁和mvcc实现了执行过程中的一致性和原子性

事务的持久性是如何实现的？

使用Redo log保证了事务的持久性。当事务提交时，必须先将事务的所有日志写入日志文件进行持久化

并发一致性问题（丢脏不换）

丢失修改（同时修改被覆盖）

指一个事务的更新操作被另一个事务的更新操作替换。常见情况是一个事务先提交，然后另一个事务覆盖了它的修改。

读脏数据（读一次读到了一个失败的write的脏数据）

在不同的事务下，当前事务可以读取到另一个事务未提交的数据，导致读取到的数据不准确。

不可重复读（多次读，读到了一个成功的write的前后两次数据）

在一个事务内多次读取同一数据集合，但在事务结束前，另一个事务修改了数据，导致多次读取结果不一致。

幻影读（针对的集合等类型（属性变化）的不可重复读）

类似于不可重复读，但是是针对数据集合范围的操作。一个事务读取某个范围的数据，另一个事务在该范围内插入新数据，导致第一次和第二次读取结果不同。

并发不一致性问题的主要原因是事务隔离性的破坏，解决方法包括通过并发控制和事务隔离级别来保证数据的一致性。

锁

详见： MySQL 有哪些锁？ | 小林coding

分类

粒度

MySQL 中提供了两种封锁粒度：行级锁以及表级锁，页面锁。

表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。

行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。

页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。

应该尽量只锁定需要修改的那部分数据，而不是所有的资源。锁定的数据量越少，发生锁争用的可能就越小，系统的并发程度就越高。

行级锁的类型主要有三（四）类：

这个博客还行

InnoDB常用锁总结（行锁、间隙锁、临键锁、表锁） - Hello-Brand - 博客园

Record Lock，记录锁，也就是仅仅把一条记录锁上；（无法对记录进行修改）
- 记录锁一般在使用主键或者唯一索引进行查找时体现
Gap Lock，间隙锁，锁定一个范围，但是不包含记录本身；（记录之间不可以加入）
- 间隙锁通常在不使用唯一索引进行范围查找时出现DELETE FROM users WHERE id BETWEEN 7 AND 13;
- 普通索引作为查询条件，恒定间隙锁。
- 索引作为查询条件，并以范围取值时，产生间隙锁。
- 或者当唯一索引所查找的id是不存在的时候，\# 6是不存在的记录，间隙锁，锁住的区间为（5,7）,对应上面的前置条件 mysql> select * from userinfo where id = 6 for update;
Next-Key Lock：Record Lock + Gap Lock 的组合，锁定一个范围，并且锁定记录本身。（前面两个会和）
- 普通索引select for update，当目标id是存在的。
插入意向锁：一个事务在插入一条记录的时候，需要判断插入位置是否已被其他事务加了间隙锁（next-key lock 也包含间隙锁）。（插入意向锁名字虽然有意向锁，但是它并不是意向锁，它是一种特殊的间隙锁，属于行级别锁。）

注意：Next-Key Lock 是 Record Lock 和 Gap Lock 的组合，它既锁定了索引记录本身，也锁定了索引记录之间的间隙，这样可以有效地防止幻读等并发问题的发生。(也就是说，这些锁是针对索引进行了锁。 InnoDB 存储引擎的行级锁是通过索引来实现的)

间隙锁

Gap Lock 称为间隙锁，只存在于可重复读隔离级别，目的是为了解决可重复读隔离级别下幻读的现象。

假设，表中有一个范围 id 为（3，5）间隙锁，那么其他事务就无法插入 id = 4 这条记录了，这样就有效的防止幻读现象的发生。

间隙锁虽然存在 X 型间隙锁和 S 型间隙锁，但是并没有什么区别，间隙锁之间是兼容的，即两个事务可以同时持有包含共同间隙范围的间隙锁，并不存在互斥关系，因为间隙锁的目的是防止插入幻影记录而提出的。

Next-Key Lock

可以有多个事务同时对某个范围上next key锁

因为：间隙锁（Gap Lock）不是独占锁，它是一种特殊的锁类型，用于在数据库中保护索引范围而不是具体的数据行。而间隙锁与间隙锁之间是兼容的，所以所以两个事务中 select ... for update 语句并不会相互影响

Next-Key Lock 称为临键锁，是 Record Lock + Gap Lock 的组合，锁定一个范围，并且锁定记录本身。

假设，表中有一个范围 id 为（3，5] 的 next-key lock，那么其他事务即不能插入 id = 4 记录，也不能修改 id = 5 这条记录。

所以，next-key lock 即能保护该记录，又能阻止其他事务将新纪录插入到被保护记录前面的间隙中。

插入意向锁

一个事务在插入一条记录的时候，需要判断插入位置是否已被其他事务加了间隙锁（next-key lock 也包含间隙锁）。

如果有的话，插入操作就会发生阻塞，直到拥有间隙锁的那个事务提交为止（释放间隙锁的时刻），在此期间会生成一个插入意向锁，表明有事务想在某个区间插入新记录，但是现在处于等待状态。

那么「插入意向锁」锁住的就是一个点（不是一个间隙范围）。因而从这个角度来说，插入意向锁确实是一种特殊的间隙锁。

封锁类型

读写锁

X 锁（Exclusive互斥锁）：又称写锁，一个事务对数据对象 A 加了 X 锁，就可以对 A 进行读取和更新。加锁期间其它事务不能对 A 加任何锁。，期间其他事务不能对该数据对象加锁（其他事务不可读写）。
S 锁（Shared共享锁）：又称读锁，一个事务对数据对象 A 加了 S 锁，可以对 A 进行读取操作，但是不能进行更新操作。加锁期间其它事务能对 A 加 S 锁，但是不能加 X 锁，允许对数据对象进行读取操作，但不允许更新操作，期间其他事务可以对该数据对象加 S 锁，但不能加 X 锁。

因此，读写锁可以做到读读并行，但是无法做到写读、写写并行。

意向锁

（需要强调一下，意向锁是一种不与行级锁冲突表级锁，这一点非常重要。意向锁分为两种：）

我们先来看一下百度百科上对意向锁存在意义的描述：

如果另一个任务试图在该表级别上应用共享或排它锁，则受到由第一个任务控制的表级别意向锁的阻塞。第二个任务在锁定该表前不必检查各个页或行锁，而只需检查表上的意向锁。

比如某个表里面已经有行共享锁了，当前想要给该表加入独占锁（这俩锁互斥），那么不需要一行行的扫描，只需要判断意向锁就行。

需要知道意向锁之间的兼容互斥性：即意向锁之间是互相兼容的。意向锁不会与行级的共享 / 排他锁互斥！！！

意向共享锁（IS）意向排他锁（IX）

意向共享锁（IS） 兼容兼容

意向排他锁（IX） 兼容兼容

	意向共享锁（IS）	意向排他锁（IX）
意向共享锁（IS）	兼容	兼容
意向排他锁（IX）	兼容	兼容

意向锁（Intention Locks）支持多粒度封锁，引入了 IX/IS 锁。

IS 锁：表示事务想要在表中的某个数据行上加 S 锁。
IX 锁：表示事务想要在表中的某个数据行上加 X 锁。

意向锁的引入简化了对表级封锁的管理，使得事务能够更高效地加锁。

乐观锁与悲观锁

悲观锁：认为数据在并发环境下会发生冲突，因此在读取数据时会先加锁，确保其他事务无法修改数据，直到当前事务完成操作。
乐观锁：认为数据在大多数情况下不会发生冲突，所以在操作数据时不加锁，而是在更新数据时检查是否有其他事务对数据进行了修改，若无修改则更新成功，否则进行相应的处理。

Mysql是怎么加锁的

唯一索引等值查询（next-key怎么退化的）：

当查询的记录是「存在」的，在索引树上定位到这一条记录后，将该记录的索引中的 next-key lock 会退化成「记录锁」。
当查询的记录是「不存在」的，在索引树找到第一条大于该查询记录的记录后，将该记录的索引中的 next-key lock 会退化成「间隙锁」。

非唯一索引等值查询：

当查询的记录「存在」时，由于不是唯一索引，所以肯定存在索引值相同的记录，于是非唯一索引等值查询的过程是一个扫描的过程，直到扫描到第一个不符合条件的二级索引记录就停止扫描，然后在扫描的过程中，对扫描到的二级索引记录加的是 next-key 锁，而对于第一个不符合条件的二级索引记录，该二级索引的 next-key 锁会退化成间隙锁。同时，在符合查询条件的记录的主键索引上加记录锁。
当查询的记录「不存在」时，扫描到第一条不符合条件的二级索引记录，该二级索引的 next-key 锁会退化成间隙锁。因为不存在满足查询条件的记录，所以不会对主键索引加锁。

非唯一索引和主键索引的范围查询的加锁规则不同之处在于：

唯一索引在满足一些条件的时候，索引的 next-key lock 退化为间隙锁或者记录锁。
非唯一索引范围查询，索引的 next-key lock 不会退化为间隙锁和记录锁。

不走索引导致全表上锁

还有一件很重要的事情，在线上在执行 update、delete、select … for update 等具有加锁性质的语句，一定要检查语句是否走了索引，如果是全表扫描的话，会对每一个索引加 next-key 锁，相当于把整个表锁住了，这是挺严重的问题。

一般来说，增删改都会加锁，要谨慎考虑

事务隔离级别(两提两可)

未提交读 (READ UNCOMMITTED) 修改在事务中即使未提交，也对其他事务可见。

已提交读 (READ COMMITTED) 事务只能读取已提交的修改，未提交的修改对其他事务不可见。

可重复读 (REPEATABLE READ) 同一事务中多次读取数据结果一致。

可串行化 (SERIALIZABLE) 强制事务串行执行，防止并发一致性问题，需加锁实现。

对于「已读提交」和「可重复读」隔离级别的事务来说，它们是通过 Read View *来实现的，它们的区别在于创建 Read View 的时机不同，大家可以把 Read View 理解成一个数据快照，就像相机拍照那样，定格某一时刻的风景。*「读提交」隔离级别是在「每个语句执行前」都会重新生成一个 Read View，而「可重复读」隔离级别是「启动事务时」生成一个 Read View，然后整个事务期间都在用这个 Read View。

innodb数据行结构

数据行隐藏字段（这个隐藏字段的trx_id配置readview的四个字段实现了快照读）

在内部，InnoDB 存储引擎为每行数据添加了三个隐藏字段open in new window：

DB_TRX_ID（6字节）：表示最后一次插入或更新该行的事务 id。此外，delete 操作在内部被视为更新，只不过会在记录头 Record header 中的 deleted_flag 字段将其标记为已删除
DB_ROLL_PTR（7字节） 回滚指针，指向该行的 undo log 。如果该行未被更新，则为空
DB_ROW_ID（6字节）：如果没有设置主键且该表没有唯一非空索引时，InnoDB 会使用该 id 来生成聚簇索引

多版本并发控制 (MVCC)

多版本并发控制（MVCC，Multiversion concurrency control）可以看作是乐观控制的模式。

参考：InnoDB存储引擎对MVCC的实现 | JavaGuide

基本思想

背景：在实际场景中读操作往往多于写操作，因此又引入了读写锁来避免不必要的加锁操作，例如读和读没有互斥关系。读写锁中读和写操作仍然是互斥的，而 MVCC 利用了多版本的思想，写操作更新最新的版本快照，而读操作去读旧版本快照，没有互斥关系，这一点和 CopyOnWrite 类似。

在 MVCC 中事务的修改操作（DELETE、INSERT、UPDATE）会为数据行新增一个版本快照。

脏读和不可重复读最根本的原因是事务读取到其它事务未提交的修改。在事务进行读取操作时，为了解决脏读和不可重复读问题，MVCC 规定只能读取已经提交的快照。当然一个事务可以读取自身未提交的快照，这不算是脏读

MVCC （Multi-Version Concurrency Control, MVCC）利用多版本的思想，通过快照来实现并发控制，避免了不必要的加锁操作。事务的修改操作会为数据行创建新的版本快照，而读操作则可以读取旧版本快照。

Undo 日志（Undo log）

Undo 日志存储了数据行的多个版本快照，通过回滚指针将它们连接起来。INSERT、UPDATE、DELETE 操作都会创建日志，并记录事务版本号。

ReadView

用来判断当前版本数据的可见性。

MVCC 维护了一个 ReadView 结构，包含了当前系统未提交的事务列表和最小、最大事务版本号。在进行 SELECT 操作时，根据快照的事务版本号和 ReadView 进行判断，决定是否可以使用该快照。

可见性原理是

主要看行记录的字段trx_id和这几个字段的关系实现可见性

如果记录的 trx_id 值小于 Read View 中的 min_trx_id 值，表示这个版本的记录是在创建 Read View 前已经提交的事务生成的，所以该版本的记录对当前事务可见。
如果记录的 trx_id 值大于等于 Read View 中的 max_trx_id 值，表示这个版本的记录是在创建 Read View 后才启动的事务生成的，所以该版本的记录对当前事务不可见。
如果记录的 trx_id 值在 Read View 的
1 2

min_trx_id max_trx_id
之间，需要判断 trx_id 是否在 m_ids 列表中：
- 如果记录的 trx_id 在 m_ids 列表中，表示生成该版本记录的活跃事务依然活跃着（还没提交事务），所以该版本的记录对当前事务不可见。
- 如果记录的 trx_id 不在 m_ids列表中，表示生成该版本记录的活跃事务已经被提交，所以该版本的记录对当前事务可见。

GPT的解释

在MySQL的InnoDB存储引擎中，为了实现高并发性能和事务的隔离性，采用了多版本并发控制（Multi-Version Concurrency Control，MVCC）机制。接下来，我将简要解释一下MVCC、undo log和read view的概念及其工作原理。

MVCC（多版本并发控制）：MVCC是一种用于实现数据库事务隔离性的技术。它的基本思想是为每个事务操作的数据对象创建一个快照（snapshot），这样每个事务都在各自的快照上执行操作，而不是直接操作共享数据。这样可以避免事务之间的冲突，提高并发性能。在InnoDB中，MVCC主要通过undo log和read view来实现。
undo log（回滚日志）：为了实现MVCC，InnoDB需要在修改数据时保留数据的旧版本。当一个事务修改了一行数据时，InnoDB会将这行数据的旧版本存储在一个名为undo log的数据结构中。这样，其他事务就可以继续访问这行数据的旧版本，从而实现事务隔离。同时，如果一个事务需要回滚（rollback），undo log也可以用于恢复数据的原始状态。
read view（读视图）：在InnoDB中，为了确保一个事务在执行过程中看到的数据是一致的，需要创建一个名为read view的数据结构。read view记录了当前事务开始时所有活跃事务的ID，这样当前事务就可以根据这些ID来判断哪些数据行的版本是可见的。具体来说，如果一个数据行的版本是在当前事务开始之前创建的，并且创建它的事务已经提交，那么这个版本就是可见的。这样可以确保事务在执行过程中看到的数据是一致的，实现可重复读（repeatable read）隔离级别。

总之，InnoDB通过MVCC机制实现了高并发性能和事务隔离性。MVCC主要依赖于undo log和read view来保留数据的旧版本和确定数据的可见性。这些技术使得事务之间可以并发执行，而不会相互干扰。

不同隔离级别怎么实现的。

可重复读是如何工作的？

可重复读隔离级别是启动事务时生成一个 Read View，然后整个事务期间都在用这个 Read View。

读提交是如何工作的？

读提交隔离级别是在每次读取数据时，都会生成一个新的 Read View。

快照读与当前读

SELECT 操作是快照读，不需要加锁；读取某个时间节点的数据

**而INSERT、UPDATE、DELETE 操作需要加锁，以读取最新数据。**读取最新的

当前读和快照读分别是什么？如何解决幻读

当前读：在锁定读（使用锁隔离事物）的时候读到的是最新版本的数据select for update

通过 next-key lock（记录锁+间隙锁）方式解决了幻读，

**快照读：可重复读（repeatable-read）下 mvcc生效读取的是数据的快照，并不是最新版本的数据（未提交事物的数据 ** select 普通

快照读读情况下，mysql通过mvcc来避免幻读

MVCC原理

**隔离级别实现是通过「事务的 Read View 里的字段」和「记录中的两个隐藏列（trx_id 和 roll_pointer）」的比对，如果不满足可见行，就会顺着 undo log 版本链里找到满足其可见性的记录，从而控制并发事务访问同一个记录时的行为，**这就叫 MVCC（多版本并发控制）

update 如果没加索引会锁全表

InnoDB 存储引擎自己实现了行锁，通过 next-key 锁（记录锁和间隙锁的组合）来锁住记录本身和记录之间的“间隙”，防止其他事务在这个记录之间插入新的记录，从而避免了幻读现象。

当我们执行 update 语句时，实际上是会对记录加独占锁（X 锁）的

在 update 语句的 where 条件没有使用索引，就会全表扫描，于是就会对所有记录加上 next-key 锁（记录锁 + 间隙锁），相当于把整个表锁住了。

解决办法

update 语句必须满足如下条件之一才能执行成功：

使用 where，并且 where 条件中必须有索引列；
使用 limit；
同时使用 where 和 limit，此时 where 条件中可以没有索引列；

mysql死锁怎么办

设置事务等待锁的超时时间。当一个事务的等待时间超过该值后，就对这个事务进行回滚，于是锁就释放了，另一个事务就可以继续执行了。在 InnoDB 中，参数 innodb_lock_wait_timeout 是用来设置超时时间的，默认值时 50 秒。

当发生超时后，就出现下面这个提示：

开启主动死锁检测。主动死锁检测在发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。将参数 innodb_deadlock_detect 设置为 on，表示开启这个逻辑，默认就开启。

当检测到死锁后，就会出现下面这个提示：

上面这个两种策略是「当有死锁发生时」的避免方式。

关系数据库

函数依赖

记 A->B 表示 A 函数决定 B，也可以说 B 函数依赖于 A。

如果 {A1，A2，… ，An} 是关系的一个或多个属性的集合，该集合函数决定了关系的其它所有属性并且是最小的，那么该集合就称为键码。(Primary Key)

对于 A->B，如果能找到 A 的真子集 A’，使得 A’-> B，那么 A->B 就是部分函数依赖，否则就是完全函数依赖。

对于 A->B，B->C，则 A->C 是一个传递函数依赖

范式

范式理论旨在解决数据库中的异常情况，其中高级别的范式依赖于低级别的范式。第一范式是最低级别的范式。

第一范式 (1NF)

属性不可再分。

第二范式 (2NF)

每个非主属性完全依赖于键码（消除部分依赖）。（就是非主属性完全依赖于主关键字。）（必须有个建码能够完全区分每一行）

可以通过分解表来解决

第三范式 (3NF) [ 消除传递依赖 ]****

非主属性不传递依赖于键码。

需要注意的是，可以通过分解关系来满足第二范式和第三范式的要求。

其他问题

索引工作原理

索引的实现通常使用B树及其变种B+树

索引递增原理

一张表，里面有ID自增主键，当insert了17条记录之后，删除了第15,16,17条记录，再把Mysql重启，再insert一条记录，这条记录的ID是18还是15 ？

如果表的类型为
1
MyISAM
，ID为18

因为MyISAM表会把自增主键的最大ID记录到数据文件里，重启MySQL自增主键的最大ID也不会丢失
如果表的类型是
1
InnoDB
，ID是15

InnoDB表只是把自增主键的最大ID记录到内存中，所以重启数据库或者是对表进行OPTIMIZE操作，都会导致最大ID丢失

一张表最多创建多少索引？

任何标准表最多可以创建16个索引列。

字段类型

MySQL 字段类型可以简单分为三大类：

数值类型：整型（TINYINT、SMALLINT、MEDIUMINT、INT 和 BIGINT）、浮点型（FLOAT 和 DOUBLE）、定点型（DECIMAL）
字符串类型：CHAR、VARCHAR、TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT、TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB 等，最常用的是 CHAR 和 VARCHAR。
日期时间类型：YEAR、TIME、DATE、DATETIME 和 TIMESTAMP 等。

MySQL 基础架构

先连接，再查询（先查询，查不到就进行1语法分析，然后2mysql优化器优化执行方案，最后3执行器执行，从存储引擎获取数据）

， MySQL 主要由下面几部分构成：

连接器： 身份认证和权限相关(登录 MySQL 的时候)。
查询缓存： 执行查询语句的时候，会先查询缓存（MySQL 8.0 版本后移除，因为这个功能不太实用）。
分析器： 没有命中缓存的话，SQL 语句就会经过分析器，分析器说白了就是要先看你的 SQL 语句要干嘛，再检查你的 SQL 语句语法是否正确。
优化器： 按照 MySQL 认为最优的方案去执行。
执行器： 执行语句，然后从存储引擎返回数据。执行语句之前会先判断是否有权限，如果没有权限的话，就会报错。
插件式存储引擎：主要负责数据的存储和读取，采用的是插件式架构，支持 InnoDB、MyISAM、Memory 等多种存储引擎
- MySQL 存储引擎采用的是 插件式架构 ，支持多种存储引擎，我们甚至可以为不同的数据库表设置不同的存储引擎以适应不同场景的需要。存储引擎是基于表的，而不是数据库。

Mysql引擎

MySQL 当前默认的存储引擎是 InnoDB。并且，所有的存储引擎中只有 InnoDB 是事务性存储引擎，也就是说只有 InnoDB 支持事务。

MyISAM 和 InnoDB 有什么区别？

MySQL 5.5 版本之后，InnoDB 是 MySQL 的默认存储引擎。（InnoDB从社区转正了）

InnoDB 支持行级别的锁粒度，MyISAM 不支持，只支持表级别的锁粒度。

MyISAM 不提供事务支持。InnoDB 提供事务支持，实现了 SQL 标准定义了四个隔离级别。

MyISAM 不支持外键，而 InnoDB 支持。

MyISAM 不支持 MVCC，而 InnoDB 支持。

虽然 MyISAM 引擎和 InnoDB 引擎都是使用 B+Tree 作为索引结构，但是两者的实现方式不太一样。

MyISAM 不支持数据库异常崩溃后的安全恢复，而 InnoDB 支持。

InnoDB 的性能比 MyISAM 更强大。

考虑需求高低，灵活选择存储引擎

能否单独为一张表设置存储引擎？

可以。我们可以为不同的表设置不同的存储引擎

MySQL 的隔离级别是基于锁实现的吗？

MySQL 的隔离级别基于锁和 MVCC 机制共同实现的。

MySQL 的默认隔离级别是什么?

MySQL InnoDB 存储引擎的默认支持的隔离级别是 REPEATABLE-READ（可重读）

能用 MySQL 直接存储文件（比如图片）吗？

可以是可以，直接存储文件对应的二进制数据即可。不过，还是建议不要在数据库中存储文件，会严重影响数据库性能，消耗过多存储空间。建议用第三方对象存储服务OBS

MySQL 如何存储 IP 地址？

可以将 IP 地址转换成整形数据存储，性能更好，占用空间也更小。

msyql缓存查询原理

SQL 是查询语句（select 语句），MySQL 就会先去查询缓存（ Query Cache ）里查找缓存数据，看看之前有没有执行过这一条命令，这个查询缓存是以 key-value 形式保存在内存中的，key 为 SQL 查询语句，value 为 SQL 语句查询的结果。

只要一个表有更新操作，那么这个表的查询缓存就会被清空。

MySQL 8.0 版本直接将查询缓存删掉了，也就是说 MySQL 8.0 开始，执行一条 SQL 查询语句，不会再走到查询缓存这个阶段了。

优化器

优化器主要负责将 SQL 查询语句的执行方案确定下来，比如在表里面有多个索引的时候，优化器会基于查询成本的考虑，来决定选择使用哪个索引。

要想知道优化器选择了哪个索引，我们可以在查询语句最前面加个 explain 命令，这样就会输出这条 SQL 语句的执行计划

Mysql死锁问题

MySQL死锁的原因和处理方法

出现死锁的例子

 1
 2
 3
 4
 5
 6
 7
 8
 9
10


事务 a

表 t  id=100 更新  加行锁
表 t  id=200 更新  已加锁


事务 b

表 t  id=200 更新 加行锁
表 t  id=100 更新 已加锁

死锁与锁等待是两个概念
- 如未开启事务，多个客户端执行的insert操作
当多个事务同时持有和请求同一资源上的锁而产生循环依赖的时候就产生了死锁

办法

充分利用索引，优化索引，尽量把有风险的事务sql使用上覆盖索
kill id 杀死进程
拆分sql，严禁大事务
事务超时机制
死锁检测算法

mysql 的各种log

bin log作用是什么？

MySQL的bin log日志是用来记录MySQL中增删改时的记录日志。

当你的一条sql操作对数据库中的内容进行了更新，就会增加一条bin log日志。查询操作不会记录到bin log中。

bin log最大的用处就是进行主从复制，以及数据库的恢复。

binlog 是 MySQL 的 Server 层实现的日志，所有存储引擎都可以使用

常用于主从复制，全量恢复

redo log作用是什么？

redo log是一种基于磁盘的数据结构，用来在MySQL宕机情况下将不完整的事务执行数据纠正，

redo log 记录了此次事务「完成后」的数据状态，记录的是更新之后的值

redo log就是为了恢复更新了内存但是由于宕机等原因没有刷入磁盘中的那部分数据

实现了事务中的持久性，主要用于掉电等故障恢复；

为什么数据本身要写入磁盘，还要redo log

写入 redo log 的方式使用了追加操作，所以磁盘操作是顺序写，而写入数据需要先找到写入位置，然后才写到磁盘，所以磁盘操作是随机写。

磁盘的「顺序写」比「随机写」高效的多，因此 redo log 写入磁盘的开销更小。

redo log写满了日志，那就回重新回到文件头覆盖着再写一遍。

bin和redo的区别

日志是否全量：binlog 文件保存的是全量的日志，也就是保存了所有数据变更的情况，理论上只要记录在 binlog 上的数据，都可以恢复，所以如果不小心整个数据库的数据被删除了，得用 binlog 文件恢复数据。
用途不同：
- binlog 用于备份恢复、主从复制；
- redo log 用于掉电等故障恢复。
层级不同
- bin log是数据库生成的
- undo log 和 redo log 这两个日志都是 Innodb 存储引擎生成的。

undo log作用是什么？

undo log主要用来回滚到某一个版本，是一种逻辑日志。

undo log记录的是修改之前的数据，比如：当delete一条记录时，undolog中会记录一条对应的insert记录，从而保证能恢复到数据修改之前。在执行事务回滚的时候，就可以通过undo log中的记录内容并以此进行回滚。

存储的是执行前的记录，方便实现快照的历史记录

现了事务中的原子性，主要用于事务回滚和 MVCC。

Buffer Pool知识

结构

Buffer Pool 的作用是什么？

说明 Buffer Pool 的作用是在内存中缓存热点数据，以减少对磁盘的访问次数，提高数据库的性能和响应速度。
有了缓冲池后：
- 当读取数据时，如果数据存在于 Buffer Pool 中，客户端就会直接读取 Buffer Pool 中的数据，否则再去磁盘中读取。
- 当修改数据时，首先是修改 Buffer Pool 中数据所在的页，然后将其页设置为脏页，最后由后台线程将脏页写入到磁盘
以页为单位进行数据存储，和内存换页机制。

Buffer Pool 的命中率是什么？

解释 Buffer Pool 的命中率是指在数据访问中缓冲池命中数据的比例，高命中率表示大部分数据都在缓冲池中，减少了对磁盘的访问。
如何提高：
- LRU算法

Buffer Pool 中的脏页是什么？（WAL【Write-Ahead Logging】技术）

解释 Buffer Pool 中的脏页是指已经被修改但尚未写回磁盘的数据页，说明脏页对数据库性能的影响和处理方法。
为了解决宕机没有写入磁盘的问题，InnoDB 的更新操作采用的是 Write Ahead Log 策略，即先写日志，再写入磁盘，通过 redo log 日志让 MySQL 拥有了崩溃恢复能力。

mysql实际使用守则

MySQL开发 | 小熊学Java 全能学习+面试指南

其他问题

sql注入问题

定义： SQL 注入攻击是通过将恶意的 SQL 语句如添加、删除等插入到应用的输入参数中，经过后台解析后发送到数据库服务器上解析执行进行的攻击

为什么会出现SQL注入

Web 应用程序对于用户输入的数据和合法性没有严谨的判断，前端用户的输入直接传输给后端，攻击者通过构造不同的参数，形成不同的 SQL 语句来实现对数据库的任意操作。

SQL 注入产生需要满足两个条件：

参数用户可控：前端传给后端的参数内容是用户可以控制的
参数带入数据库查询：传入的参数直接拼接到SQL语句，且带入数据库查询

如何防止SQL注入

在开发时应该秉持一种外部参数皆不可信的原则来进行开发。

加强参数验证: 开发时，验证所有来自前端的输入，必须是符合要求的数据类型，符合指定规则的数据才允许继续往下执行。

预编译语句是一种在发送到数据库执行之前已经预先编译的SQL语句模板。与直接拼接SQL查询字符串不同，预编译语句将参数作为独立的输入，而不是将它们与SQL语句混合在一起。这样可以防止攻击者通过注入恶意SQL代码来执行攻击。

在Java中，可以使用PreparedStatement来创建预编译语句：

1
2
3
4
5


javaCopy codeString sql = "SELECT * FROM users WHERE username = ? AND password = ?";
PreparedStatement statement = connection.prepareStatement(sql);
statement.setString(1, username);
statement.setString(2, password);
ResultSet result = statement.executeQuery();

SQL语句参数化处理: 减少使用或不使用字符串拼接的方式执行SQL，而是将用户输入当着参数传给执行SQL的方法，如Django中的cursor.execute()函数就支持在SQL语句中使用占位符，将输入作为参数传递给方法执行。
存储过程: 使用存储过程也可以有效防止SQL注入，不过在存储过程中，需使用占位符，并且使用输入参数来预编译SQL语句后再执行。
目前很多框架都自带了防止，例如mybatis plus

SQL 和 NoSQL 有什么区别

	SQL 数据库	NoSQL 数据库
数据存储模型	结构化存储，具有固定行和列的表格	非结构化存储。文档：JSON 文档，键值：键值对，宽列：包含行和动态列的表，图：节点和边
发展历程	开发于 1970 年代，重点是减少数据重复	开发于 2000 年代后期，重点是提升可扩展性，减少大规模数据的存储成本
例子	Oracle、MySQL、Microsoft SQL Server、PostgreSQL	文档：MongoDB、CouchDB，键值：Redis、DynamoDB，宽列：Cassandra、 HBase，图表：Neo4j、 Amazon Neptune、Giraph
ACID 属性	提供原子性、一致性、隔离性和持久性 (ACID) 属性	通常不支持 ACID 事务，为了可扩展、高性能进行了权衡，少部分支持比如 MongoDB 。不过，MongoDB 对 ACID 事务的支持和 MySQL 还是有所区别的。
性能	性能通常取决于磁盘子系统。要获得最佳性能，通常需要优化查询、索引和表结构。	性能通常由底层硬件集群大小、网络延迟以及调用应用程序来决定。
扩展	垂直（使用性能更强大的服务器进行扩展）、读写分离、分库分表	横向（增加服务器的方式横向扩展，通常是基于分片机制）
用途	普通企业级的项目的数据存储	用途广泛比如图数据库支持分析和遍历连接数据之间的关系、键值数据库可以处理大量数据扩展和极高的状态变化
查询语法	结构化查询语言 (SQL)	数据访问语法可能因数据库而异

数据库高可用问题

不停机扩容

实际上，不停机扩容，实操起来是个非常麻烦而且很有风险的操作，当然，面试回答起来就简单很多。

第一阶段：在线双写，查询走老库

建立好新的库表结构，数据写入久库的同时，也写入拆分的新库
数据迁移，使用数据迁移程序，将旧库中的历史数据迁移到新库
使用定时任务，新旧库的数据对比，把差异补齐

第二阶段：在线双写，查询走新库

完成了历史数据的同步和校验
把对数据的读切换到新库

第三阶段：旧库下线

旧库不再写入新的数据
经过一段时间，确定旧库没有请求之后，就可以下线老库

读写分离和分库分表详解

读写分离

读写分离主要是为了将对数据库的读写操作分散到不同的数据库节点上。 这样的话，就能够小幅提升写性能，大幅提升读性能。

主节点是写，从节点是读，要保证数据一致性。

主从复制原理：

MySQL主从复制

主库将数据库中数据的变化写入到 binlog
从库连接主库
从库会创建一个 I/O 线程向主库请求更新的 binlog
主库会创建一个 binlog dump 线程来发送 binlog ，从库中的 I/O 线程负责接收
从库的 I/O 线程将接收的 binlog 写入到 relay log 中。
从库的 SQL 线程读取 relay log 同步数据本地（也就是再执行一遍 SQL ）

由于主从更新会有延迟，最终会出现主从延迟问题。

分库分表

分库就是将数据库中的数据分散到不同的数据库上，可以垂直分库，也可以水平分库。（业务拆分和表的页拆分）

垂直分库 就是把单一数据库按照业务进行划分，不同的业务使用不同的数据库，进而将一个数据库的压力分担到多个数据库。

水平分库 是把同一个表按一定规则拆分到不同的数据库中，每个库可以位于不同的服务器上，这样就实现了水平扩展，解决了单表的存储和性能瓶颈的问题。

分表就是对单表的数据进行拆分，可以是垂直拆分，也可以是水平拆分。（列拆分和行拆分）

垂直分表 是对数据表列的拆分，把一张列比较多的表拆分为多张表。

举个例子：我们可以将用户信息表中的一些列单独抽出来作为一个表。

水平分表 是对数据表行的拆分，把一张行比较多的表拆分为多张表，可以解决单一表数据量过大的问题。

如何对数据水平切分

常见的分片算法有：

哈希分片：求指定分片键的哈希，然后根据哈希值确定数据应被放置在哪个表中。哈希分片比较适合随机读写的场景，不太适合经常需要范围查询的场景。哈希分片可以使每个表的数据分布相对均匀，但对动态伸缩（例如新增一个表或者库）不友好。
范围分片：按照特定的范围区间（比如时间区间、ID 区间）来分配数据，比如将 id 为 1~299999 的记录分到第一个表， 300000~599999 的分到第二个表。范围分片适合需要经常进行范围查找且数据分布均匀的场景，不太适合随机读写的场景（数据未被分散，容易出现热点数据的问题）。
映射表分片：使用一个单独的表（称为映射表）来存储分片键和分片位置的对应关系。映射表分片策略可以支持任何类型的分片算法，如哈希分片、范围分片等。映射表分片策略是可以灵活地调整分片规则，不需要修改应用程序代码或重新分布数据。不过，这种方式需要维护额外的表，还增加了查询的开销和复杂度。
一致性哈希分片：将哈希空间组织成一个环形结构，将分片键和节点（数据库或表）都映射到这个环上，然后根据顺时针的规则确定数据或请求应该分配到哪个节点上，解决了传统哈希对动态伸缩不友好的问题。
地理位置分片：很多 NewSQL 数据库都支持地理位置分片算法，也就是根据地理位置（如城市、地域）来分配数据。
融合算法分片：灵活组合多种分片算法，比如将哈希分片和范围分片组合。

分库分表会带来什么问题呢？

join 操作：同一个数据库中的表分布在了不同的数据库中，导致无法使用 join 操作。

跨库聚合查询问题：分库分表会导致常规聚合查询操作，如 group by，order by 等变得异常复杂。

事务问题：同一个数据库中的表分布在了不同的数据库中，如果单个操作涉及到多个数据库，那么数据库自带的事务就无法满足我们的要求了。需要引入分布式事务

分布式 ID：分库之后，数据遍布在不同服务器上的数据库，数据库的自增主键已经没办法满足生成的主键唯一了。我们如何为不同的数据节点生成全局唯一主键

数据库运维问题

如何删除一张表里面的百万级别的数据

先删除索引（因为索引会导致删除很慢）
删除数据
重建索引

目录

数据库原理

事务

ACID

1. 原子性（Atomicity）

2. 一致性（Consistency）（事务前后一直）

3. 隔离性（Isolation）（事务之间隔离）

4. 持久性（Durability）

MySQL事务一致性，原子性是如何实现的？

事务的持久性是如何实现的？

并发一致性问题（丢脏不换）

丢失修改（同时修改被覆盖）

读脏数据（读一次读到了一个失败的write的脏数据）

不可重复读（多次读，读到了一个成功的write的前后两次数据）

幻影读（针对的集合等类型（属性变化）的不可重复读）

锁

粒度

行级锁的类型主要有三（四）类：

间隙锁

Next-Key Lock

插入意向锁

封锁类型

读写锁

意向锁

乐观锁与悲观锁

Mysql是怎么加锁的

不走索引导致全表上锁

事务隔离级别(两提两可)

innodb数据行结构

多版本并发控制 (MVCC)

GPT的解释

不同隔离级别怎么实现的。

可重复读是如何工作的？

读提交是如何工作的？

快照读与当前读

当前读和快照读分别是什么？如何解决幻读

MVCC原理

update 如果没加索引会锁全表

mysql死锁怎么办

关系数据库

函数依赖

范式

第一范式 (1NF)

第二范式 (2NF)

第三范式 (3NF)** [ 消除传递依赖 ]**

其他问题

索引工作原理

索引递增原理

一张表最多创建多少索引？

字段类型

MySQL 基础架构

Mysql引擎

MyISAM 和 InnoDB 有什么区别？

能否单独为一张表设置存储引擎？

MySQL 的隔离级别是基于锁实现的吗？

MySQL 的默认隔离级别是什么?

能用 MySQL 直接存储文件（比如图片）吗？

MySQL 如何存储 IP 地址？

msyql缓存查询原理

优化器

Mysql死锁问题

MySQL死锁的原因和处理方法

mysql 的各种log

bin log作用是什么？

redo log作用是什么？

undo log作用是什么？

Buffer Pool知识

mysql实际使用守则

其他问题

sql注入问题

如何防止SQL注入

SQL 和 NoSQL 有什么区别

数据库高可用问题

不停机扩容

读写分离和分库分表详解

读写分离

分库分表

如何对数据水平切分

分库分表会带来什么问题呢？

数据库运维问题

第三范式 (3NF) [ 消除传递依赖 ]****