mongodb模糊查询性能和es的对比 mongodb模糊查询语句

转载

lazihuman 2024-07-31 16:07:08

文章标签 mongodb模糊查询性能和es的对比数据库 c# json 5e 文章分类 MongoDB 数据库

本文是一篇关于数据数字的帖子

近最有一监控业务，由于数据采集到非数字内容，致导监控图表法无常正表现，所以要找出这部分数据，停止删除，然后开辟员从头源改正插入数据库的数据，不再发生非数字内容。

上面举一个例子：

立建测试数据：

for(i=1;i<=100;i++){db.test.insert({id:i,content:"test content",name:"wang"+i});} 
  
 
  

     > db.test.find() 
  
 
  

    { "_id" : ObjectId("504ece595e8dc12ed97482b2"), "id" : 1, "content" : "test content", "name" : "wang1" } 
  
 
  

    { "_id" : ObjectId("504ece595e8dc12ed97482b3"), "id" : 2, "content" : "test content", "name" : "wang2" } 
  
 
  

    { "_id" : ObjectId("504ece595e8dc12ed97482b4"), "id" : 3, "content" : "test content", "name" : "wang3" } 
  
 
  

    { "_id" : ObjectId("504ece595e8dc12ed97482b5"), "id" : 4, "content" : "test content", "name" : "wang4" } 
  
 
  

    { "_id" : ObjectId("504ece595e8dc12ed97482b6"), "id" : 5, "content" : "test content", "name" : "wang5" } 
  
 
  

    { "_id" : ObjectId("504ece595e8dc12ed97482b7"), "id" : 6, "content" : "test content", "name" : "wang6" } 
  
 
  

    { "_id" : ObjectId("504ece595e8dc12ed97482b8"), "id" : 7, "content" : "test content", "name" : "wang7" } 
  
 
  

    { "_id" : ObjectId("504ece595e8dc12ed97482b9"), "id" : 8, "content" : "test content", "name" : "wang8" } 
  
 
  

    { "_id" : ObjectId("504ece595e8dc12ed97482ba"), "id" : 9, "content" : "test content", "name" : "wang9" } 
  
 
  
 
   
 
  

    for(i=1;i<=101;i++){db.test.insert({id:i,content:666888,name:"joe"+i});} 
  
 
  

     > db.test.find({content:666888}) 
  
 
  

    { "_id" : ObjectId("504ececc5e8dc12ed9748316"), "id" : 1, "content" : 666888, "name" : "joe1" } 
  
 
  

    { "_id" : ObjectId("504ececc5e8dc12ed9748317"), "id" : 2, "content" : 666888, "name" : "joe2" } 
  
 
  

    { "_id" : ObjectId("504ececc5e8dc12ed9748318"), "id" : 3, "content" : 666888, "name" : "joe3" } 
  
 
  

    { "_id" : ObjectId("504ececc5e8dc12ed9748319"), "id" : 4, "content" : 666888, "name" : "joe4" } 
  
 
  

    { "_id" : ObjectId("504ececc5e8dc12ed974831a"), "id" : 5, "content" : 666888, "name" : "joe5" } 
  
 
  

    { "_id" : ObjectId("504ececc5e8dc12ed974831b"), "id" : 6, "content" : 666888, "name" : "joe6" } 
  
 
  

    { "_id" : ObjectId("504ececc5e8dc12ed974831c"), "id" : 7, "content" : 666888, "name" : "joe7" } 
  
 
  

    { "_id" : ObjectId("504ececc5e8dc12ed974831d"), "id" : 8, "content" : 666888, "name" : "joe8" } 
  
 
  

    { "_id" : ObjectId("504ececc5e8dc12ed974831e"), "id" : 9, "content" : 666888, "name" : "joe9" }

通过模糊询查，找查关相数据：

> db.test.find({name:/joe/})    ---询查name字段含有joe的数据 等同于db.test.find({name:{$regex:'joe'}}) 
  
 
  

    { "_id" : ObjectId("504ececc5e8dc12ed9748316"), "id" : 1, "content" : 666888, "name" : "joe1" } 
  
 
  

    { "_id" : ObjectId("504ececc5e8dc12ed9748317"), "id" : 2, "content" : 666888, "name" : "joe2" } 
  
 
  

    { "_id" : ObjectId("504ececc5e8dc12ed9748318"), "id" : 3, "content" : 666888, "name" : "joe3" } 
  
 
  

    { "_id" : ObjectId("504ececc5e8dc12ed9748319"), "id" : 4, "content" : 666888, "name" : "joe4" } 
  
 
  

    { "_id" : ObjectId("504ececc5e8dc12ed974831a"), "id" : 5, "content" : 666888, "name" : "joe5" } 
  
 
  

    { "_id" : ObjectId("504ececc5e8dc12ed974831b"), "id" : 6, "content" : 666888, "name" : "joe6" } 
  
 
  

    { "_id" : ObjectId("504ececc5e8dc12ed974831c"), "id" : 7, "content" : 666888, "name" : "joe7" } 
  
 
  

    { "_id" : ObjectId("504ececc5e8dc12ed974831d"), "id" : 8, "content" : 666888, "name" : "joe8" } 
  
 
  

    { "_id" : ObjectId("504ececc5e8dc12ed974831e"), "id" : 9, "content" : 666888, "name" : "joe9" }

