【MongoDB学习笔记九】MongoDB索引

MongoDB  /  houtizong 发布于 1年前   115

索引

  • 可以在任意列上建立索引
  • 索引的构造和使用与传统关系型数据库几乎一样,适用于Oracle的索引优化技巧也适用于
    Mongodb
  • 使用索引可以加快查询,但同时会降低修改,插入等的性能
  • 内嵌文档照样可以建立使用索引

测试数据

 

 

var p1 = {"name":"Jack","age":34,"nickname":"xiaoqiang",friends:[  {"name":"Cloe","age":"28"},  {"name":"Audrey","age":"31"},]}var p2 = {"name":"Cloe","age":28,friends:[  {"name":"Jack","age":"34"},  {"name":"Mike","age":"31"},]}var p3 = {"name":"Audrey","age":31,friends:[  {"name":"Jack","age":"34"},  {"name":"Hammer","age":"61","relation":"parent"},]}var p = [p1,p2, p3];db.people.drop();db.people.insert(p);

 

 

 

建立索引

当查询列中包含name属性时,索引将其作用

db.people.ensureIndex({"name" : 1}) //1表示正向索引

 

建立复合索引

 

复合索引的起作用是当name和age属性相邻,复合索引起作用

db.people.ensureIndex({"name" : 1,"age" : -1}) //1表示正向索引,-1表示反向索引

 

查询计划查看索引是否作用

查询时,哪个索引起作用与find中定义的查询对象(JSON)的书写顺序无关,这是合理的,因为JSON对象本身是键值对,无序的键值集合

 

1. db.people.find().explain()

 

 

