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/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 終於有人把MYSQL索引講清楚了MySql索引
- mysql實現oracle的lead和lag函式功能MySqlOracle函式
- MySQL排名函式實現MySql函式
- MySQL分析函式實現MySql函式
- 基於函式的索引函式索引
- MySQL 8.0 中的索引可以隱藏了！MySql索引
- mysql實現開窗函式MySql函式
- MySQL函式索引及優化MySql函式索引優化
- Oracle基於函式的索引Oracle函式索引
- 利用指標實現strncmp函式功能指標函式
- 13、MySQL Case-MySQL分析函式實現MySql函式
- 不使用日期函式實現ADD_MONTHS函式功能函式
- linux終端下實現的conio.h中的getch()功能函式Linux函式
- Clickhouse 的 mysql CDC，終於好使了MySql
- 牛逼！MySQL 8.0 中的索引可以隱藏了…MySql索引
- OCP之基於函式的索引函式索引
- 關於函式索引的問題?函式索引
- MySQL——索引實現原理MySql索引
- 不使用日期函式實現ADD_MONTHS函式功能（二）函式
- C#函式實現的小功能集合C#函式
- MySQL5.7 虛擬列實現表示式索引MySql索引
- 【Mysql】MySQL 5.7新特性之Generated Column（函式索引）MySql函式索引
- MySQL空間函式實現位置打卡MySql函式
- mysql 拾遺提高(函式、事務、索引)MySql函式索引
- 終於實現了一門屬於自己的程式語言
- [PY3]——函式——函式註解 | 實現型別檢查功能函式型別
- 【Mysql】Mysql似oracle分析函式sum over的實現MySqlOracle函式
- 測試建立基於函式的索引函式索引
- 關於函式索引(function-based index)函式索引FunctionIndex
- （函式）實現strstr函式函式
- mysql索引底層實現MySql索引
- 終於可以愉快的擼Java非同步程式碼了!Java非同步
- Linux 終於足夠好，可以取代 Windows 了嗎？LinuxWindows
- 到新公司工作了，終於可以寫部落格了
- 資料型別與函式索引-MySQL篇資料型別函式索引MySql
- 漫畫通訊：有了它，終於可以放心買買買了
- VSCode 的 Live Share 功能終於來了VSCode
- Mongodb3.0：終於具備壓縮功能了MongoDB