13.4.11 Using Spatial Indexes

13.4.11 Using Spatial Indexes

optimizer는 WHERE 절에서 MBRContains() 또는 MBRWithin() 같은 함수를 사용하는 쿼리에 대해, 사용 가능한 spatial 인덱스가 검색에 참여할 수 있는지 여부를 조사합니다.
다음 쿼리는 주어진 rectangle 안에 있는 모든 object를 찾습니다:

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

이 쿼리가 어떤 방식으로 실행되는지 확인하려면 EXPLAIN을 사용합니다:

1mysql> SET @poly =
2    -> 'Polygon((30000 15000,
3                 31000 15000,
4                 31000 16000,
5                 30000 16000,
6                 30000 15000))';
7mysql> EXPLAIN SELECT fid,ST_AsText(g) FROM geom WHERE
8    -> MBRContains(ST_GeomFromText(@poly),g)\G
9*************************** 1. row ***************************
10           id: 1
11  select_type: SIMPLE
12        table: geom
13         type: range
14possible_keys: g
15          key: g
16      key_len: 32
17          ref: NULL
18         rows: 50
19        Extra: Using where
201 row in set (0.00 sec)

spatial 인덱스가 없으면 어떻게 되는지 확인해 보십시오:

1mysql> SET @poly =
2    -> 'Polygon((30000 15000,
3                 31000 15000,
4                 31000 16000,
5                 30000 16000,
6                 30000 15000))';
7mysql> EXPLAIN SELECT fid,ST_AsText(g) FROM g IGNORE INDEX (g) WHERE
8    -> MBRContains(ST_GeomFromText(@poly),g)\G
9*************************** 1. row ***************************
10           id: 1
11  select_type: SIMPLE
12        table: geom
13         type: ALL
14possible_keys: NULL
15          key: NULL
16      key_len: NULL
17          ref: NULL
18         rows: 32376
19        Extra: Using where
201 row in set (0.00 sec)

spatial 인덱스 없이 SELECT statement를 실행하면 동일한 결과가 나오지만, 실행 시간이 0.00초에서 0.46초로 증가합니다:

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