8.3.3 SPATIAL Index Optimization

MySQL permits creation of SPATIAL indexes on NOT NULL geometry-valued columns (see Section 11.4.10, “Creating Spatial Indexes”). The optimizer checks the SRID attribute for indexed columns to determine which spatial reference system (SRS) to use for comparisons, and uses calculations appropriate to the SRS. (Prior to MySQL 8.0, the optimizer performs comparisons of SPATIAL index values using Cartesian calculations; the results of such operations are undefined if the column contains values with non-Cartesian SRIDs.)

MySQL是允许在非空的几何值列上建立空间索引（查看Section 11.4.10, “Creating Spatial Indexes”）。优化器惠检查索引列上的SRID属性，然后决定哪个合适的空间索引系统（SRS）被用于比较、计算（在MYSQL 8.0之前，优化器利用笛卡尔计算空间索引值进行比较；如果列值里面包含非笛卡尔SRID值，则不返回结果）

For comparisons to work properly, each column in a SPATIAL index must be SRID-restricted. That is, the column definition must include an explicit SRID attribute, and all column values must have the same SRID.

为了比较工作能够正常工作，每一列对SRID有限制要求，首先必须有明确的SRID属性，且所有的列值必须有同样的SRID值

The optimizer considers SPATIAL indexes only for SRID-restricted columns:

• Indexes on columns restricted to a Cartesian SRID enable Cartesian bounding box computations.

• Indexes on columns restricted to a geographic SRID enable geographic bounding box computations.

优化器仅仅对受限的SRID列虑空间索引：

• 列上有受限的笛卡尔边界框计算的。
• 列上有受限的几何边界框计算的。

 

The optimizer ignores SPATIAL indexes on columns that have no SRID attribute (and thus are not SRID-restricted). MySQL still maintains such indexes, as follows:

• They are updated for table modifications (INSERT, UPDATE, DELETE, and so forth). Updates occur as though the index was Cartesian, even though the column might contain a mix of Cartesian and geographical values.

• They exist only for backward compatibility (for example, the ability to perform a dump in MySQL 5.7 and restore in MySQL 8.0). Because SPATIAL indexes on columns that are not SRID-restricted are of no use to the optimizer, each such column should be modified:

  • Verify that all values within the column have the same SRID. To determine the SRIDs contained in a geometry column col_name, use the following query:

    SELECT DISTINCT ST_SRID(col_name) FROM tbl_name;

    If the query returns more than one row, the column contains a mix of SRIDs. In that case, modify its contents so all values have the same SRID.

  • Redefine the column to have an explicit SRID attribute.

  • Recreate the SPATIAL index.

对于没有SRID属性或者非SRID-restricted，优化器会忽略空间索引列。下面这些情况，MySQL仍然维护该索引

• 表更新操作（INSERT, UPDATE, DELETE等等），当那列值是笛卡尔甚至是该列包含了笛卡尔和几何值更新就会发生
• 他们是向后兼容的（比如，可以在MySQL5.7版本执行dump然后在MySQL8.0版本恢复）。因为在非SRID-restricted列上的空间索引是不适用优化器的，每一列都应该被修改。 

           1、 为了验证每一列上有相同的SRID值 ，你可以用下面的SQL语句         

       SELECT DISTINCT ST_SRID(col_name) FROM tbl_name;

     2、 如果这个查询返回结果超过一行，说明包含了多个SRID值，在这种情况下，需要修改成统一的SRID值

     3、重新定义列上有一个明确的SRID属性

     4、新创建空间索引