Mysql空间索引

2019-12-03 本文已影响0人 ZackJiang

Mysql 空间索引

本文主要根据mysql 8.0的文档翻译总结，如果使用的是mysql 5.7版本，可能会有些许差异

在涉及LBS的服务开发过程中，经常需要存储地理空间的位置并进行一定计算（附近商家等需求），本文主要介绍mysql对于LBS的支持。

Mysql的空间扩展主要提供一下几个方面的功能：

表示空间数值的数据类型
操作空间数值的函数
空间索引，用于提供访问空间列的速度

其中前两点对InnoDB，MyISAM，NDB，ARCHIVE等mysql存储引擎都支持，第三点只有对InnoDB和MyISAM的支持，由于InnoDB的支持行锁以及事务的特性，现在基本上已经是默认存储引擎了，所以本文以下内容都默认使用InnoDB。

创建空间列以及空间索引的语句如下：

CREATE TABLE geom (g GEOMETRY NOT NULL SRID 4326, SPATIAL INDEX(g));

Mysql空间数据类型

Mysql的空间数据类型与OpenGIS的数据类型相对应。

单一几何值的空间数据类型：
- GEOMETRY
- POINT
- LINESTRING
- POLYGON
其中GEOMETRY可以存储任意类型的集合类型，POINT LINESTRING POLYGON则限制了集合类型
空间集合数据类型：
- MULTIPOINT
- MULTILINESTRING
- MULTIPOLYGON
- GEOMETRYCOLLECTION

空间数据类型的表示形式

Mysql的空间数据有不同表示格式，其中咱能看懂的也就第一种

Well-known Text Format （WKT）形如 Point(1 1)
Well-know Binary Format (WKB) 二进制表示，贴下帧结构，自己感受下

Component Size Value

Byte order 1 byte 01

WKB type 4 bytes 01000000

X coordinate 8 bytes 000000000000F03F

Y coordinate 8 bytes 000000000000F0BF
Mysql内部几何存储结构

就是在WKB的前面加上4个字节来表示SRID，就变成了mysql存储的数据结构

Component	Size	Value
Byte order	1 byte	`01`
WKB type	4 bytes	`01000000`
X coordinate	8 bytes	`000000000000F03F`
Y coordinate	8 bytes	`000000000000F0BF`

什么是SRID

因为上文提到了SRID，这里说下什么是SRID，SR是指Spatial Reference，也就是我们常说的空间参考系，mysql支持卡迪尔坐标系和地理坐标系，其中地理坐标系又有好多种，下面说几种常用的空间参考系

SRID=0表示一个无限的卡迪尔坐标系平面，且坐标轴上无单位
SRID=4326表示GPS坐标系
SRID=3857是web地图投影，就是你在谷歌地图上看到的坐标系

ALTER TABLE geom ADD position POINT SRID 4326

Mysql的所有空间坐标系都存在表mysql.st_spatial_reference_system中，这个表是隐藏的，看不见的，但是你可以通过infomation_shcema.st_spatial_reference_system中查看参考系的信息，这个表就是mysql.st_spatial_reference_system的一个视图的实现。

mysql> select * from information_schema.st_spatial_reference_systems where srs_id=4326;
+----------+--------+--------------+--------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------+
| SRS_NAME | SRS_ID | ORGANIZATION | ORGANIZATION_COORDSYS_ID | DEFINITION                                                                                                                                                                                                                                                                                                              | DESCRIPTION |
+----------+--------+--------------+--------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------+
| WGS 84   |   4326 | EPSG         |                     4326 | GEOGCS["WGS 84",DATUM["World Geodetic System 1984",SPHEROID["WGS 84",6378137,298.257223563,AUTHORITY["EPSG","7030"]],AUTHORITY["EPSG","6326"]],PRIMEM["Greenwich",0,AUTHORITY["EPSG","8901"]],UNIT["degree",0.017453292519943278,AUTHORITY["EPSG","9122"]],AXIS["Lat",NORTH],AXIS["Lon",EAST],AUTHORITY["EPSG","4326"]] | NULL        |
+----------+--------+--------------+--------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------+
1 row in set (0.00 sec)

SRS的解析是在GIS函数调用后才会去懒加载，并把解析的地理位置定义缓存到数据字典中，以后每次需要SRS的信息时不会重复解析

操作空间数据

插入数据

INSERT INTO geom VALUES (ST_GeomFromText('POINT(1 1)'))

查询内部数据结构
```
SELECT g FROM geom
```
查询WKT
```
SELECT ST_AsText(g) FROM geom
```
查询WKB
```
SELECT ST_AsBinary FROM geom
```

此外常用的还有ST_X，ST_Y，有需要可以去从手册上查

空间索引

mysql的空间索引的数据结构是R树，R树实际上就是多维的B树，B树的数据结构在我的另一篇博客中有介绍，这里就不展开了，说几点在应用的时候需要注意的。

建立空间索引需要对应列NOT NULL且有具体的SRID，没有SRID属性的列称为非SRID约束，会接收任何SRID坐标系的值，但是优化器不能再这样没有SRID的列上使用空间索引
空间索引只能建立在空间数据类型上
如果在不支持空间索引的存储引擎中对非空间列建立索引，则会建立B树索引，可以用于精确查找空间位置，但是不能范围查找（把空间数据列当成字符串去建立索引）

最后转一篇博文 https://visonforcoding.github.io/di-li-wei-zhi-geochu-li-zhi-mysql-geo-suo-yin.html