一文了解 TiDB 的执行计划绑定功能
作者：TiDB 社区干货传送门
- 2024-03-15 北京
本文字数：14004 字
阅读完需：约 46 分钟
作者： 数据源的 TiDB 学习之路原文来源：https://tidb.net/blog/5c06d6d1
数据库中 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:00 SUMMARY_END_TIME: 2024-03-11 11:30:00 STMT_TYPE: Select SCHEMA_NAME: test DIGEST: 6c3daca0b3e0fdb874a7fc28883d1a5d55cbf68a38289ea99215bbd6d64d157e DIGEST_TEXT: select * from `test1` where `b` like ? TABLE_NAMES: test.test1 INDEX_NAMES: test1:idx1 SAMPLE_USER: root EXEC_COUNT: 1 SUM_ERRORS: 0 SUM_WARNINGS: 0 SUM_LATENCY: 21295823 MAX_LATENCY: 21295823 MIN_LATENCY: 21295823 AVG_LATENCY: 21295823 AVG_PARSE_LATENCY: 253050 MAX_PARSE_LATENCY: 253050 AVG_COMPILE_LATENCY: 577573 MAX_COMPILE_LATENCY: 577573 SUM_COP_TASK_NUM: 10 MAX_COP_PROCESS_TIME: 3395404 MAX_COP_PROCESS_ADDRESS: 172.20.12.52:20161 MAX_COP_WAIT_TIME: 87124 MAX_COP_WAIT_ADDRESS: 172.20.12.52:20161 AVG_PROCESS_TIME: 17488281 MAX_PROCESS_TIME: 17488281 AVG_WAIT_TIME: 549676 MAX_WAIT_TIME: 549676 AVG_BACKOFF_TIME: 0 MAX_BACKOFF_TIME: 0 AVG_TOTAL_KEYS: 17185 MAX_TOTAL_KEYS: 17185 AVG_PROCESSED_KEYS: 17168 MAX_PROCESSED_KEYS: 17168 AVG_ROCKSDB_DELETE_SKIPPED_COUNT: 2.9906e-320 MAX_ROCKSDB_DELETE_SKIPPED_COUNT: 6053 AVG_ROCKSDB_KEY_SKIPPED_COUNT: 1.1471e-319 MAX_ROCKSDB_KEY_SKIPPED_COUNT: 23218AVG_ROCKSDB_BLOCK_CACHE_HIT_COUNT: 7.07e-322MAX_ROCKSDB_BLOCK_CACHE_HIT_COUNT: 143 AVG_ROCKSDB_BLOCK_READ_COUNT: 0 MAX_ROCKSDB_BLOCK_READ_COUNT: 0 AVG_ROCKSDB_BLOCK_READ_BYTE: 0 MAX_ROCKSDB_BLOCK_READ_BYTE: 0 AVG_PREWRITE_TIME: 0 MAX_PREWRITE_TIME: 0 AVG_COMMIT_TIME: 0 MAX_COMMIT_TIME: 0 AVG_GET_COMMIT_TS_TIME: 0 MAX_GET_COMMIT_TS_TIME: 0 AVG_COMMIT_BACKOFF_TIME: 0 MAX_COMMIT_BACKOFF_TIME: 0 AVG_RESOLVE_LOCK_TIME: 0 MAX_RESOLVE_LOCK_TIME: 0 AVG_LOCAL_LATCH_WAIT_TIME: 0 MAX_LOCAL_LATCH_WAIT_TIME: 0 AVG_WRITE_KEYS: 0 MAX_WRITE_KEYS: 0 AVG_WRITE_SIZE: 0 MAX_WRITE_SIZE: 0 AVG_PREWRITE_REGIONS: 0 MAX_PREWRITE_REGIONS: 0 AVG_TXN_RETRY: 0 MAX_TXN_RETRY: 0 SUM_EXEC_RETRY: 0 SUM_EXEC_RETRY_TIME: 0 SUM_BACKOFF_TIMES: 0 BACKOFF_TYPES: NULL AVG_MEM: 620848 MAX_MEM: 620848 AVG_DISK: 0 MAX_DISK: 0 AVG_KV_TIME: 0 AVG_PD_TIME: 0 AVG_BACKOFF_TOTAL_TIME: 0 AVG_WRITE_SQL_RESP_TIME: 0 MAX_RESULT_ROWS: 11112 MIN_RESULT_ROWS: 11112 AVG_RESULT_ROWS: 11112 PREPARED: 0 AVG_AFFECTED_ROWS: 0 FIRST_SEEN: 2024-03-11 11:07:29 LAST_SEEN: 2024-03-11 11:07:29 PLAN_IN_CACHE: 0 PLAN_CACHE_HITS: 0 PLAN_IN_BINDING: 0 QUERY_SAMPLE_TEXT: SELECT /*+ use_index(test1, idx1) */ * from test1 where b like 'user-1%' PREV_SAMPLE_TEXT: PLAN_DIGEST: 41e285a19ae862234757fd639e67b908bb2043638f5e74f83e08247080412f6b PLAN: id task estRows operator info actRows execution info memory disk IndexLookUp_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/A BINARY_PLAN: wg1YCr0NCg1JbmRleExvb2tVcF83EsMFChAFEshSYW5nZVNjYW5fNRoBASEXw5QbeXpBQSnOWm9YpBnGQDDoVjgCQAJKGgoYCgR0ZXN0EgUBBjgxGgkKBGlkeDESAWJSK3IBRSg6WyJ1c2VyLTEiLAkJ8EkyIiksIGtlZXAgb3JkZXI6ZmFsc2VaFXRpbWU6MTMuMm1zLCBsb29wczoxNGKKAmNvcF90YXNrOiB7bnVtOiA2LCBtYXg6IDQuMgUrgG1pbjogOTk0LjPCtXMsIGF2ZzogMi4yNW1zLCBwOTU6IBUpRGF4X3Byb2Nfa2V5czogNDM2MAUiRhUACHRvdAUVFDogOS4xOAFJARIod2FpdDogMzE5LjgFaihycGNfbnVtOiA2LAUMBcEQIDEzLjMBMqBjb3ByX2NhY2hlX2hpdF9yYXRpbzogMC4wMCwgYnVpbGRfdGFza19kdQUaDG46IDMJwAGmdGRpc3RzcWxfY29uY3VycmVuY3k6IDF9ao8CdGlrdgU5BDp7AcsBLgQ6NAFvIQYAMBH/BDEuCf4IODA6JSMhBwUmJGl0ZXJzOjM0LCAhUmBzOjZ9LCBzY2FuX2RldGFpbDoge3RvdGFsBf0IZXNzLSoUMTExMTIsIQQ6GwAwX3NpemU6IDgyMDk1NhEhKV4BNDw4LCBnZXRfc25hcHNob3RfLSEMMTIuNylBaG9ja3NkYjoge2tleV9za2lwcGVkX2NvdW50OgVBKDIsIGJsb2NrOiB7OUoNIBg0OX19fXD/EQEEAXgRCkD/ARK4BQoQVGFibGVSb3dJREXGMDYaAQIhCX6Q2eeAQ0FCxgIQDwoNCgRBwE3GBFIQlqACDDZiiwI+oAIENCwllgggNC5FOiGZFCAzNDMuNgX0RZ8IMy4wKaFJnwkoAGEyngIMMjY4OEKeAgkVQYwh/RQ6IDguMzEh/QESSZ4EMjJCngIANDKeAgQyLgUyAGNangIEMjVangIQMTQ4LjYFwgGpXqECAKlSoQIANgGkIQhFjS0CKQEEODAJHiEKBSdJogQyOVGiADSSogIINjA1VYAhG1G8AF9JoRAzNTU5NVXCSaEMNjA2N1KgAgw2Ny4yBelZnxRkZWxldGU+ogIMNjA1M4HJRrsCDDIxMDZquwIEOTRiuwIcIUjI1Zo4GHU2owIMAUABWq0gADgpwAhsb2+hIBAyYusBaaHBiRRxv21pADSpTDxmZXRjaF9oYW5kbGU6IDE0CUKFhAg6IDNtjEk/BDE0BQ8AfSF+CGJsZVJWABg2LjdtcywghfoINCwgMqQEKDV9LCBuZXh0OiB7AVEAXwWcCDogNU3wBRQAdAVaGGxvb2t1cF8NhggxLjWFoEYhABxyZXNwOiAxMAF1RH1wsPIleP///////////wEYAQ== CHARSET: utf8mb4 COLLATION: utf8mb4_0900_ai_ci PLAN_HINT: use_index(@`sel_1` `test`.`test1` `idx1`), no_order_index(@`sel_1` `test`.`test1` `idx1`) MAX_REQUEST_UNIT_READ: 28.53761791796875 AVG_REQUEST_UNIT_READ: 28.53761791796875 MAX_REQUEST_UNIT_WRITE: 0 AVG_REQUEST_UNIT_WRITE: 0 MAX_QUEUED_RC_TIME: 0 AVG_QUEUED_RC_TIME: 0 RESOURCE_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 删除绑定
根据 plan digest 删除绑定
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 文档中心 介绍。
划线
评论
复制
发布于: 刚刚阅读数: 2
版权声明: 本文为 InfoQ 作者【TiDB 社区干货传送门】的原创文章。
原文链接:【http://xie.infoq.cn/article/774eac02ab94e7f050feafd76】。文章转载请联系作者。
TiDB 社区干货传送门
关注
TiDB 社区官网:https://tidb.net/ 2021-12-15 加入
TiDB 社区干货传送门是由 TiDB 社区中布道师组委会自发组织的 TiDB 社区优质内容对外宣布的栏目，旨在加深 TiDBer 之间的交流和学习。一起构建有爱、互助、共创共建的 TiDB 社区 https://tidb.net/
评论