MySQL是否支持基于函数的索引？

Question

MySQL是否支持基于函数的索引？

mysqlindexing

73

我记得在 Oracle 中可以根据函数进行索引，例如 SUBSTRING(id,1,8)。

MySQL 支持吗？如果不支持，是否有其他替代方案？

- user836026

你的意思是跳过第一个字符？我不认为这是可能的 :) - Ja͢ck

4个回答

55

MySQL自8.0.13版本起支持此功能。

https://dev.mysql.com/doc/refman/8.0/en/create-index.html#create-index-functional-key-parts

MySQL 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);

带有多个关键部分的索引可能会混合非功能性和功能性关键部分。

对于8.0.13之前的版本，有以下替代方案：

1. 自MySQL 5.7.6起

您可以使用自动生成的列来保存带有索引的子字符串：

CREATE TABLE SomeTable (
    id CHAR(10),
    sub_id CHAR(8) AS SUBSTRING(id, 1, 8) STORED, INDEX(sub_id)
)

正如Benjamin指出的那样，InnoDB支持虚拟列上的二级索引，因此STORED关键字可以省略。实际上，虚拟列上的二级索引可能更可取。更多信息请参见：Secondary Indexes and Generated Columns

2. 在MySQL 5.7.6之前

您可以使用由触发器更新并带有索引的列：

CREATE TABLE SomeTable (
    id CHAR(10),
    sub_id CHAR(8) , INDEX(sub_id)
);

CREATE TRIGGER TR_SomeTable_INSERT_sub_id
    BEFORE INSERT
    ON SomeTable FOR EACH ROW 
    SET NEW.sub_id = SUBSTRING(NEW.id, 1, 8);

CREATE TRIGGER TR_SomeTable_UPDATE_sub_id
    BEFORE UPDATE
    ON SomeTable FOR EACH ROW 
    SET NEW.sub_id = SUBSTRING(NEW.id, 1, 8);

- axxis

1

根据文档的说明，InnoDB支持虚拟列上的二级索引。因此，您可以省略STORED关键字使列变为虚拟列，节省一些存储空间，并仍然具有数据索引！ - BenMorel

1

更新：MySQL 8.0已发布，但仍然没有本地SQL表达式或函数索引可用。因此，您仍然需要使用生成的列索引作为解决方法。 - Raymond Nijland

这会影响 LOAD_INFILE 过程吗，因为有一个新的虚拟列？ - Khom Nazid

18

MySQL 5.7.5版本起，可以通过新的生成列实现此功能。

- Gabe Martin-Dempesy

3

更新：MySQL 8.0已经发布，但仍然没有本地SQL表达式或函数索引可用。因此，您仍需要使用生成的列索引作为解决方法。 - Raymond Nijland

4

是的，我们可以在MySQL中创建功能性索引。此功能从MySQL 8.0.13开始提供。（其他RDBMS在早期版本中已经具备此功能，但MySQL在其8.0.13版本中引入了它）。功能性索引这里我给出了在MySQL 8.0中创建功能性索引的示例。

这是查询语句。

SELECT * FROM app_user WHERE month(createdOn) = 5;

mysql> SELECT * FROM app_user WHERE month(createdOn) = 5;
7e9e2b7bc2e9bde15504f6c5658458ab - 74322 rows in set (5.01 sec)

即使我在createdOn列上创建了索引（通过month()函数掩盖），执行检索74322条记录的时间仍超过5秒。

现在，我使用以下语法在该列上创建了一个功能性索引。

mysql> ALTER TABLE app_user ADD INDEX
         idx_month_createdon((month(createdOn)));
Query OK, 0 rows affected (1 min 17.37 sec) Records: 0 Duplicates: 0
Warnings: 0

mysql> SELECT * FROM app_user WHERE month(createdOn) = 5;
7e9e2b7bc2e9bde15504f6c5658458ab - 74322 rows in set (0.29 sec)

创建了功能索引后，执行时间为0.29秒。

在Mysql网站中很难找到功能索引文档，它的名称为functional key parts。

我们无法删除具有功能索引的列。要实现这一点，我们需要首先删除功能索引。

mysql> ALTER TABLE app_user DROP COLUMN createdOn;
Cannot drop column 'createdOn' because it is used by a functional
index. In order to drop the column, you must remove the functional
index.

如果您使用的是MySQL > 5.7.5，您可以使用生成列来实现相同的功能。

- aakash muthuramalingam

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- paxdiablo · Accepted Answer

不，就一般情况而言，我认为即使在编写此回答时的最新版本5.6也没有这种功能。值得注意的是，8.0.13及以上版本现在支持函数索引，允许你实现下面描述的触发器方法所需的功能。

如果你正在运行旧版本的mysql，只使用列的前导部分是可以实现的（这个功能已经存在很长时间了），但不支持以第二个或后续字符开头或其他更复杂的函数。

例如，以下代码将使用名称的前五个字符创建索引：

create index name_first_five on cust_table (name(5));

对于更复杂的表达式，您可以通过创建一个包含可索引数据的另一个列，然后使用插入/更新触发器来确保其正确填充，从而实现类似的效果。

除了冗余数据的浪费空间之外，这基本上是相同的。

虽然它在技术上违反了第三范式，但通过使用触发器来保持数据同步来缓解这种情况（这通常是为了提高性能而做的）。