mysql - 由于多个、充满 JOIN 的子查询而导致查询缓慢

Question

我目前正在开发 LIMS。我需要检索与需要使用某种准备方法准备的实验室 sample 相关的信息。 (我对此进行了相当多的简化，因此对任何错误表示歉意。)我需要在查询结果中生成的列是:

Sample number|
Container type|
List of ALL chain of custodies this sample appears on|
List of ALL analysis items this sample needs to be prepped for|
List of ALL prep methods this sample needs to go through

例如:

Sample 1|100g|101, 102, 103, 104|BTEX, TPH|Moisture Content, Pentane
Sample 2|200g|101, 103|BTEX, Atterberg Limits|Headspace, Moisture Content, Pentane
Sample 3|200g|101, 102, 104|Atterberg Limits|Headspace, Moisture Content

我必须从三个不同的表中检索最后三列的列表。我目前正在使用三个不同的子查询来执行此操作:

SELECT
  lab_coc_samples.Sample_Number,
  lab_coc_samples.Container,
  (Subquery 1),
  (Subquery 2),
  (Subquery 3)
FROM  lab_coc_samples
JOIN  lab_coc                            ON lab_coc_samples.CoC_ID = lab_coc.Data_ID
JOIN  lab_analysis_items_to_prep_methods ON lab_coc.Analysis_Item = lab_analysis_items_to_prep_methods.Analysis_Code
WHERE lab_analysis_items_to_prep_methods.Prep_Code = 1

子查询 1:每个样本都会出现在几个不同的监管链上，因此我第一列的子查询是:

SELECT GROUP_CONCAT( DISTINCT lab_coc_samples.CoC_ID SEPARATOR ',')
FROM lab_coc_samples

WHERE lab_coc_samples.Sample_Code = s1.Sample_Code
GROUP BY lab_coc_samples.Sample_Code

子查询2:每个监管链都有一个分析项目，但每个样本都会链接到多个分析项目，因为它出现在多个CoC上:

SELECT GROUP_CONCAT( DISTINCT lab_analysis_items.Name SEPARATOR ',')
FROM lab_coc_samples

JOIN lab_coc            ON lab_coc.Data_ID = lab_coc_samples.CoC_ID
JOIN lab_analysis_items ON lab_analysis_items.Code = lab_coc.Analysis_Item

WHERE lab_coc_samples.Sample_Code = s1.Sample_Code
GROUP BY lab_coc_samples.Sample_Code

子查询3:每个分析项目都链接到多个准备方法(因此:多个样本与多个分析项目相关，而多个分析项目又与多个准备方法相关——喜悦):

SELECT GROUP_CONCAT( DISTINCT lab_prep_methods.Name SEPARATOR ', ') 
FROM lab_coc_samples

JOIN lab_coc                            ON lab_coc_samples.CoC_ID = lab_coc.Data_ID
JOIN lab_analysis_items_to_prep_methods ON lab_analysis_items_to_prep_methods.Analysis_Code = lab_coc.Analysis_Item
JOIN lab_prep_methods                   ON lab_prep_methods.Code = lab_analysis_items_to_prep_methods.Prep_Code

WHERE lab_coc_samples.Sample_Code = s1.Sample_Code
GROUP BY lab_coc_samples.Sample_Code

该数据库中有数千个样本需要准备，这使得该查询非常慢。如何在不修改数据库结构的情况下提高该查询的效率？即使我需要有关每个表中示例的所有实例的信息，我是否可以以某种方式使用联接？加入会有帮助吗？

Answer 1

这应该会好一点。您实际上是在内部查询中重复外部查询，但不再是:

SELECT
  lab_coc_samples.Sample_Number,
  lab_coc_samples.Container,
  (Subquery 1),
  (Subquery 2),
  (Subquery 3)

FROM  lab_coc_samples

JOIN  lab_coc
  ON  lab_coc.Data_ID = lab_coc_samples.CoC_ID

JOIN  lab_analysis_items_to_prep_methods
  ON  lab_analysis_items_to_prep_methods.Analysis_Code = lab_coc.Analysis_Item
 AND  lab_analysis_items_to_prep_methods.Prep_Code = 1

WHERE lab_coc_samples.Sample_Code = s1.Sample_Code

GROUP BY lab_coc_samples.Sample_Code

子查询1:

SELECT GROUP_CONCAT( DISTINCT lab_coc_samples.CoC_ID SEPARATOR ',')

子查询2:

SELECT GROUP_CONCAT( DISTINCT lab_analysis_items.Name SEPARATOR ',')
FROM  lab_analysis_items
WHERE lab_analysis_items.Code = lab_coc.Analysis_Item

子查询3:

SELECT GROUP_CONCAT( DISTINCT lab_prep_methods.Name SEPARATOR ', ') 
FROM  lab_prep_methods
WHERE lab_prep_methods.Code = lab_analysis_items_to_prep_methods.Prep_Code