MySql學習(五) —— 資料庫最佳化理論篇(一)

標籤：

 

一、資料庫管理系統

資料庫管理系統(Database Management System, DBMS)

衡量是否是資料庫的標準：

　ACID：是指在資料庫管理系統(DBMS)中事務所具有的四個特性：

　　1) 原子性(Atomicity)　　

　　2) 一致性(Consistency)

　　3)隔離性(Isolation)

　　4)持久性(Durability)

 

1、關係型資料庫：是建立在關聯式資料庫模型基礎上的資料庫，藉助於關係代數等概念和方法來處理資料庫中的資料，同時也是一個被組織成一組擁有正式描述性的表格，該形式的表格作用的實質是裝載著資料項目的特殊收集體，這些表格中的資料能以許多不同的方式被存取或重新召集而不需要重新組織資料庫表格。

 

2、關係代數：

　　在數學中，關係代數是支援叫做逆反(converse)的對合一運算的剩餘布爾代數。激發關係代數的例子是在集合X上的所有二元關係的代數，帶有R-S被解釋為平常的二元關係複合。

　　在資料庫中，關係代數是一階邏輯的分支，是閉合於運算下的關係的集合。運算作用於一個或多個關係上來產生一個關係。關係代數是電腦科學的一部分。

　　在純數學中的關係代數是有關於數理邏輯和集合論的代數結構。

　　關係代數是一種抽象的查詢語言，用對關係的運算來表達查詢，作為研究關係資料語言的數學工具。

　　關係代數的運算對象是關係，運算結果亦為關係。關係代數用到的運算子包括四類：集合運算子、專門的關係運算子、算數比較符、邏輯運算子；

　　資料庫關係代數的留個運算：選擇、投影、笛卡爾積(也叫"叉積"或"交叉串連")、並集、差積、重新命名；

　　　　

 

　　SQL的查詢語言鬆散的基於關係代數，儘管SQL中的運算元(表)不完全是關係，很多有用的關於關係代數的理論在SQL對應者中不成立。

 

二、資料庫調優

1、查詢最佳化技術：

　　資料庫查詢最佳化技術主要包括查詢重用技術、查詢重寫規則、查詢演算法最佳化技術、並行查詢最佳化技術、分散式查詢最佳化技術、及其它方面(如架構結構)的最佳化技術，這6項技術構成了一個"廣義的資料庫查詢最佳化"的概念。

　　從最佳化的內容角度看，查詢最佳化又分為代數最佳化和非代數最佳化，或稱為邏輯最佳化或物理最佳化。邏輯最佳化主要依據關係代數的等價變換做一些邏輯變換；物理最佳化主要根據資料讀取、表串連方式、表串連順序、排序等技術對查詢進行最佳化。"查詢重寫規則"屬於邏輯最佳化，運用了關係代數和啟發學習法規則；"查詢演算法最佳化"屬於物理最佳化，運用了基於代價估算的多表串連演算法求解最小花費的技術。

 

2、資料庫調優(Database Tuning)：

　　資料庫調優可以使資料庫應用運行得更快，其目標是使資料庫有更高的輸送量(在單位時間內完成的事務越多越好)、更短的回應時間(每一個事務響應的時間越短越好)。

　　被調優的對象是整個資料庫管理系統總體。

　　查詢語句調優的對象是一條查詢語句。

 

3、資料庫調優的方式：

　　1)人工調優：主要依賴於人，效率低下；要求操作者完全理解常識所依賴的原理，還需要對應用、資料庫管理系統、作業系統以及硬體有廣泛而深刻的理解。

　　2)基於案例的調優：總結經典案例情況中資料庫參數的推薦配置值、資料邏輯層設計等情況，從而為使用者的調優工作提供一定的參考和借鑒。但這種方式忽略了系統的動態性和不同系統間存在的差異。

