sql语句绑定变量时走IndexScan,改为拼接sql时走IndexRangeScan

为提高效率,请提供以下信息,问题描述清晰能够更快得到解决:
【 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


若提问为性能优化、故障排查类问题,请下载脚本运行。终端输出的打印结果,请务必全选并复制粘贴上传。

2 个赞

这个集群是由以前版本升上来的还是直接安装的4.0.10版本,indexscan/tablescan在官网的3.x版本里能找到相应说明,4.0版本后没找到,另外不加变量的explain analyze 执行计划发下,看下实际执行的

2 个赞

感谢回复!

集群是从4.0.4开始搭建,年初做了一次升级到4.0.10,不加变量的explain analyze如下:

explain analyze 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 | actRows | task      | access object                                                                     | execution info                                                                                                                                                                                                                                                                                                                                   | operator info                                                     | memory       | disk |
+---------------------------------+---------+---------+-----------+-----------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------+--------------+------+
| Update_4                        | N/A     | 0       | root      |                                                                                   | time:104.149516ms, loops:1                                                                                                                                                                                                                                                                                                                       | N/A                                                               | 0 Bytes      | N/A  |
| └─IndexLookUp_12                | 1.43    | 0       | root      |                                                                                   | time:104.129839ms, loops:1, index_task:47.517006ms, table_task:{num:5, concurrency:8, time:582.850528ms}                                                                                                                                                                                                                                         |                                                                   | 525.21875 KB | N/A  |
|   ├─IndexRangeScan_9(Build)     | 4.48    | 23342   | cop[tikv] | table:std_mediafileindex, index:uniq_muti2(ObjectID, Channel, BeginTime, EndTime) | time:47.525142ms, loops:5, cop_task: {num: 1, max:47.145728ms, proc_keys: 23342, rpc_num: 1, rpc_time: 47.131454ms, copr_cache_hit_ratio: 0.00}, tikv_task:{time:45ms, loops:27}                                                                                                                                                                 | range:[202915210 0 -inf,202915210 0 1638140720), keep order:false | N/A          | N/A  |
|   └─Selection_11(Probe)         | 1.43    | 0       | cop[tikv] |                                                                                   | time:186.714338ms, loops:5, cop_task: {num: 242, max: 49.098522ms, min: 1.124659ms, avg: 12.99779ms, p95: 32.400565ms, max_proc_keys: 243, p95_proc_keys: 223, tot_proc: 1.052s, tot_wait: 1.139s, rpc_num: 242, rpc_time: 3.140951918s, copr_cache_hit_ratio: 0.00}, tikv_task:{proc max:18ms, min:0s, p80:7ms, p95:11ms, iters:558, tasks:242} | in(mg_iovdb_v7.std_mediafileindex.storagetype, 1, 3)              | N/A          | N/A  |
|     └─TableRowIDScan_10         | 4.48    | 23342   | cop[tikv] | table:std_mediafileindex                                                          | time:0ns, loops:0, tikv_task:{proc max:18ms, min:0s, p80:7ms, p95:11ms, iters:558, tasks:242}                                                                                                                                                                                                                                                    | keep order:false                                                  | N/A          | N/A  |
+---------------------------------+---------+---------+-----------+-----------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------+--------------+------+
5 rows in set (0.10 sec)
2 个赞

喊研发大佬来看看

2 个赞

怎么喊:joy:

1 个赞

@听风吹雨 大佬再帮看看这个问题

1 个赞

麻烦提供下表结构,还有dashboard的中的慢SQL语句与执行计划。

1 个赞

表结构如下:

CREATE TABLE `std_mediafileindex` (
  `id` bigint(20) NOT NULL /*T![auto_rand] AUTO_RANDOM(5) */ COMMENT ' 主键',
  `IndexID` bigint(20) unsigned NOT NULL DEFAULT '0' COMMENT '主键ID',
  `ObjectID` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '设备ID',
  `Channel` tinyint(2) unsigned NOT NULL DEFAULT '0' COMMENT '通道号',
  `LocalFileName` varchar(128) NOT NULL DEFAULT '' COMMENT '本地文件',
  `LocalFilePath` varchar(128) DEFAULT '' COMMENT '本地路径',
  `FileFormat` varchar(8) NOT NULL DEFAULT '' COMMENT '文件格式',
  `FileSize` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '文件大小',
  `Resolution` tinyint(2) unsigned DEFAULT '0' COMMENT '分辨率',
  `Rate` int(11) unsigned DEFAULT '0' COMMENT 'xx',
  `FileType` tinyint(2) unsigned NOT NULL DEFAULT '0' COMMENT '文件类型:0-设备本地录制, 1-直播录制',
  `StorageType` tinyint(2) unsigned DEFAULT '0' COMMENT '存储类型:按比特位存储',
  `CloudUrl` varchar(256) DEFAULT '' COMMENT '云路径',
  `ThumbUrl` varchar(256) DEFAULT '' COMMENT '缩略图链接',
  `BeginTime` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '开始时间',
  `EndTime` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '结束时间',
  `FileModifyTime` datetime NOT NULL COMMENT '文件修改时间 ',
  `Duration` int(11) unsigned DEFAULT '0' COMMENT '时长秒',
  `Version` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '版本号',
  `InsertTime` datetime DEFAULT NULL COMMENT '添加时间',
  `UpdateTime` datetime DEFAULT NULL COMMENT '修改时间',
  `AwsBucketName` varchar(128) DEFAULT '' COMMENT '存储桶名称',
  `AwsFileName` varchar(128) DEFAULT '' COMMENT '存储文件名',
  `MetaDataOffset` int(11) unsigned DEFAULT '0' COMMENT '元数据偏移量',
  `MetaDataSize` int(11) unsigned DEFAULT '0' COMMENT '元数据大小',
  `CloudType` tinyint(2) unsigned NOT NULL DEFAULT '0' COMMENT '存储类型',
  PRIMARY KEY (`id`),
  KEY `idx_ObjectID_FileModifyTime_Channel` (`ObjectID`,`FileModifyTime`,`Channel`),
  KEY `idx_ObjectID_Version` (`ObjectID`,`Version`),
  KEY `idx_BeginTime_objectid` (`BeginTime`,`ObjectID`),
  UNIQUE KEY `uniq_indexid` (`IndexID`),
  UNIQUE KEY `uniq_muti2` (`ObjectID`,`Channel`,`BeginTime`,`EndTime`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin /*T![auto_rand_base] AUTO_RANDOM_BASE=376300001 */

dashboard的中的慢SQL语句无法显示,如下图:

执行计划如主题所示

谢谢大佬!

1 个赞

现在能复现一下SQL绑定变量时候的执行计划吗?不是慢日志的执行计划,先要判断下当时的慢SQL是否由于其它问题导致它变成了慢SQL。

1 个赞

直接在mysql客户端好像是没办法使用绑定变量的,生产上没办法再写个程序去绑定做测试:sob:

1 个赞

请教下执行计划的2个问题 1. IndexScan/TableScan我看再4.0及以上版本的官方描述中已经没有了,是把这个细拆成indexrangeScan/indexFullScan这些了吗? 什么情况会导致较新的版本执行计划还显示IndexScan/TableScan 2. 我看程序执行计划和explain anlyze上 主要时间差异在indexLookUp 和selection 2个算子上,索引扫描和回表都是毫秒级的,是否使用IndexScan/TableScan时会影响某些算法?比如火山模型、向量模型这些

1 个赞

客户端 用@这种变量方式试试

1 个赞

1、绑定变量可以参考:
SET @startTime = ‘2021-04-07 16:00:00’;
select * from table where startTime=@startTime;
2、你这个是update的执行计划,你可以换成select的执行计划对比。

1 个赞

1、indexrangeScan/indexFullScan是已经从IndexScan拆分出来了,从IndexScan对应的执行计划中的execution info字段也是使用range的,所以怀疑是慢SQL的SQL指纹在生成的时候没有区分这个,这个有待我们这边确认后再回复。
2、当时产生了慢SQL,会不会有可能是当时TiDB的负载就高导致的呢?这个慢SQL有可能是原因也可能是结果。所以还得分析下现在两种执行方式下是否有大的时间差异。

使用变量的执行计划如下:

mysql>set @objectID=202915210;
set @channel=0;Query OK, 0 rows affected (0.00 sec)

mysql>set @channel=0;
Query OK, 0 rows affected (0.00 sec)

mysql>set @beginTime=1638140720;
Query OK, 0 rows affected (0.00 sec)

mysql>explain  update std_mediafileindex set storageType=storageType-1,updateTime=now() where objectID=@objectID and channel=@channel and storageType in(1,3) and beginTime<@beginTime;  
+-----------------------------+-------------+-----------+--------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| id                          | estRows     | task      | access object            | operator info                                                                                                                                                                                     |
+-----------------------------+-------------+-----------+--------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Update_4                    | N/A         | root      |                          | N/A                                                                                                                                                                                               |
| └─Selection_6               | 6562767.97  | root      |                          | eq(db_v7.std_mediafileindex.channel, getvar("channel")), eq(db_v7.std_mediafileindex.objectid, getvar("objectid")), lt(db_v7.std_mediafileindex.begintime, getvar("begintime"))                   |
|   └─TableReader_9           | 8203459.97  | root      |                          | data:Selection_8                                                                                                                                                                                  |
|     └─Selection_8           | 8203459.97  | cop[tikv] |                          | in(db_v7.std_mediafileindex.storagetype, 1, 3)                                                                                                                                                    |
|       └─TableFullScan_7     | 25532359.00 | cop[tikv] | table:std_mediafileindex | keep order:false                                                                                                                                                                                  |
+-----------------------------+-------------+-----------+--------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
5 rows in set (0.01 sec)

用强制索引试试,和上面执行计划一样的索引

强制索引作用不大,如下:

mysql>set @objectID=202915210;
Query OK, 0 rows affected (0.00 sec)

mysql>set @channel=0;
Query OK, 0 rows affected (0.00 sec)

mysql>set @beginTime=1638140720;
Query OK, 0 rows affected (0.00 sec)

mysql>explain  update std_mediafileindex force index(uniq_muti2) set storageType=storageType-1,updateTime=now() where objectID=@objectID and channel=@channel and storageType in(1,3) and beginTime<@beginTime;  
+----------------------------------+-------------+-----------+-----------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| id                               | estRows     | task      | access object                                                                     | operator info                                                                                                                                                                                     |
+----------------------------------+-------------+-----------+-----------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Update_4                         | N/A         | root      |                                                                                   | N/A                                                                                                                                                                                               |
| └─Selection_6                    | 6618363.85  | root      |                                                                                   | eq(db_v7.std_mediafileindex.channel, getvar("channel")), eq(db_v7.std_mediafileindex.objectid, getvar("objectid")), lt(db_v7.std_mediafileindex.begintime, getvar("begintime"))                   |
|   └─IndexLookUp_10               | 8272954.81  | root      |                                                                                   |                                                                                                                                                                                                   |
|     ├─IndexFullScan_7(Build)     | 25748654.00 | cop[tikv] | table:std_mediafileindex, index:uniq_muti2(ObjectID, Channel, BeginTime, EndTime) | keep order:false                                                                                                                                                                                  |
|     └─Selection_9(Probe)         | 8272954.81  | cop[tikv] |                                                                                   | in(db_v7.std_mediafileindex.storagetype, 1, 3)                                                                                                                                                    |
|       └─TableRowIDScan_8         | 25748654.00 | cop[tikv] | table:std_mediafileindex                                                          | keep order:false                                                                                                                                                                                  |
+----------------------------------+-------------+-----------+-----------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
6 rows in set (0.00 sec)

explain analyze才会执行sql,是真实执行计划

使用explain analyze 查看:
1、使用变量的执行计划
2、直接使用SQL查询的执行计划

使用force index执行超100秒都没执行完,只能kill掉,不加强制索引的情况如下:



mysql>begin;
Query OK, 0 rows affected (0.00 sec)

mysql>set @objectID=202915210;
Query OK, 0 rows affected (0.00 sec)

mysql>set @channel=0;
Query OK, 0 rows affected (0.00 sec)

mysql>set @beginTime=1638140720;
Query OK, 0 rows affected (0.00 sec)

mysql>explain analyze update std_mediafileindex set storageType=storageType-1,updateTime=now() where objectID=@objectID and channel=@channel and storageType in(1,3) and beginTime<@beginTime;  

+-------------------------------+-------------+----------+-----------+--------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------+------+
| id                            | estRows     | actRows  | task      | access object            | execution info                                                                                                                                                                                                                                                                          | operator info                                                                                                                                                                                     | memory                | disk |
+-------------------------------+-------------+----------+-----------+--------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------+------+
| Update_5                      | N/A         | 0        | root      |                          | time:20.232192665s, loops:1                                                                                                                                                                                                                                                             | N/A                                                                                                                                                                                               | 0 Bytes               | N/A  |
| └─SelectLock_7                | 6614944.22  | 0        | root      |                          | time:20.232172239s, loops:1                                                                                                                                                                                                                                                             | for update                                                                                                                                                                                        | N/A                   | N/A  |
|   └─Selection_8               | 6614944.22  | 0        | root      |                          | time:20.232163258s, loops:1                                                                                                                                                                                                                                                             | eq(db_v7.std_mediafileindex.channel, getvar("channel")), eq(db_v7.std_mediafileindex.objectid, getvar("objectid")), lt(db_v7.std_mediafileindex.begintime, getvar("begintime"))                   | 500.2890625 KB        | N/A  |
|     └─TableReader_11          | 8268680.28  | 7994271  | root      |                          | time:18.203074694s, loops:7808, cop_task: {num: 121, max: 11.901846549s, min: 86.35336ms, avg: 3.118023612s, p95: 8.96036372s, max_proc_keys: 347250, p95_proc_keys: 290815, tot_proc: 2m2.294s, tot_wait: 2.991s, rpc_num: 131, rpc_time: 6m17.276525192s, copr_cache_hit_ratio: 0.00} | data:Selection_10                                                                                                                                                                                 | 113.95494270324707 MB | N/A  |
|       └─Selection_10          | 8268680.28  | 7994271  | cop[tikv] |                          | time:0ns, loops:0, tikv_task:{proc max:2.091s, min:75ms, p80:1.121s, p95:1.485s, iters:26226, tasks:121}                                                                                                                                                                                | in(db_v7.std_mediafileindex.storagetype, 1, 3)                                                                                                                                                    | N/A                   | N/A  |
|         └─TableFullScan_9     | 26293268.00 | 26292868 | cop[tikv] | table:std_mediafileindex | time:0ns, loops:0, tikv_task:{proc max:2.049s, min:75ms, p80:1.106s, p95:1.464s, iters:26226, tasks:121}                                                                                                                                                                                | keep order:false                                                                                                                                                                                  | N/A                   | N/A  |
+-------------------------------+-------------+----------+-----------+--------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------+------+
6 rows in set (20.24 sec)

mysql>
mysql>rollback;
Query OK, 0 rows affected (0.00 sec)