我有以下MySQL表:
CREATE TABLE IF NOT EXISTS `pics` (
`id` mediumint(8) unsigned NOT NULL auto_increment,
`bnb_id` mediumint(7) unsigned NOT NULL,
`img_path` varchar(128) NOT NULL,
`img_path_gallery` varchar(128) NOT NULL,
`img_path_thumb_small` varchar(128) NOT NULL,
`img_path_thumb_large` varchar(128) NOT NULL,
`img_path_thumb_grid` varchar(128) NOT NULL,
`title` varchar(80) NOT NULL,
`order` tinyint(2) NOT NULL,
`upload_date` datetime NOT NULL,
`state` enum('LOCAL','S3') NOT NULL default 'LOCAL',
`is_cover` tinyint(1) unsigned default NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `bnb_id_2` (`bnb_id`,`is_cover`),
KEY `bnb_id` (`bnb_id`),
KEY `is_cover` (`is_cover`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=30371 ;
is_cover
是我为每个bnb_id
只选择一张图片而创建的字段:当图片被选为封面时,它被设置为1,否则被设置为NULL
。我需要将表LEFT JOIN
转换为另一个表,我们称之为bnb
;对于每个bnb
条目,pics
表中可能有多行(在bnb_id
上有一个引用完整性绑定),但在这种情况下,我必须从pics
表中只提取一行,因此需要is_cover
库和所有索引(我尝试的每一个其他解决方案都会产生持续10到50秒的查询)。
然而,即使在这种情况下,查询也非常慢,并且在bnb
表中约有10000个元素、pics
表中约为30000个元素的数据池上,每个查询都需要5到8秒的执行时间。从is_cover
=1的表中进行选择是非常快速和直接的,但当放入更大的查询中时,一切都会中断。
SELECT subbnb.*,
3956 * 2 * ASIN(
SQRT(
POWER(
SIN((_LAT - abs(lat)) * pi()/180 / 2),
2) +
COS(_LAT * pi()/180 ) *
COS(abs(lat) * pi()/180) *
POWER(
SIN((_LNG - abs(lng)) * pi()/180 / 2),
2)
)
) AS distance,
prices.price,
pics.img_path_thumb_grid,
reviews.count reviewsCount,
likes.count likesCount
FROM
(SELECT
bnb.*,
bnbdata_a.*,
pos.lat,
pos.lng
FROM bnb
JOIN bnbdata
ON (bnb.id = bnbdata.bnb_id)
JOIN positions pos
ON (bnb.id = pos.bnb_id)
) subbnb
LEFT JOIN (
SELECT *
FROM pics
WHERE is_cover = 1
) pics
ON (subbnb.id = pics.bnb_id)
LEFT JOIN (SELECT price, bnb_id FROM prices WHERE category = "DAILY") prices
ON (subbnb.id = prices.bnb_id)
LEFT JOIN (SELECT COUNT(*) AS count, bnb_id FROM reviews GROUP BY bnb_id) reviews
ON (subbnb.id = reviews.bnb_id)
LEFT JOIN (SELECT COUNT(*) AS count, bnb_id FROM likes GROUP BY bnb_id) likes
ON (subbnb.id = likes.bnb_id)
WHERE
lng BETWEEN _LNGA AND _LNGB
AND lat BETWEEN _LATA AND _LATB
HAVING distance < 10
ORDER BY distance
LIMIT 0, 25
(您看到的带_前缀的字符串是实际数值)
EXPLAIN
查询会产生以下结果:
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY <derived5> system NULL NULL NULL NULL 0 const row not found
1 PRIMARY <derived6> system NULL NULL NULL NULL 0 const row not found
1 PRIMARY <derived2> ALL NULL NULL NULL NULL 10522 Using where; Using temporary; Using filesort
1 PRIMARY <derived3> ALL NULL NULL NULL NULL 7040
1 PRIMARY <derived4> ALL NULL NULL NULL NULL 1
6 DERIVED likes index NULL PRIMARY 6 NULL 1 Using index
5 DERIVED reviews index NULL bnb_id 5 NULL 1 Using index
4 DERIVED prices ALL NULL NULL NULL NULL 1 Using where
3 DERIVED pics ref is_cover is_cover 2 11760 Using where
2 DERIVED pos ALL PRIMARY NULL NULL NULL 10543
2 DERIVED bnbdata eq_ref PRIMARY PRIMARY 3 db.pos.bnb_id 1
2 DERIVED bnb eq_ref PRIMARY PRIMARY 3 db.pos.bnb_id 1
看起来is_cover
索引被MySQL忽略了(Using where
,id 4),但当我对pics
表运行小选择时,情况也是如此,一切都很快发生。我在这个查询中找不到瓶颈,将JOIN删除到pics
会让一切变得更快,但JOINed子查询本身速度很快,大查询的其他部分也很快——即使一开始有数学计算代码,它的执行时间也不会超过2秒。
有人知道瓶颈在哪里,以及如何绕过瓶颈吗?
您可以尝试使用这样的联接来重建查询(如果不正确,很抱歉,但您只描述了一个表):
SELECT
bnb.*, bnbdata_a.*,
pos.lat, pos.lng
3956 * 2 * ASIN(
SQRT(
POWER(
SIN((_LAT - abs(lat)) * pi()/180 / 2),
2) +
COS(_LAT * pi()/180 ) *
COS(abs(lat) * pi()/180) *
POWER(
SIN((_LNG - abs(lng)) * pi()/180 / 2),
2)
)
) AS distance,
prices.price,
pics.img_path_thumb_grid,
reviews.count reviewsCount,
likes.count likesCount
FROM bnb
JOIN bnbdata
ON bnb.id = bnbdata.bnb_id
JOIN positions pos
ON bnb.id = pos.bnb_id
LEFT JOIN pics
ON bnb.id = pics.bnb_id AND pics.is_cover = 1
LEFT JOIN prices
ON bnb.id = prices.bnb_id
LEFT JOIN (SELECT COUNT(*) AS count, bnb_id FROM reviews GROUP BY bnb_id) reviews
ON bnb.id = reviews.bnb_id
LEFT JOIN (SELECT COUNT(*) AS count, bnb_id FROM likes GROUP BY bnb_id) likes
ON bnb.id = likes.bnb_id
WHERE
lng BETWEEN _LNGA AND _LNGB AND lat BETWEEN _LATA AND _LATB AND distance < 10
ORDER BY distance
LIMIT 0, 25
或者像这样重建:
SELECT tmp_bnb.*,
pics.img_path_thumb_grid,
reviews.count reviewsCount,
likes.count likesCount
FROM
(
SELECT
bnb.*, bnbdata_a.*,
pos.lat, pos.lng
3956 * 2 * ASIN(
SQRT(
POWER(
SIN((_LAT - abs(lat)) * pi()/180 / 2),
2) +
COS(_LAT * pi()/180 ) *
COS(abs(lat) * pi()/180) *
POWER(
SIN((_LNG - abs(lng)) * pi()/180 / 2),
2)
)
) AS distance,
prices.price
FROM bnb
JOIN bnbdata
ON bnb.id = bnbdata.bnb_id
JOIN positions pos
ON bnb.id = pos.bnb_id
WHERE
lng BETWEEN _LNGA AND _LNGB AND lat BETWEEN _LATA AND _LATB AND distance < 10
ORDER BY distance
LIMIT 0, 25
) as tmp_bnb
LEFT JOIN pics
ON tmp_bnb.id = pics.bnb_id AND pics.is_cover = 1
LEFT JOIN prices
ON tmp_bnb.id = prices.bnb_id
LEFT JOIN (SELECT COUNT(*) AS count, bnb_id FROM reviews GROUP BY bnb_id) reviews
ON tmp_bnb.id = reviews.bnb_id
LEFT JOIN (SELECT COUNT(*) AS count, bnb_id FROM likes GROUP BY bnb_id) likes
ON tmp_bnb.id = likes.bnb_id
或者,您可以将查询一分为二,在第一个查询中您可以获得基本信息,在第二个查询中,您可以获得其他信息,如rewiews
计数和likes
计数。
我还认为,一个好主意是将reviews_counter
和likes_counter
添加到bnb
表中,而不是每次都计数,而是每隔一段时间(小时)计数一次,或者使用插入触发器递增。还可以考虑在bnb
表中添加新的列cover_pic_id
,该列将保存封面图片的id
让我知道表演怎么样。