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
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

避免使用不使用索引的查询符：$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.