【MongoDB学习笔记九】MongoDB索引
MongoDB / houtizong 发布于 3年前 316
- 可以在任意列上建立索引
- 索引的构造和使用与传统关系型数据库几乎一样,适用于Oracle的索引优化技巧也适用于
Mongodb - 使用索引可以加快查询,但同时会降低修改,插入等的性能
- 内嵌文档照样可以建立使用索引
- cursor 用于指明这个查询，是否使用了索引，哪个属性使用索引
- nsScanned 用于指明本次查询，数据库扫描的文档数一共多少。最佳情况是这个数字应该尽可能的接近查询结果返回的文档数
- n 查询结果返回的文档数
- millis 执行查询耗费的毫秒数
索引
测试数据
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"}
查询计划结果说明
索引名
上面的例子中，索引的名字都是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.站长邮箱：[email protected];
文章归档
文章标签
友情链接
