在MySQL之前版本中,一直不支持函数索引,这也是被不少人诟病的一点;虽然可以通过generated column实现类似功能,但始终是不太方便;不过,在最新的MySQL 8.0版本中,终于引入了函数索引,这让索引的定义更加灵活方便、功能更加强大完善。
(1)先创建一张测试表
mysql> show create table test\G
*************************** 1. row ***************************
Table: test
Create Table: CREATE TABLE `test` (
`id` int NOT NULL,
`a` int DEFAULT NULL,
`b` int DEFAULT NULL,
`c` int DEFAULT NULL,
`d` int DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)
(2)可以通过下列语句,创建函数索引
mysql> alter table test add index idx_f_1((a+b));
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> alter table test add index idx_f_2((a-b));
Query OK, 0 rows affected (0.11 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> alter table test add index idx_f_3((a*b));
Query OK, 0 rows affected (0.12 sec)
Records: 0 Duplicates: 0 Warnings: 0
(3)可以通过下列语句,查看函数索引
mysql> select table_name,index_name,seq_in_index,column_name,is_visible,expression from statistics where table_name='test';+------------+------------+--------------+-------------+------------+-------------+
| TABLE_NAME | INDEX_NAME | SEQ_IN_INDEX | COLUMN_NAME | IS_VISIBLE | EXPRESSION |
+------------+------------+--------------+-------------+------------+-------------+
| test | idx_f_1 | 1 | NULL | YES | (`a` + `b`) |
| test | idx_f_2 | 1 | NULL | YES | (`a` - `b`) |
| test | idx_f_3 | 1 | NULL | YES | (`a` * `b`) |
| test | PRIMARY | 1 | id | YES | NULL |
+------------+------------+--------------+-------------+------------+-------------+
5 rows in set (0.00 sec)
(4)函数索引创建完成后,相应SQL语句,就可以使用到函数索引
mysql> explain select * from test where (a+b)>10;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| 1 | SIMPLE | test | NULL | range | idx_f_1 | idx_f_1 | 9 | NULL | 1 | 100.00 | Using where |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql> explain select * from test where (a-b)>10;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| 1 | SIMPLE | test | NULL | range | idx_f_2 | idx_f_2 | 9 | NULL | 1 | 100.00 | Using where |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql> explain select * from test where (a*b)>10;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| 1 | SIMPLE | test | NULL | range | idx_f_3 | idx_f_3 | 9 | NULL | 1 | 100.00 | Using where |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
(5)可以通过下列语句,删除函数索引,和普通语法没有区别
mysql> alter table test drop index idx_f_1;
Query OK, 0 rows affected (0.06 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> alter table test drop index idx_f_2;
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> alter table test drop index idx_f_3;
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
(1)主键不支持函数索引,因为主键以实际列进行存储,而函数索引是作为虚拟列存在的
(2)在有主键的情况下,唯一索引支持函数索引;但在无主键的情况下,被提升为主键的唯一索引不支持
(3)外键不支持函数索引
(4)空间索引和全文索引不支持函数索引
(5)函数索引不能直接使用列前缀,可以通过SUBSTRING()和CAST()来替代
(6)在删除列之前,要先删除相关的函数索引
在MySQL 8.0中,引入了不可见索引、降序索引、函数索引的新特性,索引方面功能也是趋于完善。所以,大家还是尽快升级到8.0吧。
原创声明:本文系作者授权腾讯云开发者社区发表,未经许可,不得转载。
如有侵权,请联系 cloudcommunity@tencent.com 删除。
原创声明:本文系作者授权腾讯云开发者社区发表,未经许可,不得转载。
如有侵权,请联系 cloudcommunity@tencent.com 删除。