全部产品

云数据库查询优化

小程序Serverless云数据库使用的是Mongo。在数据量比较大的情况下,有时候查询操作会报错operation exceeded time limit。本文介绍如何进行查询优化以避免此类问题。

设置合适的索引

如果您的查询操作包含了过滤条件(包含等值测试和范围过滤)或者是排序功能,则要考虑给集合添加索引。

说明

在创建索引时,可以去掉那些没有选择性的等值测试字段或范围过滤字段,以减少索引空间的占用。

针对复杂的查询语句,包含了等值测试、范围过滤以及排序功能,这里提供一个建立索引的方法。

    • 索引中字段的顺序需要按照等值测试字段、排序字段、范围过滤字段的方式排列。

    • 若等值测试包含多个字段,它们之间的顺序可以任意互换,索引中的升序降序也不影响。

    • 若排序字段包含多个字段,添加到索引中顺序需要按照排序中的顺序,升序降序也要一致。

    • 若范围过滤包含多个字段,则优先将基数值(集合中字段不同值的数量)少的字段放到前面。

说明

如果您的集合上有多条索引,尤其是您的查询语句较复杂时,Mongo不一定能为您选择正确的索引,建议您在查询语句中指定此次查询过程中使用的索引。

如下所示,您可以创建一个复合索引,依次包含course,sex, class, name, birthmonth, score。course和sex属于等值测试字段,它们需要放在最前面,顺序也可以互换。birthmonth和score属于范围过滤字段,需要放到最后,由于birthmonth取值范围是1-12,score的取值范围是0-100,所以需要把birthmonth放到score前面。class和name属于排序字段,应该放在中间,顺序和升降序都和排序时一致。

//在分数表中,查询数学成绩大于80分并且下半年出生的男生的记录,并且按照班级和姓名排序。
mpserverless.db.collection('score').find(
  {
    course: "Math",
    sex: "male",
    birthmonth: { $gt: 6 },
    score: { $gt: 80 },
  },
  {
    sort: { class: 1, name: 1 },
  },
);

大量数据查询优化

如果您的数据量非常大,在设置合适的索引之后仍然会查询超时,您要考虑以下优化方案。

  1. 尽量避免使用skip,至少不应该skip比较大的值,因为skip操作Mongo服务端依然会扫描被skip的数据,带skip操作的耗时和skip的数量线性相关。您可以考虑使用排序和范围查询功能来替代直接使用skip。

  2. 对于非常大的数据可以分段来查询,即通过一定的条件将一次查询拆分为多次查询操作。

分段计算count

您可以通过findOne+RangeQuery+Skip+Sort的方式来分段计算Count,注意使用该方法需要添加合适的索引。

如下面的代码示例所示,以_id作为分段列,我们先调用findOne接口,依次查询第100001,200001,300001....个_id值,当剩余数据条数不足100001条时,findOne返回的result为空,这时可以通过count获取剩余的记录数。注意在调用findOne时需要指定查询条件_id大于等于特定值,需要指定排序规则为按_id升序排列,需要指定查询跳过100000条记录;在调用count查询时需要指定_id值大于最后一次findOne返回的_id。

module.exports = async (ctx) => {
    const skip = 100000;
    let count = 0;
    let minId = { $minKey: 1 };
    const collection = ctx.mpserverless.db.collection('collectionName');
    while (true) {
        const query = { _id: { $gte: minId } };
        const options = {
            skip,
            projection: { _id: 1 },
            sort: { _id: 1 },
        };
        const findOneResult = await collection.findOne(query, options);
        const { affectedDocs, success } = findOneResult;
        if (!success) {
            throw new Error("findOne return success false");
        }
        if (affectedDocs > 0) {
            const newId = findOneResult.result._id;
            minId = newId;
            count += skip;
        } else {
            break;
        }
    }
    const query = { _id: { $gte: minId } };
    const countResult = await collection.count(query);
    const { affectedDocs, success } = countResult;
    if (!success) {
        throw new Error("count return success false");
    }
    count += affectedDocs;
    return count;
};

遍历整个集合

您应该避免使用find+Skip+Limit的查询方式来遍历整个集合,因为这种方式随着Skip数量的增长响应时间会越来越慢,还可能会造成请求超时。您可以改为使用find+RangeQuery+Limit+Sort的方式。

下面的代码给出了一个示例。将数据按_id排列,每次查询时都指定查询条件大于上次查询结果中的最后一条记录的_id,依次遍历到最后。

module.exports = async (ctx) => {
    const pageSize = 100;
    let minId = { $minKey: 1 };
    const collection = ctx.mpserverless.db.collection('collectionName');
    while (true) {
        const query = { _id: { $gt: minId } };
        const options = {
            limit: pageSize,
            sort: { _id: 1 },
        };
        const findResult = await collection.find(query, options);
        const { affectedDocs, success } = findResult;
        if (!success) {
            throw new Error("find return success false");
        }
        if (affectedDocs > 0) {
            const data = findResult.data;
            const lastDoc = data[data.length -1];
            minId = lastDoc._id;
            //todo 在这里添加处理data列表的逻辑。
        } else {
            break;
        }
    }
    return 'success';
};

分页查询优化

您应该使用上一页、下一页、上n页(其中n是一个比较小的数字)、下n页的翻页功能来替换随机翻页。您可以参考百度或者谷歌的搜索结果的分页功能,当结果页数非常多时,不展示共有多少页,仅支持在前10页中支持随机翻页;再往下翻页的过程中,不再支持随机翻页,仅支持向下翻一个较小的页数,这样就可以在已经查询出结果的基础上再使用find+RangeQuery+Skip(少量)+Limit+Sort的方式来快速查询到结果。例如当您已经查询出第13页的数据后,再次查询第17页的数据时,就可以把第13页的最后一条数据作为RangeQuery的判断条件,跳过3页的数据行数,再查询一页的数据就是第17页的数据。