商業邏輯該放在哪裡維護，是個難有標準答案的問題：有人主張全放在應用程式（通常是中介層），有人主張全放進資料庫的預存程序（stored procedure）。作者的觀點是——每一條 SQL 查詢都已經內嵌了部分商業邏輯，因此問題不是「該不該把商業邏輯放進資料庫」，而是：

  • 有多少商業邏輯應該維護在資料庫裡？

判斷的兩個主要面向是程式架構的正確性（correctness）與效率（efficiency）。

每條 SQL 查詢都內嵌商業邏輯#

只要你送出一條 SQL，就已經把商業邏輯送進了資料庫。以 Chinook 資料庫為例，取出某張專輯的曲目：

  select name
    from track
   where albumid = 193
order by trackid;

即使這麼簡單的查詢，每個子句都是商業邏輯的直接翻譯：

  • select 只取 name——應用程式此刻只關心曲名。
  • from 只用 track 資料表——這是針對此用途的決定。
  • where 限定 albumid = 193——直接對應業務需求。
  • order by trackid——不只表達「依碟片曲序顯示」的意圖，還內含「trackid 的順序就是碟片原始曲序」這個領域知識。

加上 join 取得曲風的變形，或在輸出欄位做衍生計算（如 milliseconds * interval '1 ms' as duration、pg_size_pretty(bytes)），商業邏輯的成分就更明顯了。

可以辯稱「沒有計算就不算商業邏輯」，但這些簡單查詢確實已承擔一部分你要實作的邏輯——當它被用在「專輯曲目列表頁」時，它甚至就是全部的邏輯。

商業邏輯對應的是使用案例#

光看一條查詢去判斷「這是商業邏輯還是資料存取」是不可能的——必須放在一個**使用案例（use case）**或使用者故事（user story）的脈絡下才有意義。

案例：列出某位藝人的所有專輯，各附上總時長。

  select album.title as album,
         sum(milliseconds) * interval '1 ms' as duration
    from album
         join artist using(artistid)
         left join track using(albumid)
   where artist.name = 'Red Hot Chili Peppers'
group by album
order by album;
         album         │           duration
═══════════════════════╪══════════════════════════════
 Blood Sugar Sex Magik │ @ 1 hour 13 mins 57.073 secs
 By The Way            │ @ 1 hour 8 mins 49.951 secs
 Californication       │ @ 56 mins 25.461 secs
(3 rows)

這是使用案例到 SQL 的直接翻譯。另一種做法是拆成多條查詢、把計算放在應用程式端：

  1. 取出該藝人的專輯清單
  2. 對每張專輯，取出每首曲目的時長
  3. 在應用程式裡逐專輯加總
延伸範例：多查詢版的 Python 物件模型實作

作者刻意寫了一支典型的「土製 ORM」程式，因為你可能在自己的專案裡認得出這些模式：

#! /usr/bin/env python3
# -*- coding: utf-8 -*-

import psycopg2
import psycopg2.extras
import sys
from datetime import timedelta

DEBUGSQL = False
PGCONNSTRING = "user=cdstore dbname=appdev application_name=cdstore"


class Model(object):
    tablename = None
    columns = None

    @classmethod
    def buildsql(cls, pgconn, **kwargs):
        if cls.tablename and kwargs:
            cols = ", ".join(['"%s"' % c for c in cls.columns])
            qtab = '"%s"' % cls.tablename
            sql = "select %s from %s where " % (cols, qtab)
            for key in kwargs.keys():
                sql += "\"%s\" = '%s'" % (key, kwargs[key])
            if DEBUGSQL:
                print(sql)
            return sql

    @classmethod
    def fetchone(cls, pgconn, **kwargs):
        if cls.tablename and kwargs:
            sql = cls.buildsql(pgconn, **kwargs)
            curs = pgconn.cursor(cursor_factory=psycopg2.extras.DictCursor)
            curs.execute(sql)
            result = curs.fetchone()
            if result is not None:
                return cls(*result)

    @classmethod
    def fetchall(cls, pgconn, **kwargs):
        if cls.tablename and kwargs:
            sql = cls.buildsql(pgconn, **kwargs)
            curs = pgconn.cursor(cursor_factory=psycopg2.extras.DictCursor)
            curs.execute(sql)
            resultset = curs.fetchall()
            if resultset:
                return [cls(*result) for result in resultset]


