注册 登录  
 加关注
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

Tsecer的回音岛

Tsecer的博客

 
 
 

日志

 
 

sqlite中对于join的实现  

2013-09-22 21:14:33|  分类: 数据库 |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |
一、多表操作
这个问题并不是一个空穴来风的纯粹分析,应用的场景是假设一个数据库中相同的表格内容是按照每个月创建一个table,这样的好处在于随着日期的变化,我们可以通过一个简单的drop命令删除不再需要的数据,而且表的大小也不会太膨胀。这些好处的作用非常明显,但是也有一些不太方便的地方,假设我们想知道过去一段时间内所有商品的最高出售量在哪一天,这个时候处理就比较麻烦,因为此时可能涉及到两张表的操作,而对于最值的计算必须同时考虑到这两者表的内容。
这个的说明还是有些抽象,不过在选择极值作者不但给出了解决的各种方法,同时也叙述的比较清晰明了,所以有兴趣了解问题起因的同学可以看下那个文章。对于join的一些形象而直观的图形化表示可以在下面的地址中找到join示意图
不过好在这里并不是说明这些问题的解决方法,前任之述备矣,这里还是看一下sqlite对于一个jion的查询步骤。柿子挑软的捏,mysql虽然华丽,但是并不是很容易入手,而且它的explain功能并没有像sqlite有一个完备的虚拟机系统,由于sqlite属于麻雀虽小,五脏俱全的一个系统,所以从这个地方看下数据库的实现并无不妥,就像linus当时做linux内核的时候还是从MINIX这样一个教学型的操作系统开始。
下面的数据库结构和最开始的极值选择中给出的例子类似:
sqlite> .schema fruits
CREATE TABLE fruits(type varchar(64), variety varchar(64), price float);
sqlite>
二、inner join
sqlite> explain select f.type, f.variety, f.price from (select type, min(price) as minprice from fruits group by type   ) as x inner join fruits as f on  f.price = x.minprice;
addr  opcode         p1    p2    p3    p4             p5  comment    
----  -------------  ----  ----  ----  -------------  --  -------------
0     Trace          0     0     0                    00             
1     OpenEphemeral  0     2     0                    00              打开临时表0,每个record 2列
2     OpenEphemeral  3     3     0     keyinfo(1,BINARY)  00           临时表3,每个record 3列,key值为二进制结构              
3     Integer        0     5     0                    00  clear abort flag
4     Integer        0     4     0                    00  indicate accumulator empty
5     Gosub          7     44    0                    00              清零1……5五个寄存器的值
6     Goto           0     64    0                    00              transaction初始化
7     OpenRead       1     3     0     3              00  fruits      打开fruits表
8     Rewind         1     16    0                    00             
9     Column         1     0     10                   00  fruits.type
10    Sequence       3     11    0                    00             
11    Column         1     2     12                   00  fruits.price
12    RealAffinity   12    0     0                    00             
13    MakeRecord     10    3     13                   00             
14    IdxInsert      3     13    0                    00             
15    Next           1     9     0                    01             
16    Close          1     0     0                    00              以上操作将fruit表中type、price以及动态生成的sequence组成一个新的临时数据库,游标放入编号3中。这里对应子查询中的select type, min(price) ,组成了临时数据表,但是没有执行聚合(aggregate)的min操作。