　　3)自調優：為資料庫系統建立一個模型，根據"影響資料庫系統效能效率的因素"，資料庫系統自動進行參數配置。

 

4、資料庫調優主要分為五個階段：

　　1)第一階段：需求分析期，應用情況的估算、系統選型策略。

　　2)第二階段：項目設計期，資料模型的設計。

　　3)第三階段：開發期，SQL設計、資料庫功能的啟用。

　　4)第四階段：測試與運行，資料庫功能的啟用、模型系統預運行、系統監控與分析。

　　5)第五階段：上線與維護，系統監控與分析。

 

5、資料庫調優五個階段的主要技術：

　　1)應用情況的估算：應用的使用方式(把商務邏輯轉換為資料庫的讀寫分布邏輯，以是讀多寫少還是讀寫均衡等來區分OLTP和OLAP；應用對資料庫的並發情況、並發是否可以池化等)、資料量、對資料庫的壓力、峰值壓力等做一個預估。

　　2)系統選型策略：確定什麼樣的資料庫可以適用應用需求，並確定資料庫是使用開源的還是商業的，是集使用群還是單機的系統，同時對作業系統、中介軟體、硬體、網路等進行選型。

　　3)資料模型的設計：主要是根據商務邏輯設計，從幾個角度考慮表的邏輯結構，內容如下：

　　　　a)E-R模型設計：遵循E-R模型設計原理。但偶爾的適當程度的非正常化可以改善系統查詢效能。

　　　　b)資料邏輯分布策略：目的是減少資料請求的不必要的資料量，把使用者需要的資料返回；可用的技術如分區、用E-R模型分表等(如互連網企業典型的用法，根據業務的不同，進行分庫、分表等操作)。

　　　　c)資料實體儲存體策略：目的是減少IO，如啟用壓縮技術、把索引和表資料的儲存分開，不同的表資料分佈於不同的資料表空間，不同的資料表空間分布在不同的實體儲存體上(尤其是讀寫量大的資料表空間分布在不同的實體儲存體上)、日誌、索引和資料分布在不同的實體儲存體上等。

　　　　d)索引：在查詢頻繁的對象上建立合適的索引，使索引的正效應大於負效應(索引的維護存在消耗)。

　　4)SQL設計：編寫正確的、查詢效率高的SQL語句。這主要依據"查詢重寫規則"，編寫語句的過程中要注意，要有意識地保障SQL能利用到索引。

　　5)資料庫功能的啟用：資料庫為提高效能提供了一些功能，可合理使用。

　　　　a)查詢重用：根據實際情況進行配置，可緩衝查詢執行計畫、查詢結果等。

　　　　b)資料庫參數的設定：可設定合適的參數如資料緩衝區等。

　　6)模型系統預運行。在備用系統上類比實際運行環境，加大壓力進行系統測試，提前發現問題。

　　7)系統監控與分析：在工業環境下，加強對系統的運行監控和日常的分析工作，具體如下：

　　　　a)應用系統資料表現：收集使用者對應用系統的使用意見、系統存在的問題等。

　　　　b)OS環境監控：即時監控CPU、記憶體、IO等，並對比即時情況與 曆史正常情況等。

　　　　c)資料庫內部狀況監控：一些資料庫提供系統資料表、視圖、工具等手段，向使用者提供資料庫運行過程中內部狀況的資訊，如鎖的資訊，這些都需要即時監控，並對比即時情況與曆史正常情況等。

　　　　d)日誌分析：在資料庫的日誌、作業系統的日誌中找出例外狀況事件，定位問題。

 

三、查詢最佳化

1.查詢重用

　　查詢重用是指儘可能利用先前的執行結果，以達到節約查詢計算全過程的時間並減少資源消耗的目的。

　　查詢重用技術主要集中在兩個方面：

　　　　a) 查詢結果的重用：在緩衝區中分配一塊緩衝塊，存放該SQL語句文本和最後的結果集，當同樣的SQL輸入時，直接把結果返回。查詢結果的重用技術節約了查詢計劃產生時間，減少了查詢執行全過程的資源消耗。

