mysql - 优化 MySQL 中的 SELF JOIN。提示 "Lost Connection to MySQL server during Query"

Question

表organizeddata设计为以ID作为主键，并在<上使用辅助索引caseindex strong>案例ID。该表包含约 500 万行和约 47000 个唯一 CaseID。

ID     | CaseID  | Actor | Activity

1      | Case1   | John  | A
2      | Case1   | Sean  | C
3      | Case1   | John  | B
4      | Case2   | John  | A
5      | Case2   | Ben   | F
6      | Case2   | Brad  | B
7      | Case2   | John  | E

我有一个在每个案例中执行连接的存储过程。 caseID 作为参数currentCase传递。

SELECT T1.ID, T2.ID, (T2.ID-T1.ID) as Diff, T1.Actor FROM
(SELECT * FROM organiseddata use index(caseindex) where CaseID=currentCase) as T1
JOIN
(SELECT * FROM organiseddata use index(caseindex) where CaseID=currentCase) as T2
ON (T2.ID-T1.ID)>=2 AND T1.Actor=T2.Actor AND T1.Activity <> T2.Activity
ORDER BY (T2.ID-T1.ID) DESC;

一旦获得T1.ID和T2.ID，我需要找到中间Actor )之间

SELECT ID, Actor as IntActor from organiseddata use index(caseindex) where CaseID=currentCase AND ID>T1.ID AND ID<T2.ID;

对于找到的每个此类 ID，我需要将结果更新到 RESULT_TABLE 中。上表的 RESULT_TABLE 结构如下(未显示值)。 A、B、C、E、F 的值均为 double 类型。

Actor  | A  | B  | C  | E  | F

A      | -- | ---| -  | -- | --
B      | -- | ---| -  | -- | --
C      | -- | ---| -  | -- | --
E      | -- | ---| -  | -- | --
F      | -- | ---| -  | -- | --

使用 CONCAT，我将 T1.Actor 和 IntActor 的值附加到如下所示的查询(一般表示)中。

INSERT INTO RESULT_TABLE(`T1.Actor`,`IntActor`) values ('',1)
ON DUPLICATE KEY UPDATE `IntActor`=`IntActor`+POW(0.5,Diff-2);

但执行一段时间后，我得到“查询期间与 MySQL 服务器的连接丢失”。我已经尝试将 max_allowed_pa​​cket 增加到更高的值，但没有成功。

我应该在 my.ini 文件中更改哪些其他配置值。有没有更好的方法来执行上面的连接。

Answer 1

对于问题的连接部分，您的查询可以重写为:

SELECT T1.ID, T2.ID, (T2.ID-T1.ID) as Diff, T1.Actor 
FROM organiseddata as T1
JOIN organiseddata as T2
    ON T2.ID>=T1.ID+2 
   AND T1.Actor=T2.Actor 
   AND T1.Activity <> T2.Activity
   AND T1.CaseID = T2.CaseID
WHERE T1.CaseID=currentCase

我会调查这个计划，然后可能会删除 caseindex 上的索引并将其替换为:

CREATE INDEX ... ON organiseddata (caseindex, Actor, ID, Activity);

与原始版本进行比较，看看是否有帮助。