如何优化这个复杂的EAV MYSQL查询？

Question

你能优化我写的这个查询吗？

我创建了一种动态虚拟数据库，让用户可以添加自定义字段而不影响数据库结构。以下是目前结构的简化视图。

tables         | columns

---

db_cases       | caseid
db_structure   | fieldname
db_data        | fieldname, data, caseid
db_names       | nameid

• 我们可以通过向db_structure添加一行来创建一个新的字段。
• 我们希望记录的任何数据都记录在db_data中。
• 名称存储在db_names中，name_id存储在db_data中。

我试图将案例输出到html表格中。

希望其余部分是不言自明的，您可以看到它是多么低效。我能否通过连接完成相同的事情？

SELECT 
case_id,
(SELECT data_field_value 
 FROM db_data 
 WHERE data_case_id = case_id AND data_field_name = 'casestatus'
) AS casestatus,
(SELECT forename_company 
 FROM db_names 
 WHERE name_id = (SELECT data_field_value 
                  FROM db_data 
                  WHERE data_case_id = case_id AND data_field_name = 'client1'
                 )
) AS client1_forename_company
FROM db_cases 

谢谢

- amof

---

我会首先缩进它并让其更易理解，但不会改变原意，也不会提供任何额外的解释。 - Iznogood

动态数据库？那么你是在一个 SQL 数据库之上添加自己的数据库语义吗？有点可怕... http://thedailywtf.com/Articles/The_Inner-Platform_Effect.aspx - Marc B

从删除它开始。 - Chibuzo

@MarcB 听起来就像是 SharePoint（列表）... 没什么大不了的;-)（实际上，SP 更改底层表模式并维护内部/漂亮/数据库列名称的映射。） - user166390

这是一个非常恶心的EAV风格结构的完美例子。在它把你扔掉之前，把它扔掉！ - user1191247

Answer 1

实际上，Chibuzo是正确的。首先删除它 :-)) 但在此之前，可以稍微尝试一下，这是很好的大脑锻炼，就像下棋或其他类似的东西 :-)

select 
    case_id,
    d_status.data_field_value as case_status,
    d_client1_name.forename_company as client1_forename_company
from db_cases 
        join db_data as d_status 
            on d_status.data_case_id = case_id 
               AND d_status.data_field_name = 'casestatus'
        join db_data as d_client1
            on d_client1.data_case_id = case_id 
               AND d_client1.data_field_name = 'client1'
        join db_names as d_client1_name
            on d_client1_name.name_id = d_client1.data_field_value

我希望这些直接连接而不使用子查询的方式更高效，虽然你需要进行测试 - 优化中经常会有意外情况。

- Tomas

---

谢谢Tomas，这很好用，并且正在快速执行查询，但是我不能忽视这个线程上的建议，即EAV并不是一个好主意。除了维护此类数据库所需的代码量（已完成），我可以问一下为什么这种模型受到高度批评吗？扩展性？ - amof

@amof，你是说这个查询比原来的查询快得多？有多快？ - Tomas

最初我无法运行针对2000个案例和1个字段的查询，30秒后就会超时，现在我能够运行针对2000个案例和10个字段的查询 - 它几乎是瞬间完成的。目前有什么可以替代我的模型吗？ - amof

@amof - 将不同类型的数据存储在单个通用VARCHAR中效率低下。EAV，特别是这样实现时，无法良好扩展。如果该字段将被密集填充，则更有意义直接修改底层表。您仍然需要正确实现某种扩展到本地数据字典。根据您的使用情况，6NF可能是用户定义字段的合适方法。如果您在SO上搜索“ EAV 6NF性能”，则会找到一些类似问题的详细答案。最佳方法严重依赖于您的需求。 - user1191247

感谢@amof，我预计会有差异，但没有这么大！关于设计问题，我能看到的是所有查询和维护都会变得非常复杂，因此如果可能的话，我会尝试避免这种情况并简化它。也许我会看一些专为此目的制作的解决方案，比如XML数据库......但我真的不能推荐，因为我没有任何经验处理这种用例。至少我很高兴听到现在查询速度快了！ - Tomas