需要帮助优化MySQL的经纬度/地理搜索 [英] Need help optimizing a lat/Lon geo search for mysql

问题描述

我有一个mysql（5.0.22）myisam表，里面大概有300k条记录，我想在五英里范围内做一个纬度/经度的搜索。

我有一个覆盖纬度/经度场的指数，当我刚刚选择纬度/经度时，它的速度很快（毫秒响应）。但是，当我选择表格中的其他字段可以放慢到5-8秒。

我使用myisam来利用全文搜索。其他索引表现良好（例如select * from Listing where slug ='xxxxx'）。

如何优化我的查询，表或索引以加快速度？ / p>

我的模式是：

  CREATE TABLE`Listing`（
`id` int（10）unsigned NOT NULL auto_increment，
`name` varchar（125）collat​​e utf8_unicode_ci default NULL，$ b $`phone` varchar（18）collat​​e utf8_unicode_ci default NULL，
 varchar（18）collat​​e utf8_unicode_ci默认为NULL，
`email` varchar（55）collat​​e utf8_unicode_ci默认为NULL，$ b $``photourl` varchar（55）collat​​e utf8_unicode_ci default NULL，
`thumburl varchar（5）collat​​e utf8_unicode_ci default NULL，
`website` varchar（85）collat​​e utf8_unicode_ci default NULL，
`categoryid` int（10）unsigned default NULL，
`addressid` int 10）unsigned default NULL，
`deleted` tinyint（1）default NULL，
`status` int（10）unsigned default'2'，
`parentid` int（10）unsigned defau （10）unsigned default NULL，
`listinginfoid` int（10）unsigned default NULL，
`createuserid` int（10）unsigned default NULL，
`createdate` datetime默认NULL，
`lasteditdate`时间戳NOT NULL默认CURRENT_TIMESTAMP更新CURRENT_TIMESTAMP，
`lastedituserid` int（10）unsigned default NULL，
`slug` varchar（155 ）collat​​e utf8_unicode_ci default NULL，
`aclid` int（10）unsigned default NULL，
`alt_address` varchar（80）collat​​e utf8_unicode_ci default NULL，
`alt_website` varchar（80）collat​​e utf8_unicode_ci默认NULL，
``lat'十进制（10,7）默认NULL，
`lon`十进制（10,7）默认NULL，
`city` varchar（80）collat​​e utf8_unicode_ci default NULL ，
`state` varchar（10）collat​​e utf8_unicode_ci default NULL，
 PRIMARY KEY（`id`），
 KEY`idx_fetch`使用BTREE（`slug`，`deleted`），
 KEY`idx_loc`（`state`，`city`），
 KEY`idx_org`（`organizationid`，`status`，`` （'status`，`lat`，`lon`），
 FULLTEXT KEY`idx_name`（`name`）
）ENGINE = MyISAM DEFAULT CHARSET = utf8 COLLATE = utf8_unicode_ci ROW_FORMAT = DYNAMIC; 
  

我的查询是：

 FROM Listing 
 WHERE 
 Listing.status ='2'
 AND（Listing.lon between -122.10913433498 and -121.96456366502）
 AND（在37.296909665016和37.441480334984之间的清单.lat）
 HAVING rawgeosearchdistance< 5 
 ORDER BY rawgeosearchdistance ASC; 
  

没有geosarch的解释计划：

 + ---- + ------------- + ------------ + ------- + ------- ---------- ----------------- + + --------- + ------ + ---- -  + ------------- + 
 | id | select_type |表| |键入| possible_keys |键| key_len | ref |行|额外| 
 + ---- + ------------- + ------------ + ------- + ----- ------------ + ----------------- + --------- + ------ +  - ---- + ------------- + 
 | 1 | SIMPLE |列表|范围| idx_geo_latlon | idx_geo_latlon | 19 | NULL | 453 |使用where | 
 + ---- + ------------- + ------------ + ------- + ----- ------------ + ----------------- + --------- + ------ +  - ---- + ------------- + 
 

使用geosearch解释计划：

 
 + ---- + ------------- + ------------ + ------- + ----------------- + ----------------- + ----- ---- + ------ + ------ + ----------------------------- + 
 | id | select_type |表| |键入| possible_keys |键| key_len | ref |行|额外| 
 + ---- + ------------- + ------------ + ------- + ----- ------------ + ----------------- + --------- + ------ +  - ---- + ----------------------------- + 
 | 1 | SIMPLE |列表|范围| idx_geo_latlon | idx_geo_latlon | 19 | NULL | 453 |在哪里使用;使用filesort | 
 + ---- + ------------- + ------------ + ------- + ----- ------------ + ----------------- + --------- + ------ +  - ---- + ----------------------------- + 
 

以下是覆盖指数的解释计划。按正确的顺序排列这些列有很大的不同：

 
 + ---- + ---------- --- + -------- + ------- + -------- + ------------- -  + --------- + ------ + -------- + --------------------- --------------------- + 
 | id | select_type |表| |键入| possible_keys |键| key_len | ref |行|额外| 
 + ---- + ------------- + -------- + ------- + --------- ------ + --------------- + --------- + ------ + -------- +  - ----------------------------------------- + 
 | 1 | SIMPLE |列表|范围| idx_geo_cover | idx_geo_cover | 12 | NULL | 453 |在哪里使用;使用索引;使用filesort | 
 + ---- + ------------- + -------- + ------- + --------- ------ + --------------- + --------- + ------ + -------- +  - ----------------------------------------- + 
 

谢谢！

解决方案

