MySQL 8.0新特性 — 函数索引

原创

brightdeng@DBA

修改于 2020-09-27 14:33:11

2.8K1

修改于 2020-09-27 14:33:11

前言

在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 删除。

云数据库 SQL Server

原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。

如有侵权，请联系 cloudcommunity@tencent.com 删除。

云数据库 SQL Server

登录后参与评论

0 条评论

热度