tidb-6.1.0 执行计划不走tiflash

是我的海 · 2022 年8 月 24 日 13:47

【 TiDB 使用环境`】测试环境
【 TiDB 版本】tidb-v6.1.0
【遇到的问题】聚合sql 不走 tiflash
【问题现象及影响】
集群拓扑：

将一个表同步到tiflash 中

sql 执行计划

MySQL [test]> explain select lesson_id,group_level_8,sum(col_1),sum(col_2),sum(col_3),sum(col_4),sum(col_5),sum(col_6),sum(col_7),sum(col_8),sum(col_9),sum(col_10),sum(col_11),sum(col_12),sum(col_13),sum(col_14),sum(col_15),sum(col_16),sum(col_17),sum(col_18),sum(col_19),sum(col_20),sum(col_21),sum(col_22),sum(col_23),sum(col_24),sum(col_25),sum(col_26),sum(col_27),sum(col_28),sum(col_29),sum(col_30),sum(col_31),sum(col_32),sum(col_33),sum(col_34),sum(col_35),sum(col_36),sum(col_37),sum(col_38),sum(col_39),sum(col_40) from tbldatasopcal_500r_200c where index1 in (1,2,3) and index2 in (1,2,3) and index4 in (1,2,3) and index5 in (1,2,3) group by lesson_id,group_level_8 limit 1\G
*************************** 1. row ***************************
           id: Projection_8
      estRows: 1.00
         task: root
access object: 
operator info: test.tbldatasopcal_500r_200c.lesson_id, test.tbldatasopcal_500r_200c.group_level_8, Column#202, Column#203, Column#204, Column#205, Column#206, Column#207, Column#208, Column#209, Column#210, Column#211, Column#212, Column#213, Column#214, Column#215, Column#216, Column#217, Column#218, Column#219, Column#220, Column#221, Column#222, Column#223, Column#224, Column#225, Column#226, Column#227, Column#228, Column#229, Column#230, Column#231, Column#232, Column#233, Column#234, Column#235, Column#236, Column#237, Column#238, Column#239, Column#240, Column#241
*************************** 2. row ***************************
           id: └─Limit_11
      estRows: 1.00
         task: root
access object: 
operator info: offset:0, count:1
*************************** 3. row ***************************
           id:   └─HashAgg_37
      estRows: 1.00
         task: root
access object: 
operator info: group by:test.tbldatasopcal_500r_200c.group_level_8, test.tbldatasopcal_500r_200c.lesson_id, funcs:sum(Column#288)->Column#202, funcs:sum(Column#289)->Column#203, funcs:sum(Column#290)->Column#204, funcs:sum(Column#291)->Column#205, funcs:sum(Column#292)->Column#206, funcs:sum(Column#293)->Column#207, funcs:sum(Column#294)->Column#208, funcs:sum(Column#295)->Column#209, funcs:sum(Column#296)->Column#210, funcs:sum(Column#297)->Column#211, funcs:sum(Column#298)->Column#212, funcs:sum(Column#299)->Column#213, funcs:sum(Column#300)->Column#214, funcs:sum(Column#301)->Column#215, funcs:sum(Column#302)->Column#216, funcs:sum(Column#303)->Column#217, funcs:sum(Column#304)->Column#218, funcs:sum(Column#305)->Column#219, funcs:sum(Column#306)->Column#220, funcs:sum(Column#307)->Column#221, funcs:sum(Column#308)->Column#222, funcs:sum(Column#309)->Column#223, funcs:sum(Column#310)->Column#224, funcs:sum(Column#311)->Column#225, funcs:sum(Column#312)->Column#226, funcs:sum(Column#313)->Column#227, funcs:sum(Column#314)->Column#228, funcs:sum(Column#315)->Column#229, funcs:sum(Column#316)->Column#230, funcs:sum(Column#317)->Column#231, funcs:sum(Column#318)->Column#232, funcs:sum(Column#319)->Column#233, funcs:sum(Column#320)->Column#234, funcs:sum(Column#321)->Column#235, funcs:sum(Column#322)->Column#236, funcs:sum(Column#323)->Column#237, funcs:sum(Column#324)->Column#238, funcs:sum(Column#325)->Column#239, funcs:sum(Column#326)->Column#240, funcs:sum(Column#327)->Column#241, funcs:firstrow(test.tbldatasopcal_500r_200c.lesson_id)->test.tbldatasopcal_500r_200c.lesson_id, funcs:firstrow(test.tbldatasopcal_500r_200c.group_level_8)->test.tbldatasopcal_500r_200c.group_level_8
*************************** 4. row ***************************
           id:     └─IndexLookUp_38
      estRows: 1.00
         task: root
access object: 
operator info: 
*************************** 5. row ***************************
           id:       ├─IndexRangeScan_25(Build)
      estRows: 749928.18
         task: cop[tikv]
access object: table:tbldatasopcal_500r_200c, index:index1_idx(index1)
operator info: range:[1,1], [2,2], [3,3], keep order:false
*************************** 6. row ***************************
           id:       └─HashAgg_14(Probe)
      estRows: 1.00
         task: cop[tikv]
access object: 
operator info: group by:test.tbldatasopcal_500r_200c.group_level_8, test.tbldatasopcal_500r_200c.lesson_id, funcs:sum(test.tbldatasopcal_500r_200c.col_1)->Column#288, funcs:sum(test.tbldatasopcal_500r_200c.col_2)->Column#289, funcs:sum(test.tbldatasopcal_500r_200c.col_3)->Column#290, funcs:sum(test.tbldatasopcal_500r_200c.col_4)->Column#291, funcs:sum(test.tbldatasopcal_500r_200c.col_5)->Column#292, funcs:sum(test.tbldatasopcal_500r_200c.col_6)->Column#293, funcs:sum(test.tbldatasopcal_500r_200c.col_7)->Column#294, funcs:sum(test.tbldatasopcal_500r_200c.col_8)->Column#295, funcs:sum(test.tbldatasopcal_500r_200c.col_9)->Column#296, funcs:sum(test.tbldatasopcal_500r_200c.col_10)->Column#297, funcs:sum(test.tbldatasopcal_500r_200c.col_11)->Column#298, funcs:sum(test.tbldatasopcal_500r_200c.col_12)->Column#299, funcs:sum(test.tbldatasopcal_500r_200c.col_13)->Column#300, funcs:sum(test.tbldatasopcal_500r_200c.col_14)->Column#301, funcs:sum(test.tbldatasopcal_500r_200c.col_15)->Column#302, funcs:sum(test.tbldatasopcal_500r_200c.col_16)->Column#303, funcs:sum(test.tbldatasopcal_500r_200c.col_17)->Column#304, funcs:sum(test.tbldatasopcal_500r_200c.col_18)->Column#305, funcs:sum(test.tbldatasopcal_500r_200c.col_19)->Column#306, funcs:sum(test.tbldatasopcal_500r_200c.col_20)->Column#307, funcs:sum(test.tbldatasopcal_500r_200c.col_21)->Column#308, funcs:sum(test.tbldatasopcal_500r_200c.col_22)->Column#309, funcs:sum(test.tbldatasopcal_500r_200c.col_23)->Column#310, funcs:sum(test.tbldatasopcal_500r_200c.col_24)->Column#311, funcs:sum(test.tbldatasopcal_500r_200c.col_25)->Column#312, funcs:sum(test.tbldatasopcal_500r_200c.col_26)->Column#313, funcs:sum(test.tbldatasopcal_500r_200c.col_27)->Column#314, funcs:sum(test.tbldatasopcal_500r_200c.col_28)->Column#315, funcs:sum(test.tbldatasopcal_500r_200c.col_29)->Column#316, funcs:sum(test.tbldatasopcal_500r_200c.col_30)->Column#317, funcs:sum(test.tbldatasopcal_500r_200c.col_31)->Column#318, funcs:sum(test.tbldatasopcal_500r_200c.col_32)->Column#319, funcs:sum(test.tbldatasopcal_500r_200c.col_33)->Column#320, funcs:sum(test.tbldatasopcal_500r_200c.col_34)->Column#321, funcs:sum(test.tbldatasopcal_500r_200c.col_35)->Column#322, funcs:sum(test.tbldatasopcal_500r_200c.col_36)->Column#323, funcs:sum(test.tbldatasopcal_500r_200c.col_37)->Column#324, funcs:sum(test.tbldatasopcal_500r_200c.col_38)->Column#325, funcs:sum(test.tbldatasopcal_500r_200c.col_39)->Column#326, funcs:sum(test.tbldatasopcal_500r_200c.col_40)->Column#327
*************************** 7. row ***************************
           id:         └─Selection_27
      estRows: 312767.14
         task: cop[tikv]
access object: 
operator info: in(test.tbldatasopcal_500r_200c.index2, 1, 2, 3), in(test.tbldatasopcal_500r_200c.index4, 1, 2, 3), in(test.tbldatasopcal_500r_200c.index5, 1, 2, 3)
*************************** 8. row ***************************
           id:           └─TableRowIDScan_26
      estRows: 749928.18
         task: cop[tikv]
access object: table:tbldatasopcal_500r_200c
operator info: keep order:false
8 rows in set (0.01 sec)

此集群是从5.4.2 升级到6.1.0 的。
因为在5.4 进行压测时，上述sql 并发达到 10 以上时，耗时增加明显并且cpu 消耗很高。并且有下列日志

但是在升级到6.1.0 之后，sql 就不会主动路由到tiflash 上

ShawnYan · 2022 年8 月 24 日 14:01

方便发下表结构和样例数据？

mpp 关一下再看执行计划呢？

是我的海 · 2022 年8 月 24 日 14:34

表结构

CREATE TABLE `tbldatasopcal_1000r_100c` (
  `lesson_id` bigint(20) NOT NULL,
  `assistant_uid` bigint(20) NOT NULL,
  `save_time` bigint(20) NOT NULL,
  `user_type` varchar(100) NOT NULL,
  `transfer_status` bigint(20) NOT NULL,
  `course_id` bigint(20) DEFAULT NULL,
  `update_time` bigint(20) DEFAULT NULL,
  `group_level_1` bigint(20) DEFAULT NULL,
  `group_level_2` bigint(20) DEFAULT NULL,
  `group_level_3` bigint(20) DEFAULT NULL,
  `group_level_4` bigint(20) DEFAULT NULL,
  `group_level_5` bigint(20) DEFAULT NULL,
  `group_level_6` bigint(20) DEFAULT NULL,
  `group_level_7` bigint(20) DEFAULT NULL,
  `group_level_8` bigint(20) DEFAULT NULL,
  `index1` bigint(20) DEFAULT NULL,
  `index2` bigint(20) DEFAULT NULL,
  `index3` bigint(20) DEFAULT NULL,
  `index4` bigint(20) DEFAULT NULL,
  `index5` bigint(20) DEFAULT NULL,
  `index6` bigint(20) DEFAULT NULL,
  `index7` bigint(20) DEFAULT NULL,
  `index8` bigint(20) DEFAULT NULL,
  `index9` bigint(20) DEFAULT NULL,
  `index10` bigint(20) DEFAULT NULL,
  `col_1` bigint(20) DEFAULT NULL,
  `col_2` bigint(20) DEFAULT NULL,
  `col_3` bigint(20) DEFAULT NULL,
  `col_4` bigint(20) DEFAULT NULL,
  `col_5` bigint(20) DEFAULT NULL,
  `col_6` bigint(20) DEFAULT NULL,
  `col_7` bigint(20) DEFAULT NULL,
  `col_8` bigint(20) DEFAULT NULL,
  `col_9` bigint(20) DEFAULT NULL,
  `col_10` bigint(20) DEFAULT NULL,
  `col_11` bigint(20) DEFAULT NULL,
  `col_12` bigint(20) DEFAULT NULL,
  `col_13` bigint(20) DEFAULT NULL,
  `col_14` bigint(20) DEFAULT NULL,
  `col_15` bigint(20) DEFAULT NULL,
  `col_16` bigint(20) DEFAULT NULL,
  `col_17` bigint(20) DEFAULT NULL,
  `col_18` bigint(20) DEFAULT NULL,
  `col_19` bigint(20) DEFAULT NULL,
  `col_20` bigint(20) DEFAULT NULL,
  `col_21` bigint(20) DEFAULT NULL,
  `col_22` bigint(20) DEFAULT NULL,
  `col_23` bigint(20) DEFAULT NULL,
  `col_24` bigint(20) DEFAULT NULL,
  `col_25` bigint(20) DEFAULT NULL,
  `col_26` bigint(20) DEFAULT NULL,
  `col_27` bigint(20) DEFAULT NULL,
  `col_28` bigint(20) DEFAULT NULL,
  `col_29` bigint(20) DEFAULT NULL,
  `col_30` bigint(20) DEFAULT NULL,
  `col_31` bigint(20) DEFAULT NULL,
  `col_32` bigint(20) DEFAULT NULL,
  `col_33` bigint(20) DEFAULT NULL,
  `col_34` bigint(20) DEFAULT NULL,
  `col_35` bigint(20) DEFAULT NULL,
  `col_36` bigint(20) DEFAULT NULL,
  `col_37` bigint(20) DEFAULT NULL,
  `col_38` bigint(20) DEFAULT NULL,
  `col_39` bigint(20) DEFAULT NULL,
  `col_40` bigint(20) DEFAULT NULL,
  `col_41` bigint(20) DEFAULT NULL,
  `col_42` bigint(20) DEFAULT NULL,
  `col_43` bigint(20) DEFAULT NULL,
  `col_44` bigint(20) DEFAULT NULL,
  `col_45` bigint(20) DEFAULT NULL,
  `col_46` bigint(20) DEFAULT NULL,
  `col_47` bigint(20) DEFAULT NULL,
  `col_48` bigint(20) DEFAULT NULL,
  `col_49` bigint(20) DEFAULT NULL,
  `col_50` bigint(20) DEFAULT NULL,
  `col_51` bigint(20) DEFAULT NULL,
  `col_52` bigint(20) DEFAULT NULL,
  `col_53` bigint(20) DEFAULT NULL,
  `col_54` bigint(20) DEFAULT NULL,
  `col_55` bigint(20) DEFAULT NULL,
  `col_56` bigint(20) DEFAULT NULL,
  `col_57` bigint(20) DEFAULT NULL,
  `col_58` bigint(20) DEFAULT NULL,
  `col_59` bigint(20) DEFAULT NULL,
  `col_60` bigint(20) DEFAULT NULL,
  `col_61` bigint(20) DEFAULT NULL,
  `col_62` bigint(20) DEFAULT NULL,
  `col_63` bigint(20) DEFAULT NULL,
  `col_64` bigint(20) DEFAULT NULL,
  `col_65` bigint(20) DEFAULT NULL,
  `col_66` bigint(20) DEFAULT NULL,
  `col_67` bigint(20) DEFAULT NULL,
  `col_68` bigint(20) DEFAULT NULL,
  `col_69` bigint(20) DEFAULT NULL,
  `col_70` bigint(20) DEFAULT NULL,
  `col_71` bigint(20) DEFAULT NULL,
  `col_72` bigint(20) DEFAULT NULL,
  `col_73` bigint(20) DEFAULT NULL,
  `col_74` bigint(20) DEFAULT NULL,
  `col_75` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`lesson_id`,`assistant_uid`,`transfer_status`,`user_type`,`save_time`) /*T![clustered_index] NONCLUSTERED */,
  KEY `course_id_idx` (`course_id`),
  KEY `update_time_idx` (`update_time`),
  KEY `group_level_1_idx` (`group_level_1`),
  KEY `group_level_2_idx` (`group_level_2`),
  KEY `group_level_3_idx` (`group_level_3`),
  KEY `group_level_4_idx` (`group_level_4`),
  KEY `group_level_5_idx` (`group_level_5`),
  KEY `group_level_6_idx` (`group_level_6`),
  KEY `group_level_7_idx` (`group_level_7`),
  KEY `group_level_8_idx` (`group_level_8`),
  KEY `index1_idx` (`index1`),
  KEY `index2_idx` (`index2`),
  KEY `index3_idx` (`index3`),
  KEY `index4_idx` (`index4`),
  KEY `index5_idx` (`index5`),
  KEY `index6_idx` (`index6`),
  KEY `index7_idx` (`index7`),
  KEY `index8_idx` (`index8`),
  KEY `index9_idx` (`index9`),
  KEY `index10_idx` (`index10`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin

样例sql

select lesson_id,group_level_8,sum(col_1),sum(col_2),sum(col_3),sum(col_4),sum(col_5),sum(col_6),sum(col_7),sum(col_8),sum(col_9),sum(col_10),sum(col_11),sum(col_12),sum(col_13),sum(col_14),sum(col_15),sum(col_16),sum(col_17),sum(col_18),sum(col_19),sum(col_20),sum(col_21),sum(col_22),sum(col_23),sum(col_24),sum(col_25),sum(col_26),sum(col_27),sum(col_28),sum(col_29),sum(col_30),sum(col_31),sum(col_32),sum(col_33),sum(col_34),sum(col_35),sum(col_36),sum(col_37),sum(col_38),sum(col_39),sum(col_40) from tbldatasopcal_1000r_100c  where index1 in (1,2,3) and index2 in (1,2,3) and index4 in (1,2,3) and index5 in (1,2,3) group by lesson_id,group_level_8 limit 1\G

我把mpp 关了也不行

h5n1 · 2022 年8 月 24 日 14:39

explain analyze结果，重新收集统计信息看看，实在不行就spm绑下执行计划

是我的海 · 2022 年8 月 24 日 14:42

MySQL [test]> explain analyze  select lesson_id,group_level_8,sum(col_1),sum(col_2),sum(col_3),sum(col_4),sum(col_5),sum(col_6),sum(col_7),sum(col_8),sum(col_9),sum(col_10),sum(col_11),sum(col_12),sum(col_13),sum(col_14),sum(col_15),sum(col_16),sum(col_17),sum(col_18),sum(col_19),sum(col_20),sum(col_21),sum(col_22),sum(col_23),sum(col_24),sum(col_25),sum(col_26),sum(col_27),sum(col_28),sum(col_29),sum(col_30),sum(col_31),sum(col_32),sum(col_33),sum(col_34),sum(col_35),sum(col_36),sum(col_37),sum(col_38),sum(col_39),sum(col_40) from tbldatasopcal_1000r_100c  where index1 in (1,2,3) and index2 in (1,2,3) and index4 in (1,2,3) and index5 in (1,2,3) group by lesson_id,group_level_8 limit 1\G



*************************** 1. row ***************************
            id: Projection_8
       estRows: 1.00
       actRows: 1
          task: root
 access object: 
execution info: time:3.52s, loops:2, Concurrency:OFF
 operator info: test.tbldatasopcal_1000r_100c.lesson_id, test.tbldatasopcal_1000r_100c.group_level_8, Column#102, Column#103, Column#104, Column#105, Column#106, Column#107, Column#108, Column#109, Column#110, Column#111, Column#112, Column#113, Column#114, Column#115, Column#116, Column#117, Column#118, Column#119, Column#120, Column#121, Column#122, Column#123, Column#124, Column#125, Column#126, Column#127, Column#128, Column#129, Column#130, Column#131, Column#132, Column#133, Column#134, Column#135, Column#136, Column#137, Column#138, Column#139, Column#140, Column#141
        memory: 866.8 KB
          disk: N/A
*************************** 2. row ***************************
            id: └─Limit_11
       estRows: 1.00
       actRows: 1
          task: root
 access object: 
execution info: time:3.52s, loops:2
 operator info: offset:0, count:1
        memory: N/A
          disk: N/A
*************************** 3. row ***************************
            id:   └─HashAgg_33
       estRows: 1.00
       actRows: 422
          task: root
 access object: 
execution info: time:3.52s, loops:1, partial_worker:{wall_time:3.51754035s, concurrency:5, task_num:67, tot_wait:17.38629974s, tot_exec:192.316962ms, tot_time:17.579897943s, max:3.517496181s, p95:3.517496181s}, final_worker:{wall_time:3.524737952s, concurrency:5, task_num:25, tot_wait:17.575486944s, tot_exec:44.791716ms, tot_time:17.620290142s, max:3.524709495s, p95:3.524709495s}
 operator info: group by:test.tbldatasopcal_1000r_100c.group_level_8, test.tbldatasopcal_1000r_100c.lesson_id, funcs:sum(Column#188)->Column#102, funcs:sum(Column#189)->Column#103, funcs:sum(Column#190)->Column#104, funcs:sum(Column#191)->Column#105, funcs:sum(Column#192)->Column#106, funcs:sum(Column#193)->Column#107, funcs:sum(Column#194)->Column#108, funcs:sum(Column#195)->Column#109, funcs:sum(Column#196)->Column#110, funcs:sum(Column#197)->Column#111, funcs:sum(Column#198)->Column#112, funcs:sum(Column#199)->Column#113, funcs:sum(Column#200)->Column#114, funcs:sum(Column#201)->Column#115, funcs:sum(Column#202)->Column#116, funcs:sum(Column#203)->Column#117, funcs:sum(Column#204)->Column#118, funcs:sum(Column#205)->Column#119, funcs:sum(Column#206)->Column#120, funcs:sum(Column#207)->Column#121, funcs:sum(Column#208)->Column#122, funcs:sum(Column#209)->Column#123, funcs:sum(Column#210)->Column#124, funcs:sum(Column#211)->Column#125, funcs:sum(Column#212)->Column#126, funcs:sum(Column#213)->Column#127, funcs:sum(Column#214)->Column#128, funcs:sum(Column#215)->Column#129, funcs:sum(Column#216)->Column#130, funcs:sum(Column#217)->Column#131, funcs:sum(Column#218)->Column#132, funcs:sum(Column#219)->Column#133, funcs:sum(Column#220)->Column#134, funcs:sum(Column#221)->Column#135, funcs:sum(Column#222)->Column#136, funcs:sum(Column#223)->Column#137, funcs:sum(Column#224)->Column#138, funcs:sum(Column#225)->Column#139, funcs:sum(Column#226)->Column#140, funcs:sum(Column#227)->Column#141, funcs:firstrow(test.tbldatasopcal_1000r_100c.lesson_id)->test.tbldatasopcal_1000r_100c.lesson_id, funcs:firstrow(test.tbldatasopcal_1000r_100c.group_level_8)->test.tbldatasopcal_1000r_100c.group_level_8
        memory: 36.5 MB
          disk: N/A
*************************** 4. row ***************************
            id:     └─IndexLookUp_34
       estRows: 1.00
       actRows: 67676
          task: root
 access object: 
execution info: time:3.51s, loops:68, index_task: {total_time: 3.25s, fetch_handle: 219.3ms, build: 165.5µs, wait: 3.03s}, table_task: {total_time: 16.4s, num: 77, concurrency: 5}
 operator info: 
        memory: 21.2 MB
          disk: N/A
*************************** 5. row ***************************
            id:       ├─IndexRangeScan_21(Build)
       estRows: 1490744.26
       actRows: 1499535
          task: cop[tikv]
 access object: table:tbldatasopcal_1000r_100c, index:index1_idx(index1)
execution info: time:151.2ms, loops:1468, cop_task: {num: 2, max: 505ms, min: 148ms, avg: 326.5ms, p95: 505ms, max_proc_keys: 1149318, p95_proc_keys: 1149318, tot_proc: 627ms, tot_wait: 1ms, rpc_num: 2, rpc_time: 652.9ms, copr_cache_hit_ratio: 0.00}, tikv_task:{proc max:465ms, min:136ms, p80:465ms, p95:465ms, iters:1474, tasks:2}, scan_detail: {total_process_keys: 1499535, total_process_keys_size: 68978610, total_keys: 1499539, rocksdb: {delete_skipped_count: 0, key_skipped_count: 1499535, block: {cache_hit_count: 942, read_count: 3, read_byte: 161.0 KB}}}
 operator info: range:[1,1], [2,2], [3,3], keep order:false
        memory: N/A
          disk: N/A
*************************** 6. row ***************************
            id:       └─HashAgg_13(Probe)
       estRows: 1.00
       actRows: 67676
          task: cop[tikv]
 access object: 
execution info: time:15.9s, loops:178, cop_task: {num: 316, max: 472.1ms, min: 1.99ms, avg: 116.2ms, p95: 272.6ms, max_proc_keys: 19850, p95_proc_keys: 11144, tot_proc: 35.2s, tot_wait: 66ms, rpc_num: 316, rpc_time: 36.7s, copr_cache_hit_ratio: 0.00}, tikv_task:{proc max:460ms, min:1ms, p80:161ms, p95:261ms, iters:1622, tasks:316}, scan_detail: {total_process_keys: 1499535, total_process_keys_size: 566250903, total_keys: 1883176, rocksdb: {delete_skipped_count: 13191, key_skipped_count: 1011522, block: {cache_hit_count: 12369987, read_count: 2401, read_byte: 24.9 MB}}}
 operator info: group by:test.tbldatasopcal_1000r_100c.group_level_8, test.tbldatasopcal_1000r_100c.lesson_id, funcs:sum(test.tbldatasopcal_1000r_100c.col_1)->Column#188, funcs:sum(test.tbldatasopcal_1000r_100c.col_2)->Column#189, funcs:sum(test.tbldatasopcal_1000r_100c.col_3)->Column#190, funcs:sum(test.tbldatasopcal_1000r_100c.col_4)->Column#191, funcs:sum(test.tbldatasopcal_1000r_100c.col_5)->Column#192, funcs:sum(test.tbldatasopcal_1000r_100c.col_6)->Column#193, funcs:sum(test.tbldatasopcal_1000r_100c.col_7)->Column#194, funcs:sum(test.tbldatasopcal_1000r_100c.col_8)->Column#195, funcs:sum(test.tbldatasopcal_1000r_100c.col_9)->Column#196, funcs:sum(test.tbldatasopcal_1000r_100c.col_10)->Column#197, funcs:sum(test.tbldatasopcal_1000r_100c.col_11)->Column#198, funcs:sum(test.tbldatasopcal_1000r_100c.col_12)->Column#199, funcs:sum(test.tbldatasopcal_1000r_100c.col_13)->Column#200, funcs:sum(test.tbldatasopcal_1000r_100c.col_14)->Column#201, funcs:sum(test.tbldatasopcal_1000r_100c.col_15)->Column#202, funcs:sum(test.tbldatasopcal_1000r_100c.col_16)->Column#203, funcs:sum(test.tbldatasopcal_1000r_100c.col_17)->Column#204, funcs:sum(test.tbldatasopcal_1000r_100c.col_18)->Column#205, funcs:sum(test.tbldatasopcal_1000r_100c.col_19)->Column#206, funcs:sum(test.tbldatasopcal_1000r_100c.col_20)->Column#207, funcs:sum(test.tbldatasopcal_1000r_100c.col_21)->Column#208, funcs:sum(test.tbldatasopcal_1000r_100c.col_22)->Column#209, funcs:sum(test.tbldatasopcal_1000r_100c.col_23)->Column#210, funcs:sum(test.tbldatasopcal_1000r_100c.col_24)->Column#211, funcs:sum(test.tbldatasopcal_1000r_100c.col_25)->Column#212, funcs:sum(test.tbldatasopcal_1000r_100c.col_26)->Column#213, funcs:sum(test.tbldatasopcal_1000r_100c.col_27)->Column#214, funcs:sum(test.tbldatasopcal_1000r_100c.col_28)->Column#215, funcs:sum(test.tbldatasopcal_1000r_100c.col_29)->Column#216, funcs:sum(test.tbldatasopcal_1000r_100c.col_30)->Column#217, funcs:sum(test.tbldatasopcal_1000r_100c.col_31)->Column#218, funcs:sum(test.tbldatasopcal_1000r_100c.col_32)->Column#219, funcs:sum(test.tbldatasopcal_1000r_100c.col_33)->Column#220, funcs:sum(test.tbldatasopcal_1000r_100c.col_34)->Column#221, funcs:sum(test.tbldatasopcal_1000r_100c.col_35)->Column#222, funcs:sum(test.tbldatasopcal_1000r_100c.col_36)->Column#223, funcs:sum(test.tbldatasopcal_1000r_100c.col_37)->Column#224, funcs:sum(test.tbldatasopcal_1000r_100c.col_38)->Column#225, funcs:sum(test.tbldatasopcal_1000r_100c.col_39)->Column#226, funcs:sum(test.tbldatasopcal_1000r_100c.col_40)->Column#227
        memory: N/A
          disk: N/A
*************************** 7. row ***************************
            id:         └─Selection_23
       estRows: 619518.68
       actRows: 1387537
          task: cop[tikv]
 access object: 
execution info: tikv_task:{proc max:388ms, min:1ms, p80:135ms, p95:221ms, iters:1622, tasks:316}
 operator info: in(test.tbldatasopcal_1000r_100c.index2, 1, 2, 3), in(test.tbldatasopcal_1000r_100c.index4, 1, 2, 3), in(test.tbldatasopcal_1000r_100c.index5, 1, 2, 3)
        memory: N/A
          disk: N/A
*************************** 8. row ***************************
            id:           └─TableRowIDScan_22
       estRows: 1490744.26
       actRows: 1499535
          task: cop[tikv]
 access object: table:tbldatasopcal_1000r_100c
execution info: tikv_task:{proc max:385ms, min:1ms, p80:134ms, p95:220ms, iters:1622, tasks:316}
 operator info: keep order:false
        memory: N/A
          disk: N/A
8 rows in set (3.53 sec)

是我的海 · 2022 年8 月 24 日 14:43

重新搜集统计信息了，也是没效果。
这是一个新的集群，我们的sql 基本都是这种聚合的，绑定执行计划不太利于维护。就想看看这个原因

另外直接执行count 是可以走到tiflash 的。

ShawnYan · 2022 年8 月 25 日 02:07

也可能跟代价计算有关，强制走 tiflash 试试吧。
set @@session.tidb_isolation_read_engines = 'tiflash'

forever · 2022 年8 月 25 日 03:49

这个改了影响有点大，正常不需要走tiflash的也的走了

wish-PingCAP · 2022 年8 月 25 日 04:32

如果有需要的话，可以使用 hint 对这个语句要求总是使用 TiFlash：https://docs.pingcap.com/zh/tidb/stable/use-tidb-to-read-tiflash#手工-hint

wish-PingCAP · 2022 年8 月 25 日 04:36

如果是执行计划固定走 TiKV 的话，就是在新版本里优化器代价估算后觉得这条 SQL 走 TiKV 索引查询比走 TiFlash 全表扫描更快。可以观察一下这条 SQL 在升级前后是执行得比以前更快还是更慢了。

xiaohetao · 2022 年8 月 25 日 06:01

我觉得你可以临时创建spm或者 hit 绑定，执行下看看走tikv和tiflash 哪个效率更高些？如果tikv 更高的话那就是数据库计算后kv 比flash 效率高才走的kv

是我的海 · 2022 年8 月 25 日 06:17

这个语句在6.1 的版本中指定tiflash 的话速度比走tikv 快很多。
因为是新的业务在测试，刚一开始就指定hint 感觉挺不友好的，应该让数据库自己路由到正确的节点。
目前的现象看着是感觉是优化器选择问题。

是我的海 · 2022 年8 月 25 日 06:18

这个肯定是走tiflash 更快的，在5.4 版本中是这样。在6.1 版本中走tikv 是2s+ , 指定tiflash 的话是不到1s

xiaohetao · 2022 年8 月 25 日 06:27

tidb_enable_index_merge 这个参数是开启的吗？

是我的海 · 2022 年8 月 25 日 06:39

是开的，但是我记得没有主动开启过它。关闭之后再次登陆执行还是不行。
在同一个会话中，同样的sql 我把表名换一个，一个走tiflash ，一个不走tiflash 。
1、查询tbldatasopcal_500r_100c 是没有走tiflash 的；

2、查询tbldatasopcal_500r_200c 走的是tiflash

这两个表是都同步到了tiflash 的

xiaohetao · 2022 年8 月 25 日 07:00

https://docs.pingcap.com/zh/tidb/dev/sql-prepared-plan-cache
你参照这个看下sql 使用的执行计划是不是缓存的

MySQL [test]> select @@last_plan_from_cache; – 选择计划缓存
±-----------------------+
| @@last_plan_from_cache |
±-----------------------+
| 1 |
±-----------------------+
1 row in set (0.00 sec)

MySQL [test]> admin flush session plan_cache; – 清空当前 session 的计划缓存
Query OK, 0 rows affected (0.00 sec)

MySQL [test]> execute stmt;
Empty set (0.00 sec)

MySQL [test]> select @@last_plan_from_cache; – 由于缓存被清空，此时无法再次选中
±-----------------------+
| @@last_plan_from_cache |
±-----------------------+
| 0 |
±-----------------------+
1 row in set (0.00 sec)

xiaohetao · 2022 年8 月 25 日 07:01

不能清全局的

是我的海 · 2022 年8 月 25 日 08:40

emmm, 还是不行，我的cache 返回值是0 。感谢XD

xiaohetao · 2022 年8 月 26 日 01:18

那没有找到原因暂时就创建一个smp 吧

h5n1 · 2022 年8 月 26 日 03:17

可以用explian format='verbose’看下2个不同版本下SQL的estCost评估