InnoDB MySql 表結構規劃與優化
我打算擁有一個包含數千萬行的 InnoDB MySql 表。
每行代表一條記錄，該記錄至少可以屬於 200 多個類別中的一個。大多數記錄將屬於 2 個或更多類別。由數字 id 表示的所有類別，從 1 到 200+。
在查詢返回屬於某個類別的所有記錄時，將這些多個類別儲存在每個記錄中以獲得最快查找速度的最有效方法是什麼？
我認為最快的方法是為每個類別設置一個單獨的列，然後只查詢該列。但問題是我將不得不製作 200 多個列，並且將來會添加新的類別。另外，我認為擁有 200 多個索引對我的伺服器沒有任何好處。
然後我想為每個類別創建一個單獨的記錄，這樣類別就只有一列，索引就可以正常工作了。這種方法的問題在於表已經數千萬，並且會隨著時間的推移不斷增長。如果我將為每個目前的多類別記錄添加重複記錄，表的大小將很快變得無法管理，並且會影響性能。
我能想到的另一種方法是將與記錄相關的所有類別儲存在一個用逗號或任何其他分隔符分隔的列中，然後執行 LIKE %% 搜尋。但如果我理解正確，這種方法不使用索引，會導致查找速度慢。
那麼處理它的正確方法是什麼？我什至考慮為每個類別創建一個單獨的表，但是當我需要對整個數據執行一些其他類型的查詢時，這是不可接受的。
推薦
“屬於某一類別的所有記錄” –> 在記錄和類別之間建立多對多映射。要使該表達到最佳狀態，請按照此處的提示進行操作。確保
SMALLINT UNSIGNED為 category_id 使用 2 字節。（你不應該相信你不會超過 255。）然後查詢類似於
SELECT r.* FROM RecordsCategories AS rc JOIN Records AS r USING(record_id) WHERE rc.category_id = 123;這種方法將對 的一部分進行索引掃描
rc以找到，然後通過 PKrecord_ids隨機進入。r是的， 中會有“數億行”rc，但它只會掃描其中的一小部分，因為所有條目category_id=123都將“聚集”在一起（在數據或二級索引中）。對替代品的批評
如果您有一列包含一個commalist 類別，則需要獲取*每一行。*那是對
Records. （順便說一句，不要使用LIKE %123%, 使用FIND_IN_SET()– 不是更快，但在處理邊緣情況時不會那麼混亂。）“每個類別都有一個單獨的表格”——這是這個（和其他）論壇上的一個非常常見的問題。答案是“從不！”。
“每個類別的單獨列” - 用勺子堵住我！該表將更胖，並且您將再次需要全表掃描。
“200+ 個索引”——硬限制是 64。實際限制更像是 5。
基於 Akina 和 Acidon 的評論：在 8.0 中，您可以使用
TINYBLOB或BINARY(..)設置位。這與擁有多個SETs. 在 8.0 之前，按位 OR（等）被限制為 64 位；現在BLOBs工作。SET/BLOB/BINARY/BIGINT 最節省空間。但是你的整個數據集只有幾十 GB，所以我不認為“大小”是個問題。即使是我推薦的 many:many 表也只會多出幾個 GB。