數據庫查詢優化技術/li_hx/TheQueryOptimizationTechniqueOfMySQL(5.6.X)@那海藍藍1數據庫查詢優化技術/li_hx/1Adatatechnologylearners,practitionersBook:《數據庫查詢優化器的藝術：原理解析與SQL性能優化》23Blog:/li_hx4Twitter：@那海藍藍Email:database_XX@126.com5Direction:DB(PostgreSQL,MySQL.etc)63/li_hx/課程大綱：1數據庫與關系代數2數據庫查詢優化技術總攬3查詢優化技術理論與MySQL實踐（一）------子查詢的優化（一）4查詢優化技術理論與MySQL實踐（二）------子查詢的優化（二）5查詢優化技術理論與MySQL實踐（三）------視圖重寫與等價謂詞重寫6查詢優化技術理論與MySQL實踐（四）------條件化簡7查詢優化技術理論與MySQL實踐（五）------外連接消除、嵌套連接消除與連接消除8查詢優化技術理論與MySQL實踐（六）------數據庫的約束規則與語義優化9查詢優化技術理論與MySQL實踐（七）------非SPJ的優化10MySQL物理查詢優化技術概述11MySQL索引的利用、優化12表掃描與連接算法與MySQL多表連接優化實踐13查詢優化的綜合實例------TPCH實踐（一）14查詢優化的綜合實例------TPCH實踐（二）15關系代數對于數據庫的查詢優化的指導意義------查詢優化技術總結/li_hx/Lesson1:DatabaseandRelationalAlgebra

3WhatistheQueryOptimizationOfMySQL？Howtolearnandmasterit？45Whatistherelationalalgebra？5/li_hx/1WhatistheRDBMS？2WhatisthetechnologyOfQueryOptimization？Whatisdatabaseoptimization？66/li_hx/1WhatisthetechnologyOfRDBMS？1.3WhydoweneedtomasterQueryOptimizationTechnology？1.4WhatistheRelationDatabaseManagementSystem？1.1WhatistheDatabaseManagementSystem？1.2WhydoweneedtolearnMySQL/PostgreSQL？7/li_hx/1.1WhatistheDatabaseManagementSystem？數據庫管理系統(DatabaseManagementSystem)：1是一種操縱和管理數據的大型軟件，用于建立、使用和維護數據，簡稱DBMS。2它對數據進行統一的管理和控制，以保證數據的安全性和完整性。3用戶通過DBMS訪問數據庫中的數據，數據庫管理員也通過dbms進行數據庫的維護工作。4它可使多個應用程序和用戶用不同的方法在同時或不同時刻去建立，修改和詢問數據。5DBMS提供數據定義語言DDL（DataDefinitionLanguage）和數據操作語言DML（DataManipulationLanguage），供用戶定義數據庫的模式結構與權限約束，實現對數據的追加、刪除等操作。8/li_hx/1.1WhatistheDatabaseManagementSystem？數據庫管理系統(DatabaseManagementSystem)：只要做數據處理，軟件規模達到一定程度，似乎都稱為了數據庫。如：HBase/MongoDB等等衡量是否是數據庫的標準：ACID，是指在數據庫管理系統（DBMS）中事務所具有的四個特性：1）原子性（Atomicity）2）一致性（Consistency）3）隔離性（Isolation）4）持久性（Durability）

+技術的復雜性9/li_hx/1.2WhatistheRelationDatabaseManagementSystem？歷史上的幾種類型的數據庫：1層次型2網狀型3關系型關系數據庫，是建立在關系數據庫模型基礎上的數據庫，借助于關系代數等概念和方法來處理數據庫中的數據，同時也是一個被組織成一組擁有正式描述性的表格，該形式的表格作用的實質是裝載著數據項的特殊收集體，這些表格中的數據能以許多不同的方式被存取或重新召集而不需要重新組織數據庫表格。10/li_hx/1.2WhatistheRelationDatabaseManagementSystem？/li_hx/blog/static/18399141320144164585328/11/li_hx/1.3WhydoweneedtolearnMySQL/PostgreSQL？趨勢1：云計算，淘汰大部分的運維人員趨勢2：云數據庫，淘汰大部分以商業數據庫為職業的DBA趨勢3：電商等興起，對開源數據庫技術的人員需求增多趨勢4：去IOE化等運動背后的商業成本和安全問題，促進開源產業發展趨勢5：MySQL和PostgreSQL為代表的開源數據庫自身正蓬勃發展中/li_hx/blog/static/18399141320144164585328/12/li_hx/1.4WhydoweneedtomasterQueryOptimizationTechnology？名師錄：