> db.people.find().explain();{"cursor" : "BasicCursor", //因为查询属性不包含定义索引的属性,因此索引没有起作用"isMultiKey" : false,"n" : 3,"nscannedObjects" : 3,"nscanned" : 3,"nscannedObjectsAllPlans" : 3,"nscannedAllPlans" : 3,"scanAndOrder" : false,"indexOnly" : false,"nYields" : 0,"nChunkSkips" : 0,"millis" : 11,"indexBounds" : {},"server" : "tom-Inspiron-3521:27017"}

 

 

2. db.people.find({"name":"Jack"});

 

> db.people.find({"name":"Jack"}).explain();{"cursor" : "BtreeCursor name_1", //使用B数游标 name_1就是创建于name属性上的索引"isMultiKey" : false,"n" : 1,"nscannedObjects" : 1,"nscanned" : 1,"nscannedObjectsAllPlans" : 1,"nscannedAllPlans" : 1,"scanAndOrder" : false,"indexOnly" : false,"nYields" : 0,"nChunkSkips" : 0,"millis" : 10,"indexBounds" : {"name" : [["Jack","Jack"]]},"server" : "tom-Inspiron-3521:27017"}

 

 

 3. db.people.find({"name":"Jack","age":34});

 

> db.people.find({"name":"Jack","age":34}).explain();{"cursor" : "BtreeCursor name_1_age_-1", //使用了B树索引,定义于name和age上的索引"isMultiKey" : false,"n" : 1,"nscannedObjects" : 1,"nscanned" : 1,"nscannedObjectsAllPlans" : 1,"nscannedAllPlans" : 1,"scanAndOrder" : false,"indexOnly" : false,"nYields" : 0,"nChunkSkips" : 0,"millis" : 0,"indexBounds" : {"name" : [["Jack","Jack"]],"age" : [[34,34]]},"server" : "tom-Inspiron-3521:27017"}

 

 

  4. db.people.find({"age":34, "name":"Jack"});

 

}> db.people.find({"age":34, "name":"Jack"}).explain();{"cursor" : "BtreeCursor name_1_age_-1",//age和name的次序颠倒不影响索引的使用"isMultiKey" : false,"n" : 1,"nscannedObjects" : 1,"nscanned" : 1,"nscannedObjectsAllPlans" : 1,"nscannedAllPlans" : 1,"scanAndOrder" : false,"indexOnly" : false,"nYields" : 0,"nChunkSkips" : 0,"millis" : 0,"indexBounds" : {"name" : [["Jack","Jack"]],"age" : [[34,34]]},"server" : "tom-Inspiron-3521:27017"}

 

 

 5. db.people.find({"age":34, "nickname":"xiaoqiang", "name":"Jack"});

 

 

db.people.find({"age":34, "nickname":"xiaoqiang", "name":"Jack"}).explain();{"cursor" : "BtreeCursor name_1", //因为name索引列定义了,因此使用name索引"isMultiKey" : false,"n" : 1,"nscannedObjects" : 1,"nscanned" : 1,"nscannedObjectsAllPlans" : 3,"nscannedAllPlans" : 3,"scanAndOrder" : false,"indexOnly" : false,"nYields" : 0,"nChunkSkips" : 0,"millis" : 17,"indexBounds" : {"name" : [["Jack","Jack"]]},"server" : "tom-Inspiron-3521:27017"}

 

 

 6. db.people.find({ "name":"Jack","age":34, "nickname":"xiaoqiang"});

 

 

 db.people.find({ "name":"Jack","age":34, "nickname":"xiaoqiang"}).explain(); //name,age,nickname数序{"cursor" : "BtreeCursor name_1", //name属性定义的索引起作用"isMultiKey" : false,"n" : 1,"nscannedObjects" : 1,"nscanned" : 1,"nscannedObjectsAllPlans" : 3,"nscannedAllPlans" : 3,"scanAndOrder" : false,"indexOnly" : false,"nYields" : 0,"nChunkSkips" : 0,"millis" : 0,"indexBounds" : {"name" : [["Jack","Jack"]]},"server" : "tom-Inspiron-3521:27017"}

 

查询计划结果说明

  • cursor 用于指明这个查询,是否使用了索引,哪个属性使用索引
  • nsScanned 用于指明本次查询,数据库扫描的文档数一共多少。最佳情况是这个数字应该尽可能的接近查询结果返回的文档数
  • n 查询结果返回的文档数
  • millis 执行查询耗费的毫秒数

索引名

上面的例子中,索引的名字都是MongoDB自动创建的,如何创建索引名呢?

 

唯一索引

 

为内嵌文档的属性设置索引

 

查看索引

索引建好后,如何察看当前的数据库有哪些索引呢?可见,默认情况下,每个集合的_id属性都已经是正向索引,这是MongoDB内置提供的

 

> db.system.indexes.find();{ "v" : 1, "name" : "_id_", "key" : { "_id" : 1 }, "ns" : "foobar.persons" }{ "v" : 1, "name" : "_id_", "key" : { "_id" : 1 }, "ns" : "foobar.db-text" }{ "v" : 1, "name" : "_id_", "key" : { "_id" : 1 }, "ns" : "foobar.addresses" }{ "v" : 1, "name" : "_id_", "key" : { "_id" : 1 }, "ns" : "foobar.c" }{ "v" : 1, "name" : "_id_", "key" : { "_id" : 1 }, "ns" : "foobar.people" }{ "v" : 1, "name" : "name_1", "key" : { "name" : 1 }, "ns" : "foobar.people" }{ "v" : 1, "name" : "name_1_age_-1", "key" : { "name" : 1, "age" : -1 }, "ns" : "foobar.people" }

 

 删除索引

 

> db.runCommand({"dropIndexes":"db.people","index":"name_1"}); //db.people不工作{ "ok" : 0, "errmsg" : "ns not found" }> db.runCommand({"dropIndexes":"foobar.people","index":"name_1"}); //foobar.people不工作{ "ok" : 0, "errmsg" : "ns not found" }> db.runCommand({"dropIndexes":"people","index":"name_1"}); //使用people,即指定了集合的名称即可{ "nIndexesWas" : 3, "ok" : 1 }

 

 

 

 

 

 

 

请勿发布不友善或者负能量的内容。与人为善,比聪明更重要!

留言需要登陆哦

技术博客集 - 网站简介:
前后端技术:
后端基于Hyperf2.1框架开发,前端使用Bootstrap可视化布局系统生成

网站主要作用:
1.编程技术分享及讨论交流,内置聊天系统;
2.测试交流框架问题,比如:Hyperf、Laravel、TP、beego;
3.本站数据是基于大数据采集等爬虫技术为基础助力分享知识,如有侵权请发邮件到站长邮箱,站长会尽快处理;
4.站长邮箱:514224527@qq.com;

      订阅博客周刊 去订阅

文章归档

文章标签

友情链接

Auther ·HouTiZong
侯体宗的博客
© 2020 zongscan.com
版权所有ICP证 : 粤ICP备20027696号
PHP交流群 也可以扫右边的二维码
侯体宗的博客