在数据量和访问量剧增条件下保持数据库效率

在某些网站里，你可以订阅好友，这样，好友发的消息就会出现在你的主页里，这种方式确实很人性化，但是就引出了一个疑问，他们应该如何组织数据库才能在很大的数据量和访问量下效率最高呢？

首先，数据库应该是这样的：（括号里是字段，其他无关信息省略）

用户表：user (uid, name), uid为主键。

信息表：msg (mid, uid, content), mid为主键，uid为发贴人。

好友表：friend (uid, fid), uid为主人，fid为好友（即uid订阅的人的id）。

这样，要查询出某人的所有好友的信息就可以这样写SQL语句：(已查询uid=1的为例)

1) SELECT * FROM msg, friend WHERE msg.uid=friend.fid AND friend.fid=1;

注意：千万不要写成：

2) SELECT * FROM msg WHERE uid IN (SELECT fid FROM friend WHERE uid=1);

子查询的方法比级联两张表慢了不止1个数量级！

（测试结果，方法1)能在10-30ms查询出结果，而方法2)则用了3min还没有出来结果）

有些人在网上说再增加一张中介表可以提高速度：

中介表：linker (uid, mid)

中介表是这样用的，每次当一个人发了一篇帖子，则将所有订阅他的人(uid)和这篇帖子的mid插入中介表里，例如，uid=1的人有3个订阅者，分别为：2、3、4，则当uid=1的人发了一篇帖子mid=100时，就应往linker表里插入这些数据：(2, 100), (3, 100), (4, 100)。这样，对一个uid而言，linker表和msg表就形成了一对一的关系。查询的时候，只需级联linker表和msg表就行了，如下：(还是以查uid=1的为例)

3) SELECT * FROM msg, linker WHERE msg.mid=linker.mid and linker.uid=1;

这样乍看好像比1)要快一些，因为就一个uid而言linker表和msg表是一对一的关系，而方法1)的却是一对多的关系。而且，有些人认为，就算不这么看，因为方法3)在插入时比方法1)多做了很多操作，所以查询的速度应该能比方法1)快，至少不会比方法1)慢。

我实际测试下来的结果是，方法3)比方法1)慢了至少1倍的时间，而且数据量越大，慢得就越多。测试结果：

a) user有10,000条，friend有160,000条，msg有80,000条时：平均查询时间：

方法1)是：0.04s，方法3)是0.08s，3)比1)慢1倍。

b) 当msg有260,000条时：方法1)是：0.18s，方法3)是：0.70s，3)比1)慢3倍。

（以上结果都是在建了索引之后的）

根据分析，3)比1)慢的原因，主要是当数据增大时，linker表的数据量急剧增大所致。因为当msg里有260,000条数据是，linker表的数据量已经超过4,000,000条了。

通过对这个的研究得出几点体会：

◆1、数据库对where的优化做得太好了（除了"in"关键字）。

◆2、索引对数据查询效率的影响是太大了，我开始测试的时候没有建索引，查询时间都在几百毫米，建了以后降低到几十毫米了。

◆3、不要随便相信网上的一些讨论，要通过自己学过的知识去验证网上讨论的对错。

评论总数：1 条 [ 查看全部 ] 网友评论