mysql - 标量子查询需要花费大量时间来执行

Question

我有下表:

1. 表1 -session_id、company_id、session_start_time
2. 表2 -id、session_id、message_time、message_type、消息
3. 表3 -公司 ID、公司名称

表1存储对公司进行的 session 。每个 session 都有很多消息，这些消息存储在table2中。对于特定的message_type(比如message_type = 2)，需要从message中提取子字符串。该子字符串是公司名称。现在我需要从table1中找到下一个session_id，其中company_id与从消息中提取的公司名称相匹配，并且session_start_time >= message_time。

我正在使用以下查询。

select t1.session_id as session1,
       t1.company_id as company1,
       @transfer_time := t2.message_time as transfer_time,
       @company2 := trim(substring(t2.message, 38, locate(' abc', t2.message) - 38)) as company2,
       (select t1.session_id
        from table1 as t1
          inner join table3 as t3 on t1.company_id = t3.company_id
        where t1.session_start_time >= @transfer_time
          and t3.company_name = @company2
       order by t1.session_start_time
       limit 1) as session 2
from table1 as t1
  inner join table2 as t2 on t1.session_id = t2.session_id
  inner join table3 as t3 on t1.company_id = t3.company_id
where t2.message_type = 2

原始查询稍微复杂一些，有更多的标量子查询。该查询的执行时间非常长。我检查了解释函数，标量子查询似乎需要很长时间才能执行。但是，我想不出更好的办法。

Answer 1

这是查询:

select t1.session_id as session1, t1.company_id as company1,
       @transfer_time := t2.message_time as transfer_time,
       @company2 := trim(substring(t2.message, 38, locate(' abc', t2.message) - 38)) as company2,
       (select t1.session_id
        from table1 t1 inner join
             table3 t3
             on t1.company_id = t3.company_id
        where t1.session_start_time >= @transfer_time and
              t3.company_name = @company2
        order by t1.session_start_time
        limit 1
       ) s session 2
from table1 t1 inner join
     table2 t2
     on t1.session_id = t2.session_id inner join
     table3 t3
     on t1.company_id = t3.company_id
where t2.message_type = 2;

首先，变量的使用不正确。 MySQL 不保证 SELECT 中变量的求值顺序。因此，您需要输入原始定义:

select t1.session_id as session1, t1.company_id as company1,
       t2.message_time as transfer_time,
       trim(substring(t2.message, 38, locate(' abc', t2.message) - 38)) as company2,
       (select t1.session_id
        from table1 t1 inner join
             table3 t3
             on t1.company_id = t3.company_id
        where t1.session_start_time >= t2.message_time and
              t3.company_name = trim(substring(t2.message, 38, locate(' abc', t2.message) - 38)) 
        order by t1.session_start_time
        limit 1
       ) s session 2
from table1 t1 inner join
     table2 t2
     on t1.session_id = t2.session_id inner join
     table3 t3
     on t1.company_id = t3.company_id
where t2.message_type = 2;

接下来，我将尝试索引:table3(company_name, company_id) 和 table1(company_id, session_start_time, session_id)。