MySQL 左连接对于大数据来说耗时太长

Question

我有两个表。属性表及其相关照片。一个属性可能有很多照片，但我只想要其中一张相关照片，当我使用左连接 MySQL 查询时，它变得太慢。

这是我的查询

SELECT `sp_property`.`id` as propertyid, `sp_property`.`property_name`, `sp_property`.`property_price`, `sp_property`.`adv_type`, `sp_property`.`usd`, `images`.`filepath_name` 
FROM (`sp_property`) 
LEFT JOIN  (select id, Max(property_id) as pid,filepath_name 
            from sp_property_images
            group by property_id) `images`
ON `images`.`pid` = `sp_property`.`id` 
WHERE `sp_property`.`published` = 'yes' 
GROUP BY `propertyid` 
ORDER BY `sp_property`.`feature_listing` desc, `submit_date` desc
LIMIT 1,20



CREATE TABLE IF NOT EXISTS `sp_property_images` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `property_id` varchar(100) NOT NULL,
  `filepath_name` text,
  `label_name` varchar(45) DEFAULT NULL,
  `primary` char(10) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `property_id` (`property_id`),
  KEY `id` (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=12941 ;

CREATE TABLE IF NOT EXISTS `sp_property` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `propertytype` varchar(50) NOT NULL,
  `adv_type` varchar(45) NOT NULL,
  `property_name` text,
  `division` varchar(45) NOT NULL,
  `township` varchar(45) NOT NULL,
  `property_price` decimal(20,2) unsigned DEFAULT NULL,
  `price_type` varchar(45) NOT NULL,
  `availability` varchar(100) DEFAULT NULL,
  `property_address` text,
  `p_dimension_length` varchar(45) NOT NULL,
  `p_dimension_width` varchar(45) NOT NULL,
  `p_dimension_sqft` varchar(45) NOT NULL,
  `p_dimension_acre` varchar(45) NOT NULL,
  `floor` varchar(45) NOT NULL,
  `phone` varchar(100) DEFAULT NULL,
  `aircorn` varchar(45) NOT NULL,
  `ownership` varchar(45) NOT NULL,
  `bedroom` varchar(45) NOT NULL,
  `bathroom` varchar(45) NOT NULL,
  `special_feature` text,
  `amentites` text,
  `property_detail` text,
  `submit_date` datetime DEFAULT NULL,
  `published` varchar(45) NOT NULL,
  `published_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `agent_id` varchar(45) NOT NULL,
  `source` varchar(45) NOT NULL,
  `contact_name` varchar(100) NOT NULL,
  `contact_no` varchar(100) NOT NULL,
  `contact_address` text NOT NULL,
  `contact_email` varchar(100) NOT NULL,
  `unit_type` varchar(100) DEFAULT NULL,
  `map_lat` varchar(100) DEFAULT NULL,
  `map_long` varchar(100) DEFAULT NULL,
  `show_map` varchar(3) DEFAULT 'no',
  `total_view` bigint(20) NOT NULL DEFAULT '0',
  `feature_listing` varchar(10) NOT NULL DEFAULT 'no',
  `new_homes_id` int(11) NOT NULL,
  `publish_price` int(1) NOT NULL DEFAULT '0',
  `usd` decimal(20,2) NOT NULL,
  `tag_id` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=18524 ;

Answer 1

您是否在表中添加了索引？您需要以下列上的三个索引:

• article_photo.a_id 用于分组和加入
• article_photo.p_id 用于排序
• 用于加入的article.a_id(尽管这希望已经是您的 table 的 PK)

连接的结果不保证按任何顺序排序，因此您可能希望将 ORDER BY 子句从子查询移动到外部查询:

 SELECT * from `article` 
 LEFT JOIN (
 SELECT * from `article_photo`  
 GROUP BY `a_id`) as images 
 ON article.a_id = images.a_id
 ORDER BY images.p_id DESC

此外，您无法保证您将获得哪一篇article_photo，因为选择没有聚合函数的数据(并且只有 MySQL 允许您这样做)。

<小时/>

现在问题包含了真实的表格和回答所必需的所有信息，这是我的看法 - 首先，这是您的查询:

SELECT `sp_property`.`id` as propertyid, `sp_property`.`property_name`, `sp_property`.`property_price`, `sp_property`.`adv_type`, `sp_property`.`usd`, `images`.`filepath_name` 
FROM (`sp_property`) 
LEFT JOIN  (select id, Max(property_id) as pid,filepath_name 
            from sp_property_images
            group by property_id) `images`
ON `images`.`pid` = `sp_property`.`id` 
WHERE `sp_property`.`published` = 'yes' 
GROUP BY `propertyid` 
ORDER BY `sp_property`.`feature_listing` desc, `submit_date` desc
LIMIT 1,20

让我们看看。您正在将 sp_property_images.property_id 与 sp_property.id 一起加入。这些列具有不同的类型(int 与 varchar)，我认为这会导致严重的性能损失(因为这些值必须转换为相同的类型)。

然后，您将按 sp_property.published 进行过滤，因此我建议也在此列上添加索引。另外，检查您是否确实需要将此列设置为 varchar。 bool /位标志可能也足够了(如果不够，枚举可能仍然是更好的选择)。

排序也能从索引中受益。添加跨越两列sp_property.feature_listing和sp_property.submit_date的索引。

如果上述所有方法仍然没有帮助，您可能需要删除子选择。它可能会阻止 mysql 引擎识别(并使用!)您在 sp_property_images.property_id 列上定义的索引。