在很長一段時(shí)間內(nèi)，PostgreSQL都被認(rèn)為是MySQL的替代品。但是，在那段時(shí)間里，根本沒有辦法使他達(dá)到MySQL所能達(dá)到的水平。最近幾年里，這些問題都無法解決，并且產(chǎn)生了許多有趣的工具來彌補(bǔ)PG。我們?cè)?a target="_blank" >Disqus中使用了兩個(gè)Slony和pgbouncer。Slony讓我們可以復(fù)制數(shù)據(jù)（有時(shí)候也可以分區(qū)），而pgbouncer為我們解決了保持鏈接和連接池的問題。

另外，讓我們看看他們的語言：我這個(gè)星期很高興能夠?qū)W會(huì)如何在PGSQL8.4中使用遞歸查詢，他們實(shí)在太強(qiáng)大了。這就是我這篇文章所真正想要和大家討論的東西。MySQL讓我們可以工作，并且工作的很好，但你只能在引擎的結(jié)構(gòu)內(nèi)完成。雖然在PG中依然如此，但你有了更多的選擇。因此，我想講講樹的線索化的問題。

大家都知道Disqus不僅僅是最大的Django網(wǎng)站（我們每個(gè)月有近100萬的訪問量），同時(shí)，他也是他也是一個(gè)最大的網(wǎng)上評(píng)論系統(tǒng)。我們?yōu)樯锨€(gè)網(wǎng)站提供了許多功能，最基本的就是評(píng)論作為樹狀結(jié)構(gòu)的線索化。

PostgreSQL提供了許多個(gè)關(guān)于線索化的解決方案。最常用的（也是最高效的）方法就是改良版的前序遍歷。簡單的說，他增加了一個(gè)左序，一個(gè)右序，他們會(huì)在你添加評(píng)論時(shí)被更新。我們還有另一個(gè)標(biāo)準(zhǔn)的方法（Reddit使用的很歡樂），那就是“取出所有的東西，然后在內(nèi)存中完成操作”。實(shí)際上，不僅僅只有Reddit這樣做。

繼續(xù)看看PGSQL為我們提供的東西，我們還可以找到兩個(gè)選項(xiàng)（最低在8.4版本）。其中一個(gè)是使用PG的內(nèi)建模塊稱為ltree。他允許你將一個(gè)節(jié)點(diǎn)的完整路徑（所有父結(jié)點(diǎn)）存儲(chǔ)下來，同時(shí)允許你通過標(biāo)準(zhǔn)的sql語句查詢他們。當(dāng)你需要按照“最早發(fā)布”排序的時(shí)候，它會(huì)非常有用，因?yàn)檫@樣以來，就變?yōu)榱撕唵蔚陌凑铡發(fā)tree——column”排序。然而，和大部分時(shí)候一樣，Disqus的情況沒有這么簡單。

我們的第二個(gè)解決方案就是遞歸查詢。他花了我很長一段時(shí)間來理解他是怎么工作的，但是當(dāng)我理解后，我被他的能力深深的吸引了。Postgre提供了許多MySQL所沒有的特性，比如over()修飾符。他們真的表現(xiàn)的非常好。

讓我們繼續(xù)深入我們的問題，這會(huì)是一個(gè)大問題。現(xiàn)在，Disqus和Reddit處理多線程的方法一樣，都是和網(wǎng)上其他的解決方案一樣，非常的簡陋。我說的是簡陋不是說代碼寫的不好，而是他的優(yōu)化沒有做到他應(yīng)該做到的。直到某些人（就是你，Obama同學(xué)）開始使用這個(gè)程序，并且所有人都想回復(fù)他的話，我們才發(fā)現(xiàn)出問題了。我們?cè)僖淮蜗氲搅薉jango（即使他們?cè)絹碓酱螅┎⑶彝ㄟ^業(yè)務(wù)邏輯將他們分組。

