扩展 SQL

数据库

表

查看表的基本信息

1
2

SELECT * FROM information_schema.tables
WHERE table_schema = 'test' AND table_name = 'user';

查看表的列信息

1
2

SELECT * FROM information_schema.columns
WHERE table_schema = 'test' AND table_name = 'user';

如何批量删除大量数据

如果要根据时间范围批量删除大量数据，最简单的语句如下：

1
2

delete from orders
where timestamp < SUBDATE(CURDATE(),INTERVAL 3 month);

上面的语句，大概率执行会报错，提示删除失败，因为需要删除的数据量太大了，所以需要分批删除。

可以先通过一次查询，找到符合条件的历史订单中最大的那个订单 ID，然后在删除语句中把删除的条件转换成按主键删除。

1
2
3
4
5
6
7

select max(id) from orders
where timestamp < SUBDATE(CURDATE(),INTERVAL 3 month);

-- 分批删除，? 填上一条语句查到的最大 ID
delete from orders
where id <= ?
order by id limit 1000;

修改表的编码格式

utf8mb4 编码是 utf8 编码的超集，兼容 utf8，并且能存储 4 字节的表情字符。如果表的编码指定为 utf8，在保存 emoji 字段时会报错。

1

ALTER TABLE <tableName> CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

其他

显示哪些线程正在运行

1
2
3
4
5
6
7
8

mysql> show processlist;
+----+-----------------+-----------------+------+---------+-------+------------------------+------------------+
| Id | User            | Host            | db   | Command | Time  | State                  | Info             |
+----+-----------------+-----------------+------+---------+-------+------------------------+------------------+
|  5 | event_scheduler | localhost       | NULL | Daemon  | 40230 | Waiting on empty queue | NULL             |
| 10 | root            | localhost:10120 | NULL | Query   |     0 | init                   | show processlist |
+----+-----------------+-----------------+------+---------+-------+------------------------+------------------+
2 rows in set (0.00 sec)

Mysql 连接完成后，如果你没有后续的动作，这个连接就处于空闲状态，你可以在 show processlist 命令中看到它。其中的 Command 列显示为“Sleep”的这一行，就表示现在系统里面有一个空闲连接。客户端如果太长时间没动静，连接器就会自动将它断开。这个时间是由参数 wait_timeout 控制的，默认值是 8 小时。

参考资料

• 《SQL 必知必会》

ShardingSphere 简介

简介

ShardingSphere 组件

ShardingSphere 是一套开源的分布式数据库中间件解决方案组成的生态圈，它由 Sharding-JDBC、Sharding-Proxy 和 Sharding-Sidecar（计划中）这 3 款相互独立的产品组成。 他们均提供标准化的数据分片、分布式事务和数据库治理功能，可适用于如 Java 同构、异构语言、云原生等各种多样化的应用场景。

ShardingSphere-JDBC

定位为轻量级 Java 框架，在 Java 的 JDBC 层提供的额外服务。 它使用客户端直连数据库，以 jar 包形式提供服务，无需额外部署和依赖，可理解为增强版的 JDBC 驱动，完全兼容 JDBC 和各种 ORM 框架。

• 适用于任何基于 JDBC 的 ORM 框架，如：JPA, Hibernate, Mybatis, Spring JDBC Template 或直接使用 JDBC。
• 支持任何第三方的数据库连接池，如：DBCP, C3P0, BoneCP, Druid, HikariCP 等。
• 支持任意实现 JDBC 规范的数据库，目前支持 MySQL，Oracle，SQLServer，PostgreSQL 以及任何遵循 SQL92 标准的数据库。

Sharding-Proxy

定位为透明化的数据库代理端，提供封装了数据库二进制协议的服务端版本，用于完成对异构语言的支持。 目前提供 MySQL 和 PostgreSQL 版本，它可以使用任何兼容 MySQL/PostgreSQL 协议的访问客户端(如：MySQL Command Client, MySQL Workbench, Navicat 等)操作数据，对 DBA 更加友好。

• 向应用程序完全透明，可直接当做 MySQL/PostgreSQL 使用。
• 适用于任何兼容 MySQL/PostgreSQL 协议的的客户端。

Sharding-Sidecar（TODO）

定位为 Kubernetes 的云原生数据库代理，以 Sidecar 的形式代理所有对数据库的访问。 通过无中心、零侵入的方案提供与数据库交互的的啮合层，即 Database Mesh，又可称数据库网格。

Database Mesh 的关注重点在于如何将分布式的数据访问应用与数据库有机串联起来，它更加关注的是交互，是将杂乱无章的应用与数据库之间的交互进行有效地梳理。 使用 Database Mesh，访问数据库的应用和数据库终将形成一个巨大的网格体系，应用和数据库只需在网格体系中对号入座即可，它们都是被啮合层所治理的对象。

混合架构

ShardingSphere-JDBC 采用无中心化架构，适用于 Java 开发的高性能的轻量级 OLTP 应用；ShardingSphere-Proxy 提供静态入口以及异构语言的支持，适用于 OLAP 应用以及对分片数据库进行管理和运维的场景。

Apache ShardingSphere 是多接入端共同组成的生态圈。 通过混合使用 ShardingSphere-JDBC 和 ShardingSphere-Proxy，并采用同一注册中心统一配置分片策略，能够灵活的搭建适用于各种场景的应用系统，使得架构师更加自由地调整适合与当前业务的最佳系统架构。

功能列表

数据分片

• 分库 & 分表
• 读写分离
• 分片策略定制化
• 无中心化分布式主键

分布式事务

• 标准化事务接口
• XA 强一致事务
• 柔性事务

数据库治理

• 分布式治理
• 弹性伸缩
• 可视化链路追踪
• 数据加密

参考资料

• shardingsphere Github
• shardingsphere 官方文档

MongoDB 的 CRUD 操作

一、基本 CRUD 操作

MongoDB 的 CRUD 操作是针对 document 的读写操作。

Create 操作

MongoDB 提供以下操作向一个 collection 插入 document

• db.collection.insertOne()：插入一条 document
• db.collection.insertMany()：插入多条 document

注：以上操作都是原子操作。

插入操作的特性：

• MongoDB 中的所有写操作都是单个文档级别的原子操作。
• 如果要插入的 collection 当前不存在，则插入操作会自动创建 collection。
• 在 MongoDB 中，存储在集合中的每个文档都需要一个唯一的 _id 字段作为主键。如果插入的文档省略 _id 字段，则 MongoDB 驱动程序会自动为 _id 字段生成 ObjectId。
• 可以 MongoDB 写入操作的确认级别来控制写入行为。

【示例】插入一条 document 示例

1
2
3
4
5
6

db.inventory.insertOne({
  item: 'canvas',
  qty: 100,
  tags: ['cotton'],
  size: { h: 28, w: 35.5, uom: 'cm' }
})

【示例】插入多条 document 示例

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

db.inventory.insertMany([
  {
    item: 'journal',
    qty: 25,
    tags: ['blank', 'red'],
    size: { h: 14, w: 21, uom: 'cm' }
  },
  {
    item: 'mat',
    qty: 85,
    tags: ['gray'],
    size: { h: 27.9, w: 35.5, uom: 'cm' }
  },
  {
    item: 'mousepad',
    qty: 25,
    tags: ['gel', 'blue'],
    size: { h: 19, w: 22.85, uom: 'cm' }
  }
])

Read 操作

MongoDB 提供 db.collection.find() 方法来检索 document。

Update 操作

MongoDB 提供以下操作来更新 collection 中的 document

• db.collection.updateOne()：更新一条 document
• db.collection.updateMany()：更新多条 document
• db.collection.replaceOne()：替换一条 document

