黄东旭解析 TiDB 的核心优势
611
2024-03-18
详解TiDB的执行计划绑定功能
数据库中SQL语句的执行计划可能会随着时间的推移而发生变化，数据量的增长、统计信息的更新等都可能会导致相同SQL语句在不同的时间节点其执行计划不一致，从而引发性能问题。在一个稳定运行的生产环境中，大部分情况下我们都希望同一个SQL语句的执行计划保持固定不变，数据库也提供了一些手段帮助用户来稳定执行计划。
比较通用的固化执行计划的方法就是使用HINT，HINT最早是***数据库中很有特色的一个功能，也是很多DBA优化中经常采用的一个手段。数据库的优化器是一个非常复杂的模块，即使是***这样的数据库在一些场景下也难以保证执行计划是最优的，HINT提供了一种机制可以告诉优化器按照想要的方式生成执行计划，当然HINT也可以被用来固化语句的执行计划。HINT的典型用法就是select /*+ (具体的Hint内容） */…，它通过一种特殊的注释方式来影响执行计划。
业内大多数数据库基本都有HINT的功能，只不过不同数据库可能有自己的HINT写法。笔者之前使用的一款数据库在表名后面增加<<+index index_name>>来让执行计划走索引，后来为了兼容***，也支持了/*+ … */这样的写法。
TiDB也支持HINT功能，语法上兼容*** 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: 23218
AVG_ROCKSDB_BLOCK_CACHE_HIT_COUNT: 7.07e-322
MAX_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: default
1 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)
假如某个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)
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 文档中心 介绍。
版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。
相关文章
