FORCE INDEX - PolarDB - Alibaba Cloud Documentation Center

This topic describes the limits and usage of FORCE INDEX in PolarDB-X.

Background information

PolarDB-X supports both global secondary indexes and local indexes. A local index can be created directly on the table or as an index partition of a global secondary index. PolarDB-X extends the functionalities of FORCE INDEX and USE INDEX in MySQL by adding the support of two-level indexes, which means you can specify both global and local indexes in the hints.

Supported version

If the compute node version is earlier than 5.4.18, only single-level indexes are supported.
If the compute node version is 5.4.18 and the minor version is earlier than 1718157, only single-level indexes are supported.
If the compute node version is 5.4.18 and the minor version is 1718157 or later, two-level indexes are supported.
If the compute node version is 5.4.19 and the release date is earlier than July 10, 2024, only single-level indexes are supported.
If the compute node version is 5.4.19 and the release date is July 10, 2024 or later, two-level indexes are supported.
If the compute node version is later than 5.4.19, two-level indexes are supported.

Note

For information about how to view the version of an instance, see View and update the version of an instance.
For information about instance versioning, see Release notes.

Usage notes

When using FORCE INDEX or INDEX HINT, you must specify an index with its complete name. Prefix match is not supported
A specified local index is forced to be used when the following condition is met:
- It is an index partition of a global secondary index which is selected by the optimizer based on cost estimations.
- Note: Global secondary indexes that do not contain the specified local index partition will not be selected.
When FORCE INDEX (PRIMARY) is used, all global secondary indexes will not be selected.

Syntax

Single-level index

# 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})*/

Note

Parameters

gsi_name: the name of the global secondary index.
local_index_name: the name of the local index.
primary: the primary key.

Two-level index

# 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}])*/

Note

Parameters

gsi_name: the name of the global secondary index.
local_index_name: the name of the local index.
primary: the primary key.

Examples

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

Single-level index

Specify the local index l_i_order in FORCE INDEX:

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  
+---------------------+

Specify the global secondary index g_i_buyer in INDEX HINT:

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                      
+---------------------+

Two-level index

Specify the local index auto_shard_key_buyer_id, which is part of the global secondary index g_i_buyer, in FORCE INDEX:

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                  
+---------------------+

Use INDEX HINT to specify the local index auto_shard_key_seller_id, which is part of the global secondary index g_i_seller on the table nicknamed t1, and the local index L_I_ORDER on the table nicknamed t2.

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                                              
+---------------------+