语法格式：

• db.collection.updateOne(<filter>, <update>, <options>)
• db.collection.updateMany(<filter>, <update>, <options>)
• db.collection.replaceOne(<filter>, <update>, <options>)

【示例】插入测试数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47

db.inventory.insertMany([
  {
    item: 'canvas',
    qty: 100,
    size: { h: 28, w: 35.5, uom: 'cm' },
    status: 'A'
  },
  { item: 'journal', qty: 25, size: { h: 14, w: 21, uom: 'cm' }, status: 'A' },
  { item: 'mat', qty: 85, size: { h: 27.9, w: 35.5, uom: 'cm' }, status: 'A' },
  {
    item: 'mousepad',
    qty: 25,
    size: { h: 19, w: 22.85, uom: 'cm' },
    status: 'P'
  },
  {
    item: 'notebook',
    qty: 50,
    size: { h: 8.5, w: 11, uom: 'in' },
    status: 'P'
  },
  { item: 'paper', qty: 100, size: { h: 8.5, w: 11, uom: 'in' }, status: 'D' },
  {
    item: 'planner',
    qty: 75,
    size: { h: 22.85, w: 30, uom: 'cm' },
    status: 'D'
  },
  {
    item: 'postcard',
    qty: 45,
    size: { h: 10, w: 15.25, uom: 'cm' },
    status: 'A'
  },
  {
    item: 'sketchbook',
    qty: 80,
    size: { h: 14, w: 21, uom: 'cm' },
    status: 'A'
  },
  {
    item: 'sketch pad',
    qty: 95,
    size: { h: 22.85, w: 30.5, uom: 'cm' },
    status: 'A'
  }
])

【示例】更新一条 document

1
2
3
4
5
6
7

db.inventory.updateOne(
  { item: 'paper' },
  {
    $set: { 'size.uom': 'cm', status: 'P' },
    $currentDate: { lastModified: true }
  }
)

【示例】更新多条 document

1
2
3
4
5
6
7

db.inventory.updateMany(
  { qty: { $lt: 50 } },
  {
    $set: { 'size.uom': 'in', status: 'P' },
    $currentDate: { lastModified: true }
  }
)

【示例】替换一条 document

1
2
3
4
5
6
7
8
9
10

db.inventory.replaceOne(
  { item: 'paper' },
  {
    item: 'paper',
    instock: [
      { warehouse: 'A', qty: 60 },
      { warehouse: 'B', qty: 40 }
    ]
  }
)

更新操作的特性：

• MongoDB 中的所有写操作都是单个文档级别的原子操作。
• 一旦设置了，就无法更新或替换 _id 字段。
• 除以下情况外，MongoDB 会在执行写操作后保留文档字段的顺序：
  • _id 字段始终是文档中的第一个字段。
  • 包括重命名字段名称的更新可能导致文档中字段的重新排序。
• 如果更新操作中包含 upsert : true 并且没有 document 匹配过滤器，MongoDB 会新插入一个 document；如果有匹配的 document，MongoDB 会修改或替换这些 document。

Delete 操作

MongoDB 提供以下操作来删除 collection 中的 document

• db.collection.deleteOne()：删除一条 document
• db.collection.deleteMany()：删除多条 document

删除操作的特性：

• MongoDB 中的所有写操作都是单个文档级别的原子操作。

二、批量写操作

MongoDB 通过 db.collection.bulkWrite() 方法来支持批量写操作（包括批量插入、更新、删除）。

此外，db.collection.insertMany() 方法支持批量插入操作。

有序和无序的操作

批量写操作可以有序或无序。

• 对于有序列表，MongoDB 串行执行操作。如果在写操作的处理过程中发生错误，MongoDB 将不处理列表中剩余的写操作。
• 对于无序列表，MongoDB 可以并行执行操作，但是不能保证此行为。如果在写操作的处理过程中发生错误，MongoDB 将继续处理列表中剩余的写操作。

在分片集合上执行操作的有序列表通常比执行无序列表要慢，因为对于有序列表，每个操作必须等待上一个操作完成。

默认情况下，bulkWrite() 执行有序操作。要指定无序写操作，请在选项文档中设置 ordered : false。

bulkWrite() 方法

bulkWrite() 支持以下写操作：

• insertOne
• updateOne
• updateMany
• replaceOne
• deleteOne
• deleteMany

【示例】批量写操作示例

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39

try {
  db.characters.bulkWrite([
    {
      insertOne: {
        document: {
          _id: 4,
          char: 'Dithras',
          class: 'barbarian',
          lvl: 4
        }
      }
    },
    {
      insertOne: {
        document: {
          _id: 5,
          char: 'Taeln',
          class: 'fighter',
          lvl: 3
        }
      }
    },
    {
      updateOne: {
        filter: { char: 'Eldon' },
        update: { $set: { status: 'Critical Injury' } }
      }
    },
    { deleteOne: { filter: { char: 'Brisbane' } } },
    {
      replaceOne: {
        filter: { char: 'Meldane' },
        replacement: { char: 'Tanys', class: 'oracle', lvl: 4 }
      }
    }
  ])
} catch (e) {
  print(e)
}

批量写操作策略

大量的插入操作（包括初始数据插入或常规数据导入）可能会影响分片集群的性能。对于批量插入，请考虑以下策略：

预拆分 collection

如果分片集合为空，则该集合只有一个初始 chunk，该 chunk 位于单个分片上。然后，MongoDB 必须花一些时间来接收数据，创建拆分并将拆分的块分发到可用的分片。为了避免这种性能成本，您可以按照拆分群集中的拆分块中的说明预拆分 collection。

无序写操作

要提高对分片集群的写入性能，请使用 bulkWrite()，并将可选参数顺序设置为 false。mongos 可以尝试同时将写入操作发送到多个分片。对于空集合，首先按照分片群集中的分割 chunk 中的说明预拆分 collection。

避免单调节流

如果在一次插入操作中，分片 key 单调递增，那么所有的插入数据都会存入 collection 的最后一个 chunk，也就是存入一个分片中。因此，集群的插入容量将永远不会超过该单个分片的插入容量。

如果插入量大于单个分片可以处理的插入量，并且无法避免单调递增的分片键，那么请考虑对应用程序进行以下修改：

• 反转分片密钥的二进制位。这样可以保留信息，并避免将插入顺序与值序列的增加关联起来。
• 交换第一个和最后一个 16 位字以“随机”插入。

SQL 和 MongoDB 对比

术语和概念

参考资料

• 官方
  • MongoDB 官网
  • MongoDB Github
  • MongoDB 官方免费教程
• 教程
  • MongoDB 教程
  • MongoDB 高手课

MongoDB 的聚合操作

聚合操作处理数据记录并返回计算结果。聚合操作将来自多个 document 的值分组，并可以对分组的数据执行各种操作以返回单个结果。 MongoDB 提供了三种执行聚合的方式：聚合管道，map-reduce 函数和单一目的聚合方法。

Pipeline

Pipeline 简介

MongoDB 的聚合框架以数据处理管道（Pipeline）的概念为模型。

MongoDB 通过 db.collection.aggregate() 方法支持聚合操作。并提供了 aggregate 命令来执行 pipeline。

MongoDB Pipeline 由多个阶段（stages）组成。每个阶段在 document 通过 pipeline 时都会对其进行转换。pipeline 阶段不需要为每个输入 document 都生成一个输出 document。例如，某些阶段可能会生成新 document 或过滤 document。

同一个阶段可以在 pipeline 中出现多次，但 $out、$merge,和 $geoNear 阶段除外。所有可用 pipeline 阶段可以参考：Aggregation Pipeline Stages。

