mysql 中的空间扩展

不念不忘少年蓝@ 2022-08-26 14:26 199阅读 0赞

## http://dev.mysql.com/doc/refman/5.1/zh/spatial-extensions-in-mysql.html\#functions-for-testing-spatial-relations-between-geometric-objects    ##

##     ##

## 19.6. 优化空间分析 ##

[19.6.1. 创建空间索引][19.6.1.] [19.6.2. 使用空间索引][19.6.2.]

可以使用索引对 2个非空间数据库中的搜索操作进行优化。对于空间数据库，这同样成立。有了以前设计的大量多维索引功能的帮助，能够对空间搜索进行优化。最典型的情况如下：

·         搜索包含给定点的所有对象的Point查询。

·         搜索与给定地区交迭的所有对象的地区查询。

MySQL采用了具有2次分裂特性的R-Trees来为空间列编制索引。使用几何对象的MBR来创建空间索引。对于大多数几何对象，MBR是包围几何对象的最小矩形。对于水平或垂直linestring，MBR退化为linestring的矩形。对于点，MBR是退化为点的矩形。

此外，还能在空间列上创建正常索引。需要为除POINT列之外的空间列上的任何索引（非空间）声明前缀。

### 19.6.1. 创建空间索引 ###

MySQL能够使用与创建正规索引类似的语法创建空间索引，但使用了SPATIAL关键字进行了扩展。对于目前编制了索引的空间列，必须将其声明为NOT NULL。在下面的示例中，介绍了创建空间索引的方法。

·         对于CREATE TABLE：

·                mysql> CREATE TABLE geom (g GEOMETRY NOT NULL, SPATIAL INDEX(g));

·         对于ALTER TABLE：

·                mysql> ALTER TABLE geom ADD SPATIAL INDEX(g);

·         对于CREATE INDEX：

·                mysql> CREATE SPATIAL INDEX sp_index ON geom (g);

对于MyISAM表，SPATIAL INDEX负责创建R-tree索引。对于支持空间索引的其他存储引擎，SPATIAL INDEX能够创建B-tree索引。对于准确的值查找而不是范围扫描，作用在空间值上的B-tree索引很有用。

要想撤销空间索引，可使用ALTER TABLE或DROP INDEX：

·         对于ALTER TABLE：

·                mysql> ALTER TABLE geom DROP INDEX g;

·         对于DROP INDEX：

·                mysql> DROP INDEX sp_index ON geom;

示例：假定表geom包含32000以上的几何对象，它们保存在类型为GEOMETRY的列g中。该表还有用于保存对象ID值的AUTO\_INCREMENT列。

要想在列g上添加空间索引，可使用下述语句：

mysql> ALTER TABLE geom ADD SPATIAL INDEX(g);
    Query OK, 32376 rows affected (4.05 sec)
    Records: 32376  Duplicates: 0  Warnings: 0

### 19.6.2. 使用空间索引 ###

优化程序将调查可用的空间索引是否能包含在使用某些函数的查询搜索中，如WHERE子句中的MBRContains()或MBRWithin()函数。例如，假定我们打算找出位于给定矩形中的所有对象：

mysql> SELECT fid,AsText(g) FROM geom WHERE
    mysql> MBRContains(GeomFromText('Polygon((30000 15000,31000 15000,31000 16000,30000 16000,30000 15000))'),g);
    +-----+-----------------------------------------------------------------------------+
    | fid | AsText(g)                                                                   |
    +-----+-----------------------------------------------------------------------------+
    |  21 | LINESTRING(30350.4 15828.8,30350.6 15845,30333.8 15845,30333.8 15828.8)     |
    |  22 | LINESTRING(30350.6 15871.4,30350.6 15887.8,30334 15887.8,30334 15871.4)     |
    |  23 | LINESTRING(30350.6 15914.2,30350.6 15930.4,30334 15930.4,30334 15914.2)     |
    |  24 | LINESTRING(30290.2 15823,30290.2 15839.4,30273.4 15839.4,30273.4 15823)     |
    |  25 | LINESTRING(30291.4 15866.2,30291.6 15882.4,30274.8 15882.4,30274.8 15866.2) |
    |  26 | LINESTRING(30291.6 15918.2,30291.6 15934.4,30275 15934.4,30275 15918.2)     |
    | 249 | LINESTRING(30337.8 15938.6,30337.8 15946.8,30320.4 15946.8,30320.4 15938.4) |
    |   1 | LINESTRING(30250.4 15129.2,30248.8 15138.4,30238.2 15136.4,30240 15127.2)   |
    |   2 | LINESTRING(30220.2 15122.8,30217.2 15137.8,30207.6 15136,30210.4 15121)     |
    |   3 | LINESTRING(30179 15114.4,30176.6 15129.4,30167 15128,30169 15113)           |
    |   4 | LINESTRING(30155.2 15121.4,30140.4 15118.6,30142 15109,30157 15111.6)       |
    |   5 | LINESTRING(30192.4 15085,30177.6 15082.2,30179.2 15072.4,30194.2 15075.2)   |
    |   6 | LINESTRING(30244 15087,30229 15086.2,30229.4 15076.4,30244.6 15077)         |
    |   7 | LINESTRING(30200.6 15059.4,30185.6 15058.6,30186 15048.8,30201.2 15049.4)   |
    |  10 | LINESTRING(30179.6 15017.8,30181 15002.8,30190.8 15003.6,30189.6 15019)     |
    |  11 | LINESTRING(30154.2 15000.4,30168.6 15004.8,30166 15014.2,30151.2 15009.8)   |
    |  13 | LINESTRING(30105 15065.8,30108.4 15050.8,30118 15053,30114.6 15067.8)       |
    | 154 | LINESTRING(30276.2 15143.8,30261.4 15141,30263 15131.4,30278 15134)         |
    | 155 | LINESTRING(30269.8 15084,30269.4 15093.4,30258.6 15093,30259 15083.4)       |
    | 157 | LINESTRING(30128.2 15011,30113.2 15010.2,30113.6 15000.4,30128.8 15001)     |
    +-----+-----------------------------------------------------------------------------+
    20 rows in set (0.00 sec)

