10bet网址
MySQL 8.0参考手册
相关的文档10bet官方网站 本手册下载
PDF (Ltr)- 41.6 mb
PDF (A4)- 41.7 mb
手册页(TGZ)- 262.2 kb
手册页(邮政编码)- 372.2 kb
信息(Gzip)- 4.0 mb
信息(邮政编码)- 4.0 mb
本手册节选

11.4.11使用空间索引

优化器研究可用的空间索引是否可以在搜索使用诸如MBRContains ()MBRWithin ()在哪里条款。下面的查询查找给定矩形中的所有对象:

mysql> SET @poly = -> 'Polygon((30000 15000, 31000 15000, 31000 16000, 30000 16000, 30000 15000))';mysql> SELECT fid,ST_AsText(g) FROM geom WHERE -> MBRContains(ST_GeomFromText(@poly),g);+-----+---------------------------------------------------------------+ | 支撑材| ST_AsText (g ) | +-----+---------------------------------------------------------------+ | 21 | LINESTRING(30 15845 15845 30350.4 - 15828.8, 30350.6, 30333.8,……| | 22 | linestring(30350.6 15871.4,30350.6 15887.8,30334 15887.8,…| | 23 | linestring(30350.6 15914.2,30350.6 15930.4,30334 15930.4,…| | 24 | linestring(30290.2 15823,30290.2 15839.4,30273.4 15839.4,…| | 25 | linestring (30291.4 15866.2,30291.6 15882.4,30274.8 15882. ...| | 26 | linestring(30291.6 15918.2,30291.6 15934.4,30275 15934.4,…| | 249 | linestring (30337.8 15938.6,30337.8 15946.8,30320.4 15946. ...| | 1 | linestring (30250.4 15129.2,30248.8 15138.4,30238.2 15136. ... | | 2 | LINESTRING(30220.2 15122.8,30217.2 15137.8,30207.6 15136, ... | | 3 | LINESTRING(30179 15114.4,30176.6 15129.4,30167 15128,3016 ... | | 4 | LINESTRING(30155.2 15121.4,30140.4 15118.6,30142 15109,30 ... | | 5 | LINESTRING(30192.4 15085,30177.6 15082.2,30179.2 15072.4, ... | | 6 | LINESTRING(30244 15087,30229 15086.2,30229.4 15076.4,3024 ... | | 7 | LINESTRING(30200.6 15059.4,30185.6 15058.6,30186 15048.8, ... | | 10 | LINESTRING(30179.6 15017.8,30181 15002.8,30190.8 15003.6, ... | | 11 | LINESTRING(30154.2 15000.4,30168.6 15004.8,30166 15014.2, ... | | 13 | LINESTRING(30105 15065.8,30108.4 15050.8,30118 15053,3011 ... | | 154 | LINESTRING(30276.2 15143.8,30261.4 15141,30263 15131.4,30 ... | | 155 | LINESTRING(30269.8 15084,30269.4 15093.4,30258.6 15093,30 ... | | 157 | LINESTRING(30128.2 15011,30113.2 15010.2,30113.6 15000.4, ... | +-----+---------------------------------------------------------------+ 20 rows in set (0.00 sec)

使用解释查询查询的执行方式。

mysql> SET @poly = -> 'Polygon((30000 15000, 31000 15000, 31000 16000, 30000 16000, 30000 15000))';mysql >解释选择支撑材,ST_AsText从几何学(g) - > MBRContains (ST_GeomFromText (@poly), g) \ g  *************************** 1。row *************************** id: 1 select_type: SIMPLE table: geom type: range possible le_keys: g key: g key_len: 32 ref: NULL rows: 50 Extra: Using where 1 row in set (0.00 sec)

检查如果没有空间索引会发生什么:

mysql> SET @poly = -> 'Polygon((30000 15000, 31000 15000, 31000 16000, 30000 16000, 30000 15000))';mysql >解释选择支撑材,ST_AsText从g (g)忽略指数(g) - > MBRContains (ST_GeomFromText (@poly), g) \ g  *************************** 1。row *************************** id: 1 select_type: SIMPLE table: geom type: ALL possible le_keys: NULL key: NULL key_len: NULL ref: NULL rows: 32376 Extra: Using where 1 row in set (0.00 sec)

执行选择没有空间索引的语句会产生相同的结果,但会导致执行时间从0.00秒上升到0.46秒:

mysql> SET @poly = -> 'Polygon((30000 15000, 31000 15000, 31000 16000, 30000 16000, 30000 15000))';mysql> SELECT fid,ST_AsText(g) FROM geom IGNORE INDEX (g) WHERE -> MBRContains(ST_GeomFromText(@poly),g);+-----+---------------------------------------------------------------+ | 支撑材| ST_AsText (g ) | +-----+---------------------------------------------------------------+ | 1 | LINESTRING(30250.4 - 15129.2, 30248.8 - 15138.4, 30238.2 15136……| | 2 | linestring(30220.2 15122.8,30217.2 15137.8,30207.6 15136,…| | 3 | linestring(30179 15114.4,30176.6 15129.4,30167 15128,3016…| | 4 | linestring(30155.2 15121.4,30140.4 15118.6,30142 15109,30…| | 5 | linestring(30192.4 15085,30177.6 15082.2,30179.2 15072.4,…| | 6 | linestring(30244 15087,30229 15086.2,30229.4 15076.4,3024…| | 7 | linestring(30200.6 15059.4,30185.6 15058.6,30186 15048.8,…| | 10 | linestring(30179.6 15017.8,30181 15002.8,30190.8 15003.6,… | | 11 | LINESTRING(30154.2 15000.4,30168.6 15004.8,30166 15014.2, ... | | 13 | LINESTRING(30105 15065.8,30108.4 15050.8,30118 15053,3011 ... | | 21 | LINESTRING(30350.4 15828.8,30350.6 15845,30333.8 15845,30 ... | | 22 | LINESTRING(30350.6 15871.4,30350.6 15887.8,30334 15887.8, ... | | 23 | LINESTRING(30350.6 15914.2,30350.6 15930.4,30334 15930.4, ... | | 24 | LINESTRING(30290.2 15823,30290.2 15839.4,30273.4 15839.4, ... | | 25 | LINESTRING(30291.4 15866.2,30291.6 15882.4,30274.8 15882. ... | | 26 | LINESTRING(30291.6 15918.2,30291.6 15934.4,30275 15934.4, ... | | 154 | LINESTRING(30276.2 15143.8,30261.4 15141,30263 15131.4,30 ... | | 155 | LINESTRING(30269.8 15084,30269.4 15093.4,30258.6 15093,30 ... | | 157 | LINESTRING(30128.2 15011,30113.2 15010.2,30113.6 15000.4, ... | | 249 | LINESTRING(30337.8 15938.6,30337.8 15946.8,30320.4 15946. ... | +-----+---------------------------------------------------------------+ 20 rows in set (0.46 sec)