您可能使用'覆盖索引'在你的纬度/经度只查询。当查询使用的索引包含您正在选择的数据时，会发生覆盖索引。 MySQL只需要访问索引而不是数据行。 查看更多信息。这可以解释为什么纬度/经度查询速度如此之快。我怀疑计算和返回的行数太多会减慢查询的长度。 （加上任何必须为having子句创建的临时表）。

I have a mysql (5.0.22) myisam table with roughly 300k records in it and I want to do a lat/lon distance search within a five mile radius.

I have an index that covers the lat/lon fields and is fast (milisecond response) when I just select for lat/lon. But when I select for additional fields in the table is slows down horribly to 5-8 seconds.

I'm using myisam to take advantage of fulltext search. The other indexes perform well (e.g. select * from Listing where slug = 'xxxxx').

How can I optimize my query, table or index to speed things up?

My schema is:

CREATE TABLE  `Listing` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `name` varchar(125) collate utf8_unicode_ci default NULL,
  `phone` varchar(18) collate utf8_unicode_ci default NULL,
  `fax` varchar(18) collate utf8_unicode_ci default NULL,
  `email` varchar(55) collate utf8_unicode_ci default NULL,
  `photourl` varchar(55) collate utf8_unicode_ci default NULL,
  `thumburl` varchar(5) collate utf8_unicode_ci default NULL,
  `website` varchar(85) collate utf8_unicode_ci default NULL,
  `categoryid` int(10) unsigned default NULL,
  `addressid` int(10) unsigned default NULL,
  `deleted` tinyint(1) default NULL,
  `status` int(10) unsigned default '2',
  `parentid` int(10) unsigned default NULL,
  `organizationid` int(10) unsigned default NULL,
  `listinginfoid` int(10) unsigned default NULL,
  `createuserid` int(10) unsigned default NULL,
  `createdate` datetime default NULL,
  `lasteditdate` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  `lastedituserid` int(10) unsigned default NULL,
  `slug` varchar(155) collate utf8_unicode_ci default NULL,
  `aclid` int(10) unsigned default NULL,
  `alt_address` varchar(80) collate utf8_unicode_ci default NULL,
  `alt_website` varchar(80) collate utf8_unicode_ci default NULL,
  `lat` decimal(10,7) default NULL,
  `lon` decimal(10,7) default NULL,
  `city` varchar(80) collate utf8_unicode_ci default NULL,
  `state` varchar(10) collate utf8_unicode_ci default NULL,
  PRIMARY KEY  (`id`),
  KEY `idx_fetch` USING BTREE (`slug`,`deleted`),
  KEY `idx_loc` (`state`,`city`),
  KEY `idx_org` (`organizationid`,`status`,`deleted`),
  KEY `idx_geo_latlon` USING BTREE (`status`,`lat`,`lon`),
  FULLTEXT KEY `idx_name` (`name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ROW_FORMAT=DYNAMIC;

My query is:

SELECT Listing.name, Listing.categoryid, Listing.lat, Listing.lon
, 3956 * 2 * ASIN(SQRT( POWER(SIN((Listing.lat - 37.369195) * pi()/180 / 2), 2) + COS(Listing.lat * pi()/180) * COS(37.369195 * pi()/180) * POWER(SIN((Listing.lon --122.036849) * pi()/180 / 2), 2) )) rawgeosearchdistance
FROM Listing
WHERE
    Listing.status = '2'
    AND ( Listing.lon between -122.10913433498 and -121.96456366502 )
    AND ( Listing.lat between 37.296909665016 and 37.441480334984)
HAVING rawgeosearchdistance < 5
ORDER BY rawgeosearchdistance ASC;

Explain plan without geosearch:

    +----+-------------+------------+-------+-----------------+-----------------+---------+------+------+-------------+
    | id | select_type | table      | type  | possible_keys   | key             | key_len |ref | rows | Extra       |
    +----+-------------+------------+-------+-----------------+-----------------+---------+------+------+-------------+
    |  1 | SIMPLE      | Listing    | range | idx_geo_latlon  | idx_geo_latlon  | 19      | NULL |  453 | Using where |
    +----+-------------+------------+-------+-----------------+-----------------+---------+------+------+-------------+

Explain plan with geosearch:

+----+-------------+------------+-------+-----------------+-----------------+---------+------+------+-----------------------------+
| id | select_type | table      | type  | possible_keys   | key             | key_len | ref  | rows | Extra                       |
+----+-------------+------------+-------+-----------------+-----------------+---------+------+------+-----------------------------+
|  1 | SIMPLE      | Listing    | range | idx_geo_latlon  | idx_geo_latlon  | 19      | NULL |  453 | Using where; Using filesort |
+----+-------------+------------+-------+-----------------+-----------------+---------+------+------+-----------------------------+

Here's the explain plan with the covering index. Having the columns in the correct order made a big difference:

+----+-------------+--------+-------+---------------+---------------+---------+------+--------+------------------------------------------+
| id | select_type | table  | type  | possible_keys | key           | key_len | ref  | rows   | Extra                                    |
+----+-------------+--------+-------+---------------+---------------+---------+------+--------+------------------------------------------+
|  1 | SIMPLE      | Listing | range | idx_geo_cover | idx_geo_cover | 12      | NULL | 453     | Using where; Using index; Using filesort |
+----+-------------+--------+-------+---------------+---------------+---------+------+--------+------------------------------------------+

Thank you!

解决方案

You are probably using a 'covering index' in your lat/lon only query. A covering index occurs when the index used by the query contains the data that you are selecting for. MySQL only needs to visit the index and never the data rows. See this for more info. That would explain why the lat/lon query is so fast.

I suspect that the calculations and the sheer number of rows returned, slows down the longer query. (plus any temp table that has to be created for the having clause).

这篇关于需要帮助优化MySQL的经纬度/地理搜索的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！