我们使用EXPLAIN来检查该查询的执行方式（ID列已被删除，以便输出能更好地与页匹配）：

让我们检查一下在没有空间索引的情况下会出现什么：

执行SELECT语句，忽略空间键：

mysql> SELECT fid,AsText(g) FROM geom IGNORE INDEX (g) WHERE
    mysql> MBRContains(GeomFromText('Polygon((30000 15000,31000 15000,31000 16000,30000 16000,30000 15000))'),g);
    +-----+-----------------------------------------------------------------------------+
    | fid | AsText(g)                                                                   |
    +-----+-----------------------------------------------------------------------------+
    |   1 | LINESTRING(30250.4 15129.2,30248.8 15138.4,30238.2 15136.4,30240 15127.2)   |
    |   2 | LINESTRING(30220.2 15122.8,30217.2 15137.8,30207.6 15136,30210.4 15121)     |
    |   3 | LINESTRING(30179 15114.4,30176.6 15129.4,30167 15128,30169 15113)           |
    |   4 | LINESTRING(30155.2 15121.4,30140.4 15118.6,30142 15109,30157 15111.6)       |
    |   5 | LINESTRING(30192.4 15085,30177.6 15082.2,30179.2 15072.4,30194.2 15075.2)   |
    |   6 | LINESTRING(30244 15087,30229 15086.2,30229.4 15076.4,30244.6 15077)         |
    |   7 | LINESTRING(30200.6 15059.4,30185.6 15058.6,30186 15048.8,30201.2 15049.4)   |
    |  10 | LINESTRING(30179.6 15017.8,30181 15002.8,30190.8 15003.6,30189.6 15019)     |
    |  11 | LINESTRING(30154.2 15000.4,30168.6 15004.8,30166 15014.2,30151.2 15009.8)   |
    |  13 | LINESTRING(30105 15065.8,30108.4 15050.8,30118 15053,30114.6 15067.8)       |
    |  21 | LINESTRING(30350.4 15828.8,30350.6 15845,30333.8 15845,30333.8 15828.8)     |
    |  22 | LINESTRING(30350.6 15871.4,30350.6 15887.8,30334 15887.8,30334 15871.4)     |
    |  23 | LINESTRING(30350.6 15914.2,30350.6 15930.4,30334 15930.4,30334 15914.2)     |
    |  24 | LINESTRING(30290.2 15823,30290.2 15839.4,30273.4 15839.4,30273.4 15823)     |
    |  25 | LINESTRING(30291.4 15866.2,30291.6 15882.4,30274.8 15882.4,30274.8 15866.2) |
    |  26 | LINESTRING(30291.6 15918.2,30291.6 15934.4,30275 15934.4,30275 15918.2)     |
    | 154 | LINESTRING(30276.2 15143.8,30261.4 15141,30263 15131.4,30278 15134)         |
    | 155 | LINESTRING(30269.8 15084,30269.4 15093.4,30258.6 15093,30259 15083.4)       |
    | 157 | LINESTRING(30128.2 15011,30113.2 15010.2,30113.6 15000.4,30128.8 15001)     |
    | 249 | LINESTRING(30337.8 15938.6,30337.8 15946.8,30320.4 15946.8,30320.4 15938.4) |
    +-----+-----------------------------------------------------------------------------+
    20 rows in set (0.46 sec)

未使用索引时，该查询的执行时间将从0.00秒上升到0.46秒。

在未来的版本中，空间索引也可能会用于优化其他函数。请参见[19.5.4节，“测试几何对象间空间关系的函数”][19.5.4]。

[19.6.1.]: http://dev.mysql.com/doc/refman/5.1/zh/spatial-extensions-in-mysql.html#creating-spatial-indexes
[19.6.2.]: http://dev.mysql.com/doc/refman/5.1/zh/spatial-extensions-in-mysql.html#using-a-spatial-index
[19.5.4]: http://dev.mysql.com/doc/refman/5.1/zh/spatial-extensions-in-mysql.html#functions-for-testing-spatial-relations-between-geometric-objects