本文通过一个案例来看看MySQL优化器如何选择索引和JOIN顺序。表结构和数据准备参考本文最后部分"测试环境"。这里主要介绍MySQL优化器的主要执行流程,而不是介绍一个优化器的各个组件(这是另一个话题)。

我们知道,MySQL优化器只有两个自由度:顺序选择;单表访问方式;这里将详细剖析下面的SQL,看看MySQL优化器如何做出每一步的选择。

explain select * from employee as A,department as B where A.LastName = 'zhou' and B.DepartmentID = A.DepartmentID and B.DepartmentName = 'TBX';

1. 可能的选择

这里看到JOIN的顺序可以是A|B或者B|A,单表访问方式也有多种,对于A表可以选择:全表扫描和索引`IND_L_D`(A.LastName = 'zhou')或者`IND_DID`(B.DepartmentID = A.DepartmentID)。对于B也有三个选择:全表扫描、索引IND_D、IND_DN。

2. MySQL优化器如何做

2.1 概述

MySQL优化器主要工作包括以下几部分:Query Rewrite(包括Outer Join转换等)、const table detection、range analysis、JOIN optimization(顺序和访问方式选择)、plan refinement。这个案例从range analysis开始。

2.2 range analysis

这部分包括所有Range和index merge成本评估(参考1 参考2)。这里,等值表达式也是一个range,所以这里会评估其成本,计算出found records(表示对应的等值表达式,大概会选择出多少条记录)。

本案例中,range analysis会针对A表的条件A.LastName = 'zhou'和B表的B.DepartmentName = 'TBX'分别做分析。其中:[......]

Read more

Oracle如何根据SQL_TEXT生成SQL_ID

2013-03-29 14:09  |  分类:MySQL,ORACLE,PHP

本文纯属八卦,基本没有任何实用价值。Oracle总是都会通过SQL_ID来标志一个唯一的SQL。SQL_ID与SQL_TEXT一一对应。如果两个SQL文本有任何不同,包括空格等任何不可见字符,都会导致SQL_ID不同。本文八卦的内容是:Oracle如何根据SQL_TEXT内容散列成一个13位的字符串。为什么这个字符串会是13位?为什么这个字符经常以数字开头?

本文参考TANEL PODER和Slavik的两篇介绍(12),详细介绍转换原理,顺便给出PHP/Perl实现代码。

0. 概述

Oracle先计算SQL_TEXT的md5散列值;取散列值的低64位(bits),每次取5位(最后一次4位),使用Base32将其依次转换成可见字符,就是你最终看到的SQL_ID。原理就是这样。

不过实际转换过程中有一些要注意的事项:

(a) Oracle在计算md5散列时,会在SQL_TEXT末尾加一个不可见字符\0,AWR报表中经常有这样的SQL_TEXT

(b) 注意little-endian的问题

(c) Base32转码的可见字符为0123456789abcdfghjkmnpqrstuvwxyz

(d) 编写程序的时候需要注意大数精度的问题,本文中Perl/PHP程序都使用了数学大数处理函数[......]

Read more

冬去春来

2013-03-20 18:50  |  分类:简单生活

从搬到这里已经住了20年,才发现很多地方都没有真正认真看过。风景在那儿,从来未改变。
冬去[......]

Read more

index merge的补充说明

2013-03-13 14:05  |  分类:MySQL,代码细节

在除了前面介绍的常见index merge的案例(Index Merge Union Access Algorithm)之外,还有一类很少见也比较特殊的index merge,多个索引扫描后进行交集,即 Index Merge Intersection。这类执行计划比较少见(因为MySQL需要ROR的原因),但是,在合适的场景使用,效率仍然会有很大的提示,本文将看看MySQL优化器如何评估和选择此类执行计划。MySQL手册对此只是三言两语简单介绍了一下,这里做个较为详细的说明。