　　　　b) 查詢計劃的重用：緩衝一條查詢語句的執行計畫及其相應文法樹結構。查詢計劃的重用技術減少了查詢計劃產生的時間和資源消耗。

　　查詢重用技術利弊：

　　　　a) 優點：節約了CPU和IO消耗。在實際的使用過程中，趨利避害，根據實際情況選用。

　　　　b) 缺點：結果集很大的話會消耗很大的記憶體資源，同樣的SQL不同使用者擷取的結果集可能不同。

 

2.查詢最佳化技術類型

　　a) 文法級：查詢語言層的最佳化，基於文法進行最佳化。

　　b) 代數級：查詢使用形式邏輯進行最佳化，運用關係代數的原理進行最佳化。

　　c) 語義級：根據完整性條件約束，對查詢語句進行語義理解，推知一些可最佳化的操作。

　　d) 物理級：物理最佳化技術，基於代價估算模型，比較得出各種執行方式中代價最小的。

 

3.查詢重寫

　　是查詢語句的一種等價轉換，即對於任何相關模式的任意狀態都會產生相同的結果。

　　查詢重寫的兩個目標：

　　　　a) 將查詢轉換為等價的效率更高的形式，例如將效率低的謂詞轉換為效率高的謂詞、消除重複條件等。

　　　　b) 盡量將查詢重寫為等價、簡單且不受表順序限制的形式，為物理查詢最佳化階段提供更多的選擇，如視圖的重寫、子查詢的合并轉換等。

　　查詢重寫的依據：

　　　　查詢重寫的依據是關係代數。

　　　　a) 關係代數的等價變換規則對查詢重寫提供了理論上的支援。

　　　　b) 查詢重寫後，查詢最佳化工具可能產生多個串連路徑，可以從候選者中擇優。 

　　查詢重寫是基於文法級、代數級、語義級的最佳化，可以統一歸屬到邏輯最佳化的範疇；基於代價估算模型是物理層面的最佳化，是從串連路徑中選擇代價最小的路徑的過程。

　　查詢重寫思路：

　　　　a) 將過程性查詢轉換為描述性查詢，如視圖重寫。

　　　　b) 將複雜的查詢(如嵌套子查詢、外串連消除、嵌套串連消除)儘可能轉換為多表串連查詢。

　　　　c) 將效率低的謂詞轉換為等價的效率高的謂詞(如等價謂詞重寫)。

　　　　d) 利用等式和不等式的性質，簡化where、having條件。

 

4.查詢最佳化演算法

　　查詢最佳化，求解給定查詢語句的高效執行計畫的過程。這樣的過程，包括了多種子問題求解。不同的子問題，對應了不同的解決辦法，即演算法。

　　

5.查詢計劃

　　查詢計劃，也稱為查詢樹，它由一系列內部的操作符組成，這些操作符按一定的運算關係構成查詢的一個執行方案。 簡單說，就是A表和B表先串連得到中間結果，然後再和另外的C表串連得到新的中間方式，直至所有表都被串連完畢。

• 查詢計劃，二叉樹上的不同節點：

　　　　a) 單表節點：

　　　　　　考慮單表的資料擷取方式：

　　　　　　1)直接通過IO獲得資料。

　　　　　　2)通過索引擷取資料。

　　　　　　3)通過索引定位元據的位置後再經過IO到資料區塊中擷取資料。

　　　　　　這是一個從實體儲存體到記憶體解析成邏輯欄位的過程，即符合馮·諾依曼體繫結構的要求(外存資料讀入記憶體才能被處理)

　　　　b) 兩表節點(兩表串連節點)

