Postgresql

如何儲存短篇小說以訪問單個句子?

  • January 25, 2017

我有史以來第一次建構數據庫(使用 PostgreSQL),並且對於儲存文本主體(又名故事)的最有效/合乎邏輯的方式非常矛盾。衝突源於使用者將以兩種方式訪問文本正文的事實:

1)點擊故事名稱即可訪問整個故事。

2)使用者可以在搜尋欄中輸入一個單詞或片語,這將返回找到該單詞/片語的所有句子(而不是整個故事)(這意味著它可能會從許多故事中返回許多句子)。

將會有大量(“無限”)的故事,每個故事大約 40 個句子,儘管它是自由文本,所以有些故事將包含幾百個句子。

我最初的數據庫設計是有一個帶有、和的 Story 模型(我使用的是 Ruby on Rails),然後有一個帶有story_id、和的 Storyline 模型。story_title``author_id_fk``storyline_id``storyline``story_id_fk

但是,我現在懷疑自己,並認為最好的方法是將故事的主體放在 Story 模型中的第 4 列,story_text稱為解析成相應的句子),然後 Storyline 模型要麼根本不存在(在這種情況下,將在需要時呼叫數組中的適當項目 - 不太正常,但也可能更有效..?),或者保持故事情節模型,但讓它包含對適當故事情節的引用,而不是實際文本本身。

任何想法或建議將不勝感激!

最佳解決方案很大程度上取決於“最佳”的定義。您可能正在尋找簡單性、可靠性、性能或磁碟空間優化或其他方面。

規格

無限數量的故事,每個故事約 40 句話

幾句話,可能很多故事。

使用者可以輸入一個單詞或片語

我們需要片語搜尋。(也許跨越句子邊界?)

假設

  • “句子”是簡單地由點和空格('. ' )分隔的子字元串。
  • 資源(RAM、磁碟空間)可能是一個限制因素。
  • 檢索整個正文和搜尋單個句子是同樣頻繁的任務。

解決方案

Postgres 9.6 準確地介紹了您所需要的。發行說明:

全文搜尋現在可以搜尋片語(多個相鄰單詞)

在這兩種情況下,最新的 Postgres 版本和文本搜尋索引將大大提高性能。

您已經考慮過:

將故事的主體放在故事模型的第 4 列

但是一個數組會增加 24 個字節 + 每個句子 1 個或更多字節,並使索引變得更加複雜。(以及幾乎所有其他操作。)我會排除數組解決方案。

storyline每句一行的附加表使索引再次變得簡單,但每句增加了約 40 個字節。查找和檢索句子很簡單。但是所有的寫操作都比較複雜。

關於 Postgres 中的儲存大小:

考慮一個帶有body普通text的表。將字元串快速拆分為句子(使用“句子”的簡單定義)。較小的總儲存大小可能對大表的性能更重要(要獲取的頁面更少)。我們只需要索引一列。簡而言之:

  • 死簡單的儲存和處理
  • 最小磁碟空間
  • 仍然是最佳性能的熱門競爭者

桌子:

CREATE TABLE story (
 story_id  serial PRIMARY KEY
, author_id int REFERENCES author  -- NOT NULL?
, title text                       -- NOT NULL?
, body  text                       -- NOT NULL?
);

FTS 索引,假設為英語:

CREATE INDEX story_body_english_fts_idx ON story USING GIN (to_tsvector('english', body));

查詢返回整個故事:

SELECT *
FROM   story
WHERE  to_tsvector('english', body) @@ phraseto_tsquery('english', 'Lewis Carroll');

查詢返回句子:

SELECT story_id, sentence_nr, sentence
FROM   story, unnest(string_to_array(body, '. ')) WITH ORDINALITY x(sentence, sentence_nr)
WHERE  to_tsvector('english', body)     @@ phraseto_tsquery('english', 'Lewis Carroll')
AND    to_tsvector('english', sentence) @@ phraseto_tsquery('english', 'Lewis Carroll');

該查詢展示了一個片語範例。但它也適用於單個單詞。

重複WHERE條件在邏輯上是多餘的,但對於在 上使用索引是必需的body。這僅檢索具有限定句子的故事,然後重新檢查未嵌套的句子(重複相同的條件)很便宜。

關於*unnest(string_to_array(body, '. ')) WITH ORDINALITY*:

注意:對整個文本正文的檢查可以跨句子邊界查找片語- 與搜尋單個句子不同。但重新檢查sentence不包括此類匹配項。您可以微調一種或另一種方式…

FTS 還有更多選項:前綴匹配、其他語言、無語言(無詞乾或同義詞庫)、中間有單詞的片語……

引用自:https://dba.stackexchange.com/questions/161078