使用EF框架的優化(六)

在處理數據庫查詢時，特別是在涉及到模糊查詢和日期字段時，我們常常面臨一個挑戰：如何在確保查詢效率的同時，實現精確和靈活的數據檢索？眾所周知，直接轉換數據庫字段類型進行匹配往往會導致查詢效率下降，甚至引發全表搜索的問題，這在處理大量數據時尤為明顯。因此，找到一種既能保持數據庫性能又能滿足查詢需求的方法顯得尤為重要。

在使用Entity Framework Core（EF Core）進行數據庫操作時，模糊查詢是一個常用而又復雜的功能。在EF Core中，進行模糊查詢通常涉及到Contains方法的使用。然而，如果直接對非字符串字段應用ToString()然后進行比較，EF Core會將其解析為字符串匹配。這種做法雖然在某些場景下有效，但可能導致效率問題。

SELECT count(1) FROM sales_orders
WHERE DATE_FORMAT(sales_orders.create_time, '%Y-%m-%d') = '2023-12-01';

SELECT count(1) FROM sales_orders
WHERE LOCATE('2023-12-01', CAST(sales_orders.create_time AS char) COLLATE utf8mb4_bin) > 0;

select count(1) from sales_orders
WHERE sales_orders.create_time >= '2023-12-01 00:00:00' and sales_orders.create_time < '2023-12-02 00:00:00';

-- 錯誤寫法 這個只能查詢到'2023-12-01 00:00:00'這個時間點的結果
select count(1) from sales_orders
WHERE sales_orders.create_time = '2023-12-01';

MySQL 查詢的 EXPLAIN 輸出，用于顯示 MySQL 如何執行特定的查詢

Access Type: ALL
這意味著 MySQL 正在進行全表掃描，即它查看表中的每一行來找到匹配的行。這通常是最慢的訪問類型，尤其是在處理大型表時。
Cost Hint: Very High
這表明查詢的成本非常高。在 MySQL 中，成本是一個相對的度量，用于表示執行查詢所需的工作量。
No usable indexes were found for the table
MySQL 沒有找到可用的索引來優化這個查詢。如果沒有索引，數據庫就必須執行全表掃描，這是非常低效的。
Filtered: 100.00%
這個值表示查詢條件過濾掉的數據百分比。在這種情況下，100% 表示沒有行被過濾掉，或者說每一行都被檢查了，這通常是因為查詢條件沒有排除任何行。這個值通常是指在表的全行中，有多少百分比的行是與查詢條件相匹配的。在理想情況下（即查詢非常有效地限制了結果集），這個百分比應該是較低的。
Rows Examined per Scan: 1994535
這表示每次掃描時檢查了多少行。在這個查詢中，幾乎檢查了兩百萬行，這是因為進行了全表掃描。
Hint: 100% is best, < 1% is worst
這個提示是關于 Filtered 百分比。這個提示可能是指行的選擇性。在這種特定情況下，因為查詢沒有過濾任何行（可能是由于查詢條件的設計），所以解釋器提示：實際上并沒有行被排除在外。
成本細節
Read: 讀取數據的成本。
Eval: 計算 WHERE 條件的成本。
Prefix: 到達當前查詢點的總成本。
Data_Read: 每次連接操作讀取的數據量。

我們已經知道將搜索字符串轉換目標數據類型，會提高查詢效率。在這種情況下，查詢慢的原因很可能是由于缺乏有效的索引。在 create_time 字段上創建一個索引可能會顯著提高查詢的性能，因為索引可以讓數據庫快速定位到那些匹配特定日期時間范圍的行，而不必掃描整個表。

CREATE INDEX idx_create_time ON sales_orders(create_time);

我們發現，直接將日期字段轉換為字符串進行查詢是一種常見但效率低下的做法。現在，我們將專注于優化這種方法，以提高查詢的效率和準確性。

首先，我們將模糊搜索字段嘗試轉換為日期類型，如果轉換成功，則進行日期模糊搜索；如果轉換失敗，則查詢時不考慮搜索日期字段。

下面是我嘗試轉換的靜態方法，允許輸入單個日期或者日期范圍。

