【mongoDB】通过mongoDB日志找出查询超时原因，构造索

1. 业务报错

2. 从业务对应的工程日志看，抛大量连mongDB大量超时异常

3. 通过mongoDB日志找出查询超时原因，构造索引添加语句

$ tail -f  /path/to/mongod.log

2022-12-15T10:40:12.352+0800 I COMMAND  [conn18066] command testDB.testColl command: find { find: "testColl", filter: { pid: { $in: [ "XT-382dc20e-551c-4a31-83e2-7cb939e6dc9f", "XT-ae9f63cd-c6ff-49fc-860c-872a841a1e06", "XT-10747273-edcf-4344-828e-55b5ce5c9456", "XT-590bdc95-5b65-4478-850c-a5674d3fcdd0", "XT-c0a10a00-4825-4b90-8c4d-568d4c390f59", "XT-c467dcf8-447d-4eda-a385-67645a97f6db", "XT-f2ae59e2-3fda-499c-9427-5239321d1e3a", "XT-4b229ce9-b67d-43de-ac93-106da5ad7af7", "XT-8175d1c7-8ef6-4387-81e1-fdfd5f6e1b8d", "XT-4aadf9c3-ed9f-424b-99e8-792f3ebf85ff", "XT-b09d1639-a6d5-4bf8-996f-5a3b98d282d1" ] }, status: 1, msgType: 6, toOidSpaceIds.oid: "cc457697-76b1-11e7-bcae-005056a350a6" }, sort: { _id: -1 }, projection: { toOidSpaceIds: 0, toOids: 0, toSpaceOids: 0 }, limit: 10 } planSummary: IXSCAN { pid: 1, toOidSpaceIds.oid: 1 } keysExamined:18396 docsExamined:18378 hasSortStage:1 cursorExhausted:1 numYields:157 nreturned:3 reslen:5602 locks:{ Global: { acquireCount: { r: 316 } }, Database: { acquireCount: { r: 158 } }, Collection: { acquireCount: { r: 158 } } } protocol:op_query 669ms



2022-12-15T10:40:12.413+0800 I COMMAND  [conn18038] command testDB.testColl command: find { find: "testColl", filter: { pid: { $in: [ "XT-382dc20e-551c-4a31-83e2-7cb939e6dc9f", "XT-ae9f63cd-c6ff-49fc-860c-872a841a1e06", "XT-10747273-edcf-4344-828e-55b5ce5c9456", "XT-590bdc95-5b65-4478-850c-a5674d3fcdd0", "XT-c0a10a00-4825-4b90-8c4d-568d4c390f59", "XT-c467dcf8-447d-4eda-a385-67645a97f6db", "XT-f2ae59e2-3fda-499c-9427-5239321d1e3a", "XT-4b229ce9-b67d-43de-ac93-106da5ad7af7", "XT-8175d1c7-8ef6-4387-81e1-fdfd5f6e1b8d", "XT-4aadf9c3-ed9f-424b-99e8-792f3ebf85ff", "XT-b09d1639-a6d5-4bf8-996f-5a3b98d282d1" ] }, status: 1, msgType: 6, toOidSpaceIds.oid: "1adadf3b-7787-11e7-bcae-005056a350a6" }, sort: { _id: -1 }, projection: { toOidSpaceIds: 0, toOids: 0, toSpaceOids: 0 }, limit: 10 } planSummary: IXSCAN { pid: 1, toOidSpaceIds.oid: 1 } keysExamined:55306 docsExamined:55288 hasSortStage:1 cursorExhausted:1 numYields:540 nreturned:3 reslen:5602 locks:{ Global: { acquireCount: { r: 1082 }, acquireWaitCount: { r: 168 }, timeAcquiringMicros: { r: 1514346 } }, Database: { acquireCount: { r: 541 } }, Collection: { acquireCount: { r: 541 } } } protocol:op_query 6237ms

