用 intarray 反正規化標籤 • PostgreSQL 的藝術

當使用者可以自訂標籤（tag），又要支援進階查詢條件時，SQL 的處理就會變得棘手。本章以 Last.fm 資料集示範：索引並搜尋同時被標為 blues 與 rhythm and blues 的曲目。

進階標籤索引#

PostgreSQL 內建許多有趣的資料型別，陣列（array）是其中之一；contrib 裡則有 intarray 擴充套件。文件中最關鍵的一段：

@@ 與 ~~ 運算子測試一個陣列是否滿足查詢，查詢以特殊資料型別 query_int 的值表示。查詢由整數值組成，逐一與陣列元素比對，並可用 &（AND）、|（OR）、!（NOT）運算子組合，必要時可加括號。例如查詢 1&(2|3) 比對「包含 1，且包含 2 或 3」的陣列。

create extension intarray;

配合 intarray 的運作方式，需要建一張新表：每首曲目對應一個整數陣列，內容是它被貼上的標籤清單（沿用先前加的 rowid 識別欄位）：

  select tt.tid, array_agg(tags.rowid) as tags
    from      tags
         join tid_tag tt
           on tags.rowid = tt.tag
group by tt.tid
   limit 3;

 tid │   tags
═════╪═══════════
   1 │ {1,2}
   2 │ {3,4}
   3 │ {5,6,7,8}
(3 rows)

「標籤文字 → 數字陣列」的計算不必每次都重來，可以把結果快取進具體化視圖（materialized view），並用特殊的索引運算子建立 GIN 索引，才能享受 intarray 的進階查詢能力：

begin;

create view lastfm.v_track_tags as
    select tt.tid, array_agg(tags.rowid) as tags
      from tags join tid_tag tt on tags.rowid = tt.tag
  group by tt.tid;

create materialized view lastfm.track_tags as
   select tid, tags
     from v_track_tags;

create index on track_tags using gin(tags gin__int_ops);

commit;

搜尋#

準備好見證魔法了。先取出兩個目標標籤的 rowid：

select array_agg(rowid)
  from tags
 where tag = 'blues' or tag = 'rhythm and blues';

 array_agg
═══════════
 {3,739}
(1 row)

intarray 實作了名為 query_int 的特殊查詢字串，長得像 '(1880&179879)'，支援 not、and、or 三種邏輯運算子。因為標籤要由使用者動態提供，我們直接從 tags 表組出 query_int 字串：

select format('(%s)',
              string_agg(rowid::text, '&')
             )::query_int as query
  from tags
 where tag = 'blues' or tag = 'rhythm and blues';

format 函式負責組字串（把中間結果包進括號）。
string_agg 以指定分隔符聚合文字值——平常分隔符是逗號或分號，這裡因為要找「同時具有兩個標籤」的曲目，用的是 and 運算子 &。

  query
═════════
 3 & 739
(1 row)

這種寫法可以輕鬆注入任意多個標籤，很適合當作應用程式裡的查詢模板，由使用者提供標籤清單。intarray 的查詢格式還接受 or 與 not 運算子；若要開放給使用者，只需調整組 query_int 的那段 SQL。

那麼到底有多少曲目同時被標為 blues 與 rhythm and blues？把上面的模板放進共同資料表運算式（CTE），再作為 join 條件套用在 track_tags 上：

with t(query) as (
     select format('(%s)',
                   array_to_string(array_agg(rowid), '&')
                   )::query_int as query
     from tags
     where tag = 'blues' or tag = 'rhythm and blues'
)
select count(*)
  from track_tags join t on tags @@ query;

 count
═══════
  2278
(1 row)

共 2,278 首。當然你會想取回曲目的中繼資料——不過這個資料集裡只有 track 雜湊 id 可以對回去：

with t(query) as (
    select format('(%s)',
                     array_to_string(array_agg(rowid), '&')
            )::query_int as query
     from tags
       where tag = 'blues' or tag = 'rhythm and blues'
)
     select track.tid,
             left(track.artist, 26)
               || case when length(track.artist) > 26 then '…' else '' end
            as artist,
            left(track.title, 26)
              || case when length(track.title) > 26 then '…' else '' end
            as title
      from      track_tags tt
            join tids on tt.tid = tids.rowid
            join t on tt.tags @@ t.query
            join lastfm.track on tids.tid = track.tid
  order by artist;

延伸輸出：33 首同時具備兩個標籤的曲目（節錄）

        tid         │           artist            │             title
════════════════════╪═════════════════════════════╪═════════════════════════════
 TRANZKG128F429068A │ Albert King                 │ Watermelon Man
 TRASBVS12903CF4537 │ Alicia Keys                 │ If I Ain't Got You
 TRAXPEN128F933F4DC │ B.B. King                   │ Please Love Me
 TRAPRRP12903CD97E9 │ Big Mama Thornton           │ Hound Dog
 TRAXULE128F9320132 │ Fontella Bass               │ Rescue Me
 TRALIVO128F4279262 │ Janis Joplin                │ Down On Me
 TRALWNE12903C95228 │ Ray Charles                 │ Heartbreaker
 TRAHSYA128F428143A │ Screamin' Jay Hawkins       │ I Put A Spell On You
 TRBFMTO128F9322AE7 │ The Rolling Stones          │ Start Me Up
 TRAERPT128F931103E │ The Rolling Stones          │ Time Is On My Side
 ...
(33 rows)

關鍵在時間的數量級：標籤搜尋只花約 10ms，前端就有充裕的時間讓使用者保持愉快——即使實作的是進階搜尋。

讓自訂標籤變簡單#

處理使用者自訂標籤的常規做法是 join 一張標籤參照表，但要表達完整的搜尋條件會相當複雜——例如「同時標了 blues 與 rhythm and blues，可能還要排除 finger picking」。intarray 提供了一個強大的專用查詢語言，且有直接的索引支援，讓你能從應用程式直接組出動態的索引搜尋。