首页
学习
活动
专区
圈层
工具
发布
首页
学习
活动
专区
圈层
工具
社区首页 >问答首页 >MySQL GeoLocation查询-全表扫描

MySQL GeoLocation查询-全表扫描
EN

Stack Overflow用户
提问于 2016-09-09 14:50:57
回答 1查看 258关注 0票数 0

问题:查找给定经度/纬度的办公室列表

MySQL版本:我们目前使用的是5.5.1

表模式:

代码语言:javascript
代码运行次数:0
运行
AI代码解释
复制
     mysql> describe delivery_office;
     +-------------------------------+--------------+------+-----+---------+-------+
     | Field                         | Type         | Null | Key | Default | Extra |
     +-------------------------------+--------------+------+-----+---------+-------+
     | id                         | bigint(20)   | NO   | PRI | NULL    |       |
     | address_1                  | varchar(255) | YES  |     | NULL    |       |
     | address_2                  | varchar(255) | YES  |     | NULL    |       |
     | address_3                  | varchar(255) | YES  |     | NULL    |       |
     | address_4                  | varchar(255) | YES  |     | NULL    |       |
     | latitude                   | double       | YES  | MUL | NULL    |       |
     | longitude                  | double       | YES  |     | NULL    |       |
     | name                       | varchar(255) | YES  |     | NULL    |       |
     | postcode                   | varchar(255) | YES  |     | NULL    |       |
     | pt_loca                    | point        | NO   | MUL | NULL    |       |
     +-------------------------------+--------------+------+-----+---------+-------+

表索引:

代码语言:javascript
代码运行次数:0
运行
AI代码解释
复制
     mysql> show index from delivery_office;
     +---------------------+------------+--------------+--------------+----------------------------+-----------+-------------+----------+--------+------+------------+---------+-        --------------+
     | Table               | Non_unique | Key_name     | Seq_in_index | Column_name                | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |         Index_comment |
     +---------------------+------------+--------------+--------------+----------------------------+-----------+-------------+----------+--------+------+------------+---------+-        --------------+
     | delivery_office |          0 | PRIMARY      |            1 |  id                      | A         |        2156 |     NULL | NULL   |      | BTREE      |                 |               |
     | delivery_office |          1 | geo_index    |            1 | latitude                | A         |        1896 |     NULL | NULL   | YES  | BTREE      |                  |               |
     | delivery_office |          1 | geo_index    |            2 | longitude               | A         |        1939 |     NULL | NULL   | YES  | BTREE      |                  |               | |                 |               |
     | delivery_office |          1 | pt_loca2     |            1 | pt_loca                    | A         |        2156 |       32 | NULL   |      | SPATIAL    |               |               |
     +---------------------+------------+--------------+--------------+----------------------------+-----------+-------------+----------+--------+------+------------+---------+-        --------------+

解决方案:使用地理位置查询来查找它。

我使用了以下解决方案,但这两种解决方案都会导致全表扫描。

我的表只有2168条记录。

Fastest Way to Find Distance Between Two Lat/Long Points

代码语言:javascript
代码运行次数:0
运行
AI代码解释
复制
    mysql> explain SELECT  *
    ->     FROM   delivery_office
    ->     WHERE   MBRContains
    ->                     (
    ->                     LineString
    ->                             (
    ->                             Point (
    ->                             51.5177 + 1 / 111.1,
    ->        -0.0968 + 1 / ( 111.1 /COS(RADIANS(51.5177)))
    ->
    ->                                   ),
    ->                             Point (
    ->                              51.5177 - 1 / 111.1,
    ->               -0.0968 - 1 / ( 111.1 / COS(RADIANS(51.5177)))
    ->
    ->                                   )
    ->                             ),
    ->                     GeomFromText('POINT(51.5177 -0.0968)')
    ->                     );
    +----+-------------+---------------------+------------+------+-----   ----------+------+---------+------+------+----------+-------+
    | id | select_type | table               | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
    +----+-------------+---------------------+------------+------+---------------+------+---------+------+------+----------+-------+
    |  1 | SIMPLE      | delivery_office | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 2156 |   100.00 | NULL  |
   +----+-------------+---------------------+------------+------+------     ---------+------+---------+------+------+----------+-------+
    1 row in set, 2 warnings (0.00 sec)

https://gis.stackexchange.com/questions/31628/find-points-within-a-distance-using-mysql

