网站正在建设 mp4,建设com网站,上海传媒公司艺人,网站建设公司止一se0在数据库的查询中#xff0c;是一定会遇到表关联查询的。当两张大表关联时#xff0c;时常会遇到性能和资源问题。这篇文章就是用一个例子来分享MongoDB带条件的关联查询发挥的作用。
假设工作环境中有两张MongoDB集合#xff1a;SC_DATA#xff08;学生基本信息集合…在数据库的查询中是一定会遇到表关联查询的。当两张大表关联时时常会遇到性能和资源问题。这篇文章就是用一个例子来分享MongoDB带条件的关联查询发挥的作用。
假设工作环境中有两张MongoDB集合SC_DATA学生基本信息集合、DICT_DATA值域字典集合集合结构如下
SC_DATAuniqueid学生唯一号sfzid 学生身份证xsxm学生姓名mz民族xb性别
DICT_DATAclss字典类别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工具中可以更清晰的看到数据量变化。此时因为在输出关联数据前先进行了过滤。这种写法可以消耗更少的数据库及系统资源但在索引使用上和正常关联略有区别需要注意。
