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

开头还是介绍一下群，如果感兴趣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)