自從8.4開始，我們就可以使用遞歸查詢來解決這個(gè)問題（在許多情況下我們已經(jīng)自己開始這么做了，雖然會(huì)有點(diǎn)復(fù)雜）這個(gè)相當(dāng)?shù)暮唵巍?/p>

因此，讓我們一個(gè)基本的例子。我們有一個(gè)的評(píng)論模型，它看起來有點(diǎn)像這樣：
 

create table comments (
  id SERIAL PRIMARY KEY,
  message VARCHAR,
  author VARCHAR,
  parent_id INTEGER REFERENCES comments(id)
);
insert into comments (message, author, parent_id)
  values ('This thread is really cool!', 'David', NULL), ('Ya David, we love it!', 'Jason', 1), ('I agree David!', 'Daniel', 1), ('gift Jason', 'Anton', 2),
  ('Very interesting post!', 'thedz', NULL), ('You sir, are wrong', 'Chris', 5), ('Agreed', 'G', 5), ('Fo sho, Yall', 'Mac', 5);

我們現(xiàn)在所做的，是建立一個(gè)基本的評(píng)價(jià)模型。我們的消息，筆者父評(píng)論（這是可選的）。現(xiàn)在，讓我們來學(xué)習(xí)如何使用遞歸查詢可以輕松地重新訂購本datd中，由id升序排序。

WITH RECURSIVE cte (id, message, author, path, parent_id, depth) AS (
  SELECT id,
    message,
    author,
    array[id] AS path,
    parent_id,
    1 AS depth
  FROM  comments
  WHERE  parent_id IS NULL
 
  UNION ALL
 
  SELECT comments.id,
    comments.message,
    comments.author,
    cte.path || comments.id,
    comments.parent_id,
    cte.depth + 1 AS depth
  FROM  comments
  JOIN cte ON comments.parent_id = cte.id
  )
  SELECT id, message, author, path, depth FROM cte
ORDER BY path;

很甜蜜吧？哦，等等，有困惑？所以我一直在尋找的查詢更復(fù)雜的是一大堆驚人的bug.
pgexperts為我們指向正確的道路。

現(xiàn)在，我不會(huì)鉆到太多，因?yàn)橛懈玫慕坛?，在此模式中處理遞歸查詢，但我們完成了我們的結(jié)果。

我們要處理一個(gè)巨大信息集，并且有些評(píng)論有將近幾千個(gè)回復(fù)。如果99%的評(píng)論都只有100個(gè)回復(fù)，那么將他們放入內(nèi)存中并不是什么問題，但當(dāng)他們開始增加時(shí)，我們最終會(huì)浪費(fèi)很多時(shí)間。PGSQL中的遞歸查詢可以讓我們很簡單的把這項(xiàng)工作交給數(shù)據(jù)庫（有時(shí)候他們處理的比我們快的多），并且給我們節(jié)省了很多花費(fèi)在網(wǎng)絡(luò)傳播和web處理的時(shí)間和資源。

有一個(gè)例子可以讓你更直觀的理解他是多么的高效，我們?cè)?jīng)見過僅在大型數(shù)據(jù)庫的SQL處理時(shí)間這一項(xiàng)上（返回25個(gè)結(jié)果，而不是1000個(gè)）就將近節(jié)省了500%的時(shí)間。這甚至沒有包括我們?cè)诔绦蚣?jí)上的花費(fèi)。是的，沒錯(cuò)，這些SQL語句僅在數(shù)據(jù)庫層上就比其他數(shù)據(jù)庫快5倍

總而言之，作為一個(gè)MySQL的擁護(hù)者，我對(duì)Disqus使用PostgreSQL所達(dá)到的性能，規(guī)模，以及靈活性表示十分震驚。我十分期待去發(fā)現(xiàn)通過這個(gè)平臺(tái)我們還能做什么，去尋找還在等待我們的挑戰(zhàn)。

最新評(píng)論