使用定時(shí)任務(wù)優(yōu)化SQL查詢(xún)
SQL Server查詢(xún)性能調(diào)優(yōu)被視為主要問(wèn)題，因?yàn)閿?shù)據(jù)庫(kù)管理人員一直在不斷努力以實(shí)現(xiàn)其托管系統(tǒng)的最高性能和最低資源使用率。
在競(jìng)爭(zhēng)激烈的IT行業(yè)中，主要挑戰(zhàn)是使產(chǎn)品在市場(chǎng)上可供用戶(hù)使用，以便他們不選擇任何其他選擇。產(chǎn)品的開(kāi)發(fā)旨在以出色的性能和最新技術(shù)使客戶(hù)受益。開(kāi)發(fā)的產(chǎn)品及其性能足以使用戶(hù)充分利用技術(shù)。當(dāng)然，大多數(shù)產(chǎn)品/應(yīng)用程序可能正在處理后端服務(wù)和數(shù)據(jù)庫(kù)服務(wù)器之間的大量數(shù)據(jù)流。
一個(gè)理想且負(fù)責(zé)任的敬業(yè)軟件開(kāi)發(fā)團(tuán)隊(duì)認(rèn)為，編寫(xiě)高效的查詢(xún)以提供更快，更優(yōu)化的結(jié)果為己任。除此之外，我們還必須優(yōu)化執(zhí)行緩慢且無(wú)法給出更快結(jié)果的SQL查詢(xún)。
SQL Server查詢(xún)性能調(diào)優(yōu)被視為主要問(wèn)題，因?yàn)閿?shù)據(jù)庫(kù)管理人員一直在不斷努力以實(shí)現(xiàn)其托管系統(tǒng)的最高性能和最低資源使用率。
使用SQL執(zhí)行計(jì)劃作為任何數(shù)據(jù)庫(kù)管理員的首要方法，我們可以調(diào)出查詢(xún)輸出調(diào)優(yōu)。這就是該策略如何建議我們進(jìn)行調(diào)整的方式。我們演示了如何通過(guò)執(zhí)行路線(xiàn)圖演示在內(nèi)部完成任務(wù)。查詢(xún)最昂貴的方面是最好的寫(xiě)查詢(xún)和推薦的索引。
1.在SQL查詢(xún)中獲得高性能的最佳實(shí)踐
在進(jìn)入執(zhí)行計(jì)劃之前，讓我們通過(guò)一些最佳實(shí)踐來(lái)編寫(xiě)高級(jí)SQL查詢(xún)。在這里，我們假設(shè)您具有如何編寫(xiě)SQL查詢(xún)的基本知識(shí)，并且以下幾點(diǎn)是按照最佳實(shí)踐實(shí)現(xiàn)高性能的要點(diǎn)。
僅對(duì)必需列執(zhí)行此查詢(xún)，這將確保針對(duì)必需列觸發(fā)db查詢(xún)，并且不會(huì)獲取任何不必要的列，從而確保更好地滿(mǎn)足效率和規(guī)范。除非它完全符合目的，否則請(qǐng)勿回復(fù)查詢(xún)。
應(yīng)該避免子查詢(xún)。根據(jù)需要執(zhí)行加入或編寫(xiě)的功能。
利用適當(dāng)?shù)乃饕ㄒ垣@得更快的搜索結(jié)果）。
經(jīng)常注意結(jié)果中的NULL事件。
通常在SQL語(yǔ)句涉及多個(gè)源的情況下使用表別名。它提高了可讀性，維護(hù)性，并確保收集了正確的色譜柱。
在ORDER BY子句中，不要包括列號(hào)。使用此功能的主要目的是為了可用性和可伸縮性，而不僅僅是性能。創(chuàng)建數(shù)據(jù)庫(kù)時(shí)可能不必?fù)?dān)心，但是隨著時(shí)間的流逝，新列將應(yīng)用于SELECT語(yǔ)句，或者無(wú)論您是否使用了ColumnNumber，原始表都將重新排序。當(dāng)您使用ColumnNumber時(shí)，結(jié)果將是不可預(yù)測(cè)的，并且在ORDERBY方面是錯(cuò)誤的。
在列列表中使用INSERT語(yǔ)句很重要。我們建議這樣做，以幫助軟件開(kāi)發(fā)人員避免對(duì)添加到列的NULL值進(jìn)行表修改。因此，可以容易地確定影響。
對(duì)于T-SQL代碼，切勿使用雙引號(hào)。
您不應(yīng)該對(duì)以“ sp_”開(kāi)頭的存儲(chǔ)過(guò)程使用前綴。這是SQL為我們提供的系統(tǒng)語(yǔ)法。建議遵循一種獨(dú)特的模式來(lái)命名易于區(qū)分的書(shū)面程序。
2.什么是SQL執(zhí)行計(jì)劃？
如所討論的，執(zhí)行計(jì)劃是SQL查詢(xún)處理器執(zhí)行的不同操作的圖形表示。當(dāng)您執(zhí)行任何查詢(xún)時(shí)，查詢(xún)處理器將生成一個(gè)執(zhí)行計(jì)劃以及要啟動(dòng)的查詢(xún)。基本上，它提供了兩個(gè)主要的執(zhí)行計(jì)劃。
估計(jì)執(zhí)行計(jì)劃
這種類(lèi)型的計(jì)劃是在查詢(xún)執(zhí)行之前生成的，或者可以說(shuō)是在編譯期間生成的。
這只是查詢(xún)處理器的估計(jì)。
與此不提供任何運(yùn)行時(shí)信息。
實(shí)際執(zhí)行計(jì)劃
這種類(lèi)型的計(jì)劃是在執(zhí)行查詢(xún)后生成的，或者可以說(shuō)是在運(yùn)行時(shí)間之后生成的。
它由查詢(xún)處理器提供實(shí)際信息。
它提供了所有信息，例如執(zhí)行該查詢(xún)時(shí)涉及的步驟。
3.如何創(chuàng)建一個(gè)SQL執(zhí)行計(jì)劃？
當(dāng)您執(zhí)行任何查詢(xún)時(shí)，會(huì)生成一個(gè)執(zhí)行計(jì)劃，該查詢(xún)必然包含該查詢(xún)以及該計(jì)劃。還有許多其他方式可以執(zhí)行此計(jì)劃。就像您可以使用“菜單”按鈕，工具欄按鈕和快捷鍵一樣。有關(guān)更多詳細(xì)信息，讓我們深入了解以下過(guò)程。
快捷鍵：有一個(gè)快捷鍵可用于檢查“估計(jì)執(zhí)行”計(jì)劃。在“查詢(xún)”窗口中編寫(xiě)查詢(xún)后，可以按Ctrl + L。
在查詢(xún)窗口的上下文菜單中，您將找到一個(gè)名為“顯示估計(jì)的執(zhí)行計(jì)劃”的菜單。它的工作方式與上述步驟相同。它將顯示“估計(jì)執(zhí)行計(jì)劃”。另外，在“查詢(xún)”菜單中，有一個(gè)選項(xiàng)可用，名為“顯示估計(jì)的執(zhí)行計(jì)劃”。
在工具欄中，有一個(gè)帶有此圖像的按鈕。圖像這就是“實(shí)際執(zhí)行”計(jì)劃的確切功能，這里有一個(gè)快捷鍵，即Ctrl +M。
（注意：如果該按鈕不存在，請(qǐng)選中“添加或刪除按鈕”。此外，在“查詢(xún)”菜單中，有一個(gè)名為“包括實(shí)際執(zhí)行計(jì)劃”的選項(xiàng)。
估計(jì)執(zhí)行計(jì)劃示例
實(shí)際執(zhí)行計(jì)劃示例
正如您在示例中看到的那樣，它顯示了實(shí)際執(zhí)行計(jì)劃。我們已經(jīng)執(zhí)行了查詢(xún)，并且有3個(gè)選項(xiàng)卡可用。如果您檢查差異，它將顯示您花費(fèi)的掃描時(shí)間。我們可以將執(zhí)行計(jì)劃分為三個(gè)部分。
聚集索引掃描（聚集）
排序操作
選擇操作
4. SQL執(zhí)行計(jì)劃的組成部分是什么？
由于表中沒(méi)有可用的數(shù)據(jù)，它是一個(gè)簡(jiǎn)單的查詢(xún)，因此估算的執(zhí)行計(jì)劃和實(shí)際的執(zhí)行計(jì)劃將是相同的，但是如果您使用大型查詢(xún)，則會(huì)發(fā)現(xiàn)兩者之間的差異。您可以使用它來(lái)優(yōu)化查詢(xún)。
當(dāng)您將鼠標(biāo)懸停在“聚集索引掃描”上時(shí)，將提供詳細(xì)的結(jié)果?？纯聪旅娴慕貓D。
如果您在上圖中看到，SQL提供了不同的詳細(xì)信息。我們將詳細(xì)討論所有內(nèi)容。
物理操作：物理操作員是執(zhí)行此類(lèi)操作的對(duì)象。其中一些示例是“索引查找”，“聚簇索引掃描”等。邏輯運(yùn)算符正在指導(dǎo)此類(lèi)運(yùn)算符執(zhí)行已定義的操作。
邏輯操作：在物理操作中，我們的軟件開(kāi)發(fā)人員使用邏輯操作員的工作。它還清楚地說(shuō)明了必須進(jìn)行哪些查詢(xún)以及如何執(zhí)行查詢(xún)。
實(shí)際執(zhí)行模式：本節(jié)將描述要執(zhí)行的實(shí)際計(jì)劃。處理引擎使用它，并執(zhí)行查詢(xún)。
估計(jì)執(zhí)行模式：與上述計(jì)劃類(lèi)似，但唯一的不同是它顯示了估計(jì)值。
存儲(chǔ)：查詢(xún)觸發(fā)的輸出就像從查詢(xún)中提取的優(yōu)化程序一樣。
所有執(zhí)行的合法事實(shí)和數(shù)字-實(shí)際計(jì)劃將顯示執(zhí)行計(jì)劃中的所有實(shí)際數(shù)字和數(shù)字。根據(jù)這種情況，我們將不會(huì)獲得任何記錄，也不會(huì)獲得任何回報(bào)。
實(shí)際批次數(shù)：僅在實(shí)際執(zhí)行計(jì)劃中存在。如果是批處理查詢(xún)，則將返回“無(wú)批處理”。
估計(jì)的運(yùn)營(yíng)成本：如果查詢(xún)中涉及其他任何運(yùn)營(yíng)成本，則將對(duì)此進(jìn)行計(jì)算并顯示在此處。
估計(jì)的I / O成本：它顯示輸入和輸出成本的準(zhǔn)確數(shù)量。
估計(jì)的CPU成本：它估計(jì)使用CPU執(zhí)行操作的成本。
估計(jì)的子樹(shù)成本：生成執(zhí)行計(jì)劃時(shí)，它將生成樹(shù)。從現(xiàn)在開(kāi)始，您將可以計(jì)算
執(zhí)行次數(shù)：僅在實(shí)際執(zhí)行計(jì)劃中存在。在單個(gè)批處理中，優(yōu)化程序可以處理的執(zhí)行次數(shù)。
估算的執(zhí)行次數(shù)：與上述估算的執(zhí)行次數(shù)相似，但唯一的區(qū)別在于它將為您提供估算的值。
每次執(zhí)行的估計(jì)行數(shù)：這只是來(lái)自O(shè)ptimizer的估計(jì)，即將返回多少行。
預(yù)計(jì)要讀取的行數(shù)：這只是來(lái)自O(shè)ptimizer的估計(jì)，即將讀取多少行。
估計(jì)行大?。侯櫭剂x，它向您顯示存儲(chǔ)的估計(jì)行大小。
實(shí)際重新綁定-在實(shí)際執(zhí)行計(jì)劃期間將處于活動(dòng)狀態(tài)。它提供了信息，例如必須重新評(píng)估對(duì)象處理多少次。
倒退實(shí)際編號(hào)：這部分將在實(shí)際執(zhí)行計(jì)劃中再次重復(fù)。
在關(guān)聯(lián)操作中，通過(guò)重復(fù)使用內(nèi)部結(jié)果數(shù)據(jù)集來(lái)執(zhí)行總行數(shù)。
已排序：確定執(zhí)行操作的數(shù)據(jù)集是否已實(shí)現(xiàn)排序。如果您在上面的示例中簽入，則會(huì)給您False，因?yàn)榈侥壳盀橹股形催M(jìn)行排序。一旦排序完成，那就是正確的。
節(jié)點(diǎn)ID：這是從右到左，然后是通常的從上到下的唯一編號(hào)類(lèi)型。因此，我們可以說(shuō)，基于執(zhí)行計(jì)劃樹(shù)，右下角將具有NodeID = 1，左上角將具有最大節(jié)點(diǎn)數(shù)。
在“添加”中，還有另外兩個(gè)組件。我們可以在下面查看其信息。
對(duì)象：定義我們對(duì)其執(zhí)行查詢(xún)或操作的表。
輸出列表：定義選定的列，這些列將顯示在數(shù)據(jù)集或結(jié)果中。
在表格中搜索數(shù)據(jù)
現(xiàn)在，我們將通過(guò)一個(gè)示例來(lái)更深入地研究這個(gè)問(wèn)題，因此在開(kāi)始之前，讓我們梳理一下有關(guān)索引以及索引之間的比較的知識(shí)。
表格掃描：在這種類(lèi)型的掃描中，無(wú)論其是否符合給定的搜索結(jié)果，都以觸及表格的每一行的方式全面執(zhí)行掃描。這種類(lèi)型的掃描是檢查小型表的有效方法，在該表中，大多數(shù)行都符合謂詞的條件。估計(jì)成本將與表中的總行數(shù)成比例。
索引掃描：如果表具有聚集索引，則執(zhí)行的查詢(xún)應(yīng)覆蓋所有行和列。因此，建議觸發(fā)一個(gè)查詢(xún)，該查詢(xún)將覆蓋表的大多數(shù)行或幾乎所有行。即沒(méi)有WHERE或Getting子句的查詢(xún)，將使用索引搜索。在數(shù)據(jù)庫(kù)優(yōu)化的過(guò)程中，查詢(xún)優(yōu)化器從可用索引中選擇最佳的索引。并且基于該信息，在掃描整個(gè)表時(shí)會(huì)清楚地定義子句功能。
此子句保留數(shù)據(jù)庫(kù)的統(tǒng)計(jì)信息。
選擇正確的索引的那一刻。下一步是將樹(shù)結(jié)構(gòu)導(dǎo)航到所有匹配的數(shù)據(jù)點(diǎn)，并使用SQL查詢(xún)處理器或引擎提取確切的記錄。
全表搜索和索引掃描之間的主要區(qū)別之一是，當(dāng)數(shù)據(jù)在索引樹(shù)中排序時(shí)，數(shù)據(jù)庫(kù)引擎會(huì)了解何時(shí)達(dá)到極限并仍在尋找極限。然后，它可以提交問(wèn)題，或者在適當(dāng)時(shí)傳遞到下一個(gè)數(shù)據(jù)集。
尋求索引：成本與表中符合條件的行和頁(yè)面的數(shù)量成正比，而不是與表中的總行數(shù)成正比，因?yàn)橹挥蟹蠗l件的行和包含這些符合條件的行的頁(yè)面受搜索這三者的影響，因此最快的一個(gè)。
5.執(zhí)行計(jì)劃如何提高查詢(xún)性能？
根據(jù)執(zhí)行計(jì)劃中顯示的上圖，有4個(gè)不同的查詢(xún)，但有一些細(xì)微的變化。讓我們一一看一下，并嘗試了解執(zhí)行計(jì)劃可以帶來(lái)哪些改進(jìn)。
查詢(xún)1：從Department WHERE DepartmentName ='HR'中選擇DepartmentID，DepartmentName
該表沒(méi)有定義任何主鍵，因此沒(méi)有創(chuàng)建任何聚集索引。這將執(zhí)行完整的表掃描，這在第一個(gè)執(zhí)行計(jì)劃中可見(jiàn)。如果表中的記錄數(shù)為百萬(wàn)，則此查詢(xún)將花費(fèi)最長(zhǎng)時(shí)間。
查詢(xún)2： SELECT EmployeeID，EmployeeName，DepartmentID，BirthDate FROM Employee WHERE DepartmentID = 3
該查詢(xún)正在執(zhí)行索引掃描，這比表掃描快一點(diǎn)，因?yàn)樗鼘⒁酝ㄟ^(guò)聚簇索引存儲(chǔ)數(shù)據(jù)的方式將數(shù)據(jù)排序。如果表中包含大量數(shù)據(jù)，速度仍然會(huì)較慢。
查詢(xún)3： SELECT * FROM Employee WHERE BirthDate ='1982-08-07'我在BirthDate列上創(chuàng)建了一個(gè)非聚集索引，如上圖所示。請(qǐng)注意，這些列顯示在下圖中的包含選項(xiàng)卡中。這意味著，只有在select子句中選擇了很多列并且WHERE子句位于Birthdate列上時(shí)，才能執(zhí)行索引查找。
第三個(gè)執(zhí)行計(jì)劃仍在顯示索引掃描。使困惑！請(qǐng)參考最佳做法部分中的第一個(gè)要點(diǎn)，以獲得高性能。您可以在SELECT子句中編寫(xiě)*，而不是特定的列。這阻止了索引查找功能，并且我們沒(méi)有收到預(yù)期的期望性能。盡管在數(shù)據(jù)庫(kù)中創(chuàng)建了非集群。現(xiàn)在，讓我們檢查最終查詢(xún)。
查詢(xún)4：從員工WHERE BirthDate ='1982-08-07'中選擇EmployeeID，EmployeeName，DepartmentID，BirthDate
您會(huì)注意到，由于我們遵循了最佳實(shí)踐，并且已準(zhǔn)確創(chuàng)建了非聚集索引，因此該查詢(xún)已使用了索引查找。
為了確定性能改進(jìn)，應(yīng)該了解最佳實(shí)踐和有關(guān)SQL Server的深入知識(shí)。執(zhí)行計(jì)劃通過(guò)其圖形表示幫助查找丟失的內(nèi)容，因此人們可以輕松地找到操作項(xiàng)以提高特定查詢(xún)的性能。我希望這個(gè)例子能對(duì)如何找出查詢(xún)中的改進(jìn)有所啟發(fā)。
6.結(jié)論
在本文中，我們進(jìn)行了廣泛的研究，并獲得了有關(guān)執(zhí)行計(jì)劃的見(jiàn)解。關(guān)于如何生成執(zhí)行計(jì)劃，計(jì)劃的執(zhí)行計(jì)劃與實(shí)際執(zhí)行計(jì)劃之間的差異以及執(zhí)行計(jì)劃節(jié)點(diǎn)的各個(gè)組成部分，一些秘密策略是已知的。我們還了解了執(zhí)行計(jì)劃如何幫助查找查詢(xún)中的改進(jìn)。該工具對(duì)于DBA應(yīng)對(duì)日常挑戰(zhàn)以及涉及大數(shù)據(jù)的問(wèn)題非常有用，因此您可以再次檢查執(zhí)行計(jì)劃并在需要時(shí)優(yōu)化查詢(xún)。