数据库中SQL语句的执行计划可能会随着时间的推移而发生变化，数据量的增长、统计信息的更新等都可能会导致相同SQL语句在不同的时间节点其执行计划不一致，从而引发性能问题。在一个稳定运行的生产环境中，大部分情况下我们都希望同一个SQL语句的执行计划保持固定不变，数据库也提供了一些手段帮助用户来稳定执行计划。
比较通用的固化执行计划的方法就是使用HINT，HINT最早是Oracle数据库中很有特色的一个功能，也是很多DBA优化中经常采用的一个手段。数据库的优化器是一个非常复杂的模块，即使是Oracle这样的数据库在一些场景下也难以保证执行计划是最优的，HINT提供了一种机制可以告诉优化器按照想要的方式生成执行计划，当然HINT也可以被用来固化语句的执行计划。HINT的典型用法就是select *+ (具体的Hint内容） */…，它通过一种特殊的注释方式来影响执行计划。
业内大多数数据库基本都有HINT的功能，只不过不同数据库可能有自己的HINT写法。笔者之前使用的一款数据库在表名后面增加<<+index
index_name>>来让执行计划走索引，后来为了兼容Oracle，也支持了/*+ … */这样的写法。
TiDB也支持HINT功能，语法上兼容Oracle HINT，可参考官方文档 Optimizer
Hints | PingCAP 文档中心。在生产系统中，HINT可以被用来解决即时SQL查询的性能问题；在应用系统开发阶段，为了保证SQL执行计划的后续稳定性，开发人员也会选择直接将HINT添加到应用代码中。然而如果系统上线后有些未指定HINT的语句因为某种原因发生了执行计划变化而导致性能突然下降，除了从应用代码添加HINT重新打版以外，还有什么更好的方法呢？TiDB的执行计划绑定功能就是专门解决生产环境执行计划变化而设计的功能，以下具体介绍相关内容。
一．怎么使用执行计划绑定？
1 创建SQL绑定(SQL Binding)
假如一条SQL语句最优的执行计划是索引扫描，但当前却错误的选择了全表扫描。在不修改SQL语句的情况下，我们可以通过创建执行计划绑定的方式来修改它的执行计划。创建绑定有两种方式，一种是根据SQL语句创建绑定，另一种是根据历史执行计划创建绑定。
根据SQL创建绑定
明确需要绑定的SQL语句，也明确应该使用什么HINT来指定执行计划时，可以使用这种方式。比如下面的语句默认采用全表扫描，而使用索引扫描效率更高，就可以直接对语句创建绑定。
mysql> explain analyze SELECT * from test1 where b like 'user-1%';+-------------------------+-----------+---------+-----------+---------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------+----------+------+| id | estRows | actRows | task | access object | execution info | operator info | memory | disk |+-------------------------+-----------+---------+-----------+---------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------+----------+------+| TableReader_7 | 11315.28 | 11112 | root | | time:236.6ms, loops:14, RU:168.359374, cop_task: {num: 6, max: 102.6ms, min: 20ms, avg: 39.3ms, p95: 102.6ms, max_proc_keys: 82752, p95_proc_keys: 82752, tot_proc: 227.5ms, tot_wait: 323.5µs, rpc_num: 6, rpc_time: 235.9ms, copr_cache_hit_ratio: 0.00, build_task_duration: 13.6µs, max_distsql_concurrency: 1} | data:Selection_6 | 249.9 KB | N/A || └─Selection_6 | 11315.28 | 11112 | cop[tikv] | | tikv_task:{proc max:100ms, min:19ms, avg: 37.5ms, p80:34ms, p95:100ms, iters:122, tasks:6}, scan_detail: {total_process_keys: 100000, total_process_keys_size: 5877790, total_keys: 100006, get_snapshot_time: 116.9µs, rocksdb: {delete_skipped_count: 543136, key_skipped_count: 643136, block: {cache_hit_count: 1582}}} | like(test.test1.b, "user-1%", 92) | N/A | N/A || └─TableFullScan_5 | 100000.00 | 100000 | cop[tikv] | table:test1 | tikv_task:{proc max:76ms, min:18ms, avg: 30ms, p80:27ms, p95:76ms, iters:122, tasks:6} | keep order:false | N/A | N/A |+-------------------------+-----------+---------+-----------+---------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------+----------+------+3 rows in set (0.24 sec)mysql> explain analyze SELECT /*+ use_index(test1, idx1) */ * from test1 where b like 'user-1%';+-------------------------------+----------+---------+-----------+----------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------+----------+------+| id | estRows | actRows | task | access object | execution info | operator info | memory | disk |+-------------------------------+----------+---------+-----------+----------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------+----------+------+| IndexLookUp_7 | 11315.28 | 11112 | root | | time:26.9ms, loops:12, RU:36.470042, index_task: {total_time: 16.9ms, fetch_handle: 16.9ms, build: 3.56µs, wait: 15.3µs}, table_task: {total_time: 24.3ms, num: 4, concurrency: 5}, next: {wait_index: 7.4ms, wait_table_lookup_build: 1.93ms, wait_table_lookup_resp: 16.7ms} | | 752.7 KB | N/A || ├─IndexRangeScan_5(Build) | 11315.28 | 11112 | cop[tikv] | table:test1, index:idx1(b) | time:15.9ms, loops:14, cop_task: {num: 7, max: 5.39ms, min: 1.2ms, avg: 2.56ms, p95: 5.39ms, max_proc_keys: 4360, p95_proc_keys: 4360, tot_proc: 11.5ms, tot_wait: 1.65ms, rpc_num: 7, rpc_time: 17.7ms, copr_cache_hit_ratio: 0.00, build_task_duration: 49.2µs, max_distsql_concurrency: 2}, tikv_task:{proc max:4ms, min:1ms, avg: 1.86ms, p80:3ms, p95:4ms, iters:35, tasks:7}, scan_detail: {total_process_keys: 11112, total_process_keys_size: 820956, total_keys: 11119, get_snapshot_time: 1.42ms, rocksdb: {key_skipped_count: 11112, block: {cache_hit_count: 26, read_count: 27, read_byte: 56.1 KB, read_time: 166µs}}} | range:["user-1","user-2"), keep order:false | N/A | N/A || └─TableRowIDScan_6(Probe) | 11315.28 | 11112 | cop[tikv] | table:test1 | time:20.6ms, loops:16, cop_task: {num: 4, max: 7.81ms, min: 2.69ms, avg: 4.95ms, p95: 7.81ms, max_proc_keys: 5056, p95_proc_keys: 5056, tot_proc: 14.7ms, tot_wait: 234.9µs, rpc_num: 4, rpc_time: 19.7ms, copr_cache_hit_ratio: 0.00, build_task_duration: 87.4µs, max_distsql_concurrency: 1}, tikv_task:{proc max:6ms, min:1ms, avg: 3.5ms, p80:6ms, p95:6ms, iters:29, tasks:4}, scan_detail: {total_process_keys: 11112, total_process_keys_size: 653142, total_keys: 11127, get_snapshot_time: 52.3µs, rocksdb: {delete_skipped_count: 11109, key_skipped_count: 22218, block: {cache_hit_count: 143}}} | keep order:false | N/A | N/A |+-------------------------------+----------+---------+-----------+----------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------+----------+------+3 rows in set (0.03 sec)mysql> create global binding for SELECT * from test1 where b like ? using SELECT /*+ use_index(test1, idx1) */ * from test1 where b like ?;Query OK, 0 rows affected (0.01 sec)复制
上述语句创建一个global级别的绑定，我们登录另外一个会话时便可以使用show global bindings查看到对应的绑定，同时查看原始语句的执行计划发现已经更改为索引扫描，证明执行计划绑定已生效。
mysql> show global bindings;+-------------------------------------------------+-------------------------------------------------------------------------------+------------+---------+-------------------------+-------------------------+---------+--------------------+--------+------------------------------------------------------------------+-------------+| Original_sql | Bind_sql | Default_db | Status | Create_time | Update_time | Charset | Collation | Source | Sql_digest | Plan_digest |+-------------------------------------------------+-------------------------------------------------------------------------------+------------+---------+-------------------------+-------------------------+---------+--------------------+--------+------------------------------------------------------------------+-------------+| select * from `test` . `test1` where `b` like ? | SELECT /*+ use_index(`test1` `idx1`)*/ * FROM `test`.`test1` WHERE `b` LIKE ? | test | enabled | 2024-03-08 18:01:47.938 | 2024-03-08 18:01:47.938 | utf8mb4 | utf8mb4_0900_ai_ci | manual | eb338909e651eac55ccb5cb60dd0f3d6962e69257ecec55381013a9517244a42 | |+-------------------------------------------------+-------------------------------------------------------------------------------+------------+---------+-------------------------+-------------------------+---------+--------------------+--------+------------------------------------------------------------------+-------------+1 row in set (0.00 sec)mysql> explain SELECT * from test1 where b like 'user-1%';+-------------------------------+----------+-----------+----------------------------+---------------------------------------------+| id | estRows | task | access object | operator info |+-------------------------------+----------+-----------+----------------------------+---------------------------------------------+| IndexLookUp_7 | 11315.28 | root | | || ├─IndexRangeScan_5(Build) | 11315.28 | cop[tikv] | table:test1, index:idx1(b) | range:["user-1","user-2"), keep order:false || └─TableRowIDScan_6(Probe) | 11315.28 | cop[tikv] | table:test1 | keep order:false |+-------------------------------+----------+-----------+----------------------------+---------------------------------------------+3 rows in set (0.00 sec)复制
根据历史执行计划创建绑定
TiDB中有一个参数tidb_enable_stmt_summary，参数打开表示将SQL耗时等执行信息记录到系统表information_schema.STATEMENTS_SUMMARY中用于定位和排查性能问题。statement_summary用于保存SQL监控指标聚合后的结果，默认每半小时清空一次。
mysql> select * from information_schema.statements_summary where QUERY_SAMPLE_TEXT like '%SELECT *+ use_index(test1, idx1) */ * from test1%' \G*************************** 1. row ***************************SUMMARY_BEGIN_TIME: 2024-03-11 11:00:00SUMMARY_END_TIME: 2024-03-11 11:30:00STMT_TYPE: SelectSCHEMA_NAME: testDIGEST: 6c3daca0b3e0fdb874a7fc28883d1a5d55cbf68a38289ea99215bbd6d64d157eDIGEST_TEXT: select * from `test1` where `b` like ?TABLE_NAMES: test.test1INDEX_NAMES: test1:idx1SAMPLE_USER: rootEXEC_COUNT: 1SUM_ERRORS: 0SUM_WARNINGS: 0SUM_LATENCY: 21295823MAX_LATENCY: 21295823MIN_LATENCY: 21295823AVG_LATENCY: 21295823AVG_PARSE_LATENCY: 253050MAX_PARSE_LATENCY: 253050AVG_COMPILE_LATENCY: 577573MAX_COMPILE_LATENCY: 577573SUM_COP_TASK_NUM: 10MAX_COP_PROCESS_TIME: 3395404MAX_COP_PROCESS_ADDRESS: 172.20.12.52:20161MAX_COP_WAIT_TIME: 87124MAX_COP_WAIT_ADDRESS: 172.20.12.52:20161AVG_PROCESS_TIME: 17488281MAX_PROCESS_TIME: 17488281AVG_WAIT_TIME: 549676MAX_WAIT_TIME: 549676AVG_BACKOFF_TIME: 0MAX_BACKOFF_TIME: 0AVG_TOTAL_KEYS: 17185MAX_TOTAL_KEYS: 17185AVG_PROCESSED_KEYS: 17168MAX_PROCESSED_KEYS: 17168AVG_ROCKSDB_DELETE_SKIPPED_COUNT: 2.9906e-320MAX_ROCKSDB_DELETE_SKIPPED_COUNT: 6053AVG_ROCKSDB_KEY_SKIPPED_COUNT: 1.1471e-319MAX_ROCKSDB_KEY_SKIPPED_COUNT: 23218AVG_ROCKSDB_BLOCK_CACHE_HIT_COUNT: 7.07e-322MAX_ROCKSDB_BLOCK_CACHE_HIT_COUNT: 143AVG_ROCKSDB_BLOCK_READ_COUNT: 0MAX_ROCKSDB_BLOCK_READ_COUNT: 0AVG_ROCKSDB_BLOCK_READ_BYTE: 0MAX_ROCKSDB_BLOCK_READ_BYTE: 0AVG_PREWRITE_TIME: 0MAX_PREWRITE_TIME: 0AVG_COMMIT_TIME: 0MAX_COMMIT_TIME: 0AVG_GET_COMMIT_TS_TIME: 0MAX_GET_COMMIT_TS_TIME: 0AVG_COMMIT_BACKOFF_TIME: 0MAX_COMMIT_BACKOFF_TIME: 0AVG_RESOLVE_LOCK_TIME: 0MAX_RESOLVE_LOCK_TIME: 0AVG_LOCAL_LATCH_WAIT_TIME: 0MAX_LOCAL_LATCH_WAIT_TIME: 0AVG_WRITE_KEYS: 0MAX_WRITE_KEYS: 0AVG_WRITE_SIZE: 0MAX_WRITE_SIZE: 0AVG_PREWRITE_REGIONS: 0MAX_PREWRITE_REGIONS: 0AVG_TXN_RETRY: 0MAX_TXN_RETRY: 0SUM_EXEC_RETRY: 0SUM_EXEC_RETRY_TIME: 0SUM_BACKOFF_TIMES: 0BACKOFF_TYPES: NULLAVG_MEM: 620848MAX_MEM: 620848AVG_DISK: 0MAX_DISK: 0AVG_KV_TIME: 0AVG_PD_TIME: 0AVG_BACKOFF_TOTAL_TIME: 0AVG_WRITE_SQL_RESP_TIME: 0MAX_RESULT_ROWS: 11112MIN_RESULT_ROWS: 11112AVG_RESULT_ROWS: 11112PREPARED: 0AVG_AFFECTED_ROWS: 0FIRST_SEEN: 2024-03-11 11:07:29LAST_SEEN: 2024-03-11 11:07:29PLAN_IN_CACHE: 0PLAN_CACHE_HITS: 0PLAN_IN_BINDING: 0QUERY_SAMPLE_TEXT: SELECT *+ use_index(test1, idx1) */ * from test1 where b like 'user-1%'PREV_SAMPLE_TEXT:PLAN_DIGEST: 41e285a19ae862234757fd639e67b908bb2043638f5e74f83e08247080412f6bPLAN: id task estRows operator info actRows execution info memory diskIndexLookUp_7 root 11315.28 11112 time:18.1ms, loops:12, index_task: {total_time: 14.2ms, fetch_handle: 14.1ms, build: 3.7µs, wait: 14.7µs}, table_task: {total_time: 16.7ms, num: 4, concurrency: 5}, next: {wait_index: 5.05ms, wait_table_lookup_build: 1.54ms, wait_table_lookup_resp: 10.7ms} 606.3 KB N/A├─IndexRangeScan_5(Build) cop[tikv] 11315.28 table:test1, index:idx1(b), range:["user-1","user-2"), keep order:false 11112 time:13.2ms, loops:14, cop_task: {num: 6, max: 4.22ms, min: 994.3µs, avg: 2.25ms, p95: 4.22ms, max_proc_keys: 4360, p95_proc_keys: 4360, tot_proc: 9.18ms, tot_wait: 319.8µs, rpc_num: 6, rpc_time: 13.3ms, copr_cache_hit_ratio: 0.00, build_task_duration: 33µs, max_distsql_concurrency: 1}, tikv_task:{proc max:4ms, min:0s, avg: 1.5ms, p80:2ms, p95:4ms, iters:34, tasks:6}, scan_detail: {total_process_keys: 11112, total_process_keys_size: 820956, total_keys: 11118, get_snapshot_time: 112.7µs, rocksdb: {key_skipped_count: 11112, block: {cache_hit_count: 49}}} N/A N/A└─TableRowIDScan_6(Probe) cop[tikv] 11315.28 table:test1, keep order:false 11112 time:13.2ms, loops:16, cop_task: {num: 4, max: 4.8ms, min: 343.6µs, avg: 3.05ms, p95: 4.8ms, max_proc_keys: 2688, p95_proc_keys: 2688, tot_proc: 8.31ms, tot_wait: 229.8µs, rpc_num: 4, rpc_time: 12.1ms, copr_cache_hit_ratio: 0.25, build_task_duration: 148.6µs, max_distsql_concurrency: 1}, tikv_task:{proc max:6ms, min:2ms, avg: 3.5ms, p80:6ms, p95:6ms, iters:29, tasks:4}, scan_detail: {total_process_keys: 6056, total_process_keys_size: 355952, total_keys: 6067, get_snapshot_time: 67.2µs, rocksdb: {delete_skipped_count: 6053, key_skipped_count: 12106, block: {cache_hit_count: 94}}} N/A N/ABINARY_PLAN: wg1YCr0NCg1JbmRleExvb2tVcF83EsMFChAFEshSYW5nZVNjYW5fNRoBASEXw5QbeXpBQSnOWm9YpBnGQDDoVjgCQAJKGgoYCgR0ZXN0EgUBBjgxGgkKBGlkeDESAWJSK3IBRSg6WyJ1c2VyLTEiLAkJ8EkyIiksIGtlZXAgb3JkZXI6ZmFsc2VaFXRpbWU6MTMuMm1zLCBsb29wczoxNGKKAmNvcF90YXNrOiB7bnVtOiA2LCBtYXg6IDQuMgUrgG1pbjogOTk0LjPCtXMsIGF2ZzogMi4yNW1zLCBwOTU6IBUpRGF4X3Byb2Nfa2V5czogNDM2MAUiRhUACHRvdAUVFDogOS4xOAFJARIod2FpdDogMzE5LjgFaihycGNfbnVtOiA2LAUMBcEQIDEzLjMBMqBjb3ByX2NhY2hlX2hpdF9yYXRpbzogMC4wMCwgYnVpbGRfdGFza19kdQUaDG46IDMJwAGmdGRpc3RzcWxfY29uY3VycmVuY3k6IDF9ao8CdGlrdgU5BDp7AcsBLgQ6NAFvIQYAMBH/BDEuCf4IODA6JSMhBwUmJGl0ZXJzOjM0LCAhUmBzOjZ9LCBzY2FuX2RldGFpbDoge3RvdGFsBf0IZXNzLSoUMTExMTIsIQQ6GwAwX3NpemU6IDgyMDk1NhEhKV4BNDw4LCBnZXRfc25hcHNob3RfLSEMMTIuNylBaG9ja3NkYjoge2tleV9za2lwcGVkX2NvdW50OgVBKDIsIGJsb2NrOiB7OUoNIBg0OX19fXD/EQEEAXgRCkD/ARK4BQoQVGFibGVSb3dJREXGMDYaAQIhCX6Q2eeAQ0FCxgIQDwoNCgRBwE3GBFIQlqACDDZiiwI+oAIENCwllgggNC5FOiGZFCAzNDMuNgX0RZ8IMy4wKaFJnwkoAGEyngIMMjY4OEKeAgkVQYwh/RQ6IDguMzEh/QESSZ4EMjJCngIANDKeAgQyLgUyAGNangIEMjVangIQMTQ4LjYFwgGpXqECAKlSoQIANgGkIQhFjS0CKQEEODAJHiEKBSdJogQyOVGiADSSogIINjA1VYAhG1G8AF9JoRAzNTU5NVXCSaEMNjA2N1KgAgw2Ny4yBelZnxRkZWxldGU+ogIMNjA1M4HJRrsCDDIxMDZquwIEOTRiuwIcIUjI1Zo4GHU2owIMAUABWq0gADgpwAhsb2+hIBAyYusBaaHBiRRxv21pADSpTDxmZXRjaF9oYW5kbGU6IDE0CUKFhAg6IDNtjEk/BDE0BQ8AfSF+CGJsZVJWABg2LjdtcywghfoINCwgMqQEKDV9LCBuZXh0OiB7AVEAXwWcCDogNU3wBRQAdAVaGGxvb2t1cF8NhggxLjWFoEYhABxyZXNwOiAxMAF1RH1wsPIleP///////////wEYAQ==CHARSET: utf8mb4COLLATION: utf8mb4_0900_ai_ciPLAN_HINT: use_index(@`sel_1` `test`.`test1` `idx1`), no_order_index(@`sel_1` `test`.`test1` `idx1`)MAX_REQUEST_UNIT_READ: 28.53761791796875AVG_REQUEST_UNIT_READ: 28.53761791796875MAX_REQUEST_UNIT_WRITE: 0AVG_REQUEST_UNIT_WRITE: 0MAX_QUEUED_RC_TIME: 0AVG_QUEUED_RC_TIME: 0RESOURCE_GROUP: default1 row in set (0.03 sec)复制
假如某条最近执行的SQL语句执行计划符合预期，从statements_summary表中找到对应记录后并记录字段plan_digest，之后我们便可以根据这个plan_digest来创建绑定。
mysql> create global binding from history using plan digest '41e285a19ae862234757fd639e67b908bb2043638f5e74f83e08247080412f6b';Query OK, 0 rows affected (0.10 sec)mysql> show global bindings;+-------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------+------------+---------+-------------------------+-------------------------+---------+--------------------+---------+------------------------------------------------------------------+------------------------------------------------------------------+| Original_sql | Bind_sql | Default_db | Status | Create_time | Update_time | Charset | Collation | Source | Sql_digest | Plan_digest |+-------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------+------------+---------+-------------------------+-------------------------+---------+--------------------+---------+------------------------------------------------------------------+------------------------------------------------------------------+| select * from `test` . `test1` where `b` like ? | SELECT *+ use_index(@`sel_1` `test`.`test1` `idx1`) no_order_index(@`sel_1` `test`.`test1` `idx1`)*/ * FROM `test`.`test1` WHERE `b` LIKE 'user-1%' | test | enabled | 2024-03-11 11:17:42.220 | 2024-03-11 11:17:42.220 | utf8mb4 | utf8mb4_0900_ai_ci | history | eb338909e651eac55ccb5cb60dd0f3d6962e69257ecec55381013a9517244a42 | 41e285a19ae862234757fd639e67b908bb2043638f5e74f83e08247080412f6b |+-------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------+------------+---------+-------------------------+-------------------------+---------+--------------------+---------+------------------------------------------------------------------+------------------------------------------------------------------+1 row in set (0.00 sec)复制
2 删除SQL绑定
假如某个SQL绑定不想使用了，可以将其删除。删除绑定可以使用SQL语句来删除，也可以根据plan digest来删除。
根据SQL删除绑定
mysql> show global bindings;+-------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------+------------+---------+-------------------------+-------------------------+---------+--------------------+---------+------------------------------------------------------------------+------------------------------------------------------------------+| Original_sql | Bind_sql | Default_db | Status | Create_time | Update_time | Charset | Collation | Source | Sql_digest | Plan_digest |+-------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------+------------+---------+-------------------------+-------------------------+---------+--------------------+---------+------------------------------------------------------------------+------------------------------------------------------------------+| select * from `test` . `test1` where `b` like ? | SELECT /*+ use_index(@`sel_1` `test`.`test1` `idx1`) no_order_index(@`sel_1` `test`.`test1` `idx1`)*/ * FROM `test`.`test1` WHERE `b` LIKE 'user-1%' | test | enabled | 2024-03-11 11:17:42.220 | 2024-03-11 11:17:42.220 | utf8mb4 | utf8mb4_0900_ai_ci | history | eb338909e651eac55ccb5cb60dd0f3d6962e69257ecec55381013a9517244a42 | 41e285a19ae862234757fd639e67b908bb2043638f5e74f83e08247080412f6b |+-------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------+------------+---------+-------------------------+-------------------------+---------+--------------------+---------+------------------------------------------------------------------+------------------------------------------------------------------+1 row in set (0.00 sec)mysql> drop global binding for select * from `test` . `test1` where `b` like ?;Query OK, 1 row affected (0.02 sec)mysql> show global bindings;Empty set (0.00 sec)复制
根据plan digest删除绑定
mysql> show global bindings;+-------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------+------------+---------+-------------------------+-------------------------+---------+--------------------+---------+------------------------------------------------------------------+------------------------------------------------------------------+| Original_sql | Bind_sql | Default_db | Status | Create_time | Update_time | Charset | Collation | Source | Sql_digest | Plan_digest |+-------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------+------------+---------+-------------------------+-------------------------+---------+--------------------+---------+------------------------------------------------------------------+------------------------------------------------------------------+| select * from `test` . `test1` where `b` like ? | SELECT /*+ use_index(@`sel_1` `test`.`test1` `idx1`) no_order_index(@`sel_1` `test`.`test1` `idx1`)*/ * FROM `test`.`test1` WHERE `b` LIKE 'user-1%' | test | enabled | 2024-03-11 11:43:15.119 | 2024-03-11 11:43:15.119 | utf8mb4 | utf8mb4_0900_ai_ci | history | eb338909e651eac55ccb5cb60dd0f3d6962e69257ecec55381013a9517244a42 | 41e285a19ae862234757fd639e67b908bb2043638f5e74f83e08247080412f6b |+-------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------+------------+---------+-------------------------+-------------------------+---------+--------------------+---------+------------------------------------------------------------------+------------------------------------------------------------------+1 row in set (0.01 sec)mysql> drop global binding for sql digest 'eb338909e651eac55ccb5cb60dd0f3d6962e69257ecec55381013a9517244a42';Query OK, 1 row affected (0.01 sec)mysql> show global bindings;Empty set (0.00 sec)复制
3 查看SQL绑定是否缓存
SQL绑定可以缓存在TiDB Server中，这样可以提升获取执行计划的效率。用于缓存SQL绑定的内存大小由参数tidb_mem_quota_binding_cache决定，默认为64MB。
mysql> select @@tidb_mem_quota_binding_cache;+--------------------------------+| @@tidb_mem_quota_binding_cache |+--------------------------------+| 67108864 |+--------------------------------+1 row in set (0.00 sec)mysql> show binding_cache status;+-------------------+-------------------+--------------+--------------+| bindings_in_cache | bindings_in_table | memory_usage | memory_quota |+-------------------+-------------------+--------------+--------------+| 1 | 1 | 400 Bytes | 64 MB |+-------------------+-------------------+--------------+--------------+1 row in set (0.00 sec)复制
二．如何使用自动捕获绑定来应对升级造成的执行计划不一致？
有时候我们可能会发现，当系统进行版本升级后某些SQL语句由于执行计划变化而导致突然变慢的情况，这可能是由于不同版本在优化器选择方面的成本计算不同导致。那么我们有没有一种方法可以将升级前语句的执行计划进行绑定保存呢？
TiDB提供了自动捕获执行计划绑定功能，这个功能由参数tidb_capture_plan_baselines控制，默认是关闭的。当我们需要进行版本升级之前，可以在现有版本中打开这个参数并运行一段时间，数据库会默认每3秒遍历一次statement summary中的历史SQL语句，并为至少出现两次的SQL语句自动捕获绑定。
当然，这种自动捕获的方式也有一定局限性，所以满足条件的SQL语句都会被捕获。如果我们想过滤某些SQL语句，可以将过滤规则插入到系统表mysql.capture_plan_baselines_blacklist中，可以按表名、执行频率、用户名进行过滤。
本文简单的聊聊TiDB中的执行计划绑定功能，描述如何使用执行计划绑定以及如何利用自动捕获执行计划的能力来应对版本升级导致的执行计划变更问题。关于执行计划管理的更多细节，请参考官网执行计划管理 (SPM) | PingCAP 文档中心 介绍。
文章转载自数据源的技术后花园，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
评论
相关阅读
【专家观点】罗敏：从理论到真实SQL，感受DeepSeek如何做性能优化
墨天轮编辑部
1280次阅读
2025-03-06 16:45:38
SQLE 4.0 正式版发布，新增 SQL 重写、SQL 性能追踪、语法知识图谱等功能
爱可生开源社区
359次阅读
2025-03-07 10:30:00
史诗级革新 | Apache Flink 2.0 正式发布
严少安
153次阅读
2025-03-25 00:55:05
云和恩墨钟浪峰：安全生产系列之SQL优化安全操作
墨天轮编辑部
151次阅读
2025-03-31 11:08:20
NineData 社区版：从 MySQL 到 TiDB 数据复制新选择
严少安
139次阅读
2025-03-13 17:27:45
PingCAP 2025 校园招聘 | 开源领航，星耀前程
PingCAP
108次阅读
2025-03-27 09:55:55
降价46%，Hologres Serverless Computing 快速入门
阿里云大数据AI技术
104次阅读
2025-03-11 10:42:00
你好，3月 | 各大数据库社区又要卷出新高度啦
严少安
85次阅读
2025-03-04 17:50:59
解锁高效查询的关键功能 Group by分组
YashanDB
79次阅读
2025-03-05 11:08:38
基于 MySQL 8.0 细粒度授权：单独授予 KILL 权限的优雅解决方案
GreatSQL
75次阅读
2025-03-05 14:32:50
