MySQL8.0 新特性函数索引

于 2024-07-13 09:26:28 发布
阅读量420 收藏 8
点赞数 10
文章标签： mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/zxrhhm/article/details/140377658
版权

MySQL 8.0 中的函数索引（Function-Based Index）是一种高级索引特性，它允许数据库管理员或开发者对表中的列执行表达式计算后的结果进行索引，而不是直接对列值或列的前缀值进行索引。这种索引技术可以显著提高查询性能，特别是在需要对列值进行复杂计算或转换的查询场景中。

MySQL8.0.13引入函数索引（Oracle,postgresql都有该项功能）。

一、函数索引的定义与作用

函数索引是对表中的列执行表达式计算后的结构进行索引，而不是对列或列前缀值。它允许对未直接存储在表中的数据进行索引，为应用程序提供了极大的便利和性能提升。例如，你可能需要经常查询某个日期字段的年份部分，通过函数索引，你可以直接对年份部分进行索引，而无需在每次查询时都执行日期转换操作。

二、函数索引的创建

在MySQL 8.0中，创建函数索引的语法与普通索引类似，但需要注意以下几点：

  • 索引表达式：函数索引对应的表达式需要用括号()括起来。
  • 表达式复杂性：索引表达式可以包括函数、算术运算、比较操作等，但应尽可能保持简单，以避免索引维护的开销过大。
  • 索引类型：函数索引可以是普通索引、唯一索引等，具体取决于你的需求。

1、创建测试表及数据

create table t_function(id int primary key auto_increment,name  varchar(100),create_time datetime);
insert into t_function(name,creatE_time) values('function-index','2024-07-01 12:00:00');
insert into t_function(name,creatE_time) values('mysql5.7.28-version','2024-07-01 15:00:00');
insert into t_function(name,creatE_time) values('version-mysql8.0.28','2024-07-01 21:30:00');
insert into t_function(name,creatE_time) values('create-index-indexname','2024-07-02 01:30:00');
insert into t_function(name,creatE_time) values('on-tablename-column','2024-07-02 03:30:00');
insert into t_function(name,creatE_time) values('column-function','2024-07-02 07:32:00');
insert into t_function(name,creatE_time) values('index-indexname-column','2024-07-02 10:32:00');
insert into t_function(name,creatE_time) values('alter-table-tablename','2024-07-02 15:32:00');
insert into t_function(name,creatE_time) values('add-index-indexname','2024-07-02 20:32:00');
insert into t_function(name,creatE_time) values('use-superdb','2024-07-03 02:56:00');
insert into t_function(name,creatE_time) values('show-tables','2024-07-03 08:41:00');
insert into t_function(name,creatE_time) values('show-index-from-t_function','2024-07-03 16:20:00');
commit;

2、创建函数索引

在将上述的表及数据在MySQL8.0的实例上创建，然后创建create_time的函数索引，SQL如下

(root@localhost)[superdb]> alter table t_function add key idx_t_function_create_time(create_time);
Query OK, 0 rows affected (0.23 sec)
Records: 0  Duplicates: 0  Warnings: 0

(root@localhost)[superdb]> alter table t_function add key idx_t_function_create_time_2 ((date(create_time)));
Query OK, 0 rows affected (0.08 sec)
Records: 0  Duplicates: 0  Warnings: 0

3、按时间查询

(root@localhost)[superdb]> select * from t_function where date(create_time)='2024-07-01';
+----+---------------------+---------------------+
| id | name                | create_time         |
+----+---------------------+---------------------+
|  1 | function-index      | 2024-07-01 12:00:00 |
|  2 | mysql5.7.28-version | 2024-07-01 15:00:00 |
|  3 | version-mysql8.0.28 | 2024-07-01 21:30:00 |
+----+---------------------+---------------------+
3 rows in set (0.02 sec)

(root@localhost)[superdb]> explain select * from t_function where date(create_time)='2024-07-01';
+----+-------------+------------+------------+------+------------------------------+------------------------------+---------+-------+------+----------+-------+
| id | select_type | table      | partitions | type | possible_keys                | key                          | key_len | ref   | rows | filtered | Extra |
+----+-------------+------------+------------+------+------------------------------+------------------------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | t_function | NULL       | ref  | idx_t_function_create_time_2 | idx_t_function_create_time_2 | 4       | const |    3 |   100.00 | NULL  |
+----+-------------+------------+------------+------+------------------------------+------------------------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.02 sec)

