DROP [GLOBAL|SESSION] BINDING

DROP [GLOBAL|SESSION] BINDING

DROP BINDING 语句用于删除指定的 SQL 绑定。绑定可用于将优化器 Hint 插入语句中，而无需更改底层查询。

BINDING 语句可以在 GLOBAL 或者 SESSION 作用域内删除执行计划绑定。在不指定作用域时，默认的作用域为 SESSION。

语法图

DropBindingStmt

GlobalScope

BindableStmt

1. DropBindingStmt ::=
2. 'DROP' GlobalScope 'BINDING' 'FOR' ( BindableStmt ( 'USING' BindableStmt )? )
3. |   ('SQL' 'DIGEST' SqlDigest)

5. GlobalScope ::=
6. ( 'GLOBAL' | 'SESSION' )?

8. BindableStmt ::=
9. ( SelectStmt | UpdateStmt | InsertIntoStmt | ReplaceIntoStmt | DeleteStmt )

示例

你可以根据 SQL 语句或 sql_digest 删除绑定。

下面的示例演示如何根据 SQL 语句删除绑定。

1. CREATE TABLE t1 (
2. ->  id INT NOT NULL PRIMARY KEY auto_increment,
3. ->  b INT NOT NULL,
4. ->  pad VARBINARY(255),
5. ->  INDEX(b)
6. -> );
7. Query OK, 0 rows affected (0.07 sec)

9. INSERT INTO t1 SELECT NULL, FLOOR(RAND()*1000), RANDOM_BYTES(255) FROM dual;
10. Query OK, 1 row affected (0.01 sec)
11. Records: 1  Duplicates: 0  Warnings: 0

13. INSERT INTO t1 SELECT NULL, FLOOR(RAND()*1000), RANDOM_BYTES(255) FROM t1 a JOIN t1 b JOIN t1 c LIMIT 100000;
14. Query OK, 1 row affected (0.00 sec)
15. Records: 1  Duplicates: 0  Warnings: 0

17. INSERT INTO t1 SELECT NULL, FLOOR(RAND()*1000), RANDOM_BYTES(255) FROM t1 a JOIN t1 b JOIN t1 c LIMIT 100000;
18. Query OK, 8 rows affected (0.00 sec)
19. Records: 8  Duplicates: 0  Warnings: 0

21. INSERT INTO t1 SELECT NULL, FLOOR(RAND()*1000), RANDOM_BYTES(255) FROM t1 a JOIN t1 b JOIN t1 c LIMIT 100000;
22. Query OK, 1000 rows affected (0.04 sec)
23. Records: 1000  Duplicates: 0  Warnings: 0

25. INSERT INTO t1 SELECT NULL, FLOOR(RAND()*1000), RANDOM_BYTES(255) FROM t1 a JOIN t1 b JOIN t1 c LIMIT 100000;
26. Query OK, 100000 rows affected (1.74 sec)
27. Records: 100000  Duplicates: 0  Warnings: 0

29. INSERT INTO t1 SELECT NULL, FLOOR(RAND()*1000), RANDOM_BYTES(255) FROM t1 a JOIN t1 b JOIN t1 c LIMIT 100000;
30. Query OK, 100000 rows affected (2.15 sec)
31. Records: 100000  Duplicates: 0  Warnings: 0

33. INSERT INTO t1 SELECT NULL, FLOOR(RAND()*1000), RANDOM_BYTES(255) FROM t1 a JOIN t1 b JOIN t1 c LIMIT 100000;
34. Query OK, 100000 rows affected (2.64 sec)
35. Records: 100000  Duplicates: 0  Warnings: 0

37. SELECT SLEEP(1);
38. +----------+
39. | SLEEP(1) |
40. +----------+
41. |        0 |
42. +----------+
43. 1 row in set (1.00 sec)

45. ANALYZE TABLE t1;
46. Query OK, 0 rows affected (1.33 sec)

