sql - 优化一个奇怪的 MySQL 查询

Question

希望有人能帮忙解决这个问题。我有一个查询，它从 PHP 应用程序中提取数据并将其转换为在 Ruby on Rails 应用程序中使用的 View 。 PHP应用程序的表格是E-A-V样式的表格，具有以下业务规则:

给定字段:名字、姓氏、电子邮件地址、电话号码和手机运营商:

• 每个属性都定义了两个自定义字段:一个是必填字段，一个是非必填字段。客户端可以使用其中之一，不同的客户端根据自己的规则使用不同的(例如客户端 A 可能不关心名字和姓氏，但客户端 B 可能)
• RoR 应用必须将每“对”属性仅视为单个属性。

现在，这是查询。问题是它在大约 11,000 条记录下运行得很好。然而，真实的数据库有超过 40,000 个，查询速度非常慢，大约需要 125 秒才能运行，这从业务角度来看是完全 Not Acceptable 。我们绝对需要提取这些数据，并且我们需要与现有系统进行交互。

UserID 部分是伪造一个与 Rails 表相关的 Rails 式外键。我是一个 SQL Server 人员，而不是 MySQL 人员，所以也许有人可以指出如何改进这个查询？他们(业务)要求加快速度，但我不确定如何加快速度，因为我需要每个客户端的每个字段，然后必须合并数据，因此需要各种 group_concat 和 ifnull 调用。

select `ls`.`subscriberid` AS `id`,left(`l`.`name`,(locate(_utf8'_',`l`.`name`) - 1)) AS `user_id`,
ifnull(min((case when (`s`.`fieldid` in (2,35)) then `s`.`data` else NULL end)),_utf8'') AS `first_name`,
ifnull(min((case when (`s`.`fieldid` in (3,36)) then `s`.`data` else NULL end)),_utf8'') AS `last_name`,
ifnull(`ls`.`emailaddress`,_utf8'') AS `email_address`,
ifnull(group_concat((case when (`s`.`fieldid` = 81) then `s`.`data` when (`s`.`fieldid` = 154) then `s`.`data` else NULL end) separator ''),_utf8'') AS `mobile_phone`,
ifnull(group_concat((case when (`s`.`fieldid` = 100) then `s`.`data` else NULL end) separator ','),_utf8'') AS `sms_only`,
ifnull(group_concat((case when (`s`.`fieldid` = 34) then `s`.`data` else NULL end) separator ','),_utf8'') AS `mobile_carrier` 
from ((`list_subscribers` `ls` 
    join `lists` `l` on((`ls`.`listid` = `l`.`listid`)))
    left join `subscribers_data` `s` on((`ls`.`subscriberid` = `s`.`subscriberid`)))  
where (left(`l`.`name`,(locate(_utf8'_',`l`.`name`) - 1)) regexp _utf8'[[:digit:]]+') 
group by `ls`.`subscriberid`,`l`.`name`,`ls`.`emailaddress`

编辑我删除了正则表达式，这使得查询速度从近 120 秒加快到大约 20 秒。如果我可以删除该组，那么它会更快，但我不能，因为删除它会导致它为每个字段复制带有空白数据的行，而不是聚合它们。例如:

使用分组依据

id     user_id     first_name     last_name     email_address     mobile_phone     sms_only     mobile_carrier1         1          John           Doe        jdoe@example.com    5551234567       0          Sprint

没有分组依据

id      user_id      first_name      last_name      email_address      mobile_phone      sms_only      mobile_carrier1       1            John                           jdoe@xample.com1       1                             Doe           jdoe@example.com1       1                                           jdoe@example.com1       1                                           jdoe@example.com   5551234567

等等。我们需要的是第一个结果。

编辑#2

该查询似乎仍然需要很长时间，但今天早些时候它在生产数据库上只运行了大约 20 秒。在不进行任何更改的情况下，相同的查询现在再次花费 60 秒以上。这仍然是 Not Acceptable ..关于如何改进这个问题还有其他想法吗？

Answer 1

毫无疑问，这是我见过的第二个最可怕的 SQL 查询:-)

我的建议是用存储要求来换取速度。当您发现查询有很多每行函数( ifnull 、 case 等)时，这是一个常见的技巧。随着表变大，这些每行函数永远无法很好地扩展。

在表中创建新字段，其中将保存要提取的值，然后在插入/更新(使用触发器)而不是选择时计算这些值。从技术上讲，这不会破坏 3NF，因为触发器保证了列之间的数据一致性。

绝大多数数据库表的读取次数远多于写入次数，因此这将分摊许多选择的计算成本。此外，几乎每个报告的数据库问题都是速度问题，而不是存储问题。

举个例子来说明我的意思。您可以替换:

case when (`s`.`fieldid` in (2,35)) then `s`.`data` else NULL end

与:

`s`.`data_2_35`

在您的查询中，如果您的插入/更新触发器只是设置 data_2_35列至data或NULL取决于 fieldid 的值。然后你索引data_2_35而且，瞧，以一点存储为代价，即时提高了速度。

这个技巧可以对五个 case 完成条款，left/regexp位和“裸体”ifnull函数(包含 ifnull 和 min 的 group_concat 函数可能更难实现)。