Mysql查询优化器浅析(上)
Mysql查询优化器的工作是为查询语句选择合适的执行路径。查询优化器的代码一般是经常变动的,这和存储引擎不太一样。因此,需要理解最新版本的查询优化器是如何组织的,请参考相应的源代码。整体而言,优化器有很多相同性,对mysql一个版本的优化器做到整体掌握,理解起mysql新版本以及其他数据库的优化器都是类似的。
优化器会对查询语句进行转化,转化等价的查询语句。举个例子,优化器会将下面语句进行转化:
以下是代码片段: SELECT … WHERE 5=a; |
转化后的等价语句为:
以下是代码片段: SELECT … WHERE a=5; |
因为这两个语句的结果集是一致的,所以这两个语句是等价的。
这里我需要提出一点需要注意的,如果查询语句没带order by。查询语句1出现的结果为(1,1),(2,2),查询语句2出现的结果为(2,2),(1,1),我们会认为这是等价的,因为不带order by的查询语句是无序的,怎么排序都行。
2 代码组织
在内核当中handle_select()函数是处理查询语句的顶层函数,里面有两个分支,一个是处理带union的情况,另外一个是处理不带union的情况,这里我们只是列出一个简单的路径便于说明,调用层次见下图。
以下是代码片段: handle_select() mysql_select() JOIN::prepare() setup_fields() JOIN::optimize() /* optimizer is from here ... */ optimize_cond() opt_sum_query() make_join_statistics() get_quick_record_count() choose_plan() /* Find the best way to access tables */ /* as specified by the user. */ optimize_straight_join() best_access_path() /* Find a (sub-)optimal plan among all or subset */ /* of all possible query plans where the user */ /* controlls the exhaustiveness of the search. */ greedy_search() best_extension_by_limited_search() best_access_path() /* Perform an exhaustive search for an optimal plan */ find_best() make_join_select() /* ... to here */ JOIN::exec() |
上面的缩进表示函数的相互调用关系,因此可以看出handle_select()调用函数mysql_select(),mysql_select()调用JOIN::prepare(),等等。
mysql_select()首先调用函数JOIN::prepare()进行语句分析、元数据设置、子查询转化等等。然后调用函数JOIN::optimize()进行优化,选出最后的执行计划。最后调用函数JOIN::exec()执行该执行计划。
尽管出现了单词“JOIN”,这些优化函数是为所有的查询语句服务的,不管你是什么查询类型。
函数optimize_cond()和函数opt_sum_query()是执行一些转化操作。函数make_join_statistics()对所有可用索引统计信息进行分析。
3 常量转化
对类似下面的表达式可以进行转化:
以下是代码片段: WHERE column1 = column2 AND column2 = ’x’; |
因为我们知道:如果A=B and B=C,那么A=C。所以上面的表达式可以转化为:
以下是代码片段: WHERE column1 = ’x’ AND column2 = ’x’; |
对于column1 <operator> column2,只要<operator>是属于下面的操作符之一就可以进行类似的转化:
以下是引用片段: =,<,>,<=,>=,<>,<=>,LIKE |
从中我们也可以看出,对于BETWEEN的情况是不进行转换的。
4 无效代码的排除
见如下表达式:
以下是代码片段: WHERE 0=0 AND column1=’y’ |
因为第一个条件是始终为true的,所以可以移除该条件,变为:
以下是代码片段: WHERE column1=’y’ |
再见如下表达式:
以下是代码片段: WHERE (0=1 AND s1=5) OR s1=7 |
因为前一个括号内的表达式始终为false,因此可以移除该表达式,变为:
以下是代码片段: WHERE s1=7 |
一些情况下甚至可以将整个WHERE子句去掉,见下面的表达式:
以下是代码片段: WHERE (0=1 AND s1=5) |
我们可以看到,WHERE子句始终为FALASE,那么WHERE条件是不可能发生的。当然我们也可以讲,WHERE条件被优化掉了。
如果一个列的定义是不允许为NULL,那么:
以下是代码片段: WHERE not_null_column IS NULL |
该条件是始终为false的,再看:
以下是代码片段: WHERE not_null_column IS NOT NULL |
该条件是始终为true的,因此这样的表达式也是可以从条件表达式中删除的。
当然,也是有特殊情况的,比如在out join中,被定义为NOT NULL的列也可能包含NULL值。在这种情况下,IS NULL条件是被保留的。
当然优化器没有对所有的情况进行检测,因为这实在太复杂了。举个例子:
以下是代码片段: CREATE TABLE Table1(column1 CHAR(1)); … SELECT * FROM Table1 WHERE column1 = ’Canada’; |
尽管该条件是无效条件,优化器也不会将它移除。
5 常量计算
如下表达式:
以下是代码片段: WHERE columb1 = 1 + 2 |
转化为:
以下是代码片段: WHERE columb1 = 3 |
6 常量以及常量表
常量表的定义如下:
1) 一个表只有0行或者1行数据。
2) 在WHERE子句中包含条件column = constant,并且这些列是primary key,或者这些列是UNIQUE(假设该UNIQUE同时被定义为NOT NULL)。这样生成的查询结果也可以成为常量表。
如果表Table0定义中包含:
以下是代码片段: … PRIMARY KEY(column1,column2) |
再看下面的语法:
以下是代码片段: FROM Table0 … WHERE column1=5 AND column2=7 … |
那么该语句返回的就是常量表。
举个更简单的情况,建设Table1定义中包含:
以下是代码片段: … unique_not_null_column INT NOT NULL UNIQUE |
再看下面的语法:
以下是代码片段: FROM Table1 ... WHERE unique_not_null_column=5 |
该语句返回的也是常量表。
从例子中我们可以看出常量表最多只有1个行值。MySQL会预先评估常量表,找出这个值,然后将这个值引入到查询语句中进行优化,举例如下:
以下是代码片段: SELECT Table1.unique_not_null_column, Table2.any_column FROM Table1, Table2 WHERE Table1.unique_not_null_column = Table2.any_column AND Table1.unique_not_null_column = 5; |
在评估这个查询语句时,MySQL首先发现通过Table1.unique_not_null_column条件的限制,Table1会变成一个常量表。然后,取回该值。
如果取回操作失败(Table1中没有行满足条件unique_not_null_column = 5),那么该常量表就包含0行,那么如果对该语句执行EXPLAIN操作,会得到提示信息:
以下是引用片段: Impossible WHERE noticed after reading const tables |
另外一种情况是取回操作成功(Table1中严格只有一行满足条件unique_not_null_column = 5),那么常量表中包含一条数据,并且MySQL会将查询语句转化为:
以下是代码片段: SELECT 5, Table2.any_column FROM Table1, Table2 WHERE 5 = Table2.any_column AND 5 = 5; |
实际上,这个例子是个复杂的例子,这里面也用到了上文所说的常量转化。
建议继续学习:
- MySQL数据库InnoDB存储引擎查询优化器实现的分析之单表查询 (阅读:2471)
- Mysql查询优化器浅析(下) (阅读:2088)
- MySQL数据库InnoDB存储引擎查询优化器实现的分析之单表unique查询 (阅读:2091)
- MySQL数据库InnoDB存储引擎查询优化器实现的分析之多表简单JOIN查询 (阅读:2007)
- MySQL数据库InnoDB存储引擎查询优化器实现的分析 (阅读:1970)
- MySQL数据库InnoDB存储引擎查询优化器实现的分析之optimizer_search_depth参数 (阅读:1898)
- MySQL数据库InnoDB存储引擎查询优化器实现的分析之附录 (阅读:1851)
- MySQL数据库InnoDB存储引擎查询优化器实现的分析之统计信息 (阅读:1645)
- MySQL数据库InnoDB存储引擎查询优化器实现的分析之best_access_path函数分析 (阅读:1546)
扫一扫订阅我的微信号:IT技术博客大学习
- 作者:yzyangwanfu 来源: 杨万富的专栏
- 标签: 查询优化器
- 发布时间:2009-10-19 23:44:12
- MySQL数据库InnoDB存储引擎查询优化器实现的分析之单表查询
- Mysql查询优化器浅析(下)
- MySQL数据库InnoDB存储引擎查询优化器实现的分析之单表unique查询
- MySQL数据库InnoDB存储引擎查询优化器实现的分析之多表简单JOIN查询
- MySQL数据库InnoDB存储引擎查询优化器实现的分析
- MySQL数据库InnoDB存储引擎查询优化器实现的分析之optimizer_search_depth参数
- MySQL数据库InnoDB存储引擎查询优化器实现的分析之附录
- MySQL数据库InnoDB存储引擎查询优化器实现的分析之统计信息
- MySQL数据库InnoDB存储引擎查询优化器实现的分析之best_access_path函数分析
- [67] Oracle MTS模式下 进程地址与会话信
- [65] 如何拿下简短的域名
- [65] Go Reflect 性能
- [59] 图书馆的世界纪录
- [59] android 开发入门
- [59] 【社会化设计】自我(self)部分――欢迎区
- [58] IOS安全–浅谈关于IOS加固的几种方法
- [53] 视觉调整-设计师 vs. 逻辑
- [47] 读书笔记-壹百度:百度十年千倍的29条法则
- [46] 界面设计速成