mysql 8.x 中的 geometry 变更/缓存命中/SRID/EPSG/WKT等问题

mysql 8.x 中的 geometry 变更/缓存命中问题/SRID/EPSG

1.0 geometry 基本支持变更

前一阵，把mysql升级到了 8.0 的版本，发现几个问题，第一个问题，mysql 淘汰了一部分 geometry 查询的函数，例如 GeomFromText 替换成了 ST_GeometryFromText。于是去查询了一下网上的文章，很快了解到，5.7 到 8.0 之间，geometry 的支持是有很多更新的，特别是查询的函数方面，具体更新如下：

◦ These functions are removed in favor of the MBR names: Contains(), Disjoint(), Equals(), Intersects(), Overlaps(), Within().
◦ These functions are removed in favor of the ST_ names: Area(), AsBinary(), AsText(), AsWKB(), AsWKT(), Buffer(), Centroid(), ConvexHull(), Crosses(), Dimension(), Distance(), EndPoint(), Envelope(), ExteriorRing(), GeomCollFromText(), GeomCollFromWKB(), GeomFromText(), GeomFromWKB(), GeometryCollectionFromText(), GeometryCollectionFromWKB(), GeometryFromText(), GeometryFromWKB(), GeometryN(), GeometryType(), InteriorRingN(), IsClosed(), IsEmpty(), IsSimple(), LineFromText(), LineFromWKB(), LineStringFromText(), LineStringFromWKB(), MLineFromText(), MLineFromWKB(), MPointFromText(), MPointFromWKB(), MPolyFromText(), MPolyFromWKB(), MultiLineStringFromText(), MultiLineStringFromWKB(), MultiPointFromText(), MultiPointFromWKB(), MultiPolygonFromText(), MultiPolygonFromWKB(), NumGeometries(), NumInteriorRings(), NumPoints(), PointFromText(), PointFromWKB(), PointN(), PolyFromText(), PolyFromWKB(), PolygonFromText(), PolygonFromWKB(), SRID(), StartPoint(), Touches(), X(), Y().
◦ GLength() is removed in favor of ST_Length().

很多函数只是换个名字，提升下计算的精度，用法没什么变化，在官方的changelog 里可以看到这些变更的描述：MySQL :: MySQL 8.0 Release Notes :: Changes in MySQL 8.0.0 (2016-09-12, Development Milestone) 另外，很多新方法的计算效率比老方法要高一些（不过和索引的威力比的话基本没有太大感知，可以自己做一下无索引测试尝试一下）

不过，有意思的事情来了，我们很多人其实都有一个不太好的习惯，遇到问题之后，只解决问题本身，而不是解决问题的根源，这里我也犯了一个同样的错误，其实在 mysql 8.0 的 release note 里有一个专门的 Spatial Data Support 一栏，里面除了上述的内容，还有更多的关于地理信息存储的变更内容，不过我并没有关注，而是选择只关注了上述问题的内容，而且因为 release note 是英文，短时间内很难看明白，所以我直接选择了忽略，于是就有了后面的问题。

2.0：缓存命中问题

修改了查询函数后，程序已经不会报错了，but，当我去前端加载页面的时候，发现地理信息的查询变得特别慢，初步排除掉机器配置网络带宽等问题后，第一时间怀疑是新装的mysql默认配置可能有些什么问题，不过很快这个问题就被排除了，而且其实默认配置的影响应该也不会有好几个数量级，而更有可能的问题是一些其他原因导致索引未命中的问题，于是我们用 explain 命令查看一下。

发现的确没有命中缓存，然后我们去 5.7 的 mysql 里同样的 sql 执行后发现是可以命中缓存的，为虾米会这样呢？下一步我们如何着手解决这个问题？

第一时间想到的是搜索一下，不过搜到不少文章说这个问题的，但是文章内容基本是一知半解，和我一样，是在解决问题表象，恰好可以解决问题，但是说不清楚具体的逻辑

2.1，回到根本

所以最后还是决定把 mysql 8.0 的 release note 仔细看一遍，特别是 Spatial Data Support 这一节的内容：