class Artist(Model):
    tablename = "artist"
    columns = ["artistid", "name"]

    def __init__(self, id, name):
        self.id = id
        self.name = name


class Album(Model):
    tablename = "album"
    columns = ["albumid", "title"]

    def __init__(self, id, title):
        self.id = id
        self.title = title
        self.duration = None


class Track(Model):
    tablename = "track"
    columns = ["trackid", "name", "milliseconds", "bytes", "unitprice"]

    def __init__(self, id, name, milliseconds, bytes, unitprice):
        self.id = id
        self.name = name
        self.duration = milliseconds
        self.bytes = bytes
        self.unitprice = unitprice


if __name__ == '__main__':
    if len(sys.argv) > 1:
        pgconn = psycopg2.connect(PGCONNSTRING)
        artist = Artist.fetchone(pgconn, name=sys.argv[1])

        for album in Album.fetchall(pgconn, artistid=artist.id):
            ms = 0
            for track in Track.fetchall(pgconn, albumid=album.id):
                ms += track.duration

            duration = timedelta(milliseconds=ms)
            print("%25s: %s" % (album.title, duration))
    else:
        print('albums.py <artist name>')

執行結果：

$ ./albums.py "Red Hot Chili Peppers"
    Blood Sugar Sex Magik: 1:13:57.073000
               By The Way: 1:08:49.951000
          Californication: 0:56:25.461000

你或許不會寫得一模一樣，但物件模型的 API 往往在底層執行同一類的連環查詢；更糟的是，有些「魔法」物件模型還堅持用 select * 把中間物件灌滿所有欄位。

這種「多查詢＋應用端計算」的寫法，在正確性與效率上都有問題，以下分別分析。

正確性#

使用多條語句時，必須正確設定隔離等級（isolation level），並嚴謹控管連線與交易語意——上面的範例程式兩者都沒做。

SQL 標準定義四種隔離等級，PostgreSQL 實作其中三種（不提供 dirty read）。簡化理解：

  • Read uncommitted：PostgreSQL 接受此設定但實際執行 read committed（符合標準）。
  • Read committed：預設值。交易可以看到其他交易「已提交」的變更——同一交易內執行兩次 SELECT count(*) FROM stock;，若期間有人異動庫存，兩次結果會不同。
  • Repeatable read：整個交易期間（BEGIN 到 COMMIT）維持同一份資料庫快照（snapshot），線上備份是典型用途。
  • Serializable：保證併發執行的結果等同某種「一次一交易」的序列化順序。

每個交易可以各自使用不同的隔離等級：備份工具用 repeatable read、一般應用用 read committed、庫存管理用 serializable，可以並存。

範例程式的每次 fetch* 呼叫都看到不同的資料庫快照。若在 Album.fetchall 與 Track.fetchall 之間，有人刪除了專輯（或修正輸入錯誤、把專輯改派給別的藝人），程式會得到無聲的空結果集，向使用者顯示時長為 0——在其他語言或寫法下甚至可能直接爆錯。

以 SQL 一次完成的解法天生免疫：PostgreSQL 的每條查詢永遠在單一一致快照內執行；隔離等級影響的是「跨查詢之間」能否重用快照。

效率#

效率可從靜態與動態兩種分析衡量：靜態指開發者寫出解法的時間、維護負擔、審查難易；動態指執行期的 CPU、記憶體、網路、磁碟資源。

  • SQL 解法：八行基礎 SQL，幾分鐘寫完、審查同樣容易；一次網路往返（round trip）就拿回「每張專輯的名稱與時長」，應用端幾乎不需計算。
  • 應用端解法：先查藝人（還多傳了用不到的藝人名稱）、再查專輯清單、然後每張專輯各發一次查詢取曲目、在應用端加總、最後輸出。三張專輯就是五次往返——換成 Iron Maiden 的 21 張專輯更慘。

