MySQL 8 -- Functional Indexes

一个常见的场景是查询对涉及某种函数表达式的列使用过滤条件，这种情况下，无法使用该列上的索引。

从MySQL8.0.13开始支持函数索引。

假设有个产品信息表products，包含一个列create_time，类型是timestamp。如果想统计某个月内，产品的平均价格，你可以这样写：

?

1 2 3 4 5 6

mysql> SELECT AVG(price) FROM products WHERE MONTH(create_time)=10; +------------+ | AVG(price) | +------------+ | 202.982582 | +------------+

查询返回了正确的结果。但是如果查看执行计划会发现：

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14

mysql> EXPLAIN SELECT AVG(price) FROM products WHERE MONTH(create_time)=10\G *************************** 1. row ***************************            id: 1   select_type: SIMPLE         table: products    partitions: NULL          type: ALL possible_keys: NULL           key: NULL       key_len: NULL           ref: NULL          rows: 99015      filtered: 100.00         Extra: Using where

查询走的是全表扫描。

现在我们在create_time列上创建一个索引：

?

1 2 3

mysql> ALTER TABLE products ADD INDEX(create_time); Query OK, 0 rows affected (0.71 sec) Records: 0  Duplicates: 0  Warnings: 0

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14

mysql> explain SELECT AVG(price) FROM products WHERE MONTH(create_time)=10\G *************************** 1. row ***************************            id: 1   select_type: SIMPLE         table: products    partitions: NULL          type: ALL possible_keys: NULL           key: NULL       key_len: NULL           ref: NULL          rows: 99015      filtered: 100.00         Extra: Using where

仍然是全表扫描，创建的索引没有效果。

 

为了优化整个查询，需要重写语句，才能使用上索引。

?

1 2 3 4 5 6

mysql> SELECT AVG(price) FROM products WHERE create_time BETWEEN '2019-10-01' AND '2019-11-01'; +------------+ | AVG(price) | +------------+ | 202.982582 | +------------+

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14

mysql> EXPLAIN SELECT AVG(price) FROM products WHERE create_time BETWEEN '2019-10-01' AND '2019-11-01'\G *************************** 1. row ***************************            id: 1   select_type: SIMPLE         table: products    partitions: NULL          type: range possible_keys: create_time           key: create_time       key_len: 5           ref: NULL          rows: 182      filtered: 100.00         Extra: Using index condition

现在可以使用上索引了。

很简单的一个解决方案，但并不是任何时候都可以这样修改代码的。

 

从MySQL8.0.13开始，MySQL开始支持函数索引了。

来创建一个函数索引：

?

1 2 3

mysql> ALTER TABLE products ADD INDEX((MONTH(create_time))); Query OK, 0 rows affected (0.74 sec) Records: 0  Duplicates: 0  Warnings: 0

注意这里是双括号。

语法是正确的，因为表达式必须用括号括起来以将其与列或列前缀区分开来。

不然会报错：

?

1 2	mysql> ALTER TABLE products ADD INDEX(MONTH(create_time)); ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'create_time))' at line 1

再来执行上面的查询：

?

1 2 3 4 5 6

mysql> SELECT AVG(price) FROM products WHERE MONTH(create_time)=10; +------------+ | AVG(price) | +------------+ | 202.982582 | +------------+

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14

mysql> EXPLAIN SELECT AVG(price) FROM products WHERE MONTH(create_time)=10\G *************************** 1. row ***************************            id: 1   select_type: SIMPLE         table: products    partitions: NULL          type: ref possible_keys: functional_index           key: functional_index       key_len: 5           ref: const          rows: 182      filtered: 100.00         Extra: NULL

查询变快了，也不再走全表扫描了。函数索引functional_index被使用了。只检索了182行记录。

 

哪些函数索引被支持

函数索引可以包含各种表达式。比如：

·index((col1+col2))

·index((func(col1)+col2-col3))

 

甚至可以使用asc、desc。如：

·index( (month(col1)) desc)

 

可以包含多个函数部分，每个都用括号括起来：

·index( ( col1 + col2 ), ( FUNC(col2) ) )

·index((func(col1)), col2, (col2+col3), col4)

 

当然也存在一些限制：

·函数键不能只是包含单个列，比如index((col1), (col2))是不被支持的

·主键不能被包含在函数键中
·外键不能被包含在函数键中

·spatial和fulltext索引不能被包含在函数键中

·函数键不能引用列的前缀

 

函数索引的内部原理

函数索引是通过

函数索引是以隐藏的虚拟生成列(generated columns)的方式实现的。因此，可以在MySQL5.7上，模拟函数索引，通过显式的虚拟列。

这里可以做一个测试，先删除已经创建的索引。

?

1 2 3 4 5 6 7 8 9 10 11 12

mysql> SHOW CREATE TABLE products\G *************************** 1. row ***************************        Table: products Create Table: CREATE TABLE `products` (   `id` int unsigned NOT NULL AUTO_INCREMENT,   `description` longtext,   `price` decimal(8,2) DEFAULT NULL,   `create_time` timestamp NULL DEFAULT NULL,   PRIMARY KEY (`id`),   KEY `create_time` (`create_time`),   KEY `functional_index` ((month(`create_time`))) ) ENGINE=InnoDB AUTO_INCREMENT=149960 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

?

1 2	mysql> ALTER TABLE products DROP INDEX `create_time`, DROP INDEX `functional_index`; Query OK, 0 rows affected (0.03 sec)

 

现在再来创建虚拟的生成列：

?

1 2	mysql> ALTER TABLE products ADD COLUMN create_month TINYINT GENERATED ALWAYS AS (MONTH(create_time)) VIRTUAL; Query OK, 0 rows affected (0.04 sec)

 

在虚拟列上创建索引：

?

1 2	mysql> ALTER TABLE products ADD INDEX(create_month); Query OK, 0 rows affected (0.55 sec)

?

1 2 3 4 5 6 7 8 9 10 11 12

mysql> SHOW CREATE TABLE products\G *************************** 1. row ***************************        Table: products Create Table: CREATE TABLE `products` (   `id` int unsigned NOT NULL AUTO_INCREMENT,   `description` longtext,   `price` decimal(8,2) DEFAULT NULL,   `create_time` timestamp NULL DEFAULT NULL,   `create_month` tinyint GENERATED ALWAYS AS (month(`create_time`)) VIRTUAL,   PRIMARY KEY (`id`),   KEY `create_month` (`create_month`) ) ENGINE=InnoDB AUTO_INCREMENT=149960 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

 

执行之前的查询，看看是否效果一样

?

1 2 3 4 5 6

mysql> SELECT AVG(price) FROM products WHERE MONTH(create_time)=10; +------------+ | AVG(price) | +------------+ | 202.982582 | +------------+

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14

mysql> EXPLAIN SELECT AVG(price) FROM products WHERE MONTH(create_time)=10\G *************************** 1. row ***************************            id: 1   select_type: SIMPLE         table: products    partitions: NULL          type: ref possible_keys: create_month           key: create_month       key_len: 2           ref: const          rows: 182      filtered: 100.00         Extra: NULL

一样的行为效果。

由于函数索引被实现为隐藏的虚拟列，因此数据不需要额外的空间，只会将索引空间添加到表中。

顺便说一句，这与用于在 JSON 文档的字段上创建索引的技术相同。