一. 函数索引简介
二. 函数索引测试
- 1. 创建表结构
- 2. 插入数据
- 3. 创建普通索引 idx_create_time：
- 4. 查看 sql 执行计划：
- 5. 创建函数索引 fun_create_time：
- 6. 再次查看 sql 执行计划：
- 7. 该表此时的索引信息：
三. 函数索引场景
- 1. 字段计算
- 2. 字段子串
四. 函数索引效率

一. 函数索引简介

函数索引就是给字段加了函数的索引，这里的函数也可以是表达式。所以也叫表达式索引。

这个功能是在 MySQL8.0 版本引入的，本质是通过虚拟列来实现的（虚拟列 MySQL 5.7 版本引入）。

二. 函数索引测试

1. 创建表结构

create table idx_test (id int primary key auto_increment,col1 int,col2 int,col3 varchar(30),create_time TIMESTAMP default now());

2. 插入数据

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

3. 创建普通索引 idx_create_time：

alter table idx_test add index idx_create_time(create_time);

4. 查看 sql 执行计划：

5. 创建函数索引 fun_create_time：

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

6. 再次查看 sql 执行计划：

7. 该表此时的索引信息：

通过简单的测试测试了下函数索引，接下来再看看函数索引的其他使用场景。

三. 函数索引场景

1. 字段计算

查询 sql：

新增 col1 + col2 的函数索引：

alter table idx_test add index fun_colsum( (col1 + col2) );

查看执行计划：

2. 字段子串

查询 sql：

新增 col3 子串的函数索引：

alter table idx_test add index fun_subcol( (substr(col3, 6, 8)) );

查看执行计划：

其他场景还有 right 函数、cast 函数求 json 的value 等，都可以按照此类套路去建立函数索引。

四. 函数索引效率

函数索引的使用条件比较苛刻，sql 必须严格按照索引建立的定义来写，这样才能用到函数索引。

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

sql1 用到普通索引：

select * from idx_test where create_time > '2021-02-02 00:00:00' and create_time <='2021-03-03 00:00:00';

sql2 用到函数索引：

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

通过 explain format=json 来看下详细执行计划：

sql1 用到普通索引：