如何使用關係數據庫根據作為集合/集合的屬性進行索引

我想在 O(log N) 時間內查詢作為集合的屬性。

如何在 MySQL 或 PostgreSQL 等關係數據庫中為此設置索引？

例子：

book: {
  title: "Hitchhiker's Guide to the Galaxy",
  genre: ["science-fiction", "comedy"]       
}

我想在 O(log N) 時間內做這樣的查詢：

SELECT * FROM books WHERE genre='comedy'
( or perhaps: 'comedy' in genre )

我已經使用 Big Table 完成了這項工作，這非常簡單。我已經使用由程式碼維護的二級索引使用 Cassandra 完成了它 - 我很遺憾，因為它涉及大量的複雜性成本。

使用 MySQL 或 PostgreSQL 等關係數據庫來執行此操作的好方法是什麼？我希望能夠配置表和索引，這樣我就不必使用程式碼維護任何額外的東西。

我知道這可能是一個非常基本的問題，但是我在Google上搜尋這個概念時遇到了很多麻煩，而且我擔心我的關係數據庫經驗非常有限。我懷疑這種情況有一個術語/行話，但我什至不知道它是什麼。

Welp，由於您對關係數據庫解決方案感興趣，關鍵字是relational，第一步是以更規範的形式儲存您的數據，人們將使用關係數據庫。例如，您可能希望將數據儲存在三個相關的表中：Books、Genres和BooksGenres。下面是一些用於創建這些表的範例 DDL 腳本：

CREATE TABLE Books
(
   BookId INTEGER PRIMARY KEY,
   Title VARCHAR(500)
);

CREATE TABLE Genres
(
   GenreId INTEGER PRIMARY KEY,
   GenreName VARCHAR(100)
);

CREATE TABLE BooksGenres
(
   BookId INTEGER,
   GenreId INTEGER
);

ALTER TABLE BooksGenres ADD PRIMARY KEY (BookId, GenreId);

這將創建一個專用於您的Books數據的表，一個用於唯一列表的Genres表，以及一個表 ( BooksGenres)，因為它們具有多對多關係，因此將兩者關聯在一起。

以這種方式構造它而不是單個非規範化表的一個好處是，如果您更改了特定的名稱，Genre您只需更新Genres表中的單個記錄，並且所有Books相關的記錄Genre都會在您加入時自動反映該更改這兩張桌子在一起。

在單個非規範化表中，您必須更新使用效率低得多的每條Book記錄。Genre

現在要注意的另一件事是在大多數現代關係數據庫系統中，PRIMARY KEY欄位會CLUSTERED INDEX自動為它們創建。這就是為什麼我們不需要編寫任何額外的程式碼來創建特定索引的原因，因為我們即將加入的PRIMARY KEY欄位恰好是每個相應表的欄位，這使得以下查詢開箱即用：

SELECT B.*, G.* -- Note using * is an anti-pattern for multiple reasons, so you should actually explicitly list out only the fields you need
FROM Books AS B
INNER JOIN BooksGenres AS BG
   ON B.BookId = BG.BookId
INNER JOIN Genres AS G
   ON BG.GenreId = G.GenreId
WHERE G.GenreName = 'comedy';

現在假設您確實想通過一個不是表上PRIMARY KEY/的欄位來連接CLUSTERED INDEX，並且您想為該欄位適當地索引以提高效率，例如該Genres.GenreName欄位。NONCLUSTERED INDEX然後，您只需要在該表中的該欄位上創建一個二級索引（稱為 a ），如下所示：

CREATE NONCLUSTERED INDEX IX_Genres_GenreName ON Genres (GenreName);

如果查詢優化器發現它比減少數據WHERE G.GenreName = 'comedy'的索引更有效，那麼您使用的關係數據庫系統甚至可以為上述查詢使用該二級索引來服務它的一部分。CLUSTERED INDEX

您通常希望創建涵蓋查詢的謂詞（JOIN, WHERE,HAVING子句）的索引，因為這些子句會在為您的查詢提供數據時減少數據。但有時對子句中的欄位進行索引也很有幫助，ORDER BY因為它會保存按相同順序預先排序的數據，並在每次ORDER BY執行帶有該子句的查詢時將您從排序操作中解救出來。

最後，您的範例是一個相對簡單的案例，但是隨著您的架構隨著表中更多欄位的發展而變得更加複雜，並且正在針對這些表執行不同的查詢。

例如，對於復合（多列）索引，您定義列的順序（大多數時候）很重要，因為它們定義了數據本身的順序（通常儲存在具有搜尋時間的B-Tree 資料結構O(log(n))中） ，並且通常只能在您的查詢使用索引中定義的所有或任何連續子集（從左到右讀取）時有效使用。

其他考慮有時也會發揮作用，例如使用最具選擇性（最獨特）的列引導您的索引定義，因為它會導致最平衡的 B-Tree，並在尋找索引時提高效率。

關於索引有很多可以說的，遠遠超過你的特定問題的單一答案，所以除了我所說的和我連結的嵌入式資源之外，你可能還會發現這些額外的感興趣的資源：

1. 關係數據庫的數據庫索引基礎
2. B-Tree：搜尋和插入
3. 複合索引是否也適用於第一個欄位的查詢？
4. 什麼是數據庫索引選擇性？
5. 什麼是數據庫中的基數？
6. 聚集索引 - Brent Ozar
7. 索引和數據訪問模式 - Erik Darling

注意：我連結的資源在實踐中可能指的是特定的數據庫系統，但這些概念都是通用的，足以適用於您選擇的任何現代關係數據庫系統。

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