MySQL8.0 - 新特性 - Functional Index-云栖社区-阿里云 - 网站安全

前言

MySQL8.0.13版本开始支持使用表达式或函数来作为索引键值，这使得索引的定义更加灵活，一些运算可以直接转移到索引上去。实际上在之前的版本，我们也可以通过在generated column上创建索引的方式来实现类似功能，

root@test 05:20:44>CREATE TABLE t1 (a INT, b INT, c INT, PRIMARY KEY((a + b)));
ERROR 3756 (HY000): The primary key cannot be a functional index
root@test 05:21:12>CREATE TABLE t1 (a INT, b INT, c INT, KEY((a + b)));
Query OK, 0 rows affected (0.13 sec)

root@test 05:21:29>ALTER TABLE t1 ADD KEY((a+b), (a-b));
Query OK, 0 rows affected (0.20 sec)
Records: 0  Duplicates: 0  Warnings: 0

root@test 05:22:10>ALTER TABLE t1 ADD KEY((a+b), a);
Query OK, 0 rows affected (0.20 sec)
Records: 0  Duplicates: 0  Warnings: 0

root@test 12:08:58>SHOW INDEX FROM t1;
+-------+------------+--------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+-------------+
| Table | Non_unique | Key_name           | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression  |
+-------+------------+--------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+-------------+
| t1    |          1 | functional_index   |            1 | NULL        | A         |           0 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | (`a` + `b`) |
| t1    |          1 | functional_index_2 |            1 | NULL        | A         |           0 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | (`a` + `b`) |
| t1    |          1 | functional_index_2 |            2 | NULL        | A         |           0 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | (`a` - `b`) |
| t1    |          1 | functional_index_3 |            1 | NULL        | A         |           0 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | (`a` + `b`) |
| t1    |          1 | functional_index_3 |            2 | a           | A         |           0 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL        |
+-------+------------+--------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+-------------+
5 rows in set (0.01 sec)

限制

主键上无法建functional index
可以混合普通key和functional key
表达式需要定义在括号()内，但类似这样INDEX ((col1), (col2))定义是不允许的
functional key不允许选做外键
Spatial/Fulltext index不允许functional key
如果列被某个functional index引用，需要先删除索引，才能删列
不允许直接引用列前缀，但可以通过函数substring/cast来workaround
对于一个unique的functional index，不能隐式转换为表的主键

root@test 10:27:48>CREATE TABLE tb (col longtext, key(col));
ERROR 1170 (42000): BLOB/TEXT column 'col' used in key specification without a key length
root@test 10:28:15>CREATE TABLE tb (col longtext, key((substring(col, 1, 10)));
Query OK, 0 rows affected (0.12 sec)

实现思路

该特性的实现思路是针对索引上被括号包围的表达式建立隐藏的虚拟列（virtual generated column），并在虚拟列上创建索引，这些功能早已经存在了，因此这个worklog主要做了几件事情：

语法支持

扩展新的语法，允许在创建索引时使用表达式
索引内的表达式被翻译成创建列的操作(Create_field), 索引上每个表达式各对应一个虚拟列

自动创建虚拟列

这个功能的核心就是讲索引创建引用的表达式转换成虚拟列并隐藏处理，因此在创建索引之前要进行预处理，入口函数

mysql_prepare_create_table
    -->add_functional_index_to_create_list

在获得表达式后，需要根据表达式来推导列类型，由于代码中已经有为create table as select推导列类型, 所以这里复用了其中的代码，单独抽出来函数create_table_from_items中的代码refactor到Create_field *generate_create_field中

虚拟列的命名为计算 md5(index name + key part number), 参考函数: make_functional_index_column_name
如上例：

root@test 12:10:02>SET SESSION debug="+d,show_hidden_columns";
Query OK, 0 rows affected (0.00 sec)

root@test 12:12:54>show create table t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL,
  `c` int(11) DEFAULT NULL,
  `3bb8c14d415110ac3b3c55ce9108ae2d` bigint(12) GENERATED ALWAYS AS ((`a` + `b`)) VIRTUAL,
  `0d1cbc68e8957783288d2b71268047c7` bigint(12) GENERATED ALWAYS AS ((`a` + `b`)) VIRTUAL,
  `0d8d996e0f781cf4e749dfa71efc17ba` bigint(12) GENERATED ALWAYS AS ((`a` - `b`)) VIRTUAL,
  `e0a812eddbaed00becd72bf920eccab8` bigint(12) GENERATED ALWAYS AS ((`a` + `b`)) VIRTUAL,
  KEY `functional_index` (((`a` + `b`))),
  KEY `functional_index_2` (((`a` + `b`)),((`a` - `b`))),
  KEY `functional_index_3` (((`a` + `b`)),`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

隐藏虚拟列

dd::Column::enum_hidden_type m_hidden: 增加该属性，用于区分是用户定义的generated column还是自动生成的。据此判断该列是否能够对用户可见或者是否可以被删除等等

  enum class enum_hidden_type {
    /// The column is visible (a normal column)
    HT_VISIBLE = 1,
    /// The column is completely invisible to the server
    HT_HIDDEN_SE = 2,
    /// The column is visible to the server, but hidden from the user.
    /// This is used for i.e. implementing functional indexes.
    HT_HIDDEN_SQL = 3
  };

增加两个接口函数来判断是否是隐藏列：

is_field_for_functional_index(), 用于：

"ALTER TABLE tbl DROP COLUMN;" 当列是隐藏列时，会抛出错误, ref: is_field_used_by_functional_index
Item_field::print() : 打印列的表达式而非列名, ref: get_field_name_or_expression

is_hidden_from_user()

"INSERT INTO tbl;" without a column list, ref: insert_fields, Sql_cmd_insert_base::prepare_inner()
"SELECT * FROM tbl;"
"SHOW CREATE TABLE tbl;" and "SHOW FIELDS FROM tbl;",  ref : store_create_info

DDL:

prepare_create_field()
-当尝试加一个和隐藏列相同名字的列时，抛出错误

创建索引名：
当用户未指定列名时，server会自动创建列名，对于functional index和普通索引不太一样：因为列名是索引名和在索引上的key number产生的hash值，因此必须在生成虚拟列之前产生索引名.

mysql_alter_table:

当删除索引时，相应的隐藏虚拟列也必须删除, ref: handle_drop_functional_index
当rename索引名时，隐藏列名也必须重新计算并重命名, ref: handle_rename_functional_index

报错

Functional_index_error_handler：隐藏虚拟列上的错误或warning信息, 转换成索引错误信息

参考文档

1.WL#1075: Add support for functional indexes
2.官方文档
 3.相关代码