CREATE [GLOBAL|SESSION] BINDING

CREATE [GLOBAL|SESSION] BINDING 语句用于在 TiDB 中创建新的执行计划绑定。绑定可用于将优化器 Hint 插入语句中，而无需更改底层查询。

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

被绑定的 SQL 语句会被参数化后存储到系统表中。在处理 SQL 查询时，只要参数化后的 SQL 语句和系统表中某个被绑定的 SQL 语句一致，并且系统变量 tidb_use_plan_baselines 的值为 ON（其默认值为 ON），即可使用相应的优化器 Hint。如果存在多个可匹配的执行计划，优化器会从中选择代价最小的一个进行绑定。更多信息，请参考创建绑定。

语法图

CreateBindingStmt

CREATE [GLOBAL|SESSION] BINDING - 图1

GlobalScope

CREATE [GLOBAL|SESSION] BINDING - 图2

BindableStmt

CREATE [GLOBAL|SESSION] BINDING - 图3

  1. CreateBindingStmt ::=
  2.     'CREATE' GlobalScope 'BINDING' ( 'FOR' BindableStmt 'USING' BindableStmt ) 
  3. |   ( 'FROM' 'HISTORY' 'USING' 'PLAN' 'DIGEST' PlanDigest )
  5. GlobalScope ::=
  6.     ( 'GLOBAL' | 'SESSION' )?
  8. BindableStmt ::=
  9.     ( SelectStmt | UpdateStmt | InsertIntoStmt | ReplaceIntoStmt | DeleteStmt )

示例

你可以根据 SQL 或历史执行计划创建绑定。

下面的示例演示如何根据 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)

下面的示例演示如何根据历史执行计划创建绑定。

  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)

MySQL 兼容性

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

另请参阅