将 MySQL 表分成 2 个？当前搜索速度太慢

Question

我正在使用 MySQL 表来存储用户将搜索的地图上的事物数据。该网站允许用户添加额外的过滤器来缩小结果范围。

主 MySQL 查询：

SELECT listing_id, listings.property_1, property_2, address_1, address_2, city, listings.lat, listings.lng, img_subpath, photos, some_timestamp
    FROM table
    WHERE listings.lat BETWEEN 61.123 AND 61.124
    AND listings.lng BETWEEN 40.123 AND 40.124
    AND some_timestamp BETWEEN DATE_SUB( NOW(), INTERVAL 0 DAY) AND DATE_SUB( NOW(), INTERVAL 5 DAY)')
    JOIN (SELECT price, lat, lng, MAX(some_timestamp) as latest FROM listings GROUP BY price, lat, lng) as t2', 'listings.price=t2.price AND listings.lat=t2.lat AND listings.lng=t2.lng AND listings.some_timestamp=t2.latest', 'inner')
    LIMIT 200,0

列：

（索引列以粗体显示）

（以 property_ 开头的列除前 2 个之外都是可选过滤器）

• listing_id int(8)
• url varchar(255)
• city varchar(32)
• somename varchar(32)
• price int(7)
• price_per int(7)
• property_1 int(2)
• property_2 int(2)
• address_1 varchar(255)
• address_2 carchar(255)
• city varchar(64 )
• state varchar(32)
• postal int(6)
• some_timestamp 时间戳
• another_timestamp 时间戳
• lat float(10)
• lng float(10)
• 描述文本
• img_subpath varchar(15)
• 照片文本
• reply_email varchar(255)
• 电话 varchar(16)
• property_3 tinyint(1)
• property_4 tinyint(1)
• property_5 tinyint(1)
• property_6 tinyint(1)
• property_7 tinyint(1)
• property_8 tinyint(1)
• property_9 tinyint(1)
• property_10 tinyint(1)
• property_11tinyint(1)
• property_12tinyint(1)
• property_13tinyint(1)
• property_14tinyint(1)

问题数据库获取结果的时间过长（3-4秒）。如何才能将其速度加快到 <0.5 秒，这在非常相似的网站上已经完成了？

如果我有 2 个表，第一个表将仅包含搜索中涉及的列，下一个表包含所有其他列。因此，当搜索发生时，它会搜索第一个表，获取结果的 listing_id，然后使用 IN 子句从第二个表中检索所有其他数据？

请指教，谢谢！

Answer 1

我认为您正在尝试通过以下方式获取过去五天的时间戳：

AND some_timestamp BETWEEN DATE_SUB( NOW(), INTERVAL 0 DAY)
AND DATE_SUB( NOW(), INTERVAL 5 DAY)')

如果是这种情况，它应该与以下内容相同

AND some_timestamp BETWEEN DATE_SUB(NOW(), INTERVAL 5 DAY)) AND NOW()

如果您没有带有 some_timestamp 且具有未来值的记录，您

AND some_timestamp > DATE_SUB(NOW(), INTERVAL 5 DAY))

现在可以使用，我认为这应该工作得更快

SELECT listing_id, listings.property_1, property_2, address_1, address_2, city,
    listings.lat, listings.lng, img_subpath, photos, some_timestamp
FROM t2
LEFT JOIN listings
    ON (listings.price=t2.price AND listings.lat=t2.lat AND listings.lng=t2.lng
        AND t2.some_timestamp < listings.some_timestamp)
WHERE listings.lat BETWEEN 61.123 AND 61.124
    AND listings.lng BETWEEN 40.123 AND 40.124
    AND some_timestamp BETWEEN DATE_SUB(NOW(), INTERVAL 5 DAY)) AND NOW()
    AND t2.some_timestamp is null
LIMIT 200

PS：我不认为拆分表会提高性能太多...实际上，如果您必须连接两个表才能获得结果，则需要更多时间

Answer 2

您所提出的在某种程度上是索引如何工作背后的理论。速度减慢很可能是由于加入聚合子选择造成的。我建议预先聚合该信息（无法立即看到任何不能这样做的原因）并针对该表进行联接。