* Spatial functions for import and export of Well-Known Text (WKT) values used MySQL 'GEOMETRYCOLLECTION()' nonstandard syntax rather than OpenGIS 'GEOMETRYCOLLECTION EMPTY' standard syntax. Now both syntaxes are understood for import and the standard syntax is used for export. See Functions That Create Geometry Values from WKT Values . (Bug #23632147, Bug #81964)
* The ST_X() and ST_Y() spatial functions now permit an optional second argument that specifies an X or Y coordinate value, respectively. With two arguments, the function result is the point value from the first argument with the appropriate coordinate modified. In addition, ST_X() and ST_Y() with a single argument now are stricter and produce an ER_UNEXPECTED_GEOMETRY_TYPE error rather than returning NULL if the argument is a valid geometry but not a point. For more information, see Point Property Functions . * The ST_SRID() spatial function now permits an optional second argument that specifies a SRID value. With two arguments, the function result is the geometry value from the first argument with its SRID modified according to the second argument. For more information, see General Geometry Property Functions .
* MySQL now stores information about spatial reference systems other than SRID 0, for use with spatial data. This information is stored in the st_spatial_reference_systems data dictionary table and is based on EPSG Dataset 8.7. For information about spatial reference systems, see Spatial Reference System Support . Previously, the ST_IsValid() , ST_MakeEnvelope() , and ST_Validate() functions required geometry arguments with SRID 0. They now accept geometry arguments with an SRID for a projected spatial reference system.

虽然还是挺晦涩的，我们还是大概总结一下这几段话在说什么，可能可以找到我们需要的线索。

1. 导入 wkt 数据支持 'GEOMETRYCOLLECTION EMPTY' 标准格式和 'GEOMETRYCOLLECTION()' 非标准格式，而导出则是标准格式。wkt 是一个表示 geometry 的标准格式。这里说的是空数据的表示方式。
2. ST_X() 和 ST_Y() 现在支持第二个参数，例如，原来的用法：ST_X(Point(56.7, 53.34)); 获取一个点的 x 值，现在ST_X(Point(56.7, 53.34), 10.5)；可以把第二个参数的值赋值给 Point。
3. ST_SRID() 现在 ，空间函数允许使用可选的第二个参数来指定SRID值。具有两个参数时，函数结果是第一个参数的几何值，其SRID根据第二个参数进行了修改。有关更多信息，请参见 常规几何特性函数 。
4. MySQL现在支持存储除 SRID 0 以外的空间参考系统的信息，以用于空间数据。此信息存储在st_spatial_reference_systems 数据字典表中，并且基于EPSG数据集8.7。有关空间参考系统的信息，请参见 空间参考系统支持 。现在使用某些判定方法支持传入第二个参数 SRID。

以上是 mysql 8.0 release note 中关于 Spatial Data Support 的所有描述。这里我们可以看到一个不是那么熟悉的概念:SRID，显然这不是什么新概念，但是我的确不是很熟悉这是什么，而这可能是问题的关键。事实证明也是如此，于是我用 SRID 和 mysql 搜索了一些文章对这个概念进行了一些了解。

2.2，SRID 和 EPSG 基本了解

关于 SRID 的定义，可以参考 wikipedia 中的内容（需翻墙）Spatial reference system - Wikipedia

A spatial reference system (SRS) or coordinate reference system (CRS) is a coordinate-based local, regional or global system used to locate geographical entities. A SRS commonly defines a specific map projection , as well as transformations between different SRS.
SRS are defined by the OGC ’s Simple Feature Access standard using well-known text representation of coordinate reference systems . Support has been implemented by several geographic information systems (GIS). SRS can be referred to using a SRID number, including EPSG codes . It is specified in ISO 19111:2007 Geographic information—Spatial referencing by coordinates, prepared by ISO/TC 211 , also published as OGC Abstract Specification, Topic 2: Spatial referencing by coordinate. [1]

翻译一下，SRS 或者 CRS 是一种以坐标表示地理位置的系统，SRS通常定义特定的 地图投影方法 以及不同SRS之间的 转换 。 SRS 由 OGC 使用 well-known text representation of coordinate reference systems 的 Simple Feature Access 标准所定义，已经被诸多系统所实现 SRS 可以由 SRID 来唯一引用，这是一个由 ISO 和 OGC 都发布的标准。 所以 SRID 其实是代表一种 SRS 系统，每个 SRS 坐标系统都有自己的 SRID。通常，坐标系统都是使用 EPSG code 表示的，例如 EPSG:4326 指的就是 WGS 84坐标，也就是大家通常所说的84坐标，GPS 吐出来的原始坐标通常是这个坐标系的。

那如何查询一个投影系统的SRID呢？ Coordinate reference systems 在这里可以看到所有的坐标系统的code和介绍，也可以搜索查询。

不过大家日常接触的一些坐标系，如火星坐标系、国测坐标系等，有一个问题，有一些坐标系没有得到国际标准组织的承认或者注册，所以没有 EPSG code，也就没有 SRID。

而非常不凑巧，我们的业务数据库里存储的大部分数据都是国测坐标系的，这就让人很尴尬了，这个问题等会再说，因为到现在为止，其实我们还不知道 SRID 到底是怎么影响 mysql 索引命中的

2.3，SRID 和 索引的关系，及坑

我们在 mysql 8.0 的官方文档里搜索 SRID，可以找到一个以下一个章节：MySQL :: MySQL 8.0 Reference Manual :: 12.17.2 Argument Handling by Spatial Functions

重点大意：

1. SRID值是与几何值关联的整数。可用的最大SRID值为2的32次方减一。
2. SRID 0表示无限的直角笛卡尔平面，其xy轴未分配任何单位
3. 如果未指定SRID，则新几何值的默认SRID 0是默认值（注意，这里埋了个坑）
4. 对于多个几何值的计算，所有值都必须在相同的SRS（也就是有同样的 SRID）中，否则会发生错误。
5. 通过 MySQL :: MySQL 8.0 Reference Manual :: 11.4.5 Spatial Reference System Support 的方式可以查看 mysql 支持的坐标系统，查询他们的name和SRID

Ok，了解了 mysql 对 SRID 的支持，我们再了解下官方文档中关于 SRS 查询的优化指南，MySQL :: MySQL 8.0 Reference Manual :: 11.4.9 Optimizing Spatial Analysis，MySQL :: MySQL 8.0 Reference Manual :: 11.4.10 Creating Spatial Indexes

总结下大意：

1. 对于 MyISAM 和 InnoDB表，可以使用SPATIAL索引来优化包含空间数据的列中的搜索操作
2. 使用R-树与二次拆分为SPATIAL对空间列的索引
3. 一个 SPATIAL 索引是使用几何的最小外接矩形（MBR）构建的（这里又了解了一个概念，一些sql查询方法里带的 MBR 前缀，原来是这个意思），
4. 对于大多数几何图形，MBR是围绕几何图形的最小矩形。对于水平或垂直线串，MBR是退化为线串的矩形。对于一个点，MBR是一个退化为该点的矩形
5. 创建索引的方式：CREATE TABLE geom (g GEOMETRY NOT NULL SRID 4326, SPATIAL INDEX(g));

这里我和我的同事踩了个坑，其实这个坑在另一个官方文档里有明确的一句话定义，但是我们了解到这里的时候，被一句话误导了，就是上面总结的第三点中的：如果未指定SRID，则新几何值的默认SRID 0是默认值，其实这里讲的是如果定义一个 wkt 坐标，或者使用地理计算的函数的时候，如果不指定 SRID，则默认 SRID 是0，但是我们把这句话理解成了“当一个列在创建的时候没有指定 SRID（刚才的 CREATE TABLE 语句），则默认 SRID 是0”，而且 SPATIAL 索引是可以对 SRID 为 0的列生效的，所以我们以为一个列，即使创建的时候不指定 SRID，也可以创建索引，存储坐标，和参与计算。

然而，在这个文档里（MySQL :: MySQL 8.0 Reference Manual :: 11.4.1 Spatial Data Types）明确指出了：

1. SPATIAL可以在空间列上创建索引NOT NULL并具有特定的SRID，因此如果您打算对列进行索引，请使用NOT NULL和SRID 属性对其进行声明：
2. InnoDB表格允许SRID 使用笛卡尔和地理SRS的值。 MyISAM表允许SRID 使用笛卡尔SRS的值。
3. 列只能包含具有给定SRID的值。尝试使用其他SRID插入值会产生错误。
4. 没有SRID属性的空间列不受SRID限制，并接受具有任何SRID的值。但是，在SPATIAL修改列定义以包含SRID属性之前，优化器无法在其上使用索引 ，这可能需要先修改列内容，以便所有值都具有相同的SRID

这篇文章最重要的知识出现了，如果一个 SPATIAL 列需要索引生效，必须显式声明这一列的 SRID，即使是 0，也需要显式声明，否则索引不生效，这也是我们查询慢的最终根源，另外添加索引的列必须是 not null的，这也是索引生效的条件之一。

解决这个问题很简单

ALTER TABLE demo  MODIFY COLUMN position POINT NOT NULL SRID 0;

至此，问题解决了吗？木有，而且问题还不止一个，我们继续。

2.3，我应该用哪个 SRID？

刚才提到了，我们是用国测坐标存储坐标的，那国测坐标系的 SRID 是什么呢？很抱歉，没有。所以我的第一反应是使用84坐标系的 SRID 来存储，两者应该也差不多吧，毕竟可以通过简单的方法直接转换的，于是我用 SRID 4326 试了一下，发现在添加索引时，报了一个错误，大概的意思是经度的值不支持超过90的值，我直接懵逼了，这里抛出了第二个问题，我存储点用的是这样的wkt格式： POINT(120.123 60.123) ,他这个提示给我的第一直觉是“不好，我从一开始把wkt(Well-known Text Representation of Geometry)的格式搞反了？完蛋了，所有的数据都要订正，所以的系统代码都要跟着改”，吓出一身冷汗。

然后去查了一些资料，发现的确有些地方对 wkt 的格式是 lon lat，有些是 lat lon，好像并没有一个固定的规范，而 EPSG 里的描述都是 X Y，貌似标准只关注 x和y，并不关注 xy 哪个是经度，再看一下标准文档吧，另外我的数据是从 openstreetmap 来的，他原始的 wkt 就是这样格式的，而且是 84 坐标的。

2.3，wkt 经纬度顺序问题

其实，在使用几个前端框架的时候，我已经对经纬度的表示顺序困惑很久了，一些库是 lonlat，一些库是 latlon，而且都是业内知名的流行库，为什么会有这个差异，一直让我很困惑。 最后我发现网上有不少争论类似事情的帖子，而且一些标准的定义也在变化。 延伸阅读：Axis Order Confusion - OSGeo 事实上，经过查阅，我发现我之前的认知是错误的，网上很多文章的分析也是错误的，事实上坐标顺序不是 wkt 标准定义的，而是 SRS 中某个坐标系统自己定义的，例如 84 坐标系 在 epsg 网站中查询到的结果：WGS 84 - WGS84 - World Geodetic System 1984, used in GPS - EPSG:4326

Coordinate system: Ellipsoidal 2D CS. Axes: latitude, longitude. Orientations: north, east. UoM: degree

4326 SRID 的坐标顺序就是 lat/lon。而其他坐标系，不一定是 lat/lon 还是 lon/lat。 所以，回到我们的问题，我们之前存储的 wkt 格式是 lon/lat ，所以无法添加为 4326 的坐标系，是很正常的。 最后，我们发现，其实如果是不支持的坐标系，可以用默认的 SRID ，即 0，将列的 SRID 显式设置为 0，然后就可以添加索引啦（不过此处还遗留一个问题，如果存储的坐标就是一个特殊的坐标系，但是存到mysql里是SRID为0，最后在做空间计算的时候会不会有误差？）这可能需要去了解下这些投影的具体算法了。

3.0，总结

因为篇幅问题，这里还有些问题没有深挖下去，暂时先不展开讲了，今天主要是通过一个慢查询的问题，了解了一些 OpenGIS 的标准等信息，GIS 领域的概念其实还非常多，以后再慢慢学习和分享。