《姜承尧的 MySQL 实战宝典》笔记

拉勾教育教程 - 姜承尧的 MySQL 实战宝典 学习笔记

MySQL 面试之事务和锁篇

MySQL 事务

扩展阅读：

• MySQL 官方文档之 InnoDB 锁和事务模型

【基础】什么是事务，什么是 ACID？

:::details 要点

事务指的是满足 ACID 特性的一组操作。事务内的 SQL 语句，要么全执行成功，要么全执行失败。可以通过 Commit 提交一个事务，也可以使用 Rollback 进行回滚。通俗来说，事务就是要保证一组数据库操作，要么全部成功，要么全部失败。

ACID 是数据库事务正确执行的四个基本要素。

• 原子性（Atomicity）
  • 事务被视为不可分割的最小单元，事务中的所有操作要么全部提交成功，要么全部失败回滚。
  • 回滚可以用日志来实现，日志记录着事务所执行的修改操作，在回滚时反向执行这些修改操作即可。
• 一致性（Consistency）
  • 数据库在事务执行前后都保持一致性状态。
  • 在一致性状态下，所有事务对一个数据的读取结果都是相同的。
• 隔离性（Isolation）
  • 一个事务所做的修改在最终提交以前，对其它事务是不可见的。
• 持久性（Durability）
  • 一旦事务提交，则其所做的修改将会永远保存到数据库中。即使系统发生崩溃，事务执行的结果也不能丢失。
  • 可以通过数据库备份和恢复来实现，在系统发生奔溃时，使用备份的数据库进行数据恢复。

一个支持事务（Transaction）中的数据库系统，必需要具有这四种特性，否则在事务过程（Transaction processing）当中无法保证数据的正确性。

:::

【中级】事务存在哪些并发一致性问题？

:::details 要点

事务中存在的并发一致性问题有：

• 丢失修改
• 脏读
• 不可重复读
• 幻读

“丢失修改”是指一个事务的更新操作被另外一个事务的更新操作替换。

如下图所示，T₁ 和 T₂ 两个事务对同一个数据进行修改，T₁ 先修改，T₂ 随后修改，T₂ 的修改覆盖了 T₁ 的修改。

“脏读（dirty read）”是指当前事务可以读取其他事务未提交的数据。

如下图所示，T₁ 修改一个数据，T₂ 随后读取这个数据。如果 T₁ 撤销了这次修改，那么 T₂ 读取的数据是脏数据。

“不可重复读（non-repeatable read）”是指一个事务内多次读取同一数据，过程中，该数据被其他事务所修改，导致当前事务多次读取的数据可能不一致。

如下图所示，T₂ 读取一个数据，T₁ 对该数据做了修改。如果 T₂ 再次读取这个数据，此时读取的结果和第一次读取的结果不同。

“幻读（phantom read）”是指一个事务内多次读取同一范围的数据，过程中，其他事务在该数据范围新增了数据，导致当前事务未发现新增数据。

事务 T₁ 读取某个范围内的记录时，事务 T₂ 在该范围内插入了新的记录，T₁ 再次读取这个范围的数据，此时读取的结果和和第一次读取的结果不同。

:::

【中级】长事务可能会导致哪些问题？

:::details 要点

长事务可能会导致以下问题：

• 锁竞争与资源阻塞
  • 长事务长时间持有锁，导致其他事务阻塞，增加系统等待时间，降低并发性能。
  • 业务线程因数据库请求等待而阻塞，可能引发连锁反应（如服务雪崩），造成严重线上事故。
• 死锁风险增加 - 多个长事务互相等待对方释放锁，容易形成死锁，导致系统无法正常执行。
• 主从延迟问题 - 主库执行时间长，从库同步及重放耗时增加，导致主从数据长时间不一致。
• 回滚效率低下 - 长事务执行中途失败时，回滚操作会浪费已执行的资源与时间，影响系统效率。

:::

【高级】事务的二阶段提交是什么？

:::details 要点

事务的二阶段提交确保 redo log（重做日志） 和 binlog（二进制日志） 的一致性，防止崩溃恢复时出现数据丢失或不一致。

两阶段流程

• Prepare 阶段（准备阶段） - InnoDB 写入 redo log，并标记为 prepare 状态（事务预提交，但未最终提交）。
• Commit 阶段（提交阶段） - MySQL Server 写入 binlog（记录 DML 操作）。binlog 写入成功后，InnoDB 将 redo log 状态改为 commit，完成事务提交。

:::

:::details 细节

binlog 和 redo log 的区别

为什么需要二阶段提交？

无论是单独先写 redo log 或先写 binlog，都可能导致数据不一致：

1. 先写 redo log，后写 binlog（宕机时 binlog 未写入） - redo log 恢复数据，但 binlog 缺失该事务 → 主从数据不一致。
2. 先写 binlog，后写 redo log（宕机时 redo log 未写入） - binlog 有记录，但 redo log 未提交 → 数据库实际数据丢失，与 binlog 不一致。

为了解决以上问题，所以需要事务二阶段提交（repare → commit），以确保写入两日志的原子性。

二阶段提交如何保证一致性？

MySQL 崩溃恢复时，检查两日志状态：

• redo log prepare，binlog 未写入 - 直接回滚（两日志均无有效记录）。
• redo log prepare，binlog 已写入 - 对比两日志数据：
  • 一致：提交事务（redo log commit）。
  • 不一致：回滚事务（保证数据一致）。

:::

【中级】有哪些事务隔离级别，分别解决了什么问题？

:::details 要点

为了解决以上提到的并发一致性问题，SQL 标准提出了四种“事务隔离级别”来应对这些问题。事务隔离级别等级越高，越能保证数据的一致性和完整性，但是执行效率也越低。因此，设置数据库的事务隔离级别时需要做一下权衡。

事务隔离级别从低到高分别是：

• “读未提交（read uncommitted）” - 是指，事务中的修改，即使没有提交，对其它事务也是可见的。
• “读已提交（read committed）” ** - 是指，事务提交后，其他事务才能看到它的修改**。换句话说，一个事务所做的修改在提交之前对其它事务是不可见的。
  • 读已提交解决了脏读的问题。
  • 读已提交是大多数数据库的默认事务隔离级别，如 Oracle。
• “可重复读（repeatable read）” - 是指：保证在同一个事务中多次读取同样数据的结果是一样的。
  • 可重复读解决了不可重复读问题。
  • 可重复读是 InnoDB 存储引擎的默认事务隔离级别。
• “串行化（serializable ）” - 是指，强制事务串行执行，对于同一行记录，加读写锁，一旦出现锁冲突，必须等前面的事务释放锁。
  • 串行化解决了幻读问题。由于强制事务串行执行，自然避免了所有的并发问题。
  • 串行化策略会在读取的每一行数据上都加锁，这可能导致大量的超时和锁竞争。这对于高并发应用基本上是不可接受的，所以一般不会采用这个级别。

事务隔离级别对并发一致性问题的解决情况：

:::

【中级】MySQL 的默认事务隔离级别是什么？为什么？

:::details 要点

事务隔离级别等级越高，越能保证数据的一致性和完整性，但是执行效率也越低。因此，设置数据库的事务隔离级别时需要做一下权衡。

MySQL 中的事务功能是在存储引擎层实现的，并非所有存储引擎都支持事务功能。比如 MyISAM 引擎就不支持事务，这也是 MyISAM 被 InnoDB 取代的重要原因之一。

大部分数据库的默认隔离级别是“读已提交”。然而，InnoDB 的默认隔离级别是“可重复读”。这是为了兼容早期 binlog 的 statement 格式问题。如果使用可重复读以下的隔离级别，使用了 statement 格式的 binlog 会产生主从数据不一致的问题。

此外，在 InnoDB 中，可重复读隔离级别虽然不能解决幻读，但是可以很大程度的避免幻读的发生。根据不同的查询方式，分别提出了避免幻读的方案：

• 针对快照读（普通 select 语句），通过 MVCC 方式解决了幻读，因为可重复读隔离级别下，事务执行过程中看到的数据，一直跟这个事务启动时看到的数据是一致的，即使中途有其他事务插入了一条数据，是查询不出来这条数据的，所以就很好了避免幻读问题。
• 针对当前读（select ... for update 等语句），通过 Next-Key Lock（记录锁+间隙锁）方式解决了幻读，因为当执行 select … for update 语句的时候，会加上 Next-Key Lock，如果有其他事务在 Next-Key Lock 锁范围内插入了一条记录，那么这个插入语句就会被阻塞，无法成功插入，所以就很好了避免幻读问题。

很大程度的避免幻读，不代表完全解决幻读问题，下面是两个示例：

• 对于快照读，MVCC 并不能完全避免幻读现象。因为当事务 A 更新了一条事务 B 插入的记录，那么事务 A 前后两次查询的记录条目就不一样了，所以就发生幻读。
• 对于当前读，如果事务开启后，并没有执行当前读，而是先快照读，然后这期间如果其他事务插入了一条记录，那么事务后续使用当前读进行查询的时候，就会发现两次查询的记录条目就不一样了，所以就发生幻读。

:::

【高级】MySQL 是如何实现事务的？

:::details 要点

MySQL 主要是通过 锁、Redo Log 、Undo Log、MVCC 来实现事务。

• MySQL 利用锁（行锁、间隙锁等等）机制，控制数据的并发修改，满足事务的隔离性。
• Redo Log（重做日志），它会记录事务对数据库的所有修改，当 MySQL 发生宕机或崩溃时，通过重放 redo log 就可以恢复数据，用来满足事务的持久性。
• Undo Log（回滚日志），它会记录事务的反向操作，简单地说就是保存数据的历史版本，用于事务的回滚，使得事务执行失败之后可以恢复之前的样子。实现原子性和隔离性
• MVCC（多版本并发控制），满足了非锁定读的需求，提高了并发度，实现了读已提交和可重复读两种隔离级别，实现了事务的隔离性。

