本次主要聊一聊关于函数到底会不会导致索引失效呢？

很多人认为，在使用函数后就无法使用索引。

这主要是因为索引是按照列值的原始顺序组织和存储的。当对列应用函数时（如数学运算、字符串操作或日期函数等），函数会改变原始数据的值或格式，使得数据库无法直接定位到这些经过函数转换后的值。因此，数据库不得不执行全表扫描，以确保能够评估所有行上的函数操作，这导致查询性能下降。

在 MySQL 8.0 之后，引入了函数索引，这改变了以往对函数使用索引的限制。

MySQL 8.0 引入了功能索引（Functional Indexes）的新特性，这种索引允许在创建时包含列上的表达式。这意味着可以对数据进行计算或转换，并针对结果建立索引。即使在查询中使用了函数操作，也可以利用这些索引来优化查询性能。

功能索引不是直接在表的列上创建的，而是基于列的某个表达式创建的。这个表达式可以是简单的数学运算，也可以是字符串函数、日期函数等。一旦创建了功能索引，MySQL 在执行涉及该表达式的查询时能够使用这个索引，从而提升查询效率。

使用方式

在 MySQL 8.0 中，您可以创建一个基于 first_name 和 last_name 合并后的表达式的功能索引，示例如下：

plsql

复制代码

CREATE INDEX full_name_index ON employees ((CONCAT(first_name, ' ', last_name)));

这个例子中，我们使用了 CONCAT 函数将 first_name 和 last_name 合并成一个全名，并在创建索引时使用了这个表达式 (CONCAT(first_name, ' ', last_name))。 这样一来，即使在查询中直接使用全名的合并结果，MySQL 也能够利用 full_name_index 索引来优化查询性能。

plsql

复制代码

SELECT * FROM employees WHERE CONCAT(first_name, ' ', last_name) = 'Pai daxing';

如上图所示，执行计划显示我们成功利用了 idx_full_name索引！

因此，即使在查询的 WHERE 子句中使用了 CONCAT 函数，我们依然能够充分利用 idx_full_name 函数索引，从而有效提升了查询的效率。

注意事项

函数索引虽然能够显著提高涉及索引表达式的查询性能，但其创建可能增加数据插入、更新和删除的成本，因为数据库需要维护更多的索引数据。因此，在创建函数索引时需要谨慎，不能盲目使用。

使用函数索引时，必须仔细考虑和测试，以确保性能提升符合预期。

此外，在创建函数索引时，需要确保表达式是确定的，即对于给定的输入值始终产生相同的输出值。非确定性表达式不适合用于函数索引。

常见函数索引用法

给大家列举一些常见的函数索引的使用。

字符串处理

当您经常需要根据某个字符串列的某部分进行查询时，可以考虑使用函数索引。例如，如果您希望根据邮箱地址的域名部分查询用户，可以创建如下的函数索引：

plsql

复制代码

CREATE INDEX idx_email_domain ON users ((SUBSTRING_INDEX(email, '@', -1)));

这样，当您查询特定域名的邮箱时（例如 qq 邮箱），可以利用这个索引：

plsql

复制代码

SELECT * FROM users WHERE SUBSTRING_INDEX(email, '@', -1) = 'qq.com';

日期和时间处理

对于涉及日期和时间处理的查询，函数索引也非常有用。假设您需要频繁查询基于订单日期的年份或月份，可以创建如下的索引：

plsql

复制代码

CREATE INDEX idx_order_year ON orders ((YEAR(order_date)));CREATE INDEX idx_order_month ON orders ((MONTH(order_date)));

这允许您高效地查询特定年份或月份的订单：

plsql

复制代码

SELECT * FROM orders WHERE YEAR(order_date) = 2022;SELECT * FROM orders WHERE MONTH(order_date) = 12;

数学运算

如果查询条件经常包含对数值列的数学运算，可以针对这些运算创建函数索引。例如，如果您希望根据产品折扣后的价格进行查询，可以创建如下索引：

sql

复制代码

CREATE INDEX idx_discounted_price ON products ((price * (1 - discount_rate)));

然后，您可以高效地查询特定范围的折扣价格：

sql

复制代码

SELECT * FROM products WHERE price * (1 - discount_rate) BETWEEN 50 AND 100;

使用 JSON 函数

如果在 MySQL 中使用 JSON 数据类型，并且需要基于 JSON 属性进行查询，可以创建基于 JSON 函数的索引。例如，如果您有一个存储 JSON 数据的列，并且希望根据 JSON 文档中的某个键进行查询，可以创建如下索引：

plsql

复制代码

CREATE INDEX idx_json_key ON orders ((JSON_UNQUOTE(JSON_EXTRACT(order_info, '$.status'))));

这样，您可以高效地查询具有特定状态的订单：

plsql

复制代码

SELECT * FROM orders WHERE JSON_UNQUOTE(JSON_EXTRACT(order_info, '$.status')) = 'shipped';

大小写不敏感的搜索

如果您需要执行大小写不敏感的字符串搜索，可以创建一个基于 LOWER() 或 UPPER() 函数的索引：

sql

复制代码

CREATE INDEX idx_lower_case_name ON customers ((LOWER(name)));

这样可以让您执行大小写不敏感的搜索，同时不影响性能：

sql

复制代码

SELECT * FROM customers WHERE LOWER(name) = LOWER('John Doe');

在使用函数索引时，需要权衡索引的维护成本和性能提升。虽然函数索引可以显著提升特定查询的性能，但它们也会增加插入、更新和删除操作的成本，因为数据库需要维护更多的索引数据。因此，在实际应用中，建议仅对那些经常作为查询条件的列和表达式创建函数索引。