我有以下MySQL查询:
I have the following MySQL query:
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 离我指定的坐标最近的表。
This will get me the zip code in my zip_codes table that is nearest to the coordinates I have specified.
然而,这运行得很慢!大约1秒钟。所有类似的查询也会持续1秒左右。我想知道我是否可以优化我的表结构或查询来改善查询时间。
However, this is running pretty slow! Around 1 second. All similar queries run around 1 second as well. I am wondering if I can optimize either my table structure or the query to improve the query time.
这是我的 zip_codes的架构 table:
This is the schema of my zip_codes table:
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 到DECIMAL并且查询现在实际上需要更长时间,令人惊讶!
UPDATE I changed the field type for lat and lng to DECIMAL and the queries are actually taking longer now, surprisingly!
推荐答案好的,所以我要警告你,这绝对不是一个完美的解决方案,并且有以下缺点:
ok, so I have to warn you that this is definitely not a perfect solution and has the following drawbacks:
它不适用于美国的所有点。例如,如果您在阿拉斯加的某处选择一个点,距离表格中的每个邮政编码中心不超过50公里,它将不返回任何内容
it does not work for all points in the US. For example, if you select a point somewhere in Alaska, further than 50 km from every of the ZIP code centers in the table, it will return nothing
它需要MyISAM存储引擎
it requires MyISAM storage engine
包含硬编码值(请参阅第1点~50 km)。它不完全是50公里,与经度不同。
in includes hardcoded values (see ~50 km in the 1st point). It's not exactly 50km and varies from the longitude.
先决条件:
鉴于您发送的转储,您应该启动以下查询:
Given the dump you sent, you should launch the following queries:
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秒,这要好得多。
on my machine it takes about 0.011 seconds, which is much better.
但是再次看到我在更新声明附近的评论,你应该考虑两件事:
But again, see my comment above near the update statement, you should consider two things:
更多推荐
优化地理距离相关的MySQL查询
发布评论