背景

我正在為目前位於四個不同城市的電影院連鎖店開發一個網站（將來可能會擴展）。他們對所有城市使用相同的單一數據庫網站，這意味著我必須在某些表中有一列，其中包含每行所屬城市的 ID。

現在我有三個不同的表：

• Cinemas- 包含每個城市的電影院（ID 和名稱）。
• Movies- 包含所有已經/將要在電影院放映的電影。
• Showtimes- 包含所有城市所有電影的所有放映時間。

表的結構Showtimes如下：

Column Name   | Column Type  | Description
--------------+--------------+---------------
ID            | BIGINT       | (Primary) Unique ID for each showtime (perhaps unnecessary?)
CinemaID      | TINYINT      | Foreign key bound to Cinemas.ID
MovieID       | BIGINT       | Foreign key bound to Movies.ID
Showtime      | DATETIME     | At what date and time the movie will show 

(will contain multiple rows for each movie, i.e. one row for each showtime)

如何使用此表

該網站的使用者必須能夠：

• 查看所選城市的所有目前/即將上映的電影和放映時間（按日期排序）。

範例查詢（後端）：

SELECT MovieID, Showtime FROM Showtimes WHERE CinemaID = ? ORDER BY Showtime

• 選擇一部電影並僅查看該特定標題的所有放映時間（在所選城市中）。

範例查詢：

SELECT Showtime FROM Showtimes WHERE CinemaID = ? AND MovieID = ? ORDER BY Showtime

• 選擇一天並僅查看當天的所有電影和放映時間（在所選城市）。

範例查詢：

SELECT MovieID, Showtime FROM Showtimes WHERE CinemaID = ? AND (Showtime BETWEEN [date 12:00 AM] AND [date 12:00 PM])

所以很自然地我決定我需要為列創建索引。

問題

我遇到的問題是決定/確定如何正確索引列。每列一個索引似乎相當昂貴$$ 1 $$ $$ 2 $$所以我開始研究複合索引，這似乎是正確的選擇，但也導致了更多的混亂。

根據我的理解（根據我所讀到的內容），您應該按選擇性順序將列添加到索引中，使最有選擇性的（我猜這意味著最獨特/具有最大基數？）列在第一個綜合指數$$ 3 $$（在我的情況下，這將是Showtime列）。唯一的問題是，如果第一列包含在搜尋查詢中，則該索引只能由數據庫使用$$ 4 $$ $$ 5 $$，它目前不在我的任何一個查詢中。

問題

為了涵蓋所有使用場景，我應該對我的列應用什麼樣的索引？（最後一個場景可以省略，但前兩個是必需的）

我應該對所有列、某些列使用複合索引，還是每列都需要一個單獨的索引？

此表每週最多更新幾次以添加新的放映時間。

腳註

1 MySQL 索引 - 最佳實踐是什麼？

2 索引表中的每一列

3 索引中列的順序有多重要？（問題）

4 索引中列的順序有多重要？（＃2最高投票的答案）

5 什麼時候應該使用複合索引？

複合主鍵

我會將主鍵定義為(CinemaID, MovieID, Showtime).

這 3 列唯一標識每一行，因此ID不需要單獨的列。

綜合（二級）指數

有了這個 PK，您查詢所需的唯一附加索引是(CinemaID, Showtime).

為什麼要有這些索引？

考慮使用索引的一個好方法是將它們視為電子表格中列的順序。

想像一個電子表格，(CinemaID, MovieID, Showtime)它按每列連續排序。

您的所有查詢都CinemaID存在，這意味著您可以快速找到電子表格的“部分” CinemaID。然後，對於按 搜尋的查詢MovieID，您可以輕鬆地在第二列中找到MovieID與搜尋值匹配的“小節”。

由於第 3 列Showtime也已排序，您可以想像在該電影院中找到該電影的所有放映時間是多麼快速和容易。DBMS 以類似的方式做事，並且可以非常快速地檢索這些結果。

至於您的其他查詢，它們都以某種方式開始，CinemaID然後Showtime以某種方式使用。他們也需要MovieID在他們的結果。

因此，該(CinemaID, Showtime)索引已涵蓋在那裡。同樣，CinemaID很容易找到電子表格的“部分”（在類比中），並且所有可能的放映時間（並且會有重複，假設有多個螢幕）將按順序列出並且很容易搜尋和/或按這些值排序。

更好的是，由於您的主鍵包括MovieID，該列包含在定義列之後的所有二級索引中（至少對於 MySQL InnoDB - 其他引擎也是如此，但不一定是全部。）

想想這是我們二級索引的“電子表格”中的第三列。該列存在的原因是如果需要，主鍵的所有部分都可用於查找主表（也稱為聚集索引，在 InnoDB 中）。在這個簡單的情況下，不需要查找，因此它甚至更有效，因為它不需要雙重查找。

僅使用此主鍵和單個二級索引，您應該在您列出的任何查詢上獲得出色的性能。

事後諸葛亮

如果您同時在多個螢幕上播放一部電影，我認為這唯一標識每一行的假設可能不正確。如果您希望能夠單獨辨識這些螢幕，那麼我的解決方案不是最好的（我可以針對這種情況提供另一種解決方案，請告訴我。）

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