MySQL 8 -- Functional Indexes

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

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

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

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

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

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列上创建一个索引：

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

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

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

 

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

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

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开始支持函数索引了。

来创建一个函数索引：

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

注意这里是双括号。

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

不然会报错：

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

再来执行上面的查询：

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

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上，模拟函数索引，通过显式的虚拟列。

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

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

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

 

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

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

 

在虚拟列上创建索引：

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

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

 

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

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

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 文档的字段上创建索引的技术相同。