关于大数据查询与导出

关于大数据查询与导出

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

背景:

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 th