SQL效能第1篇：關係優化

女主宣言

本文旨在讓大家瞭解關係優化的相關內容，包括它的需求和需要考慮的重要問題。在下一部分中，我們將研究查詢分析和優化器可以部署的一些方法，以制定SQL訪問路徑。希望對大家在SQL效能優化方面有所幫助。

應用程式開發人員必須編寫高效的SQL程式碼，並瞭解如何優化SQL以開發高效的應用程式。但不僅僅是程式設計師，DBA也必須理解SQL優化。這是一個共同的責任，通常需要健壯的工具來幫助有效地編碼、修改和優化SQL。

關係優化器是DBMS的核心。它是一個推理引擎，負責為任何給定的SQL請求確定最佳的資料庫導航策略。開發人員通過編碼SQL語句指定需要哪些資料，DBMS訪問關於資料所在位置的後設資料，關係優化器決定如何有效地導航資料庫。終端使用者不需要知道實際資料儲存在哪裡以及如何儲存。優化器知道這些資訊。

為了優化SQL，關係優化器必須通過解析每個SQL語句來分析它，以確定必須訪問的表和列。優化器還將訪問儲存在系統目錄或資料庫物件本身中的統計資訊。統計資訊用於確定完成滿足SQL請求所需執行的任務的最佳方法。這個過程稱為關係優化。

關係優化非常強大，因為它允許查詢適應不斷變化的資料庫環境。優化器可以通過制定新的訪問路徑來響應更改，而不需要實現應用程式編碼更改。因此，當表在大小上展開或收縮、索引被新增或刪除、資料庫變得無序或重新組織時，應用程式可以是靈活的。

無論資料是如何物理儲存和操作的，都可以使用SQL來訪問資料，DBMS將考慮資料庫的當前狀態來優化資料訪問。這種訪問標準與物理儲存特徵的分離稱為物理資料獨立性。

每個關聯式資料庫系統都依賴優化器將SQL語句呈現為可執行訪問路徑。此外，每個供應商的關係優化器的工作方式略有不同，使用不同的步驟和不同的資訊。然而，從資料庫管理系統到資料庫管理系統的過程是相同的。優化器解析SQL語句並執行各種階段的優化，通常包括驗證語法和語義的正確性，然後是查詢分析和制定滿足查詢的訪問路徑。

關係優化器可以部署許多型別的策略來優化SQL語句。每個DBMS優化器使用的內部操作和指令都是嚴格保守的祕密。現代關係優化器是基於成本的，這意味著優化器將嘗試為每個查詢制定降低總體成本的訪問路徑。要以這種方式工作，優化器必須評估和分析多個因素，包括估計的CPU和I/O成本、資料庫統計資訊和實際的SQL語句。

1   CPU和I/O成本

優化器使用公式和模型來估計執行正在優化的查詢的每個潛在訪問路徑的機器成本。基於CPU資訊，優化器可以粗略估計使用它分析的每個優化訪問路徑執行查詢所需的CPU時間。

此外，關係優化器必須估算實際寫入和檢索資料的成本。優化器根據資料庫統計資料、資料快取效率和中間工作檔案的I/O成本，使用一系列公式估算查詢的I/O成本。這些公式會產生一個篩選因子，它決定查詢的相對I/O成本。

2    資料庫統計

如果沒有關於儲存在資料庫中的資料的準確統計，關係優化器幾乎沒有用處。關係型DBMS提供了一個實用程式或命令來收集關於資料庫物件的統計資訊，並儲存它們以供優化器(或DBA用於效能監視)使用。例如，要在DB2中收集統計資訊，DBA必須執行RUNSTATS實用程式;要在SQL Server中收集統計資訊，發出UPDATE STATISTICS命令。

每當新增或修改了大量資料時，都需要收集修改後的統計資訊。如果做不到這一點，將導致優化器基於不準確的統計資料估算成本。這可能不利於查詢效能。

資料庫統計資訊向優化器提供關於表空間、表、列和索引的狀態的資訊。資料庫管理系統收集統計資訊，如：

• 表空間、表或索引中的行數

• 儲存在列中的惟一值的數目

• 列最常見的值

• 索引鍵密度，或儲存在索引鍵列中的重複值的平均百分比

• 有關群集表的群集比率的詳細資訊

• 列與其他列的相關性

• 索引或表空間的結構狀態

• 資料庫物件使用的儲存量

並非每次請求新的統計資料時都會收集所有統計資料;您可以指定要收集哪種型別的資料庫統計資訊。當然，收集到的準確統計資料因資料庫管理系統的不同而不同;您的資料庫系統中可能有更多或更少的統計資訊。但是，關鍵是保持統計資料儘可能準確，以確保有效和有用的關係優化。

在針對測試資料庫開發應用程式時，測試資料的統計資訊不能準確反映生產資料庫的統計資訊。只要可能，DBA應該與應用程式開發團隊一起建立一個指令碼，將生產統計資訊填充到測試系統中。根據DBMS的不同，這可以通過SQL語句或資料測試工具來完成。如果沒有生產統計資訊，DBMS可能會在測試環境中選擇不同的訪問路徑，而不是在生產環境中選擇的訪問路徑——這可能會導致效能問題。

總結

在本篇中，我們介紹了關係優化的主題，包括它的需求和需要考慮的重要問題。在下一部分中，我們將研究查詢分析和優化器可以部署的一些方法，以制定SQL訪問路徑。希望對大家在SQL效能優化方面有所幫助。

HULK一線技術雜談

由360雲平臺團隊打造的技術分享公眾號，內容涉及雲端計算、資料庫、大資料、監控、泛前端、自動化測試等眾多技術領域，通過夯實的技術積累和豐富的一線實戰經驗，為你帶來最有料的技術分享

原文連結：https://mp.weixin.qq.com/s/pSIOl6kLsrujvprHehk_Pw

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/31555491/viewspace-2284238/，如需轉載，請註明出處，否則將追究法律責任。