• 第一阶段：$match 阶段按状态字段过滤 document，然后将状态等于“ A”的那些 document 传递到下一阶段。
• 第二阶段：$group 阶段按 cust_id 字段对 document 进行分组，以计算每个唯一 cust_id 的金额总和。

最基本的管道阶段提供过滤器，其操作类似于查询和 document 转换（修改输出 document 形式）。

其他管道操作提供了用于按特定字段对 document 进行分组和排序的工具，以及用于汇总数组（包括 document 数组）内容的工具。另外，管道阶段可以将运算符用于诸如计算平均值或连接字符串之类的任务。

聚合管道也可以在分片 collection 上操作。

Pipeline 优化

投影优化

Pipeline 可以确定是否仅需要 document 中必填字段即可获得结果。

Pipeline 串行优化

（$project、$unset、$addFields、$set） + $match 串行优化

对于包含投影阶段（$project 或 $unset 或 $addFields 或 $set），且后续跟随着 $match 阶段的 Pipeline ，MongoDB 会将所有 $match 阶段中不需要在投影阶段中计算出的值的过滤器，移动一个在投影阶段之前的新 $match 阶段。

如果 Pipeline 包含多个投影阶段 和 / 或 $match 阶段，则 MongoDB 将为每个 $match 阶段执行此优化，将每个 $match 过滤器移动到该过滤器不依赖的所有投影阶段之前。

【示例】Pipeline 串行优化示例

优化前：

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

{ $addFields: {
    maxTime: { $max: "$times" },
    minTime: { $min: "$times" }
} },
{ $project: {
    _id: 1, name: 1, times: 1, maxTime: 1, minTime: 1,
    avgTime: { $avg: ["$maxTime", "$minTime"] }
} },
{ $match: {
    name: "Joe Schmoe",
    maxTime: { $lt: 20 },
    minTime: { $gt: 5 },
    avgTime: { $gt: 7 }
} }

优化后：

1
2
3
4
5
6
7
8
9
10
11

{ $match: { name: "Joe Schmoe" } },
{ $addFields: {
    maxTime: { $max: "$times" },
    minTime: { $min: "$times" }
} },
{ $match: { maxTime: { $lt: 20 }, minTime: { $gt: 5 } } },
{ $project: {
    _id: 1, name: 1, times: 1, maxTime: 1, minTime: 1,
    avgTime: { $avg: ["$maxTime", "$minTime"] }
} },
{ $match: { avgTime: { $gt: 7 } } }

说明：

{ name: "Joe Schmoe" } 不需要计算任何投影阶段的值，所以可以放在最前面。

{ avgTime: { $gt: 7 } } 依赖 $project 阶段的 avgTime 字段，所以不能移动。

maxTime 和 minTime 字段被 $addFields 阶段所依赖，但自身不依赖其他，所以会新建一个 $match 阶段，并将其置于 $project 阶段之前。

Pipeline 并行优化

如果可能，优化阶段会将 Pipeline 阶段合并到其前身。通常，合并发生在任意序列重新排序优化之后。

$sort + $limit

当 $sort 在 $limit 之前时，如果没有中间阶段修改文档数量（例如 $unwind、$group），则优化程序可以将 $limit 合并到 $sort 中。如果有管道阶段更改了 $sort 和 $limit 阶段之间的文档数，则 MongoDB 不会将 $limit 合并到 $sort 中。

【示例】$sort + $limit

优化前：

1
2
3

{ $sort : { age : -1 } },
{ $project : { age : 1, status : 1, name : 1 } },
{ $limit: 5 }

优化后：

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

{
    "$sort" : {
       "sortKey" : {
          "age" : -1
       },
       "limit" : NumberLong(5)
    }
},
{ "$project" : {
         "age" : 1,
         "status" : 1,
         "name" : 1
  }
}

$limit + $limit

如果一个 $limit 紧随另一个 $limit，那么它们可以合并为一。

优化前：

1
2

{ $limit: 100 },
{ $limit: 10 }

优化后：

1
2
3

{
  $limit: 10
}

$skip + $skip

如果一个 $skip 紧随另一个 $skip ，那么它们可以合并为一。

优化前：

1
2

{ $skip: 5 },
{ $skip: 2 }

优化后：

1
2
3

{
  $skip: 7
}

$match + $match

如果一个 $skip 紧随另一个 $skip ，那么它们可以通过 $and 合并为一。

优化前：

1
2

{ $match: { year: 2014 } },
{ $match: { status: "A" } }

优化后：

1
2
3
4
5

{
  $match: {
    $and: [{ year: 2014 }, { status: 'A' }]
  }
}

$lookup + $unwind

如果一个 $unwind 紧随另一个 $lookup，并且 $unwind 在 $lookup 的 as 字段上运行时，优化程序可以将 $unwind 合并到 $lookup 阶段。这样可以避免创建较大的中间文档。

优化前：

1
2
3
4
5
6
7
8
9

{
  $lookup: {
    from: "otherCollection",
    as: "resultingArray",
    localField: "x",
    foreignField: "y"
  }
},
{ $unwind: "$resultingArray"}

优化后：

1
2
3
4
5
6
7
8
9

{
  $lookup: {
    from: "otherCollection",
    as: "resultingArray",
    localField: "x",
    foreignField: "y",
    unwinding: { preserveNullAndEmptyArrays: false }
  }
}

Pipeline 限制

结果集中的每个文档均受 BSON 文档大小限制（当前为 16 MB）

Pipeline 的内存限制为 100 MB。

Map-Reduce

聚合 pipeline 比 map-reduce 提供更好的性能和更一致的接口。

Map-reduce 是一种数据处理范式，用于将大量数据汇总为有用的聚合结果。为了执行 map-reduce 操作，MongoDB 提供了 mapReduce 数据库命令。

在上面的操作中，MongoDB 将 map 阶段应用于每个输入 document（即 collection 中与查询条件匹配的 document）。 map 函数分发出多个键-值对。对于具有多个值的那些键，MongoDB 应用 reduce 阶段，该阶段收集并汇总聚合的数据。然后，MongoDB 将结果存储在 collection 中。可选地，reduce 函数的输出可以通过 finalize 函数来进一步汇总聚合结果。

MongoDB 中的所有 map-reduce 函数都是 JavaScript，并在 mongod 进程中运行。 Map-reduce 操作将单个 collection 的 document 作为输入，并且可以在开始 map 阶段之前执行任意排序和限制。 mapReduce 可以将 map-reduce 操作的结果作为 document 返回，也可以将结果写入 collection。

单一目的聚合方法

MongoDB 支持一下单一目的的聚合操作：

• db.collection.estimatedDocumentCount()
• db.collection.count()
• db.collection.distinct()

所有这些操作都汇总了单个 collection 中的 document。尽管这些操作提供了对常见聚合过程的简单访问，但是它们相比聚合 pipeline 和 map-reduce，缺少灵活性和丰富的功能性。

SQL 和 MongoDB 聚合对比

MongoDB pipeline 提供了许多等价于 SQL 中常见聚合语句的操作。

下表概述了常见的 SQL 聚合语句或函数和 MongoDB 聚合操作的映射表：

【示例】

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101

