MongoDB---索引
MongoDB---索引
索引是用来加快查询的,数据库索引与数据的索引类似,有了索引就不需要翻遍整本书,数据库可以直接在索引中查找,
使得查询速度很快,在索引中找到条目后,就可以直接跳转到目标文档的位置.
1.索引简介
要掌握如何为查询配置最佳索引会有些难度.
MongoDB索引几乎和关系型数据库的索引一样.绝大数优化关系型数据库索引的技巧同样适用于MongoDB.
如:
db.refactor.insert({"username":"refactor","age":24,"isactive":true})
db.refactor.insert({"username":"refactor","age":30,"isactive":false})
db.refactor.insert({"username":"aaaaa","age":24,"isactive":false})
db.refactor.insert({"username":"aaaaa","age":34,"isactive":true})
db.refactor.insert({"username":"sssssss","age":24,"isactive":true})
db.refactor.insert({"username":"tttttt","age":24,"isactive":true})
db.refactor.insert({"username":"tttttt","age":54,"isactive":true})
db.refactor.insert({"username":"bbbbb","age":24,"isactive":false})
db.refactor.insert({"username":"rrrrr","age":24,"isactive":true})
db.refactor.insert({"username":"rrrrr","age":54,"isactive":false})
www.zzzyk.com
要按照username键进行查找,就可以在此键上建立索引,来提高查询速度.
db.refactor.ensureIndex({"username":1})
对某个键创建索引会加速对该键的查询,但是对于其他的查询可能没有帮助,即便查询中包含了被索引的键.
db.refactor.find({"age":24}).sort({"age":1,"username":1})
不会用到username索引.服务器必须查找所有文档,找到想要的日期,这个过程叫:表扫描,就是在没有索引的书中查找
内容,要从第一页开始,从前翻到后.通常说,应避免让服务器做表扫描,因为集合很大时会很慢.
一定要创建查询中用到的所有键索引,对于上面的查询,应该建立age和username的索引.
db.refactor.ensureIndex({"age":1,"username":1})
传递给ensureIndex的文档是一组值为1或-1的键,表示索引的创建方向.若索引只有一个键,则方向无关紧要.
若是有多个键,就得考虑索引的方向问题了.
如:
> db.runCommand({"dropIndexes":"refactor","index":"*"})
{
"nIndexesWas" : 2,
"msg" : "non-_id indexes dropped for collection",
"ok" : 1
}
> db.refactor.ensureIndex({"username":1,"age":1})
> db.refactor.ensureIndex({"username":1,"age":-1})
> db.system.indexes.find()
{ "v" : 1, "key" : { "_id" : 1 }, "ns" : "test.blog", "name" : "_id_" }
{ "v" : 1, "key" : { "_id" : 1 }, "ns" : "test.refactor", "name" : "_id_" }
{ "v" : 1, "key" : { "_id" : 1 }, "ns" : "test.users", "name" : "_id_" }
{ "v" : 1, "key" : { "username" : 1, "age" : 1 }, "ns" : "test.refactor", "name"
: "username_1_age_1" } www.zzzyk.com
{ "v" : 1, "key" : { "username" : 1, "age" : -1 }, "ns" : "test.refactor", "name
" : "username_1_age_-1" }
如果以{"username":1,"age":1}这种方式创建索引,MongoDB会按如下方式组织:
> db.refactor.find().hint({"username":1,"age":1})
{ "_id" : ObjectId("500231f4218b8ef3edbc6f00"), "username" : "aaaaa", "age" : 24
, "isactive" : false }
{ "_id" : ObjectId("500231f4218b8ef3edbc6f01"), "username" : "aaaaa", "age" : 34
, "isactive" : true }
{ "_id" : ObjectId("500231f4218b8ef3edbc6f05"), "username" : "bbbbb", "age" : 24
, "isactive" : false }
{ "_id" : ObjectId("500231f4218b8ef3edbc6efe"), "username" : "refactor", "age" :
24, "isactive" : true }
{ "_id" : ObjectId("500231f4218b8ef3edbc6eff"), "username" : "refactor", "age" :
30, "isactive" : false }
{ "_id" : ObjectId("500231f4218b8ef3edbc6f06"), "username" : "rrrrr", "age" : 24
, "isactive" : true }
{ "_id" : ObjectId("500231f6218b8ef3edbc6f07"), "username" : "rrrrr", "age" : 54
, "isactive" : false }
{ "_id" : ObjectId("500231f4218b8ef3edbc6f02"), "username" : "sssssss", "age" :
24, "isactive" : true }
{ "_id" : ObjectId("500231f4218b8ef3edbc6f03"), "username" : "tttttt", "age" : 2
4, "isactive" : true }
{ "_id" : ObjectId("500231f4218b8ef3edbc6f04"), "username" : "tttttt", "age" : 5
4, "isactive" : true }
用户名按照字母升序排列,同名的组按照年龄升序排列.
如果以{"username":1,"age":-1}这种方式创建索引,MongoDB会按如下方式组织:
> db.refactor.find().hint({"username":1,"age":-1})
{ "_id" : ObjectId("500231f4218b8ef3edbc6f01"), "username" : "aaaaa", "age" : 34
, "isactive" : true }
{ "_id" : ObjectId("500231f4218b8ef3edbc6f00"), "username" : "aaaaa", "age" : 24
, "isactive" : false }
{ "_id" : ObjectId("500231f4218b8ef3edbc6f05"), "username" : "bbbbb", "age" : 24
, "isactive" : false } www.zzzyk.com
{ "_id" : ObjectId("500231f4218b8ef3edbc6eff"), "username" : "refactor", "age" :
30, "isactive" : false }
{ "_id" : ObjectId("500231f4218b8ef3edbc6efe"), "username" : "refactor", "age" :
24, "isactive" : true }
{ "_id" : ObjectId("500231f6218b8ef3edbc6f07"), "username" : "rrrrr", "age" : 54
, "isactive" : false }
{ "_id" : ObjectId("500231f4218b8ef3edbc6f06"), "username" : "rrrrr", "age" : 24
, "isactive" : true }
{ "_id" : ObjectId("500231f4218b8ef3edbc6f02"), "username" : "sssssss", "age" :
24, "isactive" : true }
{ "_id" : ObjectId("500231f4218b8ef3edbc6f04"), "username" : "tttttt", "age" : 5
4, "isactive" : true }
{ "_id" : ObjectId("500231f4218
- 更多DB2疑问解答：
- sql语句 从DB2移动到其他数据库
- DB2 character类型更新报错
- db2 怎么查看执行的sql语句需要多少系统临时表空间?
- db2数据库的table表里，一money字段中的数据显示为：56000,00 本人想要用sql语句将它修改为56,000,000
- DB2导出表结构，能不能只导出指定的表结构。求大神指教，给出命令哈
- DB2数据库，增加数据表空间容量需要重启数据库吗？
- cognos连接db2,为什么启动不了服务啊， 连接Oracle可以启动服务。。
- PostgreSQL对比DB2
- db2如何查看编写好的存储过程？
- db2数值截取
- 谁知道关于DB2怎么看系统日志，归档日志的位置。并且怎么修改日志呢？
- db2怎样重组表结构?
- 求一本【牛新庄的DB2运维教程】？
- db2 的 SECADM权限 是干什么的
- DB2怎么删除表空间所以数据