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

我有史以來第一次建構數據庫（使用 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 中的儲存大小：

• 為讀取性能配置 PostgreSQL

考慮一個帶有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*：

• 如何保留未嵌套數組中元素的原始順序？
• LATERAL 和 PostgreSQL 中的子查詢有什麼區別？

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

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

• 從 GIN 索引的 TSVECTOR 列中獲取部分匹配

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