迁移完数据后，使用sync-diff-inspector校验数据问题请教

变又未变 · 2021 年5 月 7 日 06:13

为提高效率，请提供以下信息，问题描述清晰能够更快得到解决：

【TiDB 版本】
v4.0.10
【问题描述】
使用dumpling从mysql中导出10g的数据，使用dumpling导入到tidb数据库中使用sync-diff-inspector工具进行数据校验，1.5g花费了30min左右，但是10g的数据，花了27h27min左右，这个时间太长了吧，导致这么长的原因是什么呢？以及可以调整什么参数缩短时间呢？

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

来了老弟 · 2021 年5 月 7 日 12:36

log 辛苦上传我们看下，看下是否存在大表

变又未变 · 2021 年5 月 8 日 03:19

好的，辛苦了，后来有人又重新测试了一下，日志被覆盖了，我重新跑一下。跑完后，我把日志发一下

来了老弟 · 2021 年5 月 8 日 03:22

辛苦~

变又未变 · 2021 年5 月 11 日 01:14

你好，这次跑完diff，10g花了20h13min左右，还是有点长。日志我正在上传，稍等。

变又未变 · 2021 年5 月 11 日 01:19

日志连接，辛苦你帮忙看看：
链接：https://pan.baidu.com/s/15a79BT1EyYpiuPgsHIvu1w
提取码：1h17

QBin · 2021 年5 月 11 日 02:53

请给一下 show create table `tpcc10g`.`bmsql_customer`
给一下这块下游 TiDB 的执行计划：

explain analyze select BIT_XOR(CAST(CRC32(CONCAT_WS(',', c_w_id, CONCAT(c_w_id)))AS UNSIGNED)) AS checksum  from `tpcc10g`.`bmsql_customer` where 
(((`c_w_id` > '1') OR (`c_w_id` = '1' AND `c_d_id` > '2') OR (`c_w_id` = '1' AND `c_d_id` = '2' AND `c_id` > '632')) AND ((`c_w_id` < '1') OR (`c_w_id` = '1' AND `c_d_id` < '2') OR (`c_w_id` = '1' AND `c_d_id` = '2' AND `c_id` <= '736')) AND TRUE)

变又未变 · 2021 年5 月 11 日 05:39