事务实现了原子性、隔离性和持久性特性后，本身就达到了一致性的目的。

:::

【高级】各事务隔离级别是如何实现的？

:::details 要点

四种隔离级别具体是如何实现的呢？

以 InnoDB 的事务实现来说明：

• 对于“读未提交”隔离级别的事务来说，因为可以读到未提交事务修改的数据，所以直接读取最新的数据就好了；
• 对于“串行化”隔离级别的事务来说，通过加读写锁的方式来避免并行访问；
• 对于“读提交”和“可重复读”隔离级别的事务来说，它们都是通过 ReadView 来实现的，区别仅在于创建 ReadView 的时机不同。ReadView 可以理解为一个数据快照。
  • “读提交”隔离级别是在“每个语句执行前”都会重新生成一个 ReadView
  • “可重复读”隔离级别是在“启动事务时”生成一个 ReadView，然后整个事务期间都在用这个 ReadView。

:::

【中级】什么是 MVCC？

:::details 要点

MVCC 是 Multi Version Concurrency Control 的缩写，即“多版本并发控制”。MVCC 的设计目标是提高数据库的并发性，采用非阻塞的方式去处理读/写并发冲突，可以将其看成一种乐观锁。

不仅是 MySQL，包括 Oracle、PostgreSQL 等其他关系型数据库都实现了各自的 MVCC，实现机制没有统一标准。MVCC 是 InnoDB 存储引擎实现事务隔离级别的一种具体方式。其主要用于实现读已提交和可重复读这两种隔离级别。而未提交读隔离级别总是读取最新的数据行，要求很低，无需使用 MVCC。可串行化隔离级别需要对所有读取的行都加锁，单纯使用 MVCC 无法实现。

:::

【高级】MVCC 的实现原理是什么？

:::details 要点

MVCC 的实现原理，主要基于隐式字段、UndoLog、ReadView 来实现。

隐式字段

InnoDB 存储引擎中，数据表的每行记录，除了用户显示定义的字段以外，还有几个数据库隐式定义的字段：

• row_id - 隐藏的自增 ID。如果数据表没有指定主键，InnoDB 会自动基于 row_id 产生一个聚簇索引。
• trx_id - 最近修改的事务 ID。事务对某条聚簇索引记录进行改动时，就会把该事务的事务 id 记录在 trx_id 隐藏列里；
• roll_pointer - 回滚指针，指向这条记录的上一个版本。

UndoLog

MVCC 的多版本指的是多个版本的快照，快照存储在 UndoLog 中。该日志通过回滚指针 roll_pointer 把一个数据行的所有快照链接起来，构成一个版本链。

ReadView

ReadView 就是事务进行快照读时产生的读视图（快照）。

ReadView 有四个重要的字段：

• m_ids - 指的是在创建 ReadView 时，当前数据库中“活跃事务”的事务 ID 列表。注意：这是一个列表，“活跃事务”指的就是，启动了但还没提交的事务。
• min_trx_id - 指的是在创建 ReadView 时，当前数据库中“活跃事务”中事务 id 最小的事务，也就是 m_ids 的最小值。
• max_trx_id - 这个并不是 m_ids 的最大值，而是指创建 ReadView 时当前数据库中应该给下一个事务分配的 ID 值，也就是全局事务中最大的事务 ID 值 + 1；
• creator_trx_id - 指的是创建该 ReadView 的事务的事务 ID。

在创建 ReadView 后，我们可以将记录中的 trx_id 划分为三种情况：

• 已提交事务
• 已启动但未提交的事务
• 未启动的事务

ReadView 如何判断版本链中哪个版本可见？

一个事务去访问记录的时候，除了自己的更新记录总是可见之外，还有这几种情况：

• trx_id == creator_trx_id - 表示 trx_id 版本记录由 ReadView 所代表的当前事务产生，当然可以访问。
• trx_id < min_trx_id - 表示 trx_id 版本记录是在创建 ReadView 之前已提交的事务生成的，当前事务可以访问。
• trx_id >= max_trx_id - 表示 trx_id 版本记录是在创建 ReadView 之后才启动的事务生成的，当前事务不可以访问。
• min_trx_id <= trx_id < max_trx_id - 需要判断 trx_id 是否在 m_ids 列表中
  • 如果 trx_id 在 m_ids 列表中，表示生成 trx_id 版本记录的事务依然活跃（未提交事务），当前事务不可以访问。
  • 如果 trx_id 不在 m_ids 列表中，表示生成 trx_id 版本记录的事务已提交，当前事务可以访问。

这种通过“版本链”来控制并发事务访问同一个记录时的行为就叫 MVCC（多版本并发控制）。

:::

【高级】MVCC 实现了哪些隔离级别，如何实现的？

:::details 要点

对于“读已提交”和“可重复读”隔离级别的事务来说，它们都是通过 MVCC 的 ReadView 机制来实现的，区别仅在于创建 ReadView 的时机不同。ReadView 可以理解为一个数据快照。

MVCC 如何实现可重复读隔离级别

可重复读隔离级别只有在启动事务时才会创建 ReadView，然后整个事务期间都使用这个 ReadView。这样就保证了在事务期间读到的数据都是事务启动前的记录。

举个例子，假设有两个事务依次执行以下操作：

• 初始，表中 id = 1 的 value 列值为 100。
• 事务 2 读取数据，value 为 100；
• 事务 1 将 value 设为 200；
• 事务 2 读取数据，value 为 100；
• 事务 1 提交事务；
• 事务 2 读取数据，value 依旧为 100；

以上操作，如下图所示。T2 事务在事务过程中，是否可以看到 T1 事务的修改，可以根据 ReadView 中描述的规则去判断。

从图中不难看出：

• 对于 trx_id = 100 的版本记录，比对 T2 事务 ReadView ，trx_id < min_trx_id，因此在 T2 事务中的任意时刻都可见；
• 对于 trx_id = 101 的版本记录，比对 T2 事务 ReadView ，可以看出 min_trx_id <= trx_id < max_trx_id ，且 trx_id 在 m_ids 中，因此 T2 事务中不可见。

综上所述，在 T2 事务中，自始至终只能看到 trx_id = 100 的版本记录。

MVCC 如何实现读已提交隔离级别

读已提交隔离级别每次读取数据时都会创建一个 ReadView。这意味着，事务期间的多次读取同一条数据，前后读取的数据可能会出现不一致——因为，这期间可能有另外一个事务修改了该记录，并提交了事务。

举个例子，假设有两个事务依次执行以下操作：

• 初始，表中 id = 1 的 value 列值为 100。
• 事务 2 读取数据（创建 ReadView），value 为 0；
• 事务 1 将 value 设为 100；
• 事务 2 读取数据（创建 ReadView），value 为 0；
• 事务 1 提交事务；
• 事务 2 读取数据（创建 ReadView），value 为 100；

以上操作，如下图所示，T2 事务在事务过程中，是否可以看到其他事务的修改，可以根据 ReadView 中描述的规则去判断。

从图中不难看出：

• 对于 trx_id = 100 的版本记录，比对 T2 事务 ReadView ，trx_id < min_trx_id，因此在 T2 事务中的任意时刻都可见；
• 对于 trx_id = 101 的版本记录，比对 T2 事务 ReadView ，可以看出第二次查询时（T1 更新未提交），min_trx_id <= trx_id < max_trx_id ，且 trx_id 在 m_ids 中，因此 T2 事务中不可见；而第三次查询时（T1 更新已提交），trx_id < min_trx_id，因此在 T2 事务中可见；

综上所述，在 T2 事务中，当 T1 事务提交前，可读取到的是 trx_id = 100 的版本记录；当 T1 事务提交后，可读取到的是 trx_id = 101 的版本记录。

MVCC + Next-Key Lock 如何解决幻读

MySQL InnoDB 引擎的默认隔离级别虽然是“可重复读”，但是它很大程度上避免幻读现象（并不是完全解决了），解决的方案有两种：

• 针对快照读（普通 SELECT 语句），通过 MVCC 方式解决了幻读，因为可重复读隔离级别下，事务执行过程中看到的数据，一直跟这个事务启动时看到的数据是一致的，即使中途有其他事务插入了一条数据，是查询不出来这条数据的，所以就很好了避免幻读问题。
• 针对当前读（SELECT ... FOR UPDATE 等语句），通过 Next-Key Lock（记录锁+间隙锁）方式解决了幻读，因为当执行 SELECT ... FOR UPDATE 语句的时候，会加上 Next-Key Lock，如果有其他事务在 Next-Key Lock 锁范围内插入了一条记录，那么这个插入语句就会被阻塞，无法成功插入，所以就很好的避免了幻读问题。

:::

MySQL 锁

【中级】MySQL 中有哪些锁？

:::details 要点

为了解决并发一致性问题，MySQL 支持了很多种锁来实现不同程度的隔离性，以保证数据的安全性。

• 独享锁和共享锁
• 悲观锁和乐观锁
• 全局锁 - 锁定整个数据库。典型应用是全库逻辑备份。
• 表级锁
  • 表锁 - 表锁就是对数据表进行锁定，锁定粒度很大，同时发生锁冲突的概率也会较高，数据访问的并发度低。
  • 元数据锁（Metadata Lock, MDL） - 用于保护表结构变更，MDL 不需要显式使用，在访问一个表的时候会被自动加上。
    • 增删改查，加读锁
    • 结构变更，加写锁
  • 意向锁（Intention Lock） - 表明事务打算在表中的行上获取什么类型的锁。
    • 意向共享锁 (IS)
    • 意向排他锁 (IX)
  • 自增锁（Auto Increment Lock）
• 行级锁
  • 记录锁（Record Lock） - 锁定索引中的单条记录。
  • 间隙锁（Gap Lock） - 锁定索引记录之间的间隙。
  • 临键锁（Next-Key Lock） - 记录锁+间隙锁的组合。
  • 插入意向锁（Insert Intention Lock） - INSERT 操作设置的间隙锁。

