MySQL的查詢優(yōu)化技術-01

數(shù)據(jù)庫查詢優(yōu)化技術/li_hx/TheQueryOptimizationTechniqueOfMySQL(5.6.X)@那海藍藍1數(shù)據(jù)庫查詢優(yōu)化技術/li_hx/1Adatatechnologylearners,practitionersBook:《數(shù)據(jù)庫查詢優(yōu)化器的藝術：原理解析與SQL性能優(yōu)化》23Blog:/li_hx4Twitter：@那海藍藍Email:database_XX@126.com5Direction:DB(PostgreSQL,MySQL.etc)63/li_hx/課程大綱：1數(shù)據(jù)庫與關系代數(shù)2數(shù)據(jù)庫查詢優(yōu)化技術總攬3查詢優(yōu)化技術理論與MySQL實踐（一）------子查詢的優(yōu)化（一）4查詢優(yōu)化技術理論與MySQL實踐（二）------子查詢的優(yōu)化（二）5查詢優(yōu)化技術理論與MySQL實踐（三）------視圖重寫與等價謂詞重寫6查詢優(yōu)化技術理論與MySQL實踐（四）------條件化簡7查詢優(yōu)化技術理論與MySQL實踐（五）------外連接消除、嵌套連接消除與連接消除8查詢優(yōu)化技術理論與MySQL實踐（六）------數(shù)據(jù)庫的約束規(guī)則與語義優(yōu)化9查詢優(yōu)化技術理論與MySQL實踐（七）------非SPJ的優(yōu)化10MySQL物理查詢優(yōu)化技術概述11MySQL索引的利用、優(yōu)化12表掃描與連接算法與MySQL多表連接優(yōu)化實踐13查詢優(yōu)化的綜合實例------TPCH實踐（一）14查詢優(yōu)化的綜合實例------TPCH實踐（二）15關系代數(shù)對于數(shù)據(jù)庫的查詢優(yōu)化的指導意義------查詢優(yōu)化技術總結/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？數(shù)據(jù)庫管理系統(tǒng)(DatabaseManagementSystem)：1是一種操縱和管理數(shù)據(jù)的大型軟件，用于建立、使用和維護數(shù)據(jù)，簡稱DBMS。2它對數(shù)據(jù)進行統(tǒng)一的管理和控制，以保證數(shù)據(jù)的安全性和完整性。3用戶通過DBMS訪問數(shù)據(jù)庫中的數(shù)據(jù)，數(shù)據(jù)庫管理員也通過dbms進行數(shù)據(jù)庫的維護工作。4它可使多個應用程序和用戶用不同的方法在同時或不同時刻去建立，修改和詢問數(shù)據(jù)。5DBMS提供數(shù)據(jù)定義語言DDL（DataDefinitionLanguage）和數(shù)據(jù)操作語言DML（DataManipulationLanguage），供用戶定義數(shù)據(jù)庫的模式結構與權限約束，實現(xiàn)對數(shù)據(jù)的追加、刪除等操作。8/li_hx/1.1WhatistheDatabaseManagementSystem？數(shù)據(jù)庫管理系統(tǒng)(DatabaseManagementSystem)：只要做數(shù)據(jù)處理，軟件規(guī)模達到一定程度，似乎都稱為了數(shù)據(jù)庫。如：HBase/MongoDB等等衡量是否是數(shù)據(jù)庫的標準：ACID，是指在數(shù)據(jù)庫管理系統(tǒng)（DBMS）中事務所具有的四個特性：1）原子性（Atomicity）2）一致性（Consistency）3）隔離性（Isolation）4）持久性（Durability）

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

數(shù)據(jù)庫查詢優(yōu)化技術一直是DBMS實現(xiàn)技術中的精華，也是難點和重點。

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

數(shù)據(jù)庫的OLAP型應用要多于OLTP型應用。

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

而查詢優(yōu)化技術因此二者得益繼續(xù)發(fā)展。從當下技術發(fā)展的趨勢看：

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

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

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

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

isthetechnologyOfQueryOptimization？摘自：《數(shù)據(jù)庫查詢優(yōu)化器的藝術：原理解析與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查詢優(yōu)化技術的原理2查詢優(yōu)化領域的主要技術3PostgreSQL查詢優(yōu)化器分析4MySQL查詢優(yōu)化器分析5PostgreSQLpkMySQL---必讀內外兼修23/li_hx/5Howtolearnandmasterit？修習心法1讀書而明理2聽講而釋疑3練習而掌握4實踐中巧遇大造化5再：讀書/聽講/交流/思考/實踐......24/li_hx/6Whatisdatabaseoptimization？數(shù)據(jù)庫調優(yōu)/DatabaseTuning數(shù)據(jù)庫調優(yōu)可以使數(shù)據(jù)庫應用運行得更快，其目標是：使數(shù)據(jù)庫

有更高的吞吐量

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

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

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

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

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

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

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

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