OceanBase函數索引實戰指南：從創建到優化，解決查詢性能痛點（附實戰命令）詳情 - MySQL,函數索引,OceanBase,MySQL,數據庫,私藏項目實操分享瑞藍創軟件博客

“明明給字段建了索引，可帶函數的查詢還是走全表掃描，數據量大了直接卡到超時……”

如果你在使用 OceanBase 或 MySQL 時遇到過這種問題，大概率是沒用到函數索引！當查詢條件包含函數計算（比如LOWER(name)、MD5(customer_id)）時，普通索引完全失效，而函數索引能直接基於計算結果建立索引，讓查詢效率飆升10倍+！

今天這篇乾貨，從定義、用法、避坑到實戰場景，帶你徹底掌握 OceanBase 函數索引的核心玩法～

1. 函數索引的定義

函數索引是基於表達式或函數計算結果創建的索引，而非直接基於列值。

在MySQL 8.0.13開始支持函數索引，主要通過索引計算後的值來提高查詢性能。
OceanBase 支持版本：V4.2.0 及以上；

參考文檔：MySQL 8.0 參考手冊

2. 函數索引的作用

當查詢條件包含函數時，普通索引無法使用，優化函數表達式查詢；
對函數計算結果建立索引可以避免全表掃描；
對於包含計算的查詢條件能顯著提高效率；

2.1 函數索引的創建

ps：普通索引為 [索引名](列名)，函數索引為 [索引名]((列名));兩層括號後開始填寫函數表達式；

創建函數索引時，會向主表添加一個隱藏的虛擬生成列，定義為函數索引的索引鍵，然後再將虛擬生成列的值存儲到索引表中。

/* 建表時創建*/
CREATE TABLE tst_tb (col1 int, col2 int, [UNIQUE] {KEY / INDEX} [索引名]((用户函數(col1)));

/* 使用 create index 創建 */
CREATE [UNIQUE] INDEX tst_tb_func_idx2 ON tst_tb((col1+3));

/* 使用 alter table 創建 */
ALTER TABLE tst_tb ADD [UNIQUE] {INDEX | KEY} [索引名] ((用户函數(col1)));

2.2 函數索引的查詢

/* 
示例1：對名字的小寫形式創建索引,使用與索引定義完全相同的表達式查詢
*/
CREATE INDEX idx_lower_name ON tst_tb ((LOWER(name)));
SELECT * FROM employees WHERE LOWER(name) = 'John';

/* 
示例2：對JSON字段的特定路徑創建索引
從 JSON 文檔中指定的路徑返回數據 
*/
CREATE INDEX idx_json_extract ON tst_tb ((JSON_EXTRACT(person, '$.weight')));
SELECT * FROM tst_tb WHERE JSON_EXTRACT(specs, '$.weight') > 10;

3.函數索引的使用限制

> 1. 非確定性的系統函數禁止被用於創建函數索引;

> 2. 子查詢、參數、變量、存儲過程、自定義函數 function 不允許在函數索引上使用；

> 3. 函數索引的表達式結果受限於字段長度；

> 4. 函數索引無法作為主鍵使用；

> 5. 空間索引和全文索引不能定義為函數索引；

> 6. 函數索引不允許在外鍵中使用；

> 7. 某字段用於函數索引，刪列前必須刪除該索引，否則報錯；

/* 1. 使用非確定性函數*/
create index tst_01 on tst_tb((now()));
失敗原因：ErrorCode = 3758, SQLState = HY000, Details = Expression of functional index contains a disallowed function.
/*在創建函數索引時，使用了不支持的函數，索引函數使用 SYSDATE 或用户環境*/
create index current_time_fun_idx on tst_tb((current_date()));
失敗原因：ErrorCode = 3758, SQLState = HY000, Details = Expression of functional index contains a disallowed function.

/* 2.表達式過於複雜，函數索引中包括了子查詢*/
CREATE INDEX tst_02 ON tst_tb ((SELECT MAX(id) FROM tst_tb));
失敗原因：ErrorCode = 1064, SQLState = 42000, Details = You have an error in your SQL syntax; check the manual that corresponds to your OceanBase version for the right syntax to use near 'SELECT MAX(id) FROM tst_tb))' at line 1

/* 3.表達式結果太長,REPEAT 重複多次改字段字符串拼接*/
CREATE INDEX idx_invalid ON tst_tb ((REPEAT(customer_id, 100))); 
失敗原因：ErrorCode = 3757, SQLState = HY000, Details = Cannot create a functional index on an expression that returns a BLOB or TEXT. Please consider using CAST.
/* 在創建函數索引時，使用了返回 BLOB 或 TEXT 類型的表達式，而 BLOB 或 TEXT 類型的表達式不能用於創建函數索引 */

/* 4.函數索引無法作為主鍵創建*/
CREATE TABLE `tst_tb` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主鍵id，自動增長',
  `customer_id` varchar(50) DEFAULT NULL COMMENT '客户ID',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '創建時間',
  `update_time` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '更新時間',
  `product` varchar(10) DEFAULT NULL COMMENT '產品類型',
  PRIMARY KEY `pri_cust` ((lower(md5(concat(upper(customer_id), 'oceanbase'))))),
  KEY `idx_create_time` (`create_time`) BLOCK_SIZE 16384 LOCAL,
  KEY `ob_idx_customer_id` (`customer_id`) BLOCK_SIZE 16384 LOCAL
);
失敗原因：ErrorCode = 1064, SQLState = 42000, Details = You have an error in your SQL syntax; check the manual that corresponds to your OceanBase version for the right syntax to use near '(lower(md5(concat(upper(customer_id), 'oceanbase'))))), KEY `idx_create_time` ' at line 7

