MySQL8中的函数索引 _索引

宝剑锋从磨砺出，梅花香自苦寒来。这篇文章主要讲述MySQL8中的函数索引相关的知识，希望能为你提供帮助。
最近翻了下percona blog，发现 mysql从8.0.13也引入了函数索引了，这个特性貌似在PG很早就具备了。。。
在5.7中，我们可以使用虚拟列来实现函数索引的效果。MySQL 8.0 的优点是完全透明，不需要创建虚拟列。

看下面的示例

DROP TABLE products ;

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`)
) ENGINE = INNODB AUTO_INCREMENT = 149960 DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci;

查看下执行计划，可以看到走的是全表扫描
DESC SELECT AVG(price) FROM products WHERE MONTH(create_time)=10;
idselect_typeTABLEPARTITIONSTYPEpossible_keysKEYkey_lenrefROWSfilteredExtra
----------------------------------------------------------------------------------------------------
1SIMPLEproducts(NULL)ALL(NULL)(NULL)(NULL)(NULL)1100.00USING WHERE

然后，对 create_time加一个索引，试试看效果
ALTER TABLE products ADD INDEX idx_ctime (create_time);

执行计划上看，依然是全表扫描
DESC SELECT AVG(price) FROM products WHERE MONTH(create_time)=10;
idselect_typeTABLEPARTITIONSTYPEpossible_keysKEYkey_lenrefROWSfilteredExtra
----------------------------------------------------------------------------------------------------
1SIMPLEproducts(NULL)ALL(NULL)(NULL)(NULL)(NULL)1100.00USING WHERE

下面开始再加一个函数索引，注意这里的语法，有2层的括号！
ALTER TABLE products ADD INDEX idx_m_ctime ((MONTH(create_time)));

SHOW CREATE TABLE products \\G
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 `idx_m_ctime` ((MONTH(`create_time`)))
) ENGINE=INNODB AUTO_INCREMENT=149960 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

再次查看执行计划，可以看到走索引了：

DESC SELECT AVG(price) FROM products WHERE MONTH(create_time)=10;
idselect_typeTABLEPARTITIONSTYPEpossible_keysKEYkey_lenrefROWSfilteredExtra
----------------------------------------------------------------------------------------------------
1SIMPLEproducts(NULL)refidx_m_ctimeidx_m_ctime5const1100.00(NULL)

允许使用哪些函数索引

我们已经看到了一个涉及应用于列的简单函数的示例，但是您可以创建更复杂的索引。

函数索引可以包含任何类型的表达式，而不仅仅是单个函数。以下模式是有效的功能索引：
INDEX( ( col1 + col2 ) )
INDEX( ( FUNC(col1) + col2 – col3 ) )

You can use ASC or DESC as well:
INDEX( ( MONTH(col1) ) DESC )

You can have multiple functional parts, each one included in parentheses:
INDEX( ( col1 + col2 ), ( FUNC(col2) ) )

You can mix functional with nonfunctional parts:
INDEX( (FUNC(col1)), col2, (col2 + col3), col4 )

还有些需要注意的地方:
- A functional key can not contain a single column. The following is not permitted: INDEX( (col1), (col2) )
- The primary key can not include a functional key part
- The foreign key can not include a functional key part
- SPATIAL and FULLTEXT indexes can not include functional key parts
- A functional key part can not refer to a column prefix

最后，请记住，函数索引仅对优化使用完全相同的函数表达式的查询有用。下面的这几个都不会走我们创建的month的函数索引，依然会需要全表扫描。当然我们也可以创建多个函数索引来解决多个不同的查询问题。

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)

补充，目前YearningSQL平台还不支持函数索引的工单的校验和提交，需要走其它流程，由DBA人工执行。

Ref：
【MySQL8中的函数索引】?? https://www.percona.com/blog/mysql-8-0-functional-indexes/??
官方文档：https://dev.mysql.com/doc/refman/8.0/en/create-index.html