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

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

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

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

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

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

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

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

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

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

在純數(shù)學(xué)中的關(guān)系代數(shù)是有關(guān)于數(shù)理邏輯和集合論的代數(shù)結(jié)構(gòu)。

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

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

有更高的吞吐量

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

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

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

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

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

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

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

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