在某些網(wǎng)站里，你可以訂閱好友，這樣，好友發(fā)的消息就會(huì)出現(xiàn)在你的主頁里，這種方式確實(shí)很人性化，但是就引出了一個(gè)疑問，他們應(yīng)該如何組織數(shù)據(jù)庫才能在很大的數(shù)據(jù)量和訪問量下效率呢？
首先，數(shù)據(jù)庫應(yīng)該是這樣的：（括號里是字段，其他無關(guān)信息省略）
用戶表：user (uid, name), uid為主鍵。
信息表：msg (mid, uid, content), mid為主鍵，uid為發(fā)貼人。
好友表：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);
子查詢的方法比級聯(lián)兩張表慢了不止1個(gè)數(shù)量級！
（測試結(jié)果，方法1)能在10-30ms查詢出結(jié)果，而方法2)則用了3min還沒有出來結(jié)果）
有些人在網(wǎng)上說再增加一張中介表可以提高速度：
中介表：linker (uid, mid)
中介表是這樣用的，每次當(dāng)一個(gè)人發(fā)了一篇帖子，則將所有訂閱他的人(uid)和這篇帖子的mid插入中介表里，例如，uid=1的人有3個(gè)訂閱者，分別為：2、3、4，則當(dāng)uid=1的人發(fā)了一篇帖子mid=100時(shí)，就應(yīng)往linker表里插入這些數(shù)據(jù)：(2, 100), (3, 100), (4, 100)。這樣，對一個(gè)uid而言，linker表和msg表就形成了一對一的關(guān)系。查詢的時(shí)候，只需級聯(lián)linker表和msg表就行了，如下：(還是以查uid=1的為例)
3) SELECT * FROM msg, linker WHERE msg.mid=linker.mid and linker.uid=1;
這樣乍看好像比1)要快一些，因?yàn)榫鸵粋€(gè)uid而言linker表和msg表是一對一的關(guān)系，而方法1)的卻是一對多的關(guān)系。而且，有些人認(rèn)為，就算不這么看，因?yàn)榉椒?)在插入時(shí)比方法1)多做了很多操作，所以查詢的速度應(yīng)該能比方法1)快，至少不會(huì)比方法1)慢。
我實(shí)際測試下來的結(jié)果是，方法3)比方法1)慢了至少1倍的時(shí)間，而且數(shù)據(jù)量越大，慢得就越多。測試結(jié)果：
a) user有10,000條，friend有160,000條，msg有80,000條時(shí)：平均查詢時(shí)間：
方法1)是：0.04s，方法3)是0.08s，3)比1)慢1倍。
b) 當(dāng)msg有260,000條時(shí)：方法1)是：0.18s，方法3)是：0.70s，3)比1)慢3倍。
（以上結(jié)果都是在建了索引之后的）
根據(jù)分析，3)比1)慢的原因，主要是當(dāng)數(shù)據(jù)增大時(shí)，linker表的數(shù)據(jù)量急劇增大所致。因?yàn)楫?dāng)msg里有260,000條數(shù)據(jù)是，linker表的數(shù)據(jù)量已經(jīng)超過4,000,000條了。
通過對這個(gè)的研究得出幾點(diǎn)體會(huì)：
◆1、數(shù)據(jù)庫對where的優(yōu)化做得太好了（除了"in"關(guān)鍵字）。
◆2、索引對數(shù)據(jù)查詢效率的影響是太大了，我開始測試的時(shí)候沒有建索引，查詢時(shí)間都在幾百毫米，建了以后降低到幾十毫米了。
◆3、不要隨便相信網(wǎng)上的一些討論，要通過自己學(xué)過的知識(shí)去驗(yàn)證網(wǎng)上討論的對錯(cuò)。
首先，數(shù)據(jù)庫應(yīng)該是這樣的：（括號里是字段，其他無關(guān)信息省略）
用戶表：user (uid, name), uid為主鍵。
信息表：msg (mid, uid, content), mid為主鍵，uid為發(fā)貼人。
好友表：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);
子查詢的方法比級聯(lián)兩張表慢了不止1個(gè)數(shù)量級！
（測試結(jié)果，方法1)能在10-30ms查詢出結(jié)果，而方法2)則用了3min還沒有出來結(jié)果）
有些人在網(wǎng)上說再增加一張中介表可以提高速度：
中介表：linker (uid, mid)
中介表是這樣用的，每次當(dāng)一個(gè)人發(fā)了一篇帖子，則將所有訂閱他的人(uid)和這篇帖子的mid插入中介表里，例如，uid=1的人有3個(gè)訂閱者，分別為：2、3、4，則當(dāng)uid=1的人發(fā)了一篇帖子mid=100時(shí)，就應(yīng)往linker表里插入這些數(shù)據(jù)：(2, 100), (3, 100), (4, 100)。這樣，對一個(gè)uid而言，linker表和msg表就形成了一對一的關(guān)系。查詢的時(shí)候，只需級聯(lián)linker表和msg表就行了，如下：(還是以查uid=1的為例)
3) SELECT * FROM msg, linker WHERE msg.mid=linker.mid and linker.uid=1;
這樣乍看好像比1)要快一些，因?yàn)榫鸵粋€(gè)uid而言linker表和msg表是一對一的關(guān)系，而方法1)的卻是一對多的關(guān)系。而且，有些人認(rèn)為，就算不這么看，因?yàn)榉椒?)在插入時(shí)比方法1)多做了很多操作，所以查詢的速度應(yīng)該能比方法1)快，至少不會(huì)比方法1)慢。
我實(shí)際測試下來的結(jié)果是，方法3)比方法1)慢了至少1倍的時(shí)間，而且數(shù)據(jù)量越大，慢得就越多。測試結(jié)果：
a) user有10,000條，friend有160,000條，msg有80,000條時(shí)：平均查詢時(shí)間：
方法1)是：0.04s，方法3)是0.08s，3)比1)慢1倍。
b) 當(dāng)msg有260,000條時(shí)：方法1)是：0.18s，方法3)是：0.70s，3)比1)慢3倍。
（以上結(jié)果都是在建了索引之后的）
根據(jù)分析，3)比1)慢的原因，主要是當(dāng)數(shù)據(jù)增大時(shí)，linker表的數(shù)據(jù)量急劇增大所致。因?yàn)楫?dāng)msg里有260,000條數(shù)據(jù)是，linker表的數(shù)據(jù)量已經(jīng)超過4,000,000條了。
通過對這個(gè)的研究得出幾點(diǎn)體會(huì)：
◆1、數(shù)據(jù)庫對where的優(yōu)化做得太好了（除了"in"關(guān)鍵字）。
◆2、索引對數(shù)據(jù)查詢效率的影響是太大了，我開始測試的時(shí)候沒有建索引，查詢時(shí)間都在幾百毫米，建了以后降低到幾十毫米了。
◆3、不要隨便相信網(wǎng)上的一些討論，要通過自己學(xué)過的知識(shí)去驗(yàn)證網(wǎng)上討論的對錯(cuò)。