Oracle與MySQL的SQL技術(shù)優(yōu)化

1、大道相通，得魚忘筌Oracle與MySQL的SQL技術(shù)優(yōu)化概述：在國(guó)內(nèi)，Oracle為王的時(shí)代，DBA們探索總結(jié)出很多SQL 優(yōu)化要點(diǎn)，現(xiàn)在MySQL成為新的王者，這些要點(diǎn)還有價(jià) 值嗎？我在這里將和大家探究Oracle到MySQL優(yōu)化SQL的 同與不同。比如，Oracle與MySQL的引擎模型、元數(shù)據(jù)模 型、統(tǒng)計(jì)信息模型、執(zhí)行計(jì)劃模型、鎖模型對(duì)比分析， 提煉共通的SQL優(yōu)化要點(diǎn)和原則，又考慮差異調(diào)整執(zhí)行 層面的細(xì)節(jié)。Oracle的體系結(jié)構(gòu)Oracle的體系結(jié)構(gòu)-精簡(jiǎn)UserPGAUser processServer processListenerInstanceShared poolDatab

2、ase buffer cacheStreams poolLarge poolJava poolDBWnCKPTLGWRSMONPMONRECOARCnOthersRedolog bufferFixed SGAMySQL的體系結(jié)構(gòu)-精簡(jiǎn)注：此圖引用自林曉斌老師的“MySQL實(shí)戰(zhàn)45講”MySQL的體系結(jié)構(gòu)-InnoDB架構(gòu)SQL的大處理環(huán)節(jié)類似，細(xì)節(jié) 又很不同MySQL的SQL解析器、優(yōu)化器 更輕量，更簡(jiǎn)單MySQL查詢緩存的命中風(fēng)險(xiǎn)很 大，特別是OLTPOracle元數(shù)據(jù)/統(tǒng)計(jì)信息-數(shù)據(jù)字典SELECT * FROM dictionary;TablesIndexesViewsUsersSche

3、masProceduresand so onSYSTEM TablespaceMetadataOracle元數(shù)據(jù)/統(tǒng)計(jì)信息-動(dòng)態(tài)性能視圖Shared poolDatabase buffer cacheRedo log bufferStreams poolLarge poolJava poolKEEPbufferpoolREYCLCE bufferpoolnKbuffer cacheSession data Wait eventsMemory allocations Running SQLUNDO usageOpen cursorsRedo log usageand so onSystem Gl

4、obal AreaMySQL元數(shù)據(jù)/統(tǒng)計(jì)信息-INFORMATION_SCHEMA元數(shù)據(jù)方面都比較全面，Oracle更豐富基礎(chǔ)的表、列、索引、統(tǒng)計(jì)信息都有， 滿足常規(guī)SQL分析需要Oracle提供了統(tǒng)一的元數(shù)據(jù)視圖，MySQL Server層的元數(shù)據(jù)統(tǒng)一，但存儲(chǔ)插件相關(guān) 的由插件提供，比如InnoDBOracle執(zhí)行計(jì)劃MySQL執(zhí)行計(jì)劃執(zhí)行計(jì)劃-探究訪問(wèn)方法是否以最好的方式訪問(wèn)數(shù)據(jù)？掃描？索引查找？聯(lián)接順序是否以正確的順序聯(lián)接各表以便盡早盡多地消除數(shù)據(jù)？聯(lián)接類型是否使用了正確的聯(lián)接類型？分區(qū)修剪執(zhí)行過(guò)分區(qū)修剪嗎？是否消除了足夠多的數(shù)據(jù)？并行度基數(shù)每個(gè)對(duì)象是否生成正確的行數(shù)？執(zhí)行計(jì)劃-Orac

5、le：訪問(wèn)方法取數(shù)據(jù)訪問(wèn)方法全表掃描按 ROWID 訪問(wèn)表索引唯一掃描索引范圍掃描索引跳過(guò)掃描完整索引掃描快速完整索引掃描索引聯(lián)接位圖索引解釋讀取表中所有行并過(guò)濾掉那些不符合 WHERE 子句謂詞的行。用于索引、DOP 集等ROWID 指定含有所需行的數(shù)據(jù)文件和數(shù)據(jù)塊以及該行在該塊中的位置。當(dāng)在索 引或 WHERE 子句中提供 rowid 時(shí)使用將只返回一行。當(dāng)語(yǔ)句中包含 UNIQUE 或 PRIMARY KEY 約束條件時(shí)使用，這 些約束條件用于保證只訪問(wèn)一行訪問(wèn)相鄰索引項(xiàng)，可返回多個(gè) ROWID 值。與等式一起用于非唯一索引，或與范 圍謂詞一起用于唯一索引（、between 等）如果前導(dǎo)列

6、中只有很少的不同值，而非前導(dǎo)列中有許多不同的值，則跳過(guò)索引的 前導(dǎo)部分，使用其余有用的部分處理索引的所有葉塊，但只有經(jīng)過(guò)足夠多的分支塊才能找到第 1 個(gè)葉塊。當(dāng)所有需要的列都位于索引中且 order by 子句與索引結(jié)構(gòu)匹配，或者排序合并聯(lián)接已完 成時(shí)，即可使用掃描索引中的所有塊，用來(lái)在所有需要的列都在索引中時(shí)代替 FTS。使用多塊 IO，可以并行運(yùn)行散列聯(lián)接多個(gè)索引，這些索引一起包含有查詢中引用的所有表列。 不會(huì)消除排序 操作使用鍵值位圖和映射函數(shù)，映射函數(shù)可將每個(gè)比特的位置轉(zhuǎn)換成一個(gè) rowid。 可 以有效地合并對(duì)應(yīng)于 WHERE 子句中的多個(gè)條件的索引執(zhí)行計(jì)劃-Oracle：聯(lián)接類型嵌

