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
  };           

增加兩個接口函數來判斷是否是隐藏列：

1. 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           

1. 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.相關代碼