(root@localhost)[superdb]> explain select * from t_function where create_time>='2024-07-01' and create_time<date_add('2024-07-01',interval '1' day);
+----+-------------+------------+------------+-------+----------------------------+----------------------------+---------+------+------+----------+-----------------------+
| id | select_type | table      | partitions | type  | possible_keys              | key                        | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+------------+------------+-------+----------------------------+----------------------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | t_function | NULL       | range | idx_t_function_create_time | idx_t_function_create_time | 6       | NULL |    3 |   100.00 | Using index condition |
+----+-------------+------------+------------+-------+----------------------------+----------------------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.01 sec)

4、查看表t_function 索引

show columns and show index statements now support an optional EXTENDED keyword that causs statement output to include information about hidden columns and indexes that MySQL uses internally and are not accessible by users.

(root@localhost)[superdb]> show index from t_function;
+------------+------------+------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+-----------------------------+
| Table      | Non_unique | Key_name                     | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression                  |
+------------+------------+------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+-----------------------------+
| t_function |          0 | PRIMARY                      |            1 | id          | A         |          12 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL                        |
| t_function |          1 | idx_t_function_create_time   |            1 | create_time | A         |          12 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL                        |
| t_function |          1 | idx_t_function_create_time_2 |            1 | NULL        | A         |           3 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | cast(`create_time` as date) |
+------------+------------+------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+-----------------------------+
3 rows in set (0.01 sec)

(root@localhost)[superdb]> show extended index from t_function;
+------------+------------+------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+-----------------------------+
| Table      | Non_unique | Key_name                     | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression                  |
+------------+------------+------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+-----------------------------+
| t_function |          0 | PRIMARY                      |            1 | id          | A         |          12 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL                        |
| t_function |          0 | PRIMARY                      |            2 | DB_TRX_ID   | A         |        NULL |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL                        |
| t_function |          0 | PRIMARY                      |            3 | DB_ROLL_PTR | A         |        NULL |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL                        |
| t_function |          0 | PRIMARY                      |            4 | name        | A         |        NULL |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL                        |
| t_function |          0 | PRIMARY                      |            5 | create_time | A         |        NULL |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL                        |
| t_function |          1 | idx_t_function_create_time   |            1 | create_time | A         |          12 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL                        |
| t_function |          1 | idx_t_function_create_time   |            2 | id          | A         |        NULL |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL                        |
| t_function |          1 | idx_t_function_create_time_2 |            1 | NULL        | A         |           3 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | cast(`create_time` as date) |
| t_function |          1 | idx_t_function_create_time_2 |            2 | id          | A         |        NULL |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL                        |
+------------+------------+------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+-----------------------------+
9 rows in set (0.00 sec)

可见，在MySQL8.0 创建对应的函数索引后，不改变SQL写法的前提下，查询的列上进行对应的函数计算后也可以走索引

5、MySQL5.7.X版本

因MySQL5.7不支持函数索引，所以需要修改SQL写法来实现走索引（或者使用虚拟列的方式）

  • 创建表及插入数据同第1
  • 在create_time字段上创建索引
  • 查询创建时间是2024-07-01那天的所有记录
mysql> select * from t_function where date(create_time)='2024-07-01';
+----+---------------------+---------------------+
| id | name                | create_time         |
+----+---------------------+---------------------+
|  1 | function-index      | 2024-07-01 12:00:00 |
|  2 | mysql5.7.28-version | 2024-07-01 15:00:00 |
|  3 | version-mysql8.0.28 | 2024-07-01 21:30:00 |
+----+---------------------+---------------------+
3 rows in set (0.00 sec)

mysql> explain select * from t_function where date(create_time)='2024-07-01';
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table      | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | t_function | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   12 |   100.00 | Using where |
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.05 sec)
-- 执行计划中可以看出是进行了全表扫描

修改SQL写法来实现走索引（或者使用虚拟列的方式）