:::

【中级】独享锁 vs. 共享锁？

:::details 要点

InnoDB 实现标准行级锁定，根据是否独享资源，可以把锁分为两类：

• 独享锁（Exclusive），简写为 X 锁，又称为“写锁”、“排它锁”。
  • 独享锁锁定的数据只允许进行锁定操作的事务使用，其他事务无法对已锁定的数据进行查询或修改。
  • 使用方式：SELECT ... FOR UPDATE;
• 共享锁（Shared），简写为 S 锁，又称为“读锁”。
  • 共享锁锁定的资源可以被其他用户读取，但不能修改。在进行 SELECT 的时候，会将对象进行共享锁锁定，当数据读取完毕之后，就会释放共享锁，这样就可以保证数据在读取时不被修改。
  • 使用方式：SELECT ... LOCK IN SHARE MODE;

为什么要引入读写锁机制？

实际上，读写锁是一种通用的锁机制，并非 MySQL 的专利。在很多软件领域，都存在读写锁机制。

因为读操作本身是线程安全的，而一般业务往往又是读多写少的情况。因此，如果对读操作进行互斥，是不必要的，并且会大大降低并发访问效率。正式为了应对这种问题，产生了读写锁机制。

读写锁的特点是：读读不互斥、读写互斥、写写互斥。简言之：只要存在写锁，其他事务就不能做任何操作。

注：InnoDB 下的行锁、间隙锁、next-key 锁统统属于独享锁。

:::

【中级】悲观锁 vs. 乐观锁？

:::details 要点

基于加锁方式分类，MySQL 可以分为悲观锁和乐观锁。

• 悲观锁 - 假定会发生并发冲突，屏蔽一切可能违反数据完整性的操作
  • 在查询完数据的时候就把事务锁起来，直到提交事务（COMMIT）
  • 实现方式：使用数据库中的锁机制。
• 乐观锁 - 假设最好的情况——每次访问数据时，都假设数据不会被其他线程修改，不必加锁。只在更新的时候，判断一下在此期间是否有其他线程更新该数据。
  • 实现方式：更新数据时，先使用版本号机制或 CAS 算法检查数据是否被修改。

为什么要引入乐观锁？

乐观锁也是一种通用的锁机制，在很多软件领域，都存在乐观锁机制。

锁，意味着互斥，意味着阻塞。在高并发场景下，锁越多，阻塞越多，势必会拉低并发性能。那么，为了提高并发度，能不能尽量不加锁呢？

乐观锁，顾名思义，就是假设最好的情况——每次访问数据时，都假设数据不会被其他线程修改，不必加锁。虽然不加锁，但不意味着什么都不做，而是在更新的时候，判断一下在此期间是否有其他线程更新该数据。乐观锁最常见的实现方式，是使用版本号机制或 CAS 算法（Compare And Swap）去实现。

【示例】MySQL 乐观锁示例

假设，order 表中有一个字段 status，表示订单状态：status 为 1 代表订单未支付；status 为 2 代表订单已支付。现在，要将 id 为 1 的订单状态置为已支付，则操作如下：

1
2
3
4
5

select status, version from order where id=#{id}

update order
set status=2, version=version+1
where id=#{id} and version=#{version};

乐观锁的优点是：减少锁竞争，提高并发度。

乐观锁的缺点是：

• 存在 ABA 问题。所谓的 ABA 问题是指在并发编程中，如果一个变量初次读取的时候是 A 值，它的值被改成了 B，然后又其他线程把 B 值改成了 A，而另一个早期线程在对比值时会误以为此值没有发生改变，但其实已经发生变化了
• 如果乐观锁所检查的数据存在大量锁竞争，会由于不断循环重试，产生大量的 CPU 开销。

:::

【中级】全局锁 vs. 表级锁 vs. 行级锁？

:::details 要点

前文提到了，锁，意味着互斥，意味着阻塞。在高并发场景下，锁越多，阻塞越多，势必会拉低并发性能。在不得不加锁的情况下，显然，加锁的范围越小，锁竞争的发生频率就越小，系统的并发程度就越高。但是，加锁也需要消耗资源，锁的各种操作（包括获取锁、释放锁、以及检查锁状态）都会增加系统开销，锁粒度越小，系统的锁操作开销就越大。因此，在选择锁粒度时，也需要在锁开销和并发程度之间做一个权衡。

根据加锁的范围，MySQL 的锁大致可以分为：

• 全局锁 - “全局锁”会锁定整个数据库。
• 表级锁（table lock） - “表级锁”锁定整张表。用户对表进行写操作前，需要先获得写锁，这会阻塞其他用户对该表的所有读写操作。只有没有写锁时，其他用户才能获得读锁，读锁之间不会相互阻塞。表级锁有：
  • 表锁 - 表锁就是对数据表进行锁定，锁定粒度很大，同时发生锁冲突的概率也会较高，数据访问的并发度低。
  • 元数据锁（MDL） - MDL 不需要显式使用，在访问一个表的时候会被自动加上。
    • 增删改查，加读锁
    • 结构变更，加写锁
  • 意向锁（Intention Lock）
  • 自增锁（AUTO-INC）
• 行级锁（row lock） - “行级锁”锁定指定的行记录。这样其它线程还是可以对同一个表中的其它行记录进行操作。行级锁有：
  • 记录锁（Record Lock）
  • 间隙锁（Gap Lock）
  • 临键锁（Next-Key Lock）
  • 插入意向锁

以上各种加锁粒度，在不同存储引擎中的支持情况并不相同。如：InnoDB 支持全局锁、表级锁、行级锁；而 MyISAM 只支持全局锁、表级锁。

每个层级的锁数量是有限制的，因为锁会占用内存空间，锁空间的大小是有限的。当某个层级的锁数量超过了这个层级的阈值时，就会进行锁升级。锁升级就是用更大粒度的锁替代多个更小粒度的锁，比如 InnoDB 中行锁升级为表锁，这样做的好处是占用的锁空间降低了，但同时数据的并发度也下降了。

:::

【中级】死锁是如何产生的？

:::details 要点

“死锁”是指两个或多个事务竞争同一资源，并请求锁定对方占用的资源，从而导致恶性循环的现象。

产生死锁的场景：

• 多个事务以不同的顺序锁定资源，可能会产生死锁。
• 多个事务同时锁定同一个资源时，也会产生死锁。

:::

【高级】如何避免死锁？

:::details 要点

死锁的四个必要条件：互斥、占有且等待、不可强占用、循环等待。只要系统发生死锁，这些条件必然成立，但是只要破坏任意一个条件就死锁就不会成立。由此可知，要想避免死锁，就要从这几个必要条件上去着手：

• 更新表时，尽量使用主键更新，减少冲突；
• 避免长事务，尽量将长事务拆解，可以降低与其它事务发生冲突的概率；
• 设置合理的锁等待超时参数，我们可以通过 innodb_lock_wait_timeout 设置合理的等待超时阈值，特别是在一些高并发的业务中，我们可以尽量将该值设置得小一些，避免大量事务等待，占用系统资源，造成严重的性能开销。
• 在编程中尽量按照固定的顺序来处理数据库记录，假设有两个更新操作，分别更新两条相同的记录，但更新顺序不一样，有可能导致死锁；
• 在允许幻读和不可重复读的情况下，尽量使用读已提交事务隔离级别，可以避免 Gap Lock 导致的死锁问题；
• 还可以使用其它的方式来代替数据库实现幂等性校验。例如，使用 Redis 以及 ZooKeeper 来实现，运行效率比数据库更佳。

:::

【高级】如何解决死锁？

:::details 要点

当出现死锁以后，有两种策略：

• 设置事务等待锁的超时时间。这个超时时间可以通过参数 innodb_lock_wait_timeout 来设置。
• 开启死锁检测，发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。将参数 innodb_deadlock_detect 设置为 on，表示开启这个逻辑。

在 InnoDB 中，innodb_lock_wait_timeout 的默认值是 50s，意味着如果采用第一个策略，当出现死锁以后，第一个被锁住的线程要过 50s 才会超时退出，然后其他线程才有可能继续执行。对于在线服务来说，这个等待时间往往是无法接受的。但是，若直接把这个时间设置成一个很小的值，比如 1s，也是不可取的。当出现死锁的时候，确实很快就可以解开，但如果不是死锁，而是简单的锁等待呢？所以，超时时间设置太短的话，会出现很多误伤。

所以，正常情况下我们还是要采用第二种策略，即：主动死锁检测，而且 innodb_deadlock_detect 的默认值本身就是 on。为了解决死锁问题，不同数据库实现了各自的死锁检测和超时机制。InnoDB 的处理策略是：将持有最少行级排它锁的事务进行回滚。主动死锁检测在发生死锁的时候，是能够快速发现并进行处理的，但是它也是有额外负担的：每当事务被锁时，就要查看它所依赖的线程是否被其他事务锁住，如此循环，来判断是否出现了循环等待，也就是死锁。因此，死锁检测可能会耗费大量的 CPU。

:::

参考资料

• 《高性能 MySQL》
• 极客时间教程 - MySQL 实战 45 讲
• 图解 MySQL 介绍

MySQL 面试之索引篇

综合

【基础】什么是索引？为什么要使用索引？

:::details 要点

“索引”是数据库为了提高查找效率的一种数据结构。

日常生活中，我们可以通过检索目录，来快速定位书本中的内容。索引和数据表，就好比目录和书，想要高效查询数据表，索引至关重要。在数据量小且负载较低时，不恰当的索引对于性能的影响可能还不明显；但随着数据量逐渐增大，性能则会急剧下降。因此，设置合理的索引是数据库查询性能优化的最有效手段。

:::

【基础】索引的优点和缺点是什么？

:::details 要点

✔️️️️️️️ 索引的优点：