17    Sort           3     48    0                    00  GROUP BY sort
18    Column         3     0     9                    00             
19    Compare        8     9     1     keyinfo(1,BINARY)  00             
20    Jump           21    25    21                   00             
21    Move           9     8     1                    00             
22    Gosub          6     35    0                    00  output one row
23    IfPos          5     48    0                    00  check abort flag
24    Gosub          7     44    0                    00  reset accumulator
25    Column         3     2     10                   00             
26    CollSeq        0     0     0     collseq(BINARY)  00             
27    AggStep        0     10    2     min(1)         01             
28    Column         3     0     1                    00             
29    Integer        1     4     0                    00  indicate data in accumulator
30    Next           3     18    0                    00              如果该临时表中没有记录,则顺序顺序执行,跳转到48地址。
31    Gosub          6     35    0                    00  output final row
32    Goto           0     48    0                    00              这个整个就是对于min导致的group操作,之前一篇日志中已经说明了这个实现思路,这里不再赘述了。
33    Integer        1     5     0                    00  set abort flag
34    Return         6     0     0                    00             
35    IfPos          4     37    0                    00  Groupby result generator entry point
36    Return         6     0     0                    00             
37    AggFinal       2     1     0     min(1)         00             
38    SCopy          1     14    0                    00             
39    SCopy          2     15    0                    00             
40    MakeRecord     14    2     13                   00             
41    NewRowid       0     16    0                    00             
42    Insert         0     13    16                   08             
43    Return         6     0     0                    00  end groupby result generator

44    Null           0     1     0                    00             
45    Null           0     3     0                    00             
46    Null           0     2     0                    00             
47    Return         7     0     0                    00             

48    OpenRead       2     3     0     3              00  fruits      再次打开fruit表
49    Rewind         0     62    0                    00              重新打开之前子select中创建的临时表,这里大家可以看一下这里二重循环的外层循环和内层循环分别对应哪张表。
50    Rewind         2     61    0                    00              重新打开fruits表,这里是一个跳转点,之后的next会跳回到这里。
51    Column         2     2     13                   00  fruits.price 接下来就是对于where中条件的判断,价格相等
52    RealAffinity   13    0     0                    00             
53    Column         0     1     16                   00  sqlite_subquery_9BA5868_.minprice
54    Ne             16    60    13    collseq(BINARY)  6b             
55    Column         2     0     17                   00  fruits.type
56    Column         2     1     18                   00  fruits.variety
57    Column         2     2     19                   00  fruits.price
58    RealAffinity   19    0     0                    00             
59    ResultRow      17    3     0                    00              组成一个新record
60    Next           2     51    0                    01              如果fruits表中还有元素,则继续fruits中下一条,否则继续select子表下一条。
61    Next           0     50    0                    01              如果子select生成表中还有元素,则子select继续下一条。这里要注意它的前向跳转点是50,在50地址处重新归零了fruits表。这也就是说,对于select子表的每一条记录,都需要遍历fruits表的所有记录

62    Close          2     0     0                    00             
63    Halt           0     0     0                    00             
64    Transaction    0     0     0                    00             
65    VerifyCookie   0     2     0                    00             
66    TableLock      0     3     0     fruits         00             
67    Goto           0     7     0                    00             
sqlite>
三、left/right join
在codeproject以及大部分关于left join的例子中,都会展示两个集合取不同部分的操作,例如在A中但是不在B中的元素这种例子,而使用的方法一般是R.column=null来判断不在R中出现的条件,但是从上面的例子中可以看到,这个inner join是没有位置来处理这个连接的,所以这个判断在inner中没有道理的,所以单独看下对于单侧join的实现。
sqlite> explain select f.type, f.variety, f.price from (select type, min(price) as minprice from fruits group by type   ) as x left join fruits as f on  f.price = x.minprice and x.minprice=null;
addr  opcode         p1    p2    p3    p4             p5  comment     
----  -------------  ----  ----  ----  -------------  --  -------------
0     Trace          0     0     0                    00              
1     OpenEphemeral  0     2     0                    00              
2     OpenEphemeral  3     3     0     keyinfo(1,BINARY)  00              
3     Integer        0     5     0                    00  clear abort flag
4     Integer        0     4     0                    00  indicate accumulator empty
5     Gosub          7     44    0                    00              
6     Goto           0     71    0                    00              
7     OpenRead       1     3     0     3              00  fruits      
8     Rewind         1     16    0                    00              
9     Column         1     0     10                   00  fruits.type 
10    Sequence       3     11    0                    00              
11    Column         1     2     12                   00  fruits.price
12    RealAffinity   12    0     0                    00              
13    MakeRecord     10    3     13                   00              
14    IdxInsert      3     13    0                    00              
15    Next           1     9     0                    01              
16    Close          1     0     0                    00              
17    Sort           3     48    0                    00  GROUP BY sort
18    Column         3     0     9                    00              
19    Compare        8     9     1     keyinfo(1,BINARY)  00              
20    Jump           21    25    21                   00              
21    Move           9     8     1                    00              
22    Gosub          6     35    0                    00  output one row
23    IfPos          5     48    0                    00  check abort flag
24    Gosub          7     44    0                    00  reset accumulator
25    Column         3     2     10                   00              
26    CollSeq        0     0     0     collseq(BINARY)  00              
27    AggStep        0     10    2     min(1)         01              
28    Column         3     0     1                    00              
29    Integer        1     4     0                    00  indicate data in accumulator
30    Next           3     18    0                    00              
31    Gosub          6     35    0                    00  output final row
32    Goto           0     48    0                    00              
33    Integer        1     5     0                    00  set abort flag
34    Return         6     0     0                    00              
35    IfPos          4     37    0                    00  Groupby result generator entry point
36    Return         6     0     0                    00              
37    AggFinal       2     1     0     min(1)         00              
38    SCopy          1     14    0                    00              
39    SCopy          2     15    0                    00              
40    MakeRecord     14    2     13                   00              
41    NewRowid       0     16    0                    00              
42    Insert         0     13    16                   08              
43    Return         6     0     0                    00  end groupby result generator
44    Null           0     1     0                    00              
45    Null           0     3     0                    00              
46    Null           0     2     0                    00              
47    Return         7     0     0                    00              
48    OpenRead       2     3     0     3              00  fruits      
49    Rewind         0     69    0                    00              

