FORCE INDEX_云原生数据库 PolarDB(PolarDB)-阿里云帮助中心

本文介绍PolarDB-X数据库使用FORCE INDEX（索引指定）功能的相关限制、注意事项以及使用示例。

背景

PolarDB-X提供了全局索引和局部索引（基于主表和全局索引都可以创建局部索引），并拓展了MySQL生态的FORCE INDEX和INDEX HINT的功能，让其可以支持两层索引指定，既可以指定全局索引，也可以指定局部索引。

支持版本

计算节点组件版本低于5.4.18，仅支持一层索引指定。
计算节点组件版本为5.4.18，小版本低于17181576，仅支持一层索引指定；小版本不低于17181576，支持一层索引指定和二层索引指定。
计算节点组件版本为5.4.19，发布日期低于2024-07-10，仅支持一层索引指定；发布日期不低于2024-07-10，支持一层索引指定和二层索引指定。
计算节点组件版本高于5.4.19，支持一层索引指定和二层索引指定。

说明

如何查看实例版本，请参见查看实例版本。
实例版本规则详情，请参见版本发布说明。

注意事项

索引名不支持前缀匹配，必须指定索引全名。
使用FORCE INDEX（LOCAL INDEX）时，局部索引生效规则：
- 包含该局部索引的全局索引会被基于代价进行选择，只有全局索引被选中时，该局部索引才生效。
- 不包含该局部索引的全局索引不会被选择。
使用FORCE INDEX(PRIMARY)时，所有的全局索引都不会被选择。

语法

一层索引指定

# FORCE INDEX
tbl_name [[AS] alias] [index_hint]
index_hint:
    FORCE INDEX({gsi_name|local_index_name|primary})

# INDEX HINT
/*+TDDL: INDEX({table_name|table_alias}, {local_index_name|gsi_name|primary})*/

说明

参数说明

gsi_name：全局索引名
local_index_name：局部索引名
primary：主键

二层索引指定

# FORCE INDEX
tbl_name [[AS] alias] [index_hint]
index_hint:
    FORCE INDEX(gsi_name[.{local_index_name|primary}] | local_index_name | primary[.{local_index_name|primary}])

# INDEX HINT
/*+TDDL: INDEX({table_name|table_alias}, {local_index_name|gsi_name|primary} [, {local_index_name|primary}])*/

说明

参数说明

gsi_name：全局索引名
local_index_name：局部索引名
primary：主键

示例

