[グローバル|セッション]バインディングを作成

このステートメントは、TiDB に新しい実行計画バインディングを作成します。バインドを使用すると、基になるクエリを変更することなく、ステートメントにヒントを挿入できます。

BINDINGGLOBALまたはSESSIONベースのいずれかになります。デフォルトはSESSIONです。

バインドされた SQL ステートメントはパラメーター化され、システム テーブルに格納されます。 SQL クエリが処理されるとき、パラメーター化された SQL ステートメントとシステム テーブル内のバインドされた SQL ステートメントが一貫しており、システム変数tidb_use_plan_baselinesON (既定) に設定されている限り、対応するオプティマイザー ヒントを使用できます。複数の実行計画が利用可能な場合、オプティマイザは最小コストで計画をバインドすることを選択します。

あらすじ

CreateBindingStmt
CREATEGlobalScopeBINDINGFORBindableStmtUSINGBindableStmt
GlobalScope
GLOBALSESSION
BindableStmt
SelectStmtUpdateStmtInsertIntoStmtReplaceIntoStmtDeleteStmt

mysql> CREATE TABLE t1 ( -> id INT NOT NULL PRIMARY KEY auto_increment, -> b INT NOT NULL, -> pad VARBINARY(255), -> INDEX(b) -> ); Query OK, 0 rows affected (0.07 sec) mysql> INSERT INTO t1 SELECT NULL, FLOOR(RAND()*1000), RANDOM_BYTES(255) FROM dual; Query OK, 1 row affected (0.01 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> INSERT INTO t1 SELECT NULL, FLOOR(RAND()*1000), RANDOM_BYTES(255) FROM t1 a JOIN t1 b JOIN t1 c LIMIT 100000; Query OK, 1 row affected (0.00 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> INSERT INTO t1 SELECT NULL, FLOOR(RAND()*1000), RANDOM_BYTES(255) FROM t1 a JOIN t1 b JOIN t1 c LIMIT 100000; Query OK, 8 rows affected (0.00 sec) Records: 8 Duplicates: 0 Warnings: 0 mysql> INSERT INTO t1 SELECT NULL, FLOOR(RAND()*1000), RANDOM_BYTES(255) FROM t1 a JOIN t1 b JOIN t1 c LIMIT 100000; Query OK, 1000 rows affected (0.04 sec) Records: 1000 Duplicates: 0 Warnings: 0 mysql> INSERT INTO t1 SELECT NULL, FLOOR(RAND()*1000), RANDOM_BYTES(255) FROM t1 a JOIN t1 b JOIN t1 c LIMIT 100000; Query OK, 100000 rows affected (1.74 sec) Records: 100000 Duplicates: 0 Warnings: 0 mysql> INSERT INTO t1 SELECT NULL, FLOOR(RAND()*1000), RANDOM_BYTES(255) FROM t1 a JOIN t1 b JOIN t1 c LIMIT 100000; Query OK, 100000 rows affected (2.15 sec) Records: 100000 Duplicates: 0 Warnings: 0 mysql> INSERT INTO t1 SELECT NULL, FLOOR(RAND()*1000), RANDOM_BYTES(255) FROM t1 a JOIN t1 b JOIN t1 c LIMIT 100000; Query OK, 100000 rows affected (2.64 sec) Records: 100000 Duplicates: 0 Warnings: 0 mysql> SELECT SLEEP(1); +----------+ | SLEEP(1) | +----------+ | 0 | +----------+ 1 row in set (1.00 sec) mysql> ANALYZE TABLE t1; Query OK, 0 rows affected (1.33 sec) mysql> EXPLAIN ANALYZE SELECT * FROM t1 WHERE b = 123; +-------------------------------+---------+---------+-----------+----------------------+---------------------------------------------------------------------------+-----------------------------------+----------------+------+ | id | estRows | actRows | task | access object | execution info | operator info | memory | disk | +-------------------------------+---------+---------+-----------+----------------------+---------------------------------------------------------------------------+-----------------------------------+----------------+------+ | 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 | | ├─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 | | └─TableRowIDScan_9(Probe) | 583.00 | 297 | cop[tikv] | table:t1 | time:12ms, loops:4 | keep order:false | N/A | N/A | +-------------------------------+---------+---------+-----------+----------------------+---------------------------------------------------------------------------+-----------------------------------+----------------+------+ 3 rows in set (0.02 sec) mysql> CREATE SESSION BINDING FOR -> SELECT * FROM t1 WHERE b = 123 -> USING -> SELECT * FROM t1 IGNORE INDEX (b) WHERE b = 123; Query OK, 0 rows affected (0.00 sec) mysql> EXPLAIN ANALYZE SELECT * FROM t1 WHERE b = 123; +-------------------------+-----------+---------+-----------+---------------+--------------------------------------------------------------------------------+--------------------+---------------+------+ | id | estRows | actRows | task | access object | execution info | operator info | memory | disk | +-------------------------+-----------+---------+-----------+---------------+--------------------------------------------------------------------------------+--------------------+---------------+------+ | 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 | | └─Selection_6 | 583.00 | 297 | cop[tikv] | | time:224ms, loops:298 | eq(test.t1.b, 123) | N/A | N/A | | └─TableFullScan_5 | 301010.00 | 301010 | cop[tikv] | table:t1 | time:220ms, loops:298 | keep order:false | N/A | N/A | +-------------------------+-----------+---------+-----------+---------------+--------------------------------------------------------------------------------+--------------------+---------------+------+ 3 rows in set (0.22 sec) mysql> SHOW SESSION BINDINGS\G *************************** 1. row *************************** Original_sql: select * from t1 where b = ? Bind_sql: SELECT * FROM t1 IGNORE INDEX (b) WHERE b = 123 Default_db: test Status: using Create_time: 2020-05-22 14:38:03.456 Update_time: 2020-05-22 14:38:03.456 Charset: utf8mb4 Collation: utf8mb4_0900_ai_ci 1 row in set (0.00 sec) mysql> DROP SESSION BINDING FOR SELECT * FROM t1 WHERE b = 123; Query OK, 0 rows affected (0.00 sec) mysql> EXPLAIN ANALYZE SELECT * FROM t1 WHERE b = 123; +-------------------------------+---------+---------+-----------+----------------------+-------------------------------------------------------------------------+-----------------------------------+----------------+------+ | id | estRows | actRows | task | access object | execution info | operator info | memory | disk | +-------------------------------+---------+---------+-----------+----------------------+-------------------------------------------------------------------------+-----------------------------------+----------------+------+ | 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 | | ├─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 | | └─TableRowIDScan_9(Probe) | 583.00 | 297 | cop[tikv] | table:t1 | time:0s, loops:4 | keep order:false | N/A | N/A | +-------------------------------+---------+---------+-----------+----------------------+-------------------------------------------------------------------------+-----------------------------------+----------------+------+ 3 rows in set (0.01 sec)

MySQL の互換性

このステートメントは、MySQL 構文に対する TiDB 拡張です。

こちらもご覧ください

Playground
登録なしで TiDB の機能をワンストップでインタラクティブに体験できます。
製品
TiDB Dedicated
TiDB Serverless
TiDB
価格
PoC お問い合わせ
エコシステム
TiKV
TiFlash
OSS Insight
© 2024 PingCAP. All Rights Reserved.
Privacy Policy.