db.orders.insertMany([
  {
    _id: 1,
    cust_id: 'Ant O. Knee',
    ord_date: new Date('2020-03-01'),
    price: 25,
    items: [
      { sku: 'oranges', qty: 5, price: 2.5 },
      { sku: 'apples', qty: 5, price: 2.5 }
    ],
    status: 'A'
  },
  {
    _id: 2,
    cust_id: 'Ant O. Knee',
    ord_date: new Date('2020-03-08'),
    price: 70,
    items: [
      { sku: 'oranges', qty: 8, price: 2.5 },
      { sku: 'chocolates', qty: 5, price: 10 }
    ],
    status: 'A'
  },
  {
    _id: 3,
    cust_id: 'Busby Bee',
    ord_date: new Date('2020-03-08'),
    price: 50,
    items: [
      { sku: 'oranges', qty: 10, price: 2.5 },
      { sku: 'pears', qty: 10, price: 2.5 }
    ],
    status: 'A'
  },
  {
    _id: 4,
    cust_id: 'Busby Bee',
    ord_date: new Date('2020-03-18'),
    price: 25,
    items: [{ sku: 'oranges', qty: 10, price: 2.5 }],
    status: 'A'
  },
  {
    _id: 5,
    cust_id: 'Busby Bee',
    ord_date: new Date('2020-03-19'),
    price: 50,
    items: [{ sku: 'chocolates', qty: 5, price: 10 }],
    status: 'A'
  },
  {
    _id: 6,
    cust_id: 'Cam Elot',
    ord_date: new Date('2020-03-19'),
    price: 35,
    items: [
      { sku: 'carrots', qty: 10, price: 1.0 },
      { sku: 'apples', qty: 10, price: 2.5 }
    ],
    status: 'A'
  },
  {
    _id: 7,
    cust_id: 'Cam Elot',
    ord_date: new Date('2020-03-20'),
    price: 25,
    items: [{ sku: 'oranges', qty: 10, price: 2.5 }],
    status: 'A'
  },
  {
    _id: 8,
    cust_id: 'Don Quis',
    ord_date: new Date('2020-03-20'),
    price: 75,
    items: [
      { sku: 'chocolates', qty: 5, price: 10 },
      { sku: 'apples', qty: 10, price: 2.5 }
    ],
    status: 'A'
  },
  {
    _id: 9,
    cust_id: 'Don Quis',
    ord_date: new Date('2020-03-20'),
    price: 55,
    items: [
      { sku: 'carrots', qty: 5, price: 1.0 },
      { sku: 'apples', qty: 10, price: 2.5 },
      { sku: 'oranges', qty: 10, price: 2.5 }
    ],
    status: 'A'
  },
  {
    _id: 10,
    cust_id: 'Don Quis',
    ord_date: new Date('2020-03-23'),
    price: 25,
    items: [{ sku: 'oranges', qty: 10, price: 2.5 }],
    status: 'A'
  }
])

SQL 和 MongoDB 聚合方式对比：

参考资料

• 官方
  • MongoDB 官网
  • MongoDB Github
  • MongoDB 官方免费教程
• 教程
  • MongoDB 教程
  • MongoDB 高手课

MongoDB 索引

MongoDB 索引简介

索引的作用

MongoDB 在 collection 数据级别上定义索引。

索引通常能够极大的提高查询的效率。如果没有索引，MongoDB 在读取数据时必须扫描 collection 中的每个 document 并选取那些符合查询条件的记录。

这种扫描全集合的查询是非常低效的，特别是在处理大量的数据时。查询可能要花费几十秒甚至几分钟，这种性能开销是不可接受的。

索引是特殊的数据结构，索引存储在一个易于遍历读取的数据集合中，索引是对数据库表中一列或多列的值进行排序的一种结构。

createIndex() 方法

MongoDB 使用 createIndex() 方法来创建索引。

createIndex() 语法如下：

1

db.collection.createIndex( <key and index type specification>, <options> )

createIndex() 可选参数列表如下：

【示例】使用 name 作为索引，并且按照降序排序

1

db.collection.createIndex( { name: -1 } )

参考资料

• 官方
  • MongoDB 官网
  • MongoDB Github
  • MongoDB 官方免费教程
• 教程
  • MongoDB 教程
  • MongoDB 高手课

MongoDB 事务

writeConcern 可以决定写操作到达多少个节点才算成功。

• 默认：多节点复制集不做任何设定，所以是有可能丢失数据。
• w: "majority"：大部分节点确认，就视为写成功
• w: "all"：全部节点确认，才视为写成功

journal 则定义如何才算成功。取值包括：

• true：写操作落到 journal 文件中才算成功；
• false：写操作达到内存即算作成功。

【示例】在集群中使用 writeConcern 参数

1
2
3

db.transaction.insert({ count: 1 }, { writeConcern: { w: 'majoriy' } })
db.transaction.insert({ count: 1 }, { writeConcern: { w: '4' } })
db.transaction.insert({ count: 1 }, { writeConcern: { w: 'all' } })

【示例】配置延迟节点，模拟网络延迟

1
2
3
4

conf=rs.conf()
conf.memebers[2].slaveDelay=5
conf.memebers[2].priority=0
rs.reconfig(conf)

MongoDB 复制

副本和可用性

副本可以提供冗余并提高数据可用性。在不同数据库服务器上使用多个数据副本，可以提供一定程度的容错能力，以防止单个数据库服务器宕机时，数据丢失。

在某些情况下，副本还可以提供更大的读取吞吐量。因为客户端可以将读取操作发送到不同的服务器。在不同数据中心中维护数据副本可以提高数据本地性和分布式应用程序的可用性。您还可以维护其他副本以用于专用目的：例如灾难恢复，报告或备份。

MongoDB 副本

MongoDB 中的副本集是一组维护相同数据集的 mongod 进程。一个副本集包含多个数据承载节点和一个仲裁器节点（可选）。在数据承载节点中，只有一个成员被视为主要节点，而其他节点则被视为次要节点。

主节点负责接收所有写操作。副本集只能有一个主副本，能够以 { w: "majority" } 来确认集群中节点的写操作成功情况；尽管在某些情况下，另一个 MongoDB 实例可能会暂时认为自己也是主要的。主节点在其操作日志（即 oplog）中记录了对其数据集的所有更改。

从节点复制主节点的操作日志，并将操作应用于其数据集，以便同步主节点的数据。如果主节点不可用，则符合条件的从节点将选举新的主节点。

在某些情况下（例如，有一个主节点和一个从节点，但由于成本限制，禁止添加另一个从节点），您可以选择将 mongod 实例作为仲裁节点添加到副本集。仲裁节点参加选举但不保存数据（即不提供数据冗余）。

仲裁节点将永远是仲裁节点。在选举期间，主节点可能会降级成为次节点，而次节点可能会升级成为主节点。

异步复制

慢操作

从节点复制主节点的操作日志，并将操作异步应用于其数据集。通过从节点同步主节点的数据集，即使一个或多个成员失败，副本集（MongoDB 集群）也可以继续运行。

从 4.2 版本开始，副本集的从节点记录慢操作（操作时间比设置的阈值长）的日志条目。这些慢操作在 REPL 组件下的 诊断日志 中记录了日志消息，并使用了文本 op: <oplog entry> 花费了 <num>ms。这些慢操作日志条目仅取决于慢操作阈值，而不取决于日志级别（在系统级别或组件级别），配置级别或运行缓慢的采样率。探查器不会捕获缓慢的操作日志条目。

复制延迟和流控

复制延迟（Replication lag）是指将主节点上的写操作复制到从节点上所花费的时间。较短的延迟时间是可以接受的，但是随着复制延迟的增加，可能会出现严重的问题：比如在主节点上的缓存压力。

从 MongoDB 4.2 开始，管理员可以限制主节点的写入速率，使得大多数延迟时间保持在可配置的最大值 flowControlTargetLagSeconds 以下。

默认情况下，流控是开启的。

