MongoDB 索引

发表于 2020-09-21 更新于 2025-09-13 分类于数据库，文档数据库， mongodb 本文字数： 2.9k 阅读时长 ≈ 3 分钟

MongoDB 索引

::: info 概述

索引通常能够极大的提高查询的效率。如果没有索引，MongoDB 在读取数据时必须扫描 collection 中的每个 document 并选取那些符合查询条件的记录。这种扫描全集合的查询是非常低效的，特别是在处理大量的数据时。查询可能要花费几十秒甚至几分钟，这种性能开销是不可接受的。索引可提高查询性能，但添加索引会影响写入操作的性能。对于写入读取率高的集合，由于每次插入操作都必须同时更新所有索引，因此会带来较高的索引成本。

本文介绍了 MongoDB 的基本索引操作、索引类型，和设置索引的策略。掌握了 MongoDB 索引的要点，有助于提高访问 MongoDB 数据的效率。

:::

阅读全文 »

MongoDB 聚合

发表于 2020-09-21 更新于 2025-09-13 分类于数据库，文档数据库， mongodb 本文字数： 11k 阅读时长 ≈ 10 分钟

MongoDB 聚合

::: info 概述

聚合操作处理多个文档并返回计算结果。可以使用聚合操作来：

将多个文档中的值组合在一起。
对分组数据执行操作，返回单一结果。
分析一段时间内的数据变化。

在 MongoDB 中，支持以下聚合方式：

聚合管道，这是执行聚合的首选方法。
单一目的聚合方法，这些方法很简单，但缺乏聚合管道的功能。
Map-Reduce，从 MongoDB 5.0 开始，Map-Reduce 已被弃用。聚合管道提供的性能和可用性比 Map-Reduce 更优越。

本文将逐一介绍这三种聚合方式的要点和使用方法。

:::

阅读全文 »

MongoDB 事务

发表于 2020-09-20 更新于 2025-09-13 分类于数据库，文档数据库， mongodb 本文字数： 6.5k 阅读时长 ≈ 6 分钟

MongoDB 事务

::: info 概述

通俗的说，事务将多个读、写操作捆绑在一起成为一个逻辑操作单元。事务中的所有读写是一个执行的整体，整个事务要么成功（提交）、要么失败（中止或回滚）。如果失败，应用程序可以安全地重试。这样，由于不需要担心部分失败的情况（无论出于任何原因），应用层的错误处理就变得简单很多。

大多数 NoSQL 只能部分支持事务，甚至完全不支持事务。但是，MongoDB 支持 ACID 事务，这是它的一大优势。

本文主要介绍了 MongoDB 对于事务的支持力度，以及如何应用事务。

:::

阅读全文 »

MongoDB 分片

发表于 2020-09-20 更新于 2025-09-13 分类于数据库，文档数据库， mongodb 本文字数： 4.2k 阅读时长 ≈ 4 分钟

MongoDB 分片

::: info 概述

分区通常是这样定义的，即每一条数据（或者每条记录，每行或每个文档）只属于某个特定分区。实际上，每个分区都可以视为一个完整的小型数据库，虽然数据库可能存在一些跨分区的操作。

在不同系统中，分区有着不同的称呼，例如它对应于 MongoDB, Elasticsearch 和 SolrCloud 中的 shard, HBase 的 region, Bigtable 中的 tablet, Cassandra 和 Riak 中的 vnode ，以及 Couch base 中的 vBucket。

数据量如果太大，单台机器进行存储和处理就会成为瓶颈，因此需要引入数据分区机制。分区的目地是通过多台机器均匀分布数据和查询负载，避免出现热点。这需要选择合适的数据分区方案，在节点添加或删除时重新动态平衡分区。

分区通常与复制结合使用，即每个分区在多个节点都存有副本。这意味着某条记录属于特定的分区，而同样的内容会保存在不同的节点上以提高系统的容错性。一个节点上可能存储了多个分区。每个分区都有自己的主副本，例如被分配给某节点，而从副本则分配在其他一些节点。一个节点可能既是某些分区的主副本，同时又是其他分区的从副本。

:::

阅读全文 »

MongoDB 复制

发表于 2020-09-20 更新于 2025-09-13 分类于数据库，文档数据库， mongodb 本文字数： 3.3k 阅读时长 ≈ 3 分钟

MongoDB 复制

::: info 概述

复制主要指通过网络在多台机器上保存相同数据的副本。

复制数据，可能出于各种各样的原因：

提高可用性 - 当部分组件出现位障，系统依然可以继续工作，系统依然可以继续工作。
降低访问延迟 - 使数据在地理位置上更接近用户。
提高读吞吐量 - 扩展至多台机器以同时提供数据访问服务。

综上可知，复制是所有分布式系统的核心特性，是高可用的重要保证。

MongoDB 本身是一个分布式数据库，自然也需要具备复制的能力。MongoDB 复制采用了经典的主从架构。所有的写入操作都发送到主节点，由主节点负责将数据更改事件发送到从节点，每个从节点都可以接收读请求。

本文将逐一阐述 MongoDB 复制的各个要点，以及如何基于复制来保证 MongoDB 的高可用。

:::

阅读全文 »

MySQL 面试

发表于 2020-09-12 更新于 2025-09-13 分类于数据库，关系型数据库， mysql 本文字数： 28k 阅读时长 ≈ 25 分钟

MySQL 面试

关系数据库综合

【简单】什么是范式？什么是反范式？

数据库规范化，又称“范式”，是数据库设计的指导理论。范式的目标是：使数据库结构更合理，消除存储异常，使数据冗余尽量小，增进数据的一致性。

根据约束程度从低到高有：第一范式（1NF）、第二范式（2NF）、第三范式（3NF）、巴斯-科德范式（BCNF）等等。

1NF 要求所有属性都不可再分解。
2NF 要求不存在部分依赖。
3NF 要求不存在传递依赖。

反范式，顾名思义，与范式的目标正好相反。范式的目标是消除冗余；反范式的目标是冗余以提高查询效率。

范式并非越严格越好，现代数据库设计，一般最多满足 3NF。范式越高意味着表的划分更细，一个数据库中需要的表也就越多，用户不得不将原本相关联的数据分摊到多个表中。当用户同时需要这些数据时只能通过关联表的形式将数据重新合并在一起。同时把多个表联接在一起的花费是巨大的，尤其是当需要连接的两张或者多张表数据非常庞大的时候，表连接操作几乎是一个噩梦，这严重地降低了系统运行性能。因此，有时为了提高查询效率，有必要适当的冗余数据，以达到空间换时间的目的——这就是“反范式”。

第一范式 (1NF)

1NF 要求所有属性都不可再分解。

第二范式 (2NF)

2NF 要求记录有唯一标识，即实体的唯一性，即不存在部分依赖。

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

1 2	-- 学生表 student（学号、课程号、姓名、学分、成绩）

举例来说，现有一张 student 表，具有学号、课程号、姓名、学分等字段。从中可以看出，表中包含了学生信息和课程信息。由于非主键字段必须依赖主键，这里学分依赖课程号，姓名依赖学号，所以不符合 2NF。

不符合 2NF 可能会存在的问题：

数据冗余 - 每条记录都含有相同信息。
删除异常 - 删除所有学生成绩，就把课程信息全删除了。
插入异常 - 学生未选课，无法记录进数据库。
更新异常 - 调整课程学分，所有行都调整。

根据 2NF 可以拆分如下：

-- 学生表
student（学号、姓名）
-- 课程表
course（课程号、学分）
-- 学生课程关系表
student_course（学号、课程号、成绩）

第三范式 (3NF)

如果一个关系属于第二范式，并且在两个（或多个）非主键属性之间不存在函数依赖（非主键属性之间的函数依赖也称为传递依赖），那么这个关系属于第三范式。

3NF 是对字段的冗余性，要求任何字段不能由其他字段派生出来，它要求字段没有冗余，即不存在传递依赖。

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

1 2	-- 学生表 student（学号、姓名、年龄、班级号、班主任）

上表属于第二范式，因为主键由单个属性组成（学号）。

因为存在依赖传递：（学号） → （学生）→（所在班级） → （班主任）。

可能会存在问题：

数据冗余 - 有重复值；
更新异常 - 有重复的冗余信息，修改时需要同时修改多条记录，否则会出现数据不一致的情况 。

可以基于 3NF 拆解：

1 2	student（学号、姓名、年龄、所在班级号） class（班级号、班主任）

【简单】为什么不推荐使用存储过程？

存储过程的优点：

执行效率高：一次编译多次使用。
安全性强：在设定存储过程的时候可以设置对用户的使用权限，这样就和视图一样具有较强的安全性。
可复用：将代码封装，可以提高代码复用。
性能好
- 由于是预先编译，因此具有很高的性能。
- 一个存储过程替代大量 T_SQL 语句，可以降低网络通信量，提高通信速率。

存储过程的缺点：

可移植性差：存储过程不能跨数据库移植。由于不同数据库的存储过程语法几乎都不一样，十分难以维护（不通用）。
调试困难：只有少数 DBMS 支持存储过程的调试。对于复杂的存储过程来说，开发和维护都不容易。
版本管理困难：比如数据表索引发生变化了，可能会导致存储过程失效。我们在开发软件的时候往往需要进行版本管理，但是存储过程本身没有版本控制，版本迭代更新的时候很麻烦。
不适合高并发的场景：高并发的场景需要减少数据库的压力，有时数据库会采用分库分表的方式，而且对可扩展性要求很高，在这种情况下，存储过程会变得难以维护，增加数据库的压力，显然就不适用了。

_综上，存储过程的优缺点都非常突出，是否使用一定要慎重，需要根据具体应用场景来权衡_。

MySQL CRUD

扩展阅读：

《SQL 必知必会》 - SQL 的基本概念和语法【入门】

《MySQL 必知必会》 - MySQL 的基本概念和语法【入门】

【中等】如何避免重复插入数据？

在 mysql 中，当存在主键冲突或唯一键冲突的情况下，根据插入策略不同，一般有以下三种避免方法：

INSERT IGNORE INTO：若无则插入，若有则忽略
REPLACE INTO：若无则插入，若有则先删除后插入
INSERT INTO ... ON DUPLICATE KEY UPDATE：若无则插入，若有则更新

下面结合示例来说明三种方式的效果。

下面是示例的初始化准备：

