所以我現在已經進入 Web 開發大約 3 年了，專業也有一年多了，這是我處理一個與資料庫查詢最佳化相關的問題的時候，我不是 SQL 專家，我可以得到這份工作完成。沒有花哨的查詢、觸發器、預存程序等。無論如何，我不得不穀歌搜尋最後一個。

長話短說..我們的 ORM (TypeORM) 把我們搞砸了..

免責聲明：

這並不是要誹謗 TypeORM 或任何 ORM。它們是為其建置目的而設計的特殊工具。我在最後附加了一些參考連結，這些連結是人們面臨類似問題的公開討論。無論如何，讓我們繼續這篇文章。

問題！

我們的提交表有超過 70 萬筆記錄，表現非常糟糕。從表中取得資料的最長時間超過 6 秒。

查詢相當簡單。我們所擁有的只是 4 個連接、幾個 where 子句（大約 4 個）、排序（在created_at time 字段上的 DESC）、限制和跳過。

根本原因分析..

導致我們的提交表大幅放緩的幾個因素如下：-

• 索引 - 對用於連接表的欄位進行不正確的索引或未進行索引。

• 不必要的連接 - 我們的查詢中有一些不必要的連接，可以將其刪除以獲得更多效能。

• 空字串錯誤 - 我們程式碼中的一個錯誤，如果沒有為這些列提供使用者輸入，我們將與作為查詢的 where 條件一部分的所有列的空字串 (“”) 進行比較。

• ORM - ORM 正在執行一個超級愚蠢的查詢來獲取資料。

這些是我在檢查程式碼和資料庫模式以及分析正在執行以獲取所需資料的查詢時發現的精確點。

對提到的每個問題進行分析和測試。

原因 1：索引

在進行了一些谷歌搜尋並閱讀了人們的類似問題後，我發現我們的問題並不是那麼大。人們正在為數百萬行而苦苦掙扎，而我們的只是其中的一小部分，所以一定是我們做錯了什麼。

早期解決這些問題的社區提出了許多建議。我發現進行適當的索引會有很大幫助。

因此，為了進行測試，我從 beta 資料庫中獲取了提交內容，該資料庫擁有大約超過 100k 記錄。

在沒有任何最佳化的情況下，執行整個過程平均需要 2.3 秒。 （當然，這個時間不僅包括在資料庫上執行查詢的時間，還包括透過網路傳播資料的時間）

在向列加入索引後，我確實發現它縮短了幾毫秒的時間，但這還不夠。它仍然在 2 秒 左右，而且往往不止於此。

所以有點令人失望！無論如何，繼續下一個目標。

原因 2：空字串錯誤

因此，我們的時間從 2.3 秒縮短到了大約 2 秒，這對於索引來說並不算多。但後來我在我們的程式碼中發現了一個小錯誤，假設有四個輸入欄位供使用者根據四個不同的列鍵入和過濾結果。如果使用者沒有在任何輸入上鍵入任何內容（主要是在頁面首次載入時），且 API 呼叫會直接取得最新資料，而不進行任何過濾，僅進行連接和排序。

因此，在那一刻，我們為資料庫中的所有列傳遞了“”字串，這似乎無害，但實際上發生的情況是，資料庫正在對所有四列進行查找，您猜對了“”字串。所以進行了大量的查找，實際上什麼都沒有。

因此，當我將其更改為空（如empty/null）時（相當於從查詢中刪除where子句），查詢時間從2.3秒變為1.3秒。

如果您想知道使用使用者提供的實際輸入進行過濾需要多長時間。大約500ms（這是可以接受的）。

結論 - 即使您的資料庫使用所有可搜尋列進行索引，“”字串也不能很好地發揮作用。

好的，我們正朝著正確的方向前進。我們整整縮短了 1 秒，但我們仍然必須將其控制在 200/150ms 以下，所以還有很長的路要走。

原因 3：不必要的連接

在查詢提交時，我們正在與不需要的比賽和課程表進行連接。因此，當所有內容都加入到程式碼中時，我們只是刪除了它，但這表明審閱者並沒有給予太多關注（我是其中之一）。

