mongodb-4.4.10版本SQL查询进阶，mongodb与mysql的select SQL大比拼

基础的mongodb增删改查入门，请查看我的另一篇博文《mongodb-4.4.10版本与MySQL的SQL语法对比，以及mongodb增删改查入门demo》。

mongo的索引管理，请查看《mongodb-4.4.10版本的索引管理：创建、删除》

这篇文章给大家讲讲mongo的高阶查询。

查询选择器：

运算符类型 运算符 描述
范围 $eq 等于
$lt 小于
$gt 大于
$lte 小于等于
$gte 大于等于
$in 判断元素是否在指定的集合范围里
$all 判断数组中是否包含某几个元素，无关顺序
$min 判断元素是否不在指定的集合范围里
布尔运算 $ne 不等于，不匹配参数条件
$not 不匹配结果
$or 有一个条件成立则匹配
$nor 所有条件都不匹配
$and 所有条件都必须匹配
$exists 判断元素是否存在
其他 . 子文档匹配
$regex 正则表达式匹配

 

./mongo localhost:27017         连接到mongodb指令

假设我的mongo里有个lison数据库，有个users集合，集合里有这样一些BSON结构的数据：

{
    "id": 1,
    "username": "ZhangSan",
    "money":150.88,
    "age":20,
    "birthday": "2011-11-26",
    "createTime":"2020-05-15 10:00:00",
    "updateTime":"2011-11-26 12:26:21",
    "address": {
         "code":"411000"
        "adress_detail":"长沙"
    },
    "favorites": {
        "movies":["蜘蛛侠","钢铁侠","蝙蝠侠"],
        "cites":["北京","杭州","上海"]
    },
    "comments":[
      {"author":"lisi","content":"第1楼评论内容","commentTime":ISODate("2017-06-06T10:15:22.00Z")},
      {"author":"wangwu","content":"第2楼评论内容","commentTime":ISODate("2017-06-07T12:16:11.00Z")},
      {"author":"liuliu","content":"第3楼评论内容","commentTime":ISODate("2017-06-08T13:17:20.00Z")},
    ]
}

例如and, or, like, in 这样的查询选择符，请查看我的另一篇博文《mongodb-4.4.10版本查询选择器列表》

我们照例拿mysql和mongo的SQL做对比：

  Mongo  MySQL
select cl1,cl2 db.users.find({"username":"xxx"},{"username":1,"age":1}).pretty()
这里的1就表示仅查询出该字段，这些字段叫投影字段
db.users.find({"username":"Chen"},{"username":1}).pretty()
db.users.find({},{"username":1}).pretty()		 select username, age from uses where username = 'xxx'
select username from uses where username = 'Chen'
select username from uses
select cl1,cl2, _id db.users.find({"username":"xxx"},{"username":0}).pretty()
查询字段里不要username，这些字段叫投影字段，其他字段都要。
0表示排除字段，非0表示字段选择并排除其他字段，所有字段必须设置同样的值，但是_id主键可以例外。
db.users.find({},{"username":0}).pretty()

db.users.find({"username":"xxx"},{"username":1,"age":0}).pretty() 
如果这么写会报错，因为投影字段要么全设置为1，要么全设置为0。
但是有个例外：_id 主键字段可以很特殊的不要：
db.users.find({"username":"Chen"},{"username":1,"_id":0}).pretty()
 select age,money,createTime from uses where username = 'xxx'
查询字段里不要username，其他字段都要
select age,money,createTime from uses
count db.users.find({}).count() select count(*) from users
where db.users.find({"username":"xxx"}) select * from users where username = 'xxx'
and db.users.find({   “$and”:     [{"username":"xxx"},{"age":18}]     }).pretty() select * from users where username = 'xxx' and age = 18
or db.users.find({   “$or”:     [{"username":"xxx"},{"age":18}]     }).pretty() select * from users where username = 'xxx' or age = 18
in db.users.find({"username":      {"$in":     ["x1","x2","x3"]     }          }).pretty() select * from users where username = 'x1' or username = 'x2' or username = 'x3'
exists db.users.find({"money":  {"$exists":true}  }).pretty()
如果这样写，表示我要查出集合里有money字段的记录，如果是false，那么就查出没有money字段的集合记录。

select * from users where user_id exists (select user_id from orders)
mysql里的exists表示是不是有相应的值的存在记录，而mongo里的exists表示有没有该字段。
< 小于 db.users.find({"age":  {"$lt":20}  }).pretty();
db.users.find({"age":     {"$not": {"$gte":20}}      }).pretty();   -- 在这里对大于20的再取反也是一样的效果

