MySQL查询优化浅析.pdf

MySQL查询优化浅析
网易杭研-何登成
个人简介
•姓名:何登成

•工作:
–就职于网易杭州研究院,进行自主研发的TNT存储引擎的架构设计/研发工作

•联系方式
–邮箱:@
–微博:何_登成
–主页:/
何为查询优化?
•目标
–给定一个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 / PARE = 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

•计算range占用多少个二级索引页面,既为索引覆盖扫描的IO Cost
Range Query-二级索引
•二级索引(IO Cost 续)
–索引非覆盖扫描
•索引非覆盖扫描,需要回聚簇索引读取完整记录,增加IO代价

• IO Cost = (ranges + rows)

• ranges:多少个范围。
–对于IN查询,就会转换为多个索引范围查询

• rows:为范围中一共有多少记录。
–由于每一条记录都需要返回聚簇索引,因此每一条记录都会产生 1 cost