前言
与数百个不同的客户打交道时，我经常在查询方面遇到类似的问题。 在尝试优化数据库环境时，一个非常常见的问题是索引使用。 无法使用索引的查询通常是长时间运行的查询，会消耗更多内存或触发更多磁盘 iops。
一个非常常见的情况是查询对涉及某种函数表达式的列使用过滤条件。无法使用该列上的索引。
从 MySQL 8.0.13 开始支持函数索引。在本文中，我将展示它们是什么以及它们是如何工作的。
众所周知的问题
如前所述，关于索引使用的一个非常常见的问题是，当您使用函数表达式作为过滤条件时。
让我们看一个简单的例子。
您有一个名为 products 的表，其中包含产品的详细信息，包括 create_time TIMESTAMP 列。如果您想计算产品在特定月份的平均价格，您可以执行以下操作：
mysql> SELECT AVG(price) FROM products WHERE MONTH(create_time)=10;
+------------+
| AVG(price) |
+------------+
| 202.982582 |
+------------+
查询返回正确的值，但请看一下 EXPLAIN：
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
再次全表扫描。我们创建的索引无效。事实上，任何时候涉及到函数的索引列，索引都不能使用。
为了优化查询，解决方法是以不同的方式重写该SQL，以便将索引列与函数隔离开来。
让我们测试以下等效查询：
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
很棒，现在索引被使用了。然后重写查询是典型的建议。
相当简单的解决方案，但并非所有时候都可以出于许多正当理由更改应用程序代码。那么，那该怎么办呢？
MySQL 8.0 函数索引
从版本 8.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
现在让我们检查一下我们的原始查询，看看 EXPLAIN 会发生什么
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
查询不再是全表扫描，运行速度更快。已使用函数索引，仅检查了 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 )
您还应该注意以下限制：
- 功能键不能包含单个列。以下是不允许的： 索引（（col1），（col2））
- 主键不能包含功能键部分
- 外键不能包含功能键部分
- SPATIAL 和 FULLTEXT 索引不能包含功能键部分
- 功能键部分不能引用列前缀
例如，以下条件不能依赖我们创建的函数索引：
- WHERE YEAR(create_time) = 2019
- WHERE create_time > ‘2019-10-01’
- WHERE create_time BETWEEN ‘2019-10-01’ AND ‘2019-11-01’
- WHERE MONTH(create_time+INTERVAL 1 YEAR)
所有这些都将触发全表扫描。
功能索引内部
函数索引被实现为隐藏的虚拟生成列。因此，即使在 MySQL 5.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
事实上，行为是相同的。可以使用虚拟列上的索引，优化查询。
好消息是，即使在 5.7 上，您也可以使用此解决方法来模拟函数索引，获得相同的好处。 MySQL 8.0 的优势是：函数索引对用户完全透明，不需要创建虚拟列。
由于函数索引是被实现为隐藏的虚拟列，因此数据不需要额外的空间，只会将索引空间添加到表中。
顺便说一句，这与用于在 JSON 文档的字段上创建索引的技术相同。
结论
函数索引支持是 MySQL 8.0 中一个有趣的改进。一些需要重写代码以优化的查询的方式将不再需要了。请记住，只有具有相同过滤模式的查询才能依赖函数索引。然后您需要创建额外的索引或其他函数索引来改进其他查询模式。
通过显式创建虚拟生成列和索引，可以在 MySQL 5.7 上实现相同的功能。
有关更多详细信息，请阅读以下页面：
https://dev.mysql.com/doc/refman/8.0/en/create-index.html#create-index-functional-key-parts