这类执行计划完整名称应该是:The Index Merge Intersection Access Algorithm,下文简称Intersection

1. 为什么需要考虑Intersection

考虑如下查询:

SELECT COUNT(*) FROM t1 WHERE key1=1 AND key2=1;

优化器可以考虑使用索引key1或者key2进行REF/Range访问,如果使用key1,那么key2=1则作为过滤条件。另外,优化器还会考虑使用Intersection,即同时使用索引key1和key2。这样做可能的好处是:

(a) 如果两次索引扫描后做交集,如果最后ROWID很少,则回表次数大大减少

(b) 如果扫描这两个索引能是覆盖扫描的话,则无需回表[......]

Read more

前面以案例的形式介绍了什么是index merge,以及它的使用场景。本文将介绍index merge实现的主要数据结构以及MySQL如何评估index merge的成本。在开始本文之前,需要先理解Range访问相关的数据结构介绍:SEL_ARG结构SEL_TREE结构。[......]

Read more

还有五天过年

2013-02-3 16:24  |  分类:简单生活

最近博客都很枯燥,总写些不着边际的技术文章,虽然自己不觉得,不过还是枯燥之极。生活枯燥了吗?没有。大概是忙了,心理牵挂多了。今年是2月9号过年,去年好像是1月几号,为什么不同的年份农历年和公历年差这么多?是啊,为什么?好吧,本文就八卦一下这个吧。

本文尝试说明白:什么是农历?农历一年到底有多长?

1. 公历年

这个相对简单。地球有公转,即绕太阳转,绕太阳一周,就是我们说的一年,也叫回归年或者太阳年。准确的说是365.2421990741天(参考),所以一般一年就是365天。

但如果就按照365天过,每隔4年,我们的"一年"就会与地球公转的周期相差约一天(4*0.2421990741),所以就有"闰年"的概念,即每隔四年我们就让2月多一天,也就是全年是366天。借此保障我们的"一年"能够准确的描述地球的公转。但如果更精确的计算,你还会发现,每四年一个闰年还会有问题,因为4*0.2421990741并不是完整的一天的,比一天少了0.0312037036天。

所以如果按照上面计算,每四年我们的"一年"就比地球公转周期多0.0312037036天。那么每隔100年,也就相差0.78(0.0312037036*25)天。于是,我们又规定,每隔100年,即使被四整除,也不闰年了。是的,还没完!!要是按照这样算,每过100年我们的"一年"仍然与地球的公转周期相差0.22天,于是我们又规定,每隔400年我们还是在闰一次。所以每隔400年,我们的"一年"与又比地球公转周期多了0.12天,是的,没玩了...各个科学界的大佬们也觉得,后面的事情不要再操心了。按照这样的计算要再过3200年,我们的"一年"才比地球的公转周期多整整一天,后面的事情让子孙后代操心吧...

总结一下,公历年正常是365天一年,闰年是366天。如果能被4整数,但不能被100整除是闰年,除此,如果恰好被400整除也是闰年。

2 农历年

农历年是根据月球绕地球会合周期来计算的,每十二个月盈月亏为一个农历年;但是,农历年又尝试通过闰月的方式去近似回归年,正是这个"近似"的粒度(按月)很粗,所以,让每年的春节日期与公历相差很多。

2.1 农历月

开始介绍农历年前,我们先看看农历月。农历月是精确的按照月亮的盈亏(望朔)来制定的。这与回归年没有什么关系。月亮绕地球的会和周期(从地球的角度观察到的天球上原来的位置所需要的时间)是29.530589天。所以农历月就是29天或者30天,至于哪个月30天,那个月29天,这依赖于精确的天文观察,规定是:"月初必须是朔日",实际中连续大月或者小月的情况很多。

所以农历八月十五太阳总是圆的。公历就没有哪天说月亮就一定是圆的。[......]

Read more

Pages: Prev 1 2 3 4 5 6 7 8 ... 31 32 33 Next