启用流控后，随着延迟时间越来越接近 flowControlTargetLagSeconds，主对象上的写操作必须先获得令牌，然后才能进行锁定并执行写操作。通过限制每秒发出的令牌数量，流控机制尝试将延迟保持在目标以下。

故障转移

当主节点与集群中的其他成员通信的时间超过配置的 electionTimeoutMillis（默认为 10 秒）时，符合选举要求的从节点将要求选举，并提名自己为新的主节点。集群尝试完成选举新主节点并恢复正常工作。

选举完成前，副本集无法处理写入操作。如果将副本集配置为：在主节点处于脱机状态时，在次节点上运行，则副本集可以继续提供读取查询。

假设副本配置采用默认配置，则集群选择新节点的时间通常不应超过 12 秒，这包括：将主节点标记为不可用并完成选举所需的时间。可以通过修改 settings.electionTimeoutMillis 配置选项来调整此时间。网络延迟等因素可能会延长完成选举所需的时间，进而影响集群在没有主节点的情况下可以运行的时间。这些因素取决于集群实际的情况。

将默认为 10 秒的 electionTimeoutMillis 选项数值缩小，可以更快地检测到主要故障。但是，由于网络延迟等因素，集群可能会更频繁地进行选举，即使该主节点实际上处于健康状态。这可能导致 w : 1 写操作的回滚次数增加。

应用程序的连接逻辑应包括对自动故障转移和后续选举的容错处理。从 MongoDB 3.6 开始，MongoDB 驱动程序可以检测到主节点的失联，并可以自动重试一次某些写入操作。

从 MongoDB4.4 开始，MongoDB 提供镜像读取：将可选举的从节点的最近访问的数据，预热为缓存。预热从节点的缓存可以帮助在选举后更快地恢复。

读操作

读优先

默认情况下，客户端从主节点读取数据；但是，客户端可以指定读取首选项，以将读取操作发送到从节点。

异步复制到从节点意味着向从节点读取数据可能会返回与主节点不一致的数据。

包含读取操作的多文档事务必须使用读取主节点优先。给定事务中的所有操作必须路由到同一成员。

数据可见性

根据读取的关注点，客户端可以在持久化写入前查看写入结果：

• 不管写的 write concern 如何设置，其他使用 "local" 或 "available" 的读配置的客户端都可以向发布客户端确认写操作之前看到写操作的结果。
• 使用 "local" 或 "available" 读取配置的客户端可以读取数据，这些数据随后可能会在副本集故障转移期间回滚。

对于多文档事务中的操作，当事务提交时，在事务中进行的所有数据更改都将保存，并在事务外部可见。也就是说，事务在回滚其他事务时将不会提交其某些更改。在提交事务前，事务外部看不到在事务中进行的数据更改。

但是，当事务写入多个分片时，并非所有外部读操作都需要等待已提交事务的结果在所有分片上可见。例如，如果提交了一个事务，并且在分片 A 上可以看到写 1，但是在分片 B 上还看不到写 2，则在 "local" 读配置级别，外部读取可以读取写 1 的结果而看不到写 2。

镜像读取

从 MongoDB 4.4 开始，MongoDB 提供镜像读取以预热可选从节点（即优先级大于 0 的成员）的缓存。使用镜像读取（默认情况下已启用），主节点可以镜像它接收到的一部分操作，并将其发送给可选择的从节点的子集。子集的大小是可配置的。

参考资料

• 官方
  • MongoDB 官网
  • MongoDB Github
  • MongoDB 官方免费教程
• 教程
  • MongoDB 教程
  • MongoDB 高手课

MongoDB 分片

分片集群简介

当 MongoDB 需要存储海量数据时，单节点不足以存储全量数据，且可能无法提供令人满意的吞吐量。所以，可以通过 MongoDB 分片机制来支持水平扩展。

分片集群特点

对应用完全透明

数据自动均衡

动态扩容

提供三种分片方式

分片集群组件

MongoDB 分片集群含以下组件：

• shard：每个分片包含分片数据的子集。每个分片都可以部署为副本集。
• mongos：mongos 充当查询路由器，在客户端应用程序和分片集群之间提供接口。从 MongoDB 4.4 开始，mongos 可以支持 hedged reads 以最大程度地减少延迟。
• config servers：提供集群元数据存储和分片数据分布的映射。

分片集群的分布

MongoDB 复制集以 collection 为单位，将数据分布在集群中的各个分片上。最多允许 1024 个分片。

MongoDB 复制集的分片之间数据不重复，只有当所有分片都正常时，才能完整工作。

MongoDB 数据库可以同时包含分片和未分片的集合的 collection。分片 collection 会分布在集群中各节点上。而未分片的 collection 存储在主节点上。每个数据库都有其自己的主节点。

分片和未分片的 collection：

路由节点 mongos

要连接 MongoDB 分片集群，必须连接到 mongos 路由器。这包括分片和未分片的 collection。客户端不应该连接到单个分片节点进行读写操作。

连接 mongos 的方式和连接 mongod 相同，例如通过 mongo shell 或 MongoDB 驱动程序。

路由节点的作用：

• 提供集群的单一入口
• 转发应用端请求
• 选择合适数据节点进行读写
• 合并多个数据节点的返回

一般，路由节点 mongos 建议至少 2 个。

分片 Key

MongoDB 使用分片 Key 在各个分片之间分发 collection 的 document。分片 Key 由 document 中的一个或多个字段组成。

• 从 MongoDB 4.4 开始，分片 collection 中的 document 可能缺少分片 Key 字段。在跨分片分布文档时，缺少分片 Key 字段将被视为具有空值，但在路由查询时则不会。

• 在 MongoDB 4.2 及更早版本中，分片 Key 字段必须在每个 document 中存在一个分片 collection。

在分片 collection 时选择分片 Key。

• 从 MongoDB 4.4 开始，您可以通过在现有 Key 中添加一个或多个后缀字段来优化 collection 的分片 Key。
• 在 MongoDB 4.2 和更低版本中，无法在分片后更改分片 Key 的选择。

document 的分片键值决定了其在各个分片中的分布

• 从 MongoDB 4.2 开始，除非您的分片 Key 字段是不可变的_id 字段，否则您可以更新 document 的分片键值。
• 在 MongoDB 4.0 及更低版本中，文档的分片 Key 字段值是不可变的。

分片 Key 索引：要对已填充的 collection 进行分片，该 collection 必须具有以分片 Key 开头的索引。分片一个空 collection 时，如果该 collection 还没有针对指定分片 Key 的适当索引，则 MongoDB 会创建支持索引。

分片 Key 策略：分片 Key 的选择会影响分片集群的性能，效率和可伸缩性。分片 Key 及其后备索引的选择也会影响集群可以使用的分片策略。

MongoDB 分区将数据分片。每个分块都有基于分片 Key 的上下限。

为了在整个集群中的所有分片上实现块的均匀分布，均衡器在后台运行，并在各分片上迁移块。

分片策略

MongoDB 支持两种分片策略：Hash 分片和范围分片。

Hash 分片

Hash 分片策略会先计算分片 Key 字段值的哈希值；然后，根据分片键值为每个 chunk 分配一个范围。

注意：使用哈希索引解析查询时，MongoDB 会自动计算哈希值，应用程序不需要计算哈希。

尽管分片 Key 范围可能是“接近”的，但它们的哈希值不太可能在同一 chunk 上。基于 Hash 的数据分发有助于更均匀的数据分布，尤其是在分片 Key 单调更改的数据集中。

但是，Hash 分片意味着对分片 Key 做范围查询时不太可能针对单个分片，从而导致更多的集群范围内的广播操作。

范围分片

