MySQL 函数索引功能终于可以实现了

最新推荐文章于 2024-09-14 17:21:40 发布
原创 最新推荐文章于 2024-09-14 17:21:40 发布 · 290 阅读 · 0 ·
CC 4.0 BY-SA版权
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
文章标签：

#mysql #数据库

287dfa765984043a6e35267e7af8e5cc.png

开头还是介绍一下群，如果感兴趣PolarDB ,MongoDB ,MySQL ,PostgreSQL ,SQL Server,Redis ，Oracle ,Oceanbase 等有问题，有需求都可以加群群内有各大数据库行业大咖，CTO，可以解决你的问题。加群请加微信号 liuaustin3 （共1280人左右 1 + 2 + 3 +4）新人会进入3群（3群接近400准备关闭自由申请） + 4群

当对你热情的人，突然不热情了，说明你的利用的价值已经不存在了，此时没有必要刨根问底，为什么他会对你这样，人性如此，只是你懂得的太晚了而已。

升级MySQL 到MySQL8 是很多企业都还没有做的事情，可能是诱惑力不够，在SQL 的进步方面MySQL 的确是说一说，今天来说说函数索引的问题。

在不少的SQL语句中撰写中，MySQL都会强调不允许存在条件左边有函数的情况，但这对于其他的数据库来说并不是一个必须的要求，因为其他的数据库大多支持函数索引的问题，这就导致MySQL 在语句查询和索引方的太简单的问题，比较显露。

在另一个开源数据库PostgreSQL的guide中写明了，使用函数索引的情况下，索引的表达式在索引的搜索期间不会重新计算，以为他们已经存储在索引中，查询中将查询视为  where 索引列=‘常量’ ，搜索的速度与普通的简单查询是类似的。

MySQL 在8.013的版本中开始支持函数索引，函数索引允许基于表中某一个列的计算或函数来进行索引的建立。

mysql> 
mysql> explain analyze select * from orders where month(orderDate) = '01';
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                                                                                                                       |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Filter: (month(orders.orderDate) = '01')  (cost=33.35 rows=326) (actual time=0.031..0.254 rows=25 loops=1)
    -> Table scan on orders  (cost=33.35 rows=326) (actual time=0.027..0.229 rows=326 loops=1)
 |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql>

上面的部分，在之前MySQL是无法接受这样的写法的，必须转换写法才能完成这样的查询功能并且有效率的工作。

下面我们用两种方法来进行相关问题的解决，基础表