7、套循環(huán)聯(lián)接-Nested Loops Join哈希聯(lián)接 Hash Join排序合并聯(lián)接 Sort merge Joing笛卡爾聯(lián)接MERGE JOIN CARTESIAN執(zhí)行計(jì)劃-MySQL：訪問(wèn)方法與聯(lián)接類型全表掃描索引訪問(wèn)嵌套循環(huán)聯(lián)接- Nested Loops Join都有詳細(xì)的執(zhí)行計(jì)劃步驟，這是SQL性能分析的核心參考Oracle的執(zhí)行計(jì)劃中能提供更多訪 問(wèn)方法、聯(lián)接類型Oracle能提供更多的歷史執(zhí)行計(jì)劃、 可進(jìn)行執(zhí)行計(jì)劃之間的比對(duì)分析Oracle鎖-用途TM 鎖(Table Lock)TX 鎖(Transaction Lock)HW 鎖(High Watermark Lock)US

8、 鎖(Undo Segment Lock)TO 鎖(Temporary Object Lock)CF 鎖(Control File Lock)JO 鎖(Job Queue Lock)SQ 鎖(Sequence Cache)Oracle鎖-模式模式內(nèi)部編號(hào)模式值描述(以DML為例來(lái)說(shuō)明)NullKSQMNull1Null模式,不妨礙任何并發(fā)訪問(wèn),主要用 來(lái)作為Cache Invalidate的通知機(jī)制存在SSKSQMSS2SubShare模式,使用共享模式鎖住一條 記錄SXKSQMSX3SubExclusive模式,使用獨(dú)占模式鎖住一 條記錄SKSQMS4共享模式SSXKSQMSSX5Share

9、,SubExclusive,對(duì)表持有共享鎖,對(duì) 其中的記錄持獨(dú)占模式XKSQMX6Exclusive模式,對(duì)全表持獨(dú)占模式Oracle鎖-模式兼容性請(qǐng)求/占 用NullSSSXSSSXXNullYesYesYesYesYesYesSSYesYesYesYesYesNoSXYesYesYesNoNoNoSYesYesNoYesNoNoSSXYesYesNoNoNoNoXYesNoNoNoNoNoMySQL鎖-按范圍分類（ InnoDB ）行鎖表鎖 全局鎖MySQL鎖-鎖（ InnoDB ）shared (S) locksexclusive(X) lockXIXSISXConflictConfli

10、ctConflictConflictIXConflictCompatibleConflictCompatibleSConflictConflictCompatibleCompatibleISConflictCompatibleCompatibleCompatible注：表級(jí)鎖兼容性InnoDB行鎖 分為兩種都有完善的鎖機(jī)制， 能夠支撐業(yè)務(wù)的并 發(fā)數(shù)據(jù)保護(hù)需求MySQL不同存儲(chǔ)引 擎對(duì)鎖的支持不同，建議使用InnoDBSQL優(yōu)化的一般性原則減少服務(wù) 器資源消 耗（主要 是磁盤IO）合適的索引， 考慮選擇性， 索引的雙重效 應(yīng)多表時(shí)，合適 的表連接順序盡量不要在謂 詞列上做處理.SQL優(yōu)化的基本思

11、路-Oracle與MySQL共通識(shí)別問(wèn)題SQL綁定好的執(zhí)行計(jì)劃哪一步跑得慢？準(zhǔn)確的 統(tǒng)計(jì)信 息創(chuàng)建或調(diào) 整索引調(diào)整連 接方式 和順序子查詢 處理虛框：Oracle有，MySQL無(wú)如何高效的處理？方向展望常見(jiàn)問(wèn)題缺乏適當(dāng)索引列條件上使用了運(yùn)算符條件上發(fā)生隱式轉(zhuǎn)換條件對(duì)應(yīng)索引列不在復(fù)合索引第一位條件對(duì)應(yīng)索引列選擇度不夠高統(tǒng)計(jì)信息不準(zhǔn)確專家優(yōu)化（手工）自動(dòng)識(shí)別 專家優(yōu)化（半自動(dòng)）自動(dòng)識(shí)別 自動(dòng)處理（全自動(dòng)）如何高效的處理？-知識(shí)庫(kù)的建立和積累建立單位內(nèi)部SQL相關(guān)的知識(shí)庫(kù)比如涵蓋SQL知識(shí)背景SQL優(yōu)化原則SQL優(yōu)化原理SQL優(yōu)化示例SQL編寫規(guī)范隨著業(yè)務(wù)發(fā)展不斷積累更新如何高效的處理？-產(chǎn)品化、工具化如何高效的處理？-產(chǎn)品化、工具化兩大引擎，SQL解析引擎+專家引擎覆蓋應(yīng)用全生命周期:開(kāi)發(fā)、測(cè)試、上線發(fā)布、生產(chǎn)運(yùn)行內(nèi)置的專家經(jīng)驗(yàn)規(guī)則+語(yǔ)法樹(shù)特征值、文本正則匹配的靈活 自定義規(guī)則能力，全面覆蓋用戶SQL規(guī)范支持各種開(kāi)發(fā)語(yǔ)言構(gòu)建的應(yīng)用已支持?jǐn)?shù)據(jù)庫(kù)Oracle、MySQL、DB2，GaussDB、Microsoft SQL Server 、OceanBas