mysql innodb博客_【译】MySQL服务博客 - InnoDB中的空间数据索引-阿里云开发者社区...

MySQL 8.0的一个重要特性是对地理业务的支持。MySQL现在已拥有一类功能称之为空间参考系统(SRS)，其中近500个是地理区域相关的。大多数函数还支持地理计算。大家可能会关心索引的功能的增强。

MySQL 8.0附带了用于地理数据的InnoDB空间索引。由于笛卡尔和地理数据的计算方式不同，因此不能在同一个索引中混合使用。实际上，在同一索引中的多个SRS中索引数据是没有意义的。因此，MySQL加强了几何列定义中的SRID限制。

SRID限制

在5.7及更早版本中，对于加索引的几何列的唯一要求是该类型应该是几何类型，并且该列不为空。不幸的是，我们允许将不同SRID中的几何数据插入到同一个索引中。这种做法毫无意义，尤其当某些几何体位于地理SRS中时，情况会变得更糟。

因此，MySQL 8.0增加了限制，几何列只有一个SRID：

mysql> CREATE TABLE places (

-> pk INT PRIMARY KEY,

-> position POINT NOT NULL SRID 4326,

-> name VARCHAR(200)

-> );

Query OK, 0 rows affected (0,00 sec)

如果我们试图在不同的SRID中插入一个几何体，会得到一个错误：

mysql> INSERT INTO places VALUES (1, ST_GeomFromText('POINT(63.4269 10.3958)',

0), 'Nidaros Cathedral');

ERROR 3643 (HY000): The SRID of the geometry does not match the SRID of the column 'position'.

The SRID of the geometry is 0, but the SRID of the column is 4326. Consider changing the SRID

of the geometry or the SRID property of the column.

如果我们遵守SRID限制，则该点则可以插入：

mysql> INSERT INTO places VALUES (1, ST_GeomFromText('POINT(63.4269 10.3958)',

4326), 'Nidaros Cathedral');

Query OK, 1 row affected (0,00 sec)

有了这个限制，MySQL确保我们不会将同一列中不同SRID中的数据混合在一起，从而使列可以索引。

另一件事是锁定SRID。在SRID限制中它被使用，服务器不允许我们drop掉SRS：

mysql> DROP SPATIAL REFERENCE SYSTEM 4326;

ERROR 3716 (SR005): Can't modify SRID 4326. There is at least one column depending on it.

究竟是哪一列呢？

mysql> SELECT * FROM INFORMATION_SCHEMA.ST_GEOMETRY_COLUMNS WHERE SRS_ID=4326\G

*************************** 1. row ***************************

TABLE_CATALOG: def

TABLE_SCHEMA: test

TABLE_NAME: places

COLUMN_NAME: position

SRS_NAME: WGS 84

SRS_ID: 4326

GEOMETRY_TYPE_NAME: point

1 row in set (0,00 sec)

索引

我们可以轻松地在几何列上创建索引。

mysql> CREATE SPATIAL INDEX position ON places (position);

Query OK, 0 rows affected (0,00 sec)

Records: 0 Duplicates: 0 Warnings: 0

由于此列位于SRID 4326中，因此索引也将位于SRID 4326. SRID 4326是地理位置的SRS，因此这将是地理位置索引。查询优化器将自动使用这个索引来优化与空间相关的函数掉执行(ST_Contains，ST_Within等)，如果它发现这是最低成本的处理方法。所有的空间关系函数都支持地理计算。

一个可能令人惊讶的事实是，服务器仍然允许我们不必限制在单列上建索引，但是会警告这个索引永远不会被使用：

mysql> CREATE TABLE dont_do_this (

-> pk INT PRIMARY KEY,

-> position POINT NOT NULL,

-> name VARCHAR(200)

-> );

Query OK, 0 rows affected (0,00 sec)

mysql> CREATE SPATIAL INDEX position ON dont_do_this (position);

Query OK, 0 rows affected, 1 warning (0,00 sec)

Records: 0 Duplicates: 0 Warnings: 1

Warning (Code 3674): The spatial index on column 'position' will not be used by the query optimizer since the column does not have an SRID attribute. Consider adding an SRID attribute to the column.

警告说明了一切。该索引将永远不会被使用。服务器允许我们仅仅为了一个原因创建索引：向后兼容mysqldump。我们应该能够从5.7加载一个mysqldump。如果在转储中存在像这样的空间索引，它们将被创建但不会使用。

MyISAM数据

值得注意的是，这只适用于InnoDB。如果我们尝试在MyISAM中的地理SRID上创建一个SRID受限列，我们会得到一个错误：

mysql> CREATE TABLE places (

-> pk INT PRIMARY KEY,

-> position POINT NOT NULL SRID 4326,

-> name VARCHAR(200)

-> ) ENGINE=MyISAM;

ERROR 1178 (42000): The storage engine for the table doesn't support geographic spatial reference systems

如果我们尝试使用笛卡尔SRS的SRID，我们可以创建表：

​mysql> CREATE TABLE places (

-> pk INT PRIMARY KEY,

-> position POINT NOT NULL SRID 3857,

-> name VARCHAR(200)

-> ) ENGINE=MyISAM;

Query OK, 0 rows affected (0,00 sec)

原因是MyISAM不支持地理空间索引。创建表时我们已经阻止了它。

我的建议是：将InnoDB用于所有空间数据！