MySQL查詢優(yōu)化淺析(2013-02-22更新版).pdf

《MySQL查詢優(yōu)化淺析(2013-02-22更新版).pdf》由會員上傳分享，免費在線閱讀，更多相關(guān)內(nèi)容在學(xué)術(shù)論文-天天文庫。

1、MySQL查詢優(yōu)化淺析網(wǎng)易杭研-何登成個人簡介?姓名：何登成?工作：–就職于網(wǎng)易杭州研究院，進(jìn)行自主研發(fā)的TNT存儲引擎的架構(gòu)設(shè)計/研發(fā)工作?聯(lián)系方式–郵箱：he.dengcheng@gmail.com–微博：何_登成–主頁：http://hedengcheng.com/何為查詢優(yōu)化？?目標(biāo)–給定一個SQL，查找SQL最優(yōu)(局部最優(yōu))的執(zhí)行路徑，使得用戶能夠更快的得到SQL執(zhí)行的結(jié)果?指標(biāo)–代價模型；–SQL的每一種執(zhí)行路徑，均可計算一個對應(yīng)的執(zhí)行代價，代價越小，執(zhí)行效率越高；反之則反之；大綱?MySQL

2、Optimizer流程?MySQLRangeOptimizer(分享重點)–Cost模型–統(tǒng)計信息?MySQLServer層統(tǒng)計信息?InnoDB層統(tǒng)計信息?統(tǒng)計信息持久化?動態(tài)收集統(tǒng)計信息?統(tǒng)計信息收集策略–RangeQueryExamples?MySQLJoinOptimizer?MySQLOptimizerEnhancement總流程MySQLRangeOptimizer?RangeOptimizer有哪些問題–全表掃描or索引掃描選擇？–全表掃描的代價如何計算？–聚簇索引Range查詢代價如何計算

3、？–二級索引Range查詢代價如何計算？–索引覆蓋掃描vs索引非覆蓋掃描？–表級統(tǒng)計信息有哪些？–統(tǒng)計信息在Range查詢優(yōu)化中何用？–統(tǒng)計信息何時收集？收集算法？RangeQuery-代價模型?總代價模型–COST=CPUCost+IOCost?CPUCost–MySQL上層，處理返回記錄所花開銷–CPUCost=records/TIME_FOR_COMPARE=records/5–每5條記錄的處理時間，作為1Cost?IOCost–存儲引擎層面，讀取頁面的IO開銷。–以下InnoDB為例?聚簇索引?二

4、級索引RangeQuery-聚簇索引?聚簇索引(IOCost)–全掃描?IOCost=table->stat_clustered_index_size?聚簇索引頁面總數(shù)?一個頁面作為1Cost–范圍掃描?IOCost=[(ranges+rows)/total_rows]*全掃描IOCost?聚簇索引范圍掃描與返回的記錄成比率。RangeQuery-二級索引?二級索引(IOCost)–索引覆蓋掃描?索引覆蓋掃描，減少了返回聚簇索引的IO代價–keys_per_block=(stats_block_size/

5、2)/(key_info[keynr].key_length+ref_length+1)–stats_block_size/2?索引頁半滿?IOCost–(records+keys_per_block-1)/keys_per_block?計算range占用多少個二級索引頁面，既為索引覆蓋掃描的IOCostRangeQuery-二級索引?二級索引(IOCost續(xù))–索引非覆蓋掃描?索引非覆蓋掃描，需要回聚簇索引讀取完整記錄，增加IO代價?IOCost=(ranges+rows)?ranges：多少個范圍。–

6、對于IN查詢，就會轉(zhuǎn)換為多個索引范圍查詢?rows：為范圍中一共有多少記錄。–由于每一條記錄都需要返回聚簇索引，因此每一條記錄都會產(chǎn)生1costCost模型分析?聚簇索引掃描代價為索引頁面總數(shù)量?二級索引覆蓋掃描代價較小?二級索引非覆蓋掃描，代價巨大–未考慮類似于Oracle中的聚簇因子(Clusterfactor)影響？?Cost模型的計算，需要統(tǒng)計信息的支持–stat_clustered_index_size–ranges–records/rows–stats_block_size–key_info[

7、keynr].key_length–rec_per_key–...統(tǒng)計信息?MySQLServer層的統(tǒng)計信息–ha_statistics?引擎負(fù)責(zé)設(shè)置–CONST–VARIABLE?InnoDB層的統(tǒng)計信息–dict_table_struct?語句級統(tǒng)計信息–每個查詢語句，指定不同的Range?不同的Range，包含的records數(shù)量不同?同一Range，不同的索引，包含的records數(shù)量不同–records_in_rangeMySQLServer層統(tǒng)計信息?CONST統(tǒng)計信息–此類統(tǒng)計信息，在表創(chuàng)

8、建之后，就基本維持不變，類似于常量(非完全不變)?種類–max_data_file_length、data_file_name、block_size...不變–block_size?計算索引覆蓋掃描Cost所需，頁面大小–rec_per_key...會變化?標(biāo)識一個索引鍵(包括前綴鍵值)相同相同取值的平均個數(shù)?算法：rec_per_key=total_rows/key_distinct_count?此參數(shù)，是MySQL進(jìn)行Jo