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

2、st模型 統(tǒng)計信息 MySQL Server層統(tǒng)計信息 InnoDB層統(tǒng)計信息 統(tǒng)計信息持久化 動態(tài)收集統(tǒng)計信息 統(tǒng)計信息收集策略 Range Query Examples MySQL Join Optimizer MySQL Optimizer Enhancement總流程 MySQL Range Optimizer Range Optimizer有哪些問題 全表掃描or 索引掃描選擇？ 全表掃描的代價如何計算？ 聚簇索引Range查詢代價如何計算？ 二級索引Range查詢代價如何計算？ 索引覆蓋掃描vs 索引非覆蓋掃描？ 表級統(tǒng)計信息有哪些？ 統(tǒng)計信息在Range查詢優(yōu)化中何用？ 統(tǒng)計信息

3、何時收集？收集算法？Range Query-代價模型 總代價模型 COST = CPU Cost + IO Cost CPU Cost MySQL上層，處理返回記錄所花開銷 CPU Cost = records / TIME_FOR_COMPARE = records / 5 每5條記錄的處理時間，作為 1 Cost IO Cost 存儲引擎層面，讀取頁面的IO開銷。 以下InnoDB為例 聚簇索引 二級索引Range Query-聚簇索引 聚簇索引(IO Cost) 全掃描 IO Cost = table-stat_clustered_index_size 聚簇索引頁面總數(shù) 一個頁面作為 1

4、 Cost 范圍掃描 IO Cost = (ranges + rows) / total_rows * 全掃描IO Cost 聚簇索引范圍掃描與返回的記錄成比率。Range Query-二級索引 二級索引(IO Cost) 索引覆蓋掃描 索引覆蓋掃描，減少了返回聚簇索引的IO代價 keys_per_block =(stats_block_size / 2) / (key_infokeynr.key_length + ref_length + 1) stats_block_size / 2 索引頁半滿 IO Cost (records + keys_per_block - 1) / keys_p

5、er_block 計算range占用多少個二級索引頁面，既為索引覆蓋掃描的IO Cost 二級索引(IO Cost 續(xù)) 索引非覆蓋掃描 索引非覆蓋掃描，需要回聚簇索引讀取完整記錄，增加IO代價 IO Cost = (ranges + rows) ranges：多少個范圍。 對于IN查詢，就會轉(zhuǎn)換為多個索引范圍查詢 rows：為范圍中一共有多少記錄。 由于每一條記錄都需要返回聚簇索引，因此每一條記錄都會產(chǎn)生 1 costCost模型分析 聚簇索引掃描代價為索引頁面總數(shù)量 二級索引覆蓋掃描代價較小 二級索引非覆蓋掃描，代價巨大 未考慮類似于Oracle中的聚簇因子(Cluster factor)

6、影響？ Cost模型的計算，需要統(tǒng)計信息的支持 stat_clustered_index_size ranges records/rows stats_block_size key_infokeynr.key_length rec_per_key.統(tǒng)計信息 MySQL Server層的統(tǒng)計信息 ha_statistics 引擎負責設置 CONST VARIABLE InnoDB層的統(tǒng)計信息 dict_table_struct 語句級統(tǒng)計信息 每個查詢語句，指定不同的Range 不同的Range，包含的records數(shù)量不同 同一Range，不同的索引，包含的records數(shù)量不同 record

7、s_in_rangeMySQL Server層統(tǒng)計信息 CONST統(tǒng)計信息 此類統(tǒng)計信息，在表創(chuàng)建之后，就基本維持不變，類似于常量(非完全不變) 種類 max_data_file_length、data_file_name、block_size. 不變 block_size 計算索引覆蓋掃描Cost所需，頁面大小 rec_per_key. 會變化 標識一個索引鍵(包括前綴鍵值)相同相同取值的平均個數(shù) 算法：rec_per_key = total_rows / key_distinct_count 此參數(shù)，是MySQL進行Join Optimize的基礎 收集策略 表第一次open analyz

8、e命令 由InnoDB收集，并返回MySQL Server VARIABLE統(tǒng)計信息 此類統(tǒng)計信息，隨著記錄的U/D/I操作，會發(fā)生顯著的變化 種類 records：記錄數(shù)量 直接從InnoDB的統(tǒng)計信息中復制，不重新收集n_rows= ib_table-stat_n_rows; stats.records = (ha_rows)n_rows; 計算全表掃描CPU代價； data_file_length：聚簇索引總大小(非葉+ 葉) index_file_length：所有二級索引總大小. 收集策略 表第一次open analyze命令 語句執(zhí)行時InnoDB層統(tǒng)計信息 InnoDB層統(tǒng)計信息

9、 除了設置MySQL Server層統(tǒng)計信息外，還在本層維護了自身的統(tǒng)計信息 根據(jù)此統(tǒng)計信息，計算全表掃描/索引掃描代價 主要統(tǒng)計信息 stat_n_rows 表記錄數(shù)量；I/U/D操作時，實時修改； 用于設置MySQL Server層的records信息 stat_clustered_index_size 聚簇索引頁面總數(shù)量 計算MySQL Server層，data_file_length信息 計算全表掃描IO代價 stat_sum_of_other_index_size stat_modified_counter I/U/D，此值+ 收集策略 第一次open stat_modified_c

