同一个sql执行两次结果不一致，两个结果来回变化

🪐 TiDB 技术问题 TiFlash
1

【 TiDB 使用环境】生产环境
【 TiDB 版本】
【复现路径】做过哪些操作出现的问题
执行sql：

image
image1463×462 56.9 KB

【遇到的问题：问题现象及影响】
两次查询返回数据量不一致。
【资源配置】
【附件：截图/日志/监控】

表是加在tiflash中的
root 11:05: [information_schema]> select * from TIFLASH_REPLICA;
±----------------±--------------------±---------±--------------±----------------±----------±---------+
| TABLE_SCHEMA | TABLE_NAME | TABLE_ID | REPLICA_COUNT | LOCATION_LABELS | AVAILABLE | PROGRESS |
±----------------±--------------------±---------±--------------±----------------±----------±---------+
| ad_common_datas | report_simple_day | 4056 | 2 | | 1 | 1 |

2

看一下sql的执行计划，看看是走的tiflash吗？
另外看你给表设了2个tiflash副本，但是能用的只有1个？

3

image
image1454×748 49.4 KB

是走了tiflash

4

加个 order by 试试

5

你给表设了2个tiflash副本，但是能用的只有1个？你有几个tiflash节点？

6

image
image1463×436 54.2 KB

加order by 也是一样的

7

3个tiflash节点

8

ALTER TABLE a SET tiflash replica 1;—把表的副本设置为1 试试，应该有1个副本有问题。

9
  1. 是不是还有业务数据在追加？
  2. 是不是数据还在同步？ 未同步完成？

所以数据上一直有变化…
slot 200006 和 200026 的数据没变化呢

10

没有在追加数据，因为查的是历史的时间。但几天之前有一个删除操作，看数据量是能查到之前删除的数据。

11

这个不是可用的副本数吧，是判断这个tiflash是否可用吧。

13

能否在 dashboard 里看下发生不一致时，sql 的执行计划里面，各个阶段返回的 actRows 具体行数

14

有个能是几个 tiflash 副本的数据不一致导致的，比如你做了删除之后，是否有些副本的数据没有一致？

15

这个如何查看和验证？

