公开课Introduction to Data Science的关系数据库总结

最近在学习公开课  Introduction to Data Science

第二部分讲到关系数据库与关系代数（Relational Databases，Relational Algebra）

​

• SQL与RA 的关系

下图表达了基本的SQL语句与关系代数的关系。

1.  选择selection, 负号σc(R)  表示查询表R中符合条件c的列，

例如 σSalary > 40000 (Employee) 表示  

"select * from Employee where Salary > 40000"

2.  投影Projection, 负号Π A1,…,An (R)  表示查询表R中的A1... An列，

例如ΠName,Salary(Employee)  表示

“select Name, Salary from Employee;

3. 集合运算，符合R X S ，表示R与S做笛卡尔积(R中列与S总的列的所有组合)

例如J X P  表示

”select * from J,P;

  如果J是一个2行3列的表，P是一个4行5列的表，那么结果是一个2*4行3+5列的表。

4. 连接Join,  条件连接，左连接，右连接

  4.1   条件连接，符号R θ S， 表示RXS中符合条件θ的 组合，

      例如

select * from J  JOIN P ON  J.id = P.id"

 等同于

 "select * from J,P where J.id = p.id"

     注INNER JOIN 同JOIN相同。 

  4.2   左外连接R  S, 表示列出R中与S中符合条件的组合，如果S中没有对应的项，则其值认为是null，

例如

select  * from P LEFT OUTER JOIN  ON P.age = J.age;

 其中 LEFT OUTER JOIN 等同于LEFT JOIN.  

    4.3  右外链接，与左外链接相反.  

5.  Union  符号R1∪R2,  表示组合两个表，这两个表必须具有相同的格式， 

例如

select age from P  UNION (select age from J);

如果P中有两列age分别为（1,2）， J中有两列age分被为（2,3） 那么结果就是（1,2,3) ，注意这里小区了重复的记录，使用UNION ALL 可以得到（1,2,2,3) 带有重复记录的所有结果。

6.  intersection 符号R1∩R2,  表示列出R1与R2相同的记录，

    R1 ∩ R2 = R1 – (R1 – R2)   ，  R1 ∩ R2 = R1 _θ R2   条件是记录相同。

7.  difference 符号R1-R2， 表示列出R1中的在R2中查不到的记录

select * from P where age not in (select age from J);

 

•   SQL优化

• 下图表示相同的查询结果用不同的方法会有不同的耗时。

   

课程中讲到 SQL is what， not how。 意思是SQL 定义了我们需要的结果，而不是如数如何操作的？ 实际上DB会在SQL上做一些优化比如 Sqlite 的 The SQLite Query Planner， Mysql 的 优化Optimization ， 实际中还是需要手工优化的，当需要在多个表之间做操作的时候，比如下面的例子

表结构如下

CREATE TABLE `Frequency` (  `docid` varchar(255) NOT NULL DEFAULT '',  `term` varchar(255) NOT NULL DEFAULT '',  `count` int(11) DEFAULT NULL,  PRIMARY KEY (`docid`,`term`),）

  在课后作业similarity matrix 中需要计算两两文档的相似性，这个实际是一个矩阵运算(有意思的一点）。

   1） 代码如下，用时1m22.042s

select x.docid,y.docid,sum(x.count*y.count)as count from Frequency x, Frequency y where x.term = y.term and x.docid < y.docid group by x.docid, y.docid  ORDER BY  count  ASC;

   2） 提交答案只需要，结果中的某一个， 用时 1m10.919s， 可以看出这里实际还是计算了所有文档的相似性后截取的，DB并没有优化。

select * from (select x.docid as a ,y.docid as b,sum(x.count*y.count)as count from Frequency x, Frequency y where x.term = y.term and x.docid < y.docid group by x.docid, y.docid  ORDER BY  count  ASC)x where x.a="10080_txt_crude" and x.b="17035_txt_earn";

   3） 手工将上面的where限定语句放入计算过程中， 用时0m0.005s

select * from (select x.docid as a ,y.docid as b,sum(x.count*y.count)as count from Frequency x, Frequency y where x.term = y.term and x.docid < y.docid  and x.docid="10080_txt_crude" and y.docid="17035_txt_earn" group by x.docid, y.docid  ORDER BY  count  ASC)x;

   

    

   a)将上面的例子放入mysql 中 执行时间对比

   

sql代码sqlite执行时间mysql执行时间1）计算所相似性1m19.892s49m48.016s2）计算所有相似性，然后选择某对文档的值1m9.073s49m52.044s3）选择某对文档，计算相似性0m0.003s0m0.005s

    可以看出 1),2) 之间差距不大，因为都对其计算了相似性，但是2），3）的差距非常大，可以看出DB不能在这种组合查询预判优化。

    有意思的地方是mysql执行的时间比sqlite长很多。下面尝试去分析其原因。

 

  b) 分析我们的SQL，可以看出我们需要查询列docid 和term ，创建INDEX， 然后去执行，

CREATE INDEX  docid_index ON Frequency(docid);CREATE INDEX  term_index ON Frequency(term);

sql代码sqlite执行时间

mysql执行时间

1)计算所相似性1m56.473s7m36.114s2)计算所有相似性，然后选择某对文档的值1m39.954s7m51.956s3)选择某对文档，计算相似性0m0.003s0m0.007s

   计算结果中可以看出 sqlite 没有性能的提升，原因分析，因为原先的表就存在主键PRIMARY KEY (`docid`,`term`), sqlite对这个主键的处理分配到了每个column里面形成index，所以我们创建的index算是冗余的了？

   MySQL带来了很大的性能提高，但是仍然跟sqlite有很大差距。这里MySQL默认使用的是InnoDB engine，InnoDB支持更多的功能，如transaction row lock，这样带来了并发性，但同时在影响了单任务的性能，这里受到影响的如下有一个query cache.  这个cache 有辅助在表不变化的情况下，执行相同query 会提高速度，比如我们执行两次SQL  2）语句，那么第二次用时0m0.006s就得到了结果，因为其从cache中直接找到了结果。

mysql> SHOW PROFILE FOR QUERY 1;

+--------------------------------+----------+

| Status                         | Duration |

+--------------------------------+----------+

| starting                       | 0.000018 |

| Waiting for query cache lock   | 0.000004 |

| checking query cache for query | 0.000029 |

| checking permissions           | 0.000004 |

| Opening tables                 | 0.000007 |

| init                           | 0.000010 |

| optimizing                     | 0.000004 |

| executing                      | 0.000011 |

| end                            | 0.000003 |

| query end                      | 0.000003 |

| closing tables                 | 0.000003 |

| freeing items                  | 0.000014 |

| logging slow query             | 0.000002 |

| cleaning up                    | 0.000002 |

+--------------------------------+----------+

    相关测试脚本

   https://github.com/trumanz/coursera/tree/master/datasci/assignment_Relation_DB/performance_test

•  课后题答案

   上传到了github

    https://github.com/trumanz/coursera/tree/master/datasci/assignment_Relation_DB