MySQL查询优化浅析--何登成.pdf

MySQL查询优化浅析 网易杭研-何登成 个人简介 • 姓名：何登成 • 工作： – 就职于网易杭州研究院，进行自主研发的TNT存储引擎的架构设计/研发工作 • 联系方式 – 邮箱：he.dengcheng@ – 微博：何_登成 – 主页：/ 何为查询优化？ • 目标 – 给定一个SQL，查找SQL最优(局部最优)的执行路径，使 得用户能够更快的得到SQL执行的结果 • 指标 – 代价模型； – SQL 的每一种执行路径，均可计算一个对应的执行代价， 代价越小，执行效率越高；反之则反之； 大纲 • MySQL Optimizer流程 • MySQL Range Optimizer (分享重点) – Cost模型 – 统计信息 • MySQL Server层统计信息 • InnoDB层统计信息 • 动态收集统计信息 • 统计信息收集策略 – Range Query Examples • MySQL Join Optimizer • MySQL Optimizer Enhancement 总流程 MySQL Range Optimizer • Range Optimizer有哪些问题 – 全表扫描or 索引扫描选择？ – 全表扫描的代价如何计算？ – 聚簇索引Range查询代价如何计算？ – 二级索引Range查询代价如何计算？ – 索引覆盖扫描vs 索引非覆盖扫描？ – 表级统计信息有哪些？ – 统计信息在Range查询优化中何用？ – 统计信息何时收集？收集算法？ 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 • 聚簇索引页面总数 • 一个页面作为1 Cost – 范围扫描 • IO Cost = [(ranges + rows) / total_rows ] * 全扫描IO Cost • 聚簇索引范围扫描与返回的记录成比率。 Range Query-二级索引 • 二级索引(IO Cost) – 索引覆盖扫描 • 索引覆盖扫描，减少了返回聚簇索引的IO代价 – keys_per_block = (stats_block_size / 2) / (key_info[keynr].key_length + ref_length + 1) – stats_block_size / 2  索引页半满 • IO Cost – (records + keys_per_block - 1) / keys_per_block • 计算ra