ドロップ [グローバル|セッション] バインディング

このステートメントは、特定の SQL ステートメントからバインディングを削除します。バインディングを使用して、基になるクエリを変更することなく、ステートメントにヒントを挿入できます。

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

あらすじ

DropBindingStmt
DROPGlobalScopeBINDINGFORBindableStmtUSINGBindableStmt
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> SHOW SESSION BINDINGS\G Empty set (0.00 sec)

MySQL の互換性

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

こちらもご覧ください

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