prepared plan cache support point get plan with extra filter

[dbsid]

Enhancement

Currently the prepared plan cache does not support point get plan with extra filter.

1. select bankno, c1, c2 from t WHERE c1= ? can be cached
2. select bankno, c1, c2 from t WHERE c1= ? and c2 = ? can not be cached.
3. This issue applies to multi-column unique key. If the unique key is (c1,c2), then select bankno, c1, c2 from t WHERE c1= ? and c2 = ? and c3 = ? can not be cached
4. For multi-column unique key, if only part of unique key columns used in predicated, the plan can be cached. For example, if the unique key is (c1,c2), then select bankno, c1, c2 from t WHERE c1= ? and c3 = ? can be cached
5. This issue applies to query that don't have parameters. select bankno, c1, c2 from t WHERE c1= 'abc' and c2 = '1' can not be cached; select bankno, c1, c2 from t WHERE c1= 'abc' can be cached.

mysql> create table t
    -> (
    ->     bankno varchar(128),
    ->     c1 varchar(32),
    ->     c2 varchar(2),
    ->     UNIQUE KEY `UK1` (`c1`)
    -> );
Query OK, 0 rows affected (0.36 sec)

mysql>
mysql> set @c1='11001561000101600000197';
Query OK, 0 rows affected (0.04 sec)

mysql> set @c2='1';
Query OK, 0 rows affected (0.04 sec)

mysql> prepare stmt from 'select bankno, c1, c2 from t WHERE c1= ? AND c2= ?';
Query OK, 0 rows affected (0.04 sec)

mysql> execute stmt using @c1, @c2;
Empty set (0.04 sec)

mysql> execute stmt using @c1, @c2;
Empty set (0.03 sec)

mysql> select @@last_plan_from_cache;
+------------------------+
| @@last_plan_from_cache |
+------------------------+
|                      0 |
+------------------------+
1 row in set (0.04 sec)

mysql> select connection_id();
+-----------------+
| connection_id() |
+-----------------+
|             119 |
+-----------------+
1 row in set (0.04 sec)

mysql> execute stmt using @c1, @c2;
Empty set (0.04 sec)

mysql> explain for connection 119;
+-------------------+---------+---------+------+------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+--------+------+
| id                | estRows | actRows | task | access object          | execution info                                                                                                                                                                                                                              | operator info        | memory | disk |
+-------------------+---------+---------+------+------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+--------+------+
| Selection_6       | 0.00    | 0       | root |                        | time:389.2µs, loops:1                                                                                                                                                                                                                       | eq(hzbank.t.c2, "1") | N/A    | N/A  |
| └─Point_Get_5     | 1.00    | 0       | root | table:t, index:UK1(c1) | time:387.9µs, loops:1, Get:{num_rpc:1, total_time:356.2µs}, scan_detail: {total_process_keys: 0, total_keys: 1, rocksdb: {delete_skipped_count: 0, key_skipped_count: 0, block: {cache_hit_count: 0, read_count: 0, read_byte: 0 Bytes}}}   |                      | N/A    | N/A  |
+-------------------+---------+---------+------+------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+--------+------+
2 rows in set (0.04 sec)

[qw4990]

This issue can be solved by #28478:

mysql>  execute stmt using @c1, @c2;
Empty set (0.00 sec)

mysql>  execute stmt using @c1, @c2;
Empty set (0.00 sec)

mysql> select @@last_plan_from_cache;
+------------------------+
| @@last_plan_from_cache |
+------------------------+
|                      1 |
+------------------------+
1 row in set (0.00 sec)

But the cached plan is IndexLookUp instead of PointGet:

