【譯】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用于所有空間資料！