【MVC+MySQL+EntityFramework】查詢效能最佳化筆記

標籤：

通過在DbContext中定了表之間的關係，查詢後在View中通過item.ProjectOverHour來顯示關聯表資料。

 

            modelBuilder.Entity<ProjectOverHour>()                .HasMany(e => e.DailyReports)                .WithRequired(e => e.ProjectOverHour)                .HasForeignKey(e => new { e.ProjectIndex, e.EmployeeId, e.ReportDate })                .WillCascadeOnDelete(false);

開發完後發現頁面僅僅顯示10條記錄耗時已經超過1s了。

調查後發現如下原因：

1、EntityFramework中，關聯表資料不是在查詢時載入的，而是在用到時（即View中調用item.ProjectOverHour時）才會去DB查詢。

將畫面顯示條數改為20條後，頁面載入時間成正比例上升。

考慮不讓EntityFramework執行多次查詢，使用Select方法使其一次查詢出所需要的關聯資料。

Select<DailyReport, DailyReportSearchResultViewModel>(d => new DailyReportSearchResultViewModel()                {                   ...                   OverHour = d.ProjectOverHour.OverHour,                   ...                });

這樣修改後產生的SQL中關聯了需要的表，並且只執行了一次查詢。

因為在DbContext中定義的是外鍵關係，導致產生的SQL文是InnerJoin的關係，而不是想定的LeftJoin關係。

調查後沒有發現如何在DbContext中定義Left Join關係。（有WithOptional方法，但執行時總是出錯）

後來改用如下寫法，實現了Left Join的效果，查詢結果也是正確的。

from d in dailyReportsQueryjoin ot1 in db.ProjectOverHours   on new { ProjectIndex = d.ProjectIndex, EmployeeId = d.EmployeeId, ReportDate = d.ReportDate }   equals new { ProjectIndex = ot1.ProjectIndex, EmployeeId = ot1.EmployeeId, ReportDate = ot1.WorkDate } into ottempfrom ot in ottemp.DefaultIfEmpty()select new DailyReportSearchResultViewModel{   ...   ApprovalOverHour = ot.ApprovalOverHour,   ...};

但是頁面載入時間仍然很長（4s左右）。

後來發現把關聯表中的兩個View的演算法改為Template後，資料查詢快了很多。

這個感覺很沒道理，查詢應該Meger查詢更快才對。

在用產生的SQL測試時，發現建立索引佔據了99%的時間。

原來關聯View的業務主鍵，在原來的表裡並不是主鍵。

將原來的表裡追加相應的索引後，查詢快了很多。

逐個把關聯的表都加了索引。

 

上述修改後，資料少時頁面載入頁很快（0.1s左右）。

但是在追加了2w條資料後再測試時，發現頁面載入又變慢了。

還是EntityFramework產生的SQL文的問題。

因為使用OrderBy來排序，並且使用了分頁，產生的SQL文變成了如下的形式。

SELECT    ...FROM (SELECT    ...FROM `dailyreport` AS `Extent1` LEFT OUTER JOIN ...INNER JOIN ... WHERE (0 = `Extent1`.`DeleteFlag`) ... ) AS `Project1` ORDER BY `Project1`.`...` DESC, `Project1`.`...` ASC, `Project1`.`...` DESCLIMIT 0,10

把查詢結果作為子查詢，然後再排序和分頁。

查詢用時1.1s，其中Sending data佔了99%的時間。

仍然比最初的頁面載入時間要多。

最後改為將InnerJoin的表用Select方法取出相關資料，LeftJoin的表的資料，通過迴圈Select結果集，單獨取得這些資料。

修改後開發環境頁面載入時間在0.2~0.3s，伺服器環境在0.4~0.5s。

雖然不是很理想，但是對於社內用的小系統來說，效能已經可以接受了。

 

2、MySql的DB伺服器是放在一台虛擬機器上的，效能一直不好，並且已經有別的系統在用。

因為要用到該DB伺服器上的另一個資料庫的資料，所以考慮把本系統的資料建立在一台新機器上，然後建立遠端連線表來取資料。

但發現每次查詢都要從遠程伺服器取得所有的資料然後再查詢，效率極差。

最後還是只好放在該伺服器上了。

CREATE TABLE IF NOT EXISTS `tablename`` (    ......) ENGINE=FEDERATED DEFAULT CHARSET=utf8CONNECTION=‘mysql://user:[email protected]:3306/schema/tablename‘;

如果DB能換台效能好些的伺服器，應該能帶來不少的提升。

 

【MVC+MySQL+EntityFramework】查詢效能最佳化筆記