使用一个巨大的where子句优化sql查询

qlfbtfca  于 2021-06-17  发布在  Mysql
关注(0)|答案(1)|浏览(167)

我正在开发一个系统(使用laravel),在这个系统中,用户可以填充一些过滤器来获得他们需要的数据。数据不是实时准备的,一旦设置了过滤器,就会将作业推送到队列中,一旦查询完成,就会创建一个csv文件。然后用户收到一封电子邮件,其中包含创建的文件,以便他们可以下载该文件。
我在作业中看到了一些错误,处理一个作业的时间超过30分钟,当我检查时,我看到一些用户创建了超过600个值的过滤器。
此筛选器值转换如下:

SELECT filed1, 
       field2, 
       field6 
FROM   table 
       INNER JOIN table2 
               ON table.id = table2.cid 
/* this is how we try not to give same data to the users again so we used NOT IN */ 
WHERE  table.id NOT IN(SELECT data_id 
                       FROM   data_access 
                       WHERE  data_user = 26) 
       AND ( /* this bit is auto populated with the filter values */ 
           table2.filed_a = 'text a' 
            OR table2.filed_a = 'text b' 
            OR table2.filed_a = 'text c' )

我没想到用户会因为一个巨大的过滤器而变得狂野和微调。他们可以这样做,但需要一个解决方案,使这个查询更快。
一种方法是动态地创建一个带有筛选器值的temp表,并转换查询 INNER JOIN 但不确定是否会提高性能。另外,考虑到在正常的一天中,系统需要创建至少40个ish temp表,然后删除它们。从长远来看,这会成为另一个问题吗?
我很想听到任何其他的建议,可以帮助我解决这个问题以外的临时表方法。

pengsaosao

pengsaosao1#

我建议这样写查询:

SELECT ?.filed1, ?.field2, ?.field6  -- qualify column names (but no effect on performance)
FROM table t JOIN
     table2 t2
     ON t.id = t2.cid 

WHERE NOT EXISTS (SELECT 1
                  FROM data_access da
                  WHERE t.id = da.data_id AND da.data_user = 26
                 ) AND
     t2.filed_a IN ('text a', 'text b', 'text c') ;

然后我会推荐索引。很可能是:
table2(filed_a, cid) table1(id) (如果 id 已经是主键) data_access(data_id, data_user) 您可以将其作为自己的查询进行测试。我不知道如何让laravel产生这个(假设它满足您的性能目标)。

相关问题