关于大数据查询与导出

上周末,帮朋友处理了一个关于大数据的查询与导出问题,整理一下,在此记录一下用以备忘,同时也为有类似需要的朋友提供一个参考.

背景:

1. 数据库服务使用: SqlServer2008 ;
2. 查询的流水表总数据量约在 800W 条左右 ;
3. 需要展示的字段需要从流水表+基础资料表中
4. 导出需要 加载指定模板 ;
5. 要求查询响应时间<=2s,导出<=10s; (当然每次仅处理符合条件的数据) .
6. 该系统运行了大概2年时间,系统刚上线的时候,各项性能指标还ok,目前该功能点查询和导出时直接卡死.
7. 该项目为 常规 winform 类型,普通三层架构.

改造步骤:

1. 数据库,
   1. 该功能主要查询的表为日常业务流水,增长比较大,且查询多已产生的时间段作为查询条件,首先考虑使用创建时间段(每半年做一个分区)做分区表处理.
   2. 为提高数据文件读写性能,将该业务流水表存储为独立的数据文件,
   3. 创建查询条件 ”创建时间”,“付款公司Id”(int 类型)  和 “付款方式” 字段的索引.
2. 程序方面,
   1. 首先引入后台线程, 将耗时的查询从主线程[UI线程]中移除,转为后台线程处理,
   2. 采用分页查询数据,每次固定加载1000条数据,待滚动条滚动至当前结果集中最后一页的时候,自动加载下一页数据,
   3. 导出需要处理 查询和填充文件 两个操作.而这两个操作都比较耗时.引入队列+生产/消费模式处理.
   4. 去掉关联查询,查询的数据,采用仅查询业务流水表,内存中读取缓存的基础数据组合为前段UI需要的数据,
   5. 简化程序,同时也为降低sql语句复杂度,引入ORM,这里引入微软自家的EntityFramework(版本6.0) .
