使用MySQL的函数索引

从MySQL 8.0.13起，开始支持函数索引功能，该功能可以很好的帮助开发人员或者DBA去优先生产环境的SQL语句。通常，我们是并建议在SQL语句的查询条件中对列进行任何的函数计算的，因为这种做很有可能导致原本可以使用索引的查询条件，变得无法使用索引。

例如，我们看如下的查询条件：

SELECT * FROM user_info WHERE substr(id_card_no,1,6) = '330106';

虽然，该表的id_card_no上原本是存在索引的，但是上面的写法则会导致SQL无法正常使用id_card_no列上的索引。一般来说，我们会建议开发人员，避免这种写法，更多的是将表达式放到右侧，如上的SQL则建议修改为：

SELECT * FROM user_info WHERE id_card_no like '330106%';

但某些情况下，我们可能无法修改SQL，例如：

有很多的函数计算或表达式操作并不能简单的转换到表达式的右边，例如：uid%1024 = 7
应用程序来自第三方，而我们并不拥有第三方的代码，也无法进行修改SQL
生产故障已经由此产生，可能来不及修改线上的代码或SQL
还有一个略微“牵强”的说法：SQL语法更加注重解释性，并不关注实现，基于此大原则，上面的SQL写法并没有什么问题。

函数索引使用示例

MySQL的函数索引是8.0版本引入的重要特性之一。它允许开发人员在查询中使用函数，并且依旧可以有效地加速查询性能。具体的，函数索引的作用是通过在函数表达式上创建索引，在处理字符串、日期、数值等类型的数据时特别有用。

使用函数索引的一个常见场景是对字符串列进行查询。例如，以前面的取身份证号码前六位为例子介绍如何使用函数索引。

假设，我们有如下表结构：

CREATE TABLE `t_001` (
  `id` int NOT NULL AUTO_INCREMENT,
  `id_card_no` varchar(32) NOT NULL,
  `gmt_created` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `IND_ID_CARD_NO` (`id_card_no`)
) ENGINE=InnoDB ;

这时，我们执行如下SQL：

SELECT * FROM t_001 WHERE substr(id_card_no,1,6) = '330101';

我们查看其执行计划如下：

可以看到，虽然有该表的id_card_no字段有索引，但是确无法被使用。如果，表特别的时候，需要全表扫描，性能就会非常差。这时，我们可以尝试使用函数索引。具体操作如下：

ALTER TABLE t_001 ADD KEY IND_FUNCTIONAL_ID_CARD_PREFIX((substr(id_card_no,1,6)))

然后，再次观察执行计划：

可以看到，这时候，查询就可以正常的使用该函数索引了。

使用时的一些注意事项

需要注意的是，函数索引也有一些限制和注意事项。首先，函数索引的创建必须基于已经存在的函数，且函数必须是确定性的，即对于相同的输入始终返回相同的输出。此外，函数索引的选择应谨慎，因为不适当的函数选择可能会导致索引无效，或者在查询时产生性能问题。

在实际使用中，我们应该根据具体的业务需求和查询模式来决定是否使用函数索引。在某些情况下，创建额外的虚拟列可能更适合，或者通过优化查询语句来避免使用函数索引。

总而言之，MySQL的函数索引功能为开发人员提供了更多灵活性和效率，可以加速复杂查询和特定数据操作。然而，在使用函数索引时需要注意选择合适的函数和索引类型，并根据具体情况进行性能测试和调优。掌握函数索引的原理和最佳实践，将有助于提高MySQL数据库的性能和响应速度。

一些注意事项：

函数索引创建时，需要使用表达式，而表达式是需要括号括起来的，所以，上面SQL语句的写法是：

ALTER TABLE t_001 ADD KEY IND_FUNCTIONAL_ID_CARD_PREFIX((substr(id_card_no,1,6)))

而不是：

-- 以下是错误的写法，少了一个括号。可以对比上面的写法
ALTER TABLE t_001 ADD KEY IND_FUNCTIONAL_ID_CARD_PREFIX(substr(id_card_no,1,6))

函数索引应该（这是猜测：参考）使用了虚拟列的实现方式，所以另一个解决此类问题的方式是建一个虚拟列，然后在该列上新建索引。

orczhou.com