SQL 优化记录

[yaogengzhu]

我写的

async function getTeamList(req, res) {
  const { user } = req;
  let { page, pageSize } = req.body;
  page = page || 1;
  pageSize = pageSize || 10;
  try {
    // 查询1: 团队总数
    const total = await knex('user')
      .count('* as total')
      .where('parent_id', req.user.id);
    // 查询2: 今日新增
    const today = await knex('user')
      .count('* as today')
      .where('parent_id', req.user.id)
      .whereRaw('DATE(created_at) = DATE(NOW())');
    // 查询3: 昨日新增
    const yesterday = await knex('user')
      .count('* as yesterday')
      .where('parent_id', req.user.id)
      .whereRaw('DATE(created_at) = DATE(NOW() - INTERVAL 1 DAY)');
    // 查询4: 本月新增
    const thisMonth = await knex('user')
      .count('* as this_month')
      .where('parent_id', req.user.id)
      .whereRaw(
        'MONTH(created_at) = MONTH(NOW()) AND YEAR(created_at) = YEAR(NOW())',
      );
    const teamInfo = {
      total: total[0].total,
      today: today[0].today,
      yesterday: yesterday[0].yesterday,
      thisMonth: thisMonth[0].this_month,
    };
    // 查询5: 团队列表
    const result = await knex('user')
      .select('*')
      .where('parent_id', user.id)
      .limit(pageSize)
      .offset((page - 1) * pageSize);
    if (result.length != 0) {
      for (let i = 0; i < result.length; i++) {
        // 查询7天内拉新
        const sevenDay = await knex('user')
          .count('* as count')
          .where('parent_id', result[i].id)
          .whereRaw('created_at > DATE_SUB(NOW(), INTERVAL 7 DAY)');
        result[i].sevenDay = sevenDay[0].count;
        // 七天出单
        const sevenDayOrder = await knex('sys_orders as so')
          .join('command_task as ct', 'ct.command_id', 'so.command_id')
          .count('* as count')
          .where('ct.user_id', result[i].id)
          .whereRaw('so.pay_time > DATE_SUB(NOW(), INTERVAL 7 DAY)');
        result[i].sevenDayOrder = sevenDayOrder[0].count;
      }
    }
    res.send({
      code: 0,
      message: 'ok',
      data: result,
      teamInfo,
    });
  } catch (e) {
    res.send({
      code: 1,
      message: '获取团队列表失败',
    });
    logger.info(e);
  }
}

优化的语句

async function getTeamList(req, res) {
  let { page, pageSize } = req.body;
  page = page || 1;
  pageSize = pageSize || 10;

  // 提取重复的查询条件
  const parentIdCondition = { parent_id: req.user.id };

  try {
    const [total, today, yesterday, thisMonth, result] = await Promise.all([
      // 查询1: 团队总数
      knex('user').count('* as total').where(parentIdCondition),

      // 查询2: 今日新增
      knex('user')
        .count('* as today')
        .where(parentIdCondition)
        .whereRaw('DATE(created_at) = DATE(NOW())'),

      // 查询3: 昨日新增
      knex('user')
        .count('* as yesterday')
        .where(parentIdCondition)
        .whereRaw('DATE(created_at) = DATE(NOW() - INTERVAL 1 DAY)'),

      // 查询4: 本月新增
      knex('user')
        .count('* as this_month')
        .where(parentIdCondition)
        .whereRaw(
          'MONTH(created_at) = MONTH(NOW()) AND YEAR(created_at) = YEAR(NOW())',
        ),

      // 查询5: 团队列表
      knex('user')
        .select('*')
        .where(parentIdCondition)
        .limit(pageSize)
        .offset((page - 1) * pageSize),
    ]);

    const teamInfo = {
      total: total[0].total,
      today: today[0].today,
      yesterday: yesterday[0].yesterday,
      thisMonth: thisMonth[0].this_month,
    };

    if (result.length !== 0) {
      const userIds = result.map((user) => user.id);
      // 查询7天内拉新和7天出单
      const [sevenDayCounts, sevenDayOrderCounts] = await Promise.all([
        knex('user')
          .select('parent_id')
          .count('* as count')
          .whereIn('parent_id', userIds)
          .whereRaw('created_at > DATE_SUB(NOW(), INTERVAL 7 DAY)')
          .groupBy('parent_id'),

        knex('sys_orders as so')
          .join('command_task as ct', 'ct.command_id', 'so.command_id')
          .select('ct.user_id')
          .count('* as count')
          .whereIn('ct.user_id', userIds)
          .whereRaw('so.pay_time > DATE_SUB(NOW(), INTERVAL 7 DAY)')
          .groupBy('ct.user_id'),
      ]);

      const sevenDayMap = new Map(
        sevenDayCounts.map((item) => [item.parent_id, item.count]),
      );
      const sevenDayOrderMap = new Map(
        sevenDayOrderCounts.map((item) => [item.user_id, item.count]),
      );

      result.forEach((user) => {
        user.sevenDay = sevenDayMap.get(user.id) || 0;
        user.sevenDayOrder = sevenDayOrderMap.get(user.id) || 0;
      });
    }

    res.send({
      code: 0,
      message: 'ok',
      data: result,
      teamInfo,
    });
  } catch (e) {
    res.send({
      code: 1,
      message: '获取团队列表失败',
    });
    logger.info(e);
  }
}

总结： 这里我们使用了 Promise.all 来并行执行多个查询，这样可以减少总的查询时间。同时，我们将查询7天内拉新和7天出单的操作合并为一个查询，并使用 Map 对象来存储结果，以便在后续的循环中快速查找。

注意：这里的优化主要是减少了查询次数，但实际上查询效率还是取决于数据库的性能和表的索引设置。如果查询仍然很慢，建议检查数据库性能和索引配置。