范围分片根据分片 Key 值将数据划分为多个范围。然后，根据分片 Key 值为每个 chunk 分配一个范围。

值比较近似的一系列分片 Key 更有可能驻留在同一 chunk 上。范围分片的效率取决于选择的分片 Key。分片 Key 考虑不周全会导致数据分布不均，这可能会削弱分片的某些优势或导致性能瓶颈。

分片集群中的区域

区域可以提高跨多个数据中心的分片集群的数据局部性。

在分片集群中，可以基于分片 Key 创建分片数据区域。可以将每个区域与集群中的一个或多个分片关联。分片可以与任意数量的区域关联。在平衡的集群中，MongoDB 仅将区域覆盖的 chunk 迁移到与该区域关联的分片。

每个区域覆盖一个或多个分片 Key 值范围。区域覆盖的每个范围始终包括其上下边界。

在定义要覆盖的区域的新范围时，必须使用分片 Key 中包含的字段。如果使用复合分片 Key，则范围必须包含分片 Key 的前缀。

选择分片 Key 时，应考虑将来可能使用的区域。

参考资料

• 官方
  • MongoDB 官网
  • MongoDB Github
  • MongoDB 官方免费教程
• 教程
  • MongoDB 教程
  • MongoDB 高手课

Mysql 面试

基础

EXISTS 和 IN 有什么区别？

• EXISTS - 先对外表进行循环查询，再将查询结果放入 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 关键字。

CHAR 和 VARCHAR 的区别是什么？

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

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

金钱相关的数据用什么类型存储？

MySQL 中有 3 种类型可以表示浮点数，分别是 float、double 和 decimal。

float 和 double 为什么会丢失精度？

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

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

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

【示例】丢失精度案例

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

-- 创建表
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 类型。

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

如何存储 emoji 😃？

Mysql 中的默认字符集为 utf8，无法存储 emoji，如果要存储 emoji，必须使用 utf8mb4 字符集。

设置 utf8mb4 字符集方法如下：

1
2

ALTER TABLE test
DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

什么是范式？什么是反范式？

什么是范式？

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

根据约束程度从低到高有：第一范式（1NF）、第二范式（2NF）、第三范式（3NF）、巴斯-科德范式（BCNF）等等。现代数据库设计，一般最多满足 3NF——范式过高，虽然具有对数据关系更好的约束性，但也导致数据关系表增加而令数据库 IO 更繁忙。因此，在实际应用中，本来可以交由数据库处理的关系约束，很多都是在数据库使用程序中完成的。

什么是三大范式？

三大范式，从低到高，依次为：

• 1NF 要求属性具有原子性，不可再分解。
• 2NF 要求记录有唯一标识，即实体的唯一性，即不存在部分依赖。
• 3NF 是对字段的冗余性，要求任何字段不能由其他字段派生出来，它要求字段没有冗余，即不存在传递依赖。

现代数据库设计，一般最多满足 3NF——范式过高，虽然具有对数据关系更好的约束性，但也导致数据关系表增加而令数据库 IO 更繁忙。因此，在实际应用中，本来可以交由数据库处理的关系约束，很多都是在数据库使用程序中完成的。

什么是反范式？

范式和反范式：

• 范式 - 消除冗余
• 反范式 - 适当冗余数据，以提高查询效率——空间换时间

架构

一条 SQL 查询语句是如何执行的？

1. 连接器：连接器负责跟客户端建立连接、获取权限、维持和管理连接。
2. 查询缓存：命中缓存，则直接返回结果。弊大于利，因为失效非常频繁——任何更新都会清空查询缓存。
3. 分析器
   • 词法分析：解析 SQL 关键字
   • 语法分析：生成一颗对应的语法解析树
4. 优化器
   • 根据语法树生成多种执行计划
   • 索引选择：根据策略选择最优方式
5. 执行器
   • 校验读写权限
   • 根据执行计划，调用存储引擎的 API 来执行查询
6. 存储引擎：存储数据，提供读写接口

一条 SQL 更新语句是如何执行的？

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

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

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

一条 SQL 查询语句的执行顺序是怎样的？

一条完整的 SELECT 语句内部的执行顺序是这样的：

1. FROM - 对 FROM 子句中的左表 <left_table> 和右表 <right_table> 执行笛卡儿积（Cartesianproduct），产生虚拟表 VT1
2. ON - 对虚拟表 VT1 应用 ON 筛选，只有那些符合 <join_condition> 的行才被插入虚拟表 VT2 中
3. JOIN - 如果指定了 OUTER JOIN（如 LEFT OUTER JOIN、RIGHT OUTER JOIN），那么保留表中未匹配的行作为外部行添加到虚拟表 VT2 中，产生虚拟表 VT3。如果 FROM 子句包含两个以上表，则对上一个连接生成的结果表 VT3 和下一个表重复执行步骤 1）～步骤 3），直到处理完所有的表为止
4. WHERE - 对虚拟表 VT3 应用 WHERE 过滤条件，只有符合 <where_condition> 的记录才被插入虚拟表 VT4 中
5. GROUP BY - 根据 GROUP BY 子句中的列，对 VT4 中的记录进行分组操作，产生 VT5
6. CUBE|ROLLUP - 对表 VT5 进行 CUBE 或 ROLLUP 操作，产生表 VT6
7. HAVING - 对虚拟表 VT6 应用 HAVING 过滤器，只有符合 <having_condition> 的记录才被插入虚拟表 VT7 中。
8. SELECT - 第二次执行 SELECT 操作，选择指定的列，插入到虚拟表 VT8 中
9. DISTINCT - 去除重复数据，产生虚拟表 VT9
10. ORDER BY - 将虚拟表 VT9 中的记录按照 <order_by_list> 进行排序操作，产生虚拟表 VT10。11）
11. LIMIT - 取出指定行的记录，产生虚拟表 VT11，并返回给查询用户

存储引擎

Mysql 有哪些常见存储引擎？

• InnoDB - Mysql 的默认存储引擎。支持事务、外键、表级锁和行级锁、自动崩溃恢复。索引采用 B+ 树聚簇索引。
• MyISAM - Mysql 5.1 版本前的默认存储引擎。特性丰富，但不支持事务、外键、行级锁、自动崩溃恢复。索引采用 B+ 树非聚簇索引。
• Memory - 适合快速访问数据，且数据不会被修改，重启丢失也没有关系。
• Archive - 适合存储归档数据。
• NDB - 用于 Mysql 集群场景。
• CSV - 可以将 CSV 文件作为 Mysql 的表来处理，但这种表不支持索引。

Mysql 中同一个数据库中的不同表可以设置不同的存储引擎。

InnoDB 和 MyISAM 有哪些差异？

如何选择存储引擎？

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

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

日志

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 中日志页都刷到磁盘。

日志为什么要两阶段提交？

索引

什么是索引？为什么要使用索引？

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

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

索引的优点和缺点是什么？

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

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

❌ 索引的缺点：

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

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

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

何时适用索引？何时不适用索引？

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

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

❌ 什么情况不适用索引？

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

索引如何分类？

索引可以从不同维度来分类：

• 数据结构
  • B+tree 索引
  • Hash 索引
  • Full-text 索引
• 物理存储
  • 聚簇索引（主键索引）
  • 二级索引（辅助索引）
• 字段特性
  • 主键索引（PRIMARY）
  • 唯一索引（UNIQUE）
  • 普通索引（INDEX）
  • 前缀索引
• 字段个数
  • 单列索引
  • 联合索引

索引有哪些常见数据结构？

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

Mysql 索引的常见数据结构：

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

为什么 InnoDB 索引采用 B+ 树？

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 索引的应用场景很苛刻，不适用于绝大多数场景。

