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 BINDING - 图1

GlobalScope

CREATE BINDING - 图2

BindableStmt

CREATE BINDING - 图3

  1. CreateBindingStmt ::=
  2. 'CREATE' GlobalScope 'BINDING' ( 'FOR' BindableStmt 'USING' BindableStmt
  3. | 'FROM' 'HISTORY' 'USING' 'PLAN' 'DIGEST' PlanDigest )
  4. GlobalScope ::=
  5. ( 'GLOBAL' | 'SESSION' )?
  6. BindableStmt ::=
  7. ( 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)
  8. INSERT INTO t1 SELECT NULL, FLOOR(RAND()*1000), RANDOM_BYTES(255) FROM dual;
  9. Query OK, 1 row affected (0.01 sec)
  10. Records: 1 Duplicates: 0 Warnings: 0
  11. INSERT INTO t1 SELECT NULL, FLOOR(RAND()*1000), RANDOM_BYTES(255) FROM t1 a JOIN t1 b JOIN t1 c LIMIT 100000;
  12. Query OK, 1 row affected (0.00 sec)
  13. Records: 1 Duplicates: 0 Warnings: 0
  14. INSERT INTO t1 SELECT NULL, FLOOR(RAND()*1000), RANDOM_BYTES(255) FROM t1 a JOIN t1 b JOIN t1 c LIMIT 100000;
  15. Query OK, 8 rows affected (0.00 sec)
  16. Records: 8 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, 1000 rows affected (0.04 sec)
  19. Records: 1000 Duplicates: 0 Warnings: 0
  20. INSERT INTO t1 SELECT NULL, FLOOR(RAND()*1000), RANDOM_BYTES(255) FROM t1 a JOIN t1 b JOIN t1 c LIMIT 100000;
  21. Query OK, 100000 rows affected (1.74 sec)
  22. Records: 100000 Duplicates: 0 Warnings: 0
  23. INSERT INTO t1 SELECT NULL, FLOOR(RAND()*1000), RANDOM_BYTES(255) FROM t1 a JOIN t1 b JOIN t1 c LIMIT 100000;
  24. Query OK, 100000 rows affected (2.15 sec)
  25. Records: 100000 Duplicates: 0 Warnings: 0
  26. INSERT INTO t1 SELECT NULL, FLOOR(RAND()*1000), RANDOM_BYTES(255) FROM t1 a JOIN t1 b JOIN t1 c LIMIT 100000;
  27. Query OK, 100000 rows affected (2.64 sec)
  28. Records: 100000 Duplicates: 0 Warnings: 0
  29. SELECT SLEEP(1);
  30. +----------+
  31. | SLEEP(1) |
  32. +----------+
  33. | 0 |
  34. +----------+
  35. 1 row in set (1.00 sec)
  36. ANALYZE TABLE t1;
  37. Query OK, 0 rows affected (1.33 sec)
  38. EXPLAIN ANALYZE SELECT * FROM t1 WHERE b = 123;
  39. +-------------------------------+---------+---------+-----------+----------------------+---------------------------------------------------------------------------+-----------------------------------+----------------+------+
  40. | id | estRows | actRows | task | access object | execution info | operator info | memory | disk |
  41. +-------------------------------+---------+---------+-----------+----------------------+---------------------------------------------------------------------------+-----------------------------------+----------------+------+
  42. | 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 |
  43. | ├─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 |
  44. | └─TableRowIDScan_9(Probe) | 583.00 | 297 | cop[tikv] | table:t1 | time:12ms, loops:4 | keep order:false | N/A | N/A |
  45. +-------------------------------+---------+---------+-----------+----------------------+---------------------------------------------------------------------------+-----------------------------------+----------------+------+
  46. 3 rows in set (0.02 sec)
  47. CREATE SESSION BINDING FOR
  48. SELECT * FROM t1 WHERE b = 123
  49. USING
  50. SELECT * FROM t1 IGNORE INDEX (b) WHERE b = 123;
  51. Query OK, 0 rows affected (0.00 sec)
  52. EXPLAIN ANALYZE SELECT * FROM t1 WHERE b = 123;
  53. +-------------------------+-----------+---------+-----------+---------------+--------------------------------------------------------------------------------+--------------------+---------------+------+
  54. | id | estRows | actRows | task | access object | execution info | operator info | memory | disk |
  55. +-------------------------+-----------+---------+-----------+---------------+--------------------------------------------------------------------------------+--------------------+---------------+------+
  56. | 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 |
  57. | └─Selection_6 | 583.00 | 297 | cop[tikv] | | time:224ms, loops:298 | eq(test.t1.b, 123) | N/A | N/A |
  58. | └─TableFullScan_5 | 301010.00 | 301010 | cop[tikv] | table:t1 | time:220ms, loops:298 | keep order:false | N/A | N/A |
  59. +-------------------------+-----------+---------+-----------+---------------+--------------------------------------------------------------------------------+--------------------+---------------+------+
  60. 3 rows in set (0.22 sec)
  61. SHOW SESSION BINDINGS\G
  62. *************************** 1. row ***************************
  63. Original_sql: select * from t1 where b = ?
  64. Bind_sql: SELECT * FROM t1 IGNORE INDEX (b) WHERE b = 123
  65. Default_db: test
  66. Status: using
  67. Create_time: 2020-05-22 14:38:03.456
  68. Update_time: 2020-05-22 14:38:03.456
  69. Charset: utf8mb4
  70. Collation: utf8mb4_0900_ai_ci
  71. 1 row in set (0.00 sec)
  72. DROP SESSION BINDING FOR SELECT * FROM t1 WHERE b = 123;
  73. Query OK, 0 rows affected (0.00 sec)
  74. EXPLAIN ANALYZE SELECT * FROM t1 WHERE b = 123;
  75. +-------------------------------+---------+---------+-----------+----------------------+-------------------------------------------------------------------------+-----------------------------------+----------------+------+
  76. | id | estRows | actRows | task | access object | execution info | operator info | memory | disk |
  77. +-------------------------------+---------+---------+-----------+----------------------+-------------------------------------------------------------------------+-----------------------------------+----------------+------+
  78. | 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 |
  79. | ├─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 |
  80. | └─TableRowIDScan_9(Probe) | 583.00 | 297 | cop[tikv] | table:t1 | time:0s, loops:4 | keep order:false | N/A | N/A |
  81. +-------------------------------+---------+---------+-----------+----------------------+-------------------------------------------------------------------------+-----------------------------------+----------------+------+
  82. 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)
  3. mysql> SELECT /*+ IGNORE_INDEX(t, a) */ * FROM t WHERE a = 1;
  4. Empty set (0.01 sec)
  5. mysql> SELECT plan_digest FROM INFORMATION_SCHEMA.STATEMENTS_SUMMARY WHERE QUERY_SAMPLE_TEXT = 'SELECT /*+ IGNORE_INDEX(t, a) */ * FROM t WHERE a = 1';
  6. +------------------------------------------------------------------+
  7. | plan_digest |
  8. +------------------------------------------------------------------+
  9. | 4e3159169cc63c14b139a4e7d72eae1759875c9a9581f94bb2079aae961189cb |
  10. +------------------------------------------------------------------+
  11. 1 row in set (0.01 sec)
  12. mysql> CREATE BINDING FROM HISTORY USING PLAN DIGEST '4e3159169cc63c14b139a4e7d72eae1759875c9a9581f94bb2079aae961189cb';
  13. Query OK, 0 rows affected (0.02 sec)
  14. mysql> SELECT * FROM t WHERE a = 1;
  15. Empty set (0.01 sec)
  16. mysql> SELECT @@LAST_PLAN_FROM_BINDING;
  17. +--------------------------+
  18. | @@LAST_PLAN_FROM_BINDING |
  19. +--------------------------+
  20. | 1 |
  21. +--------------------------+
  22. 1 row in set (0.01 sec)

MySQL 兼容性

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

另请参阅