50    Integer        0     17    0                    00  init LEFT JOIN no-match flag 清除命中标志
51    Rewind         2     65    0                    00              
52    Column         2     2     13                   00  fruits.price
53    RealAffinity   13    0     0                    00              
54    Column         0     1     16                   00  sqlite_subquery_9BA6CB8_.minprice
55    Ne             16    64    13    collseq(BINARY)  6b              
56    Null           0     18    0                    00              
57    Ne             18    64    16    collseq(BINARY)  6a              
58    Integer        1     17    0                    00  record LEFT JOIN hit
59    Column         2     0     19                   00  fruits.type 
60    Column         2     1     20                   00  fruits.variety
61    Column         2     2     21                   00  fruits.price
62    RealAffinity   21    0     0                    00              
63    ResultRow      19    3     0                    00              
64    Next           2     52    0                    01         这里同样是对第二个表fruits作为内层循环,外层是子select生成的临时表     

65    IfPos          17    68    0                    00               If the value of register P1 is 1 or greater, jump to P2.这里是inner join所没有的一个操作,对于子select返回的一条记录,如果此时在整个fruits中没有对应项,则此时sqlite会为该join生成一个NullRow暨龙乡,而NullRow的意义则是 Move the cursor P1 to a null row.  Any OP_Column operations hat occur while the cursor is on the null row will always write a NULL,所以之后游标虽然没有匹配,但是此时有一个逻辑上的匹配列,就好象零的意义一样,存在的本身就表示了不存在
66    NullRow        2     0     0                    00              .
67    Goto           0     58    0                    00              
68    Next           0     50    0                    01              

69    Close          2     0     0                    00              
70    Halt           0     0     0                    00              
71    Transaction    0     0     0                    00              
72    VerifyCookie   0     2     0                    00              
73    TableLock      0     3     0     fruits         00              
74    Goto           0     7     0                    00              
sqlite>
四、回归一下问题
对于开始说的两表合并的问题,选择最大记录项的方法依然需要使用这里所说的join方法,但是还需要使用union all首先将两张表合成一张表,这个表可以作为一个子表,或者作为一个mysql中支持的temporary table,临时表的优点在于它的生命期只在一个session中,当会话结束之后,临时表会被自动删除。
  评论这张
 
阅读(795)| 评论(0)
推荐 转载

历史上的今天

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2017