聚簇索引和非聚簇索引有什么区别？

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

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

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

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

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

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

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

索引有哪些优化策略？

索引基本原则

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

覆盖索引

覆盖索引是指：索引上的信息足够满足查询请求，不需要回表查询数据。

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

最左匹配原则

这里的最左前缀，可以是联合索引的最左 N 个字段，也可以是字符串索引的最左 M 个字符。

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

应该将选择性高的列或基数大的列优先排在多列索引最前列。“索引的选择性”是指不重复的索引值和记录总数的比值，选择性越高，查询效率越高。但有时，也需要考虑 WHERE 子句中的排序、分组和范围条件等因素，这些因素也会对查询性能造成较大影响。

前缀索引

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

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

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

使用索引来排序

Mysql 有两种方式可以生成排序结果：通过排序操作；或者按索引顺序扫描。

索引最好既满足排序，又用于查找行。这样，就可以通过命中覆盖索引直接将结果查出来，也就不再需要排序了。

这样整个查询语句的执行流程就变成了：

1. 从索引 (city,name,age) 找到第一个满足 city=’杭州’条件的记录，取出其中的 city、name 和 age 这三个字段的值，作为结果集的一部分直接返回；
2. 从索引 (city,name,age) 取下一个记录，同样取出这三个字段的值，作为结果集的一部分直接返回；
3. 重复执行步骤 2，直到查到第 1000 条记录，或者是不满足 city=’杭州’条件时循环结束。

= 和 in 可以乱序

不需要考虑 =、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。

哪些情况下，索引会失效？

导致索引失效的情况有：

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

普通索引和唯一索引，应该怎么选择？

普通索引和唯一索引的查询性能相差微乎其微。

事务

什么是事务，什么是 ACID？

“事务”指的是满足 ACID 特性的一组操作。事务内的 SQL 语句，要么全执行成功，要么全执行失败。可以通过 Commit 提交一个事务，也可以使用 Rollback 进行回滚。

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

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

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

事务存在哪些并发一致性问题？

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Mysql 中的事务功能是在存储引擎层实现的，并非所有存储引擎都支持事务功能。InnoDB 是 Mysql 的首先事务存储引擎。

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

以 InnoDB 的事务实现来说明：

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

什么是 MVCC？

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

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

MVCC 的实现原理是什么？

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 实现了哪些隔离级别，如何实现的？

对于“读已提交”和“可重复读”隔离级别的事务来说，它们都是通过 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 支持了很多种锁来实现不同程度的隔离性，以保证数据的安全性。

独享锁和共享锁

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 的锁大致可以分为：

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

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

每个层级的锁数量是有限制的，因为锁会占用内存空间，锁空间的大小是有限的。当某个层级的锁数量超过了这个层级的阈值时，就会进行锁升级。锁升级就是用更大粒度的锁替代多个更小粒度的锁，比如 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。

优化

如何发现慢 SQL？

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

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

什么是执行计划？

如何分析执行计划？

如何优化 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 子句的顺序完全一致，并且所有列的排序方向都一样时，才能够使用索引来对结果做排序

UNION 优化

条件下推

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

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

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

哪种 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 才累加。
  • 也就是前面的第一条原则，server 层要什么字段，InnoDB 就返回什么字段。

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

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

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

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

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

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

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

如何优化查询计数？

可以使用 Redis 保存计数，但存在丢失更新一集数据不一致问题。

可以使用数据库其他表保存计数，但要用事务进行控制，增/删数据时，同步改变计数。

参考资料

• 《高性能 MySQL》
• MySQL 实战 45 讲
• 图解 MySQL 介绍

MongoDB 建模示例

关系型模型

嵌入式文档一对一关系模型

嵌入式文档一对一关系模型 - 嵌入式文档模式

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

// patron document
{
   _id: "joe",
   name: "Joe Bookreader"
}

// address document
{
   patron_id: "joe", // reference to patron document
   street: "123 Fake Street",
   city: "Faketon",
   state: "MA",
   zip: "12345"
}

合并为：

1
2
3
4
5
6
7
8
9
10

{
  "_id": "joe",
  "name": "Joe Bookreader",
  "address": {
    "street": "123 Fake Street",
    "city": "Faketon",
    "state": "MA",
    "zip": "12345"
  }
}

嵌入式文档一对一关系模型 - 子集模式

假设，有一个用于描述电影信息的 collection 定义：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

{
  "_id": 1,
  "title": "The Arrival of a Train",
  "year": 1896,
  "runtime": 1,
  "released": ISODate("01-25-1896"),
  "poster": "http://ia.media-imdb.com/images/M/MV5BMjEyNDk5MDYzOV5BMl5BanBnXkFtZTgwNjIxMTEwMzE@._V1_SX300.jpg",
  "plot": "A group of people are standing in a straight line along the platform of a railway station, waiting for a train, which is seen coming at some distance. When the train stops at the platform, ...",
  "fullplot": "A group of people are standing in a straight line along the platform of a railway station, waiting for a train, which is seen coming at some distance. When the train stops at the platform, the line dissolves. The doors of the railway-cars open, and people on the platform help passengers to get off.",
  "lastupdated": ISODate("2015-08-15T10:06:53"),
  "type": "movie",
  "directors": ["Auguste Lumière", "Louis Lumière"],
  "imdb": {
    "rating": 7.3,
    "votes": 5043,
    "id": 12
  },
  "countries": ["France"],
  "genres": ["Documentary", "Short"],
  "tomatoes": {
    "viewer": {
      "rating": 3.7,
      "numReviews": 59
    },
    "lastUpdated": ISODate("2020-01-09T00:02:53")
  }
}

在应用中，有的场景只需要显示电影的简单浏览信息，不需要显示类似 fullplot、poster 这样的详细信息。因为，我们可以考虑将原结构一份为二，并通过 id 字段关联起来。

用于展示摘要信息的 movie collection

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

// movie collection

{
  "_id": 1,
  "title": "The Arrival of a Train",
  "year": 1896,
  "runtime": 1,
  "released": ISODate("1896-01-25"),
  "type": "movie",
  "directors": ["Auguste Lumière", "Louis Lumière"],
  "countries": ["France"],
  "genres": ["Documentary", "Short"]
}

用于展示细节信息的 movie_details collection

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

// movie_details collection

{
  "_id": 156,
  "movie_id": 1, // reference to the movie collection
  "poster": "http://ia.media-imdb.com/images/M/MV5BMjEyNDk5MDYzOV5BMl5BanBnXkFtZTgwNjIxMTEwMzE@._V1_SX300.jpg",
  "plot": "A group of people are standing in a straight line along the platform of a railway station, waiting for a train, which is seen coming at some distance. When the train stops at the platform, ...",
  "fullplot": "A group of people are standing in a straight line along the platform of a railway station, waiting for a train, which is seen coming at some distance. When the train stops at the platform, the line dissolves. The doors of the railway-cars open, and people on the platform help passengers to get off.",
  "lastupdated": ISODate("2015-08-15T10:06:53"),
  "imdb": {
    "rating": 7.3,
    "votes": 5043,
    "id": 12
  },
  "tomatoes": {
    "viewer": {
      "rating": 3.7,
      "numReviews": 59
    },
    "lastUpdated": ISODate("2020-01-29T00:02:53")
  }
}

嵌入式文档一对多关系模型

嵌入式文档一对多关系模型 - 嵌入式文档模式

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

// patron document
{
   _id: "joe",
   name: "Joe Bookreader"
}

// address documents
{
   patron_id: "joe", // reference to patron document
   street: "123 Fake Street",
   city: "Faketon",
   state: "MA",
   zip: "12345"
}