網路資源要同時考慮延遲與頻寬。應用伺服器與資料庫之間延遲常在 1–2 ms 之譜：

  • 作者實測：整條 SQL 在伺服器端不到 1 ms 執行完，含傳送查詢與接收結果平均約 3 ms。
  • 對主鍵的簡單查詢（where id = :id）伺服器端常只要 0.1 ms——理論上 1 ms 能做十次，但每次都得等約 1 ms 的網路傳輸。

當查詢在伺服器端 1 ms 內完成，網路往返就成為主要的執行時間因素。避開原生 SQL 的代價，在正確性與效率上都看得見。

預存程序——資料存取 API#

PostgreSQL 可建立伺服器端函式（server-side function），把程式碼存在資料庫、呼叫時執行。以藝人 id 為參數的版本：

create or replace function get_all_albums
  (
     in artistid bigint,
     out album    text,
     out duration interval
  )
 returns setof record
 language sql
as $$
   select album.title as album,
          sum(milliseconds) * interval '1 ms' as duration
     from album
          join artist using(artistid)
          left join track using(albumid)
    where artist.artistid = get_all_albums.artistid
group by album
order by album;
$$;

（若用藝人「名稱」當參數，函式就無法有效率地使用，且毫無必要。）這是 SQL 語言寫成的函式——本質上就是一條可帶參數的 SQL。呼叫方式：

select * from get_all_albums(127);

只有藝人名稱時，可用子查詢直接帶入（注意子查詢作為函式引數仍需自己的括號，形成雙層括號）。自 PostgreSQL 9.3 引入 lateral join 後，還能把函式用在 join 子句中：

select album, duration
  from artist,
       lateral get_all_albums(artistid)
 where artist.name = 'Red Hot Chili Peppers';

lateral join 讓查詢保持高效率，還能組進更複雜的情境——例如列出「資料庫中恰好有四張專輯」的藝人及其專輯時長：

with four_albums as
(
    select artistid
      from album
  group by artistid
    having count(*) = 4
)
   select artist.name, album, duration
     from four_albums
          join artist using(artistid),
          lateral get_all_albums(artistid)
order by artistid, duration desc;

預存程序讓 SQL 程式碼得以在伺服器端跨使用案例重用——當然有其利弊。

程序式程式碼與預存程序#

預存程序最大的陷阱：分不清何時該用程序式（procedural）程式碼、何時該用帶參數的純 SQL。同一個範例可以用 PLpgSQL 寫得很醜——在函式裡宣告 record、跑 for ... loop、對每張專輯再各查一次——這等於把前面批評過的應用端錯誤模式原封不動搬進資料庫，唯一差別是迴圈在資料庫伺服器上跑、省掉了網路往返。

要用預存程序，一律先用 SQL 語言寫，只在必要時才改用 PLpgSQL。想要效率，預設就是 SQL。

商業邏輯該放在哪？#

同一個簡單案例，我們看了三種實作：應用程式端、應用程式環境中的一條 SQL、伺服器端預存程序。

  • 應用程式端多查詢版既不正確也沒效率，應該避免。與其跟網路延遲玩遊戲，不如善用 PostgreSQL 的 join 能力：五次往返、ping 2 ms，起跑前就先輸掉 10 ms，而對照的查詢不到 1 ms 就執行完。
  • 也要考慮併發與擴展性：同樣的結果集要發五倍的查詢，可以想見擴展能力大約也差這個倍數。與其在 API 前面再架一層快取架構，不如寫出更聰明、更有效率的 SQL。
  • 預存程序讓開發者在資料庫伺服器內建立資料存取 API，並與資料庫 schema 以交易方式一起維護——PostgreSQL 連 DDL（data definition language，即 create、alter、drop）都支援交易。另一個優點是查詢文字存在伺服器端，網路上傳輸的資料又更少。
Backward 撰寫 SQL 查詢 一個小型應用 Forward