优化地理距离相关的MySQL查询(Optimizing a Geo-Distance Related

我有以下MySQL查询：

SELECT 
a.*, 
( 3959 * acos( cos( radians('47.3909') ) * cos( radians( a.lat ) ) * cos( radians( a.lng ) - radians('-122.2637') ) + sin( radians('47.3909') ) * sin( radians( a.lat ) ) ) ) AS distance 
FROM zip_codes AS a 
ORDER BY distance ASC 
LIMIT 1;

这将让我在我的邮政编码zip_codes表，它是最接近我指定的坐标。

然而，这种运行太慢了！ 1秒左右。所有类似的查询1秒左右上运行。我想知道如果我可以优化我的任何表结构或改善查询时间查询。

这是我的架构zip_codes表：

CREATE TABLE `zip_codes` (
  `zip` varchar(10) COLLATE utf8_unicode_ci NOT NULL,
  `city` varchar(64) CHARACTER SET utf8 DEFAULT NULL,
  `state` char(2) CHARACTER SET utf8 DEFAULT NULL,
  `type` char(1) CHARACTER SET utf8 DEFAULT NULL,
  `timezone` int(11) DEFAULT NULL,
  `lat` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
  `lng` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
  `country` varchar(2) COLLATE utf8_unicode_ci DEFAULT '',
  PRIMARY KEY (`zip`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ROW_FORMAT=COMPACT;

UPDATE我改变字段类型的lat和lng ，以十进制和查询，现在居然需要更长的时间，令人惊讶！

好了，所以我要提醒你，这绝对不是一个完美的解决方案，并具有以下缺点：

它并不适用于美国所有点工作。例如，如果你从每一个表中的邮政编码中心选择一个点的地方在阿拉斯加，进一步超过50公里，它会返回任何结果
它需要MyISAM存储引擎
在包括硬编码值（见〜于第一点50公里）。这不正是50公里，从经度变化。

先决条件：

给你发送转储，应启动以下查询：

ALTER TABLE `zip_codes` ENGINE=MYISAM; -- changing your storage engine to MyISAM. It supports spatial indexes in MySQL
ALTER TABLE `zip_codes` ADD `pt` POINT NOT NULL; -- adding POINT() spatial datatype for zip cetner. Eventually, you may remove the old lat/lng decimal columns
ALTER TABLE `zip_codes` ADD `region` POLYGON NOT NULL; -- adding a rectangle over the center of the zip code. See below, this is something to utilize spatial index later in ST_Intersects function

// update the new columns with respective values
UPDATE `zip_codes` SET `pt` = POINT(lat,lng);
UPDATE `zip_codes` SET `region` = GEOMFROMTEXT(CONCAT('POLYGON((',lat-0.5,' ',lng-0.5,', ',lat+0.5,' ',lng-0.5,', ',lat+0.5,' ',lng+0.5,', ',lat-0.5,' ',lng+0.5,', ',lat-0.5,' ',lng-0.5,'))')); -- 0.5 is 0.5 degrees hardcode. There is a better approach and it's better to write a MySQL function that will increase the MBR with certain step until there is intersection (see my point #1 above, this is the best solution)

// create indexes on the newly created columns
ALTER TABLE `zip_codes` ADD SPATIAL INDEX(`region`);
ALTER TABLE `zip_codes` ADD SPATIAL INDEX(`pt`);

新查询

SELECT SQL_NO_CACHE zip,ST_Distance(`pt`,POINT('47.3909','-122.2637')) AS dst
FROM `zip_codes`
WHERE ST_Intersects(POINT('47.3909','-122.2637'),`region`)
ORDER BY `dst`
LIMIT 1;

我的机器上大约需要0.011秒，这是好多了。

但同样，见上附近的更新语句我的意见，你应该考虑两件事情：