2022MongoDB7-索引

在MongoDB中，所有的集合都有一个默认_id索引，

1给createtime创建一个降序排列的索引：

db.createIndex({"createtime":-1})

2查看索引：

db.order.getIndexes();

3创建索引时默认background=false，表示前台创建索引，如果设为true就表示后台创建索引，如果集合数据量大就把background设为true

前台创建索引缺点：创建过程中对外可读不可写

后台创建索引优点：创建过程中可读可写

#用后台创建索引，索引名称叫tyjs09
db.order.createIndex({"title":1},{"background":true, "name":"tyjs09"});
#查看集合中使用索引的key都有哪些：
db.order.getIndexKeys();
#查看当前集合中索引的总容量
db.order.totalIndexSize();
#查看索引的单个容量
db.order.totalIndexSize(1);
#删除tyjs09这个索引
 db.order.dropIndex("tyjs09");
 #一次性删除所有的自建索引
 db.order.dropIndexes();
 #把该集合上的全部索引重建一遍
 db.order.reIndex();
备注：一个集合经过了一个月的时间，随着写操作越来越多，导致索引变慢了，索引不够平衡了，这时候就需要重建索引了，重建索引的频率取决于你数据写的频率

索引的类型：

1单字段索引

#单字段索引使用场景：
> db.order.find({"title":"o1"});
{ "_id" : ObjectId("61dc1836a3c2932347187ce3"), "title" : "o1", "payment" : 10086, "items" : [ "手机", "手表", "手机壳" ] }
#分析本次查询使用的技术
> db.order.find({"title":"o1"}).explain();
{
    "queryPlanner" : {
        "plannerVersion" : 1,
        "namespace" : "users.order", #所属的集合是哪个集合users数据库中的order集合
        "indexFilterSet" : false,
        #被解析的查询条件是title=o1
        "parsedQuery" : {
            "title" : {
                "$eq" : "o1"
            }
        },
        "queryHash" : "6E0D6672",
        "planCacheKey" : "B1CDA929",
        #执行的计划是FETCH抓取策略
        "winningPlan" : {
            "stage" : "FETCH",
            "inputStage" : {
                "stage" : "IXSCAN",
                "keyPattern" : {
                    "title" : 1
                },
                "indexName" : "title_1",
                "isMultiKey" : false,
                "multiKeyPaths" : {
                    "title" : [ ]
                },
                "isUnique" : false,
                "isSparse" : false,
                "isPartial" : false,
                "indexVersion" : 2,
                "direction" : "forward",
                "indexBounds" : {
                    "title" : [
                        "[\"o1\", \"o1\"]"
                    ]
                }
            }
        },
        "rejectedPlans" : [ ]
    },
    "serverInfo" : {
        "host" : "6533ac116da1",
        "port" : 27017,
        "version" : "4.2.6",
        "gitVersion" : "20364840b8f1af16917e4c23c1b5f5efd8b352f8"
    },
    "ok" : 1
}
> 

2交叉索引：

用到2个以上带索引的字段叫交叉索引，例如下例中使用了id和title这两个字段进行索引查询

> db.order.find({"_id":ObjectId("61dc1836a3c2932347187ce7"), "title":"o5"});
{ "_id" : ObjectId("61dc1836a3c2932347187ce7"), "title" : "o5", "payment" : 10086, "items" : [ "电脑", "手机", "手机" ] }

> db.order.find({"_id":ObjectId("61dc1836a3c2932347187ce7"), "title":"o5"}).explain();
{
    "queryPlanner" : {
        "plannerVersion" : 1,
        "namespace" : "users.order",
        "indexFilterSet" : false,
        "parsedQuery" : {
            #查询条件and
            "$and" : [ 
                {
                    "_id" : {
                        "$eq" : ObjectId("61dc1836a3c2932347187ce7")
                    }
                },
                {
                    "title" : {
                        "$eq" : "o5"
                    }
                }
            ]
        },
        "queryHash" : "322E1A74",
        "planCacheKey" : "32C89328",
        "winningPlan" : {
            "stage" : "FETCH", #查询规则抓取
            "filter" : {
                "title" : {
                    "$eq" : "o5"
                }
            },
            "inputStage" : {
                "stage" : "IXSCAN", #使用了IXSCAN索引查询
                "keyPattern" : {
                    "_id" : 1
                },
                "indexName" : "_id_", #用到的索引名称
                "isMultiKey" : false,
                "multiKeyPaths" : {
                    "_id" : [ ]
                },
                "isUnique" : true,
                "isSparse" : false,
                "isPartial" : false,
                "indexVersion" : 2,
                "direction" : "forward",
                "indexBounds" : {
                    "_id" : [
                        "[ObjectId('61dc1836a3c2932347187ce7'), ObjectId('61dc1836a3c2932347187ce7')]"
                    ]
                }
            }
        },
        "rejectedPlans" : [ ]
    },
    "serverInfo" : {
        "host" : "6533ac116da1",
        "port" : 27017,
        "version" : "4.2.6",
        "gitVersion" : "20364840b8f1af16917e4c23c1b5f5efd8b352f8"
    },
    "ok" : 1
}
> 

3复合索引：

同时给两个字段创建索引，叫复合索引，好于交叉索引

db.order.createIndex({"title":1,"payment":1});

4多key索引：

把数组中的每一个元素都拿出来做独立管理

