騰訊大講堂48-數(shù)據(jù)庫(kù)查詢優(yōu)化淺析

騰訊大講堂第四十八期研發(fā)管理部大講堂主頁：://km.oa/class與講師互動(dòng)：://km.oa/group/class.數(shù)據(jù)庫(kù)查詢優(yōu)化淺析Alexyang無線產(chǎn)品部20211013.提綱應(yīng)用級(jí)查詢優(yōu)化SQL語句的解析方式索引的本質(zhì)以及調(diào)優(yōu)分析執(zhí)行方案.提綱應(yīng)用級(jí)查詢優(yōu)化SQL語句的解析方式索引的本質(zhì)以及調(diào)優(yōu)分析執(zhí)行方案.一、需要性能/查詢優(yōu)化的原因影響性能的因素：整個(gè)系統(tǒng)環(huán)境軟件：操作系統(tǒng)、中間件、應(yīng)用程序、數(shù)據(jù)庫(kù)硬件：CPU、內(nèi)存、磁盤、網(wǎng)絡(luò)上面任何一個(gè)都可能成為系統(tǒng)的性能瓶頸.系統(tǒng)級(jí)性能優(yōu)化的點(diǎn)操作系統(tǒng)：虛擬內(nèi)存、文件系統(tǒng)格式、系統(tǒng)參數(shù)〔LINUX〕中間件：日志、部署時(shí)去掉打印信息〔打印信息對(duì)性能影響較大)、工作線程數(shù)、連接池、JAVA垃圾回收模式、JAVA內(nèi)存設(shè)置應(yīng)用程序：使用連接池、合理的業(yè)務(wù)設(shè)計(jì)、盡可能不要對(duì)數(shù)據(jù)庫(kù)操作進(jìn)行同步、JAVA程序的性能優(yōu)化〔參考網(wǎng)絡(luò)資源〕數(shù)據(jù)庫(kù)：工作線程、I/O線程、內(nèi)存配置等、索引的利用、SQL改寫、效勞器代碼改進(jìn).這里我們主要考慮以下兩個(gè)點(diǎn)應(yīng)用層存在問題。數(shù)據(jù)庫(kù)層存在問題。.合理的邏輯設(shè)計(jì)以某實(shí)際應(yīng)用系統(tǒng)為例，原來任務(wù)管理模塊并發(fā)用戶數(shù)只有15個(gè)，該模塊用來顯示所有待處理的任務(wù)，以及每個(gè)任務(wù)的里程碑個(gè)數(shù)。經(jīng)過檢查，發(fā)現(xiàn)其實(shí)現(xiàn)如下：執(zhí)行語句SELECTCOUNT(*)FROMTASK;selecttask_idfromtaskorderbytask_iddesc;

對(duì)結(jié)果集中的每條記錄

{SELECT*FROMTASK_STEPWHERETASK_STEP.TASK_ID=?}分析：如果TASK表有10000條記錄，需要向數(shù)據(jù)庫(kù)發(fā)送10000個(gè)查詢。假設(shè)通訊使用了100ms，每個(gè)查詢的執(zhí)行時(shí)間是20ms。使用的時(shí)間為：(100ms+20ms)*10000=1200s=20min.解決方案selecttask_step.*fromtask,task_stepwheretask_step.task_id=task.task_idorderbytask.task_iddesc;備注：1〕繼續(xù)對(duì)task_step.*處理，減少網(wǎng)絡(luò)通訊量。2〕默認(rèn)看到自己的任務(wù)，提供連接查詢其他任務(wù)。.盡量使用數(shù)據(jù)庫(kù)提供的功能在某實(shí)際應(yīng)用系統(tǒng)中，對(duì)于每個(gè)任務(wù)、日記等對(duì)象都有一個(gè)唯一的ID，該系統(tǒng)使用數(shù)據(jù)庫(kù)的一個(gè)表的一條整型記錄來維持。當(dāng)新建一個(gè)任務(wù)時(shí)，取用該表的當(dāng)前記錄值，操作完畢后加1。為了防止不同對(duì)象使用相同ID，使用如下方法來保證：inttemp=0; synchronized{執(zhí)行SELECT*FROMOBJECTID;temp=結(jié)果集中的idupdate結(jié)果集中的id〔id+1〕}insertintotaskvalues(temp,...);.解決方案createsequencemyseqincrementby1;insertintotaskvalues(myseq.nextval,...);.二、SQL語句的解析方式.SQL語句的處理過程.語法/詞法分析負(fù)責(zé)解析SQL命令和過程性語句塊過濾注釋,解析參數(shù)使用成熟的工具〔如YACC/LEX〕生成靈活的語法規(guī)那么定義便于語法的擴(kuò)展與修改生成統(tǒng)一格式的語法樹.對(duì)應(yīng)的語法結(jié)構(gòu)Select*fromt1,t2,t3;par_from_tv_list_struct結(jié)構(gòu)par_from_tv_list_t*from_tv_list;par_from_tv_t*from_tvpar_from_tv_list_struct結(jié)構(gòu)par_from_tv_list_t*from_tv_list;par_from_tv_t*from_tvpar_from_tv_list_struct結(jié)構(gòu)par_from_tv_t*from_tvpar_from_tv_struct結(jié)構(gòu)T1par_from_tv_struct結(jié)構(gòu)T2par_from_tv_struct結(jié)構(gòu)T3規(guī)則2規(guī)則1.語義分析數(shù)據(jù)庫(kù)對(duì)象名字解析合法性檢查內(nèi)部ID的轉(zhuǎn)換權(quán)限檢查語法樹數(shù)據(jù)結(jié)構(gòu)的簡(jiǎn)化與預(yù)處理DDL語句分解為對(duì)系統(tǒng)表的DML視圖對(duì)象的替換等.代價(jià)優(yōu)化器接受語義分析的輸入統(tǒng)計(jì)信息的分析數(shù)據(jù)分布統(tǒng)計(jì)直方圖代價(jià)的計(jì)算基于統(tǒng)計(jì)信息操作符號(hào)的選擇基于本錢的最優(yōu)執(zhí)行路徑選擇生成優(yōu)化的執(zhí)行方案.基于操作符的執(zhí)行方案物理操作符功能簡(jiǎn)單專一,完成特定的數(shù)據(jù)庫(kù)操作如:連接,過濾,排序等執(zhí)行方案操作符的組合,構(gòu)成樹型的執(zhí)行方案執(zhí)行時(shí),數(shù)據(jù)從葉子向根流動(dòng)在根聚集成結(jié)果集,或者得到結(jié)果狀態(tài)方案被自動(dòng)緩存,以備重用.實(shí)例分析select*fromA,B,C,DwhereA.a＝B.bandB.b1=C.candC.c1=D.dandA.a1>123.三、索引的本質(zhì)以及調(diào)優(yōu).需要了解的概念索引聚集索引、非聚集索引唯一索引、非唯一索引單列索引、組合索引...Clusteredtable/index.lusteredindexonaclusteredtable

.lusteredindexonaclusteredtable

.lusteredindexonaclusteredtable

.lusteredindexonaclusteredtable

.索引調(diào)優(yōu)的6個(gè)知識(shí)點(diǎn)1、將索引和數(shù)據(jù)存放到不同的文件組2、組合索引的使用3、唯一索引與非唯一索引的差