+----------------------------+---------+---------+-----------+------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------+--------+------+
| id                         | estRows | actRows | task      | access object          | execution info                                                                                                                                                    | operator info                                                                               | memory | disk |
+----------------------------+---------+---------+-----------+------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------+--------+------+
| IndexLookUp_9              | 0.00    | 0       | root      |                        | time:367µs, loops:1,                                                                                                                                              |                                                                                             | N/A    | N/A  |
| ├─Selection_7(Build)       | 0.00    | 0       | cop[tikv] |                        | time:291.7µs, loops:1, cop_task: {num: 1, max: 240.9µs, proc_keys: 0, rpc_num: 1, rpc_time: 231.3µs, copr_cache_hit_ratio: 0.00}, tikv_task:{time:0s, loops:0}    | eq(test.t.c1, "11001561000101600000197")                                                    | N/A    | N/A  |
| │ └─IndexRangeScan_5       | 1.00    | 0       | cop[tikv] | table:t, index:uk1(c1) | tikv_task:{time:0s, loops:0}                                                                                                                                      | range:["11001561000101600000197","11001561000101600000197"], keep order:false, stats:pseudo | N/A    | N/A  |
| └─Selection_8(Probe)       | 0.00    | 0       | cop[tikv] |                        |                                                                                                                                                                   | eq(test.t.c2, "1")                                                                          | N/A    | N/A  |
|   └─TableRowIDScan_6       | 0.00    | 0       | cop[tikv] | table:t                |                                                                                                                                                                   | keep order:false, stats:pseudo                                                              | N/A    | N/A  |
+----------------------------+---------+---------+-----------+------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------+--------+------+
5 rows in set (0.00 sec)

PointGet plans are quite dangerous for plan-cache since they may be over-optimized, so PointGet plans are not allowed to be cached in some cases, and this issue is one of these cases.
In #28478, I modified the strategy which lets the optimizer does not generate PointGet plans in these cases, and so the plan-cache can be working for this issue again.

There is still some room for improvement like caching a PointGet plan in this case, which is left for further optimization.

[dbsid]

Consider this kind of queries is common, now the plan changed from point get to index lookup, one round trip becomes two round trips, we should cache PointGet in this case.

[dbsid]

For none clustered indexes table, the change of #28478 should bring little performance change, either original PointGet or newIndexLookUp need two rpc calls.
For clustered indexes, with the pr #28478, the plan change from PointGet to IndexReader, which is expected.

test case

CREATE TABLE `t` (
  `c1` varchar(3) NOT NULL,
  `c2` varchar(32) NOT NULL,
  `c3` varchar(1) NOT NULL,
  primary KEY `T_PK1` (`c1`) /*T![clustered_index] CLUSTERED */
);

insert into t values ('1', '1', '1');

set @c1='1';
set @c2='1';
prepare stmt from "select * from t where c1= ? AND c2= ?";
execute stmt using @c1, @c2;
explain for connection 2187;

explain analyze select * from t where c1='1' AND c2= '1';

output

mysql> show variables where variable_name = 'tidb_enable_clustered_index';
+-----------------------------+-------+
| Variable_name               | Value |
+-----------------------------+-------+
| tidb_enable_clustered_index | ON    |
+-----------------------------+-------+
1 row in set, 1 warning (0.05 sec)

mysql> CREATE TABLE `t` (
    ->   `c1` varchar(3) NOT NULL,
    ->   `c2` varchar(32) NOT NULL,
    ->   `c3` varchar(1) NOT NULL,
    ->   primary KEY (`c1`) /*T![clustered_index] CLUSTERED */
    -> );
Query OK, 0 rows affected (0.13 sec)

mysql> show create table t;
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t     | CREATE TABLE `t` (
  `c1` varchar(3) NOT NULL,
  `c2` varchar(32) NOT NULL,
  `c3` varchar(1) NOT NULL,
  PRIMARY KEY (`c1`) /*T![clustered_index] CLUSTERED */
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.04 sec)

mysql> insert into t values ('1', '1', '1');
Query OK, 1 row affected (0.04 sec)

mysql> set @c1='1';
Query OK, 0 rows affected (0.04 sec)

mysql> set @c2='1';
Query OK, 0 rows affected (0.03 sec)

mysql> prepare stmt from "select * from t where c1= ? AND c2= ?";
Query OK, 0 rows affected (0.04 sec)

mysql> execute stmt using @c1, @c2;
explain for connection 2525;
+----+----+----+
| c1 | c2 | c3 |
+----+----+----+
| 1  | 1  | 1  |
+----+----+----+
1 row in set (0.04 sec)

