mysql - MySQL 查询中的多个 JOIN 子句是否可以指数化它必须检查的行数？

Question

因此，我向我们的主机提供商寻求技术支持，解决我在关键搜索页面上遇到的数据库响应缓慢的问题，经过一些调查后，他告诉我我正在使用的查询正在检查 67,998,176 行。现在出现在该查询中的最大表最多 116 行，其余表平均每行 25 行。

这是我查询的 SQL

 SELECT COUNT( DISTINCT `A`.`id`) AS `total_num_resource_rows`
 FROM `admin_site_resources` AS `A`
 LEFT JOIN `admin_site_organization_resource` AS `B` ON `B`.`res_id`=`A`.`id`
 LEFT JOIN `admin_site_organizations` AS `C` ON `B`.`org_id`=`C`.`id`
 LEFT JOIN `admin_site_resource_res_topic` AS `D` ON `D`.`resource_id`=`A`.`id` 
 LEFT JOIN `admin_site_resource_topics` AS `E` ON `E`.`id`=`D`.`res_topic_id`
 LEFT JOIN `admin_site_resource_audience` AS `F` ON `F`.`resource_id`=`A`.`id`
 LEFT JOIN `admin_site_audiences` AS `G` ON `G`.`id`=`F`.`audience_id`
 LEFT JOIN `admin_site_resource_curriculum_topic` AS `H` ON `H`.`resource_id`=`A`.`id`
 LEFT JOIN `admin_site_curriculum_topics` AS `I` ON `I`.`id`=`H`.`curriculum_topic_id`
 LEFT JOIN `admin_site_resource_curriculum_grade` AS `J` ON `J`.`resource_id`=`A`.`id`
 LEFT JOIN `admin_site_curriculum_grades` AS `K` ON `K`.`id`=`J`.`curriculum_grade_id`
 LEFT JOIN `admin_site_resource_curriculum_subject` AS `L` ON `L`.`resource_id`=`A`.`id`
 LEFT JOIN `admin_site_curriculum_subjects` AS `M` ON `M`.`id`=`L`.`curriculum_subject_id` 
 LEFT JOIN `admin_site_resource_res_type` AS `N` ON `N`.`resource_id`=`A`.`id`
 LEFT JOIN `admin_site_resource_types` AS `O` ON `O`.`id`=`N`.`res_type_id` 
 LEFT JOIN `admin_site_resource_res_area_location` AS `P` ON `P`.`resource_id`=`A`.`id`
 LEFT JOIN `admin_site_resource_area_locations` AS `Q` ON `Q`.`id`=`P`.`res_area_location_id` 

 WHERE `A`.`post_status`='approved' AND (
`A`.`resource_name` LIKE '%alpha%'
OR `A`.`aliases` LIKE '%alpha%' 
OR `A`.`short_desc` LIKE '%alpha%' 
OR `A`.`resource_url` LIKE '%alpha%' 
OR `A`.`other_resource_type` LIKE '%alpha%' 
OR `C`.`org_name` LIKE '%alpha%' 
OR `E`.`topic_label` LIKE '%alpha%' 
OR `G`.`audience_label` LIKE '%alpha%' 
OR `I`.`topic_label` LIKE '%alpha%' 
OR `K`.`grade_label` LIKE '%alpha%' 
OR `M`.`subject_label` LIKE '%alpha%' 
OR `O`.`type_label` LIKE '%alpha%' 
OR `Q`.`area_location_label` LIKE '%alpha%' );

是的，我知道这个查询很长而且很难看，但我想这是必须为它完成的所有规范化的结果。我无法理解的是，几行突然变成了将近 6800 万行。是 LEFT JOINS 造成的吗？

Answer 1

根据每个表中有多少行与每个连接条件匹配，您最多可以有第一个表中的 116 行，每行与第二个表中的最多 116 行(现在为 13456 行)配对，每行最多配对第三个表中的 116 行(现在为 1560896 行)，每行与第四个表中的最多 116 行(现在为 181063936 行)配对......你知道这是怎么回事吗？

你可能在那个最大的表中只有 116 行，但是当你获取 16 个表的键的所有组合时...... http://en.wikipedia.org/wiki/Combination