Support SQL of Prepare use SPM to bind SQL.

[superlzs0476]

Feature Request

Is your feature request related to a problem? Please describe:

TiDB can not support bind SQL of Prepare if the application use Prepare SQL that has an incorrect execution plan, we want to use SPM to bind this type of SQL.

Describe the feature you'd like:

SPM support SQL of Prepare.

Describe alternatives you've considered:

Teachability, Documentation, Adoption, Migration Strategy:

[gekaiwen]

We faced the same question.
e.g.
sql : select x from t limit 1,20
normal SQL parameterization: select x from t limit ...
prepared statement SQL parameterization: select x from t limit ?,?

[SunRunAway]

@superlzs0476 @gekaiwen
I tested against the Master branch and Binding SQL does support prepare queries.

mysql> create table t(a int, key (a));
Query OK, 0 rows affected (0.00 sec)

mysql> PREPARE stmt FROM 'select * from t where a = 40';
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE SESSION BINDING FOR select * from t where a = 1 USING select * from t ignore index (a) where a = 1;
Query OK, 0 rows affected (0.00 sec)

mysql> EXECUTE stmt;
Empty set (0.00 sec)

mysql> explain for connection 3;
+-------------------------+----------+-----------+---------------+--------------------------------+
| id                      | estRows  | task      | access object | operator info                  |
+-------------------------+----------+-----------+---------------+--------------------------------+
| TableReader_7           | 10.00    | root      |               | data:Selection_6               |
| └─Selection_6           | 10.00    | cop[tikv] |               | eq(test.t.a, 40)               |
|   └─TableFullScan_5     | 10000.00 | cop[tikv] | table:t       | keep order:false, stats:pseudo |
+-------------------------+----------+-----------+---------------+--------------------------------+
3 rows in set (0.00 sec)

mysql> CREATE SESSION BINDING FOR select * from t where a = 1 USING select * from t use index (a) where a = 1;
Query OK, 0 rows affected (0.00 sec)

mysql> EXECUTE stmt;
Empty set (0.00 sec)

mysql> explain for connection 3;
+------------------------+---------+-----------+---------------------+-----------------------------------------------+
| id                     | estRows | task      | access object       | operator info                                 |
+------------------------+---------+-----------+---------------------+-----------------------------------------------+
| IndexReader_6          | 10.00   | root      |                     | index:IndexRangeScan_5                        |
| └─IndexRangeScan_5     | 10.00   | cop[tikv] | table:t, index:a(a) | range:[40,40], keep order:false, stats:pseudo |
+------------------------+---------+-----------+---------------------+-----------------------------------------------+
2 rows in set (0.00 sec)

[SunRunAway]

Closing it.
Maybe you're facing the bug #19836

[zz-jason]

@SunRunAway BTW, we can use @@last_plan_from_cache to check whether the last execution plan comes from Plan Cache. Similarly, it's better to add a session variable to check whether the last execution plan comes from SQL Binding Baselines. See #16425 for details.