MySQL 函数索引 (Functional indexes)

MySQL 8.0.13 版本开始支持函数索引（Functional indexes），在此之前，只能使用表的字段或者字段前缀来创建索引，从 8.0.13 版本开始，可以使用函数或者表达式的值来创建索引，这些函数或者表达式的值并不直接存储在表中。

函数索引示例：

CREATE TABLE t1 (col1 INT, col2 INT, INDEX func_index ((ABS(col1))));
CREATE INDEX idx1 ON t1 ((col1 + col2));
CREATE INDEX idx2 ON t1 ((col1 + col2), (col1 - col2), col1);
ALTER TABLE t1 ADD INDEX ((col1 * 40) DESC);


涉及多个字段的索引，可以混合使用函数索引和非函数索引，如下：

alter table sbtest1 add index idx_name_age(name, (age+1));


函数索引定义时，可以使用 DESC， ASC 指定其排序方式，如下：

alter table sbtest1 add index idx_1(name desc, (age*10) asc );


函数索引的限制条件：

函数索引实际上是作为一个隐藏的虚拟列实现的，因此其很多限制与虚拟列相同，如下：

• 函数索引的字段数量受到表的字段总数限制
• 函数索引能够使用的函数与虚拟列上能够使用的函数相同
• 子查询，参数，变量，存储过程，用户定义的函数不允许在函数索引上使用
• 虚拟列本身不需要存储，函数索引和其他索引一样需要占用存储空间
• 函数索引可以使用 UNIQUE  标识，但是主键不能使用函数索引，主键要求被存储，但是函数索引由于其使用的虚拟列不能被存储，因此主键不能使用函数索引
• 如果表中没有主键，那么 InnoDB 将会使其非空的唯一索引作为主键，因此该唯一索引不能定义为函数索引
• 函数索引不允许在外键中使用
• 空间索引和全文索引不能定义为函数索引
• 对于非函数的索引，如果创建相同的索引，将会有一个告警信息，而函数索引则不会
• 如果一个字段被用于函数索引，那么删除该字段前，需要先删除该函数索引，否则删除该字段会报错
• 非函数索引支持对字段前缀进行索引，函数索引不支持前缀。可以使用 SUBSTRING() 或者 CAST() 。但是查询 SQL 中的参数必须与函数索引定义时的参数完全相同才能使用该索引，如下示例：

CREATE TABLE tbl (
col1 LONGTEXT,
INDEX idx1 ((SUBSTRING(col1, 1, 10)))
);
SELECT * FROM tbl WHERE SUBSTRING(col1, 1, 9) = '123456789';
SELECT * FROM tbl WHERE SUBSTRING(col1, 1, 10) = '1234567890';


SUBSTRING(col1, 1, 9) 无法使用函数索引，SUBSTRING(col1, 1, 10) 可以使用函数索引。