数据库中的索引
- 说明：索引是一种排序好的便于快速查询的数据结构
- 作用：帮助数据库高效地查询数据
索引优缺点
- 优点
提高数据查询的效率，降低数据库的IO成本；
通过索引对数据进行排序，降低数据排序的成本，降低CPU的消耗。
- 缺点
占用磁盘空间；
大量索引影响SQL语句效率，因为每次插入和修改都需要更新索引。
语法
- 创建索引语法：db.集合名.createIndex(待创建索引的列[,额外选项])
- 参数：
待创建索引的列:{键:1,…,键:-1}
说明：1升序，2降序，例如{age:1}表示创建age索引并按照升序的方式存储。
额外选项：设置索引的名称或者唯一索引等等。
- 删除索引语法：
全部删除：db.集合名.dropIndexes()
删除指定：db.集合名.dropIndex(索引名)
- 查看索引语法：db.集合名.getIndexes()
练习
准备
use test5
for(var i=0;i<100000;i++){
db.c1.insert({'name':"aaa"+i,"age":i});
}> use test5
switched to db test5
> for(var i=0;i<100000;i++){
... db.c1.insert({'name':"aaa"+i,"age":i});
... }
WriteResult({ "nInserted" : 1 })
> db.c1.count()
100000创建普通索引
- 给name添加普通索引
> db.c1.createIndex({name:1})
{
"createdCollectionAutomatically" : false,
"numIndexesBefore" : 1,
"numIndexesAfter" : 2,
"ok" : 1
}
> db.c1.getIndexes()
[
{
"v" : 2,
"key" : {
"_id" : 1 # 默认的_id索引
},
"name" : "_id_",
"ns" : "test5.c1"
},
{
"v" : 2,
"key" : {
"name" : 1 # 新建的name索引
},
"name" : "name_1",
"ns" : "test5.c1"
}
]- 删除刚创建的name索引
> db.c1.dropIndex('name_1')
{ "nIndexesWas" : 2, "ok" : 1 }
> db.c1.getIndexes()
[
{
"v" : 2,
"key" : {
"_id" : 1
},
"name" : "_id_",
"ns" : "test5.c1"
}
]- 给name创建索引并起名为woniu
> db.c1.createIndex({name:1},{name:"woniu"})
{
"createdCollectionAutomatically" : false,
"numIndexesBefore" : 1,
"numIndexesAfter" : 2,
"ok" : 1
}
> db.c1.getIndexes()
[
{
"v" : 2,
"key" : {
"_id" : 1
},
"name" : "_id_",
"ns" : "test5.c1"
},
{
"v" : 2,
"key" : {
"name" : 1
},
"name" : "woniu",
"ns" : "test5.c1"
}
]创建复合/组合索引
需求：给name和age添加组合索引
说明：就是一次性给两个字段建立索引
语法：db.集合名.createIndex({键1:方式,键2:方式})
> db.c1.createIndex({name:1,age:1})
{
"createdCollectionAutomatically" : false,
"numIndexesBefore" : 2,
"numIndexesAfter" : 3,
"ok" : 1
}
> db.c1.getIndexes()
[
{
"v" : 2,
"key" : {
"_id" : 1
},
"name" : "_id_",
"ns" : "test5.c1"
},
{
"v" : 2,
"key" : {
"name" : 1
},
"name" : "woniu",
"ns" : "test5.c1"
},
{
"v" : 2,
"key" : {
"name" : 1,
"age" : 1
},
"name" : "name_1_age_1",
"ns" : "test5.c1"
}
]创建唯一索引
语法：db.集合名.createIndex(待添加索引的列,{unique:列名})
- 删除全部索引
> db.c1.dropIndexes()
{
"nIndexesWas" : 3,
"msg" : "non-_id indexes dropped for collection",
"ok" : 1
}
# 系统自带的索引不会删
> db.c1.getIndexes()
[
{
"v" : 2,
"key" : {
"_id" : 1
},
"name" : "_id_",
"ns" : "test5.c1"
}- 设置唯一索引
> db.c1.createIndex({name:1},{unique:"name"})
{
"createdCollectionAutomatically" : false,
"numIndexesBefore" : 1,
"numIndexesAfter" : 2,
"ok" : 1
}
> db.c1.getIndexes()
[
{
"v" : 2,
"key" : {
"_id" : 1
},
"name" : "_id_",
"ns" : "test5.c1"
},
{
"v" : 2,
"unique" : true,
"key" : {
"name" : 1
},
"name" : "name_1",
"ns" : "test5.c1"
}
]- 测试唯一索引的特性
> db.c1.insert({name:"a"})
WriteResult({ "nInserted" : 1 })
> db.c1.find({name:"a"})
{ "_id" : ObjectId("60311d35c7988913752fabc7"), "name" : "a" }
# name="a"的数据已经存在了，并且name列设置了唯一索引，因此再插入相同的数据就会报错
> db.c1.insert({name:"a"})
WriteResult({
"nInserted" : 0,
"writeError" : {
"code" : 11000,
"errmsg" : "E11000 duplicate key error collection: test5.c1 index: name_1 dup key: { : \"a\" }"
}
})分析索引
- 语法：db.集合名.find().explain(‘executionStats’)
- 说明：
> db.c1.find({id:500}).explain('executionStats')
{
......
},
"executionStats" : { # 执行计划相关统计信息
"executionSuccess" : true,# 执行成功的状态
"nReturned" : 0,# 返回结果集数目
"executionTimeMillis" : 39,# 执行所需时间，毫秒
"totalKeysExamined" : 0,# 索引检查时间
"totalDocsExamined" : 100001,# 检查文档总数
"executionStages" : {
"stage" : "COLLSCAN",# 索引扫描方式
"filter" : { # 过滤条件
"id" : {
"$eq" : 500
}
},
"nReturned" : 0,# 返回结果集数目
"executionTimeMillisEstimate" : 42,# 预估执行时间，毫秒
"works" : 100003,# 工作单元数，一个查询会被派生为一些小的工作单元
"advanced" : 0,# 优先返回的结果数目
"needTime" : 100002,
"needYield" : 0,
"saveState" : 782,
"restoreState" : 782,
"isEOF" : 1,
"invalidates" : 0,
"direction" : "forward",
"docsExamined" : 100001
}
},
"serverInfo" : {
"host" : "LAPTOP-QBLKQL59",
"port" : 27017,
"version" : "3.4.4",
"gitVersion" : "888390515874a9debd1b6c5d36559ca86b44babd"
},
"ok" : 1
}扫描方式：
COLLSCAN 全表扫描
IXSCAN 索引扫描
FETCH 根据索引去检索指定document
- age未添加索引情况
> db.c1.find({age:1}).explain('executionStats')
{
......
},
"executionStats" : {
"executionSuccess" : true,
"nReturned" : 1,
"executionTimeMillis" : 41,
"totalKeysExamined" : 0,
"totalDocsExamined" : 100001,
"executionStages" : {
"stage" : "COLLSCAN",
"filter" : {
"age" : {
"$eq" : 1
}
},
"nReturned" : 1,
"executionTimeMillisEstimate" : 32,
"works" : 100003,
"advanced" : 1,
"needTime" : 100001,
"needYield" : 0,
"saveState" : 782,
"restoreState" : 782,
"isEOF" : 1,
"invalidates" : 0,
"direction" : "forward",
"docsExamined" : 100001
}
},
"serverInfo" : {
......
},
"ok" : 1
}此时扫描方式为全表扫描。
- age添加索引后情况
> db.c1.createIndex({age:1})
{
"createdCollectionAutomatically" : false,
"numIndexesBefore" : 2,
"numIndexesAfter" : 3,
"ok" : 1
}
> db.c1.find({age:1}).explain('executionStats')
{
"queryPlanner" : {
......
},
"executionStats" : {
"executionSuccess" : true,
"nReturned" : 1,
"executionTimeMillis" : 9,
"totalKeysExamined" : 1,
"totalDocsExamined" : 1,
"executionStages" : {
"stage" : "FETCH",
"nReturned" : 1,
"executionTimeMillisEstimate" : 10,
"works" : 2,
"advanced" : 1,
"needTime" : 0,
"needYield" : 0,
"saveState" : 0,
"restoreState" : 0,
"isEOF" : 1,
"invalidates" : 0,
"docsExamined" : 1,
"alreadyHasObj" : 0,
"inputStage" : {
"stage" : "IXSCAN",
"nReturned" : 1,
"executionTimeMillisEstimate" : 10,
"works" : 2,
"advanced" : 1,
"needTime" : 0,
"needYield" : 0,
"saveState" : 0,
"restoreState" : 0,
"isEOF" : 1,
"invalidates" : 0,
"keyPattern" : {
"age" : 1
},
"indexName" : "age_1",
"isMultiKey" : false,
"multiKeyPaths" : {
"age" : [ ]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"age" : [
"[1.0, 1.0]"
]
},
"keysExamined" : 1,
"seeks" : 1,
"dupsTested" : 0,
"dupsDropped" : 0,
"seenInvalidated" : 0
}
}
},
"serverInfo" : {
......
},
"ok" : 1
}此时索引方式为索引扫描，根据索引去检索指定文档，工作单元数大大减少，检索效率极大提高。
索引选择规则
- 为常做条件、排序、分组的字段建立索引
- 选择唯一索引
- 选择较小的数据列，为较长的字符串使用前缀索引
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
