欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

MySQL Geometry扩展在地理位置计算中的效率优势

程序员文章站 2023-11-29 20:15:46
由于在Geometry中,有相关自带函数和SPATIAL INDEX的性能优化,可以让某些位置计算的效率提升。以下是几种计算方法的效果对比。 1. 数据准备 首先创建一个数据表,这是一个店铺数据表,结构如下: 创建语句: CREATE TABLE `store_geometry` ( `id` in ......

由于在geometry中,有相关自带函数和spatial index的性能优化,可以让某些位置计算的效率提升。以下是几种计算方法的效果对比。

1. 数据准备

  首先创建一个数据表,这是一个店铺数据表,结构如下:

  MySQL Geometry扩展在地理位置计算中的效率优势

  创建语句:

  create table `store_geometry` (
    `id` int(11) not null,
    `name` varchar(64) not null,
    `latitude` double default null,
    `longitude` double default null,
    `city` varchar(16) default null,
    `district` varchar(16) default null,
    `address` varchar(64) default null,
    `geohash_8` varchar(16) default null,
    `geometry` geometry default null,
    primary key (`id`)
  ) engine=myisam default charset=utf8 row_format=dynamic

  然后插入数据,包含id,name,latitude,longitude,city,district,address这些字段的数值。

  原始字段数值插入后,通过geometry函数计算出geometry字段的数值,并更新:

  update `store_geometry` set geometry=geomfromtext(concat('point(',longitude,' ',latitude,')'))

  到此,数据准备工作完成。

2. 对比实例:筛选出在一定矩形范围内的店铺

  对比时,表内共有100,000条左右的店铺数据。

  矩形范围:

    max_x=121.474243

    min_x=121.470724

    max_y=31.234504

    min_y=31.230229

2.1 方法一:使用经度和纬度字段判断是否在此区间内

  我们看看对latitude,longitude2个字段做索引前后的性能对比

  先在索引前查询:

  set @max_x=121.474243;
  set @min_x=121.470724;
  set @max_y=31.234504;
  set @min_y=31.230229;
  select * from `store_geometry` where longitude between @min_x and @max_x and latitude between @min_y and @max_y;

  查询结果有70条,耗时0.473秒

  MySQL Geometry扩展在地理位置计算中的效率优势

  MySQL Geometry扩展在地理位置计算中的效率优势

  然后索引后使用相同语句查询,速度有明显加快:

  MySQL Geometry扩展在地理位置计算中的效率优势

2.2 方法二:使用geometry字段数据和相关几何计算函数判断是否在此区间内

  同样的我们先不对geometry字段创建索引

  set @mbr=geomfromtext(concat('polygon','((',@min_x,' ',@min_y,',',@max_x,' ',@min_y,',',@max_x,' ',@max_y,',',@min_x,' ',@max_y,',',@min_x,' ',@min_y,'))'));
  select * from `store_geometry` where st_contains(@mbr, geometry);

  查询结果相同,耗时如下:

  MySQL Geometry扩展在地理位置计算中的效率优势

  然后对geometry创建索引,这里注意不要用mysql客户端工具在界面上创建,因为只能创建普通索引,没有效果。

  create spatial index i_geometry on `store_geometry`(geometry);

  MySQL Geometry扩展在地理位置计算中的效率优势

  然后用相同语句查询,结果如下:

  MySQL Geometry扩展在地理位置计算中的效率优势

3.结论

  实验结果很明显,geometry扩展在进行位置计算时具有性能上的明显优势。

方法 索引 查询耗时(秒)
使用经度和纬度字段 no index 0.473
index 0.015
使用geometry字段 no index 0.092
index 0.008