MySQL 查询优化器介绍

FROM: MySQL数据库Query的优化系列均来自51CTO读书频道

在 MySQL 中有一个专门负责优化 SELECT 语句的优化器模块 —— MySQL Query Optimizer，其主要的功能是通过计算分析系统中收集的各种统计信息，为客户端请求的 Query 给出最优的执行计划，也就是最优的数据检索方式。 当 MySQL Query Optimizer 接收到从 Query Parser (解析器) 过来的 Query 时，会根据 MySQL Query 语句的相应语法对该 Query 进行分解分析，同时还会做很多其他的计算转化工作，如常量转化, 无效内容删除, 常量计算等。所有这些工作都是为了 Optimizer 分析出最优的数据检索方式，也就是常说的执行计

在分析 MySQL Query Optimizer 的工作原理之前，先了解一下 MySQL 的 Query Tree。MySQL 的Query Tree是通过优化实现 DBXP 的经典数据结构 和 Tree构造器 而生成的，是指导完成一个 Query 语句的请求须要处理的工作步骤，我们可以简单地认为就是一个的数据处理流程，只是以 Tree 的数据结构存放而已。通过 Query Tree可以很清楚地知道一个 Query 的完成须要经过哪些步骤，每一步的数据来源在哪里，处理方式是怎样的。在整个DBXP 的Query Tree 生成过程中，MySQL 使用了LEX 和 YACC 这两个功能非常强大的语法 (词法) 分析工具。MySQL Query Optimizer的所有工作都是基于这个Query Tree进行的。各位朋友如果对MySQL Query Tree 实现生成的详细信息比较感兴趣，可以参考 Chales A. Bell 的《Expert MySQL》这本书，里面有比较详细的介绍。

MySQL Query Optimizer 并不是一个纯粹的CBO (Cost Base Optimizer) ，而是在CBO的基础上增加了一个被称为Heuristic Optimize (启发式优化) 的功能。也就是说，MySQL Query Optimizer在优化一个Query认为的最优执行计划时，并不一定完全按照系数据库的元信息和系统统计信息，而是在此基础上增加了某些特定的规则。 其实就是在CBO的实现中增加了部分RBO (Rule Base Optimizer) 的功能，以确保在某些特殊场景下控制 Query 按照预定的方式生成执行计划。

当客户端向MySQL 请求一条Query，命令解析器模块完成请求分类，区别出是 SELECT 并转发给MySQL Query Optimizer时，MySQL Query Optimizer 首先会对整条Query进行优化，处理掉一些常量表达式的预算，直接换算成常量值。并对 Query 中的查询条件进行简化和转换，如去掉一些无用或显而易见的条件、结构调整等。然后分析 Query 中的 Hint 信息 (如果有) ，看显示Hint信息是否可以完全确定该Query 的执行计划。如果没有 Hint 或Hint 信息还不足以完全确定执行计划，则会读取所涉及对象的统计信息，根据 Query 进行写相应的计算分析，然后再得出最后的执行计划。
Query 语句的优化思路和原则主要体现在以下几个方面：

• 优化更需要优化的Query;


• 定位优化对象的性能瓶颈;


• 明确优化目标;


• 从 Explain 入手;


• 多使用Profile;


• 永远用小结果集驱动大的结果集;


• 尽可能在索引中完成排序;


• 只取自己需要的Columns;


• 仅仅使用最有效的过滤条件;


• 尽可能避免复杂的Join和子查询。

上面所列的几点信息，前面4点可以理解为Query优化的一个基本思路，后面部分则是优化的基本原则。