/* 5.空間索引和全文索引不能定義為函數索引*/
CREATE TABLE tbl_func (c1 int, c2 int, index tbl1_func_idx1 ((c1+1)), SPATIAL KEY ((c1+c2)));
失敗原因：ErrorCode = 3760, SQLState = HY000, Details = Spatial functional index is not supported.
/* 不支持空間索引套用函數索引*/
CREATE TABLE tbl_func (c1 int, c2 varchar(20), index tbl1_func_idx1 ((c1+1)),  FULLTEXT INDEX full_idx_func ((SUBSTR(c2,1,2))) WITH PARSER ngram PARSER_PROPERTIES=(ngram_token_size=3));
失敗原因：ErrorCode = 1235, SQLState = 0A000, Details = Fulltext index on generated column is not supported
/* 不支持全文索引套用函數索引*/

/* 6.函數索引不允許在外鍵中使用*/
CREATE TABLE products (
    product_id INT PRIMARY KEY,
    product_code VARCHAR(20) NOT NULL,  
    product_name VARCHAR(100) NOT NULL,
    INDEX idx_lower_product_code ((LOWER(product_code)))
);


CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    product_code_ref VARCHAR(20),   
    CONSTRAINT fk_order_product FOREIGN KEY (product_code_ref) -- 創建外鍵引用函數索引 
    REFERENCES products (LOWER(product_code))  
);

失敗原因：ErrorCode = 1064, SQLState = 42000, Details = You have an error in your SQL syntax; check the manual that corresponds to your OceanBase version for the right syntax to use near '(product_code)) )' at line 7

/* 7. 某字段用於函數索引，刪列前必須刪除該索引，否則報錯*/
ALTER TABLE products DROP COLUMN product_code;
失敗原因：ErrorCode = 3837, SQLState = HY000, Details = Column 'product_code' has a functional index dependency and cannot be dropped or renamed.

參考文檔：

騰訊雲：mysql函數索引_MySQL 函數索引 (Functional indexes)

函數索引支持的系統函數列表

函數索引不支持的系統函數列表

MySQL 函數索引測試分析

4. 無法創建函數索引的替代方案

