CREATE [GLOBAL|SESSION] BINDING

    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)
    32. INSERT INTO t1 SELECT NULL, FLOOR(RAND()*1000), RANDOM_BYTES(255) FROM t1 a JOIN t1 b JOIN t1 c LIMIT 100000;
    33. Query OK, 100000 rows affected (2.64 sec)
    34. Records: 100000  Duplicates: 0  Warnings: 0
    36. SELECT SLEEP(1);
    37. +----------+
    38. | SLEEP(1) |
    39. +----------+
    40. |        0 |
    41. +----------+
    42. 1 row in set (1.00 sec)
    44. ANALYZE TABLE t1;
    45. Query OK, 0 rows affected (1.33 sec)
    47. EXPLAIN ANALYZE SELECT * FROM t1 WHERE b = 123;
    48. +-------------------------------+---------+---------+-----------+----------------------+---------------------------------------------------------------------------+-----------------------------------+----------------+------+
    49. | id                            | estRows | actRows | task      | access object        | execution info                                                            | operator info                     | memory         | disk |
    50. +-------------------------------+---------+---------+-----------+----------------------+---------------------------------------------------------------------------+-----------------------------------+----------------+------+
    51. | 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  |
    52. | ├─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  |
    53. | └─TableRowIDScan_9(Probe)     | 583.00  | 297     | cop[tikv] | table:t1             | time:12ms, loops:4                                                        | keep order:false                  | N/A            | N/A  |
    54. +-------------------------------+---------+---------+-----------+----------------------+---------------------------------------------------------------------------+-----------------------------------+----------------+------+
    55. 3 rows in set (0.02 sec)
    57. CREATE SESSION BINDING FOR
    58.     ->  SELECT * FROM t1 WHERE b = 123
    59.     -> USING
    60.     ->  SELECT * FROM t1 IGNORE INDEX (b) WHERE b = 123;
    61. Query OK, 0 rows affected (0.00 sec)
    63. EXPLAIN ANALYZE  SELECT * FROM t1 WHERE b = 123;
    64. +-------------------------+-----------+---------+-----------+---------------+--------------------------------------------------------------------------------+--------------------+---------------+------+
    65. +-------------------------+-----------+---------+-----------+---------------+--------------------------------------------------------------------------------+--------------------+---------------+------+
    66. | 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  |
    67. | └─Selection_6           | 583.00    | 297     | cop[tikv] |               | time:224ms, loops:298                                                          | eq(test.t1.b, 123) | N/A           | N/A  |
    68. |   └─TableFullScan_5     | 301010.00 | 301010  | cop[tikv] | table:t1      | time:220ms, loops:298                                                          | keep order:false   | N/A           | N/A  |
    69. +-------------------------+-----------+---------+-----------+---------------+--------------------------------------------------------------------------------+--------------------+---------------+------+
    70. 3 rows in set (0.22 sec)
    72. SHOW SESSION BINDINGS\G
    73. *************************** 1. row ***************************
    74. Original_sql: select * from t1 where b = ?
    75.     Bind_sql: SELECT * FROM t1 IGNORE INDEX (b) WHERE b = 123
    76.   Default_db: test
    77.       Status: using
    78.  Create_time: 2020-05-22 14:38:03.456
    79.  Update_time: 2020-05-22 14:38:03.456
    80.      Charset: utf8mb4
    81.    Collation: utf8mb4_0900_ai_ci
    82. 1 row in set (0.00 sec)
    84. DROP SESSION BINDING FOR SELECT * FROM t1 WHERE b = 123;
    85. Query OK, 0 rows affected (0.00 sec)
    87. EXPLAIN ANALYZE  SELECT * FROM t1 WHERE b = 123;
    88. +-------------------------------+---------+---------+-----------+----------------------+-------------------------------------------------------------------------+-----------------------------------+----------------+------+
    89. | id                            | estRows | actRows | task      | access object        | execution info                                                          | operator info                     | memory         | disk |
    90. +-------------------------------+---------+---------+-----------+----------------------+-------------------------------------------------------------------------+-----------------------------------+----------------+------+
    91. | 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  |
    92. | ├─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  |
    93. | └─TableRowIDScan_9(Probe)     | 583.00  | 297     | cop[tikv] | table:t1             | time:0s, loops:4                                                        | keep order:false                  | N/A            | N/A  |
    94. +-------------------------------+---------+---------+-----------+----------------------+-------------------------------------------------------------------------+-----------------------------------+----------------+------+
    95. 3 rows in set (0.01 sec)