• 索引大大减少了服务器需要扫描的数据量，从而加快检索速度。
• 索引可以帮助服务器避免排序和临时表。
• 索引可以将随机 I/O 变为顺序 I/O。
• 支持行级锁的数据库，如 InnoDB 会在访问行的时候加锁。使用索引可以减少访问的行数，从而减少锁的竞争，提高并发。
• 唯一索引可以确保每一行数据的唯一性，通过使用索引，可以在查询的过程中使用优化隐藏器，提高系统的性能。

❌ 索引的缺点：

• 创建和维护索引要耗费时间，这会随着数据量的增加而增加。
• 索引需要占用额外的物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立组合索引那么需要的空间就会更大。
• 写操作（INSERT/UPDATE/DELETE）时很可能需要更新索引，导致数据库的写操作性能降低。

基于以上，可以归纳出索引的基本使用规则：

• 索引不是越多越好，不要为所有列都创建索引
• 要尽量避免冗余和重复索引
• 要考虑删除未使用的索引
• 尽量的扩展索引，不要新建索引
• 频繁作为 WHERE 过滤条件的列应该考虑添加索引

:::

【基础】何时适用索引？何时不适用索引？

:::details 要点

✔️️️️ 什么情况适用索引？

• 字段的数值有唯一性的限制，如用户名。
• 频繁作为 WHERE 条件或 JOIN 条件的字段，尤其在数据表大的情况下。
• 频繁用于 GROUP BY 或 ORDER BY 的字段。将该字段作为索引，查询时就无需再排序了，因为 B+ 树本身就是按序存储的。
• DISTINCT 字段需要创建索引。

❌ 什么情况不适用索引？

• 频繁写操作（ INSERT/UPDATE/DELETE ），也就意味着需要更新索引。
• 很少作为 WHERE 条件或 JOIN 条件的字段，也就意味着索引会经常无法命中，没有意义，还增加空间开销。
• 非常小的表，对于非常小的表，大部分情况下简单的全表扫描更高效。
• 特大型的表，建立和使用索引的代价将随之增长。可以考虑使用分区技术或 Nosql。

:::

【基础】索引有哪些分类？

:::details 要点

MySQL 索引可以从以下四个维度来分类：

• 按【数据结构】分类：B+tree 索引、Hash 索引、Full-text 索引
• 按【物理存储】分类：聚簇索引、二级索引（辅助索引）
• 按【字段特性】分类：主键索引、普通索引、前缀索引
• 按【字段个数】分类：单列索引、联合索引（复合索引、组合索引）

:::

【中级】创建索引有哪些基本原则？

:::details 要点

• 索引不是越多越好，不要为所有列都创建索引。要考虑到索引的维护代价、空间占用和查询时回表的代价。索引一定是按需创建的，并且要尽可能确保足够轻量。一旦创建了多字段的联合索引，我们要考虑尽可能利用索引本身完成数据查询，减少回表的成本。
• 要尽量避免冗余和重复索引。
• 要考虑删除未使用的索引。
• 尽量的扩展索引，不要新建索引。
• 频繁作为 WHERE 过滤条件的列应该考虑添加索引。

:::

【中级】哪些情况下，索引会失效？

:::details 要点

导致索引失效的情况有：

• 对索引使用左模糊匹配
• 对索引使用函数或表达式
• 对索引隐式类型转换
• 联合索引不遵循最左匹配原则
• 索引列判空 - 索引列与 NULL 或者 NOT NULL 进行判断的时候也会失效
• WHERE 子句中的 OR

:::

【基础】= 和 in 的顺序对于命中索引是否有影响？

:::details 要点

不需要考虑 =、IN 等的顺序，MySQL 会自动优化这些条件的顺序，以匹配尽可能多的索引列。

【示例】如有索引 (a, b, c, d)，查询条件 c > 3 and b = 2 and a = 1 and d < 4 与 a = 1 and c > 3 and b = 2 and d < 4 等顺序都是可以的，MySQL 会自动优化为 a = 1 and b = 2 and c > 3 and d < 4，依次命中 a、b、c、d。

:::

数据结构

【基础】索引有哪些常见数据结构？

:::details 要点

在 MySQL 中，索引是在存储引擎层而不是服务器层实现的，所以，并没有统一的索引标准。不同存储引擎的索引的数据结构也不相同。下面是 MySQL 常用存储引擎对一些主要索引数据结构的支持：

MySQL 索引的常见数据结构：

• 哈希索引
  • 因为索引数据结构紧凑，所以查询速度非常快。
  • 只支持等值比较查询 - 包括 =、IN()、<=>；不支持任何范围查询，如 WHERE price > 100。
  • 无法用于排序 - 因为哈希索引数据不是按照索引值顺序存储的。
  • 不支持部分索引匹配查找 - 因为哈希索引时使用索引列的全部内容来进行哈希计算的。如，在数据列 (A,B) 上建立哈希索引，如果查询只有数据列 A，无法使用该索引。
  • 不能用索引中的值来避免读取行 - 因为哈希索引只包含哈希值和行指针，不存储字段，所以不能使用索引中的值来避免读取行。不过，访问内存中的行的速度很快，所以大部分情况下这一点对性能影响不大。
  • 哈希索引有可能出现哈希冲突
    • 出现哈希冲突时，必须遍历链表中所有的行指针，逐行比较，直到找到符合条件的行。
    • 如果哈希冲突多的话，维护索引的代价会很高。
• B 树索引
  • 适用于全键值查找、键值范围查找和键前缀查找，其中键前缀查找只适用于最左前缀查找。
  • 所有的关键字（可以理解为数据）都存储在叶子节点，非叶子节点并不存储真正的数据，所有记录节点都是按键值大小顺序存放在同一层叶子节点上。
  • 所有的叶子节点由指针连接。

:::

【中级】为什么 InnoDB 采用 B+ 树索引？

:::details 要点

二叉搜索树的特点是：每个节点的左儿子小于父节点，父节点又小于右儿子。其查询时间复杂度是 $$O(log(N))$$。

当然为了维持 $$O(log(N))$$ 的查询复杂度，你就需要保持这棵树是平衡二叉树。为了做这个保证，更新的时间复杂度也是 $$O(log(N))$$。

随着数据库中数据的增加，索引本身大小随之增加，不可能全部存储在内存中，因此索引往往以索引文件的形式存储的磁盘上。这样的话，索引查找过程中就要产生磁盘 I/O 消耗，相对于内存存取，I/O 存取的消耗要高几个数量级。可以想象一下一棵几百万节点的二叉树的深度是多少？如果将这么大深度的一颗二叉树放磁盘上，每读取一个节点，需要一次磁盘的 I/O 读取，整个查找的耗时显然是不能够接受的。那么如何减少查找过程中的 I/O 存取次数？

一种行之有效的解决方法是减少树的深度，将二叉树变为 N 叉树（多路搜索树），而 B+ 树就是一种多路搜索树。

B+ 树索引适用于全键值查找、键值范围查找和键前缀查找，其中键前缀查找只适用于最左前缀查找。

理解 B+Tree 时，只需要理解其最重要的两个特征即可：

• 首先，所有的关键字（可以理解为数据）都存储在叶子节点，非叶子节点并不存储真正的数据，所有记录节点都是按键值大小顺序存放在同一层叶子节点上。
• 其次，所有的叶子节点由指针连接。如下图为简化了的 B+Tree。

:::

:::details 细节

B+ 树 vs B 树

• B+ 树只在叶子节点存储数据，而 B 树的非叶子节点也要存储数据，所以 B+ 树的单个节点的数据量更小，在相同的磁盘 I/O 次数下，就能查询更多的节点。
• 另外，B+ 树叶子节点采用的是双链表连接，适合 MySQL 中常见的基于范围的顺序查找，而 B 树无法做到这一点。

B+ 树 vs 二叉树

• 对于有 N 个叶子节点的 B+ 树，其搜索复杂度为 O(logdN)，其中 d 表示节点允许的最大子节点个数为 d 个。
• 在实际的应用当中， d 值是大于 100 的，这样就保证了，即使数据达到千万级别时，B+ 树的高度依然维持在 13 层左右，也就是说一次数据查询操作只需要做 13 次的磁盘 I/O 操作就能查询到目标数据。
• 而二叉树的每个父节点的儿子节点个数只能是 2 个，意味着其搜索复杂度为 O(logN)，这已经比 B+ 树高出不少，因此二叉树检索到目标数据所经历的磁盘 I/O 次数要更多。

一言以蔽之，使用 B+ 树，而不是二叉树，是为了减少树的高度，也就是为了减少磁盘 I/O 次数。

B+ 树索引和 Hash 索引的差异

• B+ 树索引支持范围查询；Hash 索引不支持。
• B+ 树索引支持联合索引的最左匹配原则；Hash 索引不支持。
• B+ 树索引支持排序；Hash 索引不支持。
• B+ 树索引支持模糊查询；Hash 索引不支持。
• Hash 索引的等值查询比 B+ 树索引效率高。

综上，Hash 索引的应用场景很苛刻，不适用于绝大多数场景。

:::

【中级】B+ 树索引能存多少数据？

:::details 要点

在 InnoDB 存储引擎中，B+ 树默认数据页大小为 16KB。

所有 B+ 树都是从高度为 1 的树开始，然后根据数据的插入，慢慢增加树的高度。随着插入 B+ 树索引的记录变多，1 个页（16K）无法存放这么多数据，所以会发生 B+ 树的分裂，B+ 树的高度变为 2。

非叶子节点可存储的记录数

根节点和中间节点存放的是索引键对，由（索引键、指针）组成。

索引键就是排序的列，而指针是指向下一层的地址，在 MySQL 的 InnoDB 存储引擎中占用 6 个字节。若主键是 BIGINT 类型，占 8 个字节。也即是说，这样的一个键值对需要 8+6 = 14 字节。

1

非叶子节点可存储的记录数 = 页大小（16K） / 键值对大小（14） ≈ 1100

叶子节点可存储的记录数

