初体验：Typeorm的SQL优化

刚接触数据项目的后端开发时，经历了一次记忆深刻的SQL优化过程，这次优化过程可以说是小白打怪升级的第一关。在导师指导下完成优化，接口响应速度竟缩短至原来的10%以下。将此次查询优化记录于此，是希望自己不再设限于前端，而能学到更多的前后端知识。

1.实现业务逻辑

需求背景：以指定时间粒度统计产品的曝光量、回收量数据，并图形化展示数据。

这样的需求在数据项目中是非常常见的。在刚接手需求时，我也同样自信满满，认为其实现难度Just so so。在测试数据较少时，我以如下的逻辑代码操作数据，初步满足了图表查询的功能体验。回头看这段代码，着实有很多繁复冗余的步骤，优化查询应首先区分不同的优化点，一起看看有哪些值得优化的地方吧。

   // 查找pv数据 ////优化点1：全表查询冗余，可用字段查询替代
    const pvData = await this.reportPvEntity.find({
      curId,
      upTime: MoreThanOrEqual(lastTime)
    })
    // 查找pv表中关联的report数据 ////优化点1：全表查询冗余，可用字段查询替代
    let reportData = await this.reportPageEntity.find({
      xxId,
      uid: In(uids)
    })
    // report表的upTime字段同步pv表 ////优化点2：嵌套循环，时间复杂度呈二次幂，可用联查替代
    reportData.map(item => {
      const curPv = pvData.find(pv => {
        return pv.uid === item.uid
      })
      if (curPv && curPv.upTime) {
        item.upTime = curPv.upTime
      }
      return item
    })
    const data = {
      reportTimeList: reportData || [],
      pvTimeList: pvData || []
    }
	//...
	//// 在前端逻辑中，还需将两个对象数组内容按照时间粒度进行分组，取不同时间粒度中的记录数，最终形成两个形如下式的对象数组
	////              [ { upTime: 记录数1 }, { upTime: 记录数2 }, ... ]
	//// 优化点3：按照时间粒度分组生成记录数键值对，可用分组查询替代前端运算逻辑

1.优化后端运算、查询效率

根据上一节的分析可见，业务在后端逻辑中存在两次全表查询，一次 n * m 时间复杂度的嵌套循环，两次数组生成键值对的遍历，在前端逻辑中，还存在两次根据时间粒度分组的遍历。进行优化前要分清业务中哪些步骤造成了更多的开销，分清优化的主次。使用winston日志插件，能够打印出在进程中两处埋点的时间差。经过分析，优化点3（嵌套循环）产生了最大开销，在百万数据量下，造成了约3秒的运算时延，其余优化点均产生不到一秒的时延。于是我们有如下优化策略： 1. 首要考虑优化点2的问题，以SQL条件查询和高级语句来替代其嵌套造成的较大时延。 2. 着手优化点1，以字段查询替代全表查询。

    // 查找pv数据 ////优化点1 优化完成，字段查询替代全表查询
    const pvData = await this.reportPvEntity.find({
      select: ['uid', 'upTime'],
      where: {
        curId,
        upTime: MoreThanOrEqual(lastTime)
      }
    })
    // 查找与pv表uid关联的report数据  ////优化点2 优化完成 使用SQL条件查询和高级语句替代嵌套循环
    const reportData = await createQueryBuilder('ReportPageEntity', 'report')
      .where('report.xx_id = :xxId', { xxId })
      .innerJoinAndSelect(ReportPvEntity, 'pv', 'report.uid = pv.uid')
      .select('report.upTime')
      .getMany();
	const data = {
      reportTimeList: reportData || [],
      pvTimeList: pvData || []
    }
    //...
	//// 在前端逻辑中，还需将两个对象数组内容按照时间粒度进行分组，取不同时间粒度中的记录数
	//// 优化点3：待优化

2.将前端运算转移至服务端，以更高效的SQL集成查询与运算

在上一小节的优化后，使用winston日志插件打印运算点之间时间戳，发现优化点2的在使用高级语句替换嵌套循环后的时延足足减少了4/5，整体的接口耗时减少了约3/4。在此时，业务已经能满足百万数据量的使用体验了，但我们仍不满足，立志将更大量级的数据需求也优化至无差别体验。在查阅学习和与导师讨论后，明确了一种以时间粒度为分组依据的分组查询方式来集成查询和前端业务逻辑中的多个运算，但这样的分组查询要如何在typeorm中使用呢？查阅资料，直接撸SQL即可。

导师左神 亲自输出，使用FROM_UNIXTIME函数，对业务代码进行进一步的优化，实现了千万数据无差别体验。

      // 格式化的up_time字段，按照不同的时间粒度进行分组查询
      if (timeGap === 'day') {
        const [pvLData, pvLData] = await Promise.all([
        this.reportPvEntity.createQueryBuilder('pv')
          .where('pv.xxId = :xxId', { xxId })
		  .andWhere('pv.up_time >= :lastday', { lastday: Date.now() - 30 * 24 * 60 * 60 * 1000 })
          .select('count(*)', 'count')
          .addSelect(`FROM_UNIXTIME(pv.up_time / 1000,'%Y/%m/%d 00:00:00')`, 'dayType')
          .groupBy('dayType')
          .getRawMany(),
        this.reportPageEntity.createQueryBuilder('page')
          .where('page.pageId = :pageId', { pageId })
		  .andWhere('pv.up_time >= :lastday', { lastday: Date.now() - 30 * 24 * 60 * 60 * 1000 })
          .innerJoinAndSelect(ReportPvEntity, 'pv', 'page.uid = pv.uid')
          .select('count(*)', 'count')
          .addSelect(`FROM_UNIXTIME(pv.up_time / 1000,'%Y/%m/%d 00:00:00')`, 'dayType')
          .groupBy('dayType')
          .getRawMany()
      ])
    } else if (timeGap === 'xxxx') {
      // ......
    }

3.优化总结

从最初的查询冗余、复杂循环运算的业务代码，到最后的两个高度集成的查询语句，代码经历了从零散混乱到优雅集中，从开销较大到无差别体验的过程，总结来说，运用SQL的高级语句、函数等可以替代复杂的运算，一个集成的复杂查询造成的开销远小于简单查询之后的循环运算。SQL的高级语句众多，在文档学习后还需在项目中多多运用，许多文档中没有提到的解决方案往往能解决一些特殊问题。