設置索引以支持靈活的 API 搜尋端點

我有一個 MySQL 5.7 表，其中包含使用者可以搜尋的活動。它將最終保存少於 100,000 行，並且插入將少於 50/天。該activities表的結構如下：

CREATE TABLE activities (
   activityID int AUTO_INCREMENT, 
   createdBy char, -- a unique AWS ID. eg: "us-east-1:128d0a74-c82f-4553-916d-90053e4a8b0f"
   active bool, 
   city varchar, 
   length int, 
   price  decimal, 
   latitude decimal, 
   longitude decimal,
   CONSTRAINT Activities_PK PRIMARY KEY (activityID)
);

我將根據發送到 API 端點的查詢字元串建構查詢。有 3 個“路徑”可以搜尋 API 以返回活動。

• 如果createdBY包含查詢字元串，Select ... where createdBy = ~some id~
• 如果包含，則按city選擇city，如果包含，則選擇最小值，如果包含，則選擇最大值，如果包含length``price``price
• 如果latitude和longitude包括在內，則在 2 點之間搜尋

到目前為止我提出的索引：

• createdBy，作為 B 樹。這應該是 HASH 索引，因為它是唯一標識符嗎？
• (city, length, price). 由於這是一個覆蓋索引，如果構造的 Select 查詢僅搜尋 by city，即使它包含lengthand ，它是否仍會使用該索引price？

另外，由於覆蓋索引從左側開始，我是否還應該包含(city, price, length)索引？

• (latitude, longitude)

這些是可接受的指標嗎？我應該包含活動列的索引嗎？所有查詢都應該只返回活動活動。我的猜測是約 90% 的行將處於活動狀態，而約 10% 的行將處於非活動狀態。這個答案表明沒有。

非常感謝！歡迎任何其他建議！

InnoDB 不支持HASH索引；只有B-TREE. 您當然可以使用另一個名為createdByHash. 使用類似的東西CRC32作為您的散列函式，使其基於整數並每行節省幾個字節。改為索引此列並進行查詢SELECT .. FROM .. WHERE createdByHash = CRC32(<input>) AND createdBy = <input>（您必須同時擁有這兩個部分，因為可能與 CRC32 發生衝突）

是的，MySQL 按從左到右的順序使用多列索引。SELECT city, length, price FROM .. WHERE city = x仍將使用索引。如果您有任何基於範圍的查詢，則索引應將這些範圍列放在右側。僅使用該索引的前兩列的索引(A, B, C)。WHERE A=4 AND B>5 AND C=3

如果你在緯度/經度上做一個簡單的大於/小於，你所擁有的可能會沒問題。這個很難，因為它們都是范圍謂詞。您可能需要在應用程序中試驗 (lat, long) 與 (long, lat)。

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