mysql> select * from t_function where create_time>='2024-07-01' and create_time<date_add('2024-07-01',interval '1' day);
+----+---------------------+---------------------+
| id | name                | create_time         |
+----+---------------------+---------------------+
|  1 | function-index      | 2022-04-01 12:00:00 |
|  2 | mysql5.7.28-version | 2022-04-01 15:00:00 |
|  3 | version-mysql8.0.28 | 2022-04-01 21:30:00 |
+----+---------------------+---------------------+
3 rows in set (0.00 sec)
-- 等价修改SQL写法来第一种实现走索引
mysql> select * from t_function where create_time between '2024-07-01' and date_add('2022-07-01',interval '1' day);
+----+---------------------+---------------------+
| id | name                | create_time         |
+----+---------------------+---------------------+
|  1 | function-index      | 2024-07-01 12:00:00 |
|  2 | mysql5.7.28-version | 2024-07-01 15:00:00 |
|  3 | version-mysql8.0.28 | 2024-07-01 21:30:00 |
+----+---------------------+---------------------+
3 rows in set (0.05 sec)
-- 等价修改SQL写法第二种来实现走索引
mysql> select * from t_function where create_time>='2024-07-01' and create_time<date_add('2022-04-01',interval '1' day);
+----+---------------------+---------------------+
| id | name                | create_time         |
+----+---------------------+---------------------+
|  1 | function-index      | 2024-07-01 12:00:00 |
|  2 | mysql5.7.28-version | 2024-07-01 15:00:00 |
|  3 | version-mysql8.0.28 | 2024-07-01 21:30:00 |
+----+---------------------+---------------------+
3 rows in set (0.05 sec)
-- 查看执行计划
mysql> explain select * from t_function where create_time>='2024-07-01' and create_time<date_add('2022-04-01',interval '1' day);
+----+-------------+------------+------------+-------+----------------------------+----------------------------+---------+------+------+----------+-----------------------+
| id | select_type | table      | partitions | type  | possible_keys              | key                        | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+------------+------------+-------+----------------------------+----------------------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | t_function | NULL       | range | idx_t_function_create_time | idx_t_function_create_time | 6       | NULL |    3 |   100.00 | Using index condition |
+----+-------------+------------+------------+-------+----------------------------+----------------------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

三、函数索引的使用场景

函数索引特别适用于以下场景：

  • 日期和时间的计算：如查询特定年份、月份或日期的记录。
  • 字符串处理：如查询字符串的某个子串或进行大小写不敏感的匹配。
  • 数值计算：如查询某个数值范围经过计算后落在特定区间的记录。

四、注意事项

  • 索引维护：函数索引的维护成本可能比普通索引更高，因为每次插入、更新或删除记录时，都需要重新计算索引表达式。
  • 索引选择性：函数索引的选择性（即索引列中不同值的比例）对于查询性能至关重要。选择性较低的索引可能无法提供显著的性能提升。
  • 兼容性：不是所有的MySQL存储引擎都支持函数索引。例如，InnoDB存储引擎支持函数索引，但MyISAM则不支持。

五、总结

MySQL 8.0 的函数索引为数据库管理员和开发者提供了更灵活的索引选择，可以显著提高查询性能。然而，在使用函数索引时，需要仔细考虑索引的维护成本、选择性和兼容性等因素。通过合理使用函数索引，可以优化数据库查询性能，提升应用程序的整体性能。

