mysql查询优化浅析

《mysql查询优化浅析》由会员上传分享，免费在线阅读，更多相关内容在学术论文-天天文库。

1、MySQL查询优化浅析网易杭研-何登成个人简介•姓名：何登成•工作：–就职于网易杭州研究院，进行自主研发的TNT存储引擎的架构设计/研发工作•联系方式–邮箱：he.dengcheng@gmail.com–微博：何_登成–主页：http://hedengcheng.com/何为查询优化？•目标–给定一个SQL，查找SQL最优(局部最优)的执行路径，使得用户能够更快的得到SQL执行的结果•指标–代价模型；–SQL的每一种执行路径，均可计算一个对应的执行代价，代价越小，执行效率越高；反之则反之；大纲•MySQLOptimizer流程•MySQLRangeOptimiz

2、er(分享重点)–Cost模型–统计信息•MySQLServer层统计信息•InnoDB层统计信息•动态收集统计信息•统计信息收集策略–RangeQueryExamples•MySQLJoinOptimizer•MySQLOptimizerEnhancement总流程MySQLRangeOptimizer•RangeOptimizer有哪些问题–全表扫描or索引扫描选择？–全表扫描的代价如何计算？–聚簇索引Range查询代价如何计算？–二级索引Range查询代价如何计算？–索引覆盖扫描vs索引非覆盖扫描？–表级统计信息有哪些？–统计信息在Range查询优化中何用

3、？–统计信息何时收集？收集算法？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•聚簇索引页面总数•一个页面作为1Cos

4、t–范围扫描•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•计算ran

5、ge占用多少个二级索引页面，既为索引覆盖扫描的IOCostRangeQuery-二级索引•二级索引(IOCost续)–索引非覆盖扫描•索引非覆盖扫描，需要回聚簇索引读取完整记录，增加IO代价•IOCost=(ranges+rows)•ranges：多少个范围。–对于IN查询，就会转换为多个索引范围查询•rows：为范围中一共有多少记录。–由于每一条记录都需要返回聚簇索引，因此每一条记录都会产生1costCost模型分析•聚簇索引扫描代价为索引页面总数量•二级索引覆盖扫描代价较小•二级索引非覆盖扫描，代价巨大–未考虑类似于Oracle中的聚簇因子(Clusterf

6、actor)影响？•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，不

7、同的索引，包含的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

8、进行JoinOptimi