Archive for November, 2006

写 SQL 还真是要有点功力

2006-11-23 21:42 | by 2ndboy

  最近两周都在实现一个不大不小的 Web 应用,用的是 PHP + MySQL(当然,用了数据库抽象层,所以用什么数据库系统是无所谓的)。我本不是专业的 Web 开发者更不是 DBA,所以简单的 SQL 都不在话下,遇到稍微复杂一点的就搞不定了。我的应用里有一块类似论坛的东西,为了说得清楚一些,现将问题简化后描述一下:

有一个用户表,里面有两个字段,一个自增长的 id 字段,一个是用户名字段;还有一个贴子表,里面有四个字段,一个自增长 id,一个帖子标题,一个发帖用户 id 还有一个最近回复用户 id,建表的 SQL 如下:

  1. CREATE TABLE users(
  2.  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  3.  nick VARCHAR( 16 ) NOT NULL ) ;
  4.  CREATE TABLE posts(
  5.  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  6.  title VARCHAR( 64 ) NOT NULL,
  7.  userId INT NOT NULL,
  8.  lastReplyUserId INT NOT NULL );

如果需要在显示帖子列表的同时把帖子作者的名字也显示出来,我可以马上写出如下 SQL:

  1. SELECT p.*,u.nick FROM users u,posts p WHERE u.id=p.userId

  但是如果需要在列出帖子列表的同时既显示帖子作者又显示最后回复人的名字,就难住我了:P 于是马上在 MSN 跟 GTalk 上求助,经过一番描述,两个同学和一个同事分别给出了三种不同的实现,同学 C 的方案是:

  1. SELECT p.title,u1.nick user,u2.nick reply FROM posts p LEFT JOIN users u1 ON p.userid=u1.id LEFT JOIN users u2 ON p.lastReplyUserId=u2.id

同事 X 给出的方案是:

  1. SELECT p.title,u1.nick user,u2.nick reply FROM users u1,users u2,posts p WHERE u1.id=p.userId AND u2.id=p.lastReplyUserId

同学 D 给出的方案是:

  1. SELECT title,(SELECT nick FROM users WHERE id=p.userId) user, (SELECT nick FROM users WHERE id=p.lastReplyUserId) reply FROM posts p

准备了一些测试用例来测试这些 SQL:

  1. INSERT INTO users (id,nick) VALUES (1,'tom');
  2.  INSERT INTO users (id,nick) VALUES (2,'jack');
  3.  
  4.  INSERT INTO posts (id,title,userId,lastReplyUserId) VALUES (NULL,'test',1,1);
  5.  INSERT INTO posts (id,title,userId,lastReplyUserId) VALUES (NULL,'foo',1,2);
  6.  INSERT INTO posts (id,title,userId,lastReplyUserId) VALUES (NULL,'bar',2,1);
  7.  INSERT INTO posts (id,title,userId,lastReplyUserId) VALUES (NULL,'hehe',2,2);
  8.  INSERT INTO posts (id,title,userId,lastReplyUserId) VALUES (NULL,'none',2,0);

结果都可以得出正确的结果:
title user reply
test tom tom
foo tom jack
bar jack tom
hehe jack jack
none jack NULL

为了比较这三个方案之间的效率差异,我用程序在库里两个表中各放了 10000 条记录:

  1.  for( $i = 0; $i < 10000; $i++ )
  2.  {
  3.          $sql = sprintf( "INSERT INTO users (nick) VALUES ('%s')", uniqid() );
  4.          $res = & $db->exec( $sql );
  5.          if( PEAR::isError( $res ) )
  6.                  exit;
  7.  }
  8.  
  9.  for( $i = 0; $i < 10000; $i++ )
  10.  {
  11.          $sql = sprintf(
  12.                  "INSERT INTO posts (title,userId,lastReplyUserId) VALUES ('%s',%d,%d)",
  13.                  uniqid(),
  14.                  rand( 1, 10000),
  15.                  rand( 1, 10000 ) );
  16.          $res = & $db->exec( $sql );
  17.          if( PEAR::isError( $res ) )
  18.                  exit;
  19.  }
  20.  ?>

通过对每次 SQL 反复的执行几百次(当然也是用测试程序:)),记录执行时间后得出如下结论:
同事 X 的方案最快,平均耗时为 0.0462s
同学 C 给出的方案跟同事 X 查不多,平均执行耗时为 0.0469s
同学 D 给出的方案在这三个方案中耗时最多,平均为 0.0572s

  同事 X 跟同学 C 的方案都是做表联合查询,但是为什么单纯的联合查询会比 LEFT JOIN 快呢?同学 D 给出的方案用了子查询,不知道是不是因为有三个查询嵌套在一起所以慢,请路过的高人给点提示,或者是不是有更好的解决办法?

  不管怎么样,经过实测,决定使用同事 X 提供的 SQL 来实现这个 Web 应用,谢谢他们几个人的无私帮助:)

20061127 Update:终于找到时间上来更新一下,其实同事 X 的方案在最后回复人为 NULL 的时候是有问题的(不能给出结果),所以最后用了效率稍有一点点差距的同学 C 的方案。

