oracle分页查询优化_Mongodb分页查询优化上

【背景】

       最近遇到mongo集群性能问题,主要体现在查询性能或者聚合性能慢(查询类似关系型数据库中select * from xx where a&＃61;&＃39;xx&＃39;,另外聚合类似group by&＃43;count、sum),nosql与关系型数据库存在很多类似&＃xff0c;比如分页查询语句是比较常见问题,分页优化在数据库优化原理类似.常见分页场景需求(本次主要基于这2种场景进行优化介绍)

    1、取top N这种小结果集&＃xff0c;想办法利用索引有序特性尽快返回结果集.

db.collection.find({query}).sort({name:1}).limit(50)

    2、分页翻页&＃xff0c;尤其是结果集特别多越往后翻页越慢db.collection.find({query}).sort({name:1}).skip(N).limit(50),这里N越大&＃xff0c;性能会越低.

【分页top N案例以及优化思路】

       1、具体SQL逻辑:根据网点查询当天的签收明细并返回第一页2000条&＃xff0c;所有sql都是查询当天签收&＃xff0c;当天从00:00:00-23:59:59&＃xff0c;查询时间越接近23:59:59&＃xff0c;满足结果集的数据越多&＃xff0c;直到数据没有变化.后面还有翻页的功能&＃xff0c;暂时先不讨论.其中sort是根据单号来&＃xff0c;所有单号都唯一的.signStatus只有0,1.

  db.test.find({org:"10000",signT:{$gte:new Date(1590940800000), $lte: new Date(1591027199999) }, signStatus: { $in: [ 0, 1 ] } }).sort({no:1}).limit(2000);

        2、慢日志中分析不同不同索引对应效率

通过mtools分析慢日志&＃xff0c;平均执行时间300ms.

        分析一个慢日志情况&＃xff1a;

        排序顺序与索引顺序一致则无需排序,执行时间是1084ms:索引【org_1_no_1_signT_1】

        排序顺序与索引顺序不一致则需排序,执行时间是156ms:  索引【org_1_signT_1】

    【org_1_no_1_signT_1】索引执行效率&＃xff1a;

    备注:返回6000&＃xff0c;因为存在3个分片&＃xff0c;需要mongos进一步过滤

     "executionStats": {

         "nReturned": 6000,

         "executionTimeMillis": 1084,

         "totalKeysExamined": 168130,

         "totalDocsExamined": 6000

 【org_1_signT_1】索引执行效率

     "executionStats": {

         "nReturned": 6000,

         "executionTimeMillis": 156,

         "totalKeysExamined": 43744,

         "totalDocsExamined": 43744

       总结&＃xff1a;1、排序与回表效率问题;--针对当前小结果集下&＃xff0c;ER索引效率要明显高于ESR索引效率.

                 第一个索引满足ESR理论,通过索引没有返回多余的行数&＃xff0c;每个节点2000行,但是从16万索引key中过滤满足条件6000&＃xff0c;解决排序问题&＃xff0c;无排序回表少&＃xff0c;索引是检索效率低且执行时间长

                第二个索引不满足ESR理论&＃xff0c;只能满足ER理论&＃xff0c;索引key与回表结果集一致&＃xff0c;回表过滤到37744条.有排序回表多&＃xff0c;索引效率高.执行时间短.   

             2、如果结果集呈现N倍数据级增长&＃xff0c;比如百万级别&＃xff0c;那么ER索引效率肯定低于ESR索引效率,虽然说ESR理论下最佳&＃xff0c;但本次SQL写法ESR效率不高.

       3、了解业务需求以及设计原因

    db.test.find({org:"10000",signT:{$gte:new Date(1590940800000), $lte: new Date(1591027199999) }, signStatus: { $in: [ 0, 1 ] } }).sort({no:1}).limit(2000);

          signT时间基本上都是一个时间点&＃xff0c;存在少量不一样时间&＃xff0c;所以说排序字段不能signT.所以采用no单号&＃xff0c;后续沟通集合中存在一个staDate字段&＃xff0c;同一天日期完全一致&＃xff0c;后续将代码中signT使用staDate来替代&＃xff0c;并修改索引为ES索引&＃xff0c;完美解决排序与回表问题.

          创建索引:mongodb 4.2版本开始&＃xff0c;background:1可以不用加&＃xff0c;类似oracle或者mysql online ddl,只是在创建索引与结束加锁.4.2版本之前&＃xff0c;后台创建索引比较慢,前台创建是db级别排他锁&＃xff0c;导致整个db无法访问.谨慎操作。

          db.test.createIndex({org:1,staDate:1,no:1},{background:1})

      4、最终修业务SQL如下

     db.test.find({org:"10000",staDate: new Date(1591027199999) }, signStatus: { $in: [ 0, 1 ] } }).sort({no:1}).limit(2000);

   【org_1_no:1_staDate_1】索引执行效率

"executionStats" : {

"executionSuccess" : true,

"nReturned" : 6000,

"executionTimeMillis" : 10,

"totalKeysExamined" : 6000,

"totalDocsExamined" : 6000,

【分页top N优化总结】

     1、性能提升 

       通过修改业务SQL逻辑&＃xff0c;top 2000执行基本几十毫秒&＃xff0c;相比之前最低都要100ms&＃xff0c;最大要几秒&＃xff0c;性能提升几倍到几十倍&＃xff0c;如果数据量提升几个数理级别&＃xff0c;提升至少100倍.

     2、不管ESR最佳实践还是ES或者SR等相关索引规则&＃xff0c;都是结合实际SQL以及结果集大小来具体问题具体分析&＃xff0c;本案例中没有修改业务代码前ER比ESR效果好&＃xff0c;即使ER有排序&＃xff0c;这些都是建立结果集小的情况下&＃xff0c;如果结果集很大&＃xff0c;不管ESR还是ER都存在缺点&＃xff0c;集合或者索引变成热点问题。