mysql> explain for connection 2525;
+--------------------------+---------+---------+-----------+---------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------+--------+------+
| id                       | estRows | actRows | task      | access object | execution info                                                                                                                                                                                              | operator info                                   | memory | disk |
+--------------------------+---------+---------+-----------+---------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------+--------+------+
| TableReader_7            | 0.01    | 1       | root      |               | time:621.8µs, loops:2, cop_task: {num: 1, max: 527.5µs, proc_keys: 1, rpc_num: 1, rpc_time: 508.2µs, copr_cache_hit_ratio: 0.00}                                                                            | data:Selection_6                                | N/A    | N/A  |
| └─Selection_6            | 0.01    | 1       | cop[tikv] |               | tikv_task:{time:0s, loops:1}, scan_detail: {total_process_keys: 1, total_keys: 2, rocksdb: {delete_skipped_count: 0, key_skipped_count: 1, block: {cache_hit_count: 0, read_count: 0, read_byte: 0 Bytes}}} | eq(test.t.c1, "1"), eq(test.t.c2, "1")          | N/A    | N/A  |
|   └─TableRangeScan_5     | 1.00    | 1       | cop[tikv] | table:t       | tikv_task:{time:0s, loops:1}                                                                                                                                                                                | range:["1","1"], keep order:false, stats:pseudo | N/A    | N/A  |
+--------------------------+---------+---------+-----------+---------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------+--------+------+
3 rows in set (0.04 sec)

mysql> explain analyze select * from t where c1='1' AND c2= '1';
+-------------------+---------+---------+------+--------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------+-----------+------+
| id                | estRows | actRows | task | access object                        | execution info                                                                                                                                                                                                                              | operator info      | memory    | disk |
+-------------------+---------+---------+------+--------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------+-----------+------+
| Selection_6       | 0.00    | 1       | root |                                      | time:496.7µs, loops:2                                                                                                                                                                                                                       | eq(test.t.c2, "1") | 387 Bytes | N/A  |
| └─Point_Get_5     | 1.00    | 1       | root | table:t, clustered index:PRIMARY(c1) | time:466.9µs, loops:3, Get:{num_rpc:1, total_time:422.7µs}, scan_detail: {total_process_keys: 1, total_keys: 1, rocksdb: {delete_skipped_count: 0, key_skipped_count: 0, block: {cache_hit_count: 0, read_count: 0, read_byte: 0 Bytes}}}   |                    | N/A       | N/A  |
+-------------------+---------+---------+------+--------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------+-----------+------+
2 rows in set (0.04 sec)

[dbsid]

A range predicates on the primary key(clustered key) becomes full table scan, either prepared or not, not sure it's expected.

>prepare stmt from "select * from t where c1 >= ? AND c1 <= ?";
Query OK, 0 rows affected (0.00 sec)

>execute stmt using @c1, @c2
    -> ;
+----+----+----+
| c1 | c2 | c3 |
+----+----+----+
| 1  | 1  | 1  |
+----+----+----+
1 row in set (0.01 sec)

>explain for connection 2187;
+-------------------------+---------+---------+-----------+---------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------------------------------+--------+------+
| id                      | estRows | actRows | task      | access object | execution info                                                                                                                                                                                              | operator info                              | memory | disk |
+-------------------------+---------+---------+-----------+---------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------------------------------+--------+------+
| TableReader_7           | 0.00    | 1       | root      |               | time:1.25ms, loops:2, cop_task: {num: 1, max: 1.15ms, proc_keys: 1, rpc_num: 1, rpc_time: 1.13ms, copr_cache_hit_ratio: 0.00}                                                                               | data:Selection_6                           | N/A    | N/A  |
| └─Selection_6           | 0.00    | 1       | cop[tikv] |               | tikv_task:{time:0s, loops:1}, scan_detail: {total_process_keys: 1, total_keys: 2, rocksdb: {delete_skipped_count: 0, key_skipped_count: 1, block: {cache_hit_count: 0, read_count: 0, read_byte: 0 Bytes}}} | ge(hzbank.t.c1, "1"), le(hzbank.t.c1, "1") | N/A    | N/A  |
|   └─TableFullScan_5     | 1.00    | 1       | cop[tikv] | table:t       | tikv_task:{time:0s, loops:1}                                                                                                                                                                                | keep order:false, stats:pseudo             | N/A    | N/A  |
+-------------------------+---------+---------+-----------+---------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------------------------------+--------+------+
3 rows in set (0.00 sec)

