.Net EF Core千萬級數據實踐

.Net 開發中操作資料庫EF一直是我的首選，工作和學習也一直在使用。EF一定程度的提高了開發速度，開發人員專註業務，不用編寫sql。方便的同時也產生了一直被人詬病的一個問題性能低下。

EF Core + MySql的組合越來越流行，所以本文資料庫使用MySql完成相關示例。

說明

由於工作中也一直使用Sql Server，所以記錄這篇文章時也學習了很多MySql的內容。

MySql安裝，打開官網（//dev.mysql.com/downloads/installer/）下載安裝。

示例項目說明：

.Net 5.0 + 最基本的 EF Code First 模型。兩個Entity，分別為Order和OrderItem。

資料庫：

Order數據量500W

Order實體除了基本欄位定義還定義了一個OrderItems

OrderItems數據量800W

 

OrderItem定義了一個Order virtual 屬性

並在實體和表映射是定義了外鍵關聯

正常系統中單表最大可能就千萬級數據，數據再多便會考慮分表，所以最初設想是單個表準備1000W+的數據，但是沒有考慮到我這個老年筆記型電腦，所以實際操作時數據做了適當減少。

MySql記錄

準備好測試數據後寫了一些簡單的SQL查詢來做測試，一些稍微複雜點的查詢耗時就十秒、二十秒。此時應該從資料庫的優化入手，優化EF查詢不能解決我們的問題。優化MySql查詢和排序最簡單有效的辦法就是創建索引，根據業務需求合理的創建索引，保證索引的命中（最左原則），還要設置一個足夠大的innodb-buffer-pool-size。

參考文章

必須掌握的 MySQL 優化原理：//mp.weixin.qq.com/s/wuGbnvo3bCThO2ERqHpPAQ

MySQL 性能優化的21條實用技巧：//mp.weixin.qq.com/s/pyAddBuxjodmT7gkOBamTw

深入理解MySQL索引之B+Tree：//blog.csdn.net/b_x_p/article/details/86434387

MySql最左匹配原則解析：//www.cnblogs.com/wanggang0211/p/12599372.html

日誌記錄和診斷

項目中添加了兩種方式查看EF生成的SQL和執行耗時。做一下簡單說明實際開發中可自行選擇。

Microsoft.Extensions.Logging

確保項目安裝了Microsoft.Extensions.Logging包。

添加一個ILoggerFactory類型靜態屬性

public static readonly ILoggerFactory MyLoggerFactory = LoggerFactory.Create(builder => { builder.AddConsole(); });

EF Core 註冊此實例

options.EnableSensitiveDataLogging()
                .UseLoggerFactory(MyLoggerFactory)
                .EnableDetailedErrors()

MiniProfile

安裝MiniProfiler.AspNetCore.Mvc包

Startup的ConfigureServices方法增加程式碼

services.AddMiniProfiler(options =>
            {
                // All of this is optional. You can simply call .AddMiniProfiler() for all defaults

                // (Optional) Path to use for profiler URLs, default is /mini-profiler-resources
                options.RouteBasePath = "/profiler";

                // (Optional) Control which SQL formatter to use, InlineFormatter is the default
                options.SqlFormatter = new StackExchange.Profiling.SqlFormatters.InlineFormatter();

                // (Optional) You can disable "Connection Open()", "Connection Close()" (and async variant) tracking.
                // (defaults to true, and connection opening/closing is tracked)
                options.TrackConnectionOpenClose = true;

                // (Optional) Use something other than the "light" color scheme.
                // (defaults to "light")
                options.ColorScheme = StackExchange.Profiling.ColorScheme.Auto;

                // The below are newer options, available in .NET Core 3.0 and above:

                // (Optional) You can disable MVC filter profiling
                // (defaults to true, and filters are profiled)
                options.EnableMvcFilterProfiling = true;
                // ...or only save filters that take over a certain millisecond duration (including their children)
                // (defaults to null, and all filters are profiled)
                // options.MvcFilterMinimumSaveMs = 1.0m;

                // (Optional) You can disable MVC view profiling
                // (defaults to true, and views are profiled)
                options.EnableMvcViewProfiling = true;
                // ...or only save views that take over a certain millisecond duration (including their children)
                // (defaults to null, and all views are profiled)
                // options.MvcViewMinimumSaveMs = 1.0m;

            }).AddEntityFramework();

Startup的Configure方法增加如下程式碼

app.UseMiniProfiler();

_ViewImports.cshtml文件中添加引用和對應taghelper

@using StackExchange.Profiling
@addTagHelper *, MiniProfiler.AspNetCore.Mvc

在視圖文件中添加MiniProfiler 

<mini-profiler />

.Net Core 5 提供了IQueryable的ToQueryString()方法可以直接獲取Linq查詢對應的SQL語句。

查詢數據

先說明兩個實例中沒有出現的基本查詢優化方案

1.大表避免整表返回（sql中的select *），簡化查詢實體僅返回業務需要的欄位，返回多個欄位時可以將Select查詢映射到匿名類。

