mysql 虚拟索引,给MySQL做虚拟的“函数索引”

本文内容遵从CC版权协议, 可以随意转载, 但必须以超链接形式标明文章原始出处和作者信息及版权声明网址: http://www.penglixun.com/tech/database/mysql_virtual_function_index.html

对于带有列的函数计算的SQL，MySQL是无法使用索引的，MySQL并没有Oracle中的函数索引，

例如：

SELECT *

FROM table_1

WHERE func_1(col_1) < const_1

AND func_2(col_2) = const_2

ORDER BY func_1(col_1);

在Oracle中，我们可以建里组合函数索引：

CREATE INDEX idx_1 ON table_1 ( func_2(col_2), func_1(col_1));

但是在MySQL中，没有提供函数索引，怎么办？就此罢休？存在这样的需求，就得去解决，遂想到使用冗余+触发器来虚拟函数索引。

首先，利用新列来存储函数计算的结果，增加col_1_f1，col_2_f2两个列分别存储col_1/col_2的函数计算结果，然后写2个触发器，分别处理INSERT和UPDATE的情况。

CREATE TRIGGER trg_f1 BEFORE UPDATE

ON verycd FOR EACH ROW

BEGIN

SET NEW.col_1_f1=func_1(NEW.col_1);

SET NEW.col_2_f2=func_2(NEW.col_2);

END;

CREATE TRIGGER trg_f1 BEFORE INSERT

ON verycd FOR EACH ROW

BEGIN

SET NEW.col_1_f1=func_1(NEW.col_1);

SET NEW.col_2_f2=func_2(NEW.col_2);

END;

然后我们对col_1_f1，col_2_f2两个列建索引：

CREATE INDEX idx_1 ON table_1 ( col_2_f2，col_1_f1 );

将查询语句改为：

SELECT *

FROM table_1

WHERE col_1_f1 < const_1

AND col_2_f2 = const_2

ORDER BY col_1_f1;

这就实现了虚拟的“函数索引”，从这样看函数索引的实现并不复杂，MySQL只要在B-Tree中记录函数计算后的结果作为建树关键字就能实现，不知道MySQL为什么不做呢？

欢迎拍砖~