數(shù)據(jù)庫的SQL優(yōu)化

通用數(shù)據(jù)庫的SQL優(yōu)化目錄CONTENTS個人/主題簡介?優(yōu)化的本質(zhì)從SQL優(yōu)化管理需求從SQL優(yōu)化管理設(shè)計(jì)秒殺大數(shù)據(jù)的案例簡介Oracle

ACE-Pro

（Database方向）MySQL

講師PostgreSQLACE

Partner阿里云

MVP墨天輪MVPTiDB

MVAITPUB論壇內(nèi)存數(shù)據(jù)庫版主、核心專家、金牌顧問ITPUB培訓(xùn)專家百人團(tuán)收錄國內(nèi)數(shù)據(jù)庫風(fēng)云百人團(tuán)機(jī)械工業(yè)出版社專家委員會委員優(yōu)化的本質(zhì)減少IO、減少交互、減少不必要的操作。優(yōu)化的本質(zhì)盡可能的少讓數(shù)據(jù)庫做事情（包含架構(gòu)和框架方面）持續(xù)多年的常見SQLselectcount(0)from(SELECT此處省略上百行FROM(dLEFT

JOIN deond.ID=

de.IDLEFT

JOIN pONd.ID=

p.ID)LEFT

JOIN oONo.ID=

d.IDWHERE此處省略幾十行orderBYd.TIME

DESC)tmp_count;互聯(lián)網(wǎng)分頁與數(shù)據(jù)庫分頁分頁概念：不對結(jié)果集進(jìn)行真實(shí)的查詢與返回，只把結(jié)果集的很少一部分進(jìn)行返回。分頁特點(diǎn)：與總數(shù)關(guān)系不大。select*fromtwhere

rownum<10或者limit

10或者用fetch

first10rowsonly;即使幾十億數(shù)據(jù)甚至PB級別由于分頁作用毫秒級別返回。未發(fā)生真實(shí)結(jié)果集查詢。數(shù)據(jù)庫極輕量級操作。常見的分頁SQLSELECT*

FROM

(SELECT *，ROWNUMRNFROM

(SELECT*FROM

TABLE

where條件

使用到索引)

A

WHERE

ROWNUM<=40)WHERERN>=

21；DBA視角的分頁。即使用到索引也不會返回全部索引的數(shù)據(jù)。select

count(1)

from

(select

*

from

t

where

條件

是不是用到索引不關(guān)心

+排序

);分頁組件，但是約等于沒有分頁效果。真實(shí)發(fā)生了結(jié)果集查詢。括號內(nèi)數(shù)據(jù)庫極重量級操作。開發(fā)視角的分頁。對比VS互聯(lián)網(wǎng)分頁方式在海量數(shù)據(jù)下，按照索引偏移量分頁，通過“下一頁”顯示查詢結(jié)果，不顯示結(jié)果集大小。Select*fromtlimit

10某些架構(gòu)采用如下分頁方式采用mybatis默認(rèn)的分頁方式pagehelperselectcount(1)from(select*

fromt 復(fù)雜查詢加排序，或者全表);--因?yàn)橐獙?shí)實(shí)在在計(jì)算總條數(shù)才能顯示總頁數(shù)，真實(shí)發(fā)生了結(jié)果集查詢。等于沒有起到的分頁的預(yù)期效果。日志樣例：

說明了掃描行數(shù)和返回行數(shù)日志說明：

不同SQL下的分頁效果的掃描行數(shù)和返回行數(shù)日志說明：

應(yīng)用開發(fā)實(shí)際的效果真實(shí)情況遠(yuǎn)比實(shí)驗(yàn)環(huán)境復(fù)雜1000萬的N表關(guān)聯(lián)，符合條件有10萬。關(guān)聯(lián)比1:M不要求顯示總條數(shù)：查10頁，每頁10條。實(shí)際累計(jì)掃描100條*N表的個數(shù)*M的比例。VS1000萬的N表關(guān)聯(lián)，符合條件有10萬。關(guān)聯(lián)比1:M要求顯示總條數(shù)，采用默認(rèn)做法：

查10頁，每頁10條。實(shí)際累計(jì)掃描10X10萬*N表的個數(shù)*M的比例=M*N*100萬條每次點(diǎn)擊都會查詢M*N*10萬真實(shí)情況遠(yuǎn)比實(shí)驗(yàn)環(huán)境惡劣甚至是故障（

優(yōu)化效果也顯著）結(jié)果集達(dá)到一定程度還會觸發(fā)全表。如1000萬中查300萬以上，實(shí)際掃描是1000萬。還涉及到數(shù)據(jù)傾斜問題。存儲過程中的問題打開一個存儲過程，或者一段應(yīng)用程序代碼：selectcount(*)

into

num

from

table

（可能有where條件，也可能沒有）;后面是if (num>0)。存儲過程中的問題其實(shí)他想要的是一個布爾值而不是一個整形值。selectcount(*)intonumfromtablelimit

1;然后if (num>0)。通常開發(fā)關(guān)注實(shí)現(xiàn)（

邏輯）

不關(guān)注性能14億的人口庫， where

姓名=薛曉剛 and 性別=男。返回1-2條數(shù)據(jù)。邏輯與的關(guān)系是求交集。14億的人口庫， where

姓名=薛曉剛 or 性別=男。

返回7億條數(shù)據(jù)。邏輯或的關(guān)系是求并集。現(xiàn)在可能連邏輯都不關(guān)注了OR的需求要謹(jǐn)慎SELECT*

FROM

X WHEREcodeA=‘123456’

orcodeB=‘123456’。重點(diǎn)：凡是OR都是擴(kuò)大范圍的操作，一般不用于高頻檢索應(yīng)用。優(yōu)化的最高境界優(yōu)化的基礎(chǔ)是設(shè)計(jì)當(dāng)量變達(dá)到質(zhì)變低效SQL是數(shù)據(jù)庫永遠(yuǎn)的痛DBA需要擴(kuò)展自己的角度DBA需要擴(kuò)展自己的角度優(yōu)化的阻力開發(fā)人員是否懂索引？開發(fā)人員是否懂?dāng)?shù)據(jù)庫高可用？開發(fā)人員是否懂事務(wù)隔離級別？開發(fā)人員會不會考慮SQL的復(fù)雜性？我們要告訴開發(fā)怎么做！優(yōu)化的最大阻力業(yè)務(wù)人員是否懂開發(fā)？業(yè)務(wù)人員會不會考慮SQL的性能？業(yè)務(wù)懂不懂為什么數(shù)據(jù)庫會宕機(jī)？CPU滿

IO滿