數據庫查詢優化技術一直是DBMS實現技術中的精華，也是難點和重點。

王珊中國人民大學信息學院教授/博士生導師13/li_hx/1.4WhydoweneedtomasterQueryOptimizationTechnology？從數據處理的角度看：

數據庫的OLAP型應用要多于OLTP型應用。

大數據技術目前處理了一部分非結構化的數據，但分布式數據庫技術的發展，將使得數據庫技術進一步發展甚至出現革命性的進步進而與現行的hadoop/spark等技術爭奪數據分析的陣地。

而查詢優化技術因此二者得益繼續發展。從當下技術發展的趨勢看：

大數據背景下，盛行幾十年的SQL查詢技術煥發了新的生機，不僅在數據庫中占有半壁江山，而且在大數據處理技術下，各種SQL查詢接口/功能層異軍突起。

而查詢優化技術因具有普適性得以繼續使用。14/li_hx/2Whatistherelationalalgebra？數學中，關系代數是支持叫做逆反(converse)的對合一元運算的剩余布爾代數。激發關系代數的例子是在集合X上的所有二元關系的代數，帶有R·S被解釋為平常的二元關系復合。關系代數的早期形式形成于十九世紀德·摩根、皮爾士和ErnstSchr?der的工作。它今日的純等式形式是阿爾弗雷德·塔斯基和他的學生在1940年代開發的。15/li_hx/2Whatistherelationalalgebra？數據庫中，關系代數是一階邏輯的分支，是閉合于運算下的關系的集合。運算作用于一個或多個關系上來生成一個關系。關系代數是計算機科學的一部分。

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

SQL的查詢語言松散的基于了關系代數，盡管SQL中的操作數（表）不完全是關系，很多有用的關于關系代數的理論在SQL對應者中不成立。16/li_hx/2Whatistherelationalalgebra？Codd的關系代數的六個原始運算是“選擇”、“投影”、笛卡爾積（也叫做“叉積”或“交叉連接”）、并集、差集和“重命名”。（實際上，Codd忽略了重命名，而ISBL的發明者顯著的包括了它）。這六個運算在省略其中任何一個都要損失表達能力的意義上是基本的。已經依據這六個原始運算定義了很多其他運算。其中最重要的是交集、除法和自然連接。事實上ISBL顯著的用自然連接替代了笛卡爾積，它是笛卡爾積的退化情況。第一個基于Codd的代數的查詢語言是ISBL，許多作者都認同這個先驅的工作展示了一個使Codd的想法成為有用語言的方式1970年E.F.Codd發表了數據的關系模型論文。Codd提議這樣一種代數作為數據庫查詢語言的基礎，稱為關系代數。17/li_hx/2Whatistherelationalalgebra？關系代數是一種抽象的查詢語言，用對關系的運算來表達查詢，作為研究關系數據語言的數學工具。關系代數的運算對象是關系，運算結果亦為關系。關系代數用到的運算符包括四類：1）集合運算符2）專門的關系運算符3）算術比較符4）邏輯運算符比較運算符和邏輯運算符是用來輔助專門的關系運算符進行操作的，所以按照運算符的不同，主要將關系代數分為傳統的集合運算和專門的關系運算兩類。18/li_hx/3What

isthetechnologyOfQueryOptimization？摘自：《數據庫查詢優化器的藝術：原理解析與SQL性能的藝術》19/li_hx/3What

isthetechnologyOfQueryOptimization？QueryreuseThequeryresultreuseThequeryplanreuseTheRuleOfQueryRewriteBasedonRelationalAlgebraandHeuristicRuleViewRewrite、Sub-queryOptimization、EquivalentPredicateRewrite、ConditionSimplification、OuterJoinElimination、JoinElimination、NestJoinEliminationSematicOptimizationTheAlgorithmOfQueryOptimizationSingleTableScanAlgorithmTwoTableJoinAlgorithmMulti-tableJoinAlgorithmParallelQueryOptimizationDistributeQueryOptimization1920/li_hx/4WhatistheQueryOptimizationOfMySQL？Queryreuse------MySQL5.6.10NotSupportThequeryresultreuseThequeryplanreuseTheRuleOfQueryRewriteBasedonRelationalAlgebraandHeuristicRuleViewRewrite、

Sub-queryOptimization、EquivalentPredicateRewrite、ConditionSimplification、OuterJoinElimination、JoinElimination、NestJoinEliminationSematicOptimization