-- 建表
CREATE TABLE `user` (
  `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `name` VARCHAR(255) NOT NULL COMMENT '名称',
  `age` INT(3) DEFAULT '0' COMMENT '年龄',
  PRIMARY KEY (`id`),
  UNIQUE KEY `name`(`name`)
) DEFAULT CHARSET = utf8mb4;

-- 测试数据
INSERT INTO `user`
VALUES (1, '刘备', 30);
INSERT INTO `user`
VALUES (2, '关羽', 28);

::: tabs#避免重复插入数据

@tab INSERT IGNORE INTO

INSERT IGNORE INTO 会根据主键或者唯一键判断，忽略数据库中已经存在的数据：

若数据库没有该条数据，就插入为新的数据，跟普通的 INSERT INTO 一样
若数据库有该条数据，就忽略这条插入语句，不执行插入操作

INSERT IGNORE INTO user (name, age)
VALUES ('关羽', 29), ('张飞', 25);

-- 最终数据
+----+--------+------+
| id | name   | age  |
+----+--------+------+
|  1 | 刘备   |   30 |
|  2 | 关羽   |   28 |
|  3 | 张飞   |   25 |
+----+--------+------+

@tab REPLACE INTO

REPLACE INTO 会根据主键或者唯一键判断：

若表中已存在该数据，则先删除此行数据，然后插入新的数据，相当于 delete + insert
若表中不存在该数据，则直接插入新数据，跟普通的 insert into 一样

REPLACE INTO user(id, name, age)
VALUES (2,  '关羽', 29), (4,  '赵云', 22);

-- 最终数据
+----+--------+------+
| id | name   | age  |
+----+--------+------+
|  1 | 刘备   |   30 |
|  2 | 关羽   |   29 |
|  3 | 张飞   |   25 |
|  4 | 赵云   |   22 |
+----+--------+------+

@tab INSERT … ON DUPLICATE KEY UPDATE

INSERT ... ON DUPLICATE KEY UPDATE 会根据主键或者唯一键判断：

若数据库已有该数据，则直接更新原数据，相当于 UPDATE
若数据库没有该数据，则插入为新的数据，相当于 INSERT

INSERT INTO user(id, name, age)
VALUES (2, '关羽', 27)
ON DUPLICATE KEY UPDATE name=values(name), age=values(age);

-- 最终数据
+----+--------+------+
| id | name   | age  |
+----+--------+------+
|  1 | 刘备   |   30 |
|  2 | 关羽   |   27 |
|  3 | 张飞   |   25 |
|  4 | 赵云   |   22 |
+----+--------+------+

:::

【简单】EXISTS 和 IN 有什么区别？

EXISTS 和 IN 区别如下：

功能
- EXISTS 用于判断子查询的结果集是否为空。
- IN 用于判断某个值是否在指定的集合中。
性能
- EXISTS 先外后内 - 先对外表进行循环查询，再将查询结果放入 EXISTS 的子查询中进行条件比较，一旦找到匹配记录，则终止内表子查询。
- IN 先内后外 - 先查询内表，将内表的查询结果作为条件，提供给外表查询语句进行比较。
应用
- 如果查询的两个表大小相当，那么 EXISTS 和 IN 差别不大。
- EXISTS 适合外表小而内表大的场景。
- IN 适合外表大而内表小的场景。

EXISTS 和 IN 的对比示例如下：

1 2	SELECT * FROM A WHERE cc IN (SELECT cc FROM B) SELECT * FROM A WHERE EXISTS (SELECT cc FROM B WHERE B.cc=A.cc)

当 A 小于 B 时，用 EXISTS。因为 EXISTS 的实现，相当于外表循环，实现的逻辑类似于：

1
2
3

for i in A
    for j in B
        if j.cc == i.cc then ...

当 B 小于 A 时用 IN，因为实现的逻辑类似于：

1
2
3

for i in B
    for j in A
        if j.cc == i.cc then ...

哪个表小就用哪个表来驱动，A 表小就用 EXISTS，B 表小就用 IN；如果两个表大小相当，则使用 EXISTS 和 IN 的区别不大。

【简单】UNION 和 UNION ALL 有什么区别？

UNION 和 UNION ALL 都是将两个结果集合并为一个，两个要联合的 SQL 语句字段个数必须一样，而且字段类型要“相容”（一致）。

UNION 需要进行去重扫描，因此效率较低；而 UNION ALL 不会进行去重。
UNION 会按照字段的顺序进行排序；而 UNION ALL 只是简单的将两个结果合并就返回。

【简单】JOIN 有哪些类型？

**在 SELECT, UPDATE 和 DELETE 语句中，“连接”可以用于联合多表查询。连接使用 JOIN 关键字，并且条件语句使用 ON 而不是 WHERE**。

连接可以替换子查询，并且一般比子查询的效率更快。

JOIN 有以下类型：

内连接 - 内连接又称等值连接，用于获取两个表中字段匹配关系的记录，使用 INNER JOIN 关键字。在没有条件语句的情况下返回笛卡尔积。
- 笛卡尔积 - “笛卡尔积”也称为交叉连接（CROSS JOIN），它的作用就是可以把任意表进行连接，即使这两张表不相关。
- 自连接（=） - “自连接（=）”可以看成内连接的一种，只是连接的表是自身而已。
- 自然连接（NATURAL JOIN） - “自然连接”会自动连接所有同名列。自然连接使用 NATURAL JOIN 关键字。
外连接
- 左连接（LEFT JOIN） - “左外连接”会获取左表所有记录，即使右表没有对应匹配的记录。左外连接使用 LEFT JOIN 关键字。
- 右连接（RIGHT JOIN） - “右外连接”会获取右表所有记录，即使左表没有对应匹配的记录。右外连接使用 RIGHT JOIN 关键字。

SQL JOIN

【中等】为什么不推荐多表 JOIN？

扩展阅读：https://www.cnblogs.com/eiffelzero/p/18608160

《阿里巴巴 Java 开发手册》中强制要求超过三个表禁止 join。这是为什么呢？

主要原因如下：

性能问题
- 查询效率低：当涉及多个表进行 JOIN 操作时，MySQL 需要执行多次扫描，尤其是在没有合适索引支持的情况下，性能可能会大幅下降。每增加一个表的 JOIN，查询的复杂度呈指数增长。
- 临时表的创建：MySQL 在执行复杂的多表 JOIN 时，通常会创建临时表来存储中间结果。如果数据量很大，临时表可能会溢出到磁盘，导致磁盘 I/O 操作增加，从而显著影响查询性能。
索引的作用有限
- 在多表 JOIN 的操作中，虽然每个表可以使用索引加速查询，但是当涉及到多个表的连接时，MySQL 必须在这些表之间执行 JOIN 操作，这时索引的效果会大大降低。特别是在没有合适索引的情况下，JOIN 查询会导致全表扫描，极大地降低了查询效率。
数据冗余
- 在多表 JOIN 时，如果一个表中的一行数据与另一个表中的多行数据进行匹配，结果会产生数据冗余。例如，假设有两个表：A 和 B，A 中有 10 条记录，B 中有 5 条记录。如果在 A 和 B 上做 JOIN 操作，且匹配条件满足 2 条记录，那么最终的结果会有 20 条记录（10 * 2）。这会导致数据量急剧增加，浪费存储空间。
可读性和可维护性
- 多表 JOIN 的 SQL 查询通常比较复杂，尤其是当涉及多个表、多个连接条件以及嵌套查询时，查询语句的可读性会下降，增加了维护的难度。
- 复杂的查询可能让开发者和运维人员难以理解和优化，从而增加了错误的风险。
可能引发死锁
- 在进行多个表 JOIN 操作时，如果涉及到多张表的锁定，可能会导致死锁。特别是在高并发的环境下，频繁执行 JOIN 操作容易导致多个事务之间相互等待，最终导致死锁问题。
优化器的作用有限
- MySQL 的优化器对多表 JOIN 的优化能力相对有限，尤其在处理非常复杂的查询时，可能无法有效选择最优的执行计划，从而导致性能瓶颈。
- 虽然 MySQL 使用了 查询缓存 和 索引优化，但对于多表 JOIN 的优化仍然受到很多限制，导致性能不如预期。

【中等】DROP、DELETE 和 TRUNCATE 有什么区别？

DROP 删除数据表，包括数据和结构。在 InnoDB 中，表数据存于 .ibd 文件；表结构元数据存于 .frm 文件。DROP 本质上是就是直接删除 .ibd 和 .frm 文件。
DELETE 删除数据，但保留表结构。执行 DELETE 后，空间大小不会立刻变化。这是因为，DLETE 操作实际上只是标记，被写入 biglog、redo log 和 undo log。
TRUNCATE 会删除全部表数据，且不会记录日志，因此无法回滚。TRUNCATE 执行后，自增主键重新从 1 开始。

MySQL 数据类型

扩展阅读：

MySQL 官方文档之数据类型

MySQL 如何选择 float, double, decimal

【简单】CHAR 和 VARCHAR 的区别是什么？

CHAR 和 VARCHAR 的主要区别在于：CHAR 是定长字符串，VARCHAR 是变长字符串。

长度限制
- CHAR(M) 和 VARCHAR(M) 的 M 都代表能够保存的字符数的最大值，无论是字母、数字还是中文，每个都只占用一个字符。
占用空间
- CHAR 在存储时会在右边填充空格以达到指定的长度，检索时会去掉空格；
- VARCHAR 在存储时需要使用 1 或 2 个额外字节记录字符串的长度，检索时不需要处理。
  - 字符长度超过 255，使用 2 个字节
  - 字符长度未超过 255，使用 1 个字节
应用
CHAR 适合存储长度较短或长度固定的字符串。例如 Bcrypt 算法、MD5 算法加密后的密码、身份证号码；
VARCHAR 适合存储长度不确定的字符串。例如用户昵称、文章标题等。

BINARY 和 VARBINARY 类似于 CHAR 和 VARCHAR，不同的是它们包含二进制字符串而不要非二进制字符串。也就是说，它们包含字节字符串而不是字符字符串。这说明它们没有字符集，并且排序和比较基于列值字节的数值值。

【简单】金额数据用什么类型存储？

MySQL 中有 3 种类型可以表示浮点数，分别是 FLOAT、DOUBLE 和 DECIMAL。

采用 FLOAT 和 DOUBLE 类型会丢失精度。数据的精确度取决于分配给每种数据类型的存储长度。由于计算机只能存储二进制，所以浮点型数据在存储的时候，必须转化成二进制。

单精度类型 FLOAT 存储空间为 4 字节，即 32 位。
双精度类型 DOUBLE 存储空间为 8 字节，即 64 位。

如果存储的数据转为二进制后，超过存储的位数，数据就被截断，因此存在丢失精度的可能。

更重要的是，从 MySQL 8.0.17 版本开始，当创建表用到类型 Float 或 Double 时，会抛出下面的警告：MySQL 提醒用户不该用上述浮点类型，甚至提醒将在之后版本中废弃浮点类型。

1	Specifying number of digits for floating point data types is deprecated and will be removed in a future release

【示例】丢失精度案例

-- 创建表
CREATE TABLE `test` (
  `value` FLOAT(10,2) DEFAULT NULL
);

mysql> insert into test value (131072.32);
Query OK, 1 row affected (0.01 sec)

mysql> select * from test;
+-----------+
| value     |
+-----------+
| 131072.31 |
+-----------+
1 row in set (0.02 sec)

说明：示例中，使用 FLOAT 类型，明明保留了两位小数。但是写入的数据却从 131072.32 变成了 131072.31 。

DECIMAL 类型是 MySQL 官方唯一指定能精确存储的类型。因此，对于不允许丢失精度的场景（如金额数据），可以使用 DECIMAL 类型。

然而，在海量并发的互联网业务中使用，金额字段的设计并不推荐使用 DECIMAL 类型，而更推荐使用 BIGINT 整型类型。这里会用到一个巧思：将资金类型的数据用分为单位存储，而不是用元为单位存储。如 1 元在数据库中用整型类型 100 存储。

为什么更推荐用 BIGINT 存储金钱数据？因为 DECIMAL 是个变长字段，若要定义金额字段，则定义为 DECIMAL(8,2) 是远远不够的。这样只能表示存储最大值为 999999.99，百万级的资金存储。用户的金额至少要存储百亿的字段，而统计局的 GDP 金额字段则可能达到数十万亿级别。用类型 DECIMAL 定义，不好统一。另外重要的是，类型 DECIMAL 是通过二进制实现的一种编码方式，计算效率远不如整型来的高效。因此，推荐使用 BIGINT 来存储金额相关的字段。

扩展阅读：MySQL 如何选择 float, double, decimal

【简单】如何存储 emoji 😃？

在表结构设计中，除了将列定义为 CHAR 和 VARCHAR 用以存储字符以外，还需要额外定义字符对应的字符集，因为每种字符在不同字符集编码下，对应着不同的二进制值。常见的字符集有 gbk、utf8，通常推荐把默认字符集设置为 utf8。

随着移动互联网的飞速发展，**推荐把 MySQL 的默认字符集设置为 utf8mb4**，否则，某些 emoji 表情字符无法在 UTF8 字符集下存储。

【示例】设置表的字符集为 utf8mb4

1	ALTER TABLE test CHARSET utf8mb4;

注意：上述修改只是将表的字符集修改为 utf8mb4，下次新增列时，若不显式地指定字符集，新列的字符集会变更为 utf8mb4，但对于已经存在的列，其默认字符集并不做修改。

【示例】设置表的默认字符集为 utf8mb4

正确设置 utf8mb4 字符集方法如下：

1	ALTER TABLE test CONVERT TO CHARSET utf8mb4;

【简单】时间数据选择 DATETIME 还是 TIMESTAMP？

表结构设计时，对时间字段的存储，通常会有 3 种选择：DATETIME、TIMESTAMP、INT。

DATETIME、TIMESTAMP、INT 数据表示范围：

DATETIME 占用 8 个字节，可表示范围为：1000-01-01 00:00:00.000000 到 9999-12-31 23:59:59.999999
TIMESTAMP 占用 4 个字节，可表示范围为：'1970-01-01 00:00:01.000000' UTC 到 '2038-01-09 03:14:07.999999' UTC。表示从 1970-01-01 00:00:00 到现在的毫秒数。
INT 类型就是直接存储 ‘1970-01-01 00:00:00’ 到现在的毫秒数，本质和 TIMESTAMP 一样，因此用 INT 不如直接使用 TIMESTAMP。

此外，TIMESTAMP 还存在潜在的性能问题。虽然从毫秒数转换到类型 TIMESTAMP 本身需要的 CPU 指令并不多，这并不会带来直接的性能问题。但是如果使用默认的操作系统时区，则每次通过时区计算时间时，要调用操作系统底层系统函数 __tz_convert()，而这个函数需要额外的加锁操作，以确保这时操作系统时区没有修改。所以，当大规模并发访问时，由于热点资源竞争，会产生两个问题。

性能不如 DATETIME： DATETIME 不存在时区转化问题。
性能抖动： 海量并发时，存在性能抖动问题。

为了优化 TIMESTAMP 的使用，强烈建议使用显式的时区，而不是操作系统时区。比如在配置文件中显示地设置时区，而不要使用系统时区

综上，由于 TIMESTAMP 存在时间上限和潜在性能问题，所以推荐使用 DATETIME 类型来存储时间字段。

MySQL 存储

扩展阅读：

MySQL 官方文档之 InnoDB 存储引擎

MySQL 官方文档之可选的存储引擎

【中等】MySQL 支持哪些存储引擎？

存储引擎层负责数据的存储和提取。MySQL 的存储引擎采用了插拔式架构，可以根据需要替换。

MySQL 内置了以下存储引擎：

InnoDB - InnoDB 是 MySQL 5.5 版本以后的默认存储引擎。
- 优点：支持事务，支持行级锁，支持外键约束等，并发性能不错且支持自动故障恢复。
MyISAM - MyISAM 是 MySQL 5.5 版本以前的默认存储引擎。
- 优点：速度快，占用资源少。
- 缺点：不支持事务，不支持行级锁，不支持外键约束，也不支持自动故障恢复功能。
Memory - 使用系统内存作为存储介质，以便得到更快的响应速度。不过，如果 mysqld 进程崩溃，则会导致所有的数据丢失。因此，Memory 引擎常用于临时表。
NDB - 也被称为 NDB Cluster 存储引擎，主要用于 MySQL Cluster 分布式集群环境，类似于 Oracle 的 RAC 集群。
Archive - Archive 存储引擎有很好的压缩机制，非常适合用于归档数据。
- Archive 存储引擎只支持 INSERT 和 SELECT 操作。
- Archive 存储引擎采用 zlib 算法压缩数据，压缩比可达到 1: 10。
CSV - 可以将 CSV 文件作为 MySQL 的表来处理，但这种表不支持索引。

【中等】InnoDB 和 MyISAM 有哪些差异？

对比项	MyISAM	InnoDB
外键	不支持	支持
事务	不支持	支持四种事务隔离级别
锁粒度	支持表级锁	支持表级锁、行级锁
索引	采用 B+ 树索引（非聚簇索引）	采用 B+ 树索引（聚簇索引）
表空间	小	大
关注点	性能	事务
计数器	维护了计数器，`SELECT COUNT(*)` 效率为 `O(1)`	没有维护计数器，需要全表扫描
自动故障恢复	不支持	支持（依赖于 redo log）

【中等】如何选择 MySQL 存储引擎？

大多数情况下，使用默认的 InnoDB 就够了。如果要提供提交、回滚和恢复的事务安全（ACID 兼容）能力，并要求实现并发控制，InnoDB 就是比较靠前的选择了。
如果数据表主要用来插入和查询记录，则 MyISAM 引擎提供较高的处理效率。
如果只是临时存放数据，数据量不大，并且不需要较高的数据安全性，可以选择将数据保存在内存的 MEMORY 引擎中。MySQL 中使用该引擎作为临时表，存放查询的中间结果。
如果存储归档数据，可以使用 ARCHIVE 引擎。

使用哪一种引擎可以根据需要灵活选择，因为存储引擎是基于表的，所以一个数据库中多个表可以使用不同的引擎以满足各种性能和实际需求。使用合适的存储引擎将会提高整个数据库的性能。

【中等】MySQL 有哪些物理存储文件？

MySQL 不同存储引擎的物理存储文件是不一样的。

InnoDB 的物理文件结构为：

.frm 文件：与表相关的元数据信息都存放在 frm 文件，包括表结构的定义信息等。
.ibd 文件或 .ibdata 文件：这两种文件都是存放 InnoDB 数据的文件，之所以有两种文件形式存放 InnoDB 的数据，是因为 InnoDB 的数据存储方式能够通过配置来决定是使用共享表空间存放存储数据，还是用独享表空间存放存储数据。
- 独享表空间存储方式使用.ibd文件，并且每个表一个.ibd文件
- 共享表空间存储方式使用.ibdata文件，所有表共同使用一个.ibdata文件（或多个，可自己配置）

MyISAM 的物理文件结构为：

.frm文件：与表相关的元数据信息都存放在 frm 文件，包括表结构的定义信息等。
.MYD (MYData) 文件：MyISAM 存储引擎专用，用于存储 MyISAM 表的数据。
.MYI (MYIndex) 文件：MyISAM 存储引擎专用，用于存储 MyISAM 表的索引相关信息。

【中等】什么是 Buffer Pool？

Buffer Pool（缓冲池）是 MySQL InnoDB 存储引擎的核心组件之一，它是数据库系统中的内存缓存区域，主要用于缓存表和索引的数据。

主要作用：

减少磁盘 I/O：将频繁访问的数据页缓存在内存中，避免每次查询都要从磁盘读取
提高查询性能：内存访问速度远快于磁盘访问
写缓冲：对数据的修改先在内存中进行，再通过后台线程定期刷新到磁盘

工作原理：

Buffer Pool 以页 (page) 为单位存储数据，默认每页 16KB
使用 LRU （最近最少使用）算法管理内存页
包含”年轻代”和”老年代”两个区域，防止全表扫描污染缓存

【中等】什么是 Change Buffer？

Change Buffer 是 InnoDB 存储引擎中的一种关键优化机制，主要用于提高非唯一二级索引的写操作性能。

Change Buffer 是一种特殊的内存数据结构，用于缓存对非唯一二级索引页的修改操作（INSERT、UPDATE、DELETE），当这些索引页不在缓冲池 (Buffer Pool) 中时，避免立即从磁盘读取索引页。

工作原理：

写操作发生时：当修改非唯一二级索引的数据时，InnoDB 会检查目标索引页是否在 Buffer Pool 中。
如果在：直接修改
- 如果不在：将修改操作记录到 Change Buffer
后续读取时：当需要读取该索引页时，InnoDB 会将 Change Buffer 中的修改与从磁盘读取的原始页合并。
后台合并：有专门的线程定期将 Change Buffer 中的变更合并到磁盘上的索引页。

优势

减少磁盘 I/O：避免为写入操作立即读取索引页
提高吞吐量：多个变更可以合并执行
减少随机 I/O：将随机写入转为顺序写入

适用场景：

适用于写多读少的非唯一二级索引
特别适合大量 DML 操作但索引不常被查询的业务场景

不适用场景：

唯一索引（需要立即检查唯一性约束）
索引被频繁查询（会导致频繁合并操作）

相关配置：

innodb_change_buffer_max_size：Change Buffer 最大占 Buffer Pool 的比例（默认 25%）
innodb_change_buffering：指定缓冲的变更类型（all/none/inserts/deletes 等）

MySQL 日志

【简单】MySQL 有哪些类型的日志？

MySQL 日志文件有很多，包括：

错误日志（error log）：错误日志文件对 MySQL 的启动、运行、关闭过程进行了记录，能帮助定位 MySQL 问题。
慢查询日志（slow query log）：慢查询日志是用来记录执行时间超过 long_query_time 这个变量定义的时长的查询语句。通过慢查询日志，可以查找出哪些查询语句的执行效率很低，以便进行优化。
一般查询日志（general log）：一般查询日志记录了所有对 MySQL 数据库请求的信息，无论请求是否正确执行。
二进制日志（bin log）：关于二进制日志，它记录了数据库所有执行的 DDL 和 DML 语句（除了数据查询语句 select、show 等），以事件形式记录并保存在二进制文件中。

还有两个 InnoDB 存储引擎特有的日志文件：

重做日志（redo log）：重做日志至关重要，因为它们记录了对于 InnoDB 存储引擎的事务日志。
回滚日志（undo log）：回滚日志同样也是 InnoDB 引擎提供的日志，顾名思义，回滚日志的作用就是对数据进行回滚。当事务对数据库进行修改，InnoDB 引擎不仅会记录 redo log，还会生成对应的 undo log 日志；如果事务执行失败或调用了 rollback，导致事务需要回滚，就可以利用 undo log 中的信息将数据回滚到修改之前的样子。

【简单】bin log 和 redo log 有什么区别？

bin log 会记录所有与数据库有关的日志记录，包括 InnoDB、MyISAM 等存储引擎的日志；而 redo log 只记 InnoDB 存储引擎的日志。
记录的内容不同，bin log 记录的是关于一个事务的具体操作内容，即该日志是逻辑日志。而 redo log 记录的是关于每个页（Page）的更改的物理情况。
写入的时间不同，bin log 仅在事务提交前进行提交，也就是只写磁盘一次。而在事务进行的过程中，却不断有 redo ertry 被写入 redo log 中。
写入的方式也不相同，redo log 是循环写入和擦除，bin log 是追加写入，不会覆盖已经写的文件。

【简单】redo log 如何刷盘？

redo log 的写入不是直接落到磁盘，而是在内存中设置了一片称之为 redo log buffer 的连续内存空间，也就是 redo 日志缓冲区。

在如下的一些情况中，log buffer 的数据会刷入磁盘：

log buffer 空间不足时：log buffer 的大小是有限的，如果不停的往这个有限大小的 log buffer 里塞入日志，很快它就会被填满。如果当前写入 log buffer 的 redo 日志量已经占满了 log buffer 总容量的大约一半左右，就需要把这些日志刷新到磁盘上。
事务提交时：在事务提交时，为了保证持久性，会把 log buffer 中的日志全部刷到磁盘。注意，这时候，除了本事务的，可能还会刷入其它事务的日志。
后台线程输入：有一个后台线程，大约每秒都会刷新一次log buffer中的redo log到磁盘。
正常关闭服务器时
触发 checkpoint 规则

重做日志缓存、重做日志文件都是以 块（block） 的方式进行保存的，称之为、重做日志块（redo log block）, 块的大小是固定的 512 字节。我们的 redo log 它是固定大小的，可以看作是一个逻辑上的 log group，由一定数量的 log block 组成。

它的写入方式是从头到尾开始写，写到末尾又回到开头循环写。

其中有两个标记位置：

write pos是当前记录的位置，一边写一边后移，写到第 3 号文件末尾后就回到 0 号文件开头。checkpoint是当前要擦除的位置，也是往后推移并且循环的，擦除记录前要把记录更新到磁盘。

当write_pos追上checkpoint时，表示 redo log 日志已经写满。这时候就不能接着往里写数据了，需要执行checkpoint规则腾出可写空间。

所谓的 checkpoint 规则，就是 checkpoint 触发后，将 buffer 中日志页都刷到磁盘。

【中等】日志为什么要两阶段提交？

由于 redo log 和 binlog 是两个独立的逻辑，如果不用两阶段提交，要么就是先写完 redo log 再写 binlog，或者采用反过来的顺序。

先写 redo log 后写 binlog。假设在 redo log 写完，binlog 还没有写完的时候，MySQL 进程异常重启。由于我们前面说过的，redo log 写完之后，系统即使崩溃，仍然能够把数据恢复回来，所以恢复后这一行 c 的值是 1。
- 但是由于 binlog 没写完就 crash 了，这时候 binlog 里面就没有记录这个语句。因此，之后备份日志的时候，存起来的 binlog 里面就没有这条语句。
- 然后你会发现，如果需要用这个 binlog 来恢复临时库的话，由于这个语句的 binlog 丢失，这个临时库就会少了这一次更新，恢复出来的这一行 c 的值就是 0，与原库的值不同。
先写 binlog 后写 redo log。如果在 binlog 写完之后 crash，由于 redo log 还没写，崩溃恢复以后这个事务无效，所以这一行 c 的值是 0。但是 binlog 里面已经记录了“把 c 从 0 改成 1”这个日志。所以，在之后用 binlog 来恢复的时候就多了一个事务出来，恢复出来的这一行 c 的值就是 1，与原库的值不同。

可以看到，如果不使用“两阶段提交”，那么数据库的状态就有可能和用它的日志恢复出来的库的状态不一致。

【中等】什么是 WAL？

WAL（Write-Ahead Logging）是一种数据库事务日志管理技术，确保在修改数据之前先将修改记录写入日志。它的关键点就是 先写日志，再写磁盘。

WAL 是一种通用技术，被广泛应用于各种数据库，但实现各有不同。在 InnoDB 中，redo log 就是 WAL 的实现。

大致流程为：

事务开始时，修改记录到 redo log 缓冲区。
redo log 缓冲区的数据周期性刷新到磁盘上的 redo log 文件。
事务提交时，确保 redo log 已写入磁盘，然后将数据页的修改写入数据文件。
系统崩溃时，通过 redo log 重新应用未完成的事务，恢复数据库到一致状态。

【中等】什么是 Log Buffer？

Log Buffer 用于缓冲 redo log 的写入，减少频繁刷盘 fsync 的开销，将多次写入优化为一次批量写入。

redo log 是 InnoDB 的重做日志，用于崩溃恢复，确保数据正确性。redo log 采用 WAL 机制：先写日志，再写磁盘数据，将随机写入转换为顺序写入。

Log Buffer 的刷盘时机

事务提交时：事务产生的多条 redo log 会先缓存在 Log Buffer，提交时一次性写入文件（受配置参数控制）。
容量触发：当 Log Buffer 超过总容量的一半（默认 16MB）时自动刷盘。
后台线程：每隔 1 秒定时刷盘。

配置参数innodb_flush_log_at_trx_commit

0：事务提交不刷盘，依赖后台线程每秒刷盘。性能最佳，但可能丢失 1 秒数据。
1（默认）：事务提交时同步刷盘（写 OS cache 并调用 fsync）。数据最安全，性能最差。
2：事务提交时仅写 OS cache，后台线程每秒调用 fsync。性能折中，服务器宕机可能丢失 1 秒数据。

MySQL 复制

【中等】MySQL 如何实现主从同步？

复制解决的基本问题是让一台服务器的数据与其他服务器保持同步。一台主库的数据可以同步到多台从库上，从库本身也可以被配置成另外一台服务器的主库。主库和从库之间可以有多种不同的组合方式。

MySQL 复制采用主从同步，基于 binlog（二进制日志） 实现。其流程大致为：

主库记录 DML/DDL 操作到 binlog。
从库获取 binlog 并重放，保持数据同步。

MySQL 支持三种复制方式：同步、异步、半同步。下面是三种方式的对比：

模式	机制	优点	缺点
异步复制（默认）	主库不等待从库响应	高性能	数据一致性弱（可能丢失）
同步复制	主库等待所有从库确认	强一致性	性能差，延迟高
半同步复制	主库等待至少一个从库确认	平衡性能与一致性	比异步略慢

异步复制

MySQL 异步复制可以分为三个步骤，分别由三个线程完成：

binlog dump 线程 - 主库接收事务请求，更新数据，并即时响应客户端（不等待从库）。主库上有一个特殊的 binlog dump 线程，负责将主服务器上的数据更改写入 binlog 中。
I/O 线程 - 从库上有一个 I/O 线程，负责从主库上读取 binlog，并写入从库的中继日志（relay log）中。
SQL 线程 - 从库上有一个 SQL 线程，负责重放中继日志（relay log），更新从库数据。

需要注意的是，采用异步复制有丢失数据的风险，主库崩溃时，未同步的 binlog 可能丢失（弱一致性）。

同步复制

主库必须等待所有从库完成 binlog 同步后才响应客户端。

特点：

数据强一致性（所有节点完全同步）
性能极差（延迟高，吞吐量低）
生产环境基本不使用

半同步复制

MySQL 5.7 引入了半同步复制：主库只需等待至少 N 个从库（可配置）确认即返回。

特点：

性能与可靠性的平衡（比全同步快，比异步安全）。
仅当主库和所有已确认从库同时崩溃时可能丢数据。

【中等】如何处理 MySQL 主从同步延迟？

主从延迟的常见解决方案

二次查询（兜底策略）：从库查不到时，再查主库。缺点是：恶意查询可能导致主库压力增大。
强制写后读走主库：写入后立即读的操作绑定走主库。缺点是：代码耦合，灵活性差。
关键业务读写主库，非关键业务读写分离
使用缓存：主库写入后同步缓存，查询优先查缓存。缺点是：引入缓存后，新增了一致性问题。
提升从库配置：优化从库硬件（CPU、内存、磁盘等），提高同步效率。

MySQL 主从延迟的常见原因及优化方案

原因	优化方案
从库单线程复制	启用并行复制（多线程同步）。
网络延迟	优化网络，缩短主从物理距离。
从库性能不足	升级硬件（CPU、内存、存储）。
长事务	减少主库长事务，优化 SQL。
从库数量过多	合理控制从库数量，避免主库同步压力过大。
从库查询负载高	增加从库实例，优化慢查询。

小结

主从延迟 无法完全避免，只能优化降低延迟时间。
业务层面应结合 缓存、读写分离策略、关键业务走主库 等方式综合解决。
技术层面可优化 并行复制、网络、硬件 等。

MySQL 读写分离+分库分表

【中等】如何实现 MySQL 读写分离？

读写分离的基本原理是：主服务器用来处理写操作以及实时性要求比较高的读操作，而从服务器用来处理读操作。

为何要读写分离？

有效减少锁竞争 - 主服务器只负责写，从服务器只负责读，能够有效的避免由数据更新导致的行锁竞争，使得整个系统的查询性能得到极大的改善。
提高查询吞吐量 - 通过一主多从的配置方式，可以将查询请求均匀的分散到多个数据副本，能够进一步的提升系统的处理能力。
提升数据库可用性 - 使用多主多从的方式，不但能够提升系统的吞吐量，还能够提升数据库的可用性，可以达到在任何一个数据库宕机，甚至磁盘物理损坏的情况下仍然不影响系统的正常运行。

读写分离的实现是根据 SQL 语义分析，将读操作和写操作分别路由至主库与从库。

读写分离有两种实现方式：代码封装、中间件。以下是两种方案的对比：

方案	实现方式	优点	缺点
代码封装	业务层通过代理类路由读写请求（读走从库，写走主库）。	简单灵活，可定制化 - 适合业务特定需求	主从切换需修改配置并重启 - 多语言需重复开发
中间件	独立代理服务（如 MySQL-Proxy、ShardingSphere），客户端无感知。	屏蔽多语言差异，统一管理数据源	有额外维护成本，可能成为性能瓶颈

结论：代码封装适合简单架构，但扩展性差；中间件适合复杂架构，但需维护。

常见的读写分离中间件

MySQL-Proxy（官方）
Atlas（360）
ShardingSphere（Apache）
Mycat

【中等】什么是分库分表？为何要分库分表？

什么是分库分表？

分库分表是一种数据库水平拆分方案，用于解决单机数据库的存储瓶颈和性能瓶颈问题。

分库：将数据分散到不同的数据库实例（如 DB1、DB2）。
分表：将数据分散到同一数据库的不同表（如 order_1、order_2）。

为何要分库分表？

分库分表主要基于以下理由：

并发连接 - 一个健康的单库最好保持在每秒 1000 个并发左右，不要太大。
磁盘容量 - 磁盘容量占满，会导致服务器不可用。
SQL 性能 - 单表数据量过大，会导致 SQL 执行效率低下。一般，单表超过 1000 万条数据，就可以考虑分表了。

#	分库分表前	分库分表后
并发支撑情况	MySQL 单机部署，扛不住高并发	MySQL 从单机到多机，能承受的并发增加了多倍
磁盘使用情况	MySQL 单机磁盘容量几乎撑满	拆分为多个库，数据库服务器磁盘使用率大大降低
SQL 执行性能	单表数据量太大，SQL 越跑越慢	单表数据量减少，SQL 执行效率明显提升

【中等】分库分表有哪些策略？

分库分表策略主要有两种：

根据数值范围划分
根据 Hash 划分
路由表

数值范围路由

数值范围路由，就是根据 ID、时间范围这类具有排序性的字段来进行划分。例如：用户 Id 为 1-9999 的记录分到第一个库，10000-20000 的分到第二个库，以此类推。

按这种策略划分出来的数据，具有数据连续性。

优点：数据迁移很简单。
缺点：容易产生热点问题，大量的流量都打在最新的数据上了。

Hash 路由

典型的 Hash 路由，如根据数值取模，当需要扩容时，一般以 2 的幂次方进行扩容（这样，扩容时迁移的数据量会小一些）。例如：用户 Id mod n，余数为 0 的记录放到第一个库，余数为 1 的放到第二个库，以此类推。

一般采用 预分区 的方式，提前根据 数据量 规划好 分区数，比如划分为 512 或 1024 张表，保证可支撑未来一段时间的 数据容量，再根据 负载情况 将表迁移到其他 数据库 中。扩容时通常采用 翻倍扩容，避免 数据映射 全部被打乱，导致 全量迁移 的情况。

优点：数据离散分布，不存在热点问题。
缺点：数据迁移、扩容麻烦（之前的数据需要重新计算 hash 值重新分配到不同的库或表）。当节点数量变化时，如扩容或收缩节点，数据节点映射关系需要重新计算，会导致数据的 重新迁移。

路由表

这种策略，就是用一张独立的表记录路由信息。

优点：简单、灵活，尤其是在扩容、迁移时，只需要迁移指定的数据，然后修改路由表即可。
缺点：每次查询，必须先查路由表，增加了 IO 开销。并且，如果路由表本身太大，也会面临性能瓶颈，如果想对路由表再做分库分表，将出现死循环式的路由算法选择问题。

【困难】分库分表存在哪些问题？

分库分表主要存在以下问题：

分布式 ID 问题
分布式事务问题
跨节点 Join 和聚合
跨分片的排序分页

分布式 ID 问题

一旦数据库被切分到多个物理结点上，我们将不能再依赖数据库自身的主键生成机制。一方面，某个分区数据库自生成的 ID 无法保证在全局上是唯一的；另一方面，应用程序在插入数据之前需要先获得 ID，以便进行 SQL 路由。

分布式 ID 的解决方案详见：分布式 ID

分布式事务问题

跨库事务也是分布式的数据库集群要面对的棘手事情。合理采用分表，可以在降低单表数据量的情况下，尽量使用本地事务，善于使用同库不同表可有效避免分布式事务带来的麻烦。在不能避免跨库事务的场景，有些业务仍然需要保持事务的一致性。而基于 XA 的分布式事务由于在并发度高的场景中性能无法满足需要，并未被互联网巨头大规模使用，他们大多采用最终一致性的柔性事务代替强一致事务。

分布式事务的解决方案详见：分布式事务

跨节点 Join 和聚合

分库分表后，无法直接跨节点 join 、count、order by、group by 以及聚合。

针对这类问题，普遍做法是二次查询。

在第一次查询时，获取各个节点上的结果。
在程序中将这些结果进行合并、筛选。

跨分片的排序分页

一般来讲，分页时需要按照指定字段进行排序。当排序字段就是分片字段的时候，我们通过分片规则可以比较容易定位到指定的分片，而当排序字段非分片字段的时候，情况就会变得比较复杂了。为了最终结果的准确性，我们需要在不同的分片节点中将数据进行排序并返回，并将不同分片返回的结果集进行汇总和再次排序，最后再返回给用户。如下图所示：

上面图中所描述的只是最简单的一种情况（取第一页数据），看起来对性能的影响并不大。但是，如果想取出第 10 页数据，情况又将变得复杂很多，如下图所示：

有些读者可能并不太理解，为什么不能像获取第一页数据那样简单处理（排序取出前 10 条再合并、排序）。其实并不难理解，因为各分片节点中的数据可能是随机的，为了排序的准确性，必须把所有分片节点的前 N 页数据都排序好后做合并，最后再进行整体的排序。很显然，这样的操作是比较消耗资源的，用户越往后翻页，系统性能将会越差。

那如何解决分库情况下的分页问题呢？有以下几种办法：

如果是在前台应用提供分页，则限定用户只能看前面 n 页，这个限制在业务上也是合理的，一般看后面的分页意义不大（如果一定要看，可以要求用户缩小范围重新查询）。

如果是后台批处理任务要求分批获取数据，则可以加大 page size，比如每次获取 5000 条记录，有效减少分页数（当然离线访问一般走备库，避免冲击主库）。

分库设计时，一般还有配套大数据平台汇总所有分库的记录，有些分页查询可以考虑走大数据平台。

【困难】如何实现迁库和扩容？

停机迁移/扩容（不推荐）

停机迁移/扩容是最暴力、最简单的迁移、扩容方案。

停机迁移/扩容流程：

预估停服时间，发布停服公告；停服，不允许数据访问。
编写临时的数据导入程序，从老数据库中读取数据。
将数据写入中间件。
中间件根据分片规则，将数据分发到分库（分表）中。
应用程序修改配置，重启。

停机迁移/扩容方案分析：

优点：简单、无数据一致性问题。
缺点：
- 停服时间长（数据量大时可能需数小时）。
- 风险高，失败后难以回滚。

结论：代价过高，不推荐使用。

双写迁移

双写迁移方案核心思想：

新旧库同时写入，通过开关控制读写状态（只写旧库、只写新库、双写）。
逐步切换读请求到新库，确保数据一致性。

双写迁移方案关键步骤：

双写阶段：先写旧库，再写新库，以旧库结果为准。记录旧库成功但新库失败的日志，用于补偿。
数据校验：运行对比程序，检查新旧库数据差异并修复。
灰度切换读请求：逐步将读流量切至新库，观察稳定性。
最终切换：读写全部切至新库，清理旧库冗余数据。

双写迁移流程：

修改应用程序配置，将数据同时写入老数据库和中间件。这就是所谓的双写，同时写俩库，老库和新库。
编写临时程序，读取老数据库。
将数据写入中间件。如果数据不存在，直接写入；如果数据存在，比较时间戳，只允许新数据覆盖老数据。
导入数据后，有可能数据还是存在不一致，那么就对数据进行校验，比对新老库的每条数据。如果存在差异，针对差异数据，执行（3）。循环（3）、（4）步骤，直至数据完全一致。
修改应用程序配置，将数据只写入中间件。
中间件根据分片规则，将数据分发到分库（分表）中。

双写迁移方案分析：

优点：

无需停服，业务影响小。
可灰度验证，风险可控。

缺点：

实现复杂，需处理双写一致性和补偿逻辑。

主从替换

生产环境的数据库，为了保证高可用，一般会采用主从架构。主库支持读写操作，从库支持读操作。

由于主从节点数据一致，所以将从库升级为主节点，并修改分片配置，将从节点作为分库之一，就实现了扩容。

主从替换方案流程：

解除主从关系，从库升级为主库。
应用程序，修改配置，读写通过中间件。
分库分表中间，修改分片配置。将数据按照新的规则分发。
编写临时程序，清理冗余数据。比如：原来是一个单库，数据量为 400 万。从节点升级为分库之一后，每个分库都有 400 万数据，其中 200 万是冗余数据。清理完后，进行数据校验。
为每个分库添加新的从库，保证高可用。

主从替换方案分析：

无需停机，无需全量数据迁移。
利用现有从库资源，节省成本。

三种方案对比

方案	适用场景	优点	缺点
停机迁移	小规模数据，容忍停服	简单，无一致性问题	停服时间长，风险高
双写迁移	大规模数据，要求高可用	无停服，灰度可控	复杂，需补偿机制
主从替换	已有主从架构	无需迁移数据，快速扩容	依赖现有从库，清理冗余复杂

推荐选择：

优先双写迁移：适合大多数业务，平衡风险与复杂度。
主从升级：适合已有主从且数据量适中的场景。
避免停机迁移：除非数据量极小且可接受停服。

MySQL 架构

【中等】SQL 查询语句的执行顺序是怎么样的？

所有的查询语句都是从 FROM 开始执行的，在执行过程中，每个步骤都会为下一个步骤生成一个虚拟表，这个虚拟表将作为下一个执行步骤的输入。

执行顺序

(8) SELECT (9)DISTINCT<Select_list>
(1) FROM <left_table> (3) <join_type>JOIN<right_table>
(2) ON<join_condition>
(4) WHERE<where_condition>
(5) GROUP BY<group_by_list>
(6) WITH {CUBE|ROLLUP}
(7) HAVING<having_condtion>
(10) ORDER BY<order_by_list>
(11) LIMIT<limit_number>

扩展阅读：SQL 的书写顺序和执行顺序

【困难】一条 SQL 查询语句是如何执行的？

MySQL 整个查询执行过程，总的来说分为 6 个步骤：

连接器 - 客户端和 MySQL 服务器建立连接；连接器负责跟客户端建立连接、获取权限、维持和管理连接。
查询缓存 - MySQL 服务器首先检查查询缓存，如果命中缓存，则立刻返回结果。否则进入下一阶段。MySQL 缓存弊大于利，因为失效非常频繁——任何更新都会清空查询缓存。
分析器 - MySQL 服务器进行 SQL 解析：语法分析、词法分析。
优化器 - MySQL 服务器用优化器生成对应的执行计划，根据策略选择最优索引。
执行器 - MySQL 服务器根据执行计划，调用存储引擎的 API 来执行查询。
返回结果 - MySQL 服务器将结果返回给客户端，同时缓存查询结果。

【困难】一条 SQL 更新语句是如何执行的？

更新流程和查询的流程大致相同，不同之处在于：更新流程还涉及两个重要的日志模块：

redo log（重做日志）
- InnoDB 存储引擎独有的日志（物理日志）
- 采用循环写入
bin log（归档日志）
- MySQL Server 层通用日志（逻辑日志）
- 采用追加写入

为了保证 redo log 和 bin log 的数据一致性，所以采用两阶段提交方式更新日志。

【困难】MySQL 如何选择执行计划？

MySQL 执行计划选择机制

MySQL 通过优化器（Optimizer）选择执行计划，核心流程如下：

执行计划生成步骤

解析 SQL：生成语法树，检查表/列是否存在
预处理阶段：展开视图、优化子查询
优化器核心工作：
- 生成候选执行计划（全表扫描、索引扫描、JOIN 顺序等）
- 成本估算（基于统计信息计算每个计划的 I/O、CPU 消耗）
- 选择成本最低的计划

影响执行计划的关键因素

因素	说明	示例
统计信息	表大小、索引区分度等	`ANALYZE TABLE`更新统计
索引情况	可用索引及其选择性	高区分度索引优先
查询复杂度	JOIN/子查询数量	简单查询优先走索引
系统变量	优化器开关配置	`optimizer_switch`参数
HINT 指令	强制干预优化器	`/+ INDEX(idx_name) /`

成本估算模型

优化器主要计算：

I/O 成本：读取数据页的代价
CPU 成本：处理数据的计算代价
内存成本：排序/临时表消耗

1
2
3

总成本 = （数据页读取数 × 单页 I/O 成本）
       + （扫描行数 × 行 CPU 处理成本）
       + （排序行数 × 排序成本）

查看和干预执行计划

-- 查看执行计划
EXPLAIN SELECT * FROM users WHERE age > 20;

-- 强制使用索引（慎用）
SELECT /*+ INDEX(users idx_age) */ * FROM users WHERE age > 20;

-- 更新统计信息
ANALYZE TABLE users;

常见执行计划问题

索引失效：函数计算、隐式类型转换

1 2	-- 反例：索引失效 SELECT * FROM users WHERE YEAR(create_time) = 2023;

错误 JOIN 顺序：解决方案：使用STRAIGHT_JOIN强制顺序
临时表/文件排序：关注EXPLAIN中的Using temporary/Using filesort

优化建议

定期ANALYZE TABLE更新统计信息
避免在索引列上使用函数
使用覆盖索引减少回表
监控performance_schema中的 SQL 执行历史

注意：MySQL 8.0 引入直方图统计（histogram）和代价模型改进，大幅提升复杂查询的计划准确性。

【困难】`order by` 是怎么工作的？

用 explain 命令查看执行计划时，Extra 这个字段中的“Using filesort”表示的就是需要排序。

全字段排序

1	select city,name,age from t where city='杭州' order by name limit 1000;

这个语句执行流程如下所示：

执行流程：

初始化 sort_buffer，确定放入需要排序的字段（如 name、city、age）。
从索引中找到满足条件的记录，取出对应的字段值存入 sort_buffer。
对 sort_buffer 中的数据按照排序字段进行排序。
返回排序后的结果。

内存与磁盘排序：

如果排序数据量小于 sort_buffer_size，排序在内存中完成。
如果数据量过大，MySQL 会使用临时文件进行外部排序（归并排序）。MySQL 将需要排序的数据分成 N 份，每一份单独排序后存在这些临时文件中。然后把这 N 个有序文件再合并成一个有序的大文件。

优化器追踪：通过 OPTIMIZER_TRACE 可以查看排序过程中是否使用了临时文件（number_of_tmp_files）。

rowid 排序

执行流程：
- 当单行数据过大时，MySQL 会采用 rowid 排序，只将排序字段（如 name）和主键 id 放入 sort_buffer。
- 排序完成后，根据 id 回表查询其他字段（如 city、age）。
性能影响：rowid 排序减少了 sort_buffer 的内存占用，但增加了回表操作，导致更多的磁盘 I/O。

全字段排序 VS rowid 排序

内存优先：
- 如果内存足够大，MySQL 优先使用全字段排序，以减少磁盘访问。
- 只有在内存不足时，才会使用 rowid 排序。
设计思想：如果内存够，就要多利用内存，尽量减少磁盘访问。

并不是所有的 order by 语句，都需要排序操作的。MySQL 之所以需要生成临时表，并且在临时表上做排序操作，其原因是原来的数据都是无序的。如果查询的字段和排序字段可以通过联合索引覆盖，MySQL 可以直接利用索引的有序性，避免排序操作。

MySQL 优化

【简单】如何发现慢 SQL？

慢 SQL 的监控主要通过两个途径：

慢查询日志：开启 MySQL 的慢查询日志，再通过一些工具比如 mysqldumpslow 去分析对应的慢查询日志，当然现在一般的云厂商都提供了可视化的平台。
服务监控：可以在业务的基建中加入对慢 SQL 的监控，常见的方案有字节码插桩、连接池扩展、ORM 框架过程，对服务运行中的慢 SQL 进行监控和告警。

【简单】什么是执行计划？

“执行计划”是对 SQL 查询语句在数据库中执行过程的描述。如果要分析某条 SQL 的性能问题，通常需要先查看 SQL 的执行计划，排查每一步 SQL 执行是否存在问题。

很多数据库都支持执行计划，MySQL 也不例外。在 MySQL 中，用户可以通过 EXPLAIN 命令查看优化器针对指定 SQL 生成的逻辑执行计划。

【示例】MySQL 执行计划示例

mysql> explain select * from user_info where id = 2
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: user_info
   partitions: NULL
         type: const
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 8
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

执行计划返回结果参数说明：

id - SELECT 查询的标识符。每个 SELECT 都会自动分配一个唯一的标识符。
select_type - SELECT 查询的类型。
- SIMPLE - 表示此查询不包含 UNION 查询或子查询。
- PRIMARY - 表示此查询是最外层的查询。
- UNION - 表示此查询是 UNION 的第二或随后的查询。
- DEPENDENT UNION - UNION 中的第二个或后面的查询语句，取决于外面的查询。
- UNION RESULT - UNION 的结果。
- SUBQUERY - 子查询中的第一个 SELECT。
- DEPENDENT SUBQUERY - 子查询中的第一个 SELECT, 取决于外面的查询。即子查询依赖于外层查询的结果。
table - 查询的是哪个表，如果给表起别名了，则显示别名。
partitions - 匹配的分区。
type - 表示从表中查询到行所执行的方式，查询方式是 SQL 优化中一个很重要的指标。执行效率由高到低依次为：
- system/const - 表中只有一行数据匹配。此时根据索引查询一次就能找到对应的数据。如果是 B+ 树索引，我们知道此时索引构造成了多个层级的树，当查询的索引在树的底层时，查询效率就越低。const 表示此时索引在第一层，只需访问一层便能得到数据。
- eq_ref - 使用唯一索引扫描。常见于多表连接中使用主键和唯一索引作为关联条件。
- ref - 非唯一索引扫描。还可见于唯一索引最左原则匹配扫描。
- range - 索引范围扫描。比如 <，>，between 等操作。
- index - 索引全表扫描。此时遍历整个索引树。
- ALL - 表示全表扫描。需要遍历全表来找到对应的行。
possible_keys - 此次查询中可能选用的索引。
key - 此次查询中实际使用的索引。如果这一项为 NULL，说明没有使用索引。
ref - 哪个字段或常数与 key 一起被使用。
rows - 显示此查询一共扫描了多少行，这个是一个估计值。
filtered - 表示此查询条件所过滤的数据的百分比。
extra - 额外的信息。
- Using index - 使用覆盖索引，无需回表。
- Using where - 服务器在存储引擎检索后过滤。
- Using temporary - 使用临时表。MySQL 在对查询结果排序时使用临时表，常见于排序 ORDER BY 和分组查询 GROUP BY。效率低，要避免这种问题的出现。
- Using filesort - 额外排序。无法利用索引完成排序时，就不得不将查询匹配数据进行排序，甚至可能会通过文件进行排序，效率很低。
- Using join buffer - 使用连接缓冲

更多内容请参考：MySQL 性能优化神器 Explain 使用分析

【简单】如何分析执行计划？

执行计划关键字段

type - 按性能从高到低排序：system > const > eq_ref > ref > range > index > ALL。目标应尽可能避免 ALL（全表扫描）。
possible_keys - 可能使用的索引。
key - 实际使用的索引。
rows - 预估需要检查的行数，值越小越好。
Extra - 包含重要补充信息。

执行计划分析步骤：

查看 type - 确保访问类型为 const 、 eq_ref 、 ref 或 range ，避免 ALL 。
查看 key - 确认是否使用了合适的索引。若 key 为 NULL 表示未使用索引，需优化。
查看 rows - 扫描的行数越少越好。
查看 Extra - 避免 Using temporary（使用临时表）和 Using filesort （额外排序）。

对应优化：

如果 type 为 ALL，考虑为 WHERE 条件列添加索引。
如果 Extra 包含 Using filesort ，优化 ORDER BY 或 GROUP BY 。
如果 rows 过大，检查索引是否有效。

【中等】如何优化 SQL？

避免不必要的列

这个是老生常谈，但还是经常会出的情况，SQL 查询的时候，应该只查询需要的列，而不要包含额外的列，像slect * 这种写法应该尽量避免。

分页优化

在数据量比较大，分页比较深的情况下，需要考虑分页的优化。

例如：

1	select * from table where type = 2 and level = 9 order by id asc limit 190289,10;

优化方案：

延迟关联

先通过 where 条件提取出主键，在将该表与原数据表关联，通过主键 id 提取数据行，而不是通过原来的二级索引提取数据行

例如：

1
2
3

select a.* from table a,
 (select id from table where type = 2 and level = 9 order by id asc limit 190289,10 ) b
 where a.id = b.id

书签方式

书签方式就是找到 limit 第一个参数对应的主键值，根据这个主键值再去过滤并 limit

例如：

1 2	select * from table where id > (select * from table where type = 2 and level = 9 order by id asc limit 190

索引优化

合理地设计和使用索引，是优化慢 SQL 的利器。

利用覆盖索引

InnoDB 使用非主键索引查询数据时会回表，但是如果索引的叶节点中已经包含要查询的字段，那它没有必要再回表查询了，这就叫覆盖索引

例如对于如下查询：

1	select name from test where city='上海'

我们将被查询的字段建立到联合索引中，这样查询结果就可以直接从索引中获取

1	alter table test add index idx_city_name (city, name);

低版本避免使用 or 查询

在 MySQL 5.0 之前的版本要尽量避免使用 or 查询，可以使用 union 或者子查询来替代，因为早期的 MySQL 版本使用 or 查询可能会导致索引失效，高版本引入了索引合并，解决了这个问题。

避免使用 != 或者 <> 操作符

SQL 中，不等于操作符会导致查询引擎放弃查询索引，引起全表扫描，即使比较的字段上有索引

解决方法：通过把不等于操作符改成 or，可以使用索引，避免全表扫描

例如，把column<>’aaa’，改成 column>’aaa’ or column<’aaa’，就可以使用索引了

适当使用前缀索引

适当地使用前缀所云，可以降低索引的空间占用，提高索引的查询效率。

比如，邮箱的后缀都是固定的“@xxx.com”，那么类似这种后面几位为固定值的字段就非常适合定义为前缀索引

1	alter table test add index index2(email(6));

PS: 需要注意的是，前缀索引也存在缺点，MySQL 无法利用前缀索引做 order by 和 group by 操作，也无法作为覆盖索引

避免列上函数运算

要避免在列字段上进行算术运算或其他表达式运算，否则可能会导致存储引擎无法正确使用索引，从而影响了查询的效率

1 2	select * from test where id + 1 = 50; select * from test where month(updateTime) = 7;

正确使用联合索引

使用联合索引的时候，注意最左匹配原则。

JOIN 优化

优化子查询

尽量使用 Join 语句来替代子查询，因为子查询是嵌套查询，而嵌套查询会新创建一张临时表，而临时表的创建与销毁会占用一定的系统资源以及花费一定的时间，同时对于返回结果集比较大的子查询，其对查询性能的影响更大

小表驱动大表

关联查询的时候要拿小表去驱动大表，因为关联的时候，MySQL 内部会遍历驱动表，再去连接被驱动表。

比如 left join，左表就是驱动表，A 表小于 B 表，建立连接的次数就少，查询速度就被加快了。

1	select name from A left join B ;

适当增加冗余字段

增加冗余字段可以减少大量的连表查询，因为多张表的连表查询性能很低，所有可以适当的增加冗余字段，以减少多张表的关联查询，这是以空间换时间的优化策略

避免使用 JOIN 关联太多的表

《阿里巴巴 Java 开发手册》规定不要 join 超过三张表，第一 join 太多降低查询的速度，第二 join 的 buffer 会占用更多的内存。

如果不可避免要 join 多张表，可以考虑使用数据异构的方式异构到 ES 中查询。

排序优化

利用索引扫描做排序

MySQL 有两种方式生成有序结果：其一是对结果集进行排序的操作，其二是按照索引顺序扫描得出的结果自然是有序的

但是如果索引不能覆盖查询所需列，就不得不每扫描一条记录回表查询一次，这个读操作是随机 IO，通常会比顺序全表扫描还慢

因此，在设计索引时，尽可能使用同一个索引既满足排序又用于查找行

例如：

1 2	--建立索引（date,staff_id,customer_id） select staff_id, customer_id from test where date = '2010-01-01' order by staff_id,customer_id;

只有当索引的列顺序和 ORDER BY 子句的顺序完全一致，并且所有列的排序方向都一样时，才能够使用索引来对结果做排序

条件下推

MySQL 处理 union 的策略是先创建临时表，然后将各个查询结果填充到临时表中最后再来做查询，很多优化策略在 union 查询中都会失效，因为它无法利用索引

最好手工将 where、limit 等子句下推到 union 的各个子查询中，以便优化器可以充分利用这些条件进行优化

此外，除非确实需要服务器去重，一定要使用 union all，如果不加 all 关键字，MySQL 会给临时表加上 distinct 选项，这会导致对整个临时表做唯一性检查，代价很高。

【中等】MySQL 中如何解决深分页问题？

深分页 (Deep Pagination) 是指当数据量很大时，查询靠后的分页数据（比如第 1000 页）性能急剧下降的问题。

解决方案有以下几种：

（1）使用索引覆盖+延迟关联

-- 原始深分页查询（性能差）
SELECT * FROM large_table ORDER BY id LIMIT 100000, 10;

-- 优化后的查询
SELECT * FROM large_table
INNER JOIN (
    SELECT id FROM large_table
    ORDER BY id
    LIMIT 100000, 10
) AS tmp USING(id);

（2）使用游标分页（记录上一页最后一条记录）

-- 第一页
SELECT * FROM large_table ORDER BY id LIMIT 10;

-- 获取上一页最后一条记录的 id=12345
-- 下一页查询
SELECT * FROM large_table
WHERE id > 12345
ORDER BY id
LIMIT 10;

（3）使用子查询优化

SELECT * FROM large_table
WHERE id >= (SELECT id FROM large_table ORDER BY id LIMIT 100000, 1)
ORDER BY id
LIMIT 10;

【中等】哪种 COUNT 性能最好？

先说结论：按照效率排序的话，COUNT（字段） < COUNT（主键 id) < COUNT(1) ≈ COUNT(*)。推荐采用 COUNT(*) 。

对于 COUNT（主键 id) 来说，InnoDB 引擎会遍历整张表，把每一行的 id 值都取出来，返回给 server 层。server 层拿到 id 后，判断是不可能为空的，就按行累加。

对于 COUNT(1) 来说，InnoDB 引擎遍历整张表，但不取值。server 层对于返回的每一行，放一个数字“1”进去，判断是不可能为空的，按行累加。

单看这两个用法的差别的话，你能对比出来，COUNT(1) 执行得要比 COUNT（主键 id) 快。因为从引擎返回 id 会涉及到解析数据行，以及拷贝字段值的操作。

对于 COUNT（字段） 来说：

如果这个“字段”是定义为 not null 的话，一行行地从记录里面读出这个字段，判断不能为 null，按行累加；
如果这个“字段”定义允许为 null，那么执行的时候，判断到有可能是 null，还要把值取出来再判断一下，不是 null 才累加。

但是 COUNT(*) 是例外，并不会把全部字段取出来，而是专门做了优化，不取值。COUNT(*) 肯定不是 null，按行累加。

InnoDB 和 MyISAM 的 count(*) 实现方式有什么区别？

不同的 MySQL 引擎中，COUNT(*) 有不同的实现方式：

MyISAM 引擎把一个表的总行数存在了磁盘上，因此执行 COUNT(*) 的时候会直接返回这个数，效率很高；
而 InnoDB 引擎就麻烦了，它执行 COUNT(*) 的时候，需要把数据一行一行地从引擎里面读出来，然后累积计数。

为什么 InnoDB 不跟 MyISAM 一样，也维护一个计数器？

因为即使是在同一个时刻的多个查询，由于多版本并发控制（MVCC）的原因，InnoDB 表“应该返回多少行”也是不确定的。

InnoDB 是索引组织表，主键索引树的叶子节点是数据，而普通索引树的叶子节点是主键值。所以，普通索引树比主键索引树小很多。对于 COUNT(*) 这样的操作，遍历哪个索引树得到的结果逻辑上都是一样的。因此，MySQL 优化器会找到最小的那棵树来遍历。

MyISAM 表虽然 COUNT(*) 很快，但是不支持事务；
show table status 命令虽然返回很快，但是不准确；
InnoDB 表直接 COUNT(*) 会遍历全表，虽然结果准确，但会导致性能问题。

如何优化查询计数？

可以使用 Redis 保存计数，但存在数据丢失和逻辑不一致的问题。
可以使用数据库其他表保存计数，利用事务的原子性和隔离性，可以避免数据丢失和逻辑不一致的问题。

参考资料

MongoDB 建模

发表于 2020-09-12 更新于 2025-09-13 分类于数据库，文档数据库， mongodb 本文字数： 15k 阅读时长 ≈ 13 分钟

MongoDB 建模

::: info 概述

数据建模是指对数据库中的数据以及相关实体间的链接进行组织。MongoDB 中的数据具有灵活的模式模型，因此：

单个集合中的文档不必具有相同的字段集。
字段的数据类型可能因集合中的文档而异。

通常，集合中的文档具有相似的结构。为确保数据模型的一致性，可以创建模式验证规则。

:::

阅读全文 »

MongoDB 运维

发表于 2020-09-09 更新于 2025-09-13 分类于数据库，文档数据库， mongodb 本文字数： 6.9k 阅读时长 ≈ 6 分钟

MongoDB 运维

::: info 概述

本文介绍了 MongoDB 的基本安装、备份和恢复、数据导入导出。

:::

阅读全文 »

sqoop

发表于 2020-09-09 更新于 2025-09-13 分类于大数据本文字数： 6.7k 阅读时长 ≈ 6 分钟

Sqoop

Sqoop 简介

Sqoop 是一种工具，旨在在 Hadoop 和关系数据库之间进行批量数据迁移的工具。

Sqoop 是一个常用的数据迁移工具，主要用于在不同存储系统之间实现数据的导入与导出：

导入数据：从 MySQL，Oracle 等关系型数据库中导入数据到 HDFS、Hive、HBase 等分布式文件存储系统中；
导出数据：从分布式文件系统中导出数据到关系数据库中。

目前 Sqoop 主要分为 Sqoop1 和 Sqoop2 两个版本，其中，版本号为 1.4.x 属于 Sqoop1，而版本号为 1.99.x 的属于 Sqoop2。这两个版本开发时的定位方向不同，体系结构具有很大的差异，因此它们之间互不兼容。

Sqoop1 功能结构简单，部署方便，提供命令行操作方式，主要适用于系统服务管理人员进行简单的数据迁移操作；Sqoop2 功能完善、操作简便，同时支持多种访问模式（命令行操作、Web 访问、Rest API），引入角色安全机制增加安全性等多种优点，但是结构复杂，配置部署更加繁琐。

Sqoop 社区提供了多种连接器，可以在很多数据存储之间进行数据迁移。

内置连接器
- 经过优化的专用 RDBMS 连接器：MySQL、PostgreSQL、Oracle、DB2、SQL Server、Netzza 等
- 通用的 JDBC 连接器：支持 JDBC 协议的数据库
第三方连接器
- 数据仓库：Teradata
- NoSQL 数据库：Couchbase

Sqoop 原理

Sqoop 的工作原理是：将执行命令转化成 MapReduce 作业来实现数据的迁移。

导入原理

在导入数据之前，Sqoop 使用 JDBC 检查导入的数据表，检索出表中的所有列以及列的 SQL 数据类型，并将这些 SQL 类型映射为 Java 数据类型。在转换后的 MapReduce 应用中使用这些对应的 Java 类型来保存字段的值，Sqoop 的代码生成器使用这些信息来创建对应表的类，用于保存从表中抽取的记录。

导出原理

在导出数据之前，Sqoop 会根据数据库连接字符串来选择一个导出方法，对于大部分系统来说，Sqoop 会选择 JDBC。Sqoop 会根据目标表的定义生成一个 Java 类，这个生成的类能够从文本中解析出记录数据，并能够向表中插入类型合适的值，然后启动一个 MapReduce 作业，从 HDFS 中读取源数据文件，使用生成的类解析出记录，并且执行选定的导出方法。

Sqoop 应用

参考手册：

Sqoop 官方文档之安装说明

Sqoop 官方文档之 Shell 命令

Sqoop 官方文档之连接器

Sqoop 与 MySQL

查询 MySQL 所有数据库

通常用于 Sqoop 与 MySQL 连通测试：

sqoop list-databases \
--connect jdbc:mysql://hadoop001:3306/ \
--username root \
--password root

查询指定数据库中所有数据表

sqoop list-tables \
--connect jdbc:mysql://hadoop001:3306/mysql \
--username root \
--password root

Sqoop 与 HDFS

MySQL 数据导入到 HDFS

导入命令

示例：导出 MySQL 数据库中的 help_keyword 表到 HDFS 的 /sqoop 目录下，如果导入目录存在则先删除再导入，使用 3 个 map tasks 并行导入。

注：help_keyword 是 MySQL 内置的一张字典表，之后的示例均使用这张表。

sqoop import \
--connect jdbc:mysql://hadoop001:3306/mysql \
--username root \
--password root \
--table help_keyword \           # 待导入的表
--delete-target-dir \            # 目标目录存在则先删除
--target-dir /sqoop \            # 导入的目标目录
--fields-terminated-by '\t'  \   # 指定导出数据的分隔符
-m 3                             # 指定并行执行的 map tasks 数量

日志输出如下，可以看到输入数据被平均 split 为三份，分别由三个 map task 进行处理。数据默认以表的主键列作为拆分依据，如果你的表没有主键，有以下两种方案：

添加 -- autoreset-to-one-mapper 参数，代表只启动一个 map task，即不并行执行；
若仍希望并行执行，则可以使用 --split-by <column-name> 指明拆分数据的参考列。

导入验证

# 查看导入后的目录
hadoop fs -ls  -R /sqoop
# 查看导入内容
hadoop fs -text  /sqoop/part-m-00000

查看 HDFS 导入目录，可以看到表中数据被分为 3 部分进行存储，这是由指定的并行度决定的。

HDFS 数据导出到 MySQL

sqoop export  \
    --connect jdbc:mysql://hadoop001:3306/mysql \
    --username root \
    --password root \
    --table help_keyword_from_hdfs \        # 导出数据存储在 MySQL 的 help_keyword_from_hdf 的表中
    --export-dir /sqoop  \
    --input-fields-terminated-by '\t'\
    --m 3

表必须预先创建，建表语句如下：

1	CREATE TABLE help_keyword_from_hdfs LIKE help_keyword;

Sqoop 与 Hive

MySQL 数据导入到 Hive

Sqoop 导入数据到 Hive 是通过先将数据导入到 HDFS 上的临时目录，然后再将数据从 HDFS 上 Load 到 Hive 中，最后将临时目录删除。可以使用 target-dir 来指定临时目录。

导入命令

sqoop import \
  --connect jdbc:mysql://hadoop001:3306/mysql \
  --username root \
  --password root \
  --table help_keyword \        # 待导入的表
  --delete-target-dir \         # 如果临时目录存在删除
  --target-dir /sqoop_hive  \   # 临时目录位置
  --hive-database sqoop_test \  # 导入到 Hive 的 sqoop_test 数据库，数据库需要预先创建。不指定则默认为 default 库
  --hive-import \               # 导入到 Hive
  --hive-overwrite \            # 如果 Hive 表中有数据则覆盖，这会清除表中原有的数据，然后再写入
  -m 3                          # 并行度

导入到 Hive 中的 sqoop_test 数据库需要预先创建，不指定则默认使用 Hive 中的 default 库。

# 查看 hive 中的所有数据库
hive>  SHOW DATABASES;
# 创建 sqoop_test 数据库
hive>  CREATE DATABASE sqoop_test;

导入验证

# 查看 sqoop_test 数据库的所有表
 hive>  SHOW  TABLES  IN  sqoop_test;
# 查看表中数据
 hive> SELECT * FROM sqoop_test.help_keyword;

可能出现的问题

如果执行报错 java.io.IOException: java.lang.ClassNotFoundException: org.apache.hadoop.hive.conf.HiveConf，则需将 Hive 安装目录下 lib 下的 hive-exec-**.jar 放到 sqoop 的 lib 。

1
2
3

[root@hadoop001 lib]# ll hive-exec-*
-rw-r--r--. 1 1106 4001 19632031 11 月 13 21:45 hive-exec-1.1.0-cdh5.15.2.jar
[root@hadoop001 lib]# cp hive-exec-1.1.0-cdh5.15.2.jar  ${SQOOP_HOME}/lib

Hive 导出数据到 MySQL

由于 Hive 的数据是存储在 HDFS 上的，所以 Hive 导入数据到 MySQL，实际上就是 HDFS 导入数据到 MySQL。

查看 Hive 表在 HDFS 的存储位置

# 进入对应的数据库
hive> use sqoop_test;
# 查看表信息
hive> desc formatted help_keyword;

Location 属性为其存储位置：

这里可以查看一下这个目录，文件结构如下：

执行导出命令

sqoop export  \
    --connect jdbc:mysql://hadoop001:3306/mysql \
    --username root \
    --password root \
    --table help_keyword_from_hive \
    --export-dir /user/hive/warehouse/sqoop_test.db/help_keyword  \
    -input-fields-terminated-by '\001' \             # 需要注意的是 hive 中默认的分隔符为 \001
    --m 3

MySQL 中的表需要预先创建：

1	CREATE TABLE help_keyword_from_hive LIKE help_keyword;

Sqoop 与 HBase

本小节只讲解从 RDBMS 导入数据到 HBase，因为暂时没有命令能够从 HBase 直接导出数据到 RDBMS。

MySQL 导入数据到 HBase

导入数据

将 help_keyword 表中数据导入到 HBase 上的 help_keyword_hbase 表中，使用原表的主键 help_keyword_id 作为 RowKey，原表的所有列都会在 keywordInfo 列族下，目前只支持全部导入到一个列族下，不支持分别指定列族。

sqoop import \
    --connect jdbc:mysql://hadoop001:3306/mysql \
    --username root \
    --password root \
    --table help_keyword \              # 待导入的表
    --hbase-table help_keyword_hbase \  # hbase 表名称，表需要预先创建
    --column-family keywordInfo \       # 所有列导入到 keywordInfo 列族下
    --hbase-row-key help_keyword_id     # 使用原表的 help_keyword_id 作为 RowKey

导入的 HBase 表需要预先创建：

# 查看所有表
hbase> list
# 创建表
hbase> create 'help_keyword_hbase', 'keywordInfo'
# 查看表信息
hbase> desc 'help_keyword_hbase'

导入验证

使用 scan 查看表数据：

全库导出

Sqoop 支持通过 import-all-tables 命令进行全库导出到 HDFS/Hive，但需要注意有以下两个限制：

所有表必须有主键；或者使用 --autoreset-to-one-mapper，代表只启动一个 map task;
你不能使用非默认的分割列，也不能通过 WHERE 子句添加任何限制。

第二点解释得比较拗口，这里列出官方原本的说明：

You must not intend to use non-default splitting column, nor impose any conditions via a WHERE clause.

全库导出到 HDFS：

sqoop import-all-tables \
    --connect jdbc:mysql://hadoop001:3306/数据库名 \
    --username root \
    --password root \
    --warehouse-dir  /sqoop_all \     # 每个表会单独导出到一个目录，需要用此参数指明所有目录的父目录
    --fields-terminated-by '\t'  \
    -m 3

全库导出到 Hive：

sqoop import-all-tables -Dorg.apache.sqoop.splitter.allow_text_splitter=true \
  --connect jdbc:mysql://hadoop001:3306/数据库名 \
  --username root \
  --password root \
  --hive-database sqoop_test \         # 导出到 Hive 对应的库
  --hive-import \
  --hive-overwrite \
  -m 3

Sqoop 数据过滤

query 参数

Sqoop 支持使用 query 参数定义查询 SQL，从而可以导出任何想要的结果集。使用示例如下：

sqoop import \
  --connect jdbc:mysql://hadoop001:3306/mysql \
  --username root \
  --password root \
  --query 'select * from help_keyword where  $CONDITIONS and  help_keyword_id < 50' \
  --delete-target-dir \
  --target-dir /sqoop_hive  \
  --hive-database sqoop_test \           # 指定导入目标数据库 不指定则默认使用 Hive 中的 default 库
  --hive-table filter_help_keyword \     # 指定导入目标表
  --split-by help_keyword_id \           # 指定用于 split 的列
  --hive-import \                        # 导入到 Hive
  --hive-overwrite \                     、
  -m 3

在使用 query 进行数据过滤时，需要注意以下三点：

必须用 --hive-table 指明目标表；
如果并行度 -m 不为 1 或者没有指定 --autoreset-to-one-mapper，则需要用 --split-by 指明参考列；
SQL 的 where 字句必须包含 $CONDITIONS，这是固定写法，作用是动态替换。

增量导入

sqoop import \
    --connect jdbc:mysql://hadoop001:3306/mysql \
    --username root \
    --password root \
    --table help_keyword \
    --target-dir /sqoop_hive  \
    --hive-database sqoop_test \
    --incremental  append  \             # 指明模式
    --check-column  help_keyword_id \    # 指明用于增量导入的参考列
    --last-value 300  \                  # 指定参考列上次导入的最大值
    --hive-import \
    -m 3

incremental 参数有以下两个可选的选项：

append：要求参考列的值必须是递增的，所有大于 last-value 的值都会被导入；
lastmodified：要求参考列的值必须是 timestamp 类型，且插入数据时候要在参考列插入当前时间戳，更新数据时也要更新参考列的时间戳，所有时间晚于 last-value 的数据都会被导入。

通过上面的解释我们可以看出来，其实 Sqoop 的增量导入并没有太多神器的地方，就是依靠维护的参考列来判断哪些是增量数据。当然我们也可以使用上面介绍的 query 参数来进行手动的增量导出，这样反而更加灵活。

类型支持

Sqoop 默认支持数据库的大多数字段类型，但是某些特殊类型是不支持的。遇到不支持的类型，程序会抛出异常 Hive does not support the SQL type for column xxx 异常，此时可以通过下面两个参数进行强制类型转换：

--map-column-java<mapping> - 重写 SQL 到 Java 类型的映射；
--map-column-hive <mapping> - 重写 Hive 到 Java 类型的映射。

示例如下，将原先 id 字段强制转为 String 类型，value 字段强制转为 Integer 类型：

1	$ sqoop import ... --map-column-java id=String,value=Integer

参考资料

MySQL 锁

发表于 2020-09-07 更新于 2025-09-13 分类于数据库，关系型数据库， mysql 本文字数： 13k 阅读时长 ≈ 12 分钟

MySQL 锁

不同存储引擎对于锁的支持粒度是不同的，由于 InnoDB 是 MySQL 的默认存储引擎，所以本文以 InnoDB 对于锁的支持进行阐述。

锁的分类

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

独享锁和共享锁

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

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

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

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

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

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

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

悲观锁和乐观锁

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

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

为什么要引入乐观锁？

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

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

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

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

【示例】MySQL 乐观锁示例

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

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

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

乐观锁更多详情可以参考：使用 mysql 乐观锁解决并发问题

全局锁、表级锁、行级锁

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

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

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

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

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

全局锁

全局锁会锁定整个数据库。全局锁的典型使用场景是：全库逻辑备份。

全局锁的用法

要给整个数据库加全局锁，可以执行以下命令：

1	flush tables with read lock

执行命名后，整个库处于只读状态，之后其他线程的以下语句会被阻塞：数据更新语句（数据的增删改）、数据定义语句（包括建表、修改表结构等）和更新类事务的提交语句。

如果要释放全局锁，可以执行以下命令：

1	unlock tables

此外，在客户端断开的时候会自动释放锁。

全局锁的限制

全局锁锁定期间，整个数据库都是只读状态，这意味着数据库不能更新数据。数据库备份很耗时，锁定整个数据库会导致业务停滞，如何避免这种问题？

在可重复读（Repeatable Read）隔离级别下，事务开启时会创建一个 Read View，并在整个事务期间使用该视图，确保数据一致性。即使其他事务在此期间修改数据，也不会影响备份事务的 Read View，从而保证备份数据的隔离性。如此一来，就无需加全局锁了。

官方自带的逻辑备份工具是 mysqldump。当 mysqldump 使用参数 –single-transaction 的时候，导数据之前就会启动一个事务，来确保拿到一致性视图。而由于 MVCC 的支持，这个过程中数据是可以正常更新的。对于全部是 InnoDB 引擎的库，建议选择使用 –single-transaction 参数，对应用会更友好。如果有的表使用了不支持事务的引擎（如 MyIsAM），那么备份就只能通过 FTWRL 方法，导致阻塞业务。

表级锁

“表级锁”会锁定整张表。用户对表进行写操作前，需要先获得写锁，这会阻塞其他用户对该表的所有读写操作。只有没有写锁时，其他用户才能获得读锁，读锁之间不会相互阻塞。

表锁

表锁就是对数据表进行锁定，锁定粒度很大，同时发生锁冲突的概率也会较高，数据访问的并发度低。不过好处在于对锁的使用开销小，加锁会很快。

**表锁的语法是 lock tables … read/write**，示例如下：

1 2	-- 为 xxx 表加读/写锁 lock tables XXX read/write

与 FTWRL 类似，可以用 unlock tables 主动释放锁，也可以在客户端断开的时候自动释放。需要注意，lock tables 语法除了会限制别的线程的读写外，也限定了本线程接下来的操作对象。

在还没有出现更细粒度的锁的时候，表锁是最常用的处理并发的方式。而对于 InnoDB 这种支持行锁的引擎，一般不使用 lock tables 命令来控制并发，毕竟锁住整个表的影响面还是太大。

元数据锁（Metadata Lock，MDL）

元数据锁，英文为 metadata lock，缩写为 MDL。

MDL 无需显式使用，访问表的时候会被自动加上。MDL 的作用是，保证读写的正确性。假设，如果一个查询正在遍历一个表中的数据，而执行期间另一个线程对这个表结构做变更，删了一列，那么查询线程拿到的结果跟表结构对不上，肯定是不行的。

MySQL 5.5 版本中引入了 MDL。

对一个表做“增删改查”操作的时候，加 MDL 读锁。读锁之间不互斥，因此可以有多个线程同时对一张表增删改查。
对一个表做“结构变更”操作的时候，加 MDL 写锁。读写锁之间、写锁之间是互斥的，用来保证变更表结构操作的安全性。因此，如果有两个线程要同时给一个表加字段，其中一个要等另一个执行完才能开始执行。

MDL 会直到事务提交才释放，在做表结构变更的时候，一定要小心不要导致锁住线上查询和更新。

如果数据库有一个长事务（所谓的长事务，就是开启了事务，但是一直还没提交），那么在对表结构做变更操作的时候，可能会发生意想不到的事情，比如下面这个顺序的场景：

首先，线程 A 先启用了事务（但是一直不提交），然后执行一条 SELECT 语句，此时就先对该表加上 MDL 读锁；
然后，线程 B 也执行了同样的 SELECT 语句，此时并不会阻塞，因为“读读”并不冲突；
接着，线程 C 修改了表字段，此时由于线程 A 的事务并没有提交，也就是 MDL 读锁还在占用着，这时线程 C 就无法申请到 MDL 写锁，就会被阻塞，

那么在线程 C 阻塞后，后续有对该表的 SELECT 语句，就都会被阻塞。如果此时有大量该表的 SELECT 语句的请求到来，就会有大量的线程被阻塞住，这时数据库的线程很快就会爆满了。

为什么线程 C 因为申请不到 MDL 写锁，而导致后续的申请读锁的查询操作也会被阻塞？这是因为申请 MDL 锁的操作会形成一个队列，队列中写锁获取优先级高于读锁，一旦出现 MDL 写锁等待，会阻塞后续该表的所有 CRUD 操作。

为了能安全的对表结构进行变更，在对表结构变更前，先要看看数据库中的长事务，是否有事务已经对表加上了 MDL 读锁，如果可以考虑 kill 掉这个长事务，然后再做表结构的变更。在 MySQL 的 information_schema 库的 innodb_trx 表中，可以查到当前执行中的事务。

意向锁（Intention Lock）

InnoDB 支持不同粒度的锁定，允许行锁和表锁共存。存在表级锁和行级锁时，必须先申请意向锁，再获取行级锁。意向锁是表级锁，表示事务稍后需要对表中的行使用哪种类型的锁（共享或独享）。意向锁是 InnoDB 自动添加的，不需要用户干预。

意向锁有两种类型：

意向共享锁（IS） - 表示事务有意向对表中的行设置共享锁（S）。
意向独享锁（IX） - 表示事务有意向对表中的行设置独享锁（X）。

比如 SELECT ... FOR SHARE 设置 IS 锁， SELECT ... FOR UPDATE 设置 IX 锁。

意向锁的规则如下：

一个事务在获得某个数据行的共享锁（S）之前，必须先获得表的意向共享锁（IS）或者更强的锁；
一个事务在获得某个数据行的独享锁（X）之前，必须先获得表的意向独享锁（IX）。

也就是，当执行插入、更新、删除操作，需要先对表加上 IX 锁，然后对该记录加 X 锁。而快照读（普通的 SELECT）是不会加行级锁的，快照读是利用 MVCC 实现一致性读，是无锁的。

不过，SELECT 也是可以对记录加共享锁和独享锁的，具体方式如下：

-- 先在表上加上 IS 锁，然后对读取的记录加 S 锁
select ... lock in share mode;

-- 先在表上加上 IX 锁，然后对读取的记录加 X 锁
select ... for update;

IX/IS 是表级锁，不会和行级的 X/S 发生冲突，而且意向锁之间也不会发生冲突，只会和共享表锁（lock tables ... read）和独享表锁（lock tables ... write）发生冲突。

如果申请的锁与现有锁兼容，则锁申请成功；反之，则锁申请失败。锁申请失败的情况下，申请锁的事务会一直等待，直到存在冲突的锁被释放。如果存在与申请的锁相冲突的锁，并且该锁迟迟得不到释放，就会导致死锁。

为什么要引入意向锁？

如果没有意向锁，那么加独享表锁时，就需要遍历表里所有记录，查看是否有记录存在独享锁，这样效率会很低。

有了意向锁，在对记录加独享锁前，会先加上表级别的意向独享锁。此时，如果需要加独享表锁，可以直接查该表是否有意向独享锁：如果有，就意味着表里已经有记录被加了独享锁。这样一来，就不用去遍历表里的记录了。

综上所述，意向锁的目的是为了快速判断表里是否有记录被加锁。

自增锁（AUTO-INC）

表里的主键通常都会设置成自增的，这是通过对主键字段声明 AUTO_INCREMENT 属性实现的。之后可以在插入数据时，可以不指定主键的值，数据库会自动给主键赋值递增的值，这主要是通过 AUTO-INC 锁实现的。

AUTO-INC 锁是特殊的表级锁，锁不是在一个事务提交后才释放，而是在执行完插入语句后就会立即释放。

在插入数据时，会加一个表级别的 AUTO-INC 锁，然后为被 AUTO_INCREMENT 修饰的字段赋值递增的值，等插入语句执行完成后，才会把 AUTO-INC 锁释放掉。

一个事务在持有 AUTO-INC 锁的过程中，其他事务的如果要向该表插入语句都会被阻塞，从而保证插入数据时，被 AUTO_INCREMENT 修饰的字段的值是连续递增的。但是，AUTO-INC 锁再对大量数据进行插入的时候，会影响插入性能，因为另一个事务中的插入会被阻塞。

因此，在 MySQL 5.1.22 版本开始，InnoDB 存储引擎提供了一种轻量级的锁来实现自增。一样也是在插入数据的时候，会为被 AUTO_INCREMENT 修饰的字段加上轻量级锁，然后给该字段赋值一个自增的值，就把这个轻量级锁释放了，而不需要等待整个插入语句执行完后才释放锁。

InnoDB 存储引擎提供了个 innodb_autoinc_lock_mode 的系统变量，是用来控制选择用 AUTO-INC 锁，还是轻量级的锁。

当 innodb_autoinc_lock_mode = 0，就采用 AUTO-INC 锁，语句执行结束后才释放锁；
当 innodb_autoinc_lock_mode = 2，就采用轻量级锁，申请自增主键后就释放锁，并不需要等语句执行后才释放。
当 innodb_autoinc_lock_mode = 1：
- 普通 insert 语句，自增锁在申请之后就马上释放；
- 类似 insert … select 这样的批量插入数据的语句，自增锁还是要等语句结束后才被释放；

以上模式中，innodb_autoinc_lock_mode = 2 是性能最高的方式，但是当搭配 binlog 的日志格式是 statement 一起使用的时候，在“主从复制的场景”中会发生数据不一致的问题。要解决这个问题，可以设置 binlog_format = row，这样在 binlog 中记录的是主库分配的自增值，从库同步数据时，就可以保持一致。

行锁

MySQL 的行锁是在引擎层由各个引擎自己实现的。但并不是所有的引擎都支持行锁，比如 MyISAM 引擎就不支持行锁。不支持行锁意味着并发控制只能使用表锁，对于这种引擎的表，同一张表上任何时刻只能有一个更新在执行，这就会影响到业务并发度。InnoDB 是支持行锁的，这也是 MyISAM 被 InnoDB 替代的重要原因之一。

在 InnoDB 引擎中，行锁是通过给索引上的索引项加锁来实现的。如果没有索引，InnoDB 将会通过隐藏的聚簇索引来对记录加锁。此外，在 InnoDB 引擎中，行锁是在需要的时候才加上的，但并不是不需要了就立刻释放，而是要等到事务结束时才释放。这个就是两阶段锁协议。因此，如果事务中需要锁多个行，要把最可能造成锁冲突、最可能影响并发度的锁尽量往后放。

行锁的具体实现算法有三种：Record Lock、Gap Lock 以及 Next-Key Lock。

记录锁（Record Lock）

记录锁（Record Lock）锁定一个记录上的索引，而不是记录本身。例如，执行 SELECT value FROM t WHERE value BETWEEN 10 and 20 FOR UPDATE; 后，会禁止任何其他事务插入、更新或删除 t.value 值在 10 到 20 范围之内的数据，因为该范围内的所有现有值之间的间隙已被锁定。

记录锁始终锁定索引记录，即使表定义为没有索引。如果表没有设置索引，InnoDB 会自动创建一个隐藏的聚簇索引并使用该索引进行记录锁定。

Record Lock 是有 S 锁和 X 锁之分的：

当一个事务对一条记录加了 S 型记录锁后，其他事务也可以继续对该记录加 S 型记录锁（S 型与 S 锁兼容），但是不可以对该记录加 X 型记录锁（S 型与 X 锁不兼容）;
当一个事务对一条记录加了 X 型记录锁后，其他事务既不可以对该记录加 S 型记录锁（S 型与 X 锁不兼容），也不可以对该记录加 X 型记录锁（X 型与 X 锁不兼容）。

【示例】记录锁示例

注：测试环境的事务隔离级别为可重复级别

初始化数据

-- 创建表
DROP TABLE IF EXISTS `t`;
CREATE TABLE `t` (
	`id` INT(10) NOT NULL AUTO_INCREMENT,
	`value` INT(10) DEFAULT 0,
	PRIMARY KEY (`id`)
)
	ENGINE = InnoDB
	DEFAULT CHARSET = `utf8`;

-- 分别插入 id 为 1、10、20 的数据
INSERT INTO `t`(`id`, `value`) VALUES (1, 1);
INSERT INTO `t`(`id`, `value`) VALUES (10, 10);
INSERT INTO `t`(`id`, `value`) VALUES (20, 20);

事务一、添加 X 型记录锁

-- 开启事务
BEGIN;

-- 对 id 为 1 的记录添加 X 型记录锁
SELECT * FROM `t` WHERE `id` = 1 FOR UPDATE;

-- 延迟 20 秒执行后续语句，保持锁定状态
SELECT SLEEP(20);

-- 释放锁
COMMIT;

事务二、被锁定的行记录无法修改

-- 修改 id = 10 的行记录，正常执行
UPDATE `t` SET `value` = 0 WHERE `id` = 10;

-- 修改 id = 1 的行记录，由于 id = 1 被 X 型记录锁锁定，直到事务一释放锁，方能执行
UPDATE `t` SET `value` = 0 WHERE `id` = 1;

间隙锁（Gap Lock）

间隙锁（Gap Lock）锁定索引之间的间隙，但是不包含索引本身。

间隙锁虽然存在 X 型间隙锁和 S 型间隙锁，但是并没有什么区别，它们彼此不冲突，不同事务可以在间隙上持有冲突锁，并不存在互斥关系。例如，事务 A 可以在某个间隙上持有 S 型间隙锁，而事务 B 在同一间隙上持有 X 型间隙锁。允许存在冲突间隙锁的原因是：如果从索引中清除记录，则必须合并不同事务在该记录上持有的间隙锁。

间隙锁只存在于可重复读隔离级别，目的是为了解决可重复读隔离级别下幻读的现象。如果将事务隔离级别更改为读已提交，则间隙锁定对搜索和索引扫描禁用，并且仅用于外键约束检查和重复键检查。

在 MySQL 中，间隙锁默认是开启的，即 innodb_locks_unsafe_for_binlog 参数值是 disable 的，且 MySQL 中默认的是 RR 事务隔离级别。

【示例】间隙锁示例

注：测试环境的事务隔离级别为可重复级别

初始化数据

-- 创建表
DROP TABLE IF EXISTS `t`;
CREATE TABLE `t` (
	`id` INT(10) NOT NULL AUTO_INCREMENT,
	`value` INT(10) DEFAULT 0,
	PRIMARY KEY (`id`)
)
	ENGINE = InnoDB
	DEFAULT CHARSET = `utf8`;

-- 分别插入 id 为 1、10、20 的数据
INSERT INTO `t`(`id`, `value`) VALUES (1, 1);
INSERT INTO `t`(`id`, `value`) VALUES (10, 10);
INSERT INTO `t`(`id`, `value`) VALUES (20, 20);

事务一、添加间隙锁

-- 开启事务
BEGIN;

-- 对 id 为 1 的记录添加间隙锁
SELECT * FROM `t` WHERE `id` BETWEEN 1 AND 10 FOR UPDATE;

-- 延迟 20 秒执行后续语句，保持锁定状态
SELECT SLEEP(20);

-- 释放锁
COMMIT;

事务二、被锁定范围内的行记录无法修改

-- 插入 id 为 1 到 10 范围之外的数据，正常执行
INSERT INTO `t`(`id`, `value`) VALUES (15, 15);

-- 更新 id 为 1 到 10 范围之外的数据，正常执行
UPDATE `t` SET `value` = 0 WHERE `id` = 20;

-- 插入 id 为 1 到 10 范围之内的数据，被阻塞
INSERT INTO `t`(`id`, `value`) VALUES (5, 5);

-- 更新 id 为 1 到 10 范围之内的数据，被阻塞
UPDATE `t` SET `value` = 0 WHERE `id` = 1;
UPDATE `t` SET `value` = 0 WHERE `id` = 10;

临键锁（Next-Key Lock）

临键锁（Next-Key Lock）是记录锁和间隙锁的结合，不仅锁定一个记录上的索引，也锁定索引之间的间隙（它锁定一个前开后闭区间）。

假设索引包含值 10、11、13 和 20，那么该索引可能的 Next-Key Lock 涵盖以下区间：

(-∞, 10]
(10, 11]
(11, 13]
(13, 20]
(20, +∞)

所以，Next-Key Lock 即能保护该记录，又能阻止其他事务将新纪录插入到被保护记录前面的间隙中。MVCC 不能解决幻读问题，Next-Key 锁就是为了解决幻读问题而提出的。在可重复读（REPEATABLE READ）隔离级别下，使用** MVCC + Next-Key 锁**可以解决幻读问题。

只有可重复读、串行化隔离级别下的特定操作才会取得间隙锁或 Next-Key Lock。在 Select、Update 和 Delete 时，除了基于唯一索引的查询之外，其它索引查询时都会获取间隙锁或 Next-Key Lock，即锁住其扫描的范围。主键索引也属于唯一索引，所以主键索引是不会使用间隙锁或 Next-Key Lock。

索引分为主键索引和非主键索引两种，如果一条 SQL 语句操作了主键索引，MySQL 就会锁定这条主键索引；如果一条语句操作了非主键索引，MySQL 会先锁定该非主键索引，再锁定相关的主键索引。在 UPDATE、DELETE 操作时，MySQL 不仅锁定 WHERE 条件扫描过的所有索引记录，而且会锁定相邻的键值，即所谓的 Next-Key Lock。

插入意向锁

插入意向锁不是意向锁，而是一种特殊的间隙锁。当一个事务试图插入一条记录时，需要判断插入位置是否已被其他事务加了间隙锁（临键锁（Next-Key Lock 也包含间隙锁）。如果有的话，插入操作就会发生阻塞，直到拥有间隙锁的那个事务提交为止（释放间隙锁的时刻）；在此期间，会生成一个插入意向锁，表明有事务想在某个区间插入新记录，但是现在处于等待状态。

假设存在值为 4 和 7 的索引记录。分别尝试插入值 5 和 6 的单独事务在获得插入行上的排他锁之前，每个事务都使用插入意向锁锁定 4 和 7 之间的间隙，但不要互相阻塞，因为行不冲突。

【示例】获取插入意向锁

初始化数据

1 2	mysql> CREATE TABLE child (id int(11) NOT NULL, PRIMARY KEY(id)) ENGINE=InnoDB; mysql> INSERT INTO child (id) values (90),(102);

事务 A 对 id 大于 100 的索引记录设置独享锁。独享锁包括了 id=102 之前的间隙锁：

mysql> BEGIN;
mysql> SELECT * FROM child WHERE id > 100 FOR UPDATE;
+-----+
| id  |
+-----+
| 102 |
+-----+

事务 B 将记录插入到间隙中。事务在等待获取独享锁时获取插入意向锁。

1 2	mysql> BEGIN; mysql> INSERT INTO child (id) VALUES (101);

死锁

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

产生死锁的场景：

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

死锁示例

（1）数据初始化

-- 创建表 test
CREATE TABLE `test` (
	`id` INT(10) UNSIGNED PRIMARY KEY AUTO_INCREMENT,
	`value` INT(10) NOT NULL
);

-- 数据初始化
INSERT INTO `test` (`id`, `value`) VALUES (1, 1);
INSERT INTO `test` (`id`, `value`) VALUES (2, 2);
INSERT INTO `test` (`id`, `value`) VALUES (3, 3);

（2）两个事务严格按下表顺序执行，产生死锁

事务 A	事务 B
`BEGIN;`	`BEGIN;`
– 查询 value = 4 的记录 `SELECT * FROM test WHERE value = 4 FOR UPDATE;` – 结果为空
	– 查询 value = 5 的记录 `SELECT * FROM test WHERE value = 5 FOR UPDATE;` – 结果为空
`INSERT INTO test (id, value) VALUES (4, 4);` – 锁等待中
	`INSERT INTO test (id, value) VALUES (5, 5);` – 锁等待中
– 由于死锁无法执行到此步骤 `COMMIT;`	– 由于死锁无法执行到此步骤 `COMMIT;`

死锁是如何产生的

行锁的具体实现算法有三种：Record Lock、Gap Lock 以及 Next-Key Lock。Record Lock 是专门对索引项加锁；Gap Lock 是对索引项之间的间隙加锁；Next-Key Lock 则是前面两种的组合，对索引项以其之间的间隙加锁。

只有在可重复读或以上隔离级别下的特定操作才会取得 Gap Lock 或 Next-Key Lock，在 Select、Update 和 Delete 时，除了基于唯一索引的查询之外，其它索引查询时都会获取 Gap Lock 或 Next-Key Lock，即锁住其扫描的范围。主键索引也属于唯一索引，所以主键索引是不会使用 Gap Lock 或 Next-Key Lock。

在 MySQL 中，Gap Lock 默认是开启的，即 innodb_locks_unsafe_for_binlog 参数值是 disable 的，且 MySQL 中默认的是可重复读事务隔离级别。

当我们执行以下查询 SQL 时，由于 value 列为非唯一索引，此时又是 RR 事务隔离级别，所以 SELECT 的加锁类型为 Gap Lock，这里的 gap 范围是 (4,+∞）。

1	SELECT * FROM test where value = 4 for update;

执行查询 SQL 语句获取的 Gap Lock 并不会导致阻塞，而当我们执行以下插入 SQL 时，会在插入间隙上再次获取插入意向锁。插入意向锁其实也是一种 gap 锁，它与 Gap Lock 是冲突的，所以当其它事务持有该间隙的 Gap Lock 时，需要等待其它事务释放 Gap Lock 之后，才能获取到插入意向锁。

以上事务 A 和事务 B 都持有间隙 (4,+∞）的 gap 锁，而接下来的插入操作为了获取到插入意向锁，都在等待对方事务的 gap 锁释放，于是就造成了循环等待，导致死锁。

1	INSERT INTO `test` (`id`, `value`) VALUES (5, 5);

另一个死锁场景

InnoDB 存储引擎的主键索引为聚簇索引，其它索引为辅助索引。如果使用辅助索引来更新数据库，就需要使用聚簇索引来更新数据库字段。如果两个更新事务使用了不同的辅助索引，或一个使用了辅助索引，一个使用了聚簇索引，就都有可能导致锁资源的循环等待。由于本身两个事务是互斥，也就构成了以上死锁的四个必要条件了。

出现死锁的步骤：

综上可知，在更新操作时，我们应该尽量使用主键来更新表字段，这样可以有效避免一些不必要的死锁发生。

避免死锁

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

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

解决死锁

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

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

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

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

MongoDB 索引

MongoDB 聚合

MongoDB 事务

MongoDB 分片

MongoDB 复制

MySQL 面试

关系数据库综合

【简单】什么是范式？什么是反范式？

第一范式 (1NF)

第二范式 (2NF)

第三范式 (3NF)

【简单】为什么不推荐使用存储过程？

MySQL CRUD

【中等】如何避免重复插入数据？

【简单】EXISTS 和 IN 有什么区别？

【简单】UNION 和 UNION ALL 有什么区别？

【简单】JOIN 有哪些类型？

【中等】为什么不推荐多表 JOIN？

【中等】DROP、DELETE 和 TRUNCATE 有什么区别？

MySQL 数据类型

【简单】CHAR 和 VARCHAR 的区别是什么？

【简单】金额数据用什么类型存储？

【简单】如何存储 emoji 😃？

【简单】时间数据选择 DATETIME 还是 TIMESTAMP？

MySQL 存储

【中等】MySQL 支持哪些存储引擎？

【中等】InnoDB 和 MyISAM 有哪些差异？

【中等】如何选择 MySQL 存储引擎？

【中等】MySQL 有哪些物理存储文件？

【中等】什么是 Buffer Pool？

【中等】什么是 Change Buffer？

MySQL 日志

【简单】MySQL 有哪些类型的日志？

【简单】bin log 和 redo log 有什么区别？

【简单】redo log 如何刷盘？

【中等】日志为什么要两阶段提交？

【中等】什么是 WAL？

【中等】什么是 Log Buffer？

MySQL 复制

【中等】MySQL 如何实现主从同步？

异步复制

同步复制

半同步复制

【中等】如何处理 MySQL 主从同步延迟？

MySQL 读写分离+分库分表

【中等】如何实现 MySQL 读写分离？

【中等】什么是分库分表？为何要分库分表？

【中等】分库分表有哪些策略？

数值范围路由

Hash 路由

路由表

【困难】分库分表存在哪些问题？

分布式 ID 问题

分布式事务问题

跨节点 Join 和聚合

跨分片的排序分页

【困难】如何实现迁库和扩容？

停机迁移/扩容（不推荐）

双写迁移

主从替换

三种方案对比

MySQL 架构

【中等】SQL 查询语句的执行顺序是怎么样的？

【困难】一条 SQL 查询语句是如何执行的？

【困难】一条 SQL 更新语句是如何执行的？

【困难】MySQL 如何选择执行计划？

MySQL 执行计划选择机制

【困难】order by 是怎么工作的？

全字段排序

rowid 排序

全字段排序 VS rowid 排序

MySQL 优化

【简单】如何发现慢 SQL？

【简单】什么是执行计划？

【简单】如何分析执行计划？

【中等】如何优化 SQL？

避免不必要的列

分页优化

索引优化

JOIN 优化

【困难】`order by` 是怎么工作的？