db.users.find({   “$or”:     [    {"age": {"$lt":20} } ,{"age":  {"$exists":true}}  ]     }).pretty()  
这里表示age小于20或者age字段存在的记录		 select * from users where age < 20;

select * from users where age < 20 or age exists ( select age from mylog );
order by db.users.find().sort({"username": 1 }).pretty()          1：升序
db.users.find().sort({"username": -1 }).pretty()       -1：降序		 select * from users order by username asc
select * from users order by username desc
sort
limit
skip		 db.users.find().sort({"username":1}).limit(2).skip(4)
limit表示取多少条数据，skip表示跳过多少条数据，尽量避免使用skip跳过大量数据，会影响性能。
sort里的1: 升序(asc),  -1: 降序(desc)		 select * from users order by username asc limit 4,2 
distinct db.users.distinct("username").pretty(); select distinct username from users
子文档查询 db.users.find({"favorites.movies":"蜘蛛侠"})     注意这里的favorites.movies必须要有双引号
查询数组中包含"蜘蛛侠"		 select * from users wehre favorites.movies like '%蜘蛛侠%'
子文档查询
“全是”		 db.users.find({"favorites.movies":{"$all":[ "雷神", "战狼" ]}},{"favorites.movies":1})
查询数组包含["雷神", "战狼" ]的文档，跟顺序无关，跟数量有关。
也就是雷神1和战狼谁前谁后顺序无关，只要都包含就行，且无其他值。		  
子文档查询
"in"		 db.users.find({"favorites.movies":{"$in":[ "雷神", "战狼" ]}},{"favorites.movies":1})
查询数组包含["雷神", "战狼" ]中任意一个的文档，跟顺序无关，跟数量无关		  
子文档查询
“===”		 db.users.find({"favorites.movies":[ "杀破狼", "战狼", "雷神" ]}).pretty(); 
"==="。查询数组等于[ "杀破狼", "战狼", "雷神" ]的文档，严格按照数量、顺序；
也就是数组里的值顺序必须是：杀破狼、战狼、雷神，这叫按照顺序，且不能有其他值，这叫按照数量		  
子文档查询
数组中的第一个值		 db.users.find({"favorites.movies.0": "杀破狼" ]}).pretty();
查找数组中第一个值为杀破狼的数据		  
子文档查询
数组中取值		 db.users.find({},{"favorites.movies":{"$slice":[0,3]},"favorites":1})
$slice可以从数组中取出从0条到第3条的数据；
[3,3] 就是从第3条开始，获取出3条
[10,3] 就是从第10条开始，获取出3条		  
  db.users.find({"comment":    
{"author":"lisi","content":"第1楼评论内容","commentTime":ISODate("2017-06-06T10:15:22.00Z")}
    })            每一个值都要一样，精确查找		  
  db.users.find({"comment.author":    "lisi"   })        查找lisi发表的所有评论  
  db.users.find({"comment.author":    {"$in":  ["lisi","wangwu"] }    })     查找lisi或者wangwu评论过的  
  db.users.find({"comment.author":    {"$all":  ["lisi","wangwu"] }    })    查找lisi和wangwu都评论过的  
  db.users.find({"comment":    {"$elemMatch": 
             {"author":"lisi", "content":{"$regex":".*不良评论.*"}}
}    })           查找lisi发表的，并且内容包含"不良评论"的，这里的.*就是mysql里的%		  
group $group符的存在可以支持mongo其他聚合操作，比如: avg, min, max  
group

db.getCollection('users').aggregate(
    [   
        {   $project : { day : {$substr: ["$issue_time", 0, 10] }}},        
        {   $group   : { _id : "$day",  number : { $sum : 1 }}},
        {   $sort    : { _id : 1 }}        
    ]
}

// 结果是：

2020-10-01  1018

2020-10-02  324

根据日期group的例子：

select SUBSTRING(issue_time, 1, 10) as day, sum(1) from users group by SUBSTRING(issue_time, 1, 10);

// 结果是：

2020-10-01  1018

2020-10-02  324

---- substring函数的例子

SELECT SUBSTRING('2020-01-02T00:00:00',1,10) AS col1 from dual;  // 结果是 2020-01-02
group