TheAlgorithmOfQueryOptimizationSingleTableScanAlgorithmTwoTableJoinAlgorithmMulti-tableJoinAlgorithmParallelQueryOptimizationDistributeQueryOptimization2021/li_hx/5Howtolearnandmasterit？22/li_hx/5Howtolearnandmasterit？九陽真經主要內容：1查詢優化技術的原理2查詢優化領域的主要技術3PostgreSQL查詢優化器分析4MySQL查詢優化器分析5PostgreSQLpkMySQL---必讀內外兼修23/li_hx/5Howtolearnandmasterit？修習心法1讀書而明理2聽講而釋疑3練習而掌握4實踐中巧遇大造化5再：讀書/聽講/交流/思考/實踐......24/li_hx/6Whatisdatabaseoptimization？數據庫調優/DatabaseTuning數據庫調優可以使數據庫應用運行得更快，其目標是：使數據庫

有更高的吞吐量

更短的響應時間。被調優的對象是整個數據庫管理系統總體。25/li_hx/6Whatisdatabaseoptimization？數據庫調優的方式通常有如下幾種：1）人工調優。

主要依賴于人，效率低下；要求操作者完全理解常識所依賴的原理，還需要對應用、數據庫管理系統、操作系統以及硬件有廣泛而深刻的理解。2）基于案例的調優。

總結典型應用案例情況中數據庫參數的推薦配置值、數據邏輯層設計等情況，從而為用戶的調優工作提供一定的參考和借鑒。但這種方式忽略了系統的動態性和不同系統間存在的差異。3）自調優。

為數據庫系統建立一個模型，根據“影響數據庫系統性能效率的因素”，數據庫系統自動進行參數的配置。一些商業數據庫，實現了部分自調優技術。26/li_hx/6Whatisdatabaseoptimization？數據庫調優主要分為五個階段27/li_hx/6Whatisdatabaseoptimization？數據庫調優五個階段的主要技術1應用情況的估算。

應用的使用方式（把業務邏輯轉換為數據庫的讀寫分布邏輯，以是讀多寫少還是讀寫均衡等來區分OLTP和OLAP；應用對數據庫的并發情況、并發是否可以池化等）、數據量、對數據庫的壓力、峰值壓力等做一個預估。2系統選型策略。

確定什么樣的數據庫可以適用應用需求，并確定數據庫是使用開源的還是商業的，是集使用群還是單機的系統，同時對操作系統、中間件、硬件、網絡等進行選型。28/li_hx/6Whatisdatabaseoptimization？數據庫調優五個階段的主要技術1應用情況的估算。

應用的使用方式（把業務邏輯轉換為數據庫的讀寫分布邏輯，以是讀多寫少還是讀寫均衡等來區分OLTP和OLAP；應用對數據庫的并發情況、并發是否可以池化等）、數據量、對數據庫的壓力、峰值壓力等做一個預估。2系統選型策略。

確定什么樣的數據庫可以適用應用需求，并確定數據庫是使用開源的還是商業的，是集使用群還是單機的系統，同時對操作系統、中間件、硬件、網絡等進行選型。29/li_hx/6Whatisdatabaseoptimization？數據庫調優五個階段的主要技術3數據模型的設計。主要是根據業務邏輯，從幾個角度考慮表的邏輯結構，內容如下：3.1E-R模型設計：遵循E-R模型設計原理。偶爾的適當程度的非規范化可以改善系統查詢性能。3.2數據邏輯分布策略：目的是減少數據請求的不必要的數據量，把用戶需要的數據返回；可用的技術如分區、用E-R模型分表等（如互聯網企業典型的用法，根據業務的不同，進行分庫、分表等操作）。3.3數據物理存儲策略：目的是減少IO，如啟用壓縮技術、把索引和表數據的存儲分開，不同的表數據分布于不同的表空間，不同表空間分布在不同的物理存儲上（尤其是讀寫量大的表空間分布在不同的物理存儲上）、日志、索引和數據分布在不同的物理存儲上等。3.4索引：在查詢頻繁的對象上建立合適的索引，使索引的正效應大于負效應（索引的維護存在消耗）。30/li_hx/6Whatisdatabaseoptimization？數據庫調優五個階段的主要技術4SQL設計。編寫正確的、查詢效率高的SQL語句。這依據的主要是“查詢重寫規則”，編寫語句的過程中要注意，要有意識地保障SQL能利用到索引。5數據庫功能的啟用。數據庫為提高性能提供了一些功能，可合理使用，具體如下：5.1查詢重用：