CREATE TABLE `t_order` (
	`id` bigint(11) NOT NULL AUTO_INCREMENT,
	`order_id` varchar(20) NOT NULL,
	`buyer_id` varchar(20) DEFAULT NULL,
	`seller_id` varchar(20) DEFAULT NULL,
	PRIMARY KEY (`id`),
	GLOBAL INDEX `g_i_buyer` (`buyer_id`) COVERING (`order_id`)
		PARTITION BY KEY(`buyer_id`)
		PARTITIONS 16,
	GLOBAL INDEX `g_i_seller` (`seller_id`) COVERING (`order_id`)
		PARTITION BY KEY(`seller_id`)
		PARTITIONS 16,
	KEY `l_i_order` (`order_id`),
	KEY `l_i_buyer` (`buyer_id`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8
PARTITION BY KEY(`order_id`)
PARTITIONS 16

一层索引指定

通过FORCE INDEX指定局部索引l_i_order，示例如下：

mysql> EXPLAIN SELECT * FROM T_ORDER FORCE INDEX(l_i_order);
+---------------------+
| LOGICAL EXECUTIONPLAN
+---------------------+
| Gather(concurrent=true)
|   LogicalView(tables="t_order[p1,p2,p3,...p16]", shardCount=16, sql="SELECT `id`, `order_id`, `buyer_id`, `seller_id` FROM `T_ORDER` AS `T_ORDER` FORCE INDEX(L_I_ORDER)") 
| HitCache:false   
| Source:PLAN_CACHE
| TemplateId: 3d1b35fa  
+---------------------+

通过INDEX HINT指定全局索引g_i_buyer，示例如下：

mysql> EXPLAIN /*TDDL:INDEX(T_ORDER, g_i_buyer)*/ SELECT * FROM T_ORDER;
+---------------------+
| LOGICAL EXECUTIONPLAN
+---------------------+
| Project(id="id", order_id="order_id", buyer_id="buyer_id", seller_id="seller_id") 
|   BKAJoin(condition="id = id AND order_id = order_id", type="inner")        
|     Gather(concurrent=true)           
|       IndexScan(tables="g_i_buyer_$27c2[p1,p2,p3,...p16]", shardCount=16, sql="SELECT `id`, `order_id`, `buyer_id` FROM `g_i_buyer_$27c2` AS `g_i_buyer_$27c2`") 
|     Gather(concurrent=true)           
|       LogicalView(tables="t_order[p1,p2,p3,...p16]", shardCount=16, sql="SELECT `id`, `order_id`, `seller_id` FROM `T_ORDER` AS `T_ORDER` FORCE INDEX(PRIMARY) WHERE (((`id`, `order_id`)) IN (...))") 
| HitCache:false                        
| Source:null                           
| TemplateId: NULL                      
+---------------------+

二层索引指定

通过FORCE INDEX指定全局索引g_i_buyer的局部索引auto_shard_key_buyer_id，示例如下：

mysql> EXPLAIN SELECT * FROM T_ORDER FORCE INDEX(g_i_buyer.auto_shard_key_buyer_id);
+---------------------+
| LOGICAL EXECUTIONPLAN                 
+---------------------+
| Project(id="id", order_id="order_id", buyer_id="buyer_id", seller_id="seller_id")
|   BKAJoin(condition="id = id AND order_id = order_id", type="inner")
|     Gather(concurrent=true)              
|       IndexScan(tables="g_i_buyer_$27c2[p1,p2,p3,...p16]", shardCount=16, sql="SELECT `id`, `order_id`, `buyer_id` FROM `g_i_buyer_$27c2` AS `g_i_buyer_$27c2` FORCE INDEX(AUTO_SHARD_KEY_BUYER_ID)")  
|     Gather(concurrent=true)           
|       LogicalView(tables="t_order[p1,p2,p3,...p16]", shardCount=16, sql="SELECT `id`, `order_id`, `seller_id` FROM `T_ORDER` AS `T_ORDER` FORCE INDEX(PRIMARY) WHERE (((`id`, `order_id`)) IN (...))") 
| HitCache:false                        
| Source:PLAN_CACHE                     
| TemplateId: ae278c70                  
+---------------------+

通过INDEX HINT指定别名表t1的全局索引g_i_seller的局部索引auto_shard_key_seller_id，以及别名表t2的主表的局部索引L_I_ORDER，示例如下：

mysql> EXPLAIN /*TDDL:index(t1, g_i_seller, auto_shard_key_seller_id) index(t2, primary, L_I_ORDER)*/ SELECT * FROM t_order t1 join t_order t2 on t1.id=t2.id where t1.buyer_id = '123456';
+---------------------+
| LOGICAL EXECUTIONPLAN                                         
+---------------------+
| BKAJoin(condition="id = id", type="inner")                    
|   Project(id="id", order_id="order_id", buyer_id="buyer_id", seller_id="seller_id")
|     BKAJoin(condition="id = id AND order_id = order_id", type="inner")
|       Gather(concurrent=true)                                 
|         IndexScan(tables="g_i_seller_$3e3d[p1,p2,p3,...p16]", shardCount=16, sql="SELECT `id`, `order_id`, `seller_id` FROM `g_i_seller_$3e3d` AS `g_i_seller_$3e3d` FORCE INDEX(AUTO_SHARD_KEY_SELLER_ID)")                   
|       Gather(concurrent=true)                                 
|         LogicalView(tables="t_order[p1,p2,p3,...p16]", shardCount=16, sql="SELECT `id`, `order_id`, `buyer_id` FROM `t_order` AS `t_order` FORCE INDEX(PRIMARY) WHERE ((`buyer_id` = ?) AND (((`id`, `order_id`)) IN (...)))") 
|   Gather(concurrent=true)                                     
|     LogicalView(tables="t_order[p1,p2,p3,...p16]", shardCount=16, sql="SELECT `id`, `order_id`, `buyer_id`, `seller_id` FROM `t_order` AS `t_order` FORCE INDEX(L_I_ORDER) WHERE (`id` IN (...))")                             
| HitCache:false                                                
| Source:null                                                   
| TemplateId: NULL                                              
+---------------------+