MySQL8.0.13-函数索引

MYSQL MySQL 数据库运维

MySQL8.0.13-函数索引

Eucalyptus2024-09-14

什么是函数索引

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

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

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

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

语法

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

1	CREATE INDEX index_name ON table_name (expression);

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

1	ALTER TABLE table_name ADD INDEX index_name (expression);

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

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

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

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

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

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

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

1	CREATE INDEX idx_price_times_two ON products (price * 2);

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

1	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');

创建普通索引

1	alter table idx_test add index idx_create_time(create_time);

查看执行计划

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

创建函数索引

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

查看执行计划

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

查看索引信息

1	show index from idx_test ;

函数索引效率

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

普通索引

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

查看详细执行计划

1	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

函数索引

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

查看详细执行计划

1	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 版本支持此特性。