今日杂谈

2006-11-13 20:16 | by 2ndboy

  今天刚上班就看见了躺在办公桌上的快件,哈哈,原来是我的 FireFox T-shirt 到了,赶紧拆开包装瞧瞧。原来除了 T-shirt 还有其他东西:一个 Yupoo 纸像框,10 个 FireFox 贴纸,10 个汽车用 FireFox 贴纸。Yupoo 的纸像框上还有 Yupoo 的历史介绍,现抄录于下面,以示对 Yupoo 和橘子的感谢(广告一下,没用过 Yupoo 的赶紧去注册吧:)):

2005/6/1 – Yupoo v0.61 版本诞生
2005/10/1 – Yupoo 正式成为两位创始人的唯一运营项目
2005/10/11 – Yupoo v0.7 版本发布
2005/10/15 – Yupoo 联合杭州各大网站及论坛,举办西湖博览会系列之一的“全民拍摄”活动
2005/11/5-6 – Yupoo 作为嘉宾出席第一届中文网志年会
2006/1/1 – Yupoo 第三位创始人正式加入,团队技术力量增强
2006/1/11 – Yupoo v0.8 版本发布
2006/5 – Yupoo 用户数突破 30 万,单日照片上传量突破 10 万张
2006/7/4 – Yupoo v2.0 版本问世,网站功能更实用,用户体验更舒适
2006/10 – Yupoo 正式确认参加第二届中文网志年会,并成为年会赞助商与图片合作伙伴
2006/10/10 – Yupoo 迁至杭州市数字娱乐产业园,开始新的征程……

  谢谢毛向辉跟 Mozilla 提供的 T-shirt!

—–

  最近感觉有种趋势,那就是美剧似乎要取代韩剧的流行趋势啦,《越狱》、《Lost》、《绝望的主妇》席卷中国大地,紧张刺激的情节、环环相扣的剧情让看惯了拖沓韩剧的人耳目一新(当然,我对韩剧本来就不怎么感冒)。最近在网上看了很多有关《越狱》和《Lost》的后续剧情猜想,而有关《Lost》的尤其吸引人。今天下班前在豆瓣上看到了一个用“Marseille Rousseau 鲁索基因镜象理论”来解释《Lost》的文章,值得一看,推荐!http://www.douban.com/review/1063956/

—–

  上周由于《Cars》出了清晰版本所以补看了这部据说很好看的动画片,结果——果然很好看!:)

  如果让我用一句话来形容我对这部片子的感觉的话,那就是:老美把完全虚构出来的动画形象拍出了感情!尤其是一些关键场景的音乐,恰到好处!感染力非常强!每次看到这些到处闪现着创意和灵感火花的片子的时候就不得不由衷的佩服起这些拍摄这部电影的人,编剧、动画制作、音效处理……不知道我们国内的动画片什么时候可以达到这种境界。

  最后还想说一下的就是这个中文译名,这部片子原本的英文名字叫《Cars》,本来这个简单的单词可以结合主题意会出一些很好的名字,但不知道怎么就被哪个便秘的家伙憋成了《汽车总动员》,让人不由的想起一系列的已经用俗了的名字《玩具总动员》、《海底总动员》……但愿下次引进宣传之前能先起个好名字。

PHP 5.2.0 来了!

2006-11-3 13:14 | by 2ndboy

  今天看到新闻——PHP 5.2.0 已经发布了,到官方网站上看了看,似乎有不少不错的新特性(http://www.php.net/releases/5_2_0.php)!

Input filtering extension was added and enabled by default
这个扩展不错,数据库注入是 Web 开发永远不能避开的问题,现在官方已经有了这样的解决方案,我们自己可以省掉一部分工作了:)

JSON extension was added and enabled by default
JSON 就是 JavaScript Object Notation,看似是一个 JavaScript 数据/对象的 serialization 规范,还有专门的 rfc(http://www.ietf.org/rfc/rfc4627.txt?number=4627)。从来没用过,知道其中妙处的朋友不妨指教一二。这个扩展似乎在 SourceForge 上有自己的项目站点:http://sourceforge.net/projects/php-json

ZIP extension for creating and editing zip files was introduced
这个扩展比较实在,以后你就可以在 PHP 脚本中方便的操纵 zip 文件了!

Hooks for tracking file upload progress were introduced
在 PHP 里面跟踪文件上传的进度在以前来说是根本不可能的,文件上传之后你只能从解析器那里拿到临时文件和上传结果,中间的一切步骤都不能插手。如果你要用 PHP 来做文件或者照片共享程序,那么用户上传文件的时候将得不到任何进度指示,现在好了,有时间要试试这个新特性。这里有个额外的参考:http://blog.bitflux.ch/archive/2006/09/28/upload-progress-meter-extension-for-php-5-2.html

  另外这个版本还修复了超过 200 个 bug,性能也得到了一些提升,一些组件的 client library 也更新了版本。总体来说是个值得升级和尝试一下的版本!