/*表定義語句*/
CREATE TABLE `tst_tb` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主鍵id，自動增長',
  `customer_id` varchar(50) DEFAULT NULL COMMENT '客户ID',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '創建時間',
  `update_time` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '更新時間',
  `product` varchar(10) DEFAULT NULL COMMENT '產品類型',
  `name` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_create_time` (`create_time`) BLOCK_SIZE 16384 LOCAL,
  KEY `ob_idx_customer_id` (`customer_id`) BLOCK_SIZE 16384 LOCAL
);
explain extended select * from tst_tb where name='Product One';
=================================================
|ID|OPERATOR       |NAME  |EST.ROWS|EST.TIME(us)|
-------------------------------------------------
|0 |TABLE FULL SCAN|tst_tb|1       |3           |
=================================================
Outputs & filters:
-------------------------------------
  0 - output([tst_tb.id(0x7ff8f1823480)], [tst_tb.customer_id(0x7ff8f18238d0)], [tst_tb.create_time(0x7ff8f1823d20)], [tst_tb.update_time(0x7ff8f1824170)],
       [tst_tb.product(0x7ff8f18245c0)], [tst_tb.name(0x7ff8f1822d60)]), filter([tst_tb.name(0x7ff8f1822d60) = 'Product One'(0x7ff8f1822000)(0x7ff8f1822490)]), rowset=16
      access([tst_tb.id(0x7ff8f1823480)], [tst_tb.name(0x7ff8f1822d60)], [tst_tb.customer_id(0x7ff8f18238d0)], [tst_tb.create_time(0x7ff8f1823d20)], [tst_tb.update_time(0x7ff8f1824170)],
       [tst_tb.product(0x7ff8f18245c0)]), partitions(p0)
      is_index_back=false, is_global_index=false, filter_before_indexback[false], 
      range_key([tst_tb.id(0x7ff8f1823480)]), range(MIN ; MAX)always true
      
/* 添加虛擬生成列 */
ALTER TABLE tst_tb ADD COLUMN lower_name VARCHAR(100) 
GENERATED ALWAYS AS (LOWER(name)) STORED;

/* 在虛擬生成列上創建索引 */
CREATE INDEX idx_lower_name ON tst_tb (lower_name);

/* 執行計劃 */
explain extended select * from tst_tb where lower_name='Product One';
==================================================================
|ID|OPERATOR        |NAME                  |EST.ROWS|EST.TIME(us)|
------------------------------------------------------------------
|0 |TABLE RANGE SCAN|tst_tb(idx_lower_name)|1       |8           |
==================================================================
Outputs & filters:
-------------------------------------
  0 - output([tst_tb.id(0x7ff8bd22d120)], [tst_tb.customer_id(0x7ff8bd22d570)], [tst_tb.create_time(0x7ff8bd22d9c0)], [tst_tb.update_time(0x7ff8bd22de10)],
       [tst_tb.product(0x7ff8bd22e260)], [tst_tb.name(0x7ff8bd223840)], [tst_tb.lower_name(0x7ff8bd221bc0)]), filter(nil), rowset=16
      access([tst_tb.id(0x7ff8bd22d120)], [tst_tb.name(0x7ff8bd223840)], [tst_tb.lower_name(0x7ff8bd221bc0)], [tst_tb.customer_id(0x7ff8bd22d570)], [tst_tb.create_time(0x7ff8bd22d9c0)],
       [tst_tb.update_time(0x7ff8bd22de10)], [tst_tb.product(0x7ff8bd22e260)]), partitions(p0)
      is_index_back=true, is_global_index=false, 
      range_key([tst_tb.lower_name(0x7ff8bd221bc0)], [tst_tb.id(0x7ff8bd22d120)]), range(Product One,MIN ; Product One,MAX), 
      range_cond([tst_tb.lower_name(0x7ff8bd221bc0) = 'Product One'(0x7ff8bd2eb070)(0x7ff8bd2ea650)])

表數據

OceanBase函數索引實戰指南：從創建到優化，解決查詢性能痛點（附實戰命令）_OceanBase

未走索引

OceanBase函數索引實戰指南：從創建到優化，解決查詢性能痛點（附實戰命令）_函數索引_02

走索引

OceanBase函數索引實戰指南：從創建到優化，解決查詢性能痛點（附實戰命令）_函數索引_03

客户使用場景

場景一：給表字段添加函數索引進行優化

場景描述：客户有兩張表，A 表是在線熱更數據的，B 表是離線異步更新的，其中 A表中的customer_id字段是應用程序加密過的字段值，B表中的customer_id字段是原始數據，客户將該兩張表創建了聯合查詢的視圖，為了保持一致，對 B 表的customer_id 字段做了 md5 加密，但查詢視圖的時候 B 表沒有走索引；
集羣版本：OceanBase V4.2.1.10

/* 
A 表結構 
B 表與A表結構一致，且B表的數據是異步更新
*/
CREATE TABLE `tst_ta` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主鍵id，自動增長',
  `customer_id` varchar(50) DEFAULT NULL COMMENT '客户ID',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '創建時間',
  `update_time` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '更新時間',
  `product` varchar(10) DEFAULT NULL COMMENT '產品類型',
  PRIMARY KEY (`id`, `create_time`),
  KEY `idx_create_time` (`create_time`) BLOCK_SIZE 16384 LOCAL,
  KEY `ob_idx_customer_id` (`customer_id`) BLOCK_SIZE 16384 LOCAL
);

測試表模擬數據

INSERT INTO `tst_tb` (`customer_id`, `update_time`, `product`) VALUES ('CUST001', '2023-01-15 10:30:00', 'A');
INSERT INTO `tst_tb` (`customer_id`, `update_time`, `product`) VALUES ('CUST002', '2023-01-16 11:45:00', 'B');
INSERT INTO `tst_tb` (`customer_id`, `update_time`, `product`) VALUES ('CUST003', '2023-01-17 09:15:00', 'C');
INSERT INTO `tst_tb` (`customer_id`, `update_time`, `product`) VALUES ('CUST004', '2023-01-18 14:20:00', 'A');
INSERT INTO `tst_tb` (`customer_id`, `update_time`, `product`) VALUES ('CUST005', '2023-01-19 16:30:00', 'B');
INSERT INTO `tst_tb` (`customer_id`, `update_time`, `product`) VALUES ('CUST006', '2023-01-20 10:10:00', 'D');
INSERT INTO `tst_tb` (`customer_id`, `update_time`, `product`) VALUES ('CUST007', '2023-01-21 13:25:00', 'A');
INSERT INTO `tst_tb` (`customer_id`, `update_time`, `product`) VALUES ('CUST008', '2023-01-22 15:40:00', 'C');
INSERT INTO `tst_tb` (`customer_id`, `update_time`, `product`) VALUES ('CUST009', '2023-01-23 08:50:00', 'B');
INSERT INTO `tst_tb` (`customer_id`, `update_time`, `product`) VALUES ('CUST010', '2023-01-24 17:00:00', 'D');
INSERT INTO `tst_tb` (`customer_id`, `product`) VALUES ('CUST011', 'E');
INSERT INTO `tst_tb` (`customer_id`, `product`) VALUES ('CUST012', 'A');
INSERT INTO `tst_tb` (`customer_id`, `update_time`, `product`) VALUES ('CUST013', '2023-01-27 12:15:00', 'B');
INSERT INTO `tst_tb` (`customer_id`, `update_time`, `product`) VALUES ('CUST014', '2023-01-28 14:30:00', 'C');
INSERT INTO `tst_tb` (`customer_id`, `update_time`, `product`) VALUES ('CUST015', '2023-01-29 09:45:00', 'D');
INSERT INTO `tst_tb` (`customer_id`, `product`) VALUES ('CUST016', 'A');
INSERT INTO `tst_tb` (`customer_id`, `update_time`, `product`) VALUES ('CUST017', '2023-01-31 11:20:00', 'B');
INSERT INTO `tst_tb` (`customer_id`, `product`) VALUES ('CUST018', 'E');
INSERT INTO `tst_tb` (`customer_id`, `update_time`, `product`) VALUES ('CUST019', '2023-02-02 16:10:00', 'C');
INSERT INTO `tst_tb` (`customer_id`, `update_time`, `product`) VALUES ('CUST020', '2023-02-03 10:25:00', 'D');

tst_ta.customer_id 加密後的表數據

OceanBase函數索引實戰指南：從創建到優化，解決查詢性能痛點（附實戰命令）_OceanBase_04

tst_tb.customer_id 加密前的表數據

OceanBase函數索引實戰指南：從創建到優化，解決查詢性能痛點（附實戰命令）_MySQL_05

/*
將 customer_id 字段的值統一大寫後與'oceanbase'拼接，然後經過 md5 算法加密後統一小寫後記錄
*/
CREATE VIEW `tst_view` AS
( select id,customer_id,create_time,update_time,product from tst_ta)
union all 
(select id,
  lower(md5(concat(upper(customer_id), 'oceanbase'))) AS customer_id,
  create_time,update_time,product from tst_tb
);

未走索引

OceanBase函數索引實戰指南：從創建到優化，解決查詢性能痛點（附實戰命令）_函數索引_06

/*
CREATE TABLE `tst_ta` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主鍵id，自動增長',
  `customer_id` varchar(50) DEFAULT NULL COMMENT '客户ID',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '創建時間',
  `update_time` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '更新時間',
  `product` varchar(10) DEFAULT NULL COMMENT '產品類型',
  PRIMARY KEY (`id`, `create_time`),
  KEY `idx_create_time` (`create_time`) BLOCK_SIZE 16384 LOCAL,
  KEY `ob_idx_customer_id` (`customer_id`) BLOCK_SIZE 16384 LOCAL
);

*/
explain extended select * from tst_view where customer_id='366330e31189acd28639de9dd7b29cb1';
========================================================================
|ID|OPERATOR          |NAME                      |EST.ROWS|EST.TIME(us)|
------------------------------------------------------------------------
|0 |UNION ALL         |                          |2       |11          |
|1 |├─TABLE RANGE SCAN|tst_ta(ob_idx_customer_id)|1       |7           |
|2 |└─TABLE FULL SCAN |tst_tb                    |1       |4           |
========================================================================
Outputs & filters:
-------------------------------------
  0 - output([UNION([1])(0x7ff9ce07efa0)], [UNION([2])(0x7ff9ce07f360)], [UNION([3])(0x7ff9ce07f720)], [UNION([4])(0x7ff9ce07fae0)], [UNION([5])(0x7ff9ce07fea0)]), filter(nil), rowset=16
  1 - output([tst_ta.id(0x7ff9ce064850)], [tst_ta.customer_id(0x7ff9ce065120)], [tst_ta.create_time(0x7ff9ce0659f0)], [tst_ta.update_time(0x7ff9ce0662c0)],
       [tst_ta.product(0x7ff9ce066b90)]), filter(nil), rowset=16
      access([tst_ta.__pk_increment(0x7ff9ce084030)], [tst_ta.id(0x7ff9ce064850)], [tst_ta.customer_id(0x7ff9ce065120)], [tst_ta.create_time(0x7ff9ce0659f0)],
       [tst_ta.update_time(0x7ff9ce0662c0)], [tst_ta.product(0x7ff9ce066b90)]), partitions(p0)
      is_index_back=true, is_global_index=false, 
      range_key([tst_ta.customer_id(0x7ff9ce065120)], [tst_ta.__pk_increment(0x7ff9ce084030)]), range(366330e31189acd28639de9dd7b29cb1,MIN ; 366330e31189acd28639de9dd7b29cb1,
      MAX), 
      range_cond([tst_ta.customer_id(0x7ff9ce065120) = '366330e31189acd28639de9dd7b29cb1'(0x7ff8f0484a00)(0x7ff8f0483fe0)])
  2 - output([tst_tb.id(0x7ff9ce0797e0)], [lower(md5(concat(upper(tst_tb.customer_id(0x7ff9ce07ce60))(0x7ff9ce07b6b0), 'oceanbase')(0x7ff9ce07ade0))(0x7ff9ce07a510))(0x7ff9ce079c40)],
       [tst_tb.create_time(0x7ff9ce07d870)], [tst_tb.update_time(0x7ff9ce07e140)], [tst_tb.product(0x7ff9ce07ea10)]), filter([lower(md5(concat(upper(tst_tb.customer_id(0x7ff9ce07ce60))(0x7ff9ce07b6b0),
       'oceanbase')(0x7ff9ce07ade0))(0x7ff9ce07a510))(0x7ff9ce079c40) = '366330e31189acd28639de9dd7b29cb1'(0x7ff9ce0813f0)(0x7ff9ce0991d0)]), rowset=16
      access([tst_tb.id(0x7ff9ce0797e0)], [tst_tb.create_time(0x7ff9ce07d870)], [tst_tb.customer_id(0x7ff9ce07ce60)], [tst_tb.update_time(0x7ff9ce07e140)],
       [tst_tb.product(0x7ff9ce07ea10)]), partitions(p0)
      is_index_back=false, is_global_index=false, filter_before_indexback[false], 
      range_key([tst_tb.id(0x7ff9ce0797e0)], [tst_tb.create_time(0x7ff9ce07d870)]), range(MIN,MIN ; MAX,MAX)always true

走索引

OceanBase函數索引實戰指南：從創建到優化，解決查詢性能痛點（附實戰命令）_OceanBase_07

/*添加索引 1*/
create index tst_tb_fun_idx on tst_tb((lower(md5(concat(upper(customer_id), 'oceanbase')))));

/*
執行 select * from tst_view where customer_id='366330e31189acd28639de9dd7b29cb1'; 
相當於
select id,customer_id,create_time,update_time,product from tst_ta where customer_id='366330e31189acd28639de9dd7b29cb1'
union all
select id,
  lower(md5(concat(upper(customer_id), 'oceanbase'))) AS customer_id,
  create_time,update_time,product from tst_tb where lower(md5(concat(upper(customer_id), 'oceanbase')))='366330e31189acd28639de9dd7b29cb1';

而不是 select * from tst_tb where customer_id='366330e31189acd28639de9dd7b29cb1';
*/

/* 執行計劃 */
explain extended select * from tst_view where customer_id='366330e31189acd28639de9dd7b29cb1';
============================================================================
|ID|OPERATOR              |NAME                      |EST.ROWS|EST.TIME(us)|
----------------------------------------------------------------------------
|0 |EXCHANGE IN REMOTE    |                          |2       |22          |
|1 |└─EXCHANGE OUT REMOTE |                          |2       |19          |
|2 |  └─UNION ALL         |                          |2       |14          |
|3 |    ├─TABLE RANGE SCAN|tst_ta(ob_idx_customer_id)|1       |7           |
|4 |    └─TABLE RANGE SCAN|tst_tb(tst_tb_fun_idx)    |1       |8           |
============================================================================
Outputs & filters:
-------------------------------------
  0 - output([UNION([1])(0x7fd63d894110)], [UNION([2])(0x7fd63d8944d0)], [UNION([3])(0x7fd63d894890)], [UNION([4])(0x7fd63d894c50)], [UNION([5])(0x7fd63d895010)]), filter(nil)
  1 - output([UNION([1])(0x7fd63d894110)], [UNION([2])(0x7fd63d8944d0)], [UNION([3])(0x7fd63d894890)], [UNION([4])(0x7fd63d894c50)], [UNION([5])(0x7fd63d895010)]), filter(nil)
  2 - output([UNION([1])(0x7fd63d894110)], [UNION([2])(0x7fd63d8944d0)], [UNION([3])(0x7fd63d894890)], [UNION([4])(0x7fd63d894c50)], [UNION([5])(0x7fd63d895010)]), filter(nil), rowset=16
  3 - output([tst_ta.id(0x7fd63d864850)], [tst_ta.customer_id(0x7fd63d865120)], [tst_ta.create_time(0x7fd63d8659f0)], [tst_ta.update_time(0x7fd63d8662c0)],
       [tst_ta.product(0x7fd63d866b90)]), filter(nil), rowset=16
      access([tst_ta.__pk_increment(0x7fd63d8a5180)], [tst_ta.id(0x7fd63d864850)], [tst_ta.customer_id(0x7fd63d865120)], [tst_ta.create_time(0x7fd63d8659f0)],
       [tst_ta.update_time(0x7fd63d8662c0)], [tst_ta.product(0x7fd63d866b90)]), partitions(p0)
      is_index_back=true, is_global_index=false, 
      range_key([tst_ta.customer_id(0x7fd63d865120)], [tst_ta.__pk_increment(0x7fd63d8a5180)]), range(366330e31189acd28639de9dd7b29cb1,MIN ; 366330e31189acd28639de9dd7b29cb1,
      MAX), 
      range_cond([tst_ta.customer_id(0x7fd63d865120) = '366330e31189acd28639de9dd7b29cb1'(0x7fd76aeda000)(0x7fd76aed95e0)])
  4 - output([tst_tb.id(0x7fd63d886840)], [lower(md5(concat(upper(tst_tb.customer_id(0x7fd63d87ddf0))(0x7fd63d888710), 'oceanbase')(0x7fd63d887e40))(0x7fd63d887570))(0x7fd63d886ca0)],
       [tst_tb.create_time(0x7fd63d88a480)], [tst_tb.update_time(0x7fd63d88ad50)], [tst_tb.product(0x7fd63d88b620)]), filter(nil), rowset=16
      access([tst_tb.id(0x7fd63d886840)], [tst_tb.create_time(0x7fd63d88a480)], [tst_tb.customer_id(0x7fd63d87ddf0)], [tst_tb.update_time(0x7fd63d88ad50)],
       [tst_tb.product(0x7fd63d88b620)]), partitions(p0)
      is_index_back=true, is_global_index=false, 
      range_key([tst_tb.SYS_NC21$(0x7fd63d879390)], [tst_tb.id(0x7fd63d886840)], [tst_tb.create_time(0x7fd63d88a480)]), range(366330e31189acd28639de9dd7b29cb1,
      MIN,MIN ; 366330e31189acd28639de9dd7b29cb1,MAX,MAX), 
      range_cond([tst_tb.SYS_NC21$(0x7fd63d879390) = '366330e31189acd28639de9dd7b29cb1'(0x7fd76aedaeb0)(0x7fd76aeda490)])

tst_tb.customer_id='...'的執行計劃

OceanBase函數索引實戰指南：從創建到優化，解決查詢性能痛點（附實戰命令）_OceanBase_08

tst_tb.lower(md5(concat(upper(customer_id), 'oceanbase')))='...'的執行計劃

OceanBase函數索引實戰指南：從創建到優化，解決查詢性能痛點（附實戰命令）_函數索引_09

以上就是 OceanBase 函數索引的核心內容，如果你在操作中遇到問題，或是有獨到的優化建議，歡迎在評論區留言分享，後續我們還會分享更多OceanBase / MySQL 性能優化秘籍，下次見~

更多技術分享可掃碼關注查看

OceanBase函數索引實戰指南：從創建到優化，解決查詢性能痛點（附實戰命令）_MySQL_10

瑞藍創軟件博客

瑞藍創軟件博客

博客 / 詳情