MySQL8.0.13-函数索引

周杰伦的稻香

已于 2024-09-14 17:23:30 修改

阅读量332

点赞数 6

分类专栏： # MYSQL 文章标签： android

于 2024-09-14 17:21:40 首次发布

本文链接：https://blog.csdn.net/weixin_72610956/article/details/142263097

版权

MYSQL 专栏收录该内容

60 篇文章 0 订阅

订阅专栏

什么是函数索引

本篇主要介绍 MySQL 的函数索引（也叫表达式索引）。

通常来讲，索引都是基于字段本身或者字段前缀，而函数索引是基于字段本身加上函数、操作符、表达式等计算而来。如果将表达式或者操作符也看做函数的话，简单来说，这样的索引就可以统称函数索引。

MySQL 的函数索引内部是基于虚拟列（generated columns）实现，不同于直接定义虚拟列，函数索引自动创建的虚拟列本身实时计算结果，并不存储数据，只把函数索引本身存在磁盘上。

MySQL 8.0.13 之前不支持函数索引，所以老版本包括现在主流的 MySQL 5.7 也不支持函数索引，需要手工模拟创建或者改 SQL。

语法

创建函数索引的基本语法如下：

CREATE INDEX index_name ON table_name (expression);

或者，如果正在修改表并添加新的索引，可以使用：

ALTER TABLE table_name ADD INDEX index_name (expression);

这里的 expression 可以是列名、列名的函数（如 LOWER(column_name)）、列名的计算（如 column_name * 2）或其他有效的 SQL 表达式。

以下是一些创建函数索引的例子：

对字符串列的特定转换进行索引：

CREATE INDEX idx_lastname_lowercase ON customers (LOWER(last_name));

对日期列的特定格式进行索引：

CREATE INDEX idx_birthdate ON users (DATE(birthdate));

对数值列的计算结果进行索引：

CREATE INDEX idx_price_times_two ON products (price * 2);

对 JSON 列的路径表达式进行索引：

CREATE INDEX idx_json_column ON json_table (JSON_EXTRACT(json_column, '$.path.to.value'));

请注意，不是所有的函数都适合用于函数索引。函数必须是确定性的，这意味着对于相同的输入，函数总是返回相同的结果。此外，函数索引的使用也受到存储引擎的限制，例如，InnoDB 支持函数索引，而 MyISAM 不支持。

函数索引测试

创建表结构

CREATE TABLE idx_test (
    id INT AUTO_INCREMENT PRIMARY KEY,
    col1 INT,
    col2 INT,
    col3 VARCHAR(30),
    create_time DATETIME DEFAULT CURRENT_TIMESTAMP,
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

插入数据

insert into idx_test values (1,10,10,'helloworld','2021-01-01 00:00:00'),
(2,20,20,'hellochina','2021-02-02 00:00:00'),  
(3,30,30,'hellojap','2021-03-03 00:00:00'),
(4,40,40,'hellorus','2021-04-04 00:00:00'),
(5,50,50,'helloita','2021-05-05 00:00:00');

创建普通索引

alter table idx_test add index idx_create_time(create_time);

查看执行计划

explain select * from idx_test where date(create_time)='2021-01-01';

创建函数索引

alter table idx_test add index fun_create_time((date(create_time)));

查看执行计划

alter table idx_test add index fun_create_time((date(create_time)));

查看索引信息

show index from idx_test ;

函数索引效率

如果两条 sql 一个用到普通索引，一个用到函数索引，结果集一致，那哪个 sql 的开销会大点？

普通索引

#普通索引：
select * from idx_test where create_time > '2021-02-02 00:00:00' and create_time <='2021-03-03 00:00:00';

查看详细执行计划

explain format=json select * from idx_test where create_time > '2021-02-02 00:00:00' and create_time <='2021-03-03 00:00:00'\G

函数索引

#用到函数索引：
select * from idx_test where date(create_time)='2021-03-03';

查看详细执行计划

explain format=json select * from idx_test where date(create_time)='2021-03-03'\G

分析

普通索引的开销会大点，也比较符合预期，本来建立函数索引就是为了查询更为高效。总的来看，函数索引在特定场景还是很有用处的，只要严格遵守函数索引的定义去编写 sql，那就能大大减少不必要的开销。

注意事项

表达式括号：在创建函数索引时，需要将表达式放入括号中，以区分于普通列索引或前缀索引。例如，创建一个基于 DATE() 函数的索引应该这样写：ALTER TABLE table_name ADD INDEX index_name((DATE(column_name)));
函数限制：函数索引中的表达式不能使用列的前缀，也不能包含非确定性的函数，如 NOW()、RAND() 等。此外，不能使用子查询、参数、变量、存储函数以及自定义函数。只有确定性的函数才能用于创建函数索引。
索引类型：虽然普通索引支持前缀索引，但函数索引不支持字段的前缀索引。如果需要对字段的前缀进行索引，可以使用 SUBSTRING() 或 CAST() 函数作为替代方案。
性能考虑：函数索引在查询时动态计算其值，这可能会影响性能，尤其是在数据量大或表达式复杂的情况下。因此，在创建函数索引之前，应该进行性能测试，确保它确实能够提高查询效率。
存储引擎限制：函数索引仅在某些存储引擎中受支持，如 InnoDB。在使用函数索引之前，确保它与你的存储引擎兼容。
索引维护：创建函数索引时，MySQL 会在内部创建一个隐藏的虚拟列，并在该虚拟列上创建索引。这意味着函数索引的维护与普通索引类似，但需要注意虚拟列的存在。
查询优化：在使用函数索引时，查询条件必须与索引定义的表达式完全一致，才能确保查询优化器使用该索引。例如，如果函数索引是基于 UPPER(column_name) 创建的，那么查询条件也应该是 UPPER(column_name) = 'SOMEVALUE'。
特殊数据类型：对于 JSON 数据类型，函数索引可以用于优化 JSON 列的查询，但需要注意 JSON_UNQUOTE() 函数和 CAST() 函数在索引和查询中的使用，因为它们可能影响索引的可见性和查询性能。
索引数量：虽然函数索引可以提高查询性能，但过多的索引会增加数据库的维护成本，因此应该根据实际需求合理创建索引。
版本限制：函数索引是在 MySQL 8.0.13 版本中引入的，因此需要确保你的 MySQL 版本支持此特性。