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

騰訊大講堂第四十八期研發(fā)管理部大講堂主頁：/class與講師互動(dòng)：/group/class騰訊大講堂48-數(shù)據(jù)庫查詢優(yōu)化淺析數(shù)據(jù)庫查詢優(yōu)化淺析Alexyang無線產(chǎn)品部騰訊大講堂48-數(shù)據(jù)庫查詢優(yōu)化淺析提綱應(yīng)用級(jí)查詢優(yōu)化SQL語句的解析方式索引的本質(zhì)以及調(diào)優(yōu)分析執(zhí)行計(jì)劃騰訊大講堂48-數(shù)據(jù)庫查詢優(yōu)化淺析提綱應(yīng)用級(jí)查詢優(yōu)化SQL語句的解析方式索引的本質(zhì)以及調(diào)優(yōu)分析執(zhí)行計(jì)劃騰訊大講堂48-數(shù)據(jù)庫查詢優(yōu)化淺析一、需要性能/查詢優(yōu)化的原因影響性能的因素：整個(gè)系統(tǒng)環(huán)境軟件：操作系統(tǒng)、中間件、應(yīng)用程序、數(shù)據(jù)庫硬件：CPU、內(nèi)存、磁盤、網(wǎng)絡(luò)上面任何一個(gè)都可能成為系統(tǒng)的性能瓶頸騰訊大講堂48-數(shù)據(jù)庫查詢優(yōu)化淺析系統(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ù)庫操作進(jìn)行同步、JAVA程序的性能優(yōu)化（參考網(wǎng)絡(luò)資源）數(shù)據(jù)庫：工作線程、I/O線程、內(nèi)存配置等、索引的利用、SQL改寫、服務(wù)器代碼改進(jìn)騰訊大講堂48-數(shù)據(jù)庫查詢優(yōu)化淺析這里我們主要考慮以下兩個(gè)點(diǎn)應(yīng)用層存在問題。數(shù)據(jù)庫層存在問題。騰訊大講堂48-數(shù)據(jù)庫查詢優(yōu)化淺析合理的邏輯設(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ù)庫發(fā)送10000個(gè)查詢。假設(shè)通訊使用了100ms，每個(gè)查詢的執(zhí)行時(shí)間是20ms。使用的時(shí)間為：(100ms+20ms)*10000=1200s=20min騰訊大講堂48-數(shù)據(jù)庫查詢優(yōu)化淺析解決方案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ù)。騰訊大講堂48-數(shù)據(jù)庫查詢優(yōu)化淺析盡量使用數(shù)據(jù)庫提供的功能在某實(shí)際應(yīng)用系統(tǒng)中，對(duì)于每個(gè)任務(wù)、日記等對(duì)象都有一個(gè)唯一的ID，該系統(tǒng)使用數(shù)據(jù)庫的一個(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,...);騰訊大講堂48-數(shù)據(jù)庫查詢優(yōu)化淺析解決方案createsequencemyseqincrementby1;insertintotaskvalues(myseq.nextval,...);騰訊大講堂48-數(shù)據(jù)庫查詢優(yōu)化淺析二、SQL語句的解析方式騰訊大講堂48-數(shù)據(jù)庫查詢優(yōu)化淺析SQL語句的處理過程騰訊大講堂48-數(shù)據(jù)庫查詢優(yōu)化淺析語法/詞法分析負(fù)責(zé)解析SQL命令和過程性語句塊過濾注釋,解析參數(shù)使用成熟的工具（如YACC/LEX）生成靈活的語法規(guī)則定義便于語法的擴(kuò)展與修改生成統(tǒng)一格式的語法樹騰訊大講堂48-數(shù)據(jù)庫查詢優(yōu)化淺析對(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騰訊大講堂48-數(shù)據(jù)庫查詢優(yōu)化淺析語義分析數(shù)據(jù)庫對(duì)象名字解析合法性檢查內(nèi)部ID的轉(zhuǎn)換權(quán)限檢查語法樹數(shù)據(jù)結(jié)構(gòu)的簡(jiǎn)化與預(yù)處理DDL語句分解為對(duì)系統(tǒng)表的DML視圖對(duì)象的替換等騰訊大講堂48-數(shù)據(jù)庫查詢優(yōu)化淺析代價(jià)優(yōu)化器接受語義分析的輸入統(tǒng)計(jì)信息的分析數(shù)據(jù)分布統(tǒng)計(jì)直方圖代價(jià)的計(jì)算基于統(tǒng)計(jì)信息操作符號(hào)的選擇基于成本的最優(yōu)執(zhí)行路徑選擇生成優(yōu)化的執(zhí)行計(jì)劃騰訊大講堂48-數(shù)據(jù)庫查詢優(yōu)化淺析基于操作符的執(zhí)行計(jì)劃物理操作符功能簡(jiǎn)單專一,完成特定的數(shù)據(jù)庫操作如:連接,過濾,排序等執(zhí)行計(jì)劃操作符的組合,構(gòu)成樹型的執(zhí)行計(jì)劃執(zhí)行時(shí),數(shù)據(jù)從葉子向根流動(dòng)在根匯集成結(jié)果集,或者得到結(jié)果狀態(tài)計(jì)劃被自動(dòng)緩存,以備重用騰訊大講堂48-數(shù)據(jù)庫查詢優(yōu)化淺析實(shí)例分析select*fromA,B,C,DwhereA.a＝B.bandB.b1=C.candC.c1=D.dandA.a1>123騰訊大講堂48-數(shù)據(jù)庫查詢優(yōu)化淺析三、索引的本質(zhì)以及調(diào)優(yōu)騰訊大講堂48-數(shù)據(jù)庫查詢優(yōu)化淺析需要了解的概念索引聚集索引、非聚集索引唯一索引、非唯一索引單列索引、組合索引騰訊大講堂48-數(shù)據(jù)庫查詢優(yōu)化淺析騰訊大講堂48-數(shù)據(jù)庫查詢優(yōu)化淺析騰訊大講堂48-數(shù)據(jù)庫查詢優(yōu)化淺析Clusteredtable/index騰訊大講堂48-數(shù)據(jù)庫查詢優(yōu)化淺析lusteredindexonaclusteredtable

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

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

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

騰訊大講堂48-數(shù)據(jù)庫查詢優(yōu)化淺析索引調(diào)優(yōu)的6個(gè)知識(shí)點(diǎn)1、將索引和數(shù)據(jù)存放到不同的文件組2、組合索引的使用3、唯一索引與非唯一索引的差異4、非聚集索引的作用5、是不是使用非聚集索引的查詢都需要進(jìn)行聚集的查詢6、創(chuàng)建索引的規(guī)則騰訊大講堂48-數(shù)據(jù)