mysql 翻译系列 二十八

10.3.3 空间索引优化

MySQL允许在非空的几何值列上创建SPATIAL索引（请参见13.4.10节 “创建空间索引”）。优化器会检查索引列的SRID属性，以确定使用哪个空间参考系统（SRS）进行比较，并使用适合该SRS的计算方法。（在MySQL 8.0之前，优化器使用笛卡尔计算来比较SPATIAL索引值；如果列中包含具有非笛卡尔SRID的值，这种操作的结果是不确定的。）

为了使比较能够正确进行，SPATIAL索引中的每一列都必须进行SRID限制。也就是说，列定义必须包含一个明确的SRID属性，并且所有列值必须具有相同的SRID。

优化器仅会考虑对SRID受限列的SPATIAL索引：

• 限制为笛卡尔SRID的列上的索引支持笛卡尔边界框计算。
• 限制为地理SRID的列上的索引支持地理边界框计算。

优化器会忽略没有SRID属性（因此不受SRID限制）的列上的SPATIAL索引。不过，MySQL仍会按以下方式维护这些索引：

• 对于表的修改操作（INSERT、UPDATE、DELETE等），这些索引会进行更新。即使列中可能同时包含笛卡尔值和地理值，更新操作也会按照索引是笛卡尔索引的方式进行。
• 这些索引仅为了向后兼容而存在（例如，能够在MySQL 5.7中进行转储，并在MySQL 8.0中恢复）。由于不受SRID限制的列上的SPATIAL索引对优化器没有用处，因此应对每个这样的列进行修改：
  • 验证列中的所有值是否具有相同的SRID。要确定几何列col_name中包含的SRID，可以使用以下查询：

SELECT DISTINCT ST_SRID(col_name) FROM tbl_name;

如果查询返回多行，说明该列包含多种SRID。在这种情况下，需要修改其内容，使所有值具有相同的SRID。
- 重新定义列，使其具有明确的SRID属性。
- 重新创建SPATIAL索引。

10.3.5 列索引

最常见的索引类型是单列索引，它将某一列的值副本存储在一个数据结构中，以便快速查找具有相应列值的行。B树数据结构能让索引快速找到特定值、一组值或一个值的范围，这对应于WHERE子句中的=、>、≤、BETWEEN、IN等运算符。

每张表的最大索引数量和最大索引长度由存储引擎决定。具体可查看第17章 “InnoDB存储引擎” 和第18章 “其他存储引擎”。所有存储引擎每张表至少支持16个索引，且总索引长度至少为256字节。大多数存储引擎的限制更高。

有关列索引的更多信息，请参见15.1.15节 “CREATE INDEX语句”。

索引前缀

在字符串列的索引规范中，使用<col_name>(<N>)语法，可以创建一个仅使用列中前N个字符的索引。以这种方式仅对列值的前缀进行索引，可以使索引文件小得多。在对BLOB或TEXT列进行索引时，必须指定索引的前缀长度。例如：

CREATE TABLE test (blob_col BLOB, INDEX(blob_col(10)));

对于使用REDUNDANT或COMPACT行格式的InnoDB表，前缀长度最长可达767字节。对于使用DYNAMIC或COMPRESSED行格式的InnoDB表，前缀长度限制为3072字节。对于MyISAM表，前缀长度限制为1000字节。

注意

前缀限制以字节为单位衡量，而在CREATE TABLE、ALTER TABLE和CREATE INDEX语句中，前缀长度对于非二进制字符串类型（CHAR、VARCHAR、TEXT）被解释为字符数，对于二进制字符串类型（BINARY、VARBINARY、BLOB）被解释为字节数。在为使用多字节字符集的非二进制字符串列指定前缀长度时，需要考虑这一点 。

如果搜索词超过索引前缀长度，索引将用于排除不匹配的行，然后检查剩余的行是否可能匹配。

有关索引前缀的更多信息，请参见15.1.15节 “CREATE INDEX语句”。

全文索引

全文索引用于全文搜索。只有InnoDB和MyISAM存储引擎支持全文索引，且仅适用于CHAR、VARCHAR和TEXT列。索引总是对整个列进行，不支持列前缀索引。详细信息，请参见14.9节 “全文搜索函数”。

针对单个InnoDB表的某些类型的全文查询会应用优化。具有以下特征的查询效率尤其高：

• 仅返回文档ID，或文档ID和搜索排名的全文查询。
• 按分数降序对匹配行进行排序，并应用LIMIT子句获取前N个匹配行的全文查询。要应用此优化，查询中不能有WHERE子句，且只能有一个降序的ORDER BY子句。
• 仅检索与搜索词匹配的行的COUNT(*)值，且没有其他WHERE子句的全文查询。将WHERE子句写为WHERE MATCH(<text>) AGAINST ('<other_text>')，不使用任何> 0比较运算符。

对于包含全文表达式的查询，MySQL会在查询执行的优化阶段计算这些表达式。优化器不只是查看全文表达式并进行估算，而是在制定执行计划的过程中实际计算它们。

这种行为的一个影响是，全文查询的EXPLAIN通常比非全文查询要慢，因为非全文查询在优化阶段不会进行表达式计算。

全文查询的EXPLAIN在Extra列中可能会显示Select tables optimized away，这是因为在优化过程中进行了匹配；在这种情况下，后续执行时无需访问表。

空间索引

可以对空间数据类型创建索引。MyISAM和InnoDB支持对空间类型使用R树索引。其他存储引擎对空间类型使用B树索引（ARCHIVE除外，它不支持空间类型索引）。

MEMORY存储引擎中的索引

MEMORY存储引擎默认使用哈希索引，但也支持B树索引。