开头还是介绍一下群，如果感兴趣PolarDB ,MongoDB ,MySQL ,PostgreSQL ,SQL Server,Redis ，Oracle ,Oceanbase 等有问题，有需求都可以加群群内有各大数据库行业大咖，CTO，可以解决你的问题。加群请加微信号 liuaustin3 （共1280人左右 1 2 3 4）新人会进入3群（3群接近400准备关闭自由申请） 4群
代码语言：javascript复制当对你热情的人，突然不热情了，说明你的利用的价值已经不存在了，此时没有必要刨根问底，为什么他会对你这样，人性如此，只是你懂得的太晚了而已。
升级MySQL 到MySQL8 是很多企业都还没有做的事情，可能是诱惑力不够，在SQL 的进步方面MySQL 的确是说一说，今天来说说函数索引的问题。
在不少的SQL语句中撰写中，MySQL都会强调不允许存在条件左边有函数的情况，但这对于其他的数据库来说并不是一个必须的要求，因为其他的数据库大多支持函数索引的问题，这就导致MySQL 在语句查询和索引方的太简单的问题，比较显露。
在另一个开源数据库PostgreSQL的guide中写明了，使用函数索引的情况下，索引的表达式在索引的搜索期间不会重新计算，以为他们已经存储在索引中，查询中将查询视为 where 索引列=‘常量’ ，搜索的速度与普通的简单查询是类似的。
MySQL 在8.013的版本中开始支持函数索引，函数索引允许基于表中某一个列的计算或函数来进行索引的建立。
代码语言：javascript复制
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是无法接受这样的写法的，必须转换写法才能完成这样的查询功能并且有效率的工作。
下面我们用两种方法来进行相关问题的解决，基础表
代码语言：javascript复制 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 函数索引
代码语言：javascript复制 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)
在查询中，发现并没有走索引的原因，虽然添加了函数索引，那么我们变换一下相关的写法
代码语言：javascript复制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 函数操作后的数据并不是文本，所以需要将等于号后面的文字，标记为数值，或不添加引号。
代码语言：javascript复制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)
或者改写成下方的方式，都可以避免隐士转换导致的索引问题。
代码语言：javascript复制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 可以提供的方案。
第二种方案是通过，添加虚拟列的方式来进行相关的工作，我们先添加一个虚拟列。
代码语言：javascript复制| 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中对于一些特殊的查询需求中走索引的方案。
代码语言：javascript复制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)
