MySQL 函式索引功能終於可以實現了

資料庫工作筆記發表於2023-10-17
MySql函式索引

來源：AustinDatabases


升級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)




來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/70027826/viewspace-2989355/，如需轉載，請註明出處，否則將追究法律責任。

相關文章