MySQL查询优化浅析

MySQL查询优化浅析网易杭研-何登成个人简介•姓名：何登成•工作：–就职于网易杭州研究院，进行自主研发的TNT存储引擎的架构设计/研发工作•联系方式–邮箱：[e-mail]–微博：何_登成–主页：何为查询优化？•目标–给定一个SQL，查找SQL最优(局部最优)的执行路径，使得用户能够更快的得到SQL执行的结果•指标–代价模型；–SQL的每一种执行路径，均可计算一个对应的执行代价，代价越小，执行效率越高；反之则反之；大纲•MySQLOptimizer流程•MySQLRangeOptimizer(分享重点)–Cost模型–统计信息•MySQLServer层统计信息•InnoDB层统计信息•动态收集统计信息•统计信息收集策略–RangeQueryExamples•MySQLJoinOptimizer•MySQLOptimizerEnhancement总流程MySQLRangeOptimizer•RangeOptimizer有哪些问题–全表扫描or索引扫描选择？–全表扫描的代价如何计算？–聚簇索引Range查询代价如何计算？–二级索引Range查询代价如何计算？–索引覆盖扫描vs索引非覆盖扫描？–表级统计信息有哪些？–统计信息在Range查询优化中何用？–统计信息何时收集？收集算法？RangeQuery-代价模型•总代价模型–COST=CPUCost+IOCost•CPUCost–MySQL上层，处理返回记录所花开销–CPUCost=records/TIME_FOR_COMPARE=records/5–每5条记录的处理时间，作为1Cost•IOCost–存储引擎层面，读取页面的IO开销。–以下InnoDB为例•聚簇索引•二级索引RangeQuery-聚簇索引•聚簇索引(IOCost)–全扫描•IOCost=table-stat_clustered_index_size•聚簇索引页面总数•一个页面作为1Cost–范围扫描•IOCost=[(ranges+rows)/total_rows]*全扫描IOCost•聚簇索引范围扫描与返回的记录成比率。RangeQuery-二级索引•二级索引(IOCost)–索引覆盖扫描•索引覆盖扫描，减少了返回聚簇索引的IO代价–keys_per_block=(stats_block_size/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续)–索引非覆盖扫描•索引非覆盖扫描，需要回聚簇索引读取完整记录，增加IO代价•IOCost=(ranges+rows)•ranges：多少个范围。–对于IN查询，就会转换为多个索引范围查询•rows：为范围中一共有多少记录。–由于每一条记录都需要返回聚簇索引，因此每一条记录都会产生1costCost模型分析•聚簇索引扫描代价为索引页面总数量•二级索引覆盖扫描代价较小•二级索引非覆盖扫描，代价巨大–未考虑类似于Oracle中的聚簇因子(Clusterfactor)影响？•Cost模型的计算，需要统计信息的支持–stat_clustered_index_size–ranges–records/rows–stats_block_size–key_info[keynr].key_length–rec_per_key–...统计信息•MySQLServer层的统计信息–ha_statistics•引擎负责设置–CONST–VARIABLE•InnoDB层的统计信息–dict_table_struct•语句级统计信息–每个查询语句，指定不同的Range•不同的Range，包含的records数量不同•同一Range，不同的索引，包含的records数量不同–records_in_rangeMySQLServer层统计信息•CONST统计信息–此类统计信息，在表创建之后，就基本维持不变，类似于常量(非完全不变)•种类–max_data_file_length、data_file_name、block_size...不变–block_size•计算索引覆盖扫描Cost所需，页面大小–rec_per_key...会变化•标识一个索引键(包括前缀键值)相同相同取值的平均个数•算法：rec_per_key=total_rows/key_distinct_count•此参数，是MySQL进行JoinOptimize的基础•收集策略–表第一次open–analyze命令–由InnoDB收集，并返回MySQLServerMySQLServer层统计信息•VARIABLE统计信息–此类统计信息，随着记录的U/D/I操作，会发生显著的变化•种类–records：记录数量•直接从InnoDB的统计信息中复制，不重新收集n_rows=ib_table-stat_n_rows;stats.records=(ha_rows)n_rows;•计算全表扫描CPU代价；–data_file_length：聚簇索引总大小(非叶+叶)–index_file_length：所有二级索引总大小–...•收集策略–表第一次open–analyze命令–语句执行时InnoDB层统计信息•InnoDB层统计信息–除了设置MySQLServer层统计信息外，还在本层维护了自身的统计信息–根据此统计信息，计算全表扫描/索引扫描代价•主要统计信息–stat_n_rows•表记录数量；I/U/D操作时，实时修改；•用于设置MySQLServer层的records信息–stat_clustered_index_size•聚簇索引页面总数量•计算MySQLServer层，data_file_length信息•计算全表扫描IO代价–stat_sum_of_other_index_size–stat_modified_counter•I/U/D，此值++•收集策略–第一次open–stat_modified_counter取值：(2000000000)or(stat_n_rows/16)InnoDB层统计信息•收集算法–统计索引中叶页面数量•index-stat_n_leaf_pages–随机定位索引中的8个叶页面•srv_stats_sample_pages=8;–统计页面中，前缀索引列组合的Distinct数量•例如：Indexidx(a,b,c)，包含3列•Distinct[a]=?;Distinct[a,b]=?;Distinct[a,b,c]=?–根据以上信息，计算•表数据量•每个索引前缀组合的Distinct数量–用于计算MySQLServer层的rec_per_key信息–是JoinOptimizer最重要的统计信息•优化–统计信息持久化：MySQL5.6.2–统计信息更准确：增加SampleRate•srv_stats_persistent_sample_pages=20;InnoDB层统计信息Createtablet1(aintprimarykey,bint,cint,dint)engine=innodb;createindexidx_bcont1(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)491234578921351111112133333435primaryidx_bcstat_n_rows=8;stat_n_diff_key_vals=8;stat_clustered_index_size=3;rec_per_key[a]=1;stat_n_diff_key_vals[b]=3;stat_n_diff_key_vals[b,c]=5;rec_per_key[b]=8/3;rec_per_key[b,c]=8/5;Statement级统计信息•语句级统计信息–到目前为止，MySQL/InnoDB尚缺少哪些统计信息呢？–stat_clustered_index_size(已有)–Ranges(根据where条件分析得出)–Records/Rows(无)–stats_block_size(已有)–key_info[keynr].key_length(MySQL上层维护)–rec_per_key(已计算)•rows统计信息–功能•聚簇索引范围查询•二级索引覆盖范围扫描•二级索引非覆盖范围扫描–无rows(records)统计信息•无法进行范围查询Statement级统计信息•records_in_range–每个范围查询，在查询优化阶段，针对每一个可选的索引，都会调用存储引擎层面提供的records_in_range函数，计算查询范围中的记录数量：rows/records•算法简析–RangeStart–RangeEnd–N_PAGES_READ_LIMIT•default:10•越大：越精确，性能越差•越小：...–输出•estimaterowsbetween[rangestart,rangeend]–rows•records_in_range=records_in_upper_level(叶页面数)*records_per_leafRangeQuery-PossibleKeys•我们已经能计算什么？–以下各种访问路径的Cost：CPU+IO•全表扫描•聚簇索引范围扫描•二级索引扫描•二级索引范围扫描(IndexCoverage)•二级索引范围扫描(NoCoverage)•尚缺少什么？–对于一个RangeQuery，哪些索引是可选索引？•PossibleKeys–以where中的部分算子作用列打头的索引•可选算子：,=,=,,=,in…•select*fromt1wherea算子…;–若有以a打头的索引idx_a，则idx_a即为一个possiblekeyRangeQueryOptimizer流程•RangeQueryOptimizer主流程–1.根据查询条件，计算所有的possiblekeys；–2.计算全表扫描代价•Cost_all–3.计算最小的索引范围访问代价；•对于每一个possiblekeys(可选索引)，调用records_in_ranges函数，计算范围中的rows；•根据rows，计算二级索引访问代价；•获取Cost最小的二级索引访问：Cost_range；–4.对比全表扫描代价与索引范围代价•Cost_allCost_range全表扫描；•Cost_allCost_range索引范围扫描；•流程分析–RangeQueryOptimizer，最慢的在于步骤3•减少possiblekeys；•减少records_in_range调用；RangeQueryOptimizer–举例Createtablet1(aintprimarykey,bint,cint,dint)engine=innodb;createindexidx_bcont1(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)491234578921351111112133333435primaryidx_bcstat_n_rows=8;stat_n_diff_key_vals=8;stat_clustered_index_size=3;rec_per_key[a]=1;stat_n_diff_key_vals[b]=3;stat_n_diff_key_vals[b,c]=5;rec_per_key[b]=8/3