记一次优化数据库查询的实施方案

现状描述

当前系统存在一个用于概览的接口,该接口在一次请求中对数据库执行了多次聚合(aggregate)操作.由于每次请求的查询量较大,性能存在瓶颈,导致响应速度缓慢,影响用户体验.

解决方案:

拆分查询,优化前端交互
- 将原本在单个请求中完成的多次聚合查询拆解为多个独立请求.
- 前端通过并发请求、懒加载或异步数据获取的方式,逐步获取数据,减少单次请求的压力.
- 这种方法降低了单个接口的复杂度,提升了整体的响应速度.
排查索引使用情况
- 检查 MongoDB 聚合管道中 $match 阶段的索引是否生效.
- 确保查询条件中使用了高效的索引字段,避免全表扫描.
- 利用 MongoDB 的 explain() 命令分析查询执行计划,优化索引覆盖范围.
提高数据复用,避免重复查询
- 在多次查询中,提炼公共的筛选条件、数据集或计算逻辑,避免重复执行相同查询.
- 引入 Redis 缓存,将频繁访问的数据临时存储,减少数据库压力.
- 将不常变动的数据(如统计结果)设置缓存过期时间,兼顾数据的时效性与性能.
重构代码,提取可复用逻辑
- 将聚合查询中的筛选条件、时间范围、数据转换等逻辑提炼成中间件.
- 利用 Express 中间件机制,在多个路由中共享查询条件,减少重复代码.
- 提取公共查询条件至 filter/securityFilter.ts,提高代码的可维护性和复用性.

实施过程

1. 检查数据库查询性能

2. 补充中间件

// src/app/middlewares/validateQuery.ts
import { Request, Response, NextFunction } from "express";

/**
 * 验证查询参数 - 检查 `startTime` 是否存在
 * @param {string[]} requiredParams - 必传参数列表
 */
export const validateQuery = (requiredParams: string[]) => {
  return (req: Request, res: Response, next: NextFunction) => {
    const missingParams = requiredParams.filter((param) => !req.query[param]);

    if (missingParams.length > 0) {
      res.status(400).json({
        success: false,
        message: `缺少参数: ${missingParams.join(", ")}`,
      });
      return;
    }

    next();
  };
};

// 使用
router.get(
  "/statusOverview",
  auth.isAdmin,
  validateQuery(["startTime"]),
  statusOverview.projectList
);

3. 提取filter

// src/app/helpers/filters.ts
import { RootFilterQuery } from "mongoose";
import { IProject } from "../modules/models/Project";
import _ from "lodash";

/**
 * 构建可复用查询条件
 *
 * 提高查询逻辑的复用性.支持动态构造时间、组织、项目等条件.
 *
 * @param {Object} params - 查询参数
 * @param {number} params.startTime - 起始时间戳 (必传)
 * @param {number} [params.endTime] - 结束时间戳 (可选)
 * @param {string[] | string} [params.orgs] - 组织 ID 列表或单个 ID (可选)
 * @param {string[] | undefined} [params.projects] - 项目 ID 列表 (可选)
 *
 * @returns {Object} 返回包含以下条件的查询对象：
 *   - `filter`: 基础查询条件 (包含时间、组织、项目筛选)
 *   - `filterIncludeNullProject`: 包含 `project` 字段为空的数据
 *   - `filterEventIncludeNullProject`: 包含 `event` 字段为空的数据
 *
 * @example
 * const { filter, filterIncludeNullProject, filterEventIncludeNullProject } =
 *   buildstatusOverviewFilter({
 *     startTime: 1700000000,
 *     endTime: 1700100000,
 *     orgs: ['org1', 'org2'],
 *     projects: ['proj1', 'proj2']
 *   });
 *
 * Project.aggregate([
 *   { $match: filter },
 *   { $group: { _id: "$project", count: { $sum: 1 } } }
 * ]);
 */
export const buildProjectOverviewFilter = ({
  startTime,
  endTime,
  orgs,
  projects,
}: {
  startTime: number;
  endTime?: number;
  orgs?: string[] | string;
  projects?: string[] | undefined;
}) => {
  const filter: RootFilterQuery<IProject> = {
    hasWhitelist: false,
    project: { $exists: true },
  };

  if (startTime !== undefined) {
    const startDate = new Date(+startTime);
    if (endTime !== undefined) {
      filter.endTime = {
        $gte: startDate,
        $lte: new Date(+endTime),
      };
    } else {
      filter.endTime = { $gte: startDate };
    }
  }

  const filterIncludeNullProject: RootFilterQuery<IProject> = _.omit(
    filter,
    "project"
  );

  if (projects) {
    filter.project = { $in: projects };
    filterIncludeNullProject.project = { $in: projects };
    filterEventIncludeNullProject.project = { $in: projects };
  }
  return { filter, filterIncludeNullProject, filterEventIncludeNullProject };
};

最终解决方案

经测试, 由于所需字段没做到索引覆盖, 所以数据查询本地聚合操作甚至会更加耗时, 最终通过创建中间表, 定时对历史数据聚合后写入中间表, 接口直接查询中间表, 优化前普通查询(3s-40s), 优化后500ms以内