为提高效率,请提供以下信息,问题描述清晰能够更快得到解决:
【 TiDB 使用环境】
CentOS 7.8 + TiDB v4.0.10
【概述】 场景 + 问题概述
慢查询日志的tidb_decode_plan的执行计划如下:
select tidb_decode_plan('rwnIMAkyOF80CTAJMAlOL0EJMAl0aW1lOjIuNzg1NDYxNDE3cywgbG9vcHM6MQkwIEJ5dGVzASlwCjEJMzBfMTIJMAkxLjQwODcxODEwNDUzOTc3OQk2RgAMMjUwMRlFfCwgaW5kZXhfdGFzazo1MzMuOTMxNTE4bXMsIHRhYmxlCRlYe251bTo1LCBjb25jdXJyZW5jeTo4LCAFnGgxMi41ODYyOTI0NzdzfQk1MjUuMjE4NzUgS0IFmnAyCTEzXzkJMV8wCTQuNDA2MDUxMDc2MzUzNzg3CQVkNDpzdGRfbWVkaWFmaWxlBZANl/BPOnVuaXFfbXV0aTIoT2JqZWN0SUQsIENoYW5uZWwsIEJlZ2luVGltZSwgRW5kVGltZSksIHJhbmdlOlsyMDI5MTUyMTAgMCAtaW5mLDIwMjkREYgxNjM4MTQwNzIwKSwga2VlcCBvcmRlcjpmYWxzZQkyMzM0Mil5KRIMOTM0NCESAGwleSUIAHApGQAgJRoYIDEsIG1heAUuEDY3OTAwJUAocHJvY19rZXlzOiAFUhQsIHJwY18hSgEwAQwlQAAgAWQUNjQwMzgzAWRoY29wcl9jYWNoZV9oaXRfcmF0aW86IDAuMDB9IXEEa3YJeQB7BTwAOAVpCZcYMjd9CU4vQQEEIXQMXzExCSF0Rg8COGluKG1nX2lvdmRiX3Y3LkZ9ATAuc3RvcmFnZXR5cGUsAawIMykJTYkoOC4yOTI3NzgwNzlVREoQAQgyNDIpEkQgMi4yMzE2NTcxNjRzLCBtaW4J8FQyMDbCtXMsIGF2ZzogNDg1Ljk3MTc3JQE4cDk1OiAxLjYyNDk4OTgyATYIYXhfLk0BCDQzLAEmMhQAGDIzLCB0b3QFFAE/BDMxQeUBEjB3YWl0OiA0NC4yMTNzPYQAMgGkDHJwY18lSjwgMW01Ny42MDEyMzA4NDVzQeyWiQEBmiX+ADMljQHjIDBzLCBwODA6NyHRAdAAMQUKHGl0ZXJzOjU1YTocYXNrczoyNDI5tBgzCTEwXzEwJbWuKQNuvwIEMG41pgAwMj4C/rUAEbU=');
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tidb_decode_plan('rwnIMAkyOF80CTAJMAlOL0EJMAl0aW1lOjIuNzg1NDYxNDE3cywgbG9vcHM6MQkwIEJ5dGVzASlwCjEJMzBfMTIJMAkxLjQwODcxODEwNDUzOTc3OQk2RgAMMjUwMRlFfCwgaW5kZXhfdGFzazo1MzMuOTMxNTE4bXMsIHRhYmxlCRlYe251bTo1LCBjb25jdXJyZW5jeTo4LCAFnGgxMi41ODYyOTI0NzdzfQk1MjUuMj |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| id task estRows operator info actRows execution info memory disk
Update_4 root 0 N/A 0 time:2.785461417s, loops:1 0 Bytes N/A
└─IndexLookUp_12 root 1.408718104539779 0 time:2.78542501s, loops:1, index_task:533.931518ms, table_task:{num:5, concurrency:8, time:12.586292477s} 525.21875 KB N/A
├─IndexScan_9 cop[tikv] 4.406051076353787 table:std_mediafileindex, index:uniq_muti2(ObjectID, Channel, BeginTime, EndTime), range:[202915210 0 -inf,202915210 0 1638140720), keep order:false 23342 time:533.939344ms, loops:5, cop_task: {num: 1, max:533.679008ms, proc_keys: 23342, rpc_num: 1, rpc_time: 533.640383ms, copr_cache_hit_ratio: 0.00}, tikv_task:{time:88ms, loops:27} N/A N/A
└─Selection_11 cop[tikv] 1.408718104539779 in(mg_iovdb_v7.std_mediafileindex.storagetype, 1, 3) 0 time:8.292778079s, loops:5, cop_task: {num: 242, max: 2.231657164s, min: 533.206µs, avg: 485.971773ms, p95: 1.62498982s, max_proc_keys: 243, p95_proc_keys: 223, tot_proc: 1.311s, tot_wait: 44.213s, rpc_num: 242, rpc_time: 1m57.601230845s, copr_cache_hit_ratio: 0.00}, tikv_task:{proc max:38ms, min:0s, p80:7ms, p95:17ms, iters:558, tasks:242} N/A N/A
└─TableScan_10 cop[tikv] 4.406051076353787 table:std_mediafileindex, keep order:false 23342 time:0ns, loops:0, tikv_task:{proc max:38ms, min:0s, p80:7ms, p95:17ms, iters:558, tasks:242} N/A N/A |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
把慢查询里的sql语句的绑定变量参数接上去后的计执行计划如下:
explain update std_mediafileindex set storageType=storageType-1,updateTime=now() where objectID=202915210 and channel=0 and storageType in(1,3) and beginTime<1638140720;
+---------------------------------+---------+-----------+-----------------------------------------------------------------------------------+-------------------------------------------------------------------+
| id | estRows | task | access object | operator info |
+---------------------------------+---------+-----------+-----------------------------------------------------------------------------------+-------------------------------------------------------------------+
| Update_4 | N/A | root | | N/A |
| └─IndexLookUp_12 | 1.42 | root | | |
| ├─IndexRangeScan_9(Build) | 4.43 | cop[tikv] | table:std_mediafileindex, index:uniq_muti2(ObjectID, Channel, BeginTime, EndTime) | range:[202915210 0 -inf,202915210 0 1638140720), keep order:false |
| └─Selection_11(Probe) | 1.42 | cop[tikv] | | in(mg_iovdb_v7.std_mediafileindex.storagetype, 1, 3) |
| └─TableRowIDScan_10 | 4.43 | cop[tikv] | table:std_mediafileindex | keep order:false |
+---------------------------------+---------+-----------+-----------------------------------------------------------------------------------+-------------------------------------------------------------------+
5 rows in set (0.00 sec)
慢查询日志如下:
# Time: 2021-11-29T16:16:08.347822537+08:00
# Txn_start_ts: 429437423590309949
# User@Host: xxx[xxxs] @ 10.1.0.xxx [10.1.0.xxxx]
# Conn_ID: 31296910
# Query_time: 2.786382539
# Parse_time: 0.000151646
# Compile_time: 0.000694488
# Rewrite_time: 0.000575094
# Cop_time: 8.823216285 Process_time: 1.4 Wait_time: 44.237 Request_count: 243 Total_keys: 46691 Process_keys: 46684
# DB: mg_iovdb_v7
# Index_names: [std_mediafileindex:uniq_muti2]
# Is_internal: false
# Digest: 569150f7dba3c8273187dee9eb6fa5b952647f65f78cf8c895b5a013a6b6ad55
# Stats: std_mediafileindex:429437417639641119
# Num_cop_tasks: 243
# Cop_proc_avg: 0.005761316 Cop_proc_p90: 0.013 Cop_proc_max: 0.089 Cop_proc_addr: 172.16.5.222:20160
# Cop_wait_avg: 0.182045267 Cop_wait_p90: 0.861 Cop_wait_max: 1.569 Cop_wait_addr: 172.16.5.222:20160
# Mem_max: 537824
# Prepared: true
# Plan_from_cache: false
# Has_more_results: false
# KV_total: 118.133000749
# PD_total: 0.00000577
# Backoff_total: 0
# Write_sql_response_total: 0
# Succ: true
# Plan: tidb_decode_plan('rwnIMAkyOF80CTAJMAlOL0EJMAl0aW1lOjIuNzg1NDYxNDE3cywgbG9vcHM6MQkwIEJ5dGVzASlwCjEJMzBfMTIJMAkxLjQwODcxODEwNDUzOTc3OQk2RgAMMjUwMRlFfCwgaW5kZXhfdGFzazo1MzMuOTMxNTE4bXMsIHRhYmxlCRlYe251bTo1LCBjb25jdXJyZW5jeTo4LCAFnGgxMi41ODYyOTI0NzdzfQk1MjUuMjE4NzUgS0IFmnAyCTEzXzkJMV8wCTQuNDA2MDUxMDc2MzUzNzg3CQVkNDpzdGRfbWVkaWFmaWxlBZANl/BPOnVuaXFfbXV0aTIoT2JqZWN0SUQsIENoYW5uZWwsIEJlZ2luVGltZSwgRW5kVGltZSksIHJhbmdlOlsyMDI5MTUyMTAgMCAtaW5mLDIwMjkREYgxNjM4MTQwNzIwKSwga2VlcCBvcmRlcjpmYWxzZQkyMzM0Mil5KRIMOTM0NCESAGwleSUIAHApGQAgJRoYIDEsIG1heAUuEDY3OTAwJUAocHJvY19rZXlzOiAFUhQsIHJwY18hSgEwAQwlQAAgAWQUNjQwMzgzAWRoY29wcl9jYWNoZV9oaXRfcmF0aW86IDAuMDB9IXEEa3YJeQB7BTwAOAVpCZcYMjd9CU4vQQEEIXQMXzExCSF0Rg8COGluKG1nX2lvdmRiX3Y3LkZ9ATAuc3RvcmFnZXR5cGUsAawIMykJTYkoOC4yOTI3NzgwNzlVREoQAQgyNDIpEkQgMi4yMzE2NTcxNjRzLCBtaW4J8FQyMDbCtXMsIGF2ZzogNDg1Ljk3MTc3JQE4cDk1OiAxLjYyNDk4OTgyATYIYXhfLk0BCDQzLAEmMhQAGDIzLCB0b3QFFAE/BDMxQeUBEjB3YWl0OiA0NC4yMTNzPYQAMgGkDHJwY18lSjwgMW01Ny42MDEyMzA4NDVzQeyWiQEBmiX+ADMljQHjIDBzLCBwODA6NyHRAdAAMQUKHGl0ZXJzOjU1YTocYXNrczoyNDI5tBgzCTEwXzEwJbWuKQNuvwIEMG41pgAwMj4C/rUAEbU=')
# Plan_digest: 6f860409a5c627a272993260f710d2b95b29d8e6772bb45f46cbd8ef2df9c068
update std_mediafileindex set storageType=storageType-1,updateTime=now() where objectID=? and channel=? and storageType in(1,3) and beginTime<? [arguments: (202915210, 0, 1638140720)];
【背景】 做过哪些操作
【现象】 业务和数据库现象
【问题】 当前遇到的问题
sql语句绑定变量时很慢,改为拼接sql时很快
【业务影响】
update很慢
【TiDB 版本】
v4.0.10
若提问为性能优化、故障排查类问题,请下载脚本运行。终端输出的打印结果,请务必全选并复制粘贴上传。