在MySQL中搜索属性的最佳方式

Question

Hi有一个用户表，我们在其中存储一些顶级字段，如userid、createdOn、电子邮件

第二个表称为属性，存储一对附加属性的键值。像userid，key，value

钥匙可以是姓名、电话、性别等。

这是不可能的，因为我们可能会添加新的属性，并且不想更改表。

我们在用户表中有1000多万行，在属性表中有1亿多行。

查询这样的系统的最佳方式是什么。我们目前在表上执行联接，并执行基本的where子句。

有没有更先进的方法来处理这样的数据？我们经常需要在多个字段上搜索，比如名称"%jo"和性别=男性和国家=美国和foo=bar

Answer 1

您所描述的通常称为实体属性值(Entity Attribute Value，简称EAV(。对于您所描述的情况来说，这是一种非常常见的情况，在这种情况下，您有比制作普通列更多的潜在属性。

CREATE TABLE eav_table (
entity INT NOT NULL,
property VARCHAR(64) NOT NULL,
value VARCHAR(64),
PRIMARY KEY (entity, property),
KEY (entity, property, value)
);

前两列是表的主键，因为每对只有一行。但是，为所有三列都设置一个辅助索引是有用的，因为这将是查询最常读取的列。

查询具有相等条件的多个值是可以的。MySQL可以进行元组比较。

SELECT ...
FROM eav_table
WHERE entity = 1234 AND (property, value) IN (
('gender', 'male'),
('country', 'usa'),
('foo', 'bar')
)

如果(entity, property, value)上有索引，优化器将使用它，并有效地找到匹配的行。

缺点是此语法不支持LIKE或任何其他类型的模式。所以你需要更明确地做这些：

SELECT ...
FROM eav_table
WHERE entity = 1234 AND property = 'name' AND value LIKE '%jo'

由于entity、property是索引中最左边的列，因此索引仍将起到部分作用。但是，带有前导通配符的LIKE模式无论如何都不能使用索引，因此它必须检查与前两列匹配的所有行，并测试每一行的模式。效率稍低，但至少会缩小搜索范围。

如果您还想进行类似"；哪些实体拥有美国的国家财产"您需要另一个辅助索引，将不同的列作为最左边的列：

ALTER TABLE eav_table ADD KEY (property, value);

然后，您可以搜索属性/值，并获得一组匹配的实体：

SELECT ...
FROM eav_table
WHERE (property, value) = ('country', 'usa')

如果你有合适的索引来支持你需要做的搜索，即使是一个有数百万或数亿行的表也能很好地工作。但最终，随着表变得越来越大，您可能不得不将其拆分为多个表或多个MySQL实例。为无限期增长的数据库提前规划需要进行一些容量规划和基准测试。