mysql - MySQL中存储几乎相同的4个字段的最佳方式

Question

我需要存储这样的东西:

mac_1 string
mac_2 string
mac_3 string
mac_4 string

具有通过4台mac(或1台mac)搜索记录的能力。迭代每条记录和值不是最好的选择吗？ MySQL MATCH AGAINST 会给服务器带来沉重的负担。我不能使用全文搜索 :( 您能建议一种简单而优雅的方式来存储此类数据吗？

Answer 1

对于高效的 equi-search，索引所有 4 个字段就足够了。

问题是您是否可以使用一个涵盖所有 4 个字段的复合索引，或 4 个单独的索引:

• 如果您有一个(几乎)总是搜索的主要“领先优势”，那么只需创建一个具有该领先优势的复合索引。例如，如果您总是在 mac_1 上搜索，无论您是否在其他字段上搜索，只需在 {mac_1, ...} 上创建一个复合索引。由于您使用的是 AND 条件，因此该索引将使您能够在单个索引查找中找到该行。
• 如果您有许多领先优势的变体，只需在所有字段上创建单独的索引。这将需要多达 4 次索引查找，但应该仍然相当快。

请注意 InnoDB tables are clustered并且聚集表的辅助索引上的“搜索”实际上可能需要 2 次搜索(索引搜索本身 + 主/集群索引搜索)。您可以通过 covering 避免这种情况，但可能以增加存储空间和降低缓存效率为代价。

也要小心not to be too clever在构建查询时，优化器实际上可以利用这些索引。如果需要，不要回避动态 SQL。

---

另一种设计是通过外键连接两个表，其中“子”表将包含 mac 值....

CREATE TABLE p (
    p_id INT PRIMARY KEY
    -- Other fields...
);

CREATE TABLE c (
    p_id INT REFERENCES p (p_id),
    mac varchar(50), -- Or whatever type is appropriate.
    PRIMARY KEY (p_id, mac)
);

...然后查询连接到(例如)'aaa' AND 'bbb' AND 'ccc' 的 mac 值的父行这个:

SELECT p.p_id -- Other fields...
FROM p JOIN c ON p.p_id = c.p_id
WHERE mac = 'aaa' OR mac = 'bbb' OR mac = 'ccc'
GROUP BY p.p_id -- Other fields...
HAVING COUNT(*) = 3;

[SQL Fiddle]

顺便说一句，如有必要，此设计将允许每个父行有超过 4 个 mac 值。

不幸的是，MySQL 没有最复杂的查询优化器，因此请在进行此设计之前测试实际数量的数据。