{
   patron_id: "joe",
   street: "1 Some Other Street",
   city: "Boston",
   state: "MA",
   zip: "12345"
}

合并为：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

{
  "_id": "joe",
  "name": "Joe Bookreader",
  "addresses": [
    {
      "street": "123 Fake Street",
      "city": "Faketon",
      "state": "MA",
      "zip": "12345"
    },
    {
      "street": "1 Some Other Street",
      "city": "Boston",
      "state": "MA",
      "zip": "12345"
    }
  ]
}

嵌入式文档一对多关系模型 - 子集模式

考虑一个电商网站用于表示商品的 collection：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

{
  "_id": 1,
  "name": "Super Widget",
  "description": "This is the most useful item in your toolbox.",
  "price": { "value": NumberDecimal("119.99"), "currency": "USD" },
  "reviews": [
    {
      "review_id": 786,
      "review_author": "Kristina",
      "review_text": "This is indeed an amazing widget.",
      "published_date": ISODate("2019-02-18")
    },
    {
      "review_id": 785,
      "review_author": "Trina",
      "review_text": "Nice product. Slow shipping.",
      "published_date": ISODate("2019-02-17")
    },
    ...{
      "review_id": 1,
      "review_author": "Hans",
      "review_text": "Meh, it's okay.",
      "published_date": ISODate("2017-12-06")
    }
  ]
}

评论按时间倒序排列。 当用户访问产品页面时，应用程序将加载十条最近的评论。可以将集合分为两个集合，而不是与产品一起存储所有评论：

产品集合存储有关每个产品的信息，包括产品的十个最新评论：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

{
  "_id": 1,
  "name": "Super Widget",
  "description": "This is the most useful item in your toolbox.",
  "price": { "value": NumberDecimal("119.99"), "currency": "USD" },
  "reviews": [
    {
      "review_id": 786,
      "review_author": "Kristina",
      "review_text": "This is indeed an amazing widget.",
      "published_date": ISODate("2019-02-18")
    }
    ...
    {
      "review_id": 776,
      "review_author": "Pablo",
      "review_text": "Amazing!",
      "published_date": ISODate("2019-02-16")
    }
  ]
}

review collection 存储所有的评论

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

{
  "review_id": 786,
  "product_id": 1,
  "review_author": "Kristina",
  "review_text": "This is indeed an amazing widget.",
  "published_date": ISODate("2019-02-18")
}
{
  "review_id": 785,
  "product_id": 1,
  "review_author": "Trina",
  "review_text": "Nice product. Slow shipping.",
  "published_date": ISODate("2019-02-17")
}
...
{
  "review_id": 1,
  "product_id": 1,
  "review_author": "Hans",
  "review_text": "Meh, it's okay.",
  "published_date": ISODate("2017-12-06")
}

引用式文档一对多关系模型

考虑以下映射出版商和书籍关系的示例。

该示例说明了引用式文档的优点，以避免重复发布者信息。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

{
   title: "MongoDB: The Definitive Guide",
   author: [ "Kristina Chodorow", "Mike Dirolf" ],
   published_date: ISODate("2010-09-24"),
   pages: 216,
   language: "English",
   publisher: {
              name: "O'Reilly Media",
              founded: 1980,
              location: "CA"
            }
}

{
   title: "50 Tips and Tricks for MongoDB Developer",
   author: "Kristina Chodorow",
   published_date: ISODate("2011-05-06"),
   pages: 68,
   language: "English",
   publisher: {
              name: "O'Reilly Media",
              founded: 1980,
              location: "CA"
            }
}

为避免重复出版商数据，可以使用引用型文档，并将出版商信息与书本分开保存。 使用引用时，关系的增长决定了将引用存储在何处。 如果每个出版商的图书数量很少且增长有限，则有时将图书参考存储在出版商文档中可能会很有用。 否则，如果每个发布者的书籍数量不受限制，则此数据模型将导致可变的，不断增长的数组，如以下示例所示：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

{
   name: "O'Reilly Media",
   founded: 1980,
   location: "CA",
   books: [123456789, 234567890, ...]
}

{
    _id: 123456789,
    title: "MongoDB: The Definitive Guide",
    author: [ "Kristina Chodorow", "Mike Dirolf" ],
    published_date: ISODate("2010-09-24"),
    pages: 216,
    language: "English"
}

{
   _id: 234567890,
   title: "50 Tips and Tricks for MongoDB Developer",
   author: "Kristina Chodorow",
   published_date: ISODate("2011-05-06"),
   pages: 68,
   language: "English"
}

为了避免可变的，增长的数组，请将发行者参考存储在书籍文档中：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

{
   _id: "oreilly",
   name: "O'Reilly Media",
   founded: 1980,
   location: "CA"
}

{
   _id: 123456789,
   title: "MongoDB: The Definitive Guide",
   author: [ "Kristina Chodorow", "Mike Dirolf" ],
   published_date: ISODate("2010-09-24"),
   pages: 216,
   language: "English",
   publisher_id: "oreilly"
}

{
   _id: 234567890,
   title: "50 Tips and Tricks for MongoDB Developer",
   author: "Kristina Chodorow",
   published_date: ISODate("2011-05-06"),
   pages: 68,
   language: "English",
   publisher_id: "oreilly"
}

树形结构模型

具有父节点的树形结构模型

上图结构可以用父引用来表示：

1
2
3
4
5
6
7
8

db.categories.insertMany([
  { "_id": "MongoDB", "parent": "Databases" },
  { "_id": "dbm", "parent": "Databases" },
  { "_id": "Databases", "parent": "Programming" },
  { "_id": "Languages", "parent": "Programming" },
  { "_id": "Programming", "parent": "Books" },
  { "_id": "Books", "parent": null }
])

• 检索节点的父节点：

  1	db.categories.findOne( { _id: "MongoDB" } ).parent

• 可以在父字段上创建索引以启用父节点的快速搜索：

  1	db.categories.createIndex( { parent: 1 } )

• 可以通过父字段查询找到其直接子节点：

  1	db.categories.find( { parent: "Databases" } )

• 检索子树，可以参考： $graphLookup.

具有子节点的树形结构模型

1
2
3
4
5
6
7
8

db.categories.insertMany([
  { "_id": "MongoDB", "children": [] },
  { "_id": "dbm", "children": [] },
  { "_id": "Databases", "children": ["MongoDB", "dbm"] },
  { "_id": "Languages", "children": [] },
  { "_id": "Programming", "children": ["Databases", "Languages"] },
  { "_id": "Books", "children": ["Programming"] }
])

• 检索节点的 children：

  1	db.categories.findOne( { _id: "Databases" } ).children

• 可以在 children 字段上创建索引以启用子节点的快速搜索：

  1	db.categories.createIndex( { children: 1 } )

• 可以在 children 字段中查询节点，以找到其父节点及其兄弟节点：

  1	db.categories.find( { children: "MongoDB" } )

具有祖先的树形结构模型

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

db.categories.insertMany([
  {
    "_id": "MongoDB",
    "ancestors": ["Books", "Programming", "Databases"],
    "parent": "Databases"
  },
  {
    "_id": "dbm",
    "ancestors": ["Books", "Programming", "Databases"],
    "parent": "Databases"
  },
  {
    "_id": "Databases",
    "ancestors": ["Books", "Programming"],
    "parent": "Programming"
  },
  {
    "_id": "Languages",
    "ancestors": ["Books", "Programming"],
    "parent": "Programming"
  },
  { "_id": "Programming", "ancestors": ["Books"], "parent": "Books" },
  { "_id": "Books", "ancestors": [], "parent": null }
])