簡介
維護資料庫系統的效能是最佳化資料庫支援應用程式響應能力的重要組成部分。這涉及到管理伺服器的硬體資源和軟體配置，但也與您要求它執行的查詢有關。
在上一篇指南中，我們探討了如何在 MySQL 中識別低效能和慢查詢。在本指南中，我們將透過討論如何評估和最佳化慢執行查詢來邁出下一步。這將以上一篇文章的討論為基礎，為您提供一個識別和修復與低效能查詢相關問題的完整策略。
啟用查詢分析以瞭解慢查詢階段
嘗試最佳化慢查詢時，您可能首先要做的事情之一是分析查詢，以準確找出查詢在哪一步花費了最多時間。
首先，透過輸入以下命令檢查查詢分析是否已啟用：
0 表示當前分析已停用。您可以透過輸入以下命令啟用分析：
啟用分析後，您可以透過輸入以下命令開始列出可用的配置檔案：
輸出將顯示自啟用分析以來當前會話中執行的最新語句。每個查詢都顯示總執行時間，並有一個查詢編號，可用於深入瞭解。
要顯示最新查詢的分析資訊，請鍵入
預設情況下，輸出將精確顯示語句在查詢處理的每個階段所花費的時間。這可以幫助您準確識別查詢的哪個部分效能不佳。
要顯示與不同查詢關聯的結果，您可以新增 FOR QUERY <N> 子句以及 SHOW PROFILES 輸出提供的查詢 ID。
例如，要獲取查詢 1 的預設顯示，您可以輸入
在這裡，輸出清楚地顯示在 User sleep 狀態下花費了很長時間，這直接管理了執行的函式。
您還可以更改輸出以顯示不同型別的資訊。這通常在您檢視預設輸出後有助於檢查更具體的資訊。
例如，您可以透過鍵入以下內容檢視第一個查詢的塊輸入和輸出資訊：
除了之前的資訊，這還顯示了 MySQL 必須與檔案系統上的塊資料互動的時間。
您可以指定的可用輸出變體包括
ALL：顯示所有可用資訊BLOCK IO：顯示查詢生成的輸入和輸出塊數CONTEXT SWITCHES：顯示執行查詢時發生的自願和非自願上下文切換次數CPU：顯示使用者空間和系統類別的 CPU 時間IPC：顯示傳送和接收的程序間訊息數量PAGE FAULTS：顯示頁面錯誤的數量，分為主要錯誤和次要錯誤SOURCE：顯示 MySQL 中實際執行的函式及其原始檔和行號，以允許執行對映SWAPS：顯示查詢所需的交換次數
要顯示與查詢關聯的所有可能資訊，請鍵入
我們使用 \G 語句終止序列觸發替代輸出格式，以使結果更具可讀性，因為顯示了大量額外輸出。
一旦您使用分析資訊對查詢的哪個部分慢有了很好的瞭解，您就可以關閉分析以儲存資源。
使用 EXPLAIN 瞭解查詢效能
查詢配置檔案應能幫助您瞭解查詢執行中耗時最長的階段以及與查詢相關的資源。然後，您可以使用 MySQL 的 EXPLAIN 語句來理解查詢最佳化器對查詢的評估。
EXPLAIN 語句接受 SELECT、DELETE、UPDATE、INSERT 或 REPLACE 語句，並顯示查詢最佳化器將如何評估和執行給定查詢。輸出顯示 MySQL 將如何連線表、選擇欄位、排序和篩選結果等。它還顯示諸如將評估多少行以及將查閱哪些索引以加快過程等資訊。
為了瞭解此語句的工作原理，請讓 MySQL 解釋它將如何執行查詢 SELECT * FROM INFORMATION_SCHEMA.VIEWS;
上述輸出顯示，需要六個單獨的查詢才能將該查詢的資訊彙集起來。如果您查看錶定義，您會明白原因。
為了格式化，最終的表建立命令如下所示
有五個 JOIN 語句，這意味著必須查詢六個單獨的表來構建結果。這與我們執行的 EXPLAIN 語句返回的條目數量相匹配——每個表一個。
要解釋 EXPLAIN 語句的結果，您需要了解每個單獨列的含義。其中特別重要的是 type、key、rows 和 filtered 列。
解釋 type 列
type 列告訴您 MySQL 實現查詢中任何連線操作的方式。MySQL 文件對可用的不同連線型別以及它們如何影響效能有很好的描述。
例如，型別為 const 的查詢將非常快，因為它意味著 MySQL 將唯一列與常量值進行比較，這最多隻會返回一行。
eq_ref 型別是合併兩個表的最快連線操作，因為它表示查詢使用完整的唯一、非空索引值進行比較。
當需要進行全表掃描時，會使用index 型別，但查詢不是掃描實際表，而是可以掃描索引樹。這使得它在查詢範圍內效能相對較低，但由於它可以掃描較小的索引而不是實際表，因此仍然具有一定的最佳化。
ref 連線型別表示 MySQL 能夠使用索引的引導列進行比較（當整個索引包含多個未用於比較的列時）。這意味著即使它不匹配整個比較，MySQL 也能夠有效地使用部分索引。
解釋 key 列
key 列及其相關列（possible_keys、key_length 和 ref）幫助您瞭解哪些索引可用、使用了哪些索引以及索引使用效率如何。
possible_keys 列告訴我們正在連線的表有哪些可用的索引。這很有用，因為它顯示了 MySQL 在嘗試查詢適合滿足查詢的索引時從何開始。如果您在非主鍵上連線表，這可能是您需要檢查的地方，以確保您有可用的索引來加速該過程。
key 列本身告訴您 MySQL 決定使用哪個索引來執行查詢。在某些情況下，這甚至可能包括未在 possible_keys 列中列出的結果。例如，當索引包含查詢要求的所有列時，它可以比關聯的表更有效地掃描，即使該索引不適合（並且未用於）實際連線。
key_length 列表示實際使用了多少索引。對於多列索引，這可以幫助您瞭解儲存索引的多少部分用於此特定比較。ref 列也在此處發揮作用，它告訴您具體將什麼與特定索引進行比較。這可以幫助您確定是否值得建立更最佳化的索引。
解釋評估的行數
rows 和 filtered 列很有用，因為它們告訴我們正在檢查的表的範圍。
rows 列給出 MySQL 認為執行查詢的每個部分需要評估的行數。此處的數字受我們之前討論的所有列的影響。它向我們表明 MySQL 在給定提供的語句和可用索引的情況下，能夠以多高的效率來精簡整個表。
filtered 列估計在應用查詢中的任何行篩選條件後，將被返回的評估行數的百分比。如果值為 100，則表示不會發生行篩選。低於 100 的值表示由於連線條件或其他篩選，部分評估的行不會返回。
重寫慢查詢以加快執行速度
既然我們已經討論了 MySQL 允許您評估單個語句效能的一些方法，我們就可以討論如何提高該效能了。
您需要考慮的具體步驟在很大程度上取決於您透過查詢分析和 EXPLAIN 調查發現的結果。我們將在下面介紹一些最常見的最佳化查詢效能的方法。
建立額外的索引
提高查詢執行速度最直接的方法之一是確保為表提供適當的索引。您的索引應反映您查詢資料的最常用方式，並可能隨著您的使用方式演變或新查詢的實現而隨時間變化。
要建立新索引，您可以使用 CREATE INDEX 語句
您可以建立許多不同型別的索引，具體取決於您要索引的列的資料型別、值的最大長度以及要使用的索引結構。如果您的查詢已經在使用索引且索引效能不佳，那麼這些選項可能值得研究。但是，首先，您可以透過使用大多數預設值來保持相對簡單。
您需要考慮的第一個因素是您希望索引哪些列。如果您經常在特定列上進行連線，那麼確保這些列在您的索引中得到體現非常重要。索引主要用於行查詢，因此如果沒有索引，連線操作（將不同表中的各種行進行匹配）會非常昂貴。
如果單列完全封裝了您通常查詢表的方式，則可以建立單列索引。您還可以對多列進行索引，以使索引更具實用性。如果對多列進行索引，則順序非常重要。第一列應作為查詢匹配項的主要方式，而任何附加列應幫助您最佳化這些匹配項。
例如，如果您為 person 表建立 first_name 和 last_name 的索引，您幾乎肯定會希望像這樣定義索引：
在這裡，last_name 列是索引中的第一個或最左邊的列，這意味著查詢 Alan Smith 或所有 Smith 條目都將很快。然而，它對於僅按 first_name 查詢所有 Alan 例項將無效，這可能沒關係，除非我們預計經常只按姓氏進行查詢。
雖然建立索引可以幫助您提高資料集的讀取效能，但重要的是不要過度索引您的表。索引會犧牲寫入速度，因為每次在表中記錄新值時，所有與它關聯的索引也必須更新。如果表的主鍵與將用於它們的查詢模式很好地匹配，則它們的效果最佳，因為它允許您從強大的索引效能中受益，而無需大量輔助索引來適應不同的訪問模式。
限制候選行
另一種可以加快查詢速度的方法是儘可能限制語句中候選行和返回行的數量。這是一個非常依賴上下文的建議，因此在許多情況下它可能不適用，但如果您能夠減少 MySQL 需要評估的行數，則可以顯著提高效能。
您可以使用 LIMIT 子句來做到這一點。例如，要只返回五個結果，您可以使用以下格式：
在許多情況下（取決於查詢中包含的其他子句），限制返回的行數可以幫助 MySQL 短路查詢過程，比平時更早停止。這有助於儘早節省時間，而不是遍歷整個資料集，然後再截斷結果。
這在連線多個表時尤其有用。在任何可能的情況下，最好在連線發生之前限制查詢結果。這允許 MySQL 評估少量資料，然後與其他連線進行關聯，而不是評估整個第一個表。
僅選擇相關欄位
提高查詢效能的另一種方法是關注您實際返回的列。這可以透過多種不同方式幫助加快結果。
限制所選列主要透過最小化網路負載來影響效能。透過返回不需要的列，大型查詢會大幅增加查詢產生的網路流量。只選擇滿足查詢邏輯約束所需的列有助於透過精簡網路傳輸的資料來避免此問題。
透過有選擇地返回列影響效能的另一種方式與索引相關。如果 MySQL 能夠將您返回的所有列與索引關聯起來，它就可以潛在地對該更小、更有組織的資料集進行操作，而不是對整個表進行操作。當您的索引與查詢模式很好地匹配時，限制您查詢的列允許這種情況發生。
移除帶有前導萬用字元的比較
可能對查詢效能產生負面影響的一點是過度依賴萬用字元。在 SQL 中，% 字元在比較中用作萬用字元，表示可以替換任何值。這是在記錄中查詢資料的強大方式，但它可能對查詢效能產生非常大的影響。
對於前導或字首萬用字元尤其如此。前導萬用字元是檢查列值是否與以萬用字元開頭的模式匹配的比較。例如，檢查列是否匹配 %Main St.。這種操作的效能特別差，因為 MySQL 無法使用索引來發現匹配的結果。相反，它必須遍歷整個表以查詢匹配項，即使該列具有索引。
在這個特定的例子中，如果您將經常按街道名稱查詢以發現該街道上的所有地址，那麼將街道名稱和街道號碼分成兩個單獨的列可能更有意義。這將允許您使用索引快速查詢“Main St.”上的地址，然後根據需要過濾這些結果，如果您正在尋找特定地址。建立一個複合索引，將街道名稱放在首位，街道號碼列放在第二位，將使這更快。
結論
在本指南中，我們探討了一些評估單個查詢和獲取更多資訊以瞭解其慢的原因的基本方法。我們討論了分析查詢和使用 MySQL 的 EXPLAIN 語句來獲取有關查詢規劃器如何評估查詢的詳細資訊。然後，我們研究了一些透過修改查詢使其與資料庫檢索資料的方式更好地對齊來避免這些慢速的方法。
資料庫是複雜的軟體，負責處理大量可能以多種不同格式存在的資料。除了簡單地檢索必要資料之外，還期望資料庫能夠快速返回結果。MySQL 的查詢規劃器和最佳化器機制中內建了無數最佳化，以幫助最大限度地減少回答查詢所需的時間。
然而，資料庫只能在有限的範圍內自動最佳化。作為資料庫使用者，您還需要能夠理解正在訪問的資料、您對查詢的要求以及哪些查詢最重要以進行最佳化。為此，瞭解如何評估您的標準並在查詢效能未達到預期水平時進行調整非常重要。
如果您將 Prisma 與 MySQL 資料庫一起使用，您可以在文件的查詢最佳化部分瞭解最佳化查詢的方法。這將幫助您瞭解使用 Prisma 時各種查詢結構如何影響資料庫效能。
