函数索引 Functional Key Parts
A “normal” index indexes column values or prefixes of column values. MySQL 8.0.13 and higher supports functional key parts that index expression values rather than column or column prefix values. Use of functional key parts enables indexing of values not stored directly in the table.
在mysql以外的版本中，一个普通索引，只索引列值本身或者列的前缀，如：
CREATE TABLE t1 (
col1 VARCHAR(10),
col2 VARCHAR(20),
INDEX (col1, col2(10))
);
而mysql在8.0.13及其之后的版本中，增加了函数索引函数索引（functional key parts），也即将表达式的值作为索引的内容，而不是列值本身或列值前缀。如：
create table t1 (id int primary key auto_increment,`create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间');
alter table t1 add index `fun_create_time` ((cast(`create_time` as date)))
## 或者
alter table idx_test add index fun_create_time((date(create_time)));
上面的例子中，cast(create_time
as date)的含义是将create_time字段由datetime转变为date类型后的值作为索引存储。
这样做的好处是，当查询条件使用where date(create_time) = '20230406'时，能用上该索引。这其实是个比较优秀的特性，因为我们知道以往在字段上使用函数是无法走上索引的。
测试一下，
mysql> show create table t1;
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t1 | CREATE TABLE `t1` (
`id` int NOT NULL AUTO_INCREMENT,
`create_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
KEY `fun_create_time` ((cast(`create_time` as date)))
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb3 |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> explain select * from t1 where date(create_time) = '20230406';
+----+-------------+-------+------------+------+-----------------+-----------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+-----------------+-----------------+---------+-------+------+----------+-------+
| 1 | SIMPLE | t1 | NULL | ref | fun_create_time | fun_create_time | 4 | const | 1 | 100.00 | NULL |
+----+-------------+-------+------------+------+-----------------+-----------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
可以看到date(create_time)仍然可以用上索引。然后我们将这个函数索引改成普通索引试下。
mysql> alter table t1 drop index `fun_create_time`;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> alter table t1 add index `fun_create_time` (`create_time`);
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> explain select * from `t1` where date(create_time) = '20230406';
+----+-------------+------------------------------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------------------------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | t1 | NULL | ALL | NULL | NULL | NULL | NULL | 20 | 100.00 | Using where |
+----+-------------+------------------------------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
可以看到此时，date(create_time)是无法用上索引的。
创建函数索引的规则
创建函数索引需要遵循一些规则。如果创建时违反了规则，创建索引时将会产生错误。
在索引定义中，需要将表达式放入括号之中，以便与列值索引或者前缀索引进行区分。例如：
INDEX ((col1 + col2), (col3 - col4))
`fun_create_time` ((cast(`create_time` as date)))
函数索引不能只包含一个单独的列名。例如，以下写法是错误的：
INDEX ((col1), (col2))
-函数索引中的表达式不能使用列的前缀。可以使用 SUBSTRING() 和 CAST() 函数作为一个替代方案。例如：
CREATE TABLE tbl (
col1 LONGTEXT,
INDEX idx1 ((SUBSTRING(col1, 1, 10)))
);
需要注意的是，如果使用 SUBSTRING() 函数定义索引列，要想在查询中使用该索引，必须在WHERE 子句中使用同样的 SUBSTRING() 函数和参数。例如如下示例中，只有第2个语句可以用到索引：
SELECT * FROM tbl WHERE SUBSTRING(col1, 1, 9) = '123456789';
SELECT * FROM tbl WHERE SUBSTRING(col1, 1, 10) = '1234567890';
外键不支持函数索引。
函数索引还有一些其他的特性，大家感兴趣可以去官网查看相关文档。
参考文档：
https://dev.mysql.com/doc/refman/8.0/en/create-index.html#create-index-functional-key-parts https://blog.csdn.net/horses/article/details/85059678 https://www.modb.pro/db/99257
点个“赞 or 在看” 你最好看！
👇👇👇 咔片谢谢各位老板啦！！！