可以看出问题库表：testDB.testColl

4. 登陆mongoDB,查看表字段

> use  testDB
> db.testColl.find().pretty()


{
    "_id" : ObjectId("5eb4d410d0ef6a6d66bb97cf"),
    "pid" : "XT-8175d1c7-8ef6-4387-81e1-fdfd5f6e1b8d",
    "fromServer" : "10.19.42.141",
    "msgId" : "XT-5eb4d411d08e812ff546fb4b",
    "msgType" : 2,
    "content" : "您好， 账号：test 将于2020-05-13到期，届时账号将不能使用，如需继续使用，请及时和账号负责人联系！",
    "sendTime" : ISODate("2020-05-08T03:37:53.078Z"),
    "toUserType" : 0,
    "toOidSpaceIds" : [
        {
            "oid" : "bea5be42-76af-11e7-bcae-005056a350a6",
            "spaceIds" : [
                "5da57db1d0ef6a8758734683"
            ]
        }
    ],
    "status" : 1,
    "createTime" : ISODate("2020-05-08T03:37:52.281Z"),
    "updateTime" : ISODate("2020-05-08T03:37:52.281Z")
}

5. 从mongoDB日志中过滤条件中找出相关字段，构建组合索引

filter: { pid: { $in: [ "XT-382dc20e-551c-4a31-83e2-7cb939e6dc9f", "XT-ae9f63cd-c6ff-49fc-860c-872a841a1e06", "XT-10747273-edcf-4344-828e-55b5ce5c9456", "XT-590bdc95-5b65-4478-850c-a5674d3fcdd0", "XT-c0a10a00-4825-4b90-8c4d-568d4c390f59", "XT-c467dcf8-447d-4eda-a385-67645a97f6db", "XT-f2ae59e2-3fda-499c-9427-5239321d1e3a", "XT-4b229ce9-b67d-43de-ac93-106da5ad7af7", "XT-8175d1c7-8ef6-4387-81e1-fdfd5f6e1b8d", "XT-4aadf9c3-ed9f-424b-99e8-792f3ebf85ff", "XT-b09d1639-a6d5-4bf8-996f-5a3b98d282d1" ] }, status: 1, msgType: 6, toOidSpaceIds.oid: "1adadf3b-7787-11e7-bcae-005056a350a6" }

字段：
pid
status
msgType
toOidSpaceIds（不建议到toOidSpaceIds.oid，不然是不是还要搞个 toOidSpaceIds.spaceIds）

6. 在线后台添加索引

> use  testDB
> db.testColl.ensureIndex({"pid" : 1,"status" : 1,"msgType" : 1, "toOidSpaceIds": 1},{"background" : true})

{
    "createdCollectionAutomatically" : false,
    "numIndexesBefore" : 2,
    "numIndexesAfter" : 3,
    "ok" : 1
}

7. 通过mongoDB日志查看索引创建进度

$ tail  -f   /path/to/mongod.log

2022-12-20T11:52:38.002+0800 I -        [conn527989]   Index Build (background): 270845100/271889283 99%
2022-12-20T11:52:41.002+0800 I -        [conn527989]   Index Build (background): 271016400/271889283 99%
2022-12-20T11:52:44.005+0800 I -        [conn527989]   Index Build (background): 271193800/271889283 99%
2022-12-20T11:52:47.002+0800 I -        [conn527989]   Index Build (background): 271355200/271889283 99%
2022-12-20T11:52:50.002+0800 I -        [conn527989]   Index Build (background): 271513700/271889283 99%
2022-12-20T11:52:53.002+0800 I -        [conn527989]   Index Build (background): 271673400/271889283 99%
2022-12-20T11:52:56.002+0800 I -        [conn527989]   Index Build (background): 271843700/271889283 99%
2022-12-20T11:52:56.770+0800 I INDEX    [conn527989] build index done.  scanned 271890178 total records. 4875 secs