2.如果只是單純的獲取列表不需要更新從資料庫中檢索到的實體，應使用AsNoTracking方法設置非跟蹤查詢，無需設置更改跟蹤資訊（EF 在內部維護跟蹤實例的字典），更快速地執行查詢。

Find

示例中實現兩個方法 GetByIdAsync和GetByIdFromSql，實現如下

啟動項目看到如下輸出：

EF的Find方法生成了一個簡單的sql語句執行耗時19ms，反而通過FromSqlInterpolated調用自己寫的SQL卻生成一個看著怪異的sql語句，執行耗時3ms。MiniProfiler中查看耗時差不多

兩個SQL耗時不應該有這麼大的差距，把兩個SQL複製到資料庫中執行時發現兩個SQL執行時間基本相同，說明調用EF方法EF到SQL的轉換耗時也計算在內，因為EF的快取機制再次調用時發現兩個方法的耗時基本持平。

兩種方式返回的Order中OrderItems數量為零，解決這個問題就涉及到EF載入相關數據的知識。

這裡演示預先載入和延遲載入兩種方式

預先載入

修改GetByIdAsync程式碼：

var order = await _dataDBContext.Orders.Include(a => a.OrderItems).FirstOrDefaultAsync(a => a.Id == id);

此時EF生成的程式碼就會關聯查詢OrderItem，EF生成SQL如下

查看列印的log會發現一個問題，我們修改EF程式碼為預先載入，SQL查詢生成的SQL相同卻會關聯查詢出OrderItems的數據。

再次修改程式碼

 並修改GetByIdFromSql方法參數為1362（之前和GetByIdAsync參數一樣為1360），運行

 

同樣是Find查找，1362對應的OrderItems為空，1360對應的OrderItems的Count卻為3，對應Sql查詢的1362的OrderItems也為空。應該是EF的快取機製造成的這種情況，有興趣和精力的可以查看一下EF Core的源碼。

延時載入

AddDbContext時增加UseLazyLoadingProxies方法調用

此時不管是EF的Find還是原始SQL都能查詢出OrderItems的值。

查詢結果集及外鍵關聯數據

定義如下方法查詢結果為某個用戶訂單及關聯數據

運行程式碼，會遇到使用EF時經常遇到的一個錯誤

因為獲取orders時已經建立一個連接，當我們循環orders獲取OrderItems時（當前設置為延時載入）需要在建立連接從而引發這個異常。修改程式碼通過ToList來避免這個異常

此時可以正常獲取OrderItems的數據，通過MiniProfiler查看生成的sql

這個EF方法生成了21（1條查詢orders+20條延時查詢orderitems）條sql。再次修改程式碼，改為預先載入的方式，查詢Order的同時返回OrderItems數據。

EF生成的sql從21條變為1條。

拆分查詢 

EF Core 5.0 中引入拆分查詢功能以避免「笛卡爾爆炸」問題，可以將指定 LINQ 查詢拆分為多個 SQL 查詢，僅在使用 Include 時可用。

單個EF查詢調用AsSplitQuery方法啟用拆分查詢。也可以全局啟用拆分查詢，在設置應用程式資料庫連接上下文時調用UseQuerySplittingBehavior開啟全局拆分。

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
    optionsBuilder
        .UseSqlServer(
            @"Server=(localdb)\mssqllocaldb;Database=EFQuerying;Trusted_Connection=True;ConnectRetryCount=0",
            o => o.UseQuerySplittingBehavior(QuerySplittingBehavior.SplitQuery));
}

設置拆分查詢為默認的查詢方式後，可以再調用AsSingleQuery方法指定具體的EF查詢為單個查詢模式。

 為了測試這個功能我又添加了一張Customer表，修改程式碼如下：

public async Task GetOrdersAsync(int customerId, int pageIndex, int pageSize)
        {

            var order = await _dataDBContext.Orders.Where(a => a.CustomerId == customerId)
                .OrderBy(a => a.CreatedTime)
                .Skip((pageIndex - 1) * pageSize).Take(pageSize)
                .Include(a => a.OrderItems).Include(a => a.Customer)
                .TagWith("--Get Orders").AsSplitQuery().FirstOrDefaultAsync();

            var orders = await _dataDBContext.Orders.Where(a => a.CustomerId == customerId)
                .OrderBy(a => a.CreatedTime)
                .Skip((pageIndex - 1) * pageSize).Take(pageSize)
                .Include(a => a.OrderItems).Include(a => a.Customer)
                .TagWith("--Get Orders").AsSplitQuery().ToListAsync();

            var count1 = 0;
            foreach (var _order in orders)
            {
                count1 += order.OrderItems.Count;
            }

            Console.WriteLine($"count1:{count1}");

        }

上面並不是一個能正常執行的程式碼，拋出異常MySql.Data.MySqlClient.MySqlException (0x80004005): There is already an open DataReader associated with this Connection which must be closed first。

Github的issues提到這個問題，拆分查詢需要開啟Sql Server的MARS（MultipleActiveResultSets=true）。但是MySql不持支MARS，目前我不知道如何在MySql下正常運行AsSplitQuery的程式碼。

