為什麼這個實體框架查詢在 MySQL 中表現如此糟糕？

讓我給你一個小設置。我們在 MySQL 5.1 上有一個 InnoDB 表，它有近 2000 萬條記錄，沒有外鍵，並且為我們所做的查詢提供了適當的索引。我們正在為 .NET Entity Framework 使用最新的 6.3.5 MySQL 版本。通常我們習慣於處理 SQL Server 和實體框架，但在這個項目中，我們決定嘗試 MySQL。

我對這個問題有一些理論，但讓我先做程式碼設置

EF LINQ 查詢

var failsForAcct1001 = db.Failures.Where(x => x.AccountId == 1001);
/* farther down and later on in the code */
return failsForAcct1001.OrderBy(x => x.FailureId).Take(50);

生成的 MySQL 程式碼

請忽略它們不需要了解問題的列名

select
   External1.FailureId,
   External1.col2,
   External1.col3,
from (select
         Inner1.FailureId,
         Inner1.col2,
         Inner1.col3,
     from Failures Inner1
     where External1.AccountId = 1001
   ) External1
order by External1.FailureId
limit 50

生成的這個 SQL 與 SQL Server 中發生的非常相似，並且 SQL 可以毫無問題地處理它。這不僅僅是一個實體框架問題，當我接受這個查詢並在 MySQL Workbench 中執行時，它也會超時。但是，以下查詢會在半秒內返回。

select
   External1.FailureId,
   External1.col2,
   External1.col3,
from Failures External1
where External1.AccountId = 1001
order by External1.FailureId
limit 50

我認為這個性能問題與 MySQL 試圖執行內部查詢有關，該查詢會拉回數據庫中的所有記錄，然後嘗試排序並取 50，而不是像第二個查詢那樣將這一切作為一個操作來完成做。

我真的只是想確認我的觀察結果，並詢問是否有什麼辦法可以讓第一條 SQL 語句更快地執行，而無需以任何方式修改查詢。

第 1 步 - 獲取解釋計劃，特別是EXPLAIN EXTENDED將向您展示查詢優化器實際生成的 SQL。也許額外的索引會有所幫助——但如果你“外包”你的 SQL 生成，那麼你真的會受到 ORM 開發人員更容易的東西的擺佈，而不是最適合你的應用程序的東西。您可能會嘗試的另一件事是in-memory temp db，因為這可以幫助您進行排序和派生表…

MariaDB 5.3 可以處理此類查詢。

目前 5.3.2 版本中的開關涵蓋了該 optimizer_switch='derived_merge=on' 優化，並且該優化將在即將發布的 5.3.3 版本中成為預設（無需設置）

有關 5.3 中優化器的更多資訊

引用自：https://dba.stackexchange.com/questions/774