關于大數據查詢與導出

上周末,幫朋友處理了一個關于大數據的查詢與導出問題,整理一下,在此記錄一下用以備忘,同時也為有類似需要的朋友提供一個參考.

背景:

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