为了方便计算，假设数据记录的平均大小为 1000 字节（实际一般小于这个值），则

1

叶子节点可存储的记录数 = 页大小（16K） / 记录平均大小（1000） ≈ 16

由此可知，树高度为 2 的 B+ 树索引，有一个根节点，约 1100 个叶子节点。因此，最多能存放的记录数为：

1

二层 B+ 树记录数 ≈ 1100 * 16 =  16,100

如何推算不同高度的 B+ 树可存储的记录数

综上所述，数据记录的平均大小为 1000 字节，主键为 BIGINT 的表，可以按如下推算其可存储的记录数：

• 高度为 2 的 B+树索引最多能存放约 1100 * 16 = 16,100 条记录（约 1.6 万），查询时只需 2 次 I/O。
• 高度为 3 的 B+树索引最多能存放约 1100 * 1100 * 16 = 19,360,000 条记录（约 2 千万），查询时只需 3 次 I/O。
• 高度为 4 的 B+树索引最多能存放约 1100 * 1100 * 1100 * 16 = 21,296,000,000 条记录（约 200 多亿），查询时只需 4 次 I/O。

优化 B+ 树索引的插入性能：

• 顺序插入（如自增 ID 或时间列）的维护代价小，性能较好。
• 无序插入（如用户昵称）会导致页分裂、旋转等开销较大的操作，影响性能。
• 主键设计应尽量使用顺序值（如自增 ID），以保证高并发场景下的性能。

:::

聚簇索引和非聚簇索引

【中级】聚簇索引和非聚簇索引有什么区别？

:::details 要点

根据叶子节点的内容，索引类型分为主键索引和非主键索引。

主键索引又被称为聚簇索引（clustered index），其叶子节点存的是整行数据。

• 聚簇表示数据行和相邻的键值紧凑地存储在一起，因为数据紧凑，所以访问快。
• 因为无法同时把数据行存放在两个不同的地方，所以一个表只能有一个聚簇索引。
• InnoDB 的聚簇索引实际是在同一个结构中保存了 B 树的索引和数据行。

非主键索引又被称为二级索引（secondary index），其叶子节点存的是主键的值。数据存储在一个位置，索引存储在另一个位置，索引中包含指向数据存储位置的指针。

• 如果语句是 select * from T where ID=500，即聚簇索引查询方式，则只需要搜索主键索引树；
• 如果语句是 select * from T where k=5，即非聚簇索引查询方式，则需要先搜索 k 索引树，得到 ID 的值为 500，再到 ID 索引树搜索一次。这个过程称为回表。

也就是说，基于非聚簇索引的查询需要多扫描一棵索引树。因此，我们在应用中应该尽量使用主键查询。

显然，主键长度越小，非聚簇索引的叶子节点就越小，非聚簇索引占用的空间也就越小。

:::

【基础】什么是覆盖索引？

:::details 要点

覆盖索引是指：二级索引上的信息满足查询所需的所有字段，不需要回表查询聚簇索引上的数据。

由于覆盖索引可以减少树的搜索次数，显著提升查询性能，所以使用覆盖索引是一个常用的性能优化手段。

:::

字段特性索引

【基础】AUTO_INCREMENT 列达到最大值时会发生什么？

:::details 要点

配置主键

在 MySQL 中，如果表定义的自增 ID 到达上限后，再申请下一个 ID，得到的值不变！因此会导致重复值的错误。

没有配置主键

如果 InnoDB 表中没有配置主键，InnoDB 会自动创建一个不可见的、长度为 6 个字节的 row_id 作为默认主键。

InnoDB 在全局维护一个 dict_sys.row_id 值。每次插入一行数据时，都会获取当前的 dict_sys.row_id 值，并将其加 1。row_id 的范围是 0 到 2^48 - 1。当 row_id 达到上限后，会从 0 开始重新循环。

如果插入的新数据的 row_id 在表中已存在，老数据会被新数据覆盖，且不会产生任何报错。可以通过 gdb 动态修改 dict_sys.row_id 来验证这一行为。

:::

【基础】普通键和唯一键，应该怎么选择？

:::details 要点

唯一索引的主要作用是保证数据的唯一性，而普通索引则更灵活。在业务代码保证不会写入重复数据的情况下，普通索引和唯一索引在查询性能上几乎没有差别。

普通索引 在更新操作中性能更优，尤其是在写多读少的场景下，能够利用 change buffer 减少磁盘 I/O。

唯一索引 适用于需要保证数据唯一性的场景，但在更新操作中性能较差，因为它无法使用 change buffer。

在业务允许的情况下，优先选择普通索引，因为它可以利用 change buffer 来提升更新性能。如果业务要求必须保证数据的唯一性，则必须使用唯一索引。

:::

:::details 细节

查询过程的性能差异：对于查询操作，普通索引和唯一索引的性能差异微乎其微。唯一索引在找到第一个满足条件的记录后会停止检索，而普通索引需要继续查找下一个记录，但由于数据页的读取方式，这种差异可以忽略不计。

更新过程的性能差异：更新操作中，普通索引可以利用 change buffer 来优化性能，而唯一索引则不能使用 change buffer。

• change buffer 是一种将更新操作缓存在内存中的机制，减少了对磁盘的随机读取，从而提升了更新操作的性能。
• 唯一索引在更新时需要检查唯一性约束，必须将数据页读入内存，增加了磁盘 I/O 的开销。

change buffer 的应用

• change buffer 的数据是持久化的，即使机器掉电重启，change buffer 中的数据也不会丢失，因为它会被写入磁盘。
• change buffer 适用于写多读少的场景，如账单类、日志类系统，因为这些场景下数据页在写入后不会立即被访问，change buffer 可以显著减少磁盘 I/O。
• 对于写后立即查询的场景，change buffer 的效果不明显，甚至可能增加维护成本。

change buffer vs. redo log

• redo log 主要减少随机写磁盘的 I/O 消耗，将随机写转换为顺序写。
• change buffer 主要减少随机读磁盘的 I/O 消耗，通过缓存更新操作来减少磁盘读取。

:::

【中级】为什么不推荐使用外键？

:::details 要点

逻辑外键是一种在应用程序层面上管理和维护数据完整性的方法，而不是通过数据库本身的外键约束。主要是利用应用程序代码来保证引用的完整性。

逻辑外键的优缺点：

优点：

• 灵活性高：应用程序层面控制，可以更灵活地实现复杂的业务逻辑。
• 性能优化：避免了数据库层面的约束检查，可以在某些情况下提高性能（详细看扩展知识）。
• 跨数据库兼容性：逻辑外键在不同类型的数据之间更容易迁移。

缺点：

• 代码复杂性增加：需要在应用程序代码中手动实现和维护引用完整性，增加了代码的复杂性和错误的可能性。
• 一致性风险：如果应用程序代码未正确实现引用完整性检查，可能导致数据不一致。
• 维护成本高：逻辑外键需要开发人员持续关注和维护，增加了维护成本。

物理外键的优缺点：

优点：

• 自动维护引用完整性：数据库会自动检查和维护外键约束，确保数据的一致性。
• 减少应用层复杂性：开发人员不需要手动管理引用完整性，减少了代码的复杂性和错误的可能性。
• 数据完整性保障：数据库层面的约束能够更有效地防止非法数据的插入或更新。

缺点：

• 性能开销：外键约束会增加插入、更新和删除操作的开销，特别是在处理大量数据时。
• 迁移和复制的复杂性：在进行数据库迁移或复制时，外键约束可能会增加复杂性，需要小心处理。
• 灵活性较低：物理外键在某些复杂业务逻辑下可能不够灵活，需要更多的手动控制。

:::

【中级】什么是前缀索引？

:::details 要点

“前缀索引”是指索引开始的部分字符。对于 BLOB/TEXT/VARCHAR 这种文本类型的列，必须使用前缀索引，因为数据库往往不允许索引这些列的完整长度。

前缀索引的优点是可以大大节约索引空间，从而提高索引效率。

前缀索引的缺点是会降低索引的区分度。此外，**order by 无法使用前缀索引，无法把前缀索引用作覆盖索引**。

:::

字段个数索引

【中级】什么是索引最左匹配原则？

:::details 要点

不只是索引的全部定义，只要满足最左前缀，就可以利用索引来加速检索。这里的最左，可以是联合索引的最左 N 个字段，也可以是字符串索引的最左 M 个字符。

如果是联合索引，那么 key 也由多个列组成，同时，索引只能用于查找 key 是否存在（相等），遇到范围查询 (>、<、BETWEEN、LIKE) 就不能进一步匹配了，后续退化为线性查找。因此，列的排列顺序决定了可命中索引的列数。

应该将选择性高的列或基数大的列优先排在多列索引最前列。但有时，也需要考虑 WHERE 子句中的排序、分组和范围条件等因素，这些因素也会对查询性能造成较大影响。“索引的选择性”是指不重复的索引值和记录总数的比值，最大值为 1，此时每个记录都有唯一的索引与其对应。索引的选择性越高，查询效率越高。如果存在多条命中前缀索引的情况，就需要依次扫描，直到最终找到正确记录。

:::

【中级】什么是索引下推？

:::details 要点

索引下推是一种减少回表查询、提高查询效率的技术。索引下推主要应用于联合索引。

它允许 MySQL 在使用索引查找数据时，将部分查询条件下推到存储引擎层进行过滤，从而减少需要从表中读取的数据行，减少 IO 操作。

索引下推注意点：

• MySQL 5.6 及以后版本支持索引下推，适用于 InnoDB 和 MyISAM 存储引擎。
• 如果查询中包含子查询，索引下推可能不会生效。
• 使用函数或表达式时，索引下推不会生效。
• 使用聚簇索引（主键）查询时，索引下推不会生效，因为它主要针对非聚簇索引。

扩展阅读：https://zhuanlan.zhihu.com/p/121084592

:::

参考资料