原因 4：ORM

這是造成最多的問題.. 好.. 問題!!.

所以有一種叫做 主動記錄模式 的東西，TypeORM 為我們提供了使用類似 JSON 的物件產生 SQL 查詢的東西，一個例子就是。

model.find({
      select: { userName : true, firstName : true },
      where: { userName : “SomeUsername” },
      relations: { user : true, contest: true, problem: true },
      order: { created_at : “ASC/DESC” ,
      skip: 0,
      take: 10,
})

因此，這使得開發變得快速、簡單，對於不擅長編寫原始 SQL 查詢的開發人員來說，感覺非常直觀，因為這是最抽象的版本，您實際上是在建立 JSON 物件來產生 SQL 查詢。

這種方法看起來不錯，而且大多數時候都有效，但在我們的例子中，它做了一些非常愚蠢的事情，我不會輸入它在做什麼，這樣你就可以自己看到查詢。

簡而言之，它正在執行兩個查詢，首先對於這種情況根本不需要，它可以通過我稍後編寫並測試的一個簡單的單個查詢輕鬆完成。

它不僅執行兩個單獨的查詢（原因尚不清楚，因為這是一個已知問題，有時在使用typeorm 的活動記錄模式時發生），它還將四個表連接兩次，每個查詢一次，然後還排序兩次各一次。 （這實際上沒有任何意義）

這也是表演受到最大打擊的地方。自己看看下面的查詢。

SELECT DISTINCT 
`distinctAlias`.`Submission_id` AS `ids_Submission_id`, `distinctAlias`.`Submission_created_at` 
FROM (SELECT `Submission`.`id` AS `Submission_id`, ... more selects 
FROM `submission` `Submission` 
LEFT JOIN `problem` `SubmissionSubmission_problem` 
ON `SubmissionSubmission_problem`.`id`=`Submission`.`problemId`  LEFT JOIN 
`user` `SubmissionSubmission_user` 
ON `Submission_Submission_user`.`id`=`Submission`.`userId`) `distinctAlias` 
ORDER BY `distinctAlias`.`Submission_created_at` DESC, `Submission_id` 
ASC LIMIT 10

SELECT `Submission`.`id` 
AS `Submission_id`, `Submission`.`language` 
AS `Submission_language`, `Submission`.`verdictCode` 
AS `Submission_verdictCode`, `Submission`.`tokens`
... shit ton of selects 
FROM `submission` `Submission` 
LEFT JOIN `problem` `SubmissionSubmission_problem` 
ON `SubmissionSubmission_problem`.`id`=`Submission`.`problemId`  LEFT JOIN `user` `SubmissionSubmission_user` 
ON `Submission_Submission_user`.`id`=`Submission`.`userId` WHERE `Submission`.`id` 
IN (?, ?, ?, ?, ?, ?, ?, ?, ?, ?) 
ORDER BY `Submission`.`created_at` 
DESC

所以這兩個查詢是問題的主要原因，也是主要原因之一。

因此，我編寫了一個簡單的原始 SQL 查詢來執行與它嘗試使用 2 個單獨的查詢執行的完全相同的操作，查詢如下：-

SELECT
  Submission.id,
  Submission.language,
  Submission.verdictCode,
...
FROM
  submission AS Submission
  LEFT JOIN problem ...
  LEFT JOIN user ...
ORDER BY
  Submission.created_at DESC
LIMIT 10

當我們執行這個查詢時，它的執行時間僅為 100ms!!!

因此，我們現在從 1.3 秒移至 100ms，總體從 2.3 秒移至 100ms

效能提升超過 23 倍。

之後我就去睡覺了。仍然需要做更多的測試，並嘗試找出邊緣情況（如果有），並提出為此編寫查詢的最佳方法。目前，我正在考慮使用 TypeORM 提供的儲存庫模式或查詢建構器模式。

第二天：

又來了..

全文索引

全文索引可以提高從這些索引列中搜尋單字和短語的效率，我們也可以嘗試一下。 （這是我的同事 Jay 提出的一個非常好的觀點，它進一步提高了表現）。

發現了一些更重要的點。

在 MySQL 中最佳化具有唯一索引的資料列上的「LIKE」查詢時，可以採用一些策略來提高效能。以下是一些建議：

1. 索引優化：

• 使用全文索引： 如果您的「LIKE」查詢涉及在列中搜尋單字或片語，請考慮使用全文索引而不是常規唯一索引。全文索引是專門為基於文字的搜尋而設計的，可以提供更快、更準確的結果。

• 使用排序規則： 確保列的排序規則不區分大小寫和重音。這可以透過使用「utf8_general_ci」或「utf8mb4_general_ci」等排序規則來實現。它允許更有效地利用索引，因為搜尋變得不區分大小寫和重音。

2. 查詢最佳化：

• 前綴搜尋：如果您的LIKE查詢在末尾使用通配符（例如，column LIKE 'prefix%'），索引仍然可以有效地使用。但是，如果通配符位於開頭（例如“column LIKE '%suffix'”），則不會使用索引。在這種情況下，請考慮使用替代技術，例如全文搜尋或儲存列的反向值以實現高效的後綴搜尋。

• 最小化通配符： 模式開頭的通配符（'%suffix'）會使查詢速度明顯變慢。如果可能，請嘗試建立查詢，使通配符僅出現在模式的末尾（「前綴%」）。

• 參數綁定： 如果您從應用程式內執行「LIKE」查詢，請使用參數綁定或準備好的語句，而不是直接連接查詢字串。這有助於防止SQL注入並允許資料庫更有效地快取執行計劃。

3. 快取和查詢結果：

• 快取查詢結果： 如果LIKE查詢結果相對靜態或不需要即時，可以考慮實作像memcached或Redis這樣的快取機制。快取可以透過直接從記憶體提供結果來顯著縮短反應時間。

• 物化視圖： 如果經常執行「LIKE」查詢且資料列的資料相對靜態，請考慮建立物化視圖來預先計算並儲存「LIKE」查詢的結果。如果查詢物化視圖所帶來的效能提升超過了額外的儲存和維護需求，則此方法可能會很有用。

值得注意的是，這些優化策略的有效性可能會根據您的特定用例而有所不同。

經過所有測試後建議的改進點。

1. 修正將空字串傳遞到 where/過濾條件的問題。

2. 在效能至關重要的讀取操作中，轉而使用查詢建構器而不是活動記錄模式。

3. 在用於搜尋和過濾的欄位中新增索引。另外，在不唯一且用於搜尋的列上新增全文索引。

4. 刪除/避免不必要的連線。如果可能的話，重組架構以在必要時複製資料。

5. 使用 LIKE 運算子搜尋時，使用「prefix%」模式，而不是我們使用的預設模式「%suff+pref%」。使用前綴模式有助於資料庫使用索引並提供更好的結果。

儘管如此，我們成功地將查詢時間從 7 秒 降低到 <=150 毫秒，這樣做後感覺很好，因為這是我第一次涉足性能和優化並尋找從我們已有的資源中榨取更多資源的方法。

特別感謝Mitesh Sir 在這次調查期間指出了潛在的原因並引導我走向正確的方向，並一遍又一遍地重新啟動測試伺服器😂因為由於記憶體限制，在多次執行測試後，資料庫會變得非常慢。

如果您想更多地談論與這一切相關的內容，請在 X 上關注我，https://twitter.com/RishiNavneet

參考

1. https://github.com/typeorm/typeorm/issues/3857#issuecomment-714758799

2. https://github.com/typeorm/typeorm/issues/3857

3. https://stackoverflow.com/questions/714950/mysql-performance-optimization-order-by-datetime-field

4. https://stackoverflow.com/questions/22411979/mysql-select-millions-of-rows

5. https://dba.stackexchange.com/questions/20335/can-mysql-reasonously-perform-queries-on-billions-of-rows

6. https://stackoverflow.com/questions/38346613/mysql-and-a-table-with-100-millions-of-rows

7. https://github.com/typeorm/typeorm/issues/3191

PS - 這些改進很久以前就完成了，我只是懶得發布它😬。

原文出處：https://dev.to/navneet7716/optimizing-sql-queries-h9j