db.getCollection('users').aggregate(
    [
        {"$match":{ "created_time" : { "$lt" : ISODate("2015-01-10T23:59:59.000Z")} }},
        { $project : {day:{ $substr:[{ $dateToString: { format: "%Y-%m-%d%H:%M:%S:%L%z", date: "$createdAt", timezone: "Asia/Shanghai" } },0,10]}}},
        { $group   : { _id : "$day",  number : { $sum : 1 }}},
        { $sort    : { _id : 1 }}
    ]
);

select substring(create_time, 0, 10) as day, sum(1)

from users where created_time < '2015-01-10' group by substring(create_time, 0, 10)

order by day desc

max

min

avg

 db.users.aggregate({ "$group":
       { "_id":"$_id", "max_age":{ "$min":"$age"}}  
})       
注意这里的$_id取的是_id主键的值

db.users.aggregate({ "$group":
       { "_id":"abc", "max_age":{ "$min":"$age"}}  
})  
注意这里的abc是给一个固定的值abc，并不是从_id字段里取值

上面举例max的例子，min；avg用法类同		 select _id, max(age) from users group by _id

select 'abc', max(age) from users
【应用例子】

查询2015年4月3号之前，每个用户每个月消费的总金额，并按用户名进行排序		 db.orders.aggregate([
    {"$match":{ "orderTime" : 
         { "$lt" : new Date("2015-04-03T16:00:00.000Z")} 
     }},
    {"$group":{
           "_id":{
                 "user_id":"$use_id",
                 "month": {"$month":"$orderTime"}
            },
            "total":{"$sum":"$price"}
    }},
    {"$sort":{"_id":1}}
])		 select orderMonth, useCode, sum(price)
from orders
where orderTime < '2015-04-03 16:00:00'
group by user_id, month(orderTime) orderMonth
sort by _id desc
$unwind 
打散数据		 比如这样的数据：
{
  “username”:"lisi",
  "age":20,
  "favorites": {"movies":["蜘蛛侠","钢铁侠"]}
}
上面这一行数据，使用$unwind将favorites.movies字段打散数据后就变成了2行数据：
db.users.aggregate([{"$match":{"username":"cang" }},{ "$unwind":"$favorites.movies" }]).pretty()
两行数据：
{“username”:"lisi","age":20,"favorites": {"movies":"蜘蛛侠"}}
{“username”:"lisi","age":20,"favorites": {"movies":"钢铁侠"}}

数据打散后可以进行group by和sort的例子：
db.orders.aggregate([
    {"$match":{ 
        "orderTime" : {"$lt" : new Date("2015-04-03T16:00:00.000Z")}
    }}, 
    {"$unwind":"$Auditors"},
    {"$group":{
        "_id":{"Auditors":"$Auditors"},
        "total":{"$sum":"$price"}
    }},
    {"$sort":{"_id":1}}
])		  
打散数据，例子2 db.users.aggregate([
    {"$match":{"username":"lison"}},
    {"$unwind":"$comments"},
    {"$sort":{"comments.commentTime":1}},
    {"$project":{"comments":1}},         // 仅select这个字段，其他字段不要
    {"$skip":0},
    {"$limit":3}
])		 左边这个mongodb的sql相当于（不全是）这个mysql的例子:
select comments from users 
where username = 'lison'
group by comments
order by comments.commentTime asc
limit 0,3
     
真实case 1
db.orders.aggregate(
{  "$project": {"userid": "$userid"} },
{ "$group": {
       "_id": {"userid": "$userid"},
       "total": { $sum : 1}
      }
},
{ "$sort": {"_id.userid": -1}},
{"$limit": 1}
);


 select userid, count(*) from orders group by userid order by userid desc limit 1
真实case 2

db.users.aggregate({ "$group":

        { "_id":"abc", "min_create_time":{ "$min":"$create_time"}}

});

 select min(create_time) as min_reate_time from users 
真实case 3

db.users.aggregate({ "$group":

        { "_id":"abc", "max_create_time":{ "$max":"$create_time"}}

})

 select max(create_time) as max_create_time from users
真实case 4

推荐使用ISODate这种日期封装函数格式：

写法1：db.users.find(
    {"$and" : [
            {"created_time" :

                 {"$gte": ISODate("2019-12-30T00:00:00.642+08:00")}} ,
            {"createdAt" :

                 {"$lte" : ISODate("2020-01-02T00:00:00.642+08:00")}}
        ]}
    ).count();

写法2：db.users.find(
    {"$and" : [
            {"create_time" : {"$gte": "2020-01-01 00:00:00"}} ,
            {"create_time" : {"$lte" : "2020-01-02 00:00:00"}}
        ]}
    );