10、ounter取值：( 2 000 000 000) or (stat_n_rows/16) 收集算法 統(tǒng)計索引中葉頁面數(shù)量 index-stat_n_leaf_pages 隨機定位索引中的8個葉頁面 srv_stats_sample_pages = 8; 統(tǒng)計頁面中，前綴索引列組合的Distinct數(shù)量 例如：Index idx (a, b, c)，包含3列 Distincta = ? ; Distincta, b = ? ; Distincta,b,c = ? 根據(jù)以上信息，計算 表數(shù)據(jù)量 每個索引前綴組合的Distinct數(shù)量 用于計算MySQL Server層的rec_per_key信息

11、 是Join Optimizer最重要的統(tǒng)計信息 優(yōu)化統(tǒng)計信息持久化：MySQL 5.6.2 統(tǒng)計信息更準確：增加Sample Rate srv_stats_persistent_sample_pages = 20;primary idx_bc stat_n_diff_key_valsb = 3; stat_n_diff_key_valsb,c = 5; rec_per_keyb = 8/3; rec_per_keyb,c = 8/5; stat_n_rows = 8; stat_n_diff_key_vals = 8; stat_clustered_index_size = 3; rec_p

12、er_keya = 1; Create table t1 (a int primary key, b int, c int, d int) engine = innodb; create index idx_bc on t1 (b, c); (1,1,1,1),(2,1,1,2),(3,2,1,3),(4,3,3,4),(5,3,3,5),(7,3,4),(8,3,5),(9,1,1) 4 9 2 3 1 5 1 2 3 4 5 7 8 9 1 1 1 2 1 1 1 1 3 3 3 3 3 3 4 5 統(tǒng)計信息持久化 支持版本 From MySQL 5.6.2 哪些統(tǒng)計信息持久化 Table

13、 n_rows表記錄數(shù)量 clustered_index_size聚簇索引大小 sum_of_other_index_sizes其他索引總大小 Index number of index pages索引大小 number of index leaf pages索引葉頁面數(shù)量 n_diff索引前綴組合不同取值數(shù)量統(tǒng)計信息持久化(續(xù)) 持久化統(tǒng)計信息存儲 mysql.innodb_table_stats mysql.innodb_index_stats 持久化統(tǒng)計信息修改 analyze命令 analyze table *; 更新超過一定數(shù)量，自動收集 參數(shù)：innodb_stats_auto_r

14、ecals 直接修改統(tǒng)計信息表 If you can and know definitely what will happen SQL update mysql.innodb_index_stats set stat_value = 0 where table_name = t1 and stat_name = n_diff_pfx01; 注意事項 持久化的是表級與索引級全局統(tǒng)計信息 不包括索引范圍統(tǒng)計信息 索引范圍統(tǒng)計信息見本PPT后面的Statement級統(tǒng)計信息 持久化統(tǒng)計信息作用 全表掃描代價估計 全索引掃描代價估計 Join查詢代價估計Statement級統(tǒng)計信息 語句級統(tǒng)計信息 到

15、目前為止，MySQL/InnoDB尚缺少哪些統(tǒng)計信息呢？ stat_clustered_index_size (已有) Ranges (根據(jù)where條件分析得出) Records/Rows (無) stats_block_size (已有) key_infokeynr.key_length (MySQL上層維護) rec_per_key (已計算) rows統(tǒng)計信息 功能 聚簇索引范圍查詢 二級索引覆蓋范圍掃描 二級索引非覆蓋范圍掃描 無rows(records)統(tǒng)計信息 無法進行范圍查詢 records_in_range 每個范圍查詢，在查詢優(yōu)化階段，針對每一個可選的索引，都會調(diào)用存儲引擎

16、層面提供的records_in_range函數(shù)，計算查詢范圍中的記錄數(shù)量：rows/records 算法簡析 Range Start Range End N_PAGES_READ_LIMIT default: 10 越大：越精確，性能越差 越?。? 輸出 estimate rows between range start, range end rows records_in_range = records_in_upper_level(葉頁面數(shù)) * records_per_leafRange Query-Possible Keys 我們已經(jīng)能計算什么？ 以下各種訪問路徑的Cost：CPU +

17、 IO 全表掃描 聚簇索引范圍掃描 二級索引掃描 二級索引范圍掃描(Index Coverage) 二級索引范圍掃描(No Coverage) 尚缺少什么？ 對于一個Range Query，哪些索引是可選索引？ Possible Keys 以where中的部分算子作用列打頭的索引 可選算子：, =, =, , =, in select * from t1 where a 算子; 若有以a打頭的索引idx_a，則idx_a即為一個possible keyRange Query Optimizer流程 Range Query Optimizer主流程 1. 根據(jù)查詢條件，計算所有的possible