　　　　　　1)考慮兩表以何種方式串連、代價有多大、串連路徑有哪些等。表示的是記憶體中的元組，怎麼進行元組間的串連。此時，元組通常已經存在於記憶體，直接使用即可。這是一個完成使用者語義的邏輯操作，但是只是局部操作，只涉及兩個具體的關係。完成使用者全部語義(使用者串連的語義)，需要配合多表的串連順序的操作。

　　　　　　2)不同的串連演算法導致的串連效率不同，如資料量大可使用嵌套串連，資料如果有序可使用歸併串連，或先排序後使用歸併串連等。

　　　　c) 多表節點

　　　　　　1)考慮多表串連順序如何構成代價最少的"執行計畫"。決定是AB先串連還是BC先串連，這是一個比較花費大小的運算。如果太多的串連方式被判斷，也會導致效率問題。

　　　　　　2)多個關係採用不同次序進行串連，花費的CPU資源、記憶體資源差異可能較大。

　　　　　　3)許多資料庫採用左深樹、右深樹、緊密樹三種方式或其中一部分對多表進行串連得到多種串連路徑。

• 產生最優查詢計劃的策略：

　　　　a)基於規則最佳化：根據經驗或一些已經探知或被證明有效方式，定義為"規則"(如根據關係代數得知的規則、根據經驗得知的規則等)，用這些規則化簡查詢計劃產生過程中符合可被簡化的操作，使用啟發學習法規則排除一些明顯不好的存取路徑，這就是基於規則的最佳化。

　　　　b)基於代價最佳化：根據一個代價評估模型，在產生查詢計劃的過程中，計算每條存取路徑(存取路徑主要包括上述三個關聯節點)的花費，然後選擇代價最小的作為子路徑，這樣直至所有表串連完畢得到一個完整的路徑。

　　　　　　代價計算公式：總代價 = CPU代價 + IO代價；

　　　　c)主流資料庫對於基於規則和基於代價的技術，都在使用。

　　　　　　基於規則最佳化具有操作簡單且能快速確定串連方式的優點，但這種方法只是排除了一部分不好的可能，所以得到的結果未必是最好的。

　　　　　　基於代價最佳化，是對各種可能的情況進行量化比較，從而可以得到花費最小的情況，但如果組合情況比較多則花費的判斷時間就會很多。

　　　　　　查詢最佳化工具的實現，多是兩種最佳化策略組合使用，如MySql的PostgreSQL就採取了基於規則和代價估算的查詢最佳化策略。

• 查詢最佳化為什麼要並行？

　　　   a)傳統單機資料庫系統中，給定一個查詢(Query)，查詢最佳化演算法只需找到查詢的一個具有最小執行花費的執行計畫，這樣的計劃必定具有最快的回應時間。

　　　　b)在並行資料庫系統中，查詢最佳化的目標是尋找具有最小回應時間的查詢執行計畫，這需要把查詢工作分解為一些可以並行啟動並執行子工作。一些商務資料庫提供了並行查詢的功能，用以最佳化查詢執行操作。

• 查詢最佳化並行的條件

　　　　a)系統中的可用資源(如記憶體，快取中的資料量等)

　　　　b)CPU的數目

　　　　c)運算中的特定代數運算子

　　　　如A、B、C、D四個表進行串連，每個表的單表掃描可以並行進行；在產生四個表串連的查詢計劃過程中，可選擇A和B串連的同時C和D進行串連，這樣串連操作能並行運行(操作間並行)。

• 在同一個SQL內，查詢並行可以分為：

　　　　a)操作內並行：將同一操作如單表掃描操作、兩表串連操作、排序操作等分解成多個獨立的子操作，由不同的CPU同時執行。

　　　　b)操作間並行：一條SQL查詢語句可以分解成多個子操作，由多個CPU執行。

• 在分散式資料庫系統中，查詢策略最佳化是查詢最佳化的重點

