MySQL 8.0 新特性之函数索引

MySQL 8.0 新特性之函数索引

通常来说索引使用的是列值或者列值的前缀部分。例如，在下表 t1 中，索引包含了字段 col1 的值，以及字段 col2 的前 10 个字节：

CREATE TABLE t1 (
  col1 VARCHAR(10),
  col2 VARCHAR(20),
  INDEX (col1, col2(10))
);

MySQL 8.0.13 以及更高版本支持函数索引（functional key parts），也就是将表达式的值作为索引的内容，而不是列值或列值前缀。 将函数作为索引键可以用于索引那些没有在表中直接存储的内容。例如：

CREATE TABLE t1 (col1 INT, col2 INT, INDEX func_index ((ABS(col1))));
CREATE INDEX idx1 ON t1 ((col1 + col2));
CREATE INDEX idx2 ON t1 ((col1 + col2), (col1 - col2), col1);
ALTER TABLE t1 ADD INDEX ((col1 * 40) DESC);

多列索引可以同时包含非函数列和函数列。

函数索引支持ASC和DESC选项。

函数索引必须遵循以下规则。如果索引键中包含了不允许的内容，创建索引时将会产生错误。

在索引定义中，需要将表达式放入括号之中，以便与列值索引或者前缀索引进行区分。例如，以下索引表达式使用了括号：

INDEX ((col1 + col2), (col3 - col4))

下面是一个错误的写法，表达式没有位于括号之中：

INDEX (col1 + col2, col3 - col4)

函数索引不能只包含一个单独的列名。例如，以下写法是错误的：

INDEX ((col1), (col2))

但是，可以使用非函数索引的方式进行定义：

INDEX (col1, col2)

函数索引中的表达式不能使用列的前缀。可以使用 SUBSTRING() 和 CAST() 函数作为一个替代方案，参考后文。

外键不支持函数索引。

对于CREATE TABLE … LIKE语句，新建的表中将会保留源表中的函数索引。

函数索引实际上是使用隐藏的虚拟计算列来实现，因此存在以下限制：

每个函数索引都会算作一个列数，参与计算表的总列数限制；参考 Section C.10.4, “Limits on Table Column Count and
Row Size”。

函数索引同样遵循计算列的所有限制。例如：

只有那些能够用于计算列的函数才能够用于创建函数索引。
函数索引中不允许使用子查询、参数、变量、存储函数以及自定义函数。
更多相关限制的信息，可以参考 Section 13.1.20.8, “CREATE TABLE and Generated Columns”，以及 Section 13.1.9.2, “ALTER TABLE and Generated Columns”。

函数索引支持UNIQUE选项。但是，主键不能包含函数列。主键只能使用存储的计算列，但是函数索引使用虚拟计算列实现，而不是存储计算列。

SPATIAL 索引和 FULLTEXT 索引不支持函数索引。

如果某个表中没有主键，InnoDB 存储引擎自动将第一个 UNIQUE NOT NULL 索引提升为主键。但是对于包含函数列的 UNIQUE NOT NULL 索引不会进行提升。

对于非函数索引，如果创建重复的索引，系统会提示一个警告。创建重复的函数索引不会提示任何信息。

如果要删除一个在函数索引中使用的字段，必须先删除该索引；否则将会产生错误。

虽然非函数索引支持前缀索引，但是函数索引不支持使用字段的前缀。替代的方法就是使用 SUBSTRING() 函数（或者后文中的 CAST() 函数）。如果使用 SUBSTRING() 函数定义索引列，要想在查询中使用该索引，必须在WHERE 子句中使用同样的 SUBSTRING() 函数。以下示例中，只有第二个SELECT能够使用索引，因为它的查询中使用了和索引定义相同的 SUBSTRING() 函数和参数：

CREATE TABLE tbl (
  col1 LONGTEXT,
  INDEX idx1 ((SUBSTRING(col1, 1, 10)))
);
SELECT * FROM tbl WHERE SUBSTRING(col1, 1, 9) = '123456789';
SELECT * FROM tbl WHERE SUBSTRING(col1, 1, 10) = '1234567890';

函数索引能够支持其他方式无法使用的数据类型，例如 JSON 数据。不过，使用时需要特别小心。例如，以下创建索引的语法不会生效：

CREATE TABLE employees (
  data JSON,
  INDEX ((data->>'$.name'))
);
ERROR 3757 (HY000): Cannot create a functional index on an expression that returns a BLOB or TEXT. Please consider using CAST.

该语法的问题在于：