zxrhhm
关注
  • 10
    点赞
  • 踩
  • 8
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
MySQL 8.0的新特性简述
08-30
MySQL 8.0 是一个重要的版本升级，引入了一系列新特性以提升数据库的性能、安全性和易用性。以下是对这些新特性的详细说明： 1. **数据字典**： MySQL 8.0 引入了一个事务性的数据字典，用于存储关于数据库对象的...
MySQL8.0新特性之支持原子DDL语句
09-09
MySQL 8.0引入了一项重大改进，即支持原子数据定义语言（DDL）语句，这一特性极大地增强了数据库系统的稳定性和一致性。原子DDL允许数据库在处理DDL操作时以原子事务的方式工作，确保了数据完整性，即便在服务器崩溃...
MySQL8.0新特性
07-16
MySQL 8.0是MySQL数据库管理系统的一个重大更新版本，它带来了许多新的特性和改进，旨在提升性能、可扩展性以及用户体验。以下是一些主要的新特性介绍： 1. **增强的性能** - **优化的查询执行引擎**：MySQL 8.0...
MySQL8.0 的新特性
12-14
3. **函数索引**：MySQL 8.0允许在索引中使用函数表达式，如对JSON数据的节点进行索引。例如，创建一个对用户名不区分大小写的索引：`CREATE INDEX username_upper_index ON tb_user((UPPER(username)))`。 二、...
MySQL新特性 For innodb 8.0
05-19
MySQL 8.0 版本对InnoDB存储引擎进行了大量改进和优化，为数据库管理员和开发者带来了诸多新特性。以下是一些主要的InnoDB新特性介绍： 1. **Columnar Storage (InnoDB Clustered Index)**: MySQL 8.0 引入了列式...
MySQL Buffer Pool
Mazai-Liu的博客
07-08 1094
LRU链表分为冷、热数据区域，前63%为热数据区域，后37%为冷数据区域，加载缓存页先放到冷数据区域头部。冷数据区域的缓存页第一次访问超过1秒后，再次访问时才会被移动到热数据区域头部。热数据区域中，只有后3/4的缓存页被访问才会移到头部，前1/4被访问到不会移动。淘汰数据优先淘汰冷数据区域尾部的缓存页。
mysql之比较两个表的数据
hebtu666
07-08 499
要比较两个结构相同的表中的某条数据是否完全一样，您可以使用JOIN语句或者EXISTS关键字来对比这两个表中相应的行。
mysql笔记1
m0_62975692的博客
07-11 222
首先查看systemctl status mysqld，如果是关闭的可以进入的配置文件打开mysql，我的mysql配置文件就是在/usr/local/mysql/support-filess/下，在此目录下执行./mysql.server start,打开mysql服务，也可以通过systmectl start mysqld，如果你嫌麻烦的话可以systemctl enable mysqld 一直打开mysql的服务;重新给字段添加约束的时候不可以添加not null约束的！
【MySQL】8.复合查询
最新发布
weixin_74113106的博客
07-12 307
MySQL复合查询
Windows系统MySQL的安装,客户端工具Navicat的安装
2301_78327423的博客
07-12 436
仅作为服务器(Server only)、仅作为客户端(Client only)、完全安装(Full)、自定义(Custom)。5点多的版本有点低，下载8.0.38版本的。执行一个基本的语句：show databases;双击打开它：能够发现这与我们在终端命令行显示的数据库一样。数据库自己起名字，字符集为UTF-8。)，是读者对MySQL数据库的知识了。右击localhost。提示连接成功，点击确定。移动到安装区域后，点击Next>直接叉掉，也可以exit退出掉。点击文件，点击新建连接...
MySQL8之mysql-community-server的作用
18602
07-08 323
MySQL8之。
【MySQL05】【 undo 日志】
猫吻鱼的博客
07-08 641
最近在读《MySQL 是怎样运行的》、《MySQL技术内幕 InnoDB存储引擎 》，后续会随机将书中部分内容记录下来作为学习笔记，部分内容经过个人删改，因此可能存在错误，如想详细了解相关内容强烈推荐阅读相关书籍。
mysql 表优化
马春林的专栏
07-08 186
mysql 表优化
基于Python协同过滤的旅游景点推荐系统，采用Django框架，MySQL数据存储，Bootstrap前端，echarts可视化实现
weixin_49081159的博客
07-09 823
随着旅游业的迅速发展，个性化旅游推荐系统成为提升用户体验和促进旅游市场增长的重要工具。本研究旨在设计并实现一种基于Python协同过滤的旅游景点推荐系统，结合Django框架、MySQL数据库存储、Bootstrap前端框架以及echarts数据可视化技术，为用户提供精准且个性化的旅游推荐服务。
MySQL怎么获取当前时间
zhanchulan的博客
07-08 290
这些函数可以根据您的具体需求来获取不同精度的当前时间信息。函数返回当前的日期和时间，格式为。相同，返回当前的日期和时间。
MySQL EXPLAIN 中的 type 和 ref 字段
小湘西的博客
07-08 312
在 MySQL 中，EXPLAIN语句用于分析 SQL 查询的执行计划。EXPLAIN输出的结果中包含多个字段，其中type和ref字段是理解查询执行方式的重要部分。
mysql之导入测试数据
hebtu666
07-08 176
运维时经常要这样：mysql改表名，创建一个一样的表不含数据，复制旧表几条数据进去。
MySQL物理备份与恢复工具XtraBackend使用总结
pyf2533931995的博客
07-08 910
环境：96核 754G内存 30T磁盘（生产环境的物理机）
在MySQL中遍历大数据集为什么推荐使用游标？
永乐的博客
07-08 334
从事开发的同学应该都知道，如果一个导出数据的需求总数据量在50w左右，没人会通过一条SQL直接查询出所有结果集并保存。因为这个操作会导致代码报出“内存溢出”的错误，通常会选择分页或者游标的方式实现这个需求。

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值