SHOW [GLOBAL|SESSION] BINDINGS

    语句用于显示创建过的 SQL 绑定的相关信息。BINDING 语句可以在 GLOBAL 或者 SESSION 作用域内显示执行计划绑定。在不指定作用域时,默认的作用域为 SESSION

    ShowStmt:

    ShowTargetFilterable

    GlobalScope:

    ShowLikeOrWhereOpt

    该语句会输出 GLOBAL 或者 SESSION 作用域内的执行计划绑定,在不指定作用域时默认作用域为 SESSION。目前 SHOW BINDINGS 会输出 8 列,具体如下:

    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. Records: 100000 Duplicates: 0 Warnings: 0
    22. INSERT INTO t1 SELECT NULL, FLOOR(RAND()*1000), RANDOM_BYTES(255) FROM t1 a JOIN t1 b JOIN t1 c LIMIT 100000;
    23. Query OK, 100000 rows affected (2.15 sec)
    24. Records: 100000 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 (2.64 sec)
    27. Records: 100000 Duplicates: 0 Warnings: 0
    28. SELECT SLEEP(1);
    29. +----------+
    30. | SLEEP(1) |
    31. +----------+
    32. | 0 |
    33. +----------+
    34. 1 row in set (1.00 sec)
    35. ANALYZE TABLE t1;
    36. Query OK, 0 rows affected (1.33 sec)
    37. EXPLAIN ANALYZE SELECT * FROM t1 WHERE b = 123;
    38. +-------------------------------+---------+---------+-----------+----------------------+---------------------------------------------------------------------------+-----------------------------------+----------------+------+
    39. | id | estRows | actRows | task | access object | execution info | operator info | memory | disk |
    40. +-------------------------------+---------+---------+-----------+----------------------+---------------------------------------------------------------------------+-----------------------------------+----------------+------+
    41. | 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 |
    42. | ├─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 |
    43. +-------------------------------+---------+---------+-----------+----------------------+---------------------------------------------------------------------------+-----------------------------------+----------------+------+
    44. 3 rows in set (0.02 sec)
    45. CREATE SESSION BINDING FOR
    46. -> SELECT * FROM t1 WHERE b = 123
    47. -> USING
    48. -> SELECT * FROM t1 IGNORE INDEX (b) WHERE b = 123;
    49. Query OK, 0 rows affected (0.00 sec)
    50. EXPLAIN ANALYZE SELECT * FROM t1 WHERE b = 123;
    51. +-------------------------+-----------+---------+-----------+---------------+--------------------------------------------------------------------------------+--------------------+---------------+------+
    52. | id | estRows | actRows | task | access object | execution info | operator info | memory | disk |
    53. +-------------------------+-----------+---------+-----------+---------------+--------------------------------------------------------------------------------+--------------------+---------------+------+
    54. | 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 |
    55. | └─Selection_6 | 583.00 | 297 | cop[tikv] | | time:224ms, loops:298 | eq(test.t1.b, 123) | N/A | N/A |
    56. | └─TableFullScan_5 | 301010.00 | 301010 | cop[tikv] | table:t1 | time:220ms, loops:298 | keep order:false | N/A | N/A |
    57. +-------------------------+-----------+---------+-----------+---------------+--------------------------------------------------------------------------------+--------------------+---------------+------+
    58. 3 rows in set (0.22 sec)
    59. SHOW SESSION BINDINGS\G
    60. *************************** 1. row ***************************
    61. Original_sql: select * from t1 where b = ?
    62. Bind_sql: SELECT * FROM t1 IGNORE INDEX (b) WHERE b = 123
    63. Default_db: test
    64. Status: using
    65. Create_time: 2020-05-22 14:38:03.456
    66. Update_time: 2020-05-22 14:38:03.456
    67. Charset: utf8mb4
    68. Collation: utf8mb4_0900_ai_ci

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