代码语言:javascript
代码运行次数:0
运行
AI代码解释
复制
    mysql> explain EXTENDED SELECT (
    ->      3959 * acos (
    ->        cos ( radians(51.3191750) )
    ->        * cos( radians( latitude ) )
    ->        * cos( radians( longitude ) - radians(-0.5632660) )
    ->        + sin ( radians(51.3191750) )
    ->        * sin( radians( latitude ) )
    ->      )
    ->    ) AS distance_in_miles, delivery_office.*
    ->  FROM delivery_office
    ->  HAVING distance_in_miles < 10
    ->  ORDER BY distance_in_miles
    ->  LIMIT 0 , 30;
    +----+-------------+---------------------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+
    | id | select_type | table               | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                       |
    +----+-------------+---------------------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+
    |  1 | SIMPLE      | delivery_office | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 2156 |   100.00 | Using where; Using filesort |
    +----+-------------+---------------------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+
    1 row in set, 2 warnings (0.00 sec)

有没有一种方法可以触发查询,而不会导致全表扫描。

EN

回答 1

Stack Overflow用户

发布于 2016-09-12 11:20:27

根据@RickJames的建议,我可以使用这里提供的解决方案来优化查询:

https://www.scribd.com/presentation/2569355/Geo-Distance-Search-with-MySQL https://blog.fedecarg.com/2009/02/08/geo-proximity-search-the-haversine-equation/

以下是使用此优化的结果,行扫描从全表扫描减少到181条记录:

代码语言:javascript
代码运行次数:0
运行
AI代码解释
复制
     mysql> SELECT (
         ->      3959 * acos (
         ->        cos ( radians(51.3191750) )
         ->        * cos( radians( latitude ) )
         ->        * cos( radians( longitude ) - radians(-0.5632660) )
         ->        + sin ( radians(51.3191750) )
         ->        * sin( radians( latitude ) )
         ->      )
         ->    ) AS distance_in_miles
         ->  FROM delivery_office
         ->  where longitude between (-0.5632660 - 10/abs(cos(radians(51.3191750))*69)) and (-0.5632660 + 10/abs(cos(radians(51.3191750))*69)) and latitude between (51.3191750          - (10/69)) and (51.3191750 + (10/69))
         ->  HAVING distance_in_miles < 10
         ->  ORDER BY distance_in_miles
         ->  LIMIT 0 , 10;
     +--------------------+
     | distance_in_miles  |
     +--------------------+
     | 0.3381472408327969 |
     | 1.9060143092544148 |
     |  2.840055550183541 |
     | 3.2969844240913697 |
     |  3.356363831356166 |
     |  4.815726191392366 |
     |  5.134370232935941 |
     |  5.177268204112493 |
     |  5.528244889409913 |
     |  5.702281140258665 |
     +--------------------+
     10 rows in set (0.00 sec)

     mysql> explain EXTENDED SELECT (                                                                                                                                                                                        ->      3959 * acos (
         ->        cos ( radians(51.3191750) )
         ->        * cos( radians( latitude ) )
         ->        * cos( radians( longitude ) - radians(-0.5632660) )
         ->        + sin ( radians(51.3191750) )
         ->        * sin( radians( latitude ) )
         ->      )
         ->    ) AS distance_in_miles
         ->  FROM delivery_office
         ->  where longitude between (-0.5632660 - 10/abs(cos(radians(51.3191750))*69)) and (-0.5632660 + 10/abs(cos(radians(51.3191750))*69)) and latitude between (51.3191750          - (10/69)) and (51.3191750 + (10/69))
         ->  HAVING distance_in_miles < 10
         ->  ORDER BY distance_in_miles
         ->  LIMIT 0 , 10;
     +----+-------------+---------------------+------------+-------+---------------+-----------+---------+------+------+----------+------------------------------------------+
     | id | select_type | table               | partitions | type  | possible_keys | key       | key_len | ref  | rows | filtered | Extra                                    |
     +----+-------------+---------------------+------------+-------+---------------+-----------+---------+------+------+----------+------------------------------------------+
     |  1 | SIMPLE      | delivery_office | NULL       | range | geo_index     | geo_index | 18      | NULL |  181 |    11.11 | Using where; Using index; Using filesort |
     +----+-------------+---------------------+------------+-------+---------------+-----------+---------+------+------+----------+------------------------------------------+
     1 row in set, 2 warnings (0.00 sec)
票数 0
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接:

https://stackoverflow.com/questions/39414357