> db.order.find();
{ "_id" : ObjectId("61dc1836a3c2932347187ce7"), "title" : "o5", "payment" : 10086, "items" : [ "电脑", "手机", "手机" ] }
> 
#给数组的key创建索引就是多key索引
> db.order.createIndex({"items":1});
{
    "createdCollectionAutomatically" : false,
    "numIndexesBefore" : 1,
    "numIndexesAfter" : 2,
    "ok" : 1
}
> 

5唯一索引：

db.order.createIndex({"title":1}, {"background":true, "unique":true});

练习：

#删除全部索引
> db.order.dropIndexes();
{
    "nIndexesWas" : 2,
    "msg" : "non-_id indexes dropped for collection",
    "ok" : 1
}
#插入一条同名的数据
> db.order.insert({"title":"o1", "payment":10000});
WriteResult({ "nInserted" : 1 })
> db.order.find();
{ "_id" : ObjectId("61dc1836a3c2932347187ce3"), "title" : "o1", "payment" : 10086, "items" : [ "手机", "手表", "手机壳" ] }
{ "_id" : ObjectId("61dc1836a3c2932347187ce4"), "title" : "o2", "payment" : 10086, "items" : [ "电脑", "笔记本", "手机" ] }
{ "_id" : ObjectId("61dc1836a3c2932347187ce5"), "title" : "o3", "payment" : 10086, "items" : [ "电脑", "手表", "SIA" ] }
{ "_id" : ObjectId("61dc1836a3c2932347187ce6"), "title" : "o4", "payment" : 10086, "items" : [ "手机", "SIA", "笔记本" ] }
{ "_id" : ObjectId("61dc1836a3c2932347187ce7"), "title" : "o5", "payment" : 10086, "items" : [ "电脑", "手机", "手机" ] }
{ "_id" : ObjectId("61dd8c1c0de2d306bb1aa20c"), "title" : "o1", "payment" : 10000 }
> 
#创建唯一索引报错
> db.order.createIndex({"title":1}, {"background":true, "unique":true});
{
    "ok" : 0,
    "errmsg" : "E11000 duplicate key error collection: users.order index: title_1 dup key: { title: \"o1\" }",
    "code" : 11000,
    "codeName" : "DuplicateKey",
    "keyPattern" : {
        "title" : 1
    },
    "keyValue" : {
        "title" : "o1"
    }
}
#把新创建的这条数据删除
> db.order.remove({"payment":10000});
WriteResult({ "nRemoved" : 1 })
#创建唯一索引成功
> db.order.createIndex({"title":1}, {"background":true, "unique":true});
{
    "createdCollectionAutomatically" : false,
    "numIndexesBefore" : 1,
    "numIndexesAfter" : 2,
    "ok" : 1
}
> db.order.find();
{ "_id" : ObjectId("61dc1836a3c2932347187ce3"), "title" : "o1", "payment" : 10086, "items" : [ "手机", "手表", "手机壳" ] }
{ "_id" : ObjectId("61dc1836a3c2932347187ce4"), "title" : "o2", "payment" : 10086, "items" : [ "电脑", "笔记本", "手机" ] }
{ "_id" : ObjectId("61dc1836a3c2932347187ce5"), "title" : "o3", "payment" : 10086, "items" : [ "电脑", "手表", "SIA" ] }
{ "_id" : ObjectId("61dc1836a3c2932347187ce6"), "title" : "o4", "payment" : 10086, "items" : [ "手机", "SIA", "笔记本" ] }
{ "_id" : ObjectId("61dc1836a3c2932347187ce7"), "title" : "o5", "payment" : 10086, "items" : [ "电脑", "手机", "手机" ] }
> 
#查看索引：
> db.order.getIndexes();
[
    {
        "v" : 2,
        "key" : {
            "_id" : 1
        },
        "name" : "_id_",
        "ns" : "users.order"
    },
    {
        "v" : 2,
        "unique" : true, #唯一索引
        "key" : {
            "title" : 1
        },
        "name" : "title_1",
        "ns" : "users.order",
        "background" : true
    }
]
> 
#唯一索引创建后再次插入重复数据报错
> db.order.insert({"title":"o1"});
WriteResult({
    "nInserted" : 0,
    "writeError" : {
        "code" : 11000,
        "errmsg" : "E11000 duplicate key error collection: users.order index: title_1 dup key: { title: \"o1\" }"
    }
})
> 

6部分索引：

部分索引需要一个条件即部分过滤表达式partialFilterEcpression,

#先把所有的索引全部干掉
[root@db01 ~]#docker exec -it mongod mongo admin
MongoDB shell version v4.2.6
connecting to: mongodb://127.0.0.1:27017/admin?compressors=disabled&gssapiServiceName=mongodb
Implicit session: session { "id" : UUID("7c780653-966f-4afa-b882-5f8ab4079b01") }
MongoDB server version: 4.2.6
> db.auth("tyjs09","123456");
1
> use users;
switched to db users
> db.order.dropIndexes();
{
    "nIndexesWas" : 2,
    "msg" : "non-_id indexes dropped for collection",
    "ok" : 1
}
> 
#过滤条件和索引字段可以不同，给payment加一个索引
#给payment值大于500的数据创建索引
> db.order.createIndex({"payment":1}, {"partialFilterExpression":{"payment":{"$gt":500}}});

7覆盖索引查询(适用于对性能要求及其高的场景)

你查询时返回的投影和你查询时的查询条件都属于同一个索引就是覆盖索引查询

#投影查询举例：
#投影id不要，只要title
> db.order.find({"title":"o1"}, {"_id":0, "title":1});
{ "title" : "o1" }
> 
分析：
因为条件{"title":"o1"}在内存里，数据"title":1}在内存里，不需要访问硬盘