文档章节

mongoDB 索引管理

ruyees
 ruyees
发布于 2014/10/05 11:47
字数 1897
阅读 17
收藏 0

1.单列索引
在字段x上创建索引,1 (ascending) or -1 (descending) 1表示升序(asc),-1表示降序(desc)
> db.data.ensureIndex({x:1})


显示表data里面的所有索引
> db.data.getIndexes()
[
        {
                "name" : "_id_",
                "ns" : "recommender.data",
                "key" : {
                        "_id" : 1
                }
        },
        {
                "_id" : ObjectId("4befb146b0e29ba1ce20e0bb"),
                "ns" : "recommender.data",
                "key" : {
                        "x" : 1
                },
                "name" : "x_1"
        }
]


查找字段x为6的值,此时已经用到索引了
> db.data.find({x:6})
{ "_id" : ObjectId("4bee804ba23d558eb6687117"), "x" : 6, "name" : "caihuafeng1" }


2.默认索引
上述1中db.data.getIndexes()显示出来的一共有2个索引,其中_id是创建表的时候自动创 建的索引,此索引是不能够删除的。

 

3.文档作为索引的键值
a.单列索引
MongoDB的官方文档上面是这样说的:
Documents as Keys


Indexed fields may be of any type, including documents:


往数据库recommender的表data中插入三条记录
> db.data.insert({name:"1616",info:{url:"
http://www.1616.net/",city:"beijing "}});
> db.data.insert({name:"hao123",info:{url:"
http://www.hao123.com/",city:"beijing "}});
> db.data.insert({name:"ll4la",info:{url:"
http://www.114la.com/",city:"dongguan "}});


对字段info创建索引
> db.data.ensureIndex({info: 1});


显示表data上的所有索引
> db.data.getIndexes();
[
        {
                "name" : "_id_",
                "ns" : "recommender.data",
                "key" : {
                        "_id" : 1
                }
        },
        {
                "_id" : ObjectId("4befb146b0e29ba1ce20e0bb"),
                "ns" : "recommender.data",
                "key" : {
                        "x" : 1
                },
                "name" : "x_1"
        },
        {
                "_id" : ObjectId("4befb76bb0e29ba1ce20e0bf"),
                "ns" : "recommender.data",
                "key" : {
                        "info" : 1
                },
                "name" : "info_1"
        }
]
查找指定的记录,此时会用到索引
> db.data.find({info: {url:"
http://www.1616.net/",city:"beijing "}});
{ "_id" : ObjectId("4befb711b0e29ba1ce20e0bc"), "name" : "1616", "info" : { "url" : "
http://www.1616.net/ ", "city" : "beijing" } }


b.组合索引
建立组合索引
> db.data.ensureIndex({"info.url":1, "info.city":1});
> db.data.getIndexes();
[
        {
                "name" : "_id_",
                "ns" : "recommender.data",
                "key" : {
                        "_id" : 1
                }
        },
        {
                "_id" : ObjectId("4befb146b0e29ba1ce20e0bb"),
                "ns" : "recommender.data",
                "key" : {
                        "x" : 1
                },
                "name" : "x_1"
        },
        {
                "_id" : ObjectId("4befb76bb0e29ba1ce20e0bf"),
                "ns" : "recommender.data",
                "key" : {
                        "info" : 1
                },
                "name" : "info_1"
        },
        {
                "_id" : ObjectId("4befb9d1b0e29ba1ce20e0c0"),
                "ns" : "recommender.data",
                "key" : {
                        "info.url" : 1,
                        "info.city" : 1
                },
                "name" : "info.url_1_info.city_1"
        }
]


下面几个操作均会用到索引
> db.data.find({"info.url": "
http://www.1616.net/ ", "info.city": "beijing"});
{ "_id" : ObjectId("4befb711b0e29ba1ce20e0bc"), "name" : "1616", "info" : { "url" : "
http://www.1616.net/ ", "city" : "beijing" } }


> db.data.find({"info.url": " http://www.1616.net/ "});
{ "_id" : ObjectId("4befb711b0e29ba1ce20e0bc"), "name" : "1616", "info" : { "url" : "
http://www.1616.net/ ", "city" : "beijing" } }


1表示升序(asc),-1表示降序(desc)
> db.data.find({"info.url": /http:*/i}).sort({"info.url": 1, "info.city": 1});
{ "_id" : ObjectId("4befb740b0e29ba1ce20e0be"), "name" : "ll4la", "info" : { "url" : "
http://www.114la.com/ ", "city" : "dongguan" } }
{ "_id" : ObjectId("4befb711b0e29ba1ce20e0bc"), "name" : "1616", "info" : { "url" : "
http://www.1616.net/ ", "city" : "beijing" } }
{ "_id" : ObjectId("4befb723b0e29ba1ce20e0bd"), "name" : "hao123", "info" : { "url" : "
http://www.hao123.com/ ", "city" : "beijing" } }


> db.data.find({"info.url": /http:*/i}).sort({"info.url": 1});
{ "_id" : ObjectId("4befb740b0e29ba1ce20e0be"), "name" : "ll4la", "info" : { "url" : "
http://www.114la.com/ ", "city" : "dongguan" } }
{ "_id" : ObjectId("4befb711b0e29ba1ce20e0bc"), "name" : "1616", "info" : { "url" : "
http://www.1616.net/ ", "city" : "beijing" } }
{ "_id" : ObjectId("4befb723b0e29ba1ce20e0bd"), "name" : "hao123", "info" : { "url" : "
http://www.hao123.com/ ", "city" : "beijing" } }


> db.data.find({"info.url": /http:*/i}).sort({"info.url": -1});
{ "_id" : ObjectId("4befb723b0e29ba1ce20e0bd"), "name" : "hao123", "info" : { "url" : "
http://www.hao123.com/ ", "city" : "beijing" } }
{ "_id" : ObjectId("4befb711b0e29ba1ce20e0bc"), "name" : "1616", "info" : { "url" : "
http://www.1616.net/ ", "city" : "beijing" } }
{ "_id" : ObjectId("4befb740b0e29ba1ce20e0be"), "name" : "ll4la", "info" : { "url" : "
http://www.114la.com/ ", "city" : "dongguan" } }


4.组合索引
注意,这里的组合索引与上述3中的b中的组合索引是有点不同的,4里面是对一 级字段建立组合索引,而上述3中是对二级字段建立组合索引。


在字段name及info上面创建组合索引
> db.data.ensureIndex({name: 1, info: -1});


当创建组合索引时,字段后面的1表示升序,-1表示降序,是用1还是用-1主要是跟排序的时候或指定范围内查询 的时候有关的,具体看下面的英文原文的说明。
When creating an index, the number associated with a key specifies the direction of the index, so it should always be 1 (ascending) or -1 (descending). Direction doesn't matter for single key indexes or for random access retrieval but is important if you are doing sorts or range queries on compound indexes.


显示所有的索引
> db.data.getIndexes();
[
        {
                "name" : "_id_",
                "ns" : "recommender.data",
                "key" : {
                        "_id" : 1
                }
        },
        {
                "_id" : ObjectId("4befb146b0e29ba1ce20e0bb"),
                "ns" : "recommender.data",
                "key" : {
                        "x" : 1
                },
                "name" : "x_1"
        },
        {
                "_id" : ObjectId("4befb76bb0e29ba1ce20e0bf"),
                "ns" : "recommender.data",
                "key" : {
                        "info" : 1
                },
                "name" : "info_1"
        },
        {
                "_id" : ObjectId("4befb9d1b0e29ba1ce20e0c0"),
                "ns" : "recommender.data",
                "key" : {
                        "info.url" : 1,
                        "info.city" : 1
                },
                "name" : "info.url_1_info.city_1"
        },
        {
                "_id" : ObjectId("4befbfcfb0e29ba1ce20e0c1"),
                "ns" : "recommender.data",
                "key" : {
                        "name" : 1,
                        "info" : -1
                },
                "name" : "name_1_info_-1"
        }
]


下面的排序将用到上面的索引
最后一行的"name" : "ll4la"实际上是"name" : "114la"(就是将数字一写成了字母l),但是我录入的时候写成了"name" : "ll4la",是我写错了,但是排序的结果是对的。
> db.data.find({"info.url": /http:*/i}).sort({name:1, info: -1});
{ "_id" : ObjectId("4befb711b0e29ba1ce20e0bc"), "name" : "1616", "info" : { "url" : "
http://www.1616.net/ ", "city" : "beijing" } }
{ "_id" : ObjectId("4befb723b0e29ba1ce20e0bd"), "name" : "hao123", "info" : { "url" : "
http://www.hao123.com/ ", "city" : "beijing" } }
{ "_id" : ObjectId("4befb740b0e29ba1ce20e0be"), "name" : "ll4la", "info" : { "url" : "
http://www.114la.com/ ", "city" : "dongguan" } }


MongoDB组合索引规则
If you have a compound index on multiple fields, you can use it to query on the beginning subset of fields. So if you have an index on


a,b,c


you can use it query on


a


a,b


a,b,c


如果用过MySQL的话,看起来是不是很熟悉,原理跟MySQL是一样的。


5.唯一索引
往表data中插入一条记录。
> db.data.insert({firstname: "cai", lastname: "huafeng"});


由于表data中只有一记录有字段firstname及lastname,其它的行均没有相应的值,也就是均为 null,为null就说明是相同的,而唯一索引是不允许有相同的值的,所以下面创建唯一组合索引时报错了。


所以建立唯一索引时,不管是对单个字段还是多个字段建立索引,则最好每一行均有此字段,否则会报错。


> db.data.find();
{ "_id" : ObjectId("4bee745a0863b1c233b8b7ea"), "name" : "caihuafeng" }
{ "_id" : ObjectId("4bee745f0863b1c233b8b7eb"), "website" : "1616.net" }
{ "_id" : ObjectId("4bee804ba23d558eb6687117"), "x" : 6, "name" : "caihuafeng1" }
{ "_id" : ObjectId("4bee804ba23d558eb6687118"), "x" : 6, "name" : "caihuafeng2" }
{ "_id" : ObjectId("4bee804ba23d558eb6687119"), "x" : 6, "name" : "caihuafeng3" }
{ "_id" : ObjectId("4bee804ba23d558eb668711a"), "x" : 6, "name" : "caihuafeng4" }
{ "_id" : ObjectId("4bee804ba23d558eb668711b"), "x" : 6, "name" : "caihuafeng5" }
{ "_id" : ObjectId("4bee804ba23d558eb668711c"), "x" : 6, "name" : "caihuafeng6" }
{ "_id" : ObjectId("4bee804ba23d558eb668711d"), "x" : 6, "name" : "caihuafeng7" }
{ "_id" : ObjectId("4bee804ba23d558eb668711e"), "x" : 6, "name" : "caihuafeng8" }
{ "_id" : ObjectId("4bee804ba23d558eb668711f"), "x" : 6, "name" : "caihuafeng9" }
{ "_id" : ObjectId("4bee804ba23d558eb6687120"), "x" : 6, "name" : "caihuafeng10" }
{ "_id" : ObjectId("4befb711b0e29ba1ce20e0bc"), "name" : "1616", "info" : { "url" : "
http://www.1616.net/ ", "city" : "beijing" } }
{ "_id" : ObjectId("4befb723b0e29ba1ce20e0bd"), "name" : "hao123", "info" : { "url" : "
http://www.hao123.com/ ", "city" : "beijing" } }
{ "_id" : ObjectId("4befb740b0e29ba1ce20e0be"), "name" : "ll4la", "info" : { "url" : "
http://www.114la.com/ ", "city" : "dongguan" } }
{ "_id" : ObjectId("4befc51ab0e29ba1ce20e0c2"), "firstname" : "cai", "lastname" : "huafeng" }


> db.data.ensureIndex({firstname: 1, lastname: 1}, {unique: true});
E11000 duplicate key error index: recommender.data.$firstname_1_lastname_1  dup key: { : null, : null }


下面我们用另外一个表person来进行测试
> db.person.ensureIndex({firstname:1, lastname: 1},{unique: true});
> db.person.insert({firstname: 'cai', lastname: 'huafeng'});


第二次插入同样值的时候报错了,说明唯一索引生效了,其实跟MySQL里面是一样的。
> db.person.insert({firstname: 'cai', lastname: 'huafeng'});
E11000 duplicate key error index: recommender.person.$firstname_1_lastname_1  dup key: { : "cai", : "huafeng" }


6.唯一索引中的重复值处理
删除上述5中的索引,插入两行一样的记录


> db.person.dropIndexes();
{
        "nIndexesWas" : 2,
        "msg" : "non-_id indexes dropped for collection",
        "ok" : 1
}
> db.person.find();
{ "_id" : ObjectId("4befcda6b0e29ba1ce20e0cf"), "firstname" : "cai", "lastname" : "huafeng" }
> db.person.insert({firstname: 'cai', lastname: 'huafeng'});
> db.person.find();
{ "_id" : ObjectId("4befcda6b0e29ba1ce20e0cf"), "firstname" : "cai", "lastname" : "huafeng" }
{ "_id" : ObjectId("4befcef0b0e29ba1ce20e0d1"), "firstname" : "cai", "lastname" : "huafeng" }


如果现在直接在字段firstname及lastname上面创建唯一组合索 引的时候肯定会报错,我们来试一试:
> db.person.ensureIndex({firstname: 1, lastname: 1}, {unique: true});
E11000 duplicate key error index: recommender.person.$firstname_1_lastname_1  dup key: { : "cai", : "huafeng" }


查看表person的索引,我们可以看到,新创建的索引没有生成。
> db.person.getIndexes();
[
        {
                "name" : "_id_",
                "ns" : "recommender.person",
                "key" : {
                        "_id" : 1
                }
        }
]


可以在第二个json对象加入一项dropDups: true,这样在创建唯一组合索引的时候不会报错,保留文档中第一个重复的值,其它重复的值均删除。


再次测试一下,加入dropDups选项,虽然报错了,但是唯一组合索引已经建立了。
> db.person.ensureIndex({firstname: 1, lastname: 1}, {unique: true, dropDups: true});
E11000 duplicate key error index: recommender.person.$firstname_1_lastname_1  dup key: { : "cai", : "huafeng" }
> db.person.getIndexes();
[
        {
                "name" : "_id_",
                "ns" : "recommender.person",
                "key" : {
                        "_id" : 1
                }
        },
        {
                "_id" : ObjectId("4befcfd9b0e29ba1ce20e0d3"),
                "ns" : "recommender.person",
                "key" : {
                        "firstname" : 1,
                        "lastname" : 1
                },
                "name" : "firstname_1_lastname_1",
                "unique" : true,
                "dropDups" : true
        }
]


再次查询表person中的记录,发现重复的记录已经自动删除了。
> db.person.find();
{ "_id" : ObjectId("4befcda6b0e29ba1ce20e0cf"), "firstname" : "cai", "lastname" : "huafeng" }


MongoDB官方文档的说明
A unique index cannot be created on a key that has duplicate values. If you would like to create the index anyway, keeping the first document the database indexes and deleting all subsequent documents that have duplicate values, add the dropDups option.


db.things.ensureIndex({firstname : 1}, {unique : true, dropDups : true})

 


7.删除索引
a.删除某个表中的所有索引
To delete all indexes on the specified collection:


    db.collection.dropIndexes();


b.删除某个表中的单一索引
To delete a single index:


    db.collection.dropIndex({x: 1, y: -1})

 


> db.data.dropIndex({firstname: 1, lastname: 1});
{ "nIndexesWas" : 6, "ok" : 1 }


Running directly as a command without helper:


// note: command was "deleteIndexes", not "dropIndexes", before MongoDB v1.3.2
// remove index with key pattern {y:1} from collection foo
db.runCommand({dropIndexes:'foo', index : {y:1}})
// remove all indexes:
db.runCommand({dropIndexes:'foo', index : '*'})


> db.person.ensureIndex({firstname: 1, lastname: 1});


> db.runCommand({dropIndexes:'person', index:{firstname:1, lastname:1}});
{ "nIndexesWas" : 2, "ok" : 1 }

 

 

转载自:http://blog.sina.com.cn/s/blog_5e0d59fb0100qscl.html

本文转载自:http://zuoqiang.iteye.com/blog/1175131

共有 人打赏支持
ruyees
粉丝 3
博文 71
码字总数 0
作品 0
深圳
产品经理
基于云数据库MongoDB版进行应用开发

MongoDB是一个基于分布式文件存储的数据库,在互联网、物联网、游戏、金融等领域被广泛采用。由C++语言编写。旨在为WEB应用提供可扩展的高性能数据存储解决方案。MongoDB是当前最流行的Nosql...

mcy0425
09/14
0
0
mongoDB 学习笔记纯干货(mongoose、增删改查、聚合、索引、等等)

最后更新时间:2017-07-13 11:10:49 原始文章链接:http://www.lovebxm.com/2017/07/13/mongodb_primer/ MongoDB - 简介 官网:https://www.mongodb.com/ MongoDB 是一个基于分布式文件存储的...

Airship
06/01
0
0
MongoDB【最新版V2.6】- 发行说明

开发团队表示,MongoDB 2.6是迄今为止改进最大的版本,全面增强了核心服务器,引入了新的开创性的自动化工具、重要的企业特性等等。 MongoDB 2.6版本的主要改进包括: 1 重写了整个查询执行引...

止静
2014/10/13
0
0
阿里专家视频课限时抢!精讲+实操12节课学懂MongoDB数据库!

想要学习MongoDB却不知道如何入门? 漫天的培训课程却无一讲解实战案例? 云栖社区联手阿里云大学独家出品—— 阿里云MongoDB技术负责人、MongoDB中文社区联席主席联合操刀 12期精讲+实操,全...

a独家记忆
07/12
0
0
MongoDB集群部署 - 带访问控制的分片副本集

1. 前言   Ceilometer将meter、event等数据保存在MongoDB中,之前将MongoDB部署在控制节点上,使用三副本模式,时间长了发现meter数据爆炸式增长,区区2T的磁盘捉襟见肘,而想删除旧数据,...

Sai18
08/16
0
0

没有更多内容

加载失败,请刷新页面

加载更多

设计模式之 明确责任 观察者模式 状态模式 责任链模式

观察者模式是任务分发的一种模式。 如果认为我们设计的系统的各个模块(或子系统)的最终目的是完成共同任务,那么这个任务如何分配到多个模块的就是我们遇到的第一个问题。简单设计场合我们...

backbye
16分钟前
2
0
14-利用思维导图梳理JavaSE-大汇总

14-利用思维导图梳理JavaSE-Java基础知识大汇总 主要内容 1.对象入门 2.一切都是对象 3.程序流程控制 4.初始化和消除 5.权限访问控制 6.复用类 7.多态 8.接口与抽象类 9.内部类 10.容器 11.异...

飞鱼说编程
52分钟前
5
0
利用Lombok编写优雅的spring依赖注入代码,去掉繁人的@Autowired

大家平时使用spring依赖注入,都是怎么写的? @Servicepublic class OrderService { @Autowired private UserService userService;} 是不是很熟悉的感觉?但是呢 如果你用...

HeyS1
59分钟前
25
0
IBATIS 写BLOB字段遇到的问题

1、 首先遇到的配置问题,通过设置typeHandler 来支持写入。接下来由此引出了事务的问题。 <typeHandler jdbcType="BLOB" javaType="[B" callback="org.springframework.orm.ibatis.support....

echo-neo
今天
1
0
37. Sudoku Solver

Description tags: backtrack,hash table difficulty: hard Write a program to solve a Sudoku puzzle by filling the empty cells.A sudoku solution must satisfy all of the following......

52iSilence7
今天
1
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部