CREATE TABLE `orders` (
  `orderNumber` int NOT NULL,
  `orderDate` date NOT NULL,
  `requiredDate` date NOT NULL,
  `shippedDate` date DEFAULT NULL,
  `status` varchar(15) NOT NULL,
  `comments` text,
  `customerNumber` int NOT NULL,
  PRIMARY KEY (`orderNumber`),
  KEY `customerNumber` (`customerNumber`),
  KEY `idx_orderdate` ((dayofmonth(`orderDate`))),
  CONSTRAINT `orders_ibfk_1` FOREIGN KEY (`customerNumber`) REFERENCES `customers` (`customerNumber`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |

1  函数索引

create index idx_orderdate on orders ((day(orderDate)));
mysql> explain analyze select count(*) from orders where day(orderDate) = '03';
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                                                                                                                                                                                                                          |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Aggregate: count(0)  (cost=65.95 rows=1) (actual time=0.126..0.126 rows=1 loops=1)
    -> Filter: (dayofmonth(orders.orderDate) = '03')  (cost=33.35 rows=326) (actual time=0.043..0.123 rows=16 loops=1)
        -> Table scan on orders  (cost=33.35 rows=326) (actual time=0.036..0.097 rows=326 loops=1)
 |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

在查询中，发现并没有走索引的原因，虽然添加了函数索引，那么我们变换一下相关的写法

mysql> explain analyze select count(*) from orders where day(orderDate) = day('2023-09-01');
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                                                                                                                                                                  |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Aggregate: count(0)  (cost=5.25 rows=1) (actual time=0.057..0.057 rows=1 loops=1)
    -> Index lookup on orders using idx_orderdate (dayofmonth(orderDate)=dayofmonth('2023-09-01'))  (cost=3.75 rows=15) (actual time=0.049..0.054 rows=15 loops=1)
 |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql>

在我们进行了相关的查询的方式改写后，发现可以走索引了，但是原因是什么，原因在于隐式转换，之前在 day 函数操作后的数据并不是文本，所以需要将等于号后面的文字，标记为数值，或不添加引号。

mysql> explain analyze select count(*) from orders where day(orderDate) = 01;
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                                                                                                                                           |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Aggregate: count(0)  (cost=5.25 rows=1) (actual time=0.147..0.147 rows=1 loops=1)
    -> Index lookup on orders using idx_orderdate (dayofmonth(orderDate)=1)  (cost=3.75 rows=15) (actual time=0.130..0.140 rows=15 loops=1)
 |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)


或者改写成下方的方式，都可以避免隐士转换导致的索引问题。

mysql> explain analyze select count(*) from orders where day(orderDate) = cast("7" as unsigned) ;
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                                                                                                                                                             |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Aggregate: count(0)  (cost=4.05 rows=1) (actual time=0.048..0.049 rows=1 loops=1)
    -> Index lookup on orders using idx_orderdate (dayofmonth(orderDate)=cast('7' as unsigned))  (cost=3.15 rows=9) (actual time=0.042..0.045 rows=9 loops=1)
 |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql>

这是目前 8.013 版本以上的MySQL 可以提供的方案。

第二种方案是通过，添加虚拟列的方式来进行相关的工作，我们先添加一个虚拟列。

| orders | CREATE TABLE `orders` (
  `orderNumber` int NOT NULL,
  `orderDate` date NOT NULL,
  `requiredDate` date NOT NULL,
  `shippedDate` date DEFAULT NULL,
  `status` varchar(15) NOT NULL,
  `comments` text,
  `customerNumber` int NOT NULL,
  PRIMARY KEY (`orderNumber`),
  KEY `customerNumber` (`customerNumber`),
  KEY `idx_orderdate` ((dayofmonth(`orderDate`))),
  CONSTRAINT `orders_ibfk_1` FOREIGN KEY (`customerNumber`) REFERENCES `customers` (`customerNumber`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+--------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> alter table orders add column day_t smallint generated always as (day(orderdate)) virtual;
Query OK, 326 rows affected (0.09 sec)
Records: 326  Duplicates: 0  Warnings: 0

在添加完虚拟列后，我们对虚拟列进行索引的添加，再次查询，我们可以看到在实际的操作中我们已经可以走索引了。以上就是 2中在MySQL8中对于一些特殊的查询需求中走索引的方案。

mysql> create index idx_day_t on orders (day_t);
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> explain select * from orders where day_t = 7;
+----+-------------+--------+------------+------+---------------+-----------+---------+-------+------+----------+-------+
| id | select_type | table  | partitions | type | possible_keys | key       | key_len | ref   | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+-----------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | orders | NULL       | ref  | idx_day_t     | idx_day_t | 3       | const |    9 |   100.00 | NULL  |
+----+-------------+--------+------------+------+---------------+-----------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

mysql> explain analyze select * from orders where day_t = day('2022-09-09');
+---------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                                                     |
+---------------------------------------------------------------------------------------------------------------------------------------------+
| -> Index lookup on orders using idx_day_t (day_t=dayofmonth('2022-09-09'))  (cost=3.55 rows=13) (actual time=0.051..0.111 rows=13 loops=1)
 |
+---------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql索引详解
学Java，找哪吒
07-07 10万+
MySQL的存储引擎架构将查询处理与数据的存储/提取相分离
【springboot微服务】Lucence实现Mysql全文检索
congge
04-10 8883
Lucence实现Mysql全文检索
mysql常用函数及视图索引
qq_64001795的博客
08-08 1308
CREATE [UNIQUE|FULLTEXT] INDEX 索引名 ON 表名(字段名[(长度)][ASC|DESC])示例：select str_to_date('2017-01-06 10:20:30','%Y-%m-%d %H:%i:%s');示例：select date_format(now(), '%Y-%m-%d %H:%i:%s');示例： select replace('www.google.net','w','n')示例：select length('abcdef');...
mysql函数索引_MySQL 函数索引 (Functional indexes)
weixin_29258865的博客
01-18 3418
函数索引示例：CREATE TABLE t1 (col1 INT, col2 INT, INDEX func_index ((ABS(col1))));CREATE INDEX idx1 ON t1 ((col1 + col2));CREATE INDEX idx2 ON t1 ((col1 + col2), (col1 - col2), col1);ALTER TABLE t1 ADD INDE...
mysql数据库函数索引_MySQL 函数索引的优化方案
weixin_28902131的博客
01-27 304
很多开发人员在使用MySQL时经常会在部分列上进行函数计算等，导致无法走索引，在数据量大的时候，查询效率低下。针对此种情况本文从MySQL5.7 及MySQL8.0中分别进行不同方式的优化。1、 MySQL5.7MySQL5.7版本中不支持函数索引，因此 遇到函数索引的时候需要进行修改，否则即使查询的字段上有索引，执行时也无法使用索引而进行全表扫描，数据量大的表查询时间会比较长。具体案例如下：1....
【译】MySQL 8.0 函数索引
jmysql的博客
04-29 1956
2022Java毕业设计项目全套，进来白嫖_哔哩哔哩_bilibili 前言 与数百个不同的客户打交道时，我经常在查询方面遇到类似的问题。 在尝试优化数据库环境时，一个非常常见的问题是索引使用。 无法使用索引的查询通常是长时间运行的查询，会消耗更多内存或触发更多磁盘 iops。 一个非常常见的情况是查询对涉及某种函数表达式的列使用过滤条件。无法使用该列上的索引。 从 MySQL 8.0.13 开始支持函数索引。在本文中，我将展示它们是什么以及它们是如何工作的。 众所周知的问题 如前所述，关于索引
函数索引
kf_panda
05-27 1134
函数索引： 1、创建函数索引： 1)、CREATE INDEX IDX_EMPLOYEE_NAME ON EMPLOYEE(UPPER(NAME)); SELECT * FROM EMPLOYEE WHERE UPPER(NAME) = 'PANDA'; 2)、CREATE INDEX IDX_WEATHER ON WEATHER((MAXTEMP-MINTEMP) DES
SQLServer性能优化–间接实现函数索引或者Hash索引
12-16
在SQL Server中，虽然没有直接提供函数索引或哈希索引的功能，但可以通过一些技巧间接实现类似的效果，以优化查询性能。以下两种方法分别针对不同的场景提供了解决方案。 1. **计算列上建立索引，实现“函数索引”...
mysql split函数用逗号分隔的实现
12-14
虽然MySQL的标准库中没有直接提供`split`函数，但可以通过自定义存储过程和函数来实现类似的功能。这里我们将详细讲解如何在MySQL中实现一个用逗号分隔的`split`函数。 首先，我们创建一个存储过程`splitString`，...
利用Java+MySQL实现附近功能实例
08-28
本文将深入探讨如何使用Java与MySQL数据库来实现这一功能。首先，我们需要理解这个功能的基本工作原理，以及如何在数据库层面进行优化以提高性能。 **1. 基本实现** 获取附近的人或地点通常涉及到对地理位置数据的...
MySql 函数索引
02-06 2122
      MySql 函数索引! (logical NOT) != (not equal) " % (modulo) % (wildcard character) & (bitwise AND) && (logical AND) () (06-3) (Control-Z) /z * (multiplication) + (addition) -
MySQL-函数-索引
weixin_42454678的博客
08-06 567
1.MySQL中的默认值处理 (1)在MySQL中如何定义默认值？ 修改表添加列的默认值 create table emp3(id int primary key auto_increment ,name varchar(20),address varchar(50) default “UNKOWN”); 插入数据时的默认值处理 alter table emp3 add column deptno...
MySQL8.0 函数索引
阿常呓语的专栏
10-16 3630
MySQL8.0 新特性函数索引
说说函数索引
ciqu9915的博客
05-22 477
我们进行数据库检索优化的方法，通常是对特定条件列加索引，减少由于全表扫描带来的逻辑物理IO消耗。索引的种类很多，我们经常使用的B*树索引，由于结构简单、适应性强，可以应对大多数数据访问优化需求。除B*树索引外，其他一些索引类型，...
MySQL8.0 新特性函数索引
zxrhhm的博客
07-13 2309
MySQL8.0函数索引功能介绍及使用示例
Mysql索引、函数
weixin_62319517的博客
08-23 1077
针对比较大的数据，比如我们存放的是消息内容，有几 KB 的数 据的这种情况，如果要解决like 查询效率低的问题，可以创建全文索引。唯一索引要求键值不能重复。另外需要注意的是，主键索引是一种特殊的唯一索引，它还多了一个限制条件，要求键值不能为空。如下：复合（联合）索引包含a1，b2，c3三列，但SQL语句没有包含索引前置列"a1"，按照MySQL联合索引的。- 3.当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，降低了数据的维护速度。也叫非唯一索引，是最普通的索引，没有任何的限制。
MySQL8.0.13-函数索引
Eucalyptus's blog
09-14 1428
本篇主要介绍 MySQL 的函数索引（也叫表达式索引）。通常来讲，索引都是基于字段本身或者字段前缀，而函数索引是基于字段本身加上函数、操作符、表达式等计算而来。如果将表达式或者操作符也看做函数的话，简单来说，这样的索引就可以统称函数索引。MySQL 的函数索引内部是基于虚拟列（generated columns）实现，不同于直接定义虚拟列，函数索引自动创建的虚拟列本身实时计算结果，并不存储数据，只把函数索引本身存在磁盘上。
Oracle之函数索引
coco3600的博客
11-28 6681
Oracle之函数索引 在Oracle中，有一类特殊的索引，称为函数索引（Function-Based Indexes，FBI），它基于对表中列进行计算后的结果创建索引。函数索引在不修...
mysql函数索引反转
最新发布
02-22
### 函数索引反转的概念 在数据库查询优化中，函数索引反转是指通过调整查询条件或创建特定类型的索引来提高涉及函数操作字段的查询性能。当查询涉及到对列应用函数时，默认情况下无法利用该列上的常规索引，因为索引是基于原始数据构建的而不是基于经过函数处理后的结果。 对于 `reverse` 这样的字符串反转函数，在某些场景下可能需要频繁地根据反向排列的数据进行匹配查找。如果直接使用 `LIKE` 或者其他模式匹配方式来搜索被反转过的字符串，则效率较低；此时可以通过预先计算并存储这些值或者建立适合此类操作使用的特殊结构——即所谓的“函数索引”。 然而需要注意的是，并不是所有的 MySQL 版本都支持真正的函数索引特性。直到版本 8.0 开始引入了表达式索引的功能，允许为计算得出的结果集创建索引，这其中包括但不限于调用了内置函数的情况[^2]。 ### 实现方法 假设有一个表名为 `users` 的表格，其中有一列表示用户的手机号码叫做 `mobile` ，现在想要快速定位那些号码结尾部分为 "1234" 的记录： #### 方法一：使用虚拟生成列配合普通索引 ```sql ALTER TABLE users ADD COLUMN reversed_mobile VARCHAR(255) GENERATED ALWAYS AS (REVERSE(mobile)) VIRTUAL; CREATE INDEX idx_reversed_mobile ON users(reversed_mobile); SELECT * FROM users WHERE REVERSE(mobile) LIKE '4321%'; -- 或更高效的方式 SELECT * FROM users WHERE reversed_mobile LIKE '4321%'; ``` 这种方法先增加了一个新的虚拟（VIRTUAL）自动生成列 `reversed_mobile` 来保存原电话号码对应的逆序形式，接着针对这个新添加的属性建立了标准 B-tree 类型的索引 `idx_reversed_mobile` 。最后执行查询的时候就可以直接比较已经预处理好的内容而不需要每次都重新计算一次 `REVERSE()` 函数返回值了。 #### 方法二：直接定义表达式索引（仅限于 MySQL 8.0 及以上） ```sql CREATE INDEX idx_expr_reversed_mobile ON users ((REVERSE(mobile))); EXPLAIN SELECT * FROM users WHERE REVERSE(mobile) LIKE '4321%'; ``` 这里直接指定了要作为键值的部分是一个 SQL 表达式的输出而非简单的单个物理存在字段名 `(REVERSE(mobile))` 。这种方式更加简洁明了而且无需额外占用磁盘空间用于维护辅助性的中间状态信息。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值