18、 keys； 2. 計算全表掃描代價 Cost_all 3. 計算最小的索引范圍訪問代價； 對于每一個possible keys(可選索引)，調(diào)用records_in_ranges函數(shù)，計算范圍中的rows； 根據(jù)rows，計算二級索引訪問代價； 獲取Cost最小的二級索引訪問：Cost_range； 4. 對比全表掃描代價與索引范圍代價 Cost_all Cost_range 索引范圍掃描； 流程分析 Range Query Optimizer，最慢的在于步驟3 減少possible keys； 減少records_in_range調(diào)用；Range Query Optimizer 舉例Cre

19、ate table t1 (a int primary key, b int, c int, d int) engine = innodb; create index idx_bc on t1 (b, c); (1,1,1,1),(2,1,1,2),(3,2,1,3),(4,3,3,4),(5,3,3,5),(7,3,4),(8,3,5),(9,1,1) 4 9 2 3 1 5 primaryidx_bc 1 2 3 4 5 7 8 9 1 1 1 2 1 1 1 1 3 3 3 3 3 3 4 5 stat_n_rows = 8; stat_n_diff_key_vals = 8; stat

20、_clustered_index_size = 3; rec_per_keya = 1; stat_n_diff_key_valsb = 3; stat_n_diff_key_valsb,c = 5; rec_per_keyb = 8/3; rec_per_keyb,c = 8/5; SQL舉例 select * from t1; select b,c from t1 where b = 1; select * from t1 where b = 1; select * from t1 where b = 3 and c = 4; select * from t1 where c = 3; C

21、ost計算 Cost = CPU Cost + I/O Cost SQLs select * from t1; 聚簇索引全掃描：Cost = 8/5 + 1(微調(diào)) + 3 + 1.1(微調(diào)) select b,c from t1 where b = 1; idx_bc (Index Coverage Scan)：Cost = 3/5 + 1(IO Cost) select * from t1 where b = 1; idx_bc (Non Coverage Scan)：Cost = 3/5 + (1 + 3) select * from t1 where b = 3 and c = 4;

22、idx_bc (Non Coverage Scan)：Cost = 1/5 + (1 + 1) select * from t1 where c = 3; 聚簇索引全掃描Join Optimizer Join Optimizer簡介 根據(jù)給定的join查詢，計算代價最小的查詢計劃 表的join順序最優(yōu) 每張表的執(zhí)行路徑最優(yōu) 遞歸窮舉所有可能的組合與執(zhí)行路徑 optimizer_search_depth 控制遞歸窮舉深度 optimizer_search_depth = join tables 執(zhí)行計劃全局最優(yōu)，代價高 optimizer_search_depth 執(zhí)行計劃局部最優(yōu)，代價低 re

23、c_per_key 根據(jù)此參數(shù)，計算對于Join內(nèi)表中的一條記錄，外表有多少Join到的記錄；Optimizer-分析 Range Optimizer IO代價較高，possible_keys越多，隨機IO代價越高 records_in_range結(jié)果不穩(wěn)定，導致range查詢優(yōu)化的結(jié)果不穩(wěn)定 Join Optimizer CPU代價較高 join的tables越多，窮舉最優(yōu)執(zhí)行計劃的代價越高 OLTP使用 更應該關(guān)注range查詢優(yōu)化代價，盡量較少possible_keysOptimizer 優(yōu)化 Count 優(yōu)化 可優(yōu)化SQL select count(*) from .; select

24、count(primary key) from .; select count(index key) from .; 優(yōu)化方案 將全表掃描(聚簇索引全掃描)轉(zhuǎn)換為索引鍵值最短的可選索引； sql_select.cc:make_join_readinfo(); 優(yōu)化原理 1. 索引鍵值越短，需要讀取的頁面越少，IO Cost越小； 2. 二級索引存儲Primary Key； 3. Primary Key列，在二級索引不會重復存儲； ROR 優(yōu)化 ROR 定義 Rowid Ordered Retrieval； 若二級索引的部分前導列與聚簇索引的部分前導列完全一致，則說明二級索引回聚簇索引的順序與聚

25、簇索引鍵值順序基本一致，此時二級索引回表的代價較小，可進行優(yōu)化； 思索：是否有點類似于Oracle的Cluster Factor(聚簇因子)？ ROR 優(yōu)化 滿足ROR條件的二級索引，索引非覆蓋掃描的代價，使用新的計算公式； 降低ROR索引的Cost，增加二級索引訪問概率； ROR Cost Coverage Scan：與原有一致； Non-Coverage Scan ROR Cost = Index Coverage Scan Cost + Cluster Index Range Scan Cost opt_range.cc:get_best_ror_intersect(); In 優(yōu)化 In查詢中所有的取值，均組成一個Range查詢； In的值越多 范圍查詢越多 Index Dive vs No Index Dive (MySQL 5.6) Index Dive 使用records_in_range，估算每個in