Support BatchPointGet for Range and List Partition

[Defined2014]

Enhancement

We could support BatchPointGet plan for Range and List Partition and remove the code below.

tidb/planner/core/find_best_task.go

Lines 1126 to 1134 in e68c8ff

	// We can only build batch point get for hash partitions on a simple column now. This is
	// decided by the current implementation of `BatchPointGetExec::initialize()`, specifically,
	// the `getPhysID()` function. Once we optimize that part, we can come back and enable
	// BatchPointGet plan for more cases.
	hashPartColName = getHashOrKeyPartitionColumnName(ds.ctx, tblInfo)
	if hashPartColName == nil {
	canConvertPointGet = false
	}
	}

Some things need to consider,

1. Support keepOrder = true for partition table in BatchPointGetExec #45702
2. Explain info, like Support display partition info for batchPointGet on normal plan way #45888
3. join partition return wrong result #45720
4. The statement: select ... where _tidb_rowid in (...) returns an wrong result #45889

5. tidb/server/conn.go

   Line 1875 in 6b536ae

   case *plannercore.BatchPointGetPlan:

   , prefetchPointPlanKeys function, not sure if this will affect.

Most test cases are added in TestBatchPointGetTablePartition, but please take care about all of them.

[Defined2014]

Some interesting things

mysql> show create table t;
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                                                     |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t     | CREATE TABLE `t` (
  `a` int(11) NOT NULL,
  `b` int(11) DEFAULT NULL,
  PRIMARY KEY (`a`) /*T![clustered_index] CLUSTERED */
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
PARTITION BY RANGE (`a`)
(PARTITION `p0` VALUES LESS THAN (10),
 PARTITION `p1` VALUES LESS THAN (20)) |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> explain select * from t where a = 1 or a = 11;
+------------------------+---------+-----------+---------------+------------------------------------------------------+
| id                     | estRows | task      | access object | operator info                                        |
+------------------------+---------+-----------+---------------+------------------------------------------------------+
| TableReader_6          | 2.00    | root      | partition:all | data:TableRangeScan_5                                |
| └─TableRangeScan_5     | 2.00    | cop[tikv] | table:t       | range:[1,1], [11,11], keep order:false, stats:pseudo |
+------------------------+---------+-----------+---------------+------------------------------------------------------+
2 rows in set (0.01 sec)

mysql> explain select * from t where a in (1, 11);
+-------------------+---------+------+--------------------------+---------------------------------------------+
| id                | estRows | task | access object            | operator info                               |
+-------------------+---------+------+--------------------------+---------------------------------------------+
| Batch_Point_Get_1 | 2.00    | root | table:t, partition:p0,p1 | handle:[1 11], keep order:false, desc:false |
+-------------------+---------+------+--------------------------+---------------------------------------------+
1 row in set (0.00 sec)

mysql> explain select * from t where a in (1, 11, -1);
+------------------------+---------+-----------+---------------+---------------------------------------------------------------+
| id                     | estRows | task      | access object | operator info                                                 |
+------------------------+---------+-----------+---------------+---------------------------------------------------------------+
| TableReader_6          | 3.00    | root      | partition:all | data:TableRangeScan_5                                         |
| └─TableRangeScan_5     | 3.00    | cop[tikv] | table:t       | range:[-1,-1], [1,1], [11,11], keep order:false, stats:pseudo |
+------------------------+---------+-----------+---------------+---------------------------------------------------------------+
2 rows in set (0.00 sec)