[email protected]>explain select * from t where c1 >=1 and c1 <= 1;
+-------------------------+---------+-----------+---------------+----------------------------------------------------------------------------------+
| id                      | estRows | task      | access object | operator info                                                                    |
+-------------------------+---------+-----------+---------------+----------------------------------------------------------------------------------+
| TableReader_7           | 0.80    | root      |               | data:Selection_6                                                                 |
| └─Selection_6           | 0.80    | cop[tikv] |               | ge(cast(hzbank.t.c1, double BINARY), 1), le(cast(hzbank.t.c1, double BINARY), 1) |
|   └─TableFullScan_5     | 1.00    | cop[tikv] | table:t       | keep order:false, stats:pseudo                                                   |
+-------------------------+---------+-----------+---------------+----------------------------------------------------------------------------------+
3 rows in set (0.00 sec)

[email protected]>explain select * from t where c1 >=1 and c1 <= 4;
+-------------------------+---------+-----------+---------------+----------------------------------------------------------------------------------+
| id                      | estRows | task      | access object | operator info                                                                    |
+-------------------------+---------+-----------+---------------+----------------------------------------------------------------------------------+
| TableReader_7           | 0.80    | root      |               | data:Selection_6                                                                 |
| └─Selection_6           | 0.80    | cop[tikv] |               | ge(cast(hzbank.t.c1, double BINARY), 1), le(cast(hzbank.t.c1, double BINARY), 4) |
|   └─TableFullScan_5     | 1.00    | cop[tikv] | table:t       | keep order:false, stats:pseudo                                                   |
+-------------------------+---------+-----------+---------------+----------------------------------------------------------------------------------+
3 rows in set (0.00 sec)

[email protected]>prepare stmt from "select * from t where c1 >= ? AND c1 <= ? and c2=?";
Query OK, 0 rows affected (0.00 sec)

[email protected]>execute stmt using @c1, @c2, @c2;
+----+----+----+
| c1 | c2 | c3 |
+----+----+----+
| 1  | 1  | 1  |
+----+----+----+
1 row in set (0.00 sec)

[email protected]>explain for connection 2187;
+-------------------------+----------+---------+-----------+---------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------+--------+------+
| id                      | estRows  | actRows | task      | access object | execution info                                                                                                                                                                                              | operator info                                                    | memory | disk |
+-------------------------+----------+---------+-----------+---------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------+--------+------+
| TableReader_7           | 0.01     | 1       | root      |               | time:750.2µs, loops:2, cop_task: {num: 1, max: 653µs, proc_keys: 1, rpc_num: 1, rpc_time: 634.3µs, copr_cache_hit_ratio: 0.00}                                                                              | data:Selection_6                                                 | N/A    | N/A  |
| └─Selection_6           | 0.01     | 1       | cop[tikv] |               | tikv_task:{time:0s, loops:1}, scan_detail: {total_process_keys: 1, total_keys: 2, rocksdb: {delete_skipped_count: 0, key_skipped_count: 1, block: {cache_hit_count: 0, read_count: 0, read_byte: 0 Bytes}}} | eq(hzbank.t.c2, "1"), ge(hzbank.t.c1, "1"), le(hzbank.t.c1, "1") | N/A    | N/A  |
|   └─TableFullScan_5     | 10000.00 | 1       | cop[tikv] | table:t       | tikv_task:{time:0s, loops:1}                                                                                                                                                                                | keep order:false, stats:pseudo                                   | N/A    | N/A  |
+-------------------------+----------+---------+-----------+---------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------+--------+------+
3 rows in set (0.00 sec)