48. EXPLAIN ANALYZE SELECT * FROM t1 WHERE b = 123;
49. +-------------------------------+---------+---------+-----------+----------------------+---------------------------------------------------------------------------+-----------------------------------+----------------+------+
50. | id                            | estRows | actRows | task      | access object        | execution info                                                            | operator info                     | memory         | disk |
51. +-------------------------------+---------+---------+-----------+----------------------+---------------------------------------------------------------------------+-----------------------------------+----------------+------+
52. | IndexLookUp_10                | 583.00  | 297     | root      |                      | time:10.545072ms, loops:2, rpc num: 1, rpc time:398.359µs, proc keys:297  |                                   | 109.1484375 KB | N/A  |
53. | ├─IndexRangeScan_8(Build)     | 583.00  | 297     | cop[tikv] | table:t1, index:b(b) | time:0s, loops:4                                                          | range:[123,123], keep order:false | N/A            | N/A  |
54. | └─TableRowIDScan_9(Probe)     | 583.00  | 297     | cop[tikv] | table:t1             | time:12ms, loops:4                                                        | keep order:false                  | N/A            | N/A  |
55. +-------------------------------+---------+---------+-----------+----------------------+---------------------------------------------------------------------------+-----------------------------------+----------------+------+
56. 3 rows in set (0.02 sec)

58. CREATE SESSION BINDING FOR
59. ->  SELECT * FROM t1 WHERE b = 123
60. -> USING
61. ->  SELECT * FROM t1 IGNORE INDEX (b) WHERE b = 123;
62. Query OK, 0 rows affected (0.00 sec)

64. EXPLAIN ANALYZE  SELECT * FROM t1 WHERE b = 123;
65. +-------------------------+-----------+---------+-----------+---------------+--------------------------------------------------------------------------------+--------------------+---------------+------+
66. | id                      | estRows   | actRows | task      | access object | execution info                                                                 | operator info      | memory        | disk |
67. +-------------------------+-----------+---------+-----------+---------------+--------------------------------------------------------------------------------+--------------------+---------------+------+
68. | TableReader_7           | 583.00    | 297     | root      |               | time:222.32506ms, loops:2, rpc num: 1, rpc time:222.078952ms, proc keys:301010 | data:Selection_6   | 88.6640625 KB | N/A  |
69. | └─Selection_6           | 583.00    | 297     | cop[tikv] |               | time:224ms, loops:298                                                          | eq(test.t1.b, 123) | N/A           | N/A  |
70. |   └─TableFullScan_5     | 301010.00 | 301010  | cop[tikv] | table:t1      | time:220ms, loops:298                                                          | keep order:false   | N/A           | N/A  |
71. +-------------------------+-----------+---------+-----------+---------------+--------------------------------------------------------------------------------+--------------------+---------------+------+
72. 3 rows in set (0.22 sec)

74. SHOW SESSION BINDINGS\G
75. *************************** 1. row ***************************
76. Original_sql: select * from t1 where b = ?
77. Bind_sql: SELECT * FROM t1 IGNORE INDEX (b) WHERE b = 123
78. Default_db: test
79. Status: using
80. Create_time: 2020-05-22 14:38:03.456
81. Update_time: 2020-05-22 14:38:03.456
82. Charset: utf8mb4
83. Collation: utf8mb4_0900_ai_ci
84. 1 row in set (0.00 sec)

86. DROP SESSION BINDING FOR SELECT * FROM t1 WHERE b = 123;
87. Query OK, 0 rows affected (0.00 sec)

