首页 ›存档› 技术 › 查看内容

MySQL 5.7 GIS特性与性能测试

2018-3-30 13:00 |来自: 互联网 289 0

摘要: 姜承尧破产码农 IT界最会讲故事的男同学 MySQL GIS功能与历史 GIS(Geographic Information System)是构建互联网LBS（Location Based Service）服务的核心技术。小伙伴们所熟知的大众点评、美团外卖、微信、陌陌这 ...

姜承尧破产码农

IT界最会讲故事的男同学

MySQL GIS功能与历史

GIS(Geographic Information System)是构建互联网LBS（Location Based Service）服务的核心技术。小伙伴们所熟知的大众点评、美团外卖、微信、陌陌这类应用都广泛地在使用GIS技术，比如用户查找附近的餐厅，附近商家，附近的好友等。简单来说，用户给出自己的地理信息，通常是经度与维度，应用返回附近的商家或用户信息，在此基础上应用再给出一些增值服务。

MySQL很早就支持空间数据类型，业界用的却非常少。印象中只有美团直接使用了MySQL作为LBS服务数据库。这其中最重要的原因是：

MySQL 5.7版本之前只有MyISAM引擎支持空间数据；
MySQL 5.7版本之前只有MyISAM支持R树查询；
MySQL 5.7版本之前地理空间类型性能比较一般；
MySQL 5.7版本之前地理空间函数支持度有限；
MySQL 5.7版本之前不支持GeoHash以及GeoJson；

由于上述种种原因，业界的LBS应用大多基于MongoDB数据库，因为MongoDB内置的geoindex非常好用，外加分片特性非常适合于LBS这样的应用。对于事务要求较高的应用场景，也有公司使用PostGIS。

MySQL 5.7 GIS改进

针对社区用户不断要求改进GIS的呼声（其实大多数用户还是希望生产中的数据库种类能越精简越好），MySQL官方团队对GIS进行了大幅的重构与优化，所有的这些改进都已整合到MySQL 5.7版本。这些改进包括但不限于：

通过Boost Geometry库重构之前的地理空间数据的代码实现；
增加很多通用的GIS函数，比如球面举例的计算函数ST_Distance_Sphere等；
InnoDB存储引擎原生支持地理空间数据类型
InnoDB存储引擎新增R树索引支持地理空间查询

MySQL 5.7 GIS的使用与性能

很多同学反映在前期调研或测试过程中会发现没有地图数据可供测试使用。其实，国外有一个OpenStreeMap的项目，用于收集各个国家的地图信息，官网地址：http://www.openstreetmap.org

小伙伴们可以首先可以在上面下载地图包，然后将数据导入到MySQL 5.7数据库进行测试。中国地图由于政策原因，并不是非常全，但是导入到数据库后核心的地图表nodes也有超过7G的大小，用来测试是足够了。如果嫌太大还可以下载每个城市的地图。

导入完之后就可进行测试了，现在用户只要关心表nodes即可，其表结构如下所示：

CREATE TABLE `nodes` ( 
`id` bigint(20) DEFAULT NULL, 
`geom` geometry NOT NULL, 
`user` varchar(50) DEFAULT NULL, 
`version` int(11) DEFAULT NULL, 
`timestamp` varchar(20) DEFAULT NULL, 
`uid` int(11) DEFAULT NULL, 
`changeset` int(11) DEFAULT NULL, 
UNIQUE KEY `i_nodeids` (`id`),
SPATIAL KEY `i_geomidx` (`geom`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

可以看到列geom就是存放对应的地理空间信息，而且这时表nodes的存储引擎已经是InnoDB，并且i_geomidx索引是SPATIAL KEY。接着执行最常见的LBS需求：查找当前用户周围5公里的餐馆信息。比如查询杭州西湖音乐喷泉附近5公里的坐标信息，并根据距离进行排序。在MySQL 5.7中可以通过下面的SQL执行：

SELECT id, 
  ST_Distance_Sphere(Point(120.167673,30.259498), geom) as distance, ST_AsText(geom)
FROM nodes
WHERE ST_Contains( ST_MakeEnvelope(
  Point((120.167673 (5/111)), (30.259498 (5/111))),
  Point((120.167673-(5/111)), (30.259498-(5/111)))), geom )
ORDER BY distance LIMIT 10;

函数ST_Distance_Sphere用于计算西湖音乐喷泉与附近建筑的球面距离，5.7直接可直接用过此函数计算得到，而无需通过自定义函数的方式来实现。函数ST_MakeEnvelope用于计算当前坐标5公里范围内的矩形，然后通过函数ST_Contains进行计算。而这时的执行计划可以看到已使用空间索引i_geomidx：

(root@localhost) [china]

声明：文章版权归原作者所有部分文章转自互联网如有侵权请联系 [邮箱地址] 删除

路过

雷人

握手

鲜花

鸡蛋

收藏分享邀请

上一篇：MySQL时间序列存储引擎的设计与实现下一篇：MySQL参数对比浅析(r11笔记第97天)

MySQL 5.7 GIS特性与性能测试

MySQL GIS功能与历史

MySQL 5.7 GIS改进

MySQL 5.7 GIS的使用与性能

相关分类