/// <summary>
/// 嘗試根據輸入的字符串解析日期或日期范圍。
/// 支持的格式包括單個日期（年、年月、年月日）和日期范圍（年~年、年月~年月、年月日~年月日）。
/// </summary>
/// <param name="input">輸入的日期字符串，可以是單個日期或日期范圍。</param>
/// <param name="startDate">解析成功時，返回范圍的起始日期。</param>
/// <param name="endDate">解析成功時，返回范圍的結束日期。</param>
/// <returns>如果輸入格式正確且能成功解析，則返回true；否則返回false。</returns>
/// <remarks>
/// - 單個日期的格式可以是 "yyyy", "yyyy-MM" 或 "yyyy-MM-dd"。
/// - 日期范圍由兩個這樣的日期組成，以~字符分隔。
/// - 方法會根據輸入格式確定日期范圍：
///   - 年（如 "2023"）的范圍是該年的1月1日到次年1月1日。
///   - 年月（如 "2023-10"）的范圍是該月的1日到次月1日。
///   - 年月日（如 "2023-10-12"）的范圍是該日的0時到次日0時。
///   - 對于日期范圍，起始和結束日期根據相同規則確定。
/// - 輸入字符串中的日期部分可以包含或不包含前導零（例如 "2023-1-1" 或 "2023-01-01"）。
/// </remarks>
public static bool TryParseDateInput(string input, out DateTime startDate, out DateTime endDate)
{
    var formats = new[] { "yyyy-MM-dd", "yyyy-MM-d","yyyy-M-dd", "yyyy-M-d",
                "yyyy-MM", "yyyy-M", "yyyy" };
    var parts = input.Split('~');

    startDate = default;
    endDate = default;

    // 單個日期
    if (parts.Length == 1)
    {
        if (!DateTime.TryParseExact(input, formats, CultureInfo.InvariantCulture, DateTimeStyles.None, out var date))
        {
            return false;
        }

        switch (input.Count(f => f == '-'))
        {
            case 0: // 年
                startDate = new DateTime(date.Year, 1, 1);
                endDate = startDate.AddYears(1);
                break;
            case 1: // 年月
                startDate = new DateTime(date.Year, date.Month, 1);
                endDate = startDate.AddMonths(1);
                break;
            default: // 年月日
                startDate = date;
                endDate = startDate.AddDays(1);
                break;
        }

        return true;
    }
    // 日期范圍
    else if (parts.Length == 2)
    {
        if (!DateTime.TryParseExact(parts[0], formats, CultureInfo.InvariantCulture, DateTimeStyles.None, out var start) ||
            !DateTime.TryParseExact(parts[1], formats, CultureInfo.InvariantCulture, DateTimeStyles.None, out var end))
        {
            return false;
        }

        startDate = new DateTime(start.Year, start.Month, start.Day);
        endDate = new DateTime(end.Year, end.Month, end.Day);

        switch (parts[1].Count(f => f == '-'))
        {
            case 0: // 年
                endDate = endDate.AddYears(1);
                break;
            case 1: // 年月
                endDate = endDate.AddMonths(1);
                break;
            default: // 年月日
                endDate = endDate.AddDays(1);
                break;
        }

        return true;
    }
    else
    {
        return false;
    }
}

// 嘗試解析 strLike 為日期范圍
bool isDateRange = CommonFunc.TryParseDateInput(strlike, out DateTime startDate, out DateTime endDate);
expression = expression.And(p =>
    // ... 其他條件
    p.PayStatus.Contains(strlike) ||
    (isDateRange && p.CreateTime >= startDate && p.CreateTime < endDate));

避免不必要的類型轉換：在數據庫查詢中，盡量避免將非字符串類型轉換為字符串進行匹配。這不僅會降低查詢效率，還可能使得數據庫無法利用現有索引。

使用專門的日期函數：對于日期類型的字段，使用專門的日期比較函數而不是將日期轉換為字符串。EF Core支持多種日期相關的函數，這些函數可以直接應用于日期字段，提高查詢效率。

考慮索引的影響：確保對于頻繁進行模糊匹配的字段建立合適的索引。特別是對于大型數據庫，合理的索引對于維持查詢性能至關重要。

分析生成的SQL：在開發過程中，關注EF Core生成的SQL語句。這可以幫助我們理解EF Core是如何將LINQ查詢轉換為SQL的，并據此做出優化。

減少全表掃描：盡量減少會導致全表掃描的查詢模式。

異常：

"Argument data type text is invalid for argument 1 of charindex function."

大概意思就是contains不支持text類型的字段，網上有的解決方案就是將字段的類型設置為varchar(max)，那么如何在不改變數據庫結構的情況下解決

"The text data type cannot be selected as DISTINCT because it is not comparable."