拆分查詢當前實現執行為每個查詢的往返（類似延時載入）， 這個將來會修改為單次往返中執行所有查詢。

更新數據

EF Core 默認情況下，僅在單個批處理中執行最多42條語句，可以調整這些閾值實現可能更高的性能，但在修改之前應進行基準測試確保有更高的性能。

摘自官網的一個段示例說明

 很遺憾EF目前還不支援批量更新和刪除操作，官網也給出了優化方案，用原始SQL來執行：

context.Database.ExecuteSqlRaw("UPDATE [Employees] SET [Salary] = [Salary] + 1000");

B站活躍用戶楊中科老師的一篇文章也有介紹：//www.bilibili.com/read/cv8545714 

但是複雜的更新業務寫SQL同樣是讓人頭疼的一件事，不想寫一行SQL語句，又想實現批量更新和刪除操作可以藉助第三方庫Zack.EFCore.Batch或Z.EntityFramework.Extensions.EFCore（//entityframework-extensions.net）。

性能提升

DbContext 池

AddDbContextPool 啟用可重用上下文實例的池，上下文池可以重複使用上下文實例，而不用每個請求創建新實例，從而提高大規模方案（如 web 伺服器）的吞吐量。在請求上下文實例時，EF 首先檢查池中是否有可用的實例。 請求處理完成後，實例的任何狀態都將被重置，並且實例本身會返回池中。

services.AddDbContextPool<BloggingContext>(options => options.UseSqlServer(connectionString));

poolSize 參數 AddDbContextPool 設置池保留的最大實例數 中128。 一旦 poolSize 超出，就不會快取新的上下文實例，EF 會回退到按需創建實例的非池行為。

上下文池的工作方式是跨請求重複使用同一上下文實例。 上下文池適用於上下文配置（包括解析的服務）在請求之間固定的場景。 對於需要作用域服務或需要更改配置的情況，請勿使用池。 池的性能提升通常很小，僅在高度優化的方案中採用。

預編譯查詢

執行普通Linq查詢的時會執行一次Compile，雖然EF對查詢的Linq有快取機制，但是編譯的查詢比自動快取的 LINQ 查詢效率更高。對於多次執行結構類似的查詢可以通過預編譯，僅編譯查詢一次並在每次執行時使用不同參數的方法來提高性能。

示例程式碼：

Func<DataDBContext, decimal, SingleQueryingEnumerable<int>> compiledProductReports =
            EF.CompileQuery<DataDBContext, decimal, SingleQueryingEnumerable<int>>(
            (ctx, total) => ctx.OrderItems.AsNoTracking().IgnoreAutoIncludes()
                .GroupBy(a => a.ProductId).Select(a => new
                {
                    ProductId = a.Key,
                    Quantity = a.Sum(b => b.Quantity),
                    Price = a.Sum(b => b.Price),
                }).Where(a => a.Price > total).Select(a => a.ProductId) as SingleQueryingEnumerable<int>
            );

        [Benchmark]
        public async Task ProductReports()
        {
            //var productIds = await _dataDBContext.OrderItems.IgnoreAutoIncludes().AsNoTracking()
            //    .GroupBy(a=>a.ProductId).Select(a => new {
            //        ProductId = a.Key,
            //        Quantity = a.Sum(b => b.Quantity),
            //        Price = a.Sum(b => b.Price),
            //    }).Where(a=>a.Price>100000).Select(a=>a.ProductId)
            //    .ToListAsync();

            var productIds = compiledProductReports(_dataDBContext, 100000).ToList();

        }

很遺憾這又不是一個好的程式碼，由於EF Core 5.0 增加了單個查詢和拆分查詢的概念，返回的類型為SingleQueryingEnumerable，遇到了Expression of type ‘Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1[System.Int32]’ cannot be used for return type ‘System.Linq.IQueryable`1[System.Int32]’ 這個錯誤，所以只能強轉為SingleQueryingEnumerable。但是會產生如下警告

Any

Count和Any

當有個需求判斷滿足條件的數據是否存在通常會有如下寫法

查看三種方式生成的SQL和執行耗時，Any是效率最高的一種。

補充

提升EF的性能還有很多，分為三大類：資料庫性能（純資料庫優化）、網路傳輸（減少數據傳輸和連接次數）和EF運行時開銷（跟蹤和生成SQL語句）。還有很多優化技巧沒能提及到如AsEnumerable方法改為

流式處理處理每次只獲取一條數據，但是會增加數據連接。想進一步提醒程式的性能最簡單的辦法就是在加入快取機制（Redis快取等），快取模式介紹（//mp.weixin.qq.com/s/iUDA8L30-z_36XvYP8Dq1w），EF的攔截器也為我們提供了更多的解決方案（//docs.microsoft.com/zh-cn/ef/core/logging-events-diagnostics/interceptors#example-advanced-command-interception-for-caching）。

Github地址：//github.com/MayueCif/EFCore

Tags:

VirMach 便宜 VPS

QNews

QNews