　　　　主要是資料轉送策略，(A、B兩節點的資料進行串連，是A節點資料轉送到B節點或是從B到A或是先各自進行過濾然後再傳輸等) 和局部處理最佳化(傳統的單節點資料庫的查詢最佳化技術)　　　　

　　在查詢最佳化策略中，資料的通訊開銷是最佳化演算法考慮的主要因素。分散式查詢最佳化以減少傳輸的次數和資料量作為查詢最佳化的目標。  分散式資料庫系統中的代價估算模型，除了考慮CPU代價和IO代價外，還要考慮通過網路在節點間傳輸資料的代價。這是分布式並行查詢最佳化技術與傳統單節點資料庫系統最大不同之處。

　　在分散式資料庫系統中，代價估算模型為： 總代價 = IO代價 + CPU代價 + 通訊代價  (通訊代價通常會大於IO代價和CPU代價)

 

四、邏輯查詢最佳化 

　　1.查詢的基本操作

　　　　a)選擇操作：對應的是限制條件(=, >, < 等)，操作對象是二維表中的行。

　　　　　　最佳化方式：選擇操作下推 (如A.a=B.a AND A.a=1 可轉換成A.a=1 AND B.a=1，這樣就將A、B表的選擇條件下推到各自對應的表中去)。

　　　　　　目的：盡量減少串連操作前的元組數，使得中間臨時關係盡量少(元組少，串連得到的元組數就少)。

 　　　　　  好處：這樣可能減少IO和CPU的消耗、節約記憶體空間。

　　　　b)投影操作：對應的SELECT查詢的目的列對象。

　　　　　　最佳化方式：投影操作下推

　　　　　　目的：盡量減少串連操作前的列數，使得中間臨時關係盡量小(注意：選擇操作是使元組的個數“盡量少”，投影操作是使一條元組“盡量小”)。

　　　　　　好處：這樣雖然不能減少IO (多數資料庫儲存方式是行儲存，元組是讀取的最基本單位，所以要想操作列則必須讀取一行資料)，但可以減少串連後的中間關係的元組大小，節約記憶體空間。

　　　　c)串連操作：對應的是串連條件，表示兩個表串連的條件。

　　　　　　串連操作涉及兩個子問題：

　　　　　　　　1)多表串連中每個表被串連的順序決定著效率：如果一個查詢語句只有一個表，則這樣的語句很簡單；但如果有多個表，則會涉及表之間以什麼樣的順序串連最高效。

　　　　　　　　2)多表串連每個表被串連的順序被使用者語義定義：查詢語句多表串連有著不同的語義(如是 笛卡爾積、內串連、左右串連等)，這決定著表之間的前後串連次序是不能隨意更換的，否則，結果集中資料是不同的。因此，表的前後串連次序是不能隨意交換的。　　

　　2.使用explain來擷取select的詳細資料

　　　　文法：EXPLAIN [explain_type] <select expression>;    explain_type 可以有如下：extended | partitions | (format=traditional | json)；

　　　　EXPLAIN命令，用於顯示SQL語句的查詢執行計畫。EXPLAIN為用於SELECT語句中的每個表返回一行資訊。

　　　　EXPLAIN EXTENDED命令：顯示SQL語句的詳細的查詢執行計畫；之後可以通過"SHOW WARNINGS"命令查看詳細資料。

　　　　EXPLAIN PARTITIONS命令：顯示SQL語句的帶有分區表資訊的查詢執行計畫。

　　　　EXPLAIN命令的輸出格式：

　　　　　　TRADITIONAL：傳統類型，按行隔離，每行標識一個自操作。

　　　　　　JSON：JSON格式。

 

　　3.邏輯查詢最佳化包括的技術

　　　　a)子查詢最佳化  b)視圖重寫  c)等價謂詞重寫  d)條件簡化  e)外串連消除  f)嵌套串連消除  g)串連消除  h)語義最佳化  i)非SPJ最佳化

 

　　　

 

MySql學習(五) —— 資料庫最佳化理論篇(一)