mysql - 连接表 a 到表 b 在匹配子字符串上的效率低下......想法？

Question

背景:

首先，我有一个包含名为 patients 的表的模式，我在其中关注 patient_id 和 alerts(alerts 是一串字符，其中每个字符代表一些任意值/含义)。其次，每个“患者”都是仅基于其 patient_id 前 6 位数字的组 [家庭] 的一部分。此外，还有一些第三方依赖此数据库； 我没有设计，也无法更改此架构/数据模型，我无法从 MySQL 迁移出去。

Here is a fiddle with the data model

---

挑战:

现在，我需要找到患者发出包含 !、@、#、% 的警报的事件、^ 或 & 符号及其家族成员不存在。我的第一个想法是收集所有具有包含这些符号的警报的患者，删除每个 patient_id 中的最后一位数字，然后按此值分组。现在我有一个(出于所有意图和目的)“group_ids”的列表。最后，我需要扩展列表以包含每个组的家庭成员及其各自的警报字符串。

---

这是我目前所拥有的:

查询#1:

SELECT p.patient_id, p.name_first, p.name_last, p.alerts
FROM patients p
INNER JOIN (SELECT SUBSTRING(patient_id, 1, CHAR_LENGTH(patient_id) - 1) AS group_id
    FROM patients
    WHERE patient_id BETWEEN 1000000 AND 7999999
    AND (alerts like '%!%'
    OR alerts like '%@%'
    OR alerts like '%#%'
    OR alerts like '%\%%'
    OR alerts like '%^%'
    OR alerts like '%&%')
    GROUP BY group_id) g
ON p.patient_id LIKE CONCAT(g.group_id, '%')
ORDER BY p.patient_id
LIMIT 30000;

Fiddle ~ 注意:fiddle 不是问题的准确表示，因为包含的表只有 28 条记录。

Recordset: 80,000 ~ Results: 2188 ~ Duration: 14.321 sec ~ Fetch: 0.00 sec ~ Total: 14.321 sec

---

查询#2:

SELECT p.patient_id, p.name_first, p.name_last, p.alerts
FROM patients p
JOIN (SELECT DISTINCT LEFT(patient_id, 6) AS group_id
    FROM patients
    WHERE patient_id BETWEEN 1000000 AND 7999999
    AND alerts REGEXP '[!@#%^&]') g
ON p.patient_id LIKE CONCAT(g.group_id, '%')
ORDER BY p.patient_id
LIMIT 30000;

Fiddle ~ 注意:fiddle 不是问题的准确表示，因为包含的表只有 28 条记录。

Recordset: 80,000 ~ Results: 2188 ~ Duration: 4.259 sec ~ Fetch: 5.663 sec ~ Total: 9.992 sec

编辑:在添加 name_first、name_last、alerts 和 order by 子句后，我发现这个查询花费的时间与第一个完全相同。

---

问题:

我返回的列表是准确的，但是，它不仅需要额外的处理(我打算用 PHP 来完成)，而且还需要 14 秒!

如果有人有更好的……或者至少可以指出更好、更有效的解决方案的方向，请赐教。提前致谢。

额外学分:关于 PHP 算法的任何提示，以解决上述给定数据的问题 - 忘记语义，只需一个公式即可。

Answer 1

我找到了一个足够有效的解决方案如下:

SELECT p.patient_id, name_first, name_last, alerts
FROM patients p
JOIN (SELECT DISTINCT LEFT(patient_id, 6) AS group_id
    FROM patients
    WHERE patient_id BETWEEN 1000000 AND 7999999
    AND alerts REGEXP '[!@#%^&]') g
ON LEFT(p.patient_id, 6) = g.group_id /* HERE is the simple magic */
ORDER BY p.patient_id
LIMIT 30000;

记录集:80,000 ~ 结果:2188 ~ 持续时间:0.312 秒 ~ 提取:0.062 秒 ~ 总计:0.374 秒

因为我们知道合法的 patient_ids 是 7 位数字长，我们可以通过简单地使用 LEFT(patient_id, 6) 而不是效率较低的 SUBSTRING(patient_id, 1, CHAR_LENGTH(patient_id) - 1)(我现在看到我最初可以写成 SUBSTRING(patient_id, 1, 6))。无论此处使用何种方法，真正节省的是对 ON 子句的更改。与其将 patient_id 与 LIKE CONCAT(group_id, '%') 进行比较，为什么不直接将 = 与表 'p' 中 patient_id 的左 6 位数字进行比较？

换句话说，嵌套选择用于查找所有唯一的“组”，其中至少一个成员具有所需警报符号之一。主选择使用此表来确定属于这些组的所有患者。本质上，LEFT(patient_id, 6) == 'group_id' 并且我们可以保留我们的索引...唯一的额外开销是每行调用一次 LEFT()。

"KISS." 的另一个例子

感谢大家的帮助!

编辑:因为我将在我的 PHP 算法中使用 group_id，所以我将以高效的方式将其添加到选择中:

SELECT g.group_id, RIGHT(p.patient_id, 1) AS sub_id, name_first, name_last, alerts
FROM patients p
JOIN (SELECT DISTINCT LEFT(patient_id, 6) AS group_id
    FROM patients
    WHERE patient_id BETWEEN 1000000 AND 7999999
    AND alerts REGEXP '[!@#%^&]') g
ON LEFT(p.patient_id, 6) = g.group_id
ORDER BY p.patient_id
LIMIT 30000;

HERE is a fiddle! ~ 注意:这不是解决方案的准确表示，因为包含的表只有 28 条记录。在更大的数据集上查看以上结果。

---

最后，我用来完成处理的 PHP 算法 ~ shoutout @The Nail :

$cur_group_id = 0;
$members = [];
$symbol = '';
$errs = false;
while($row = $result->fetch_assoc()){
    $row['alerts'] = preg_replace('/[^!@#%^&]+/i', '', $row['alerts']);
    if($row['group_id'] != $cur_group_id){
        if($errs){
            foreach($members as $member => $data){
                printf('<tr><td>%d%d</td><td>%s</td><td>%s</td><td>%s</td></tr>',
                    $data['group_id'],
                    $data['sub_id'],
                    $data['name_last'],
                    $data['name_first'],
                    $data['alerts']);
            }
        }
        /* reset current group */
        $cur_group_id = $row['group_id'];
        $members = array();
        $symbol = $row['alerts'];
        $errs = false;
    }
    $members[] = $row;
    if($row['alerts'] != $symbol || strlen($row['alerts']) > 1){
        $errs = true;
    }
}

总处理时间(包括查询):0.6 秒!!