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

Question

您好，有一个用户表，我们在其中存储一些顶级字段，例如 userid、createdOn、email

以及第二个名为“属性”的表，用于存储附加属性的键值对。像 userid、key、value

键可能是姓名、电话、性别等。

不可能将其展平，因为我们可能添加新属性并且不想更改表。

我们的 users 表中有超过 1000 万行，properties 表中有超过 1 亿行。

查询这样的系统的最佳方法是什么？我们目前对表进行联接并使用基本的 where 子句。

有没有更高级的方法来处理这样的数据？我们经常需要搜索多个字段，例如名称“%jo”、性别=男性、国家=usa 和 foo=bar

Answer 1

您所描述的通常称为实体属性值或 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 实例。提前规划无限增长的数据库需要您进行一些容量规划和基准测试。