PolarDB:Automatic FORCE INDEX

Background information

When PolarDB-X receives a query, the optimizer on the compute node divides the query into two parts: the part that can be pushed down and the other that cannot. The part that can be pushed down is translated into a physical SQL. Typically, the physical SQL is sent to the data node where appropriate local indexes are selected based on cost.

However, the chosen index may not always be optimal due to defects of the cost model or inaccurate statistics. Therefore, in certain situations, the optimizer on the compute node determines the optimal local index to be used and specifies it in the physical SQL using a FORCE INDEX hint. Such an automatic FORCE INDEX mechanism minimizes the risk of choosing a wrong index.

Version requirements

• The instance version must be 5.4.18-17181576 or later to support this mechanism.

• For existing instances that are upgraded to 5.4.18-17181576 or later, the mechanism is supported but disabled by default.

• For newly created instances:

  • If the compute node version is 5.4.18 or earlier, the mechanism is not supported.

  • If the compute node version is 5.4.18 but the minor version is earlier than 17181576, the mechanism is not supported. If the compute node version is 5.4.18 and the minor version is 17181576 or late, the mechanism is enabled by default.

  • If the compute node version is 5.4.19 but the release date is before July 10, 2024, the mechanism is not supported. If the compute node version is 5.4.19 and the release date is on July 10, 2024 or later, the mechanism is enabled by default.

  • If the compute node version is 5.4.19 or later, the mechanism is enabled by default.

Usage notes

• Querying a single table or broadcast table does not trigger the automatic FORCE INDEX mechanism.

• A physical SQL that joins tables does not trigger the automatic FORCE INDEX mechanism.

• Queries pushed down to a single shard do not trigger the automatic FORCE INDEX mechanism.

• Local indexes are selected by the following mechanisms in descending order of priority:

  • Manual FORCE INDEX

  • Automatic FORCE INDEX

  • XPlan

  • Indexes selection by data nodes

How automatic FORCE INDEX works

Primary key index and unique index

When the following conditions are met, a primary key index or a unique index can be automatically forced to be used:

• Columns used with the equivalence condition are collected by the optimizer and are original columns.

• The collected columns cover the primary key index or unique index.

Common secondary index

When the following conditions are met, a common secondary index can be automatically forced to be used:

• A query only contains equivalence conditions and conditions that cannot use indexes.

• Columns used with the equivalence condition and columns mentioned in the ORDER BY clause are collected by the optimizer and are original columns.

• The collected columns are covered by index prefixes.

Example

Create an example table that contains a unique index UK and a composite index LK:

CREATE TABLE `example` (
	`a` int(11) NOT NULL,
	`b` int(11) NOT NULL,
	`c` int(11) NOT NULL,
	`d` int(11) NOT NULL,
	UNIQUE KEY `UK` (`a`, `b`),
	KEY `LK` (`b`, `c`, `d`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4
PARTITION BY KEY(`a`)
PARTITIONS 4

select * from example where (a,b) in ((1,2),((3,4))) and c = 1 order by d

mysql> explain select * from example where (a,b) in ((1,2),((3,4))) and c = 1 order by d;
+------------------------+
| LOGICAL EXECUTIONPLAN                                                      
+------------------------+
| MergeSort(sort="d ASC")                                                    
|   LogicalView(tables="example[p1,p4]", shardCount=2, sql="SELECT `a`, `b`, `c`, `d` FROM `example` AS `example` FORCE INDEX(UK) WHERE ((((`a`, `b`)) IN(?)) AND (`c` = ?)) ORDER BY `d`", pruningInfo="all size:2*2(part), pruning size:2") |
| HitCache:false                                                             
| Source:PLAN_CACHE                                                          
| TemplateId: 3148a498                                                       
+------------------------+

select * from example where (a+1,b) in ((1,2),((3,4))) and c = 1 order by d

mysql> explain select * from example where (a+1,b) in ((1,2),((3,4))) and c = 1 order by d;
+------------------------+
| LOGICAL EXECUTIONPLAN    
+------------------------+
| MergeSort(sort="d ASC")       
|   LogicalView(tables="example[p1,p2,p3,p4]", shardCount=4, sql="SELECT `a`, `b`, `c`, `d` FROM `example` AS `example` WHERE (((((`a` + ?), `b`)) IN(?)) AND (`c` = ?)) ORDER BY `d`") |
| HitCache:false            
| Source:PLAN_CACHE       
| TemplateId: 5ad46a6e  
+------------------------+

select * from example where (b) in (1,2) and a > b order by c

mysql> explain select * from example where (b) in (1,2) and a > b order by c;
+------------------------+
| LOGICAL EXECUTIONPLAN  
+------------------------+
| MergeSort(sort="c ASC") 
|   LogicalView(tables="example[p1,p2,p3,p4]", shardCount=4, sql="SELECT `a`, `b`, `c`, `d` FROM `example` AS `example` FORCE INDEX(LK) WHERE ((`a` > `b`) AND (`b` IN(?))) ORDER BY `c`") |
| HitCache:false          
| Source:PLAN_CACHE       
| TemplateId: 3a4124bd    
+------------------------+

select * from example where (b) in (1,2) and c > 1 order by d

mysql> explain select * from example where (b) in (1,2) and c > 1 order by d;
+------------------------+
| LOGICAL EXECUTIONPLAN
+------------------------+
| MergeSort(sort="d ASC")
|   LogicalView(tables="example[p1,p2,p3,p4]", shardCount=4, sql="SELECT `a`, `b`, `c`, `d` FROM `example` AS `example` WHERE ((`b` IN(?)) AND (`c` > ?)) ORDER BY `d`") |
| HitCache:false                                                                
| Source:PLAN_CACHE                                                             
| TemplateId: f01b447f                                                          
+------------------------+