16 
	id                         	task     	estRows   	operator info                                                                                                                                                                                                                                                                                                                                                                                                                                                                             	actRows 	execution info                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    	memory 	disk
	Sort_6                     	root     	9.89      	ad_common_datas.report_simple_day.mediabuy_id, ad_common_datas.report_simple_day.slot_id                                                                                                                                                                                                                                                                                                                                                                                                  	4       	time:769.4ms, loops:2                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             	23.1 KB	0 Bytes
	└─Projection_8             	root     	9.89      	ad_common_datas.report_simple_day.mediabuy_id, Column#20, Column#21, Column#22, Column#23, Column#24, ad_common_datas.report_simple_day.slot_id                                                                                                                                                                                                                                                                                                                                           	4       	time:769.4ms, loops:4, Concurrency:OFF                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            	7.70 KB	N/A
	  └─HashAgg_24             	root     	9.89      	group by:ad_common_datas.report_simple_day.mediabuy_id, ad_common_datas.report_simple_day.slot_id, funcs:sum(Column#39)->Column#20, funcs:sum(Column#40)->Column#21, funcs:sum(Column#41)->Column#22, funcs:sum(Column#42)->Column#23, funcs:sum(Column#43)->Column#24, funcs:firstrow(ad_common_datas.report_simple_day.mediabuy_id)->ad_common_datas.report_simple_day.mediabuy_id, funcs:firstrow(ad_common_datas.report_simple_day.slot_id)->ad_common_datas.report_simple_day.slot_id	4       	time:769.4ms, loops:4, partial_worker:{wall_time:769.368829ms, concurrency:5, task_num:1, tot_wait:3.846366131s, tot_exec:46.163µs, tot_time:3.846425455s, max:769.343662ms, p95:769.343662ms}, final_worker:{wall_time:769.399186ms, concurrency:5, task_num:3, tot_wait:3.84666295s, tot_exec:86.654µs, tot_time:3.846753472s, max:769.360869ms, p95:769.360869ms}                                                                                                                                                                                                            	84.1 KB	N/A
	    └─IndexLookUp_25       	root     	9.89      	                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          	44      	time:769.3ms, loops:2, index_task: {total_time: 767.1ms, fetch_handle: 767ms, build: 2.55µs, wait: 5.7µs}, table_task: {total_time: 1.9ms, num: 1, concurrency: 5}                                                                                                                                                                                                                                                                                                                                                                                                              	19.4 KB	N/A
	      ├─Selection_23       	cop[tikv]	1240.74   	eq(ad_common_datas.report_simple_day.mediabuy_id, 715755)                                                                                                                                                                                                                                                                                                                                                                                                                                 	45      	time:767ms, loops:3, cop_task: {num: 85, max: 364.5ms, min: 946.6µs, avg: 116.1ms, p95: 253.1ms, max_proc_keys: 825224, p95_proc_keys: 410242, tot_proc: 9.75s, tot_wait: 91ms, rpc_num: 85, rpc_time: 9.86s, copr_cache_hit_ratio: 0.32}, tikv_task:{proc max:615ms, min:77ms, p80:302ms, p95:354ms, iters:25537, tasks:85}, scan_detail: {total_process_keys: 15092831, total_process_keys_size: 1886603875, total_keys: 17201768, rocksdb: {delete_skipped_count: 26085, key_skipped_count: 16944880, block: {cache_hit_count: 14504, read_count: 10080, read_byte: 59.0 MB}}}	N/A    	N/A
	      │ └─IndexRangeScan_21	cop[tikv]	1240738.82	table:report_simple_day, index:PRIMARY(day, player_id, campaign_id, mediabuy_id, creative_id, slot_id, province_id, city_id, tn), range:[2021-12-01,2023-05-16), keep order:false, stats:pseudo                                                                                                                                                                                                                                                                                           	25756566	tikv_task:{proc max:593ms, min:76ms, p80:294ms, p95:347ms, iters:25537, tasks:85}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 	N/A    	N/A
	      └─HashAgg_10         	cop[tikv]	9.89      	group by:ad_common_datas.report_simple_day.mediabuy_id, ad_common_datas.report_simple_day.slot_id, funcs:sum(ad_common_datas.report_simple_day.impressions)->Column#39, funcs:sum(ad_common_datas.report_simple_day.fisrt_impressions)->Column#40, funcs:sum(ad_common_datas.report_simple_day.mid_impressions)->Column#41, funcs:sum(ad_common_datas.report_simple_day.third_impressions)->Column#42, funcs:sum(ad_common_datas.report_simple_day.end_impressions)->Column#43            	44      	time:1.7ms, loops:2, cop_task: {num: 12, max: 1.61ms, min: 1.4ms, avg: 1.52ms, p95: 1.61ms, max_proc_keys: 5, p95_proc_keys: 5, tot_proc: 10ms, tot_wait: 10ms, rpc_num: 12, rpc_time: 18.1ms, copr_cache_hit_ratio: 0.00}, tikv_task:{proc max:1ms, min:0s, p80:1ms, p95:1ms, iters:12, tasks:12}, scan_detail: {total_process_keys: 45, total_process_keys_size: 5536, total_keys: 45, rocksdb: {delete_skipped_count: 0, key_skipped_count: 0, block: {cache_hit_count: 418, read_count: 4, read_byte: 205.0 KB}}}                                                             	N/A    	N/A
	        └─TableRowIDScan_22	cop[tikv]	1240.74   	table:report_simple_day, keep order:false, stats:pseudo                                                                                                                                                                                                                                                                                                                                                                                                                                   	45      	tikv_task:{proc max:1ms, min:0s, p80:1ms, p95:1ms, iters:12, tasks:12}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            	N/A    	N/A

	id                                    	task        	estRows 	operator info                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             	actRows	execution info                                                                                          	memory 	disk
	Sort_6                                	root        	1       	ad_common_datas.report_simple_day.mediabuy_id, ad_common_datas.report_simple_day.slot_id                                                                                                                                                                                                                                                                                                                                                                                                                                                                  	4      	time:54.3ms, loops:2                                                                                    	7.70 KB	0 Bytes
	└─Projection_8                        	root        	1       	ad_common_datas.report_simple_day.mediabuy_id, Column#20, Column#21, Column#22, Column#23, Column#24, ad_common_datas.report_simple_day.slot_id                                                                                                                                                                                                                                                                                                                                                                                                           	4      	time:54.3ms, loops:2, Concurrency:OFF                                                                   	7.70 KB	N/A
	  └─TableReader_50                    	root        	1       	data:ExchangeSender_49                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    	4      	time:54.3ms, loops:2, cop_task: {num: 3, max: 0s, min: 0s, avg: 0s, p95: 0s, copr_cache_hit_ratio: 0.00}	N/A    	N/A
	    └─ExchangeSender_49               	cop[tiflash]	1       	ExchangeType: PassThrough                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 	4      	tiflash_task:{proc max:52.6ms, min:52.5ms, p80:52.6ms, p95:52.6ms, iters:3, tasks:3, threads:24}        	N/A    	N/A
	      └─Projection_45                 	cop[tiflash]	1       	Column#20, Column#21, Column#22, Column#23, Column#24, ad_common_datas.report_simple_day.mediabuy_id, ad_common_datas.report_simple_day.slot_id                                                                                                                                                                                                                                                                                                                                                                                                           	4      	tiflash_task:{proc max:52.6ms, min:52.5ms, p80:52.6ms, p95:52.6ms, iters:3, tasks:3, threads:24}        	N/A    	N/A
	        └─HashAgg_46                  	cop[tiflash]	1       	group by:ad_common_datas.report_simple_day.mediabuy_id, ad_common_datas.report_simple_day.slot_id, funcs:sum(Column#53)->Column#20, funcs:sum(Column#54)->Column#21, funcs:sum(Column#55)->Column#22, funcs:sum(Column#56)->Column#23, funcs:sum(Column#57)->Column#24, funcs:firstrow(ad_common_datas.report_simple_day.mediabuy_id)->ad_common_datas.report_simple_day.mediabuy_id, funcs:firstrow(ad_common_datas.report_simple_day.slot_id)->ad_common_datas.report_simple_day.slot_id                                                                	4      	tiflash_task:{proc max:52.6ms, min:52.5ms, p80:52.6ms, p95:52.6ms, iters:3, tasks:3, threads:3}         	N/A    	N/A
	          └─ExchangeReceiver_48       	cop[tiflash]	1       	                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          	12     	tiflash_task:{proc max:51.6ms, min:51.5ms, p80:51.6ms, p95:51.6ms, iters:9, tasks:3, threads:24}        	N/A    	N/A
	            └─ExchangeSender_47       	cop[tiflash]	1       	ExchangeType: HashPartition, Hash Cols: [name: ad_common_datas.report_simple_day.mediabuy_id, collate: N/A], [name: ad_common_datas.report_simple_day.slot_id, collate: N/A]                                                                                                                                                                                                                                                                                                                                                                              	12     	tiflash_task:{proc max:50.5ms, min:0s, p80:50.5ms, p95:50.5ms, iters:3, tasks:3, threads:24}            	N/A    	N/A
	              └─HashAgg_13            	cop[tiflash]	1       	group by:Column#72, Column#73, funcs:sum(Column#67)->Column#53, funcs:sum(Column#68)->Column#54, funcs:sum(Column#69)->Column#55, funcs:sum(Column#70)->Column#56, funcs:sum(Column#71)->Column#57                                                                                                                                                                                                                                                                                                                                                        	12     	tiflash_task:{proc max:50.5ms, min:0s, p80:50.5ms, p95:50.5ms, iters:3, tasks:3, threads:3}             	N/A    	N/A
	                └─Projection_55       	cop[tiflash]	65.36   	cast(ad_common_datas.report_simple_day.impressions, decimal(32,0) BINARY)->Column#67, cast(ad_common_datas.report_simple_day.fisrt_impressions, decimal(32,0) BINARY)->Column#68, cast(ad_common_datas.report_simple_day.mid_impressions, decimal(32,0) BINARY)->Column#69, cast(ad_common_datas.report_simple_day.third_impressions, decimal(32,0) BINARY)->Column#70, cast(ad_common_datas.report_simple_day.end_impressions, decimal(32,0) BINARY)->Column#71, ad_common_datas.report_simple_day.mediabuy_id, ad_common_datas.report_simple_day.slot_id	47     	tiflash_task:{proc max:50.5ms, min:0s, p80:50.5ms, p95:50.5ms, iters:17, tasks:3, threads:24}           	N/A    	N/A
	                  └─Selection_44      	cop[tiflash]	65.36   	eq(ad_common_datas.report_simple_day.mediabuy_id, 715755), ge(ad_common_datas.report_simple_day.day, 2021-12-01 00:00:00.000000), lt(ad_common_datas.report_simple_day.day, 2023-05-16 00:00:00.000000)                                                                                                                                                                                                                                                                                                                                                   	47     	tiflash_task:{proc max:50.5ms, min:0s, p80:50.5ms, p95:50.5ms, iters:17, tasks:3, threads:24}           	N/A    	N/A
	                    └─TableFullScan_43	cop[tiflash]	49629569	table:report_simple_day, keep order:false                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 	2444893	tiflash_task:{proc max:47.5ms, min:0s, p80:47.5ms, p95:47.5ms, iters:114, tasks:3, threads:24}          	N/A    	N/A
17

你两次是查 tiflash 不一致吗？还是一次 tikv 一次 tiflash 不一致？你可以看看两次查询 tikv 是否是一致的~

18

如果同一个 SQL 语句执行两次结果不一致，可能是由多种原因引起的，例如数据不一致、事务隔离级别或硬件问题。

以下是一些可能的原因和解决方法：

  1. 数据不一致：如果在两次执行之间修改了表中的数据，则结果可能不一致。您可以尝试检查是否有其他进程或事务在执行期间修改了数据。

  2. 事务隔离级别：如果事务隔离级别设置为“READ UNCOMMITTED”或“READ COMMITTED”，可能会导致结果不一致。您可以尝试将事务隔离级别设置为“REPEATABLE READ”或“SERIALIZABLE”以确保结果的一致性。

  3. 硬件问题：如果存在硬件问题，例如磁盘 I/O 错误或网络问题，可能会导致结果不一致。您可以尝试检查硬件状态和日志，以查看是否有任何错误或警告。

此外，您还可以尝试使用“EXPLAIN”命令分析 SQL 语句的执行计划，查看两次执行之间是否有任何差异。这可能有助于您确定不一致的根本原因。

19

建议看看执行计划，是不是走tiflash 了，如果走了tiflash ，建议强制走tikv 在看一下，如果走tikv 没问题，可能是已知的BUG。

20

你怀疑是tikv 删除的数据，tiflash 没同步释放么？

嗯，看你用的版本，有问题也不意外了 :upside_down_face:

image
image763×981 44.3 KB

21

查的tiflash数据存在不一致的情况，tikv就没有问题。应该是已知的bug问题，非常感谢。