• 《高性能 MySQL》
• 极客时间教程 - MySQL 实战 45 讲
• 图解 MySQL 介绍

MySQL CRUD

::: info 概述

CRUD 由英文单词 Create, Read, Update, Delete 的首字母组成，即增删改查。

本文通过介绍基本的 MySQL CRUD 方法，向读者呈现如何访问 MySQL 数据。

扩展阅读：SQL 语法必知必会

:::

MySQL 数据类型

::: info 概述

数据类型在 MySQL 中扮演着至关重要的角色，它定义了表中每个字段可以存储的数据种类和格式。

MySQL 支持多种类型，大致可以分为三类：数值、时间和字符串类型。

:::

MySQL 简介

::: info 概述

MySQL 是一个关系型数据库管理系统，由瑞典 MySQL AB 公司开发，目前属于 Oracle 公司。MySQL 是最流行的关系型数据库管理系统之一，在 WEB 应用方面，MySQL 是最好的 RDBMS 应用软件之一。

本文简单介绍了 MySQL 的功能、特性、发行版本、简史、概念，可以让读者在短时间内对于 MySQL 有一个初步的认识。

:::

MongoDB 面试

HBase 面试

HBase 简介

【基础】什么是 HBase？

:::details 要点

HBase 是一个构建在 HDFS（Hadoop 文件系统）之上的列式数据库。

HBase 是一种类似于 Google’s Big Table 的数据模型，它是 Hadoop 生态系统的一部分，它将数据存储在 HDFS 上，客户端可以通过 HBase 实现对 HDFS 上数据的随机访问。

HBase 的核心特性如下：

• 分布式
  • 伸缩性：支持通过增减机器进行水平扩展，以提升整体容量和性能
  • 高可用：支持 RegionServers 之间的自动故障转移
  • 自动分区：Region 分散在集群中，当行数增长的时候，Region 也会自动的分区再均衡
• 超大数据集：HBase 被设计用来读写超大规模的数据集（数十亿行至数百亿行的表）
• 支持结构化、半结构化和非结构化的数据：由于 HBase 基于 HDFS 构建，所以和 HDFS 一样，支持结构化、半结构化和非结构化的数据
• 非关系型数据库
  • 不支持标准 SQL 语法
  • 没有真正的索引
  • 不支持复杂的事务：只支持行级事务，即单行数据的读写都是原子性的

HBase 的其他特性

• 读写操作遵循强一致性
• 过滤器支持谓词下推
• 易于使用的 Java 客户端 API
• 它支持线性和模块化可扩展性。
• HBase 表支持 Hadoop MapReduce 作业的便捷基类
• 很容易使用 Java API 进行客户端访问
• 为实时查询提供块缓存 BlockCache 和布隆过滤器
• 它通过服务器端过滤器提供查询谓词下推

:::

【基础】为什么需要 HBase？

:::details 要点

在 HBase 诞生之前，Hadoop 可以通过 HDFS 来存储结构化、半结构甚至非结构化的数据，它是传统数据库的补充，是海量数据存储的最佳方法，它针对大文件的存储，批量访问和流式访问都做了优化，同时也通过多副本解决了容灾问题。

