姜承尧 破产码农
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即可，其表结构如下所示：
CREATETABLE`nodes`(
`id`bigint( 20) DEFAULTNULL,
`geom`geometry NOTNULL,
`user`varchar( 50) DEFAULTNULL,
`version`int( 11) DEFAULTNULL,
`timestamp`varchar( 20) DEFAULTNULL,
`uid`int( 11) DEFAULTNULL,
`changeset`int( 11) DEFAULTNULL,
UNIQUEKEY`i_nodeids`( `id`),SPATIAL KEY`i_geomidx`( `geom`)) ENGINE= InnoDBDEFAULTCHARSET=utf8mb4
可以看到列geom就是存放对应的地理空间信息，而且这时表nodes的存储引擎已经是InnoDB，并且i_geomidx索引是SPATIAL KEY。接着执行最常见的LBS需求：查找当前用户周围5公里的餐馆信息。比如查询杭州西湖音乐喷泉附近5公里的坐标信息，并根据距离进行排序。在MySQL 5.7中可以通过下面的SQL执行：
SELECTid, ST_Distance_Sphere(Point( 120.167673, 30.259498), geom) asdistance, ST_AsText(geom)
FROMnodes
WHEREST_Contains( ST_MakeEnvelope( Point(( 120.167673+( 5/ 111)), ( 30.259498+( 5/ 111))), Point(( 120.167673-( 5/ 111)), ( 30.259498-( 5/ 111)))), geom )
ORDERBYdistance LIMIT10;
函数ST_Distance_Sphere用于计算西湖音乐喷泉与附近建筑的球面距离，5.7直接可直接用过此函数计算得到，而无需通过自定义函数的方式来实现。函数ST_MakeEnvelope用于计算当前坐标5公里范围内的矩形，然后通过函数ST_Contains进行计算。而这时的执行计划可以看到已使用空间索引i_geomidx：
(root@localhost) [china]> EXPLAINSELECTid, -> ST_Distance_Sphere(Point( 120.167673, 30.259498), geom) asdistance, ST_AsText(geom) -> FROMnodes -> WHEREST_Contains( ST_MakeEnvelope( -> Point(( 120.167673+( 5/ 111)), ( 30.259498+( 5/ 111))), -> Point(( 120.167673-( 5/ 111)), ( 30.259498-( 5/ 111))) -> ), geom ) -> ORDERBYdistance LIMIT30G************** 1.row***********
id: 1select_type: SIMPLE
table: nodes
partitions: NULLtype: range
possible_keys: i_geomidx
key: i_geomidx key_len: 34ref: NULLrows: 300filtered: 100.00Extra: Usingwhere; Using filesort1 row in set, 1warning( 0.01sec)
R树索引虽好，但是计算量依然非常巨大。在4核8G的云主机测试场景下，CPU使用率已到220%，但却只有100 QPS（主键查询该主机规格可达1W QPS）。
MySQL 5.7 GeoHash
GeoHash的原理不在这里展开，感兴趣的同学可以自行去看下。MySQL 5.7原生提供了ST_Geohash函数，可将地理空间坐标转化为Geohash格式。结合虚拟列与函数索引，那么对与上面的问题可以通过下面的方式来解决。首先创建函数索引i_geohash_idx：
ALTERTABLEnodes ADDCOLUMNgeohash VARCHAR( 128) AS(st_geohash(geom, 6)) VIRTUAL;
ALTERTABLEADDINDEXi_geohash_idx(geohash);
接着执行通过geohash的九宫格特性来计算周围的距离：
SELECTid, ST_Distance_Sphere(Point( 120.167673, 30.259498), geom) asdistance, ST_AsText(geom)
FROMnodes
WHEREgeohash IN( 'wtmknk', 'wtmkn6', 'wtmkne', 'wtmkn5', 'wtmknh', 'wtmkns', 'wtmknd', 'wtmkn4', 'wtmkn7')
ORDERBYdistance LIMIT10;
通过上述SQL语句再进行性能测试发现性能可稳定在1200 QPS，这时CPU使用率已达395%。较之R树查询方式能有12倍的性能提升，并且相信随着云主机规格提升性能上还能有进一步的提升。
后续思考问题与讨论
通过上面的测试可以发现在MySQL 5.7下通过Geohash函数+函数索引功能能较好的满足LBS应用的性能需求。但是这里还有一些问题可作为后续思考：
导入数据时如何导入geometry类型的数据？
MongoDB vs MySQL vs PostGIS的单实例性能测试？
目前MySQL 5.7 GIS还存在的缺点是什么？
如果做分布式数据库架构设计，均衡字段该怎么选择？
我会将本次测试的脚本与OpenStreetMap导入程序上传到IMG QQ群，感兴趣的同学可以加入IMG QQ群，也欢迎同学们继续在群众讨论。
长期坚持原创真的很不容易，多次想放弃。坚持是一种信仰，专注是一种态度！点赞是对作者最好的褒奖哟。
BTW：想要加入IMG微信群请先加我个人微信号再获邀请：82946772
(来源：科技讯)
京公网安备 110105001605号