89. EXPLAIN ANALYZE  SELECT * FROM t1 WHERE b = 123;
90. +-------------------------------+---------+---------+-----------+----------------------+-------------------------------------------------------------------------+-----------------------------------+----------------+------+
91. | id                            | estRows | actRows | task      | access object        | execution info                                                          | operator info                     | memory         | disk |
92. +-------------------------------+---------+---------+-----------+----------------------+-------------------------------------------------------------------------+-----------------------------------+----------------+------+
93. | IndexLookUp_10                | 583.00  | 297     | root      |                      | time:5.31206ms, loops:2, rpc num: 1, rpc time:665.927µs, proc keys:297  |                                   | 109.1484375 KB | N/A  |
94. | ├─IndexRangeScan_8(Build)     | 583.00  | 297     | cop[tikv] | table:t1, index:b(b) | time:0s, loops:4                                                        | range:[123,123], keep order:false | N/A            | N/A  |
95. | └─TableRowIDScan_9(Probe)     | 583.00  | 297     | cop[tikv] | table:t1             | time:0s, loops:4                                                        | keep order:false                  | N/A            | N/A  |
96. +-------------------------------+---------+---------+-----------+----------------------+-------------------------------------------------------------------------+-----------------------------------+----------------+------+
97. 3 rows in set (0.01 sec)

99. SHOW SESSION BINDINGS\G
100. Empty set (0.00 sec)

下面的示例演示如何根据 sql_digest 删除绑定。

1. mysql> CREATE TABLE t(id INT PRIMARY KEY , a INT, KEY(a));
2. Query OK, 0 rows affected (0.06 sec)

4. mysql> SELECT /*+ IGNORE_INDEX(t, a) */ * FROM t WHERE a = 1;
5. Empty set (0.01 sec)

7. mysql> SELECT plan_digest FROM INFORMATION_SCHEMA.STATEMENTS_SUMMARY WHERE QUERY_SAMPLE_TEXT = 'SELECT /*+ IGNORE_INDEX(t, a) */ * FROM t WHERE a = 1';
8. +------------------------------------------------------------------+
9. | plan_digest                                                      |
10. +------------------------------------------------------------------+
11. | 4e3159169cc63c14b139a4e7d72eae1759875c9a9581f94bb2079aae961189cb |
12. +------------------------------------------------------------------+
13. 1 row in set (0.01 sec)

15. mysql> CREATE BINDING FROM HISTORY USING PLAN DIGEST '4e3159169cc63c14b139a4e7d72eae1759875c9a9581f94bb2079aae961189cb';
16. Query OK, 0 rows affected (0.02 sec)

18. mysql> SELECT * FROM t WHERE a = 1;
19. Empty set (0.01 sec)

21. mysql> SELECT @@LAST_PLAN_FROM_BINDING;
22. +--------------------------+
23. | @@LAST_PLAN_FROM_BINDING |
24. +--------------------------+
25. |                        1 |
26. +--------------------------+
27. 1 row in set (0.01 sec)

29. mysql> SHOW BINDINGS\G;
30. *************************** 1. row ***************************
31. Original_sql: select * from `test` . `t` where `a` = ?
32. Bind_sql: SELECT /*+ use_index(@`sel_1` `test`.`t` ) ignore_index(`t` `a`)*/ * FROM `test`.`t` WHERE `a` = 1
33. Default_db: test
34. Status: enabled
35. Create_time: 2022-12-14 15:26:22.277
36. Update_time: 2022-12-14 15:26:22.277
37. Charset: utf8mb4
38. Collation: utf8mb4_general_ci
39. Source: history
40. Sql_digest: 6909a1bbce5f64ade0a532d7058dd77b6ad5d5068aee22a531304280de48349f
41. Plan_digest: 4e3159169cc63c14b139a4e7d72eae1759875c9a9581f94bb2079aae961189cb
42. 1 row in set (0.02 sec)

44. ERROR:
45. No query specified

47. mysql> DROP BINDING FOR SQL DIGEST '6909a1bbce5f64ade0a532d7058dd77b6ad5d5068aee22a531304280de48349f';
48. Query OK, 0 rows affected (0.00 sec)

50. mysql> SHOW BINDINGS\G;
51. Empty set (0.01 sec)

53. ERROR:
54. No query specified

MySQL 兼容性

DROP [GLOBAL|SESSION] BINDING 语句是 TiDB 对 MySQL 语法的扩展。

DROP [GLOBAL|SESSION] BINDING

DROP [GLOBAL|SESSION] BINDING

语法图

示例

MySQL 兼容性

另请参阅