需要一些帮助来优化复杂的SQL查询

问题描述：

我想知道是否有人会对我如何优化这个MySQL查询有一些输入。我认为我在索引上做了正确的事情，所以不要以为我能更快地获得这个查询（目前运行时间超过3秒），但是真的会爱一个有更多经验的人来证明我错了。需要一些帮助来优化复杂的SQL查询

这里的查询：

SELECT `rooms`.*, 
     ((IFNULL(SUM(av.host_daily_price), 0) + 
     rooms.host_daily_price * (4 - COUNT(DISTINCT av.id)))/4)/1 as 'price', 
     rooms.*, 
     (ACOS(least(1, COS(0.7115121627883911) * COS(1.291278129536698) * 
         COS(RADIANS(rooms.lat)) * COS(RADIANS(rooms.lng)) + 
         COS(0.7115121627883911) * SIN(-1.291278129536698) * 
         COS(RADIANS(rooms.lat)) * SIN(RADIANS(rooms.lng)) + 
         SIN(0.7115121627883911) * SIN(RADIANS(rooms.lat)))) * 3963.19) AS distance 
FROM `rooms` 
LEFT JOIN availabilities AS av 
    ON (av.room_id = rooms.id AND 
     av.date BETWEEN '2010-12-29' AND '2011-01-01')  
WHERE (rooms.deleted_at IS NULL) AND 
     (`rooms`.`hidden` = 0) AND 
     (rooms.id <> 7713) AND 
     (rooms.city_id = 1 AND 
     rooms.max_guests >= 4 AND 
     rooms.minimum_stay <= 3 AND 
     rooms.room_type IN ('room','apartment','house')) AND 
     (av.inquiry_id IS NULL) 
GROUP BY rooms.id 
HAVING SUM(IFNULL(status, 0)) = 0 AND 
     (COUNT(*) = 4 OR `rooms`.default_available = 1) 
ORDER BY distance ASC 
LIMIT 12;

解释的输出：

id select_type table type possible_keys key key_len ref rows Extra 

1 SIMPLE rooms ref PRIMARY,index_rooms_on_city_id,index_rooms_on_room_type,index_rooms_on_city_id_and_updated_at index_rooms_on_city_id 5 const 2412 Using where; Using temporary; Using filesort 

1 SIMPLE av ref index_availabilities_on_room_id,index_availabilities_on_room_id_and_date,index_availabilities_on_room_id_and_date_and_status index_availabilities_on_room_id 5 roomorama.rooms.id 79 Using where

让我知道如果任何其他信息将是有益的！

这是一个错字吗？为什么你在SELECT中有两次房间？ – 2010-10-15 04:43:24

答

您应该认真考虑存储距离而不是计算它，特别是如果按照该值进行排序。

由于我在寻找接近某个位置的房间（随时都会改变），因此每个查询的距离都会有所不同 – 2010-10-15 06:58:07

确定然后使用MySQL Spatial Extensions http://dev.mysql.com/doc /refman/5.0/en/spatial-extensions.html – Xint0 2010-10-19 03:55:54

答

没有看到你的表是如何索引的，很难判断是否有任何特定的问题。我不知道MySQL的解释输出，所以我不会伪造任何东西。

但是，您可以做的一件事是创建条件索引。例如，除了有像

create index rooms_by_id on rooms(room_id);

您的常用指标，你可以拥有已经建成的条件指标，从而减少了必须穿越

create index rooms_by_id_usable on rooms(room_id) 
    WHERE (deleted_at IS NOT NULL) and (hidden <> 0)

如果索引记录数您的rooms记录中的20％匹配deleted_at IS NULL and hidden <> 0)，则此备用索引rooms_by_id_usable将比rooms_by_id小20％，并且将花费（大致）20％的时间来遍历。

这一切都取决于MySQL优化器以及它如何选择使用索引等。我知道在PostgreSQL中，这确实很好。

不幸的是，我不认为MySQL支持部分索引。他们有一种叫做部分索引的东西，但它不同，在这里没有用处。 – 2010-10-15 16:10:41

答

如果距离计算被删除，性能是否显着提高？如果是这样，将lat和lng的sin和cos存储在房间表上（并在查询中使用存储的值）可能是值得的 - 这些函数相对处理器密集型，因此为所有相对较大的数据集导出它们可能会显着影响性能。

答

如何：

1 - 仅从房间取一次所有数据。正如@OMG Ponies在他的评论中指出的那样，来自房间的所有列都在查询中被提取两次。

2 - 如果被查询的常数进行的三角函数是真正恒定（例如COS（0.7115121627883911））与计算出的值替换它们，即

COS(0.7115121627883911) = .7573753305446695179374104150422980521625 
COS(1.291278129536698) = .2758925773610728508649620468976736490713 
COS(0.7115121627883911) = .7573753305446695179374104150422980521625 
SIN(-1.291278129536698) = -.9611884756680473394167554039882007538993

3 - 该查询出现在做球形三角以获得地球表面上两点之间的正确距离。这可能是更快，同样有用通过执行类似

dist = SQRT((lat2-lat1)^2 + ((long2-long1) * COS(RADIANS(lat1+lat2)/2)))^2) * 60

计算一个大概距离这（应该）给点之间（LAT2，long2）英里的距离（LAT1，long1）。调整您喜欢的任何距离度量的尾随常量。

分享和享受。

我假设表观常数实际上是与房间猎人的位置相关的查询参数。即便如此，我还是会想象查询优化器会自己做常量内联。使用笛卡尔近似来简化计算似乎是一个很好的想法;我会走得更远，并将归一化因子完全基于作为查询参数的纬度，忽略房间的纬度，以便在查询开始时计算一次，而不必每行计算一次。如果我们在谈论城市中的房间，那么错误将会很小。 – 2010-10-15 16:02:00

答

切换到PostgreSQL并使用PostGIS。这是PostgreSQL的一个地理空间扩展，可以在本地和高效地完成这类事情。如果你真的坚持MySQL，那么没有骰子，但PostgreqSQL是免费的，开源的，易于使用和快速，所以它是一个完全可行的替代MySQL（至少可以说），如果你有选择能力。

需要一些帮助来优化复杂的SQL查询

相关推荐