Hadoop 的缺陷在于：它只能执行批处理，并且只能以顺序方式访问数据。这意味着即使是最简单的工作，也必须搜索整个数据集，即：Hadoop 无法实现对数据的随机访问。实现数据的随机访问是传统的关系型数据库所擅长的，但它们却不能用于海量数据的存储。在这种情况下，必须有一种新的方案来同时解决海量数据存储和随机访问的问题，HBase 就是其中之一 (HBase，Cassandra，CouchDB，Dynamo 和 MongoDB 都能存储海量数据并支持随机访问）。

注：数据结构分类：

• 结构化数据：即以关系型数据库表形式管理的数据；
• 半结构化数据：非关系模型的，有基本固定结构模式的数据，例如日志文件、XML 文档、JSON 文档、Email 等；
• 非结构化数据：没有固定模式的数据，如 WORD、PDF、PPT、EXL，各种格式的图片、视频等。

:::

【基础】HBase 有哪些应用场景？

:::details 要点

根据上一节对于 HBase 特性的介绍，我们可以梳理出 HBase 适用、不适用的场景：

HBase 不适用场景：

• 需要索引
• 需要复杂的事务
• 数据量较小（比如：数据量不足几百万行）

HBase 适用场景：

• 能存储海量数据并支持随机访问（比如：数据量级达到十亿级至百亿级）
• 存储结构化、半结构化数据
• 硬件资源充足

一言以蔽之——HBase 适用的场景是：实时地随机访问超大数据集。

HBase 的典型应用场景

• 存储监控数据
• 存储用户/车辆 GPS 信息
• 存储用户行为数据
• 存储各种日志数据，如：访问日志、操作日志、推送日志等。
• 存储短信、邮件等消息类数据
• 存储网页数据

:::

【基础】HBase vs. RDBMS？

:::details 要点

HBase 和 RDBMS 的不同之处如下：

:::

【基础】HBase vs. HDFS？

:::details 要点

HBase 和 HDFS 的不同之处如下：

:::

【基础】行式数据库 vs. 列式数据库？

:::details 要点

行式数据库和列式数据库的不同之处如下：

列式数据库的优点：

• 支持数据压缩
• 支持快速数据检索
• 简化了管理和配置
• 有利于聚合查询（例如 COUNT、SUM、AVG、MIN 和 MAX）的高性能
• 分区效率很高，因为它提供了自动分片机制的功能，可以将较大的区域分配给较小的区域

列式数据库的缺点：

• JOIN 查询和来自多个表的数据未优化
• 必须为频繁的删除和更新创建拆分，因此降低了存储效率
• 由于非关系数据库的特性，分区和索引的设计非常困难

:::

HBase 存储

【基础】HBase 表有什么特性？

:::details 要点

Hbase 的表具有以下特点：

• 容量大：一个表可以有数十亿行，上百万列；
• 面向列：数据是按照列存储，每一列都单独存放，数据即索引，在查询时可以只访问指定列的数据，有效地降低了系统的 I/O 负担；
• 稀疏性：空 (null) 列并不占用存储空间，表可以设计的非常稀疏 ；
• 数据多版本：每个单元中的数据可以有多个版本，按照时间戳排序，新的数据在最上面；
• 存储类型：所有数据的底层存储格式都是字节数组 (byte[])。

:::

【基础】HBase 的逻辑存储模型是怎样的？

:::details 要点

HBase 是一个面向 列 的数据库管理系统，这里更为确切的而说，HBase 是一个面向 列族 的数据库管理系统。表 schema 仅定义列族，表具有多个列族，每个列族可以包含任意数量的列，列由多个单元格（cell）组成，单元格可以存储多个版本的数据，多个版本数据以时间戳进行区分。

HBase 数据模型和关系型数据库有所不同。其数据模型的关键术语如下：

• **Table**：Table 由 Row 和 Column 组成。
• **Row**：Row 是列族（Column Family）的集合。
• Row Key：Row Key 是用来检索记录的主键。
  • Row Key 是未解释的字节数组，所以理论上，任何数据都可以通过序列化表示成字符串或二进制，从而存为 HBase 的键值。
  • 表中的行，是按照 Row Key 的字典序进行排序。这里需要注意以下两点：
    • 因为字典序对 Int 排序的结果是 1,10,100,11,12,13,14,15,16,17,18,19,2,20,21,…,9,91,92,93,94,95,96,97,98,99。如果你使用整型的字符串作为行键，那么为了保持整型的自然序，行键必须用 0 作左填充。
    • 行的一次读写操作是原子性的 （不论一次读写多少列）。
  • 所有对表的访问都要通过 Row Key，有以下三种方式：
    • 通过指定的 Row Key 进行访问；
    • 通过 Row Key 的 range 进行访问，即访问指定范围内的行；
    • 进行全表扫描。
• **Column Family**：即列族。HBase 表中的每个列，都归属于某个列族。列族是表的 Schema 的一部分，所以列族需要在创建表时进行定义。
  • 一个表的列族必须作为表模式定义的一部分预先给出，但是新的列族成员可以随后按需加入。
  • 同一个列族的所有成员具有相同的前缀，例如 info:format，info:geo 都属于 info 这个列族。
• **Column Qualifier**：列限定符。可以理解为是具体的列名，例如 info:format，info:geo 都属于 info 这个列族，它们的列限定符分别是 format 和 geo。列族和列限定符之间始终以冒号分隔。需要注意的是列限定符不是表 Schema 的一部分，你可以在插入数据的过程中动态创建列。
• **Column**：HBase 中的列由列族和列限定符组成，由 :（冒号） 进行分隔，即一个完整的列名应该表述为 列族名 ：列限定符。
• **Cell**：Cell 是行，列族和列限定符的组合，并包含值和时间戳。HBase 中通过 row key 和 column 确定的为一个存储单元称为 Cell，你可以等价理解为关系型数据库中由指定行和指定列确定的一个单元格，但不同的是 HBase 中的一个单元格是由多个版本的数据组成的，每个版本的数据用时间戳进行区分。
  • Cell 由行和列的坐标交叉决定，是有版本的。默认情况下，版本号是自动分配的，为 HBase 插入 Cell 时的时间戳。Cell 的内容是未解释的字节数组。
• **Timestamp**：Cell 的版本通过时间戳来索引，时间戳的类型是 64 位整型，时间戳可以由 HBase 在数据写入时自动赋值，也可以由客户显式指定。每个 Cell 中，不同版本的数据按照时间戳倒序排列，即最新的数据排在最前面。

下图为 HBase 中一张表的：

• RowKey 为行的唯一标识，所有行按照 RowKey 的字典序进行排序；
• 该表具有两个列族，分别是 personal 和 office;
• 其中列族 personal 拥有 name、city、phone 三个列，列族 office 拥有 tel、addres 两个列。

图片引用自 : HBase 是列式存储数据库吗 https://www.iteblog.com/archives/2498.html

:::

【基础】HBase 的物理存储模型是怎样的？

:::details 要点

HBase Table 中的所有行按照 Row Key 的字典序排列。HBase Tables 通过行键的范围 (row key range) 被水平切分成多个 Region, 一个 Region 包含了在 start key 和 end key 之间的所有行。

每个表一开始只有一个 Region，随着数据不断增加，Region 会不断增大，当增大到一个阀值的时候，Region 就会等分为两个新的 Region。当 Table 中的行不断增多，就会有越来越多的 Region。

Region 是 HBase 中分布式存储和负载均衡的最小单元。这意味着不同的 Region 可以分布在不同的 Region Server 上。但一个 Region 是不会拆分到多个 Server 上的。

:::

HBase 架构

【中级】HBase 读数据流程是怎样的？

:::details 要点

以下是客户端首次读写 HBase 上数据的流程：

1. 客户端从 Zookeeper 获取 META 表所在的 Region Server；
2. 客户端访问 META 表所在的 Region Server，从 META 表中查询到访问行键所在的 Region Server，之后客户端将缓存这些信息以及 META 表的位置；
3. 客户端从行键所在的 Region Server 上获取数据。

如果再次读取，客户端将从缓存中获取行键所在的 Region Server。这样客户端就不需要再次查询 META 表，除非 Region 移动导致缓存失效，这样的话，则将会重新查询并更新缓存。

注：META 表是 HBase 中一张特殊的表，它保存了所有 Region 的位置信息，META 表自己的位置信息则存储在 ZooKeeper 上。

更为详细读取数据流程参考：

HBase 原理－数据读取流程解析

HBase 原理－迟到的‘数据读取流程部分细节

:::

【中级】HBase 写数据流程是怎样的？

:::details 要点

1. Client 向 Region Server 提交写请求；
2. Region Server 找到目标 Region；
3. Region 检查数据是否与 Schema 一致；
4. 如果客户端没有指定版本，则获取当前系统时间作为数据版本；
5. 将更新写入 WAL Log；
6. 将更新写入 Memstore；
7. 判断 Memstore 存储是否已满，如果存储已满则需要 flush 为 Store Hfile 文件。

更为详细写入流程可以参考：HBase － 数据写入流程解析

:::

【中级】HBase 有哪些核心组件？

:::details 要点

HBase 系统遵循 Master/Salve 架构，由三种不同类型的组件组成：

• Zookeeper
  • 保证任何时候，集群中只有一个 Master；
  • 存储所有 Region 的寻址入口；
  • 实时监控 Region Server 的状态，将 Region Server 的上线和下线信息实时通知给 Master；
  • 存储 HBase 的 Schema，包括有哪些 Table，每个 Table 有哪些 Column Family 等信息。
• Master
  • 为 Region Server 分配 Region ；
  • 负责 Region Server 的负载均衡 ；
  • 发现失效的 Region Server 并重新分配其上的 Region；
  • GFS 上的垃圾文件回收；
  • 处理 Schema 的更新请求。
• Region Server
  • Region Server 负责维护 Master 分配给它的 Region ，并处理发送到 Region 上的 IO 请求；
  • Region Server 负责切分在运行过程中变得过大的 Region。

HBase 使用 ZooKeeper 作为分布式协调服务来维护集群中的服务器状态。 Zookeeper 负责维护可用服务列表，并提供服务故障通知等服务：

• 每个 Region Server 都会在 ZooKeeper 上创建一个临时节点，Master 通过 Zookeeper 的 Watcher 机制对节点进行监控，从而可以发现新加入的 Region Server 或故障退出的 Region Server；
• 所有 Masters 会竞争性地在 Zookeeper 上创建同一个临时节点，由于 Zookeeper 只能有一个同名节点，所以必然只有一个 Master 能够创建成功，此时该 Master 就是主 Master，主 Master 会定期向 Zookeeper 发送心跳。备用 Masters 则通过 Watcher 机制对主 HMaster 所在节点进行监听；
• 如果主 Master 未能定时发送心跳，则其持有的 Zookeeper 会话会过期，相应的临时节点也会被删除，这会触发定义在该节点上的 Watcher 事件，使得备用的 Master Servers 得到通知。所有备用的 Master Servers 在接到通知后，会再次去竞争性地创建临时节点，完成主 Master 的选举。

:::

HBase 高可用

Hive 面试

Hive 简介

【基础】什么是 Hive？

:::details 要点

Apache Hive 是一种分布式、容错数据仓库，支持大规模分析。Hive Metastore （HMS） 提供了一个元数据的中央存储库，可以轻松分析以做出明智的数据驱动决策，因此它是许多数据湖架构的关键组件。Hive 构建在 Apache Hadoop 之上，并通过 hdfs 支持在 S3、adls、gs 等上进行存储。Hive 允许用户使用 SQL 读取、写入和管理 PB 级数据。

Hive 可以将结构化的数据文件映射成表，并提供类 SQL 查询功能。用于查询的 SQL 语句会被转化为 MapReduce 作业，然后提交到 Hadoop 上运行。

特点：

1. 简单、容易上手（提供了类似 sql 的查询语言 hql），使得精通 sql 但是不了解 Java 编程的人也能很好地进行大数据分析；
2. 灵活性高，可以自定义用户函数 (UDF) 和存储格式；
3. 为超大的数据集设计的计算和存储能力，集群扩展容易；
4. 统一的元数据管理，可与 presto／impala／sparksql 等共享数据；
5. 执行延迟高，不适合做数据的实时处理，但适合做海量数据的离线处理。

:::

【基础】什么是 HMS？

:::details 要点

Hive Metastore （HMS） 是关系数据库中 Hive 表和分区元数据的中央存储库，它使用元存储服务 API 为客户端（包括 Hive、Impala 和 Spark）提供对此信息的访问。它已成为利用各种开源软件（如 Apache Spark 和 Presto）的数据湖的构建块。事实上，整个工具生态系统，无论是开源的还是其他的，都是围绕 Hive Metastore 构建的，下图说明了其中一些。

:::

Hive 存储

【基础】Hive 支持哪些数据类型？

:::details 要点

Hive 表中的列支持以下基本数据类型：

TIMESTAMP 和 TIMESTAMP WITH LOCAL TIME ZONE 的区别如下：

• TIMESTAMP WITH LOCAL TIME ZONE：用户提交时间给数据库时，会被转换成数据库所在的时区来保存。查询时则按照查询客户端的不同，转换为查询客户端所在时区的时间。
• TIMESTAMP ：提交什么时间就保存什么时间，查询时也不做任何转换。

此外，Hive 还支持以下复杂类型：

:::

【基础】Hive 支持哪些存储格式？

:::details 要点

Hive 会在 HDFS 为每个数据库上创建一个目录，数据库中的表是该目录的子目录，表中的数据会以文件的形式存储在对应的表目录下。Hive 支持以下几种文件存储格式：

以上压缩格式中 ORC 和 Parquet 的综合性能突出，使用较为广泛，推荐使用这两种格式。

通常在创建表的时候使用 STORED AS 参数指定：

1
2
3
4
5
6

CREATE TABLE page_view(viewTime INT, userid BIGINT)
 ROW FORMAT DELIMITED
   FIELDS TERMINATED BY '\001'
   COLLECTION ITEMS TERMINATED BY '\002'
   MAP KEYS TERMINATED BY '\003'
 STORED AS SEQUENCEFILE;

各个存储文件类型指定方式如下：

• STORED AS TEXTFILE
• STORED AS SEQUENCEFILE
• STORED AS ORC
• STORED AS PARQUET
• STORED AS AVRO
• STORED AS RCFILE

:::

【基础】Hive 中的内部表和外部表有什么区别？

:::details 要点

内部表又叫做管理表 (Managed/Internal Table)，创建表时不做任何指定，默认创建的就是内部表。想要创建外部表 (External Table)，则需要使用 External 进行修饰。 内部表和外部表主要区别如下：

:::

【基础】什么是分区表？

:::details 要点

Hive 中的表对应为 HDFS 上的指定目录，在查询数据时候，默认会对全表进行扫描，这样时间和性能的消耗都非常大。

分区为 HDFS 上表目录的子目录，数据按照分区存储在子目录中。如果查询的 where 子句中包含分区条件，则直接从该分区去查找，而不是扫描整个表目录，合理的分区设计可以极大提高查询速度和性能。

分区表并非 Hive 独有的概念，实际上这个概念非常常见。通常，在管理大规模数据集的时候都需要进行分区，比如将日志文件按天进行分区，从而保证数据细粒度的划分，使得查询性能得到提升。比如，在我们常用的 Oracle 数据库中，当表中的数据量不断增大，查询数据的速度就会下降，这时也可以对表进行分区。表进行分区后，逻辑上表仍然是一张完整的表，只是将表中的数据存放到多个表空间（物理文件上），这样查询数据时，就不必要每次都扫描整张表，从而提升查询性能。

在 Hive 中可以使用 PARTITIONED BY 子句创建分区表。表可以包含一个或多个分区列，程序会为分区列中的每个不同值组合创建单独的数据目录。下面的我们创建一张雇员表作为测试：

1
2
3
4
5
6
7
8
9
10
11
12

 CREATE EXTERNAL TABLE emp_partition(
    empno INT,
    ename STRING,
    job STRING,
    mgr INT,
    hiredate TIMESTAMP,
    sal DECIMAL(7,2),
    comm DECIMAL(7,2)
)
    PARTITIONED BY (deptno INT)   -- 按照部门编号进行分区
    ROW FORMAT DELIMITED FIELDS TERMINATED BY "\t"
    LOCATION '/hive/emp_partition';

加载数据到分区表时候必须要指定数据所处的分区：

1
2
3
4

# 加载部门编号为 20 的数据到表中
LOAD DATA LOCAL INPATH "/usr/file/emp20.txt" OVERWRITE INTO TABLE emp_partition PARTITION (deptno=20)
# 加载部门编号为 30 的数据到表中
LOAD DATA LOCAL INPATH "/usr/file/emp30.txt" OVERWRITE INTO TABLE emp_partition PARTITION (deptno=30)

这时候我们直接查看表目录，可以看到表目录下存在两个子目录，分别是 deptno=20 和 deptno=30, 这就是分区目录，分区目录下才是我们加载的数据文件。

1

# hadoop fs -ls  hdfs://hadoop001:8020/hive/emp_partition/

这时候当你的查询语句的 where 包含 deptno=20，则就去对应的分区目录下进行查找，而不用扫描全表。

:::

【基础】什么是分桶表？

:::details 要点

分区提供了一个隔离数据和优化查询的可行方案，但是并非所有的数据集都可以形成合理的分区，分区的数量也不是越多越好，过多的分区条件可能会导致很多分区上没有数据。同时 Hive 会限制动态分区可以创建的最大分区数，用来避免过多分区文件对文件系统产生负担。鉴于以上原因，Hive 还提供了一种更加细粒度的数据拆分方案：分桶表 (bucket Table)。

分桶表会将指定列的值进行哈希散列，并对 bucket（桶数量）取余，然后存储到对应的 bucket（桶）中。

单从概念上理解分桶表可能会比较晦涩，其实和分区一样，分桶这个概念同样不是 Hive 独有的，对于 Java 开发人员而言，这可能是一个每天都会用到的概念，因为 Hive 中的分桶概念和 Java 数据结构中的 HashMap 的分桶概念是一致的。

当调用 HashMap 的 put() 方法存储数据时，程序会先对 key 值调用 hashCode() 方法计算出 hashcode，然后对数组长度取模计算出 index，最后将数据存储在数组 index 位置的链表上，链表达到一定阈值后会转换为红黑树 (JDK1.8+)。下图为 HashMap 的数据结构图：

图片引用自：HashMap vs. Hashtable

在 Hive 中，我们可以通过 CLUSTERED BY 指定分桶列，并通过 SORTED BY 指定桶中数据的排序参考列。下面为分桶表建表语句示例：

1
2
3
4
5
6
7
8
9
10
11
12

CREATE EXTERNAL TABLE emp_bucket(
  empno INT,
  ename STRING,
  job STRING,
  mgr INT,
  hiredate TIMESTAMP,
  sal DECIMAL(7,2),
  comm DECIMAL(7,2),
  deptno INT)
  CLUSTERED BY(empno) SORTED BY(empno ASC) INTO 4 BUCKETS  --按照员工编号散列到四个 bucket 中
  ROW FORMAT DELIMITED FIELDS TERMINATED BY "\t"
  LOCATION '/hive/emp_bucket';

这里直接使用 Load 语句向分桶表加载数据，数据时可以加载成功的，但是数据并不会分桶。

这是由于分桶的实质是对指定字段做了 hash 散列然后存放到对应文件中，这意味着向分桶表中插入数据是必然要通过 MapReduce，且 Reducer 的数量必须等于分桶的数量。由于以上原因，分桶表的数据通常只能使用 CTAS(CREATE TABLE AS SELECT) 方式插入，因为 CTAS 操作会触发 MapReduce。加载数据步骤如下：

（1）设置强制分桶

1

set hive.enforce.bucketing = true; --Hive 2.x 不需要这一步

在 Hive 0.x and 1.x 版本，必须使用设置 hive.enforce.bucketing = true，表示强制分桶，允许程序根据表结构自动选择正确数量的 Reducer 和 cluster by column 来进行分桶。

（2）CTAS 导入数据

1

INSERT INTO TABLE emp_bucket SELECT *  FROM emp;  --这里的 emp 表就是一张普通的雇员表

可以从执行日志看到 CTAS 触发 MapReduce 操作，且 Reducer 数量和建表时候指定 bucket 数量一致：

查看分桶文件

bucket（桶） 本质上就是表目录下的具体文件：

:::

【基础】分区和分桶可以一起使用吗？

:::details 要点

分区表和分桶表的本质都是将数据按照不同粒度进行拆分，从而使得在查询时候不必扫描全表，只需要扫描对应的分区或分桶，从而提升查询效率。两者可以结合起来使用，从而保证表数据在不同粒度上都能得到合理的拆分。下面是 Hive 官方给出的示例：

1
2
3
4
5
6
7
8
9
10
11
12
13

CREATE TABLE page_view_bucketed(
	viewTime INT, 
    userid BIGINT,
    page_url STRING, 
    referrer_url STRING,
    ip STRING )
 PARTITIONED BY(dt STRING)
 CLUSTERED BY(userid) SORTED BY(viewTime) INTO 32 BUCKETS
 ROW FORMAT DELIMITED
   FIELDS TERMINATED BY '\001'
   COLLECTION ITEMS TERMINATED BY '\002'
   MAP KEYS TERMINATED BY '\003'
 STORED AS SEQUENCEFILE;

此时导入数据时需要指定分区：

1
2
3

INSERT OVERWRITE page_view_bucketed
PARTITION (dt='2009-02-25')
SELECT * FROM page_view WHERE dt='2009-02-25';

:::

Hive 索引

【中级】Hive 的索引是如何工作的？

:::details 要点

Hive 在 0.7.0 引入了索引的功能，索引的设计目标是提高表某些列的查询速度。如果没有索引，带有谓词的查询（如’WHERE table1.column = 10’）会加载整个表或分区并处理所有行。但是如果 column 存在索引，则只需要加载和处理文件的一部分。

在指定列上建立索引，会产生一张索引表（表结构如下），里面的字段包括：索引列的值、该值对应的 HDFS 文件路径、该值在文件中的偏移量。在查询涉及到索引字段时，首先到索引表查找索引列值对应的 HDFS 文件路径及偏移量，这样就避免了全表扫描。

1
2
3
4
5
6
7

+--------------+----------------+----------+--+
|   col_name   |   data_type    | comment     |
+--------------+----------------+----------+--+
| empno        | int            |  建立索引的列  |   
| _bucketname  | string         |  HDFS 文件路径  |
| _offsets     | array<bigint>  |  偏移量       |
+--------------+----------------+----------+--+

创建索引：

1
2
3
4
5
6
7
8
9
10
11
12
13

CREATE INDEX index_name     --索引名称
  ON TABLE base_table_name (col_name, ...)  --建立索引的列
  AS index_type    --索引类型
  [WITH DEFERRED REBUILD]    --重建索引
  [IDXPROPERTIES (property_name=property_value, ...)]  --索引额外属性
  [IN TABLE index_table_name]    --索引表的名字
  [
     [ ROW FORMAT ...] STORED AS ...  
     | STORED BY ...
  ]   --索引表行分隔符 、 存储格式
  [LOCATION hdfs_path]  --索引表存储位置
  [TBLPROPERTIES (...)]   --索引表表属性
  [COMMENT "index comment"];  --索引注释

查看索引：

1
2

--显示表上所有列的索引
SHOW FORMATTED INDEX ON table_name;

删除索引：

删除索引会删除对应的索引表。

1

DROP INDEX [IF EXISTS] index_name ON table_name;

如果存在索引的表被删除了，其对应的索引和索引表都会被删除。如果被索引表的某个分区被删除了，那么分区对应的分区索引也会被删除。

重建索引：

1

ALTER INDEX index_name ON table_name [PARTITION partition_spec] REBUILD;

重建索引。如果指定了 PARTITION，则仅重建该分区的索引。

:::

【中级】Hive 索引有什么缺陷？

:::details 要点

索引表最主要的一个缺陷在于：索引表无法自动 rebuild，这也就意味着如果表中有数据新增或删除，则必须手动 rebuild，重新执行 MapReduce 作业，生成索引表数据。

同时按照 官方文档 的说明，Hive 会从 3.0 开始移除索引功能，主要基于以下两个原因：

• 具有自动重写的物化视图 (Materialized View) 可以产生与索引相似的效果（Hive 2.3.0 增加了对物化视图的支持，在 3.0 之后正式引入）。
• 使用列式存储文件格式（Parquet，ORC）进行存储时，这些格式支持选择性扫描，可以跳过不需要的文件或块。

ORC 内置的索引功能可以参阅这篇文章：Hive 性能优化之 ORC 索引–Row Group Index vs Bloom Filter Index

:::

Hive 架构

【高级】Hive SQL 如何执行的？

:::details 要点

Hive 在执行一条 HQL 的时候，会经过以下步骤：

1. 语法解析：Antlr 定义 SQL 的语法规则，完成 SQL 词法，语法解析，将 SQL 转化为抽象 语法树 AST Tree；
2. 语义解析：遍历 AST Tree，抽象出查询的基本组成单元 QueryBlock；
3. 生成逻辑执行计划：遍历 QueryBlock，翻译为执行操作树 OperatorTree；
4. 优化逻辑执行计划：逻辑层优化器进行 OperatorTree 变换，合并不必要的 ReduceSinkOperator，减少 shuffle 数据量；
5. 生成物理执行计划：遍历 OperatorTree，翻译为 MapReduce 任务；
6. 优化物理执行计划：物理层优化器进行 MapReduce 任务的变换，生成最终的执行计划。

关于 Hive SQL 的详细执行流程可以参考美团技术团队的文章：Hive SQL 的编译过程

:::

参考资料

• [Hive 简介及核心概念](https://github.com/heibaiying/BigData-Notes/blob/master/notes/Hive 简介及核心概念。md)

刷题

经典数据结构

数组

链表

栈和队列

解题套路

链表双指针技巧

递归操作链表

数组双指针技巧

二维数组操作技巧

滑动窗口算法

二分搜索算法

前缀和/差分技巧

栈

队列

单调栈技巧

单调队列技巧

二叉树

二叉搜索树

数据结构设计

图相关算法

DFS/回溯算法

BFS 算法

动态规划

贪心算法

分治算法

数学算法

其他经典面试题