MySQL [tpcc10g]> show create table bmsql_customer;
±---------------±--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
±---------------±--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| bmsql_customer | CREATE TABLE bmsql_customer (
c_w_id int(11) NOT NULL,
c_d_id int(11) NOT NULL,
c_id int(11) NOT NULL,
c_discount decimal(4,4) DEFAULT NULL,
c_credit char(2) DEFAULT NULL,
c_last varchar(16) DEFAULT NULL,
c_first varchar(16) DEFAULT NULL,
c_credit_lim decimal(12,2) DEFAULT NULL,
c_balance decimal(12,2) DEFAULT NULL,
c_ytd_payment decimal(12,2) DEFAULT NULL,
c_payment_cnt int(11) DEFAULT NULL,
c_delivery_cnt int(11) DEFAULT NULL,
c_street_1 varchar(20) DEFAULT NULL,
c_street_2 varchar(20) DEFAULT NULL,
c_city varchar(20) DEFAULT NULL,
c_state char(2) DEFAULT NULL,
c_zip char(9) DEFAULT NULL,
c_phone char(16) DEFAULT NULL,
c_since timestamp NULL DEFAULT NULL,
c_middle char(2) DEFAULT NULL,
c_data varchar(500) DEFAULT NULL,
PRIMARY KEY (c_w_id,c_d_id,c_id),
KEY bmsql_customer_idx1 (c_w_id,c_d_id,c_last,c_first)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin |
±---------------±--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

变又未变 · 2021 年5 月 11 日 05:39

MySQL [tpcc10g]> explain analyze select BIT_XOR(CAST(CRC32(CONCAT_WS(’,’, c_w_id, CONCAT(c_w_id)))AS UNSIGNED)) AS checksum from tpcc10g.bmsql_customer where
-> (((c_w_id > ‘1’) OR (c_w_id = ‘1’ AND c_d_id > ‘2’) OR (c_w_id = ‘1’ AND c_d_id = ‘2’ AND c_id > ‘632’)) AND ((c_w_id < ‘1’) OR (c_w_id = ‘1’ AND c_d_id < ‘2’) OR (c_w_id = ‘1’ AND c_d_id = ‘2’ AND c_id <= ‘736’)) AND TRUE);
±------------------------------±-----------±--------±----------±----------------------------------------------------------±------------------------------------------------------------------------------------------------------------------------------------------------±---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------±----------±-----+
| id | estRows | actRows | task | access object | execution info | operator info | memory | disk |
±------------------------------±-----------±--------±----------±----------------------------------------------------------±------------------------------------------------------------------------------------------------------------------------------------------------±---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------±----------±-----+
| StreamAgg_20 | 1.00 | 1 | root | | time:75.987546ms, loops:2 | funcs:bit_xor(Column#25)->Column#23 | 372 Bytes | N/A |
| └─IndexReader_21 | 1.00 | 1 | root | | time:75.979174ms, loops:2, cop_task: {num: 1, max:75.906288ms, proc_keys: 30000, rpc_num: 1, rpc_time: 75.862949ms, copr_cache_hit_ratio: 0.00} | index:StreamAgg_9 | 251 Bytes | N/A |
| └─StreamAgg_9 | 1.00 | 1 | cop[tikv] | | time:0ns, loops:0, tikv_task:{time:74ms, loops:30} | funcs:bit_xor(cast(crc32(concat_ws(",", cast(tpcc10g.bmsql_customer.c_w_id), concat(cast(tpcc10g.bmsql_customer.c_w_id))))))->Column#25 | N/A | N/A |
| └─Selection_19 | 3360000.00 | 104 | cop[tikv] | | time:0ns, loops:0, tikv_task:{time:73ms, loops:30} | or(or(gt(tpcc10g.bmsql_customer.c_w_id, 1), and(eq(tpcc10g.bmsql_customer.c_w_id, 1), gt(tpcc10g.bmsql_customer.c_d_id, 2))), and(and(eq(tpcc10g.bmsql_customer.c_w_id, 1), eq(tpcc10g.bmsql_customer.c_d_id, 2)), gt(tpcc10g.bmsql_customer.c_id, 632))), or(or(lt(tpcc10g.bmsql_customer.c_w_id, 1), and(eq(tpcc10g.bmsql_customer.c_w_id, 1), lt(tpcc10g.bmsql_customer.c_d_id, 2))), and(and(eq(tpcc10g.bmsql_customer.c_w_id, 1), eq(tpcc10g.bmsql_customer.c_d_id, 2)), le(tpcc10g.bmsql_customer.c_id, 736))) | N/A | N/A |
| └─IndexRangeScan_18 | 4200000.00 | 30000 | cop[tikv] | table:bmsql_customer, index:PRIMARY(c_w_id, c_d_id, c_id) | time:0ns, loops:0, tikv_task:{time:69ms, loops:30} | range:[1,1], keep order:false | N/A | N/A |
±------------------------------±-----------±--------±----------±----------------------------------------------------------±------------------------------------------------------------------------------------------------------------------------------------------------±---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------±----------±-----+
5 rows in set (0.08 sec)

QBin · 2021 年5 月 11 日 08:18

看起来当前查询和之前查询时候的 TiDB 的相应速度不一样。建议可以先检查一下 slowlog 看看当时 synce-diff 在运行中的慢 SQL ，分析一下当时慢的情况。从之前 sync-diff 的日志看，每一个 where 条件的查询都比较慢，基本 5s 以上
也可以看一下当时 TiDB 的 workload 是否非常高。也可以结合一下读慢的排查文档进行进一步的排查。
读性能慢-总纲
辛苦再给一下这个 explain analyze 的结果：

explain analyze select BIT_XOR(CAST(CRC32(CONCAT_WS(',', c_w_id,c_d_id,c_id,c_discount,c_credit,c_last,c_first,c_credit_lim,c_balance,c_ytd_payment,c_payment_cnt,c_delivery_cnt,c_street_1,c_street_2,c_city,c_state,c_zip,c_phone,c_since,c_middle,c_data CONCAT(c_w_id,c_d_id,c_id,c_discount,c_credit,c_last,c_first,c_credit_lim,c_balance,c_ytd_payment,c_payment_cnt,c_delivery_cnt,c_street_1,c_street_2,c_city,c_state,c_zip,c_phone,c_since,c_middle,c_data)))AS UNSIGNED)) AS checksum  
from `tpcc10g`.`bmsql_customer` where 
(((`c_w_id` > '1') OR (`c_w_id` = '1' AND `c_d_id` > '2') OR (`c_w_id` = '1' AND `c_d_id` = '2' AND `c_id` > '632')) AND ((`c_w_id` < '1') OR (`c_w_id` = '1' AND `c_d_id` < '2') OR (`c_w_id` = '1' AND `c_d_id` = '2' AND `c_id` <= '736')) AND TRUE)

变又未变 · 2021 年5 月 11 日 08:28

好的，我在跑diff时，cpu是利用率时不时回到90%多
这个指令好像有问题，我自己修改了一下，你看一下是不是你要的：
MySQL [tpcc10g]> explain analyze select BIT_XOR(CAST(CRC32(CONCAT_WS(’,’, c_w_id,c_d_id,c_id,c_discount,c_credit,c_last,c_first,c_credit_lim,c_balance,c_ytd_payment,c_payment_cnt,c_delivery_cnt,c_street_1,c_street_2,c_city,c_state,c_zip,c_phone,c_since,c_middle,c_data,CONCAT(c_w_id,c_d_id,c_id,c_discount,c_credit,c_last,c_first,c_credit_lim,c_balance,c_ytd_payment,c_payment_cnt,c_delivery_cnt,c_street_1,c_street_2,c_city,c_state,c_zip,c_phone,c_since,c_middle,c_data)))AS UNSIGNED)) AS checksum from bmsql_customer where (((c_w_id > ‘1’) OR (c_w_id = ‘1’ AND c_d_id > ‘2’) OR (c_w_id = ‘1’ AND c_d_id = ‘2’ AND c_id > ‘632’)) AND ((c_w_id < ‘1’) OR (c_w_id = ‘1’ AND c_d_id < ‘2’) OR (c_w_id = ‘1’ AND c_d_id = ‘2’ AND c_id <= ‘736’)) AND TRUE);
±-----------------------------±-----------±--------±----------±---------------------±------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------±--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------±----------±-----+
| id | estRows | actRows | task | access object | execution info | operator info | memory | disk |
±-----------------------------±-----------±--------±----------±---------------------±------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------±--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------±----------±-----+
| StreamAgg_36 | 1.00 | 1 | root | | time:795.123108ms, loops:2 | funcs:bit_xor(Column#30)->Column#23 | 372 Bytes | N/A |
| └─TableReader_37 | 1.00 | 28 | root | | time:795.108823ms, loops:2, cop_task: {num: 28, max: 549.362472ms, min: 128.971805ms, avg: 357.661512ms, p95: 444.007678ms, max_proc_keys: 194418, p95_proc_keys: 189523, tot_proc: 9.963s, tot_wait: 15ms, rpc_num: 28, rpc_time: 10.013634302s, copr_cache_hit_ratio: 0.00} | data:StreamAgg_9 | 316 Bytes | N/A |
| └─StreamAgg_9 | 1.00 | 28 | cop[tikv] | | time:0ns, loops:0, tikv_task:{proc max:547ms, min:127ms, p80:414ms, p95:442ms, iters:4112, tasks:28} | funcs:bit_xor(cast(crc32(concat_ws(",", cast(tpcc10g.bmsql_customer.c_w_id), cast(tpcc10g.bmsql_customer.c_d_id), cast(tpcc10g.bmsql_customer.c_id), cast(tpcc10g.bmsql_customer.c_discount), tpcc10g.bmsql_customer.c_credit, tpcc10g.bmsql_customer.c_last, tpcc10g.bmsql_customer.c_first, cast(tpcc10g.bmsql_customer.c_credit_lim), cast(tpcc10g.bmsql_customer.c_balance), cast(tpcc10g.bmsql_customer.c_ytd_payment), cast(tpcc10g.bmsql_customer.c_payment_cnt), cast(tpcc10g.bmsql_customer.c_delivery_cnt), tpcc10g.bmsql_customer.c_street_1, tpcc10g.bmsql_customer.c_street_2, tpcc10g.bmsql_customer.c_city, tpcc10g.bmsql_customer.c_state, tpcc10g.bmsql_customer.c_zip, tpcc10g.bmsql_customer.c_phone, cast(tpcc10g.bmsql_customer.c_since), tpcc10g.bmsql_customer.c_middle, tpcc10g.bmsql_customer.c_data, concat(cast(tpcc10g.bmsql_customer.c_w_id), cast(tpcc10g.bmsql_customer.c_d_id), cast(tpcc10g.bmsql_customer.c_id), cast(tpcc10g.bmsql_customer.c_discount), tpcc10g.bmsql_customer.c_credit, tpcc10g.bmsql_customer.c_last, tpcc10g.bmsql_customer.c_first, cast(tpcc10g.bmsql_customer.c_credit_lim), cast(tpcc10g.bmsql_customer.c_balance), cast(tpcc10g.bmsql_customer.c_ytd_payment), cast(tpcc10g.bmsql_customer.c_payment_cnt), cast(tpcc10g.bmsql_customer.c_delivery_cnt), tpcc10g.bmsql_customer.c_street_1, tpcc10g.bmsql_customer.c_street_2, tpcc10g.bmsql_customer.c_city, tpcc10g.bmsql_customer.c_state, tpcc10g.bmsql_customer.c_zip, tpcc10g.bmsql_customer.c_phone, cast(tpcc10g.bmsql_customer.c_since), tpcc10g.bmsql_customer.c_middle, tpcc10g.bmsql_customer.c_data)))))->Column#30 | N/A | N/A |
| └─Selection_35 | 3360000.00 | 104 | cop[tikv] | | time:0ns, loops:0, tikv_task:{proc max:544ms, min:126ms, p80:412ms, p95:442ms, iters:4112, tasks:28} | or(or(gt(tpcc10g.bmsql_customer.c_w_id, 1), and(eq(tpcc10g.bmsql_customer.c_w_id, 1), gt(tpcc10g.bmsql_customer.c_d_id, 2))), and(and(eq(tpcc10g.bmsql_customer.c_w_id, 1), eq(tpcc10g.bmsql_customer.c_d_id, 2)), gt(tpcc10g.bmsql_customer.c_id, 632))), or(or(lt(tpcc10g.bmsql_customer.c_w_id, 1), and(eq(tpcc10g.bmsql_customer.c_w_id, 1), lt(tpcc10g.bmsql_customer.c_d_id, 2))), and(and(eq(tpcc10g.bmsql_customer.c_w_id, 1), eq(tpcc10g.bmsql_customer.c_d_id, 2)), le(tpcc10g.bmsql_customer.c_id, 736))) | N/A | N/A |
| └─TableFullScan_34 | 4200000.00 | 4200000 | cop[tikv] | table:bmsql_customer | time:0ns, loops:0, tikv_task:{proc max:514ms, min:116ms, p80:388ms, p95:414ms, iters:4112, tasks:28} | keep order:false | N/A | N/A |
±-----------------------------±-----------±--------±----------±---------------------±------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------±--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------±----------±-----+
5 rows in set (0.80 sec)

QBin · 2021 年5 月 11 日 08:39

嗯。看起来这个 explain analyze 单跑也不慢。那可以先检查一下 slowlog 以及执行 synce-diff 时候 TiDB 的 workload

变又未变 · 2021 年5 月 11 日 08:44

监控中load：

变又未变 · 2021 年5 月 11 日 09:06

看了几个慢日志，这个查询时间都比较长：

变又未变 · 2021 年5 月 11 日 09:35

请教一下，我导完数据后，对表格analyze一下，会不会提升diff的速度？

北京大爷 · 2021 年5 月 11 日 11:43

diff 比对是用主键或者唯一索引进行 range block 比对。compaction 对 diff 有有帮助 analyze 是没有的

变又未变 · 2021 年5 月 11 日 11:49

好吧，谢谢，昨天diff的时候什么也木有做，怎么会那么慢的，看慢日志，监控，感觉也没有什么特别的地方。对了，官网上有个sync-diff的py脚本，这个脚本内部实际上也是使用的diff这个组件进行校验的是嘛？

QBin · 2021 年5 月 11 日 12:07

麻烦执行一下以下几个 SQL：

analyze table bmsql_customer;

explain analyze select BIT_XOR(CAST(CRC32(CONCAT_WS(',', c_w_id,c_d_id,c_id,c_discount,c_credit,c_last,c_first,c_credit_lim,c_balance,c_ytd_payment,c_payment_cnt,c_delivery_cnt,c_street_1,c_street_2,c_city,c_state,c_zip,c_phone,c_since,c_middle,c_data CONCAT(c_w_id,c_d_id,c_id,c_discount,c_credit,c_last,c_first,c_credit_lim,c_balance,c_ytd_payment,c_payment_cnt,c_delivery_cnt,c_street_1,c_street_2,c_city,c_state,c_zip,c_phone,c_since,c_middle,c_data)))AS UNSIGNED)) AS checksum  
from `bmsql_customer` where 
(((`c_w_id` > '1') OR (`c_w_id` = '1' AND `c_d_id` > '2') OR (`c_w_id` = '1' AND `c_d_id` = '2' AND `c_id` > '632')) AND ((`c_w_id` < '1') OR (`c_w_id` = '1' AND `c_d_id` < '2') OR (`c_w_id` = '1' AND `c_d_id` = '2' AND `c_id` <= '736')) AND TRUE) ;

explain analyze select BIT_XOR(CAST(CRC32(CONCAT_WS(',', c_w_id, CONCAT(c_w_id)))AS UNSIGNED)) AS checksum  from `tpcc10g`.`bmsql_customer` where 
(((`c_w_id` > '1') OR (`c_w_id` = '1' AND `c_d_id` > '2') OR (`c_w_id` = '1' AND `c_d_id` = '2' AND `c_id` > '632')) AND ((`c_w_id` < '1') OR (`c_w_id` = '1' AND `c_d_id` < '2') OR (`c_w_id` = '1' AND `c_d_id` = '2' AND `c_id` <= '736')) AND TRUE) ;

另外想确认一下这个表数据量没有发生变化是吧？

变又未变 · 2021 年5 月 12 日 01:18

你好，执行完是这个样子的，数据量没有变化，我导入后就直接跑diff脚本了，近期也没有人对这个库操作

MySQL [tpcc10g]> explain analyze select BIT_XOR(CAST(CRC32(CONCAT_WS(‘,’, c_w_id,c_d_id,c_id,c_discount,c_credit,c_last,c_first,c_credit_lim,c_balance,c_ytd_payment,c_payment_cnt,c_delivery_cnt,c_street_1,c_street_2,c_city,c_state,c_zip,c_phone,c_since,c_middle,c_data,CONCAT(c_w_id,c_d_id,c_id,c_discount,c_credit,c_last,c_first,c_credit_lim,c_balance,c_ytd_payment,c_payment_cnt,c_delivery_cnt,c_street_1,c_street_2,c_city,c_state,c_zip,c_phone,c_since,c_middle,c_data)))AS UNSIGNED)) AS checksum from bmsql_customer where (((c_w_id > ‘1’) OR (c_w_id = ‘1’ AND c_d_id > ‘2’) OR (c_w_id = ‘1’ AND c_d_id = ‘2’ AND c_id > ‘632’)) AND ((c_w_id < ‘1’) OR (c_w_id = ‘1’ AND c_d_id < ‘2’) OR (c_w_id = ‘1’ AND c_d_id = ‘2’ AND c_id <= ‘736’)) AND TRUE);
±-----------------------------±-----------±--------±----------±---------------------±------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------±--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------±----------±-----+
| id | estRows | actRows | task | access object | execution info | operator info | memory | disk |
±-----------------------------±-----------±--------±----------±---------------------±------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------±--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------±----------±-----+
| StreamAgg_36 | 1.00 | 1 | root | | time:1.102769251s, loops:2 | funcs:bit_xor(Column#30)->Column#23 | 372 Bytes | N/A |
| └─TableReader_37 | 1.00 | 28 | root | | time:1.10275466s, loops:2, cop_task: {num: 28, max: 644.467117ms, min: 198.610925ms, avg: 498.761109ms, p95: 618.189783ms, max_proc_keys: 194418, p95_proc_keys: 189523, tot_proc: 13.913s, tot_wait: 20ms, rpc_num: 28, rpc_time: 13.963979906s, copr_cache_hit_ratio: 0.00} | data:StreamAgg_9 | 316 Bytes | N/A |
| └─StreamAgg_9 | 1.00 | 28 | cop[tikv] | | time:0ns, loops:0, tikv_task:{proc max:642ms, min:197ms, p80:578ms, p95:615ms, iters:4112, tasks:28} | funcs:bit_xor(cast(crc32(concat_ws(“,”, cast(tpcc10g.bmsql_customer.c_w_id), cast(tpcc10g.bmsql_customer.c_d_id), cast(tpcc10g.bmsql_customer.c_id), cast(tpcc10g.bmsql_customer.c_discount), tpcc10g.bmsql_customer.c_credit, tpcc10g.bmsql_customer.c_last, tpcc10g.bmsql_customer.c_first, cast(tpcc10g.bmsql_customer.c_credit_lim), cast(tpcc10g.bmsql_customer.c_balance), cast(tpcc10g.bmsql_customer.c_ytd_payment), cast(tpcc10g.bmsql_customer.c_payment_cnt), cast(tpcc10g.bmsql_customer.c_delivery_cnt), tpcc10g.bmsql_customer.c_street_1, tpcc10g.bmsql_customer.c_street_2, tpcc10g.bmsql_customer.c_city, tpcc10g.bmsql_customer.c_state, tpcc10g.bmsql_customer.c_zip, tpcc10g.bmsql_customer.c_phone, cast(tpcc10g.bmsql_customer.c_since), tpcc10g.bmsql_customer.c_middle, tpcc10g.bmsql_customer.c_data, concat(cast(tpcc10g.bmsql_customer.c_w_id), cast(tpcc10g.bmsql_customer.c_d_id), cast(tpcc10g.bmsql_customer.c_id), cast(tpcc10g.bmsql_customer.c_discount), tpcc10g.bmsql_customer.c_credit, tpcc10g.bmsql_customer.c_last, tpcc10g.bmsql_customer.c_first, cast(tpcc10g.bmsql_customer.c_credit_lim), cast(tpcc10g.bmsql_customer.c_balance), cast(tpcc10g.bmsql_customer.c_ytd_payment), cast(tpcc10g.bmsql_customer.c_payment_cnt), cast(tpcc10g.bmsql_customer.c_delivery_cnt), tpcc10g.bmsql_customer.c_street_1, tpcc10g.bmsql_customer.c_street_2, tpcc10g.bmsql_customer.c_city, tpcc10g.bmsql_customer.c_state, tpcc10g.bmsql_customer.c_zip, tpcc10g.bmsql_customer.c_phone, cast(tpcc10g.bmsql_customer.c_since), tpcc10g.bmsql_customer.c_middle, tpcc10g.bmsql_customer.c_data)))))->Column#30 | N/A | N/A |
| └─Selection_35 | 3360000.00 | 104 | cop[tikv] | | time:0ns, loops:0, tikv_task:{proc max:641ms, min:194ms, p80:578ms, p95:614ms, iters:4112, tasks:28} | or(or(gt(tpcc10g.bmsql_customer.c_w_id, 1), and(eq(tpcc10g.bmsql_customer.c_w_id, 1), gt(tpcc10g.bmsql_customer.c_d_id, 2))), and(and(eq(tpcc10g.bmsql_customer.c_w_id, 1), eq(tpcc10g.bmsql_customer.c_d_id, 2)), gt(tpcc10g.bmsql_customer.c_id, 632))), or(or(lt(tpcc10g.bmsql_customer.c_w_id, 1), and(eq(tpcc10g.bmsql_customer.c_w_id, 1), lt(tpcc10g.bmsql_customer.c_d_id, 2))), and(and(eq(tpcc10g.bmsql_customer.c_w_id, 1), eq(tpcc10g.bmsql_customer.c_d_id, 2)), le(tpcc10g.bmsql_customer.c_id, 736))) | N/A | N/A |
| └─TableFullScan_34 | 4200000.00 | 4200000 | cop[tikv] | table:bmsql_customer | time:0ns, loops:0, tikv_task:{proc max:604ms, min:182ms, p80:550ms, p95:588ms, iters:4112, tasks:28} | keep order:false | N/A | N/A |
±-----------------------------±-----------±--------±----------±---------------------±------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------±--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------±----------±-----+
5 rows in set (1.10 sec)

变又未变 · 2021 年5 月 12 日 01:19

MySQL [tpcc10g]> explain analyze select BIT_XOR(CAST(CRC32(CONCAT_WS(’,’, c_w_id, CONCAT(c_w_id)))AS UNSIGNED)) AS checksum from bmsql_customer where (((c_w_id > ‘1’) OR (c_w_id = ‘1’ AND c_d_id > ‘2’) OR (c_w_id = ‘1’ AND c_d_id = ‘2’ AND c_id > ‘632’)) AND ((c_w_id < ‘1’) OR (c_w_id = ‘1’ AND c_d_id < ‘2’) OR (c_w_id = ‘1’ AND c_d_id = ‘2’ AND c_id <= ‘736’)) AND TRUE);
±------------------------------±-----------±--------±----------±----------------------------------------------------------±------------------------------------------------------------------------------------------------------------------------------------------------±---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------±----------±-----+
| id | estRows | actRows | task | access object | execution info | operator info | memory | disk |
±------------------------------±-----------±--------±----------±----------------------------------------------------------±------------------------------------------------------------------------------------------------------------------------------------------------±---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------±----------±-----+
| StreamAgg_20 | 1.00 | 1 | root | | time:23.415118ms, loops:2 | funcs:bit_xor(Column#25)->Column#23 | 372 Bytes | N/A |
| └─IndexReader_21 | 1.00 | 1 | root | | time:23.408011ms, loops:2, cop_task: {num: 1, max:23.379673ms, proc_keys: 30000, rpc_num: 1, rpc_time: 23.340253ms, copr_cache_hit_ratio: 0.00} | index:StreamAgg_9 | 253 Bytes | N/A |
| └─StreamAgg_9 | 1.00 | 1 | cop[tikv] | | time:0ns, loops:0, tikv_task:{time:22ms, loops:30} | funcs:bit_xor(cast(crc32(concat_ws(",", cast(tpcc10g.bmsql_customer.c_w_id), concat(cast(tpcc10g.bmsql_customer.c_w_id))))))->Column#25 | N/A | N/A |
| └─Selection_19 | 3360000.00 | 104 | cop[tikv] | | time:0ns, loops:0, tikv_task:{time:22ms, loops:30} | or(or(gt(tpcc10g.bmsql_customer.c_w_id, 1), and(eq(tpcc10g.bmsql_customer.c_w_id, 1), gt(tpcc10g.bmsql_customer.c_d_id, 2))), and(and(eq(tpcc10g.bmsql_customer.c_w_id, 1), eq(tpcc10g.bmsql_customer.c_d_id, 2)), gt(tpcc10g.bmsql_customer.c_id, 632))), or(or(lt(tpcc10g.bmsql_customer.c_w_id, 1), and(eq(tpcc10g.bmsql_customer.c_w_id, 1), lt(tpcc10g.bmsql_customer.c_d_id, 2))), and(and(eq(tpcc10g.bmsql_customer.c_w_id, 1), eq(tpcc10g.bmsql_customer.c_d_id, 2)), le(tpcc10g.bmsql_customer.c_id, 736))) | N/A | N/A |
| └─IndexRangeScan_18 | 4200000.00 | 30000 | cop[tikv] | table:bmsql_customer, index:PRIMARY(c_w_id, c_d_id, c_id) | time:0ns, loops:0, tikv_task:{time:18ms, loops:30} | range:[1,1], keep order:false | N/A | N/A |
±------------------------------±-----------±--------±----------±----------------------------------------------------------±------------------------------------------------------------------------------------------------------------------------------------------------±---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------±----------±-----+
5 rows in set (0.02 sec)