db.test.find({name:/joe/i})   加了i，那么就不会区分大小写，都市表现，等同于db.test.find({name:{$regex:'joe',$options:'i'}}) 
  
 
  

     db.test.find({name:/^joe/i})   ^配匹以joe扫尾的，而且不区分大小写

$type用使：

> db.test.find({content:{$type:2}}) --表现content是string的数据 
  
 
  

    { "_id" : ObjectId("504ece595e8dc12ed97482b2"), "id" : 1, "content" : "test content", "name" : "wang1" } 
  
 
  

    { "_id" : ObjectId("504ece595e8dc12ed97482b3"), "id" : 2, "content" : "test content", "name" : "wang2" } 
  
 
  

    { "_id" : ObjectId("504ece595e8dc12ed97482b4"), "id" : 3, "content" : "test content", "name" : "wang3" } 
  
 
  

    { "_id" : ObjectId("504ece595e8dc12ed97482b5"), "id" : 4, "content" : "test content", "name" : "wang4" } 
  
 
  

    { "_id" : ObjectId("504ece595e8dc12ed97482b6"), "id" : 5, "content" : "test content", "name" : "wang5" } 
  
 
  

    { "_id" : ObjectId("504ece595e8dc12ed97482b7"), "id" : 6, "content" : "test content", "name" : "wang6" } 
  
 
  

    { "_id" : ObjectId("504ece595e8dc12ed97482b8"), "id" : 7, "content" : "test content", "name" : "wang7" } 
  
 
  

    { "_id" : ObjectId("504ece595e8dc12ed97482b9"), "id" : 8, "content" : "test content", "name" : "wang8" } 
  
 
  

    { "_id" : ObjectId("504ece595e8dc12ed97482ba"), "id" : 9, "content" : "test content", "name" : "wang9" } 
  
 
  

    > db.test.find({content:{$type:1}})  --表现content为double型类的 
  
 
  

    { "_id" : ObjectId("504ecfcc5e8dc12ed974837b"), "id" : 1, "content" : 666888, "name" : "JOE1" } 
  
 
  

    { "_id" : ObjectId("504ecfcc5e8dc12ed974837c"), "id" : 2, "content" : 666888, "name" : "JOE2" } 
  
 
  

    { "_id" : ObjectId("504ecfcc5e8dc12ed974837d"), "id" : 3, "content" : 666888, "name" : "JOE3" } 
  
 
  

    { "_id" : ObjectId("504ecfcc5e8dc12ed974837e"), "id" : 4, "content" : 666888, "name" : "JOE4" } 
  
 
  

    { "_id" : ObjectId("504ecfcc5e8dc12ed974837f"), "id" : 5, "content" : 666888, "name" : "JOE5" } 
  
 
  

    { "_id" : ObjectId("504ecfcc5e8dc12ed9748380"), "id" : 6, "content" : 666888, "name" : "JOE6" } 
  
 
  

    { "_id" : ObjectId("504ecfcc5e8dc12ed9748381"), "id" : 7, "content" : 666888, "name" : "JOE7" } 
  
 
  

    { "_id" : ObjectId("504ecfcc5e8dc12ed9748382"), "id" : 8, "content" : 666888, "name" : "JOE8" } 
  
 
  

    { "_id" : ObjectId("504ecfcc5e8dc12ed9748383"), "id" : 9, "content" : 666888, "name" : "JOE9" } 
  
 
  

    { "_id" : ObjectId("504ecfcc5e8dc12ed9748384"), "id" : 10, "content" : 666888, "name" : "JOE10" }

上面给出mongodb基于bson型类的列表：

每日一道理
春蚕死去了，但留下了华贵丝绸；蝴蝶死去了，但留下了漂亮的衣裳；画眉飞去了，但留下了美妙的歌声；花朵凋谢了，但留下了缕缕幽香；蜡烛燃尽了，但留下一片光明；雷雨过去了，但留下了七彩霓虹。

Type Description	Type value
Double	1
String	2
Object	3
Array	4
Binary data	5
Object id	7
Boolean	8
Date	9
Null	10
Regular expression	11
JavaScript code	13
Symbol	14
JavaScript code with scope	15
32-bit integer	16
Timestamp	17
64-bit integer	18
Min key	255
Max key	127