mongo mysql 条件查询效率_mongodb查询条件对查询效率的影响
背景和概述
數(shù)據(jù)狀況：
mongodb的一個(gè)db中存了近2000萬記錄，有type(字符串)、ctime(float，創(chuàng)建時(shí)間)等索引參數(shù)，記錄的自然順序和ctime的排序是一致的。
查詢需求：
從第47萬開始查詢并返回結(jié)果，每批次返回100條；為后續(xù)業(yè)務(wù)邏輯需求，在執(zhí)行batch_size(100)前會(huì)先執(zhí)行一個(gè)count()。
探索過程：
之前只采用type作為查詢條件并用skip參數(shù)跳過前47萬記錄，感覺有些慢(約1分鐘)，于是想通過type、ctime兩個(gè)查詢條件并棄用skip參數(shù)，結(jié)果大跌眼鏡，查詢耗時(shí)猛增到了7分鐘；仔細(xì)看日志，發(fā)現(xiàn)時(shí)間都耗在了count函數(shù)，就batch_size函數(shù)的執(zhí)行時(shí)間來說，第二個(gè)查詢是更快的，猜測(cè)前一方案batch_size查詢慢是因?yàn)閟kip參數(shù)，于是測(cè)試type條件+skip參數(shù)分別為0、10w、20w、30w、40w的情況，發(fā)現(xiàn)耗時(shí)逐步顯著增長(zhǎng)，也即skip參數(shù)對(duì)速度的影響也是顯著的，skip值與耗時(shí)成正相關(guān)。
結(jié)論：
想提高查詢效率，若用了count就要減少查詢條件，最好繞過count函數(shù)相關(guān)需求，然后增加查詢條件并棄用skip參數(shù)。
主要查詢?nèi)罩镜膶?duì)比
一個(gè)查詢條件，帶skip參數(shù)，耗時(shí)1分鐘出頭 count()用時(shí)10746ms batch_size(100)用時(shí)59983ms
x = bookscache.find({"type": "s"}, {"_id": 0, "type": 0}, skip = skip)
2017-02-20T11:30:48.600+0800 I NETWORK [thread1] connection accepted from 127.0.0.1:1361 #61 (1 connection now open)
2017-02-20T11:30:48.606+0800 I NETWORK [conn61] received client metadata from 127.0.0.1:1361 conn61: { driver: { name: "PyMongo", version: "3.4.0" }, os: { type: "Windows", name: "Windows 10", architecture: "AMD64", version: "10.0.14393-" }, platform: "CPython 2.7.12.final.0" }
2017-02-20T11:30:48.617+0800 I NETWORK [thread1] connection accepted from 127.0.0.1:1365 #62 (2 connections now open)
2017-02-20T11:30:48.619+0800 I NETWORK [conn62] received client metadata from 127.0.0.1:1365 conn62: { driver: { name: "PyMongo", version: "3.4.0" }, os: { type: "Windows", name: "Windows 10", architecture: "AMD64", version: "10.0.14393-" }, platform: "CPython 2.7.12.final.0" }
2017-02-20T11:30:59.366+0800 I COMMAND [conn62] command cache.bookscache command: count { count: "bookscache", query: { type: "s" } } planSummary: COUNT_SCAN { type: 1 } keysExamined:17090106 docsExamined:0 numYields:133793 reslen:44 locks:{ Global: { acquireCount: { r: 267588 } }, Database: { acquireCount: { r: 133794 } }, Collection: { acquireCount: { r: 133794 } } } protocol:op_query 10746ms
2017-02-20T11:31:59.352+0800 I COMMAND [conn62] command cache.bookscache command: find { find: "bookscache", filter: { type: "s" }, projection: { _id: 0, type: 0 }, skip: 487951, batchSize: 100 } planSummary: IXSCAN { type: 1 } cursorid:20810842870 keysExamined:488051 docsExamined:488051 numYields:5868 nreturned:100 reslen:396171 locks:{ Global: { acquireCount: { r: 11738 } }, Database: { acquireCount: { r: 5869 } }, Collection: { acquireCount: { r: 5869 } } } protocol:op_query 59983ms
2017-02-20T11:32:26.792+0800 I - [conn61] end connection 127.0.0.1:1361 (2 connections now open)
2017-02-20T11:32:26.792+0800 I - [conn62] end connection 127.0.0.1:1365 (2 connections now open)
兩個(gè)查詢條件，無skip參數(shù)，耗時(shí)近7分鐘 count()用時(shí)409125ms batch_size(100)用時(shí)280ms
x = bookscache.find({"type": "s", "ctime": {"$gte": minCtime}}, {"_id": 0, "type": 0})
2017-02-20T11:47:09.422+0800 I NETWORK [thread1] connection accepted from 127.0.0.1:12310 #69 (1 connection now open)
2017-02-20T11:47:09.424+0800 I NETWORK [conn69] received client metadata from 127.0.0.1:12310 conn69: { driver: { name: "PyMongo", version: "3.4.0" }, os: { type: "Windows", name: "Windows 10", architecture: "AMD64", version: "10.0.14393-" }, platform: "CPython 2.7.12.final.0" }
2017-02-20T11:47:09.431+0800 I NETWORK [thread1] connection accepted from 127.0.0.1:12314 #70 (2 connections now open)
2017-02-20T11:47:09.431+0800 I NETWORK [conn70] received client metadata from 127.0.0.1:12314 conn70: { driver: { name: "PyMongo", version: "3.4.0" }, os: { type: "Windows", name: "Windows 10", architecture: "AMD64", version: "10.0.14393-" }, platform: "CPython 2.7.12.final.0" }
2017-02-20T11:53:58.561+0800 I COMMAND [conn70] command cache.bookscache command: count { count: "bookscache", query: { type: "s", ctime: { $gte: 1483859529.57 } } } planSummary: IXSCAN { ctime: 1 } keysExamined:19366965 docsExamined:19366965 fromMultiPlanner:1 numYields:164853 reslen:44 locks:{ Global: { acquireCount: { r: 329708 } }, Database: { acquireCount: { r: 164854 } }, Collection: { acquireCount: { r: 164854 } } } protocol:op_query 409125ms
2017-02-20T11:53:58.849+0800 I COMMAND [conn70] command cache.bookscache command: find { find: "bookscache", filter: { type: "s", ctime: { $gte: 1483859529.57 } }, projection: { _id: 0, type: 0 }, batchSize: 100 } planSummary: IXSCAN { ctime: 1 } cursorid:17875490189 keysExamined:582 docsExamined:582 fromMultiPlanner:1 numYields:19 nreturned:100 reslen:427036 locks:{ Global: { acquireCount: { r: 40 } }, Database: { acquireCount: { r: 20 } }, Collection: { acquireCount: { r: 20 } } } protocol:op_query 280ms
2017-02-20T12:06:07.802+0800 I COMMAND [conn70] command cache.bookscache command: getMore { getMore: 17875490189, collection: "bookscache", batchSize: 100 } originatingCommand: { find: "bookscache", filter: { type: "s", ctime: { $gte: 1483859529.57 } }, projection: { _id: 0, type: 0 }, batchSize: 100 } planSummary: IXSCAN { ctime: 1 } cursorid:17875490189 keysExamined:1830 docsExamined:1830 fromMultiPlanner:1 numYields:22 nreturned:100 reslen:457999 locks:{ Global: { acquireCount: { r: 46 } }, Database: { acquireCount: { r: 23 } }, Collection: { acquireCount: { r: 23 } } } protocol:op_query 134ms
2017-02-20T12:06:36.267+0800 I - [conn69] end connection 127.0.0.1:12310 (2 connections now open)
2017-02-20T12:06:36.267+0800 I - [conn70] end connection 127.0.0.1:12314 (2 connections now open)
更多測(cè)試和查詢?nèi)罩?/p>
skip0 用時(shí)8s(batch_size未執(zhí)行) count()用時(shí)7683ms batch_size(100)用時(shí)因中止進(jìn)程而缺失
2017-02-20T12:20:04.722+0800 I NETWORK [thread1] connection accepted from 127.0.0.1:8250 #73 (1 connection now open)
2017-02-20T12:20:04.725+0800 I NETWORK [conn73] received client metadata from 127.0.0.1:8250 conn73: { driver: { name: "PyMongo", version: "3.4.0" }, os: { type: "Windows", name: "Windows 10", architecture: "AMD64", version: "10.0.14393-" }, platform: "CPython 2.7.12.final.0" }
2017-02-20T12:20:04.731+0800 I NETWORK [thread1] connection accepted from 127.0.0.1:8254 #74 (2 connections now open)
2017-02-20T12:20:04.732+0800 I NETWORK [conn74] received client metadata from 127.0.0.1:8254 conn74: { driver: { name: "PyMongo", version: "3.4.0" }, os: { type: "Windows", name: "Windows 10", architecture: "AMD64", version: "10.0.14393-" }, platform: "CPython 2.7.12.final.0" }
2017-02-20T12:20:12.417+0800 I COMMAND [conn74] command cache.bookscache command: count { count: "bookscache", query: { type: "s" } } planSummary: COUNT_SCAN { type: 1 } keysExamined:17090106 docsExamined:0 numYields:133720 reslen:44 locks:{ Global: { acquireCount: { r: 267442 } }, Database: { acquireCount: { r: 133721 } }, Collection: { acquireCount: { r: 133721 } } } protocol:op_query 7683ms
2017-02-20T12:20:13.780+0800 I - [conn73] end connection 127.0.0.1:8250 (2 connections now open)
2017-02-20T12:20:13.780+0800 I - [conn74] end connection 127.0.0.1:8254 (2 connections now open)
skip10w 用時(shí)27s count()用時(shí)7563ms batch_size(100)用時(shí)19682ms
2017-02-20T12:20:30.563+0800 I NETWORK [thread1] connection accepted from 127.0.0.1:8964 #75 (1 connection now open)
2017-02-20T12:20:30.565+0800 I NETWORK [conn75] received client metadata from 127.0.0.1:8964 conn75: { driver: { name: "PyMongo", version: "3.4.0" }, os: { type: "Windows", name: "Windows 10", architecture: "AMD64", version: "10.0.14393-" }, platform: "CPython 2.7.12.final.0" }
2017-02-20T12:20:30.571+0800 I NETWORK [thread1] connection accepted from 127.0.0.1:8966 #76 (2 connections now open)
2017-02-20T12:20:30.571+0800 I NETWORK [conn76] received client metadata from 127.0.0.1:8966 conn76: { driver: { name: "PyMongo", version: "3.4.0" }, os: { type: "Windows", name: "Windows 10", architecture: "AMD64", version: "10.0.14393-" }, platform: "CPython 2.7.12.final.0" }
2017-02-20T12:20:38.136+0800 I COMMAND [conn76] command cache.bookscache command: count { count: "bookscache", query: { type: "s" } } planSummary: COUNT_SCAN { type: 1 } keysExamined:17090106 docsExamined:0 numYields:133734 reslen:44 locks:{ Global: { acquireCount: { r: 267470 } }, Database: { acquireCount: { r: 133735 } }, Collection: { acquireCount: { r: 133735 } } } protocol:op_query 7563ms
2017-02-20T12:20:57.820+0800 I COMMAND [conn76] command cache.bookscache command: find { find: "bookscache", filter: { type: "s" }, projection: { _id: 0, type: 0 }, skip: 100000, batchSize: 100 } planSummary: IXSCAN { type: 1 } cursorid:19274394564 keysExamined:100100 docsExamined:100100 numYields:1475 nreturned:100 reslen:909397 locks:{ Global: { acquireCount: { r: 2952 } }, Database: { acquireCount: { r: 1476 } }, Collection: { acquireCount: { r: 1476 } } } protocol:op_query 19682ms
2017-02-20T12:20:59.124+0800 I - [conn76] end connection 127.0.0.1:8966 (2 connections now open)
2017-02-20T12:20:59.124+0800 I - [conn75] end connection 127.0.0.1:8964 (2 connections now open)
skip20w 用時(shí)41s count()用時(shí)8020ms batch_size(100)用時(shí)33298ms
2017-02-20T12:21:25.084+0800 I NETWORK [thread1] connection accepted from 127.0.0.1:10346 #77 (1 connection now open)
2017-02-20T12:21:25.085+0800 I NETWORK [conn77] received client metadata from 127.0.0.1:10346 conn77: { driver: { name: "PyMongo", version: "3.4.0" }, os: { type: "Windows", name: "Windows 10", architecture: "AMD64", version: "10.0.14393-" }, platform: "CPython 2.7.12.final.0" }
2017-02-20T12:21:25.097+0800 I NETWORK [thread1] connection accepted from 127.0.0.1:10348 #78 (2 connections now open)
2017-02-20T12:21:25.098+0800 I NETWORK [conn78] received client metadata from 127.0.0.1:10348 conn78: { driver: { name: "PyMongo", version: "3.4.0" }, os: { type: "Windows", name: "Windows 10", architecture: "AMD64", version: "10.0.14393-" }, platform: "CPython 2.7.12.final.0" }
2017-02-20T12:21:33.119+0800 I COMMAND [conn78] command cache.bookscache command: count { count: "bookscache", query: { type: "s" } } planSummary: COUNT_SCAN { type: 1 } keysExamined:17090106 docsExamined:0 numYields:133726 reslen:44 locks:{ Global: { acquireCount: { r: 267454 } }, Database: { acquireCount: { r: 133727 } }, Collection: { acquireCount: { r: 133727 } } } protocol:op_query 8020ms
2017-02-20T12:22:06.419+0800 I COMMAND [conn78] command cache.bookscache command: find { find: "bookscache", filter: { type: "s" }, projection: { _id: 0, type: 0 }, skip: 200000, batchSize: 100 } planSummary: IXSCAN { type: 1 } cursorid:21072687025 keysExamined:200100 docsExamined:200100 numYields:2656 nreturned:100 reslen:487416 locks:{ Global: { acquireCount: { r: 5314 } }, Database: { acquireCount: { r: 2657 } }, Collection: { acquireCount: { r: 2657 } } } protocol:op_query 33298ms
2017-02-20T12:22:07.749+0800 I - [conn78] end connection 127.0.0.1:10348 (2 connections now open)
2017-02-20T12:22:07.749+0800 I - [conn77] end connection 127.0.0.1:10346 (2 connections now open)
skip30w 用時(shí)61s count()用時(shí)12879ms batch_size(100)用時(shí)47989ms
2017-02-20T12:22:12.500+0800 I NETWORK [thread1] connection accepted from 127.0.0.1:11645 #79 (1 connection now open)
2017-02-20T12:22:12.502+0800 I NETWORK [conn79] received client metadata from 127.0.0.1:11645 conn79: { driver: { name: "PyMongo", version: "3.4.0" }, os: { type: "Windows", name: "Windows 10", architecture: "AMD64", version: "10.0.14393-" }, platform: "CPython 2.7.12.final.0" }
2017-02-20T12:22:12.514+0800 I NETWORK [thread1] connection accepted from 127.0.0.1:11647 #80 (2 connections now open)
2017-02-20T12:22:12.517+0800 I NETWORK [conn80] received client metadata from 127.0.0.1:11647 conn80: { driver: { name: "PyMongo", version: "3.4.0" }, os: { type: "Windows", name: "Windows 10", architecture: "AMD64", version: "10.0.14393-" }, platform: "CPython 2.7.12.final.0" }
2017-02-20T12:22:25.397+0800 I COMMAND [conn80] command cache.bookscache command: count { count: "bookscache", query: { type: "s" } } planSummary: COUNT_SCAN { type: 1 } keysExamined:17090106 docsExamined:0 numYields:133837 reslen:44 locks:{ Global: { acquireCount: { r: 267676 } }, Database: { acquireCount: { r: 133838 } }, Collection: { acquireCount: { r: 133838 } } } protocol:op_query 12879ms
2017-02-20T12:23:13.388+0800 I COMMAND [conn80] command cache.bookscache command: find { find: "bookscache", filter: { type: "s" }, projection: { _id: 0, type: 0 }, skip: 300000, batchSize: 100 } planSummary: IXSCAN { type: 1 } cursorid:21139424984 keysExamined:300100 docsExamined:300100 numYields:3944 nreturned:100 reslen:623958 locks:{ Global: { acquireCount: { r: 7890 } }, Database: { acquireCount: { r: 3945 } }, Collection: { acquireCount: { r: 3945 } } } protocol:op_query 47989ms
2017-02-20T12:23:14.636+0800 I - [conn79] end connection 127.0.0.1:11645 (2 connections now open)
2017-02-20T12:23:14.636+0800 I - [conn80] end connection 127.0.0.1:11647 (2 connections now open)
skip40w 用時(shí)74s count()用時(shí)10389ms batch_size(100)用時(shí)63313ms
2017-02-20T12:23:46.867+0800 I NETWORK [thread1] connection accepted from 127.0.0.1:14010 #81 (1 connection now open)
2017-02-20T12:23:46.868+0800 I NETWORK [conn81] received client metadata from 127.0.0.1:14010 conn81: { driver: { name: "PyMongo", version: "3.4.0" }, os: { type: "Windows", name: "Windows 10", architecture: "AMD64", version: "10.0.14393-" }, platform: "CPython 2.7.12.final.0" }
2017-02-20T12:23:46.881+0800 I NETWORK [thread1] connection accepted from 127.0.0.1:14012 #82 (2 connections now open)
2017-02-20T12:23:46.882+0800 I NETWORK [conn82] received client metadata from 127.0.0.1:14012 conn82: { driver: { name: "PyMongo", version: "3.4.0" }, os: { type: "Windows", name: "Windows 10", architecture: "AMD64", version: "10.0.14393-" }, platform: "CPython 2.7.12.final.0" }
2017-02-20T12:23:57.272+0800 I COMMAND [conn82] command cache.bookscache command: count { count: "bookscache", query: { type: "s" } } planSummary: COUNT_SCAN { type: 1 } keysExamined:17090106 docsExamined:0 numYields:133781 reslen:44 locks:{ Global: { acquireCount: { r: 267564 } }, Database: { acquireCount: { r: 133782 } }, Collection: { acquireCount: { r: 133782 } } } protocol:op_query 10389ms
2017-02-20T12:25:00.588+0800 I COMMAND [conn82] command cache.bookscache command: find { find: "bookscache", filter: { type: "s" }, projection: { _id: 0, type: 0 }, skip: 400000, batchSize: 100 } planSummary: IXSCAN { type: 1 } cursorid:19982057947 keysExamined:400100 docsExamined:400100 numYields:5292 nreturned:100 reslen:460769 locks:{ Global: { acquireCount: { r: 10586 } }, Database: { acquireCount: { r: 5293 } }, Collection: { acquireCount: { r: 5293 } } } protocol:op_query 63313ms
2017-02-20T12:25:02.015+0800 I - [conn82] end connection 127.0.0.1:14012 (2 connections now open)
2017-02-20T12:25:02.015+0800 I - [conn81] end connection 127.0.0.1:14010 (2 connections now open)
總結(jié)
以上是生活随笔為你收集整理的mongo mysql 条件查询效率_mongodb查询条件对查询效率的影响的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： python xlrd xlwt综合_x
- 下一篇： sqoop同步hdfs与mysql端口_