半径范围内结果优化：慢MySQL查询性能优化技术问询

咱们先拆解下原查询拖慢速度的核心问题，再一步步针对性优化：

1. 修复地址匹配的性能瓶颈 #

原查询里用CONCAT(property.paon, ', ', property.street) = epc.ADDRESS1做关联，这种字符串拼接+等值匹配完全无法利用索引，数据量一大就会触发全表扫描，这是拖慢查询的头号元凶。

解决办法分两种情况：

• 如果能修改epc表结构：拆分出PAON和Street字段（和property表对应），用精确字段匹配替代拼接，再给关联字段加联合索引：

  -- 给epc表加联合索引
  CREATE INDEX idx_epc_paon_street_postcode ON epc(PAON, Street, POSTCODE);
  -- 给property表加覆盖索引（包含查询需要的字段）
  CREATE INDEX idx_property_paon_street_postcode_loc ON property(paon, street, postcode, latitude, longitude);
  

  关联条件改成：property.paon = epc.PAON AND property.street = epc.Street AND property.postcode = epc.POSTCODE

• 如果没法修改epc表：在property表预先计算并存储拼接后的地址字段，再给这个字段加索引：

  -- 添加存储生成的地址字段
  ALTER TABLE property ADD COLUMN full_address VARCHAR(255) AS (CONCAT(paon, ', ', street)) STORED;
  -- 给拼接地址+邮编加索引
  CREATE INDEX idx_property_full_address_postcode_loc ON property(full_address, postcode, latitude, longitude);
  

  关联条件改成：property.full_address = epc.ADDRESS1 AND property.postcode = epc.POSTCODE

2. 用空间索引优化距离计算 #

原查询用Haversine公式逐行计算距离，属于暴力扫描，数据量稍大就会卡。MySQL 5.7+支持空间数据类型和空间索引，能大幅缩小查询范围：

第一步：添加空间字段并创建索引 #

-- 给property表添加存储坐标的POINT字段
ALTER TABLE property ADD COLUMN location POINT NOT NULL;
-- 把现有经纬度转换为空间点（注意顺序：经度在前，纬度在后）
UPDATE property SET location = ST_GeomFromText(CONCAT('POINT(', longitude, ' ', latitude, ')'));
-- 创建空间索引
CREATE SPATIAL INDEX idx_property_location ON property(location);

第二步：改写距离查询逻辑 #

用ST_Distance_Sphere函数计算球面距离（返回单位是米），结合空间索引快速筛选范围内的房产：

SELECT 
  property.paon, property.saon, property.street, property.postcode,
  property.lastSalePrice, property.lastTransferDate,
  epc.ADDRESS1, epc.POSTCODE, epc.TOTAL_FLOOR_AREA,
  -- 转换为英里（和原查询单位一致，1英里=1609.34米）
  ST_Distance_Sphere(property.location, ST_GeomFromText('POINT(-1.2175 54.6921)')) / 1609.34 AS distance
FROM property
JOIN epc 
  ON property.full_address = epc.ADDRESS1 -- 用优化后的地址匹配条件
  AND property.postcode = epc.POSTCODE
-- 筛选半径范围内的数据（比如5英里，替换成你的实际半径值）
WHERE ST_Distance_Sphere(property.location, ST_GeomFromText('POINT(-1.2175 54.6921)')) <= 5 * 1609.34
ORDER BY distance;

3. 调整JOIN逻辑减少数据量 #

原查询用RIGHT JOIN会优先扫描epc全表，如果只需要两边都存在的关联数据，改成INNER JOIN更高效；或者先用CTE筛选出范围内的房产，再关联EPC数据：

WITH nearby_properties AS (
  -- 先筛选出半径内的房产，缩小关联范围
  SELECT * FROM property
  WHERE ST_Distance_Sphere(location, ST_GeomFromText('POINT(-1.2175 54.6921)')) <= 5 * 1609.34
)
SELECT 
  np.paon, np.saon, np.street, np.postcode,
  np.lastSalePrice, np.lastTransferDate,
  epc.ADDRESS1, epc.POSTCODE, epc.TOTAL_FLOOR_AREA,
  ST_Distance_Sphere(np.location, ST_GeomFromText('POINT(-1.2175 54.6921)')) / 1609.34 AS distance
FROM nearby_properties np
JOIN epc 
  ON np.full_address = epc.ADDRESS1
  AND np.postcode = epc.POSTCODE
ORDER BY distance;

额外优化小技巧 #

• 给epc.POSTCODE单独加索引：CREATE INDEX idx_epc_postcode ON epc(POSTCODE);，加速邮编匹配；
• 只查询需要的字段，避免SELECT *，减少数据传输开销；
• 定期更新表统计信息：ANALYZE TABLE property, epc;，让查询优化器选择更优的执行计划。

内容的提问来源于stack exchange，提问作者Mike