sql - 如何在垂直设计的表格上实现搜索？

Question

我有一个这样的表结构(垂直设计)。我可以为每个用户设置无限数量的属性(例如:城市、电话等)。

表:tbl_UserAttributes

┌────────┬───────────┬────────────┐
| UserID │ FieldName │ Value      |
├────────┼───────────┼────────────┤
│ 341    │ city      │ MyCity1    │
│ 772    │ phone     │ 1234567890 │
│ 033    │ city      │ MyCity2    │
│ 044    │ sex       │ M          │
│ 772    │ firstname │ MyName     │
│ ---    │ ---       │ ---        │
└────────┴───────────┴────────────┘

我必须实现一个搜索功能，它应该输出我们对水平设计的表应用这样的查询的行:

SELECT 
    FieldName 
FROM 
    tbl_UserAttributes 
WHERE 
    city='%Mumbai%' AND 
    sex='M' AND ...

请不要让我更改数据库设计。

更新:目前，我有一个 JOIN 解决方案，它非常慢并且有时会挂起服务器。任何替代方法？

Answer 1

EAV 表是一件好事，只要您不需要一次搜索多个值，在这种情况下它就变成了一件坏事。

您不能一次索引多个值，因为它们位于不同的记录中。

在 SQL Server 表中，您可以为多个值创建索引 View 并将其用于搜索。

在 Oracle 中，您可以按 UserID 对表进行聚类，这会将所有具有相同 UserID 的记录保存在一个数据页中，该数据页将使用最具选择性值的索引，并快速扫描其他值。

在 PostgreSQL 中，您可以将所有值存储在一个数组中，并使用 GIN 索引对其进行索引。

在 MySQL 中，您不能执行这两个操作。

这是一个将返回值的查询:

SELECT  *
FROM    tbl_UserAttributes tcity
JOIN    tbl_UserAttributes tsex
ON      tsex.userid = tcity.userid
WHERE   tcity.fieldname = 'city'
        AND tcity.value LIKE '%Mumbai%'
        AND tsex.fieldname = 'sex'
        AND tsex.value = 'M'

但不要指望它会很快。

更新:

如果需要精确匹配，可以在(fieldname, value, userid)上创建复合索引，将最有选择性的fieldname放入第一个表并使用 STRAIGHT_JOIN 强制顺序:

SELECT  *
FROM    tbl_UserAttributes tcity
STRAIGHT_JOIN
        tbl_UserAttributes tsex
ON      tsex.userid = tcity.userid
WHERE   tcity.fieldname = 'city'
        AND tcity.value = 'Mumbai'
        AND tsex.fieldname = 'sex'
        AND tsex.value = 'M'

但是，这对您当前的查询没有帮助，因为您正在寻找通配符匹配，在这种情况下索引不是很有帮助。除非您查询的是妇产医院数据库，否则您的第二张表不会从索引中获益太多。

它仍然会为您节省一些时间，因为可以使用索引扫描而不是表扫描。