MongoDB-aggregate流式计算:带条件的关联查询使用案例分析

时间:2024-10-04 22:40:54

在数据库的查询中,是一定会遇到表关联查询的。当两张大表关联时,时常会遇到性能和资源问题。这篇文章就是用一个例子来分享MongoDB带条件的关联查询发挥的作用。

假设工作环境中有两张MongoDB集合:SC_DATA(学生基本信息集合)、DICT_DATA(值域字典集合),集合结构如下:

SC_DATA
uniqueid 学生唯一号
sfzid         学生身份证
xsxm 学生姓名
mz 民族
xb 性别
DICT_DATA
clss 字典类别
value         字典值域
map 字典值域映射值
version 字典版本

 现在分别给这两张表插入一些测试数据,给SC_DATA插入10条数据,给DICT_DATA插入6条数据

db.SC_DATA.insertMany([
   { "uniqueid" : "10001", "sfzid" : "3715xxxx0813", "xsxm" :"张一","mz":"1","xb":"1" },
   { "uniqueid" : "10002", "sfzid" : "3715xxxx0814", "xsxm" :"张二","mz":"1","xb":"1" },
   { "uniqueid" : "10003", "sfzid" : "3715xxxx0815", "xsxm" :"张三","mz":"1","xb":"1" },
   { "uniqueid" : "10004", "sfzid" : "3715xxxx0816", "xsxm" :"张四","mz":"1","xb":"b" },
   { "uniqueid" : "10005", "sfzid" : "3715xxxx0817", "xsxm" :"张五","mz":"a","xb":"1" },
   { "uniqueid" : "10006", "sfzid" : "3715xxxx0819", "xsxm" :"张六","mz":"1","xb":"b" },
   { "uniqueid" : "10007", "sfzid" : "3715xxxx0823", "xsxm" :"张七","mz":"1","xb":"1" },
   { "uniqueid" : "10008", "sfzid" : "3715xxxx0833", "xsxm" :"张八","mz":"1","xb":"1" },
   { "uniqueid" : "10009", "sfzid" : "3715xxxx0843", "xsxm" :"张九","mz":"1","xb":"1" },
   { "uniqueid" : "100010", "sfzid" : "3715xxxx0853", "xsxm" :"张十","mz":"1","xb":"1" },
])
db.DICT_DATA.insertMany([
   { "clss" : "民族", "value" : "汉族", "map" :"1","version":"v1.0"},
   { "clss" : "民族", "value" : "壮族", "map" :"2","version":"v1.0"},
   { "clss" : "民族", "value" : "满族", "map" :"3","version":"v1.0"},
   { "clss" : "民族", "value" : "回族", "map" :"4","version":"v1.0"},
   { "clss" : "性别", "value" : "男",   "map" :"1","version":"v1.0"},
   { "clss" : "性别", "value" : "女",   "map" :"2","version":"v1.0"}

])

此时,有个需求是 “统计出SC_DATA集合中民族、性别字段在字典值域内的数据”!

         一般呢,思路是利用两集合关联,过滤出能关联上的数据。MongoDB的$lookup操作符类似于关系数据库的左连接,根据当前实际情况,用大表(SC_DATA.mz、SC_DATA.xb)左连接小表(DICT_DATA.map),能关联上的数据就是SC_DATA集合中民族、性别字段在字典值域内的数据!

        一般呢,就直接用了$lookup进行关联了,但是,观察下DICT_DATA字典数据,承担关联任务的字段——map,有多个相同值,必须加上clss条件过滤才能得出准确数据,代码如下。

db.SC_DATA.aggregate([
  {
    $lookup: {
      from: "DICT_DATA",
      localField: "mz",
      foreignField: "map",
      as: "DICT_DATA"
    }
  },
  {
    $unwind: {
      path: "$DICT_DATA",
      preserveNullAndEmptyArrays: true
    }
  },
  {
    $match: {
    "DICT_DATA.clss": "民族"
    }
  },
  {
    $group: {
      _id: null,
      count: {
        $sum: 1
      }
    }
  }
  ])

        但是,诸位请看,上面的代码是先关联,再过滤。通过compass工具分阶段查看,可以更清晰的看到关联后,因为DICT_DATA.map存在重复值,所以如果SC_DATA能和DICT_DATA关联上的话,数据会翻倍。

        对于我们上面的测试数据,SC_DATA有10条测试数据,和DICT_DATA关联后数据量是19条,过滤clss后是9条。大家可能觉得这种还好,但是如果SC_DATA有上千万条数据,DICT_DATA的数据更多,重复值更多,这样关联出来的数据是非常惊人的,效率也会变得奇慢无比,甚至会造成数据库卡死。

        如果能够在关联出结果前,就进行过滤,就会让更少量的数据进入到下一个MongoDB聚合管道,就会消耗更少量的资源。

这里也就引出了这篇文章的主角:带条件的$lookup,语法格式如下:

{
   $lookup:
      {
         from: <joined collection>,
         let: { <var_1>: <expression>, …, <var_n>: <expression> },
         pipeline: [ <pipeline to run on joined collection> ],
         as: <output array field>
      }
}

参数说明如下:

参数

说明

from

指定待执行连接操作的集合,是当前集合【可以看下面的例子理解】

let

指定各个管道阶段使用的变量,这里的变量可以放到pipeline中使用;

这里指定的都是自身当前集合中的字段变量;

这里指定变量的时候以 col_name:$col_name的形式,在pipeline中使用的时候以 $$col_name形式 使用;

pipeline

1、pipeline中,可以使用let中指定的变量,也可以使用当前集合中的字段;

2、pipeline中,$match阶段需要使用$expr操作符来访问变量,$expr允许在$match中使用聚合表达式;

3、pipeline中,放置在$expr上的$eq、$lt、$lte、$gt、$gte比较操作符,可以使用$lookup阶段引用的 from集合上的索引;

3.1、使用索引的限制一:不使用多键索引;

3.2、使用索引的限制二:当操作的数量比较大,或者操作数据类型没有定义时,不使用索引;

3.3、使用索引的限制三:索引只能用于字段和常量之间的比较,变量和变量之间的比较不能使用索引;

4、pipeline中,非$match阶段,不需要使用$expr操作符来访问变量

as

指定要添加到已连接文档的新数量字段的名称。新的大量字段包含来自加入的收集的匹配文档。如果指定的名称已存在于所连接的文档中,则现有字段将被覆盖。

        针对  “统计出SC_DATA集合中民族、性别字段在字典值域内的数据”!这个需求,我们就可以将其写为如下代码!

db.SC_DATA.aggregate([
  {
    $lookup: {
      from: "DICT_DATA",
      let: {
        mz: "$mz"
      },
      pipeline: [
        {
          $match: {
            $expr: {
              $and: [
                {
                  $eq: ["$map", "$$mz"]
                },
                {
                  $eq: ["$clss", "民族"]
                }
              ]
            }
          }
        }
      ],
      as: "DICT_DATA"
    }
  },
  {
    $unwind: {
      path: "$DICT_DATA",
      preserveNullAndEmptyArrays: true
    }
  },
  {
    $match: {
      "DICT_DATA.map": {
        $ne: null
      }
    }
  },
  {
    $group: {
      _id: null,
      count: {
        $sum: 1
      }
    }
  }
  ])

        从compass工具中,可以更清晰的看到数据量变化。此时,因为在输出关联数据前,先进行了过滤。这种写法可以消耗更少的数据库及系统资源,但在索引使用上和正常关联略有区别需要注意。