复制
相关文章
MySQL -- 全表扫描
-- db1.t有200GB mysql -h$host -P$port -u$user -p$pwd -e "select * from db1.t" > $target_file 查询数据 Inn
猿哥
2019/03/19
2.9K0
MySQL -- 全表扫描
MYSQL 查询优化之路-之DISTINCT全表扫描
背景:今天对一个20w的表做关联查询,创建各种索引,没有提高执行的效率,使用EXPLAIN检查,总是提示“Using temporary”全表扫描,这不是我想的。通过度娘,各种百度,是因为DISTINCT使用了全表扫描,现在特别记录下来。以背查验。
用户5640963
2019/07/25
4.3K1
MySQL中的全表扫描案例
这两天看到了两种可能会导致全表扫描的sql,这里给大家看一下,希望可以避免踩坑:
AsiaYe
2019/11/06
2.7K0
MySQL 全表扫描成本计算
全表扫描成本作为参照物,用于和表的其它访问方式的成本做对比。任何一种访问方式,只要成本超过了全表扫描成本,就不会被使用。
csch
2022/12/20
8970
索引 vs 全表扫描
索引是数据库的重要技术,本质是用空间换时间,或者放慢写入加速查询。通常我们会将索引和全表扫描来对比,并且一般都会觉得全表扫描很 low,真的是这样吗?
Apache IoTDB
2020/09/27
1.2K0
索引 vs 全表扫描
高水位线和全表扫描
   高水位线好比水库中储水的水位线,用于描述数据库中段的扩展方式。高水位线对全表扫描方式有着至关重要的影响。当使用delete 操作 表记录时,高水位线并不会下降,随之导致的是全表扫描的实际开销并没有任何减少。本文给出高水位线的描述,如何降低高水位线,以及高水 位线对全表扫描的影响。
Leshami
2018/08/14
5140
mysql中创建表实例全析及查询基本操作
create table cats( id int not null auto_increment, pid int not null default '0', name varchar(60) not null default '', desn text not null default '', primary key(id), index name(name, pid) )engine=InnoDB default character set=utf8;
闵开慧
2018/03/30
1.5K0
MySQL 分表查询
分表是一种数据库分割技术,用于将大表拆分成多个小表,以提高数据库的性能和可管理性。在MySQL中,可以使用多种方法进行分表,例如基于范围、哈希或列表等。下面将详细介绍MySQL如何分表以及分表后如何进行数据查询。
孟斯特
2023/10/19
1.1K0
MySQL 分表查询
MySQL单表查询
3.将取出的一条条记录进行分组group by,如果没有group by,则整体作为一组
changxin7
2019/09/10
17.9K0
mysql 查询表结构
use information_schema; select * from columns where table_name='NODES';
大数据工程师-公子
2019/03/14
9.1K0
使用索引快速全扫描(Index FFS)避免全表扫描的若干场景
2. Index FFS只能通过CBO(Index hint强制使用CBO)获得。
bisal
2022/12/01
7250
MySQL跨表查询
    跨表查询适用于两个及两个以上的表中关联信息的数据,通过联系查询到表的联系!
十月梦想
2018/10/11
9.3K0
MySQL跨表查询
MySQL单表查询
MySQL之单表查询 创建表 # 创建表 mysql> create table company.employee5( id int primary key AUTO_INCREMENT not null, name varchar(30) not null, sex enum('male','female') default 'male' not null, hire_date date not null, post varchar(50) not null,
星哥玩云
2022/08/18
6.3K0
MongoDB 定位 oplog 必须全表扫描吗?
MongoDB oplog 记录数据库的所有修改操作,除了用于主备同步;oplog 还能玩出很多花样,比如
MongoDB中文社区
2019/08/20
1.6K0
MongoDB 定位 oplog 必须全表扫描吗?
MySQL之单表查询、多表查询
having的语法格式与where一致,只不过having是在分组之后进行的过滤,即where虽然不能用聚合函数,但是having可以!
py3study
2020/01/16
22K0
Mysql避免全表update
在测试的时候忘记写where条件导致全表更新的话,可以收拾包袱走人了 下面这条语句可以开启检查,当没有加where时拦截下来 set sql_safe_updates=1; 关闭: set sql_safe_updates=0;
DH镔
2019/12/20
2.7K0
mysql-单表查询
mysql> create table employee(id int primary key auto_increment,name  varchar(20) not null,sex enum('male','female') not null default 'male',age int(3) unsigned not null default 28,hire_date date not null,post varchar(50),post_comment varchar(100),salary  double(15,2),office int,depart_id int);
py3study
2018/08/03
4.3K0
MySQL子查询,联结表
子查询:嵌套在其他查询中;执行顺序由里到外。子查询数目没有限制,如果要使用多层查询,注意写好缩进格式,不要出错。
小末快跑
2019/07/03
4.5K0
mysql分表+分页查询
我们都知道,数据量大了,都要对数据库进行分库分表。奈何一直对分表及分表查询没什么概念,这里先不讲那么多概念,先直接演示一个demo。我们直接上车,请坐稳扶好。
用户10002156
2023/08/07
4820
mysql分表+分页查询
点击加载更多

相似问题

MySql - JOIN查询的全表扫描

28

MySQL全表扫描

12

禁止MySQL对查询使用全表扫描

23

Mysql子句全表扫描

12

如何避免对MySQL查询进行全表扫描

12
添加站长 进交流群

领取专属 10元无门槛券

AI混元助手 在线答疑

扫码加入开发者社群
关注 腾讯云开发者公众号

洞察 腾讯核心技术

剖析业界实践案例

扫码关注腾讯云开发者公众号
领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档
查看详情【社区公告】 技术创作特训营有奖征文