运算符 ->> 等价于 JSON_UNQUOTE(JSON_EXTRACT(…))。
JSON_UNQUOTE() 函数返回 LONGTEXT 类型的数据，因此相应的隐藏计算列也具有这种数据类型。
MySQL 不支持非前缀的 LONGTEXT 列索引，而函数索引又不支持前缀索引。两者互相矛盾。
如果需要为 JSON 列创建索引，可以尝试使用 CAST() 函数：

CREATE TABLE employees (
  data JSON,
  INDEX ((CAST(data->>'$.name' AS CHAR(30))))
);

相应的隐藏计算列被转换为 VARCHAR(30) 类型，这种数据类型可以进行索引。但是这种方法带来了一个新的使用上的问题：

CAST() 函数返回的字符串使用 utf8mb4_0900_ai_ci 排序规则（服务器默认设置）。
JSON_UNQUOTE() 函数返回的字符串使用 utf8mb4_bin 排序规则（硬编码，不能修改）。
结果就是，索引定义中的字符排序与以下查询中的 WHERE 子句中的字符排序不一致：

SELECT * FROM employees WHERE data->>'$.name' = 'James';

以上查询不会使用索引。为了支持这种情况下能够使用函数索引，优化器查找索引时自动排除索引中的 CAST() 函数的影响，但是只有当索引表达式的排序规则能够匹配查询表达式的排序规则时才会这样处理。为了能够使用这种函数索引，可以采用以下两种解决方案之一（它们之间存在一些差异）：

解决方案 1：为索引表达式指定一个与 JSON_UNQUOTE() 相同的字符排序规则：

CREATE TABLE employees (
  data JSON,
  INDEX idx ((CAST(data->>"$.name" AS CHAR(30)) COLLATE utf8mb4_bin))
);
INSERT INTO employees VALUES
  ('{ "name": "james", "salary": 9000 }'),
  ('{ "name": "James", "salary": 10000 }'),
  ('{ "name": "Mary", "salary": 12000 }'),
  ('{ "name": "Peter", "salary": 8000 }');
SELECT * FROM employees WHERE data->>'$.name' = 'James';

运算符 ->> 等价于 JSON_UNQUOTE(JSON_EXTRACT(…)) ，而 JSON_UNQUOTE() 返回的字符串使用 utf8mb4_bin 排序规则。因此，查询条件区分大小写，只返回一条记录：

±-----------------------------------+
| data |
±-----------------------------------+
| {“name”: “James”, “salary”: 10000} |
±-----------------------------------+

解决方案 2：在查询条件中指定完整的表达式：

CREATE TABLE employees (
  data JSON,
  INDEX idx ((CAST(data->>"$.name" AS CHAR(30))))
);
INSERT INTO employees VALUES
  ('{ "name": "james", "salary": 9000 }'),
  ('{ "name": "James", "salary": 10000 }'),
  ('{ "name": "Mary", "salary": 12000 }'),
  ('{ "name": "Peter", "salary": 8000 }');
SELECT * FROM employees WHERE CAST(data->>'$.name' AS CHAR(30)) = 'James';

CAST() 函数返回的字符串使用的是 utf8mb4_0900_ai_ci 排序规则，因此查询条件不区分大小写，返回两条记录：

±-----------------------------------+
| data |
±-----------------------------------+
| {“name”: “james”, “salary”: 9000} |
| {“name”: “James”, “salary”: 10000} |
±-----------------------------------+

需要注意的是，虽然优化器支持计算列索引中的自动 CAST() 去除处理， 不能使用以下方法实现 JSON 数据的索引，因为这种方法对于存在索引时和不存在索引时返回的结果不同（Bug#27337092）：

mysql> CREATE TABLE employees (
         data JSON,
         generated_col VARCHAR(30) AS (CAST(data->>'$.name' AS CHAR(30)))
       );
Query OK, 0 rows affected, 1 warning (0.03 sec)

mysql> INSERT INTO employees (data)
       VALUES ('{"name": "james"}'), ('{"name": "James"}');
Query OK, 2 rows affected, 1 warning (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 1

mysql> SELECT * FROM employees WHERE data->>'$.name' = 'James';
+-------------------+---------------+
| data              | generated_col |
+-------------------+---------------+
| {"name": "James"} | James         |
+-------------------+---------------+
1 row in set (0.00 sec)

mysql> ALTER TABLE employees ADD INDEX idx (generated_col);
Query OK, 0 rows affected, 1 warning (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 1

mysql> SELECT * FROM employees WHERE data->>'$.name' = 'James';
+-------------------+---------------+
| data              | generated_col |
+-------------------+---------------+
| {"name": "james"} | james         |
| {"name": "James"} | James         |
+-------------------+---------------+
2 rows in set (0.01 sec)