• 首页
  • vue
  • TypeScript
  • JavaScript
  • scss
  • css3
  • html5
  • php
  • MySQL
  • redis
  • jQuery
  • 使用空间索引

    优化调查是否可用的空间索引可以参与的搜索使用的功能,如查询,MBRContains()MBRWithin()WHERE的条款。以下查询查找给定矩形中的所有对象:

    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);
    +-----	+---------------------------------------------------------------	+
    | fid 	| ST_AsText(g)                                                  	|
    +-----	+---------------------------------------------------------------	+
    |  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, ... 	|
    | 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)
    

    使用EXPLAIN检查执行该查询方式:

    mysql> SET @poly =
    -> 'Polygon((30000 15000,
                     31000 15000,
                     31000 16000,
                     30000 16000,
                     30000 15000))';
    mysql> EXPLAIN SELECT fid,ST_AsText(g) FROM geom WHERE
    -> MBRContains(ST_GeomFromText(@poly),g)\G
    *************************** 1. row 	***************************
               id	: 1
      select_type	: SIMPLE
            table	: geom
             type	: range
    possible_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> EXPLAIN SELECT fid,ST_AsText(g) FROM g IGNORE INDEX (g) WHERE
    -> MBRContains(ST_GeomFromText(@poly),g)\G
    *************************** 1. row 	***************************
               id	: 1
      select_type	: SIMPLE
            table	: geom
             type	: ALL
    possible_keys	: NULL
              key	: NULL
          key_len	: NULL
              ref	: NULL
             rows	: 32376
            Extra	: Using where
    1 row in set (0.00 sec)
    

    SELECT没有空间索引的情况下执行语句会产生相同的结果,但会使执行时间从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);
    +-----	+---------------------------------------------------------------	+
    | fid 	| 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)