大致意思就是distinct不能用于text類型，那么如何在不改變數據庫結構的條件下完成我們接口的編寫呢？這就不得不提到C#的集合類型之一HashSet了,這就是一個集合，有一些基礎的小伙伴們都知道集合是不可重復的，所以我們只要把查詢出來的結果轉換成HashSet形式就可以解決問題了。

 EF.Functions.Like(f.Content, "%" + key + "%")

        public async Task<IActionResult> Get(string key)
        {
            try
            {
                int page = 1;
                int limit = 15;
                var ef = new CmsContext();
                int offset = (page - 1) * limit;
                var li =
                    await (from a in ef.TxtArticles
                           join b in ef.TxtArticleTags on a.Id equals b.ArticleId into t1
                           from b in t1.DefaultIfEmpty()
                           join c in ef.TxtTags on b.TagId equals c.Id into t2
                           from c in t2.DefaultIfEmpty()
                           join d in ef.AdUsers on a.UserId equals d.Id into t3
                           from d in t3.DefaultIfEmpty()
                           join e in ef.AdUserImgs on d.Id equals e.UserId into t4
                           from e in t4.DefaultIfEmpty()
                           join f in ef.TxtArticleContents on a.Id equals f.ArticleId into t5
                           from f in t5.DefaultIfEmpty()
                           join g in ef.TxtTypes on a.TypeId equals g.Id into t6
                           from g in t6.DefaultIfEmpty()
                           where EF.Functions.Like(f.Content, "%" + key + "%")
                           orderby a.PubTime descending
                           select new
                           {
                               id = a.Id,
                               writer = d.Name,
                               uid = d.Id,
                               title = a.Title,
                               content = f.Content,
                               pub_time = a.PubTime,
                               view_num = a.ViewNum,
                               like_num = a.LikeNum,
                               front = a.Front,
                               type_id = a.TypeId,
                               type_name = g.TypeName
                           })
                           .Skip(offset).Take(limit).ToListAsync();
 
                return Json(li);
            }catch(Exception ex)
            {
                return Json(ex.Message);
            }
           
        }

        public async Task<IActionResult> Get(string key)
        {
            try
            {
                int page = 1;
                int limit = 30;
                var ef = new CmsContext();
                int offset = (page - 1) * limit;
                var li =
                    await (from a in ef.TxtArticles
                           join b in ef.TxtArticleTags on a.Id equals b.ArticleId into t1
                           from b in t1.DefaultIfEmpty()
                           join c in ef.TxtTags on b.TagId equals c.Id into t2
                           from c in t2.DefaultIfEmpty()
                           join d in ef.AdUsers on a.UserId equals d.Id into t3
                           from d in t3.DefaultIfEmpty()
                           join e in ef.AdUserImgs on d.Id equals e.UserId into t4
                           from e in t4.DefaultIfEmpty()
                           join f in ef.TxtArticleContents on a.Id equals f.ArticleId into t5
                           from f in t5.DefaultIfEmpty()
                           join g in ef.TxtTypes on a.TypeId equals g.Id into t6
                           from g in t6.DefaultIfEmpty()
                           where EF.Functions.Like(a.Title.ToLower(), "%" + key.ToLower() + "%")
                            || EF.Functions.Like(c.TagName.ToLower(), "%" + key.ToLower() + "%")
                            || EF.Functions.Like(g.TypeName.ToLower(), "%" + key.ToLower() + "%")
                            || EF.Functions.Like(f.Content, "%" + key + "%")
                            || EF.Functions.Like(d.Name.ToLower(), "%" + key.ToLower() + "%")
                           orderby a.PubTime descending
                           select new
                           {
                               id = a.Id,
                               writer = d.Name,
                               uid = d.Id,
                               title = a.Title,
                               //content = f.Content,
                               pub_time = a.PubTime,
                               view_num = a.ViewNum,
                               like_num = a.LikeNum,
                               front = a.Front,
                               type_id = a.TypeId,
                               type_name = g.TypeName
                           })
                           .Distinct()
                           .Skip(offset).Take(limit).ToListAsync();
 
                return Json(li);
            }catch(Exception ex)
            {
                return Json(ex.Message);
            }
           
        }

                HashSet<dynamic> hs = new HashSet<dynamic>();
                hs = li.ToHashSet<dynamic>();
 
                return Json(hs);

posted @ 2024-05-11 14:13 IT苦行僧-QF 閱讀(171) 評論(0) 收藏舉報

刷新頁面返回頂部

IT苦行僧-QF

使用EF框架的優化(六)

異常：

公告