select count(*) from user where create_time >= '2020-01-01 00:00:00' and create_time < '2020-01-02 00:00:00';

select * from user where create_time >= '2020-01-01 00:00:00' and create_time < '2020-01-02 00:00:00';

查询日期时间区间
真实case 5

db.users.deleteMany(
    {"$and" : [
            {"create_time" : {"$gte": "2020-01-01 00:00:00"}} ,
            {"create_time" : {"$lte" : "2020-01-02 00:00:00"}}
        ]}
    );

 delete from user where create_time >= '2020-01-01 00:00:00' and create_time < '2020-01-02 00:00:00';

删除日期时间区间
真实case 6

db.users.find({"_id": ObjectId("100")});

select * from users where id = '100'

主键查询
真实case 7

db.users.find({"create_time" : {"$lte" : ISODate("2020-01-02T00:00:00.642+08:00")}});

select * from users where create_time <= '2020-01-02 00:00:00'
真实case 8

db.getCollection('users').aggregate(
    [
        {"$match":{ "create_time" : { "$lt" : ISODate("2015-01-10T23:59:59.000Z")} }},
        { $project : {day:{ $substr:[{ $dateToString: { format: "%Y-%m-%d%H:%M:%S:%L%z", date: "$create_time", timezone: "Asia/Shanghai" } },0,10]}}},
        { $group   : { _id : "$day",  number : { $sum : 1 }}},
        { $sort    : { _id : 1 }}
    ]
);

查询users表create_time小于2015年1月10日(UTC时间)，并且按照年月日group by create_time(UTC+8), 统计每个group日期的总数 

 

避免使用不使用索引的查询符：$ne, $nin, $where等

【开启慢查询记录】

开启内置查询分析器，类似于开启mysql的explain： db.setProfilingLevel(n,{m})      n的取值可选：0,1,2。  例子：db.setProfilingLevel(1,2000)   表示开启慢速操作记录，记录下超过2000ms的慢速操作
       0  是默认值，表示不记录任何慢查询
       1 表示记录慢速操作，如果置为1，m必须赋值，单位为ms，用于定义慢速查询时间的阈值。
       2 表示记录所有的读写操作

【慢查询记录集合】

慢速查询结果会保存到一个特殊的盖子集合system.profile里，这个集合分配了128kb的空间，要确保监控分析数据不会消耗太多的系统性资源，盖子集合维护了自然的插入顺序，可以使用$natural操作符进行排序，
如：db.system.profile.find().sort({"$natural":-1}).limit(5)      按照时间顺序倒序取出最新5条数据

盖子集合的大小或者数量是固定的，且不能做update和delete，容量满了之后，按照时间顺序，新文档会覆盖旧文档。

【慢查询分析explain】

db.users.find({“age”:20}).explain('executionStats')      我要查询age = 20的数据，并且查看这个查询的执行计划，explain的入参有3个可选值：

queryPlanner   默认值。表示仅仅展示执行计划信息
executionStats    表示展示执行计划信息同时展示被选中的执行计划的执行情况信息
allPlansExecution     表示展示执行计划信息，并展示被选中的执行计划的执行情况信息，还展示备选的执行计划的执行情况信息

【explain结果解析】

queryPlanner（执行计划描述）

  winningPlan（被选中的执行计划）

   stage（可选项:COLLSCAN 没有走索引；IXSCAN使用了索引）    关于索引，查看另一篇博文《mongodb-4.4.10版本的索引管理：创建、删除》

   rejectedPlans(候选的执行计划)

executionStats(执行情况描述)

  nReturned （返回的文档个数）

        executionTimeMillis（执行时间ms）

        totalKeysExamined （查询了多少数据）

        totalDocsExamined （匹配了多少文档）

        inputStage : {
                 indexName     目前用到了哪个索引
        }

优化目标 Tips:

1.根据需求建立索引
2.每个查询都要使用索引以提高查询效率, winningPlan. stage 必须为IXSCAN ；
3.追求totalDocsExamined = nReturned

 

如果出现了慢查询，我们就需要使用mongo的索引来优化它，关于mongo的索引管理，请查看《mongodb-4.4.10版本的索引管理：创建、删除》

end.

posted on 2021-10-30 10:02  梦幻朵颜  阅读(214)  评论(0)  编辑  收藏  举报

Powered by: 博客园 Copyright © 2024 梦幻朵颜
Powered by .NET 8.0 on Kubernetes