【需求涉及的问题场景】
通过cdc做集群之间高可用，在使用sync_diff_inspector做上下游集群数据全量对比时，如果大表不是聚簇索引表，那么可能会导致集群性能抖动，影响其它业务。
【期望的需求行为】
希望针对非聚簇索引表进行优化，例如使用_tidb_rowid伪列进行拆分（但chunk划分的依据较难判断），或者退而求其次，强制走索引且利用hint将聚合进行下推( USE INDEX(xxx),AGG_TO_COP() )，尽量将运算发生在tikv上。
【背景信息】
sync_diff_inspector 做表数据对比的原理是将所有数据切割成多个chunk，每次计算一个chunk的所有数据行的crc32校验值（即checksum），如果这个chunk的checksum一致，则这批记录一致，直到对比完整个表。这里有两个点：1、切割的依据是按照show stats_buckets的Bound来判断切割范围的。2、切割字段的选择是优先选择主键，然后唯一索引，然后按照值大小：COUNT(DISTINCT a)/COUNT(*)来判定用哪个索引来拆分表为多个chunk。
获取chunk的checksum值的语句为：
select count(*),bit_xor(crc32(...)) as checksum from t where col1 > 'a' and col1 <='b'
对如下几种情况进行分析：
-
当表没有主键时，优化器根据执行成本可能会选择：
1）全表扫描，可能会导致tikv大量扫描，占用大量CPU；
2）索引回表，原本bit_xor,crc32等函数都可以下推到tikv执行，因为索引回表且优化器评估聚合不下推可能会导致该chunk的记录都返回给tidb-server来计算bit_xor和crc32，主要是大量的数据搬运可能会导致tikv-tidb之间grpc拥堵，导致集群TP业务抖动。
3）索引回表且在tikv层发生聚合，此种情况是相对最优情况，但是索引根据_tidb_rowid回表查询回发生大量的seek操作，如果索引顺序和_tidb_rowid顺序不一致，那么无法优化成next操作，导致效率较低。 -
当表有主键，但表是非聚簇索引表，那么该主键还是二级索引，还是会发生类似于第1中情况。
-
当表有主键，且表是聚簇索引表，那么sync_diff_inspector总是会优先选择主键做chunk的范围划分，因为主键是一级索引，因此对于范围扫描都会走table range scan，因为没有索引回表的问题且不会发生全表扫描，因此不但可以让bit_xor,crc32函数下推到tikv运算而且还不会发生全表扫描，因此效率最优。
综上，聚簇索引表在做全表数据对比时总是高效且对系统影响不大（平稳）。非聚簇索引表可能会导致集群性能抖动，因此对于非聚簇索引表建议采用_tidb_rowid伪列来进行切割，但是Bound的选择是另外一个挑战（1、对于自增或者默认情况根据最大最小值均匀划分；2、对于做了打散的能否根据表的region的start_key,stop_key转换成对应的_tidb_rowid来框定范围呢？）
下面分别对聚簇索引表和非聚簇索引表做相关测试：
--聚簇索引表
Create Table: CREATE TABLE `orders` (
`O_ORDERKEY` bigint(20) NOT NULL,
`O_CUSTKEY` bigint(20) NOT NULL,
`O_ORDERSTATUS` char(1) NOT NULL,
`O_TOTALPRICE` decimal(15,2) NOT NULL,
`O_ORDERDATE` date NOT NULL,
`O_ORDERPRIORITY` char(15) NOT NULL,
`O_CLERK` char(15) NOT NULL,
`O_SHIPPRIORITY` bigint(20) NOT NULL,
`O_COMMENT` varchar(79) NOT NULL,
PRIMARY KEY (`O_ORDERKEY`) /*T![clustered_index] CLUSTERED */
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
--非聚簇索引表
CREATE TABLE `orders_bak` (
`O_ORDERKEY` bigint(20) NOT NULL,
`O_CUSTKEY` bigint(20) NOT NULL,
`O_ORDERSTATUS` char(1) NOT NULL,
`O_TOTALPRICE` decimal(15,2) NOT NULL,
`O_ORDERDATE` date NOT NULL,
`O_ORDERPRIORITY` char(15) NOT NULL,
`O_CLERK` char(15) NOT NULL,
`O_SHIPPRIORITY` bigint(20) NOT NULL,
`O_COMMENT` varchar(79) NOT NULL,
PRIMARY KEY (`O_ORDERKEY`) /*T![clustered_index] NONCLUSTERED */
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
针对聚簇索引表获取chunk的checksum校验值的语句和执行计划：
SELECT
COUNT(*) AS CNT,
BIT_XOR(
CAST(
CRC32(
CONCAT_WS(
',',
`O_ORDERKEY`,
`O_CUSTKEY`,
`O_ORDERSTATUS`,
`O_TOTALPRICE`,
`O_ORDERDATE`,
`O_ORDERPRIORITY`,
`O_CLERK`,
`O_SHIPPRIORITY`,
`O_COMMENT`,
CONCAT(
ISNULL(`O_ORDERKEY`),
ISNULL(`O_CUSTKEY`),
ISNULL(`O_ORDERSTATUS`),
ISNULL(`O_TOTALPRICE`),
ISNULL(`O_ORDERDATE`),
ISNULL(`O_ORDERPRIORITY`),
ISNULL(`O_CLERK`),
ISNULL(`O_SHIPPRIORITY`),
ISNULL(`O_COMMENT`)
)
)
) AS UNSIGNED
)
) AS CHECKSUM
FROM
`tpch10`.`orders`
WHERE
(
(
((`O_ORDERKEY` > '8833953'))
AND ((`O_ORDERKEY` <= '9668836'))
)
AND (TRUE)
);
--执行计划：
+-----------------------------+-----------+---------+-----------+---------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+------+
| id | estRows | actRows | task | access object | execution info | operator info | memory | disk |
+-----------------------------+-----------+---------+-----------+---------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+------+
| HashAgg_12 | 1.00 | 1 | root | | time:516.3µs, loops:2, RU:0.475597, partial_worker:{wall_time:496.366µs, concurrency:5, task_num:1, tot_wait:1.966037ms, tot_exec:4.041µs, tot_time:1.973846ms, max:397.408µs, p95:397.408µs}, final_worker:{wall_time:0s, concurrency:5, task_num:1, tot_wait:2.083908ms, tot_exec:5.721µs, tot_time:2.091214ms, max:424.129µs, p95:424.129µs} | funcs:count(Column#12)->Column#10, funcs:bit_xor(Column#13)->Column#11 | 13.6 KB | N/A |
| └─TableReader_13 | 1.00 | 1 | root | | time:399.1µs, loops:2, cop_task: {num: 1, max: 398.2µs, proc_keys: 0, tot_proc: 1.79µs, tot_wait: 59.6µs, rpc_num: 1, rpc_time: 382.4µs, copr_cache_hit_ratio: 1.00, build_task_duration: 9.15µs, max_distsql_concurrency: 1} | data:HashAgg_6 | 275 Bytes | N/A |
| └─HashAgg_6 | 1.00 | 1 | cop[tikv] | | tikv_task:{time:209ms, loops:204}, scan_detail: {get_snapshot_time: 16.7µs, rocksdb: {block: {}}} | funcs:count(1)->Column#12, funcs:bit_xor(cast(crc32(concat_ws(",", cast(tpch10.orders.o_orderkey, var_string(20)), cast(tpch10.orders.o_custkey, var_string(20)), tpch10.orders.o_orderstatus, cast(tpch10.orders.o_totalprice, var_string(18)), cast(tpch10.orders.o_orderdate, var_string(10)), tpch10.orders.o_orderpriority, tpch10.orders.o_clerk, cast(tpch10.orders.o_shippriority, var_string(20)), tpch10.orders.o_comment, "000000000")), bigint(22) UNSIGNED BINARY))->Column#13 | N/A | N/A |
| └─TableRangeScan_11 | 203152.59 | 208723 | cop[tikv] | table:orders | tikv_task:{time:73ms, loops:204} | range:(8833953,9668836], keep order:false | N/A | N/A |
+-----------------------------+-----------+---------+-----------+---------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+------+
4 rows in set (0.00 sec)
可以看到其走的table range scan且函数均下推到tikv中执行，返回给tidb-server的只有聚合后的几行记录（这里只有一个region是一行记录）。
针对非聚簇索引表获取chunk的checksum校验值语句和执行计划：
SELECT
COUNT(*) AS CNT,
BIT_XOR(
CAST(
CRC32(
CONCAT_WS(
',',
`O_ORDERKEY`,
`O_CUSTKEY`,
`O_ORDERSTATUS`,
`O_TOTALPRICE`,
`O_ORDERDATE`,
`O_ORDERPRIORITY`,
`O_CLERK`,
`O_SHIPPRIORITY`,
`O_COMMENT`,
CONCAT(
ISNULL(`O_ORDERKEY`),
ISNULL(`O_CUSTKEY`),
ISNULL(`O_ORDERSTATUS`),
ISNULL(`O_TOTALPRICE`),
ISNULL(`O_ORDERDATE`),
ISNULL(`O_ORDERPRIORITY`),
ISNULL(`O_CLERK`),
ISNULL(`O_SHIPPRIORITY`),
ISNULL(`O_COMMENT`)
)
)
) AS UNSIGNED
)
) AS CHECKSUM
FROM
`tpch10`.`orders_bak`
WHERE
(
(
((`O_ORDERKEY` > '8833953'))
AND ((`O_ORDERKEY` <= '9668836'))
)
AND (TRUE)
);
--其执行计划：
+------------------------------+-------------+----------+-----------+------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+------+
| id | estRows | actRows | task | access object | execution info | operator info | memory | disk |
+------------------------------+-------------+----------+-----------+------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+------+
| HashAgg_13 | 1.00 | 1 | root | | time:3.54s, loops:2, RU:35679.746372, partial_worker:{wall_time:3.537738662s, concurrency:5, task_num:1, tot_wait:17.68843555s, tot_exec:3.767µs, tot_time:17.688443423s, max:3.537694388s, p95:3.537694388s}, final_worker:{wall_time:0s, concurrency:5, task_num:1, tot_wait:17.688526743s, tot_exec:8.012µs, tot_time:17.688535975s, max:3.53771454s, p95:3.53771454s} | funcs:count(Column#13)->Column#11, funcs:bit_xor(Column#14)->Column#12 | 13.6 KB | N/A |
| └─TableReader_14 | 1.00 | 1 | root | | time:3.54s, loops:2, cop_task: {num: 2, max: 3.54s, min: 2.55s, avg: 3.04s, p95: 3.54s, max_proc_keys: 7939404, p95_proc_keys: 7939404, tot_proc: 6.07s, tot_wait: 129.2µs, rpc_num: 2, rpc_time: 6.09s, copr_cache_hit_ratio: 0.00, build_task_duration: 12.3µs, max_distsql_concurrency: 2} | data:HashAgg_6 | 436 Bytes | N/A |
| └─HashAgg_6 | 1.00 | 1 | cop[tikv] | | tikv_task:{proc max:3.53s, min:2.55s, avg: 3.04s, p80:3.53s, p95:3.53s, iters:13561, tasks:2}, scan_detail: {total_process_keys: 13884864, total_process_keys_size: 2205608093, total_keys: 13884866, get_snapshot_time: 48.3µs, rocksdb: {key_skipped_count: 13884864, block: {cache_hit_count: 46859, read_count: 26392, read_byte: 507.9 MB, read_time: 183.2ms}}} | funcs:count(1)->Column#13, funcs:bit_xor(cast(crc32(concat_ws(",", cast(tpch10.orders_bak.o_orderkey, var_string(20)), cast(tpch10.orders_bak.o_custkey, var_string(20)), tpch10.orders_bak.o_orderstatus, cast(tpch10.orders_bak.o_totalprice, var_string(18)), cast(tpch10.orders_bak.o_orderdate, var_string(10)), tpch10.orders_bak.o_orderpriority, tpch10.orders_bak.o_clerk, cast(tpch10.orders_bak.o_shippriority, var_string(20)), tpch10.orders_bak.o_comment, "000000000")), bigint(22) UNSIGNED BINARY))->Column#14 | N/A | N/A |
| └─Selection_12 | 258213.41 | 208723 | cop[tikv] | | tikv_task:{proc max:3.53s, min:2.41s, avg: 2.97s, p80:3.53s, p95:3.53s, iters:13561, tasks:2} | gt(tpch10.orders_bak.o_orderkey, 8833953), le(tpch10.orders_bak.o_orderkey, 9668836) | N/A | N/A |
| └─TableFullScan_11 | 17139728.00 | 13884864 | cop[tikv] | table:orders_bak | tikv_task:{proc max:3.42s, min:2.33s, avg: 2.87s, p80:3.42s, p95:3.42s, iters:13561, tasks:2} | keep order:false | N/A | N/A |
+------------------------------+-------------+----------+-----------+------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+------+
5 rows in set (3.54 sec)
--让其强制走主键索引
+----------------------------------+-----------+---------+-----------+---------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+------+
| id | estRows | actRows | task | access object | execution info | operator info | memory | disk |
+----------------------------------+-----------+---------+-----------+---------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+------+
| HashAgg_13 | 1.00 | 1 | root | | time:83.9ms, loops:2, RU:753.270397, partial_worker:{wall_time:83.911166ms, concurrency:5, task_num:1, tot_wait:419.244414ms, tot_exec:14.561µs, tot_time:419.272395ms, max:83.867643ms, p95:83.867643ms}, final_worker:{wall_time:83.924262ms, concurrency:5, task_num:1, tot_wait:419.433702ms, tot_exec:26.633µs, tot_time:419.463128ms, max:83.90034ms, p95:83.90034ms} | funcs:count(Column#14)->Column#11, funcs:bit_xor(Column#15)->Column#12 | 13.6 KB | N/A |
| └─IndexLookUp_14 | 1.00 | 14 | root | | time:83.8ms, loops:2, index_task: {total_time: 61.1ms, fetch_handle: 59.6ms, build: 17.2µs, wait: 1.41ms}, table_task: {total_time: 245.9ms, num: 14, concurrency: 5}, next: {wait_index: 1.99ms, wait_table_lookup_build: 88.2µs, wait_table_lookup_resp: 81.7ms} | | 2.46 MB | N/A |
| ├─IndexRangeScan_11(Build) | 258213.41 | 208723 | cop[tikv] | table:orders_bak, index:PRIMARY(O_ORDERKEY) | time:54.5ms, loops:207, cop_task: {num: 12, max: 13.9ms, min: 491.5µs, avg: 4.86ms, p95: 13.9ms, max_proc_keys: 50144, p95_proc_keys: 50144, tot_proc: 51.6ms, tot_wait: 707.1µs, rpc_num: 12, rpc_time: 58.2ms, copr_cache_hit_ratio: 0.00, build_task_duration: 18.3µs, max_distsql_concurrency: 1}, tikv_task:{proc max:13ms, min:0s, avg: 4.08ms, p80:9ms, p95:13ms, iters:251, tasks:12}, scan_detail: {total_process_keys: 208723, total_process_keys_size: 9183812, total_keys: 208735, get_snapshot_time: 166.3µs, rocksdb: {key_skipped_count: 208723, block: {cache_hit_count: 23, read_count: 303, read_byte: 526.5 KB, read_time: 487.9µs}}} | range:(8833953,9668836], keep order:false | N/A | N/A |
| └─HashAgg_7(Probe) | 1.00 | 14 | cop[tikv] | | time:240.9ms, loops:28, cop_task: {num: 14, max: 25.2ms, min: 2.24ms, avg: 17.1ms, p95: 25.2ms, max_proc_keys: 20480, p95_proc_keys: 20480, tot_proc: 233.3ms, tot_wait: 761.5µs, rpc_num: 14, rpc_time: 239.8ms, copr_cache_hit_ratio: 0.00, build_task_duration: 234µs, max_distsql_concurrency: 1}, tikv_task:{proc max:25ms, min:1ms, avg: 16.6ms, p80:23ms, p95:25ms, iters:210, tasks:14}, scan_detail: {total_process_keys: 208723, total_process_keys_size: 33148172, total_keys: 208737, get_snapshot_time: 204µs, rocksdb: {key_skipped_count: 208723, block: {cache_hit_count: 1128}}} | funcs:count(1)->Column#14, funcs:bit_xor(cast(crc32(concat_ws(",", cast(tpch10.orders_bak.o_orderkey, var_string(20)), cast(tpch10.orders_bak.o_custkey, var_string(20)), tpch10.orders_bak.o_orderstatus, cast(tpch10.orders_bak.o_totalprice, var_string(18)), cast(tpch10.orders_bak.o_orderdate, var_string(10)), tpch10.orders_bak.o_orderpriority, tpch10.orders_bak.o_clerk, cast(tpch10.orders_bak.o_shippriority, var_string(20)), tpch10.orders_bak.o_comment, "000000000")), bigint(22) UNSIGNED BINARY))->Column#15 | N/A | N/A |
| └─TableRowIDScan_12 | 258213.41 | 208723 | cop[tikv] | table:orders_bak | tikv_task:{proc max:17ms, min:0s, avg: 6.29ms, p80:9ms, p95:17ms, iters:210, tasks:14} | keep order:false | N/A | N/A |
+----------------------------------+-----------+---------+-----------+---------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+------+
5 rows in set (0.09 sec)
可以看到默认情况下因为选择范围较大导致发生了全表扫描（我已经对orders和orders_bak表做了统计信息搜集），在强制指定让其走主键索引后通过主键回表查找对应的记录，但需要把符合条件记录都搬到tidb-server上进行计算，在并发较多的情况下会影响集群的稳定性。
