MySql:多个左连接使查询非常慢

Question

运行以下查询需要很长时间。所有表都包含超过 200,000 条记录。索引存在于连接语句中的所有列上:

Select files.filename,
coalesce(temp_decmetadata.glare_roadGlare, 0) as glare_roadGlare,
coalesce(temp_decmetadata.glare_sun, 0) as glare_sun,
coalesce(temp_decmetadata.spray_heavySpray, 0) as spray_heavySpray,
coalesce(temp_kafaspdobjectdata.contrast_wellVisible, 0) as contrast_wellVisible,
coalesce(temp_kafaspdobjectdata.roadSafety_zebraCrossing, 0) as roadSafety_zebraCrossing,
coalesce(temp_visionmetadata.roadUser_bicycle, 0) as roadUser_bicycle,
coalesce(temp_visionmetadata.roadUser_car, 0) as roadUser_car,
coalesce(temp_visionmetadata.roadUser_pedestrian, 0) as roadUser_pedestrian
from files
left join 
(
 Select id,
 count(case when glare like '%roadGlare%' then 1 end) as glare_roadGlare,
 count(case when glare like '%sun%' then 1 end) as glare_sun,
 count(case when spray like '%heavySpray%' then 1 end) as spray_heavySpray
 from decmetadata
 group by id 
) temp_decmetadata on temp_decmetadata.id = files.id 
left join 
(
 Select id,
 count(case when contrast like '%wellVisible%' then 1 end) as contrast_wellVisible,
 count(case when roadSafety like '%zebraCrossing%' then 1 end) as roadSafety_zebraCrossing
 from kafaspdobjectdata
 group by id 
) temp_kafaspdobjectdata on temp_kafaspdobjectdata.id = files.id 
left join 
(
 Select id,
 count(case when roadUser like '%bicycle%' then 1 end) as roadUser_bicycle,
 count(case when roadUser like '%car%' then 1 end) as roadUser_car,
 count(case when roadUser like '%pedestrian%' then 1 end) as roadUser_pedestrian
 from visionmetadata
 group by id 
) temp_visionmetadata on temp_visionmetadata.id = files.id 

“解释选择...”的结果附在下面:

Answer 1

我不是 MySQL 专家，但您确实可以通过将 JOIN 条件移动到内部 WHERE 条件并将行过滤为仅满足任何 的行来改进子查询CASE 条件，这将减少必须由 LIKE 运算符评估的行数(这是此查询中最昂贵的操作)。以下脚本应该可以让您了解:

...
CROSS JOIN  (
  SELECT
  SUM(CASE WHEN glare LIKE '%roadGlare%' THEN 1 ELSE 0 END) AS glare_roadGlare,
  SUM(CASE WHEN glare LIKE '%sun%' THEN 1 ELSE 0 END) AS glare_sun,
  SUM(CASE WHEN spray LIKE '%heavySpray%' THEN 1 ELSE 0 END) AS spray_heavySpray
  FROM decmetadata
  WHERE ( id = files.id ) -- benefits from an index over DECMETADATA.ID
    AND ( 1=2
      OR glare LIKE '%roadGlare%'
      OR glare LIKE '%sun%'
      OR spray LIKE '%heavySpray%'
    )
) temp_decmetadata
...