3. 下面略微介绍一下程序中的处理代码片段.
   1. 在原界面上增加一个友好提示,
      
   2. 查询功能按钮代码片段
      1. 查询按钮点击.

         /// <summary>
                 /// 响应查询按钮事件
                 /// </summary>
                 /// <param name="sender"></param>
                 /// <param name="e"></param>
                 private void btnQuery_Click(object sender, EventArgs e)
                 {
                     if (txtCompany.Tag == null)
                     {
                         MessageBox.Show(this, "请选择指定的结算公司", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
                         return;
                     }
                     _pageIndex = 0;
                     lblTotalCount.Text = "当前共[0]条数据";
                     gridData.Rows.Clear();
                     LoadQueryData();
                 }
         
                 /// <summary>
                 /// 开始查询数据
                 /// </summary>
                 private void LoadQueryData()
                 {
                     plProcessStatus.Visible = true;     //展示进度panel
                     SetControlStatus(true);             //设置其他功能控件暂时为只读状态
                     _isCurrentLoadEnd = false;          //标识正在加载数据
                     bgwQuery.RunWorkerAsync();          //开始执行后台查询
                 }

      2. 引入BackgroundWorker组件执行后台查询 ,关于构建EF查询条件可参见之前文章[使用EF构建企业级应用(三)].

         /// <summary>
         /// 后台线程查询数据
         /// </summary>
         /// <param name="sender"></param>
         /// <param name="e"></param>
         private void bgwQuery_DoWork(object sender, DoWorkEventArgs e)
         {
            int total;
            _queryList = ExecQuery(_pageIndex, _pageSize, out total);
            _rowCount = total;
         }
         
         /// <summary>
         /// 执行分页查询方法,返回当前查询结果,
         /// </summary>
         /// <param name="pgIndex">当前页码</param>
         /// <param name="pgSize">每次查询分页大小</param>
         /// <param name="total">记录总数</param>
         /// <returns></returns>
         private List<OrderDetail> ExecQuery(int pgIndex, int pgSize, out int total)
         {
             List<OrderDetail> lst = null;
         
             var queryParam = BuildQueryExpression(pgIndex, pgSize);
             using (var services = new KYEService())
             {
                 lst = services.GetOrderDetailList(queryParam, out total);
             }
             return lst;
         }
         
         /// <summary>
         /// 构建查询条件
         /// </summary>
         /// <param name="pgIndex">当前查询第几页</param>
         /// <param name="pgSize">当前查询分页大小</param>
         /// <returns>当前查询条件</returns>
         private EFQueryParam<OrderDetail> BuildQueryExpression(int pgIndex, int pgSize)
         {
           //计算查询时间段
           var queryBeginDate = new DateTime(_queryYear, _queryMonth, 1);
           var queryEndDate = queryBeginDate.AddMonths(1).AddDays(-1);
         
           //构建查询条件
           var exp = QueryBuilder.Create<OrderDetail>();
           exp = exp.Equals(t => t.PaymentCompanyId, (int)txtCompany.Tag);//结算公司
           exp = exp.GreaterThanOrEqual(t => t.FromDate, queryBeginDate); //纳入月份转化为开始日期
           exp = exp.LessThanOrEqual(t => t.FromDate, queryEndDate);      //纳入月份沾化为结束日期
           if (_queryPaymentType != EPaymentType.ALL)
           {
               exp = exp.Equals(t => t.PaymentType, _queryPaymentType);   //付款方式
           }
         
           //执行查询
           var queryParam = new EFQueryParam<OrderDetail>(exp, "FromDate", true, pgIndex, pgSize);
           return queryParam;
         }
         
         /// <summary>
         /// 查询数据线程结束后,开始UI绑定数据
         /// </summary>
         /// <param name="sender"></param>
         /// <param name="e"></param>
         private void bgwQuery_RunWorkerCompleted(object sender, RunWorkerCompletedEventArgs e)
         {
             plProcessStatus.Visible = false;                 //隐藏进度条
             if (_queryList != null && _queryList.Count > 0)  //当前查询有数据
             {
                 //后台线程异步展示数据到UI
                 Thread thBindGrid = new Thread(() =>
                 {
                     lblTotalCount.Invoke(new Action(() =>
                     {
                         lblTotalCount.Text = string.Format("当前共[{0}]条数据", _rowCount);
                     }));
                     //循环绑定数据
                     for (int i = 0; i < _queryList.Count; i++)
                     {
                         gridData.Invoke(new Action<OrderDetail>(FillData), _queryList[i]);
                     }
                     _isCurrentLoadEnd = true;     //标识当前查询加载结束
         
                     //绑定结束恢复其他功能按钮为可用状态(设置为非只读)
                     btnExport.Invoke(new Action(() =>
                     {
                         SetControlStatus(false);
                     }));
                 });
                 thBindGrid.IsBackground = true;
                 thBindGrid.Start();
             }
             else
             {
                 SetControlStatus(false);
             }
         }

         /// <summary>
         /// 绑定具体行数据
         /// </summary>
         /// <param name="detail">具体行数据</param>
         private void FillData(OrderDetail detail)
         {
             var index = gridData.Rows.Add();
         
             gridData["dgcIndex", index].Value = index + 1;
         
             //TODO... 具体绑定到Grid的代码略
         }

         /// <summary>
         /// 处理滚动条移动的时候,自动加载下一页数据
         /// </summary>
         /// <param name="sender"></param>
         /// <param name="e"></param>
         private void gridData_Scroll(object sender, ScrollEventArgs e)
         {
             //已经加载结束或者水平滚动,则不处理
             if (!_isCurrentLoadEnd                      //当前查询数据还未绑定结束
                 || _rowCount <= gridData.Rows.Count     //当前符合条件的数据已经查询完毕
                 || e.ScrollOrientation == ScrollOrientation.HorizontalScroll)
             {
                 return;
             }
         
             var gridPageRowCount = gridData.DisplayRectangle.Height;    //Grid每页能显示的记录数
         
             //当前滚动到最后一页
             if (gridData.FirstDisplayedScrollingRowIndex >= gridData.Rows.Count - gridPageRowCount - 1)
             {
                 _pageIndex += 1;
                 LoadQueryData();            //加载下一页数据
             }
         }

   3. 导出功能,队列+生产/消费模式处理

      /// <summary>
      /// 执行导出操作
      /// </summary>
      /// <param name="p"></param>
      private void ExecExport(string fileName)
      {
          plProcessStatus.Visible = true;
          SetControlStatus(true);
      
          IExport rpter = new ExcelExporter();
          var formater = BuildExportFormater();
          bool isQueryEnd = false;                    //当前是否查询结束
          var templateFieName = Path.Combine(Application.StartupPath, "Template", "Rpt_CustomerList.xls");
      
      
          //创建供导出的队列
          Queue<List<OrderDetail>> exportQueue = new Queue<List<OrderDetail>>();
      
          #region 查询线程
          //处理后台查询
          Thread thQuery = new Thread(() =>
             {
                 int tempTotal = 0;
                 int tempPgIndex = 0;
                 int queryPageSize = 3000;      //每次查询3k
                 var tempList = ExecQuery(tempPgIndex, queryPageSize, out tempTotal);
                 if (tempList != null && tempList.Count > 0)
                 {
                     lock (locker)
                     {
                         exportQueue.Enqueue(tempList);
                         Monitor.PulseAll(locker);
                     }
      
                     tempPgIndex += 1;
      
                     //循环查询直至查询结束
                     while (tempPgIndex * _pageSize < tempTotal)
                     {
                         var temp_tempList = ExecQuery(tempPgIndex, queryPageSize, out tempTotal);
                         if (temp_tempList != null && temp_tempList.Count > 0)
                         {
                             lock (locker)
                             {
                                 exportQueue.Enqueue(temp_tempList);      //将查询结果加入到队列
                                 Monitor.PulseAll(locker);
                             }
                         }
                         tempPgIndex += 1;
                     }
                 }
                 isQueryEnd = true;
             });
          #endregion
      
          #region 导出excel线程
          //处理将查询的结果写入到文件中
          Thread thExport = new Thread(() =>
             {
                 rpter.Export(templateFieName, fileName, formater);//读取模板,并创建新文件,
                 int tempRowIndex = 0;
                 while (!isQueryEnd || exportQueue.Count > 0)     //未查询结束及队列不为空,执行导出
                 {
                     if (exportQueue.Count > 0)
                     {
                         List<OrderDetail> tempExpotLst = null;
                         lock (locker)
                         {
                             tempExpotLst = exportQueue.Dequeue();        //取队列数据,导出excel操作
                         }
                         if (tempExpotLst != null && tempExpotLst.Count > 0)
                         {
                             formater.DetailRowBeginIndex += tempRowIndex;
                             rpter.ExportByAppend(fileName, formater, tempExpotLst);          //执行导出操作(追加形式)
                             tempRowIndex = tempExpotLst.Count;
                         }
                     }
                     else
                     {
                         Thread.Sleep(200);
                     }
                 }
      
                 //导出贴图片
                 var imgRow = formater.DetailRowBeginIndex + tempRowIndex + 8;
                 formater.ImageCellFormaters.Add(new ImageCellFormater(imgRow, 2, Resources.ywz));
                 rpter.ExportByAppend(fileName, formater, null);
      
                 //导出结束 恢复按钮可用状态
                 btnExport.Invoke(new Action(() =>
                 {
                     plProcessStatus.Visible = false;         //隐藏进度栏
                     SetControlStatus(false);
                     if (MessageBox.Show(this, "数据已成功导出至[" + fileName + "],是否立即打开导出文件?",
                         "提示",
                         MessageBoxButtons.YesNo,
                         MessageBoxIcon.Question) == DialogResult.Yes)
                     {
                         Process.Start(fileName);
                     }
                 }));
             });
          #endregion
      
          thQuery.IsBackground = true;
          thExport.IsBackground = true;
      
          thQuery.Start();            //开始查询线程
          thExport.Start();           //开始导出线程
      }

4. 后台查询数据方法 (基于EF实现),

   /// <summary>
   /// 获取物流明细记录
   /// </summary>
   /// <param name="queryParam">查询条件</param>
   /// <param name="total">返回符合条件的总记录数量</param>
   /// <returns></returns>
   public List<OrderDetail> GetOrderDetailList(EFQueryParam<OrderDetail> queryParam, out int total)
   {
       total = 0;
       var lst = GetRepository<OrderDetail, Int64>().Get(queryParam, out total).ToList();
   
       //组织其他冗余数据
       if (lst != null && lst.Count > 0)
       {
           //冗余公司信息,供前台UI使用
           var companyList = GetCompanyList();
           if (companyList != null && companyList.Count > 0)
           {
               var companyDic = companyList.ToDictionary(p => p.Id);               //转化为字典,提高效率
               var tempbgIndex = queryParam.PageIndex * queryParam.PageSize + 1;   //生成排序
               lst.ForEach(t =>
               {
                   t.Index = tempbgIndex;
                   //寄件公司
                   if (companyDic.ContainsKey(t.FromCompanyId))
                   {
                       t.FromComoany = companyDic[t.FromCompanyId];
                   }
                   //收件公司
                   if (companyDic.ContainsKey(t.ToCompanyId))
                   {
                       t.ToCompany = companyDic[t.ToCompanyId];
                   }
                   //付款公司
                   if (companyDic.ContainsKey(t.PaymentCompanyId))
                   {
                       t.PaymentCompany = companyDic[t.PaymentCompanyId];
                   }
                   tempbgIndex += 1;
               });
           }
       }
       return lst;
   }
   
   /// <summary>
   /// 获取公司信息
   /// </summary>
   /// <returns></returns>
   public List<Company> GetCompanyList()
   {
       //从缓存中获取
       var lst = ApplicationRuntime.Instance.CurrentCache.Get<List<Company>>(KYEConsts.CachesKey.Company,
           () => this.GetRepository<Company>().Get().ToList());
       return lst;
   }

结语: 经过这一系列改造后, 性能上大大改进了,查询响应耗时<=1s, 导出<=8s, 每次符合条件的数据大概在2W条左右.

至于本文中提到的

1. EF构建查询条件表达式,及查询数据库方式,可参见之前文章.http://www.cnblogs.com/xie-zhonglai/archive/2012/04/07/2435903.html

2. 导出Excel.本文使用了NPOI 这个组件,详情可参见文章: http://www.cnblogs.com/xie-zhonglai/p/3979771.html