# 聚合操作解决方案 (继续...)

+ 表中数据不经常被修改
  + 在计算节点 **TiDB** 上开启 **copr-cache**(TiDB 4.0 引入，默认开启)
  + 确定是 **copr-cache** 的优化

```4
explain analyze select count(*) from t1 where dt>'2021-11-25 20:39:58' ;
+------------------------------+-------------+----------+-----------+---------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------+-----------+------+
| id                           | estRows     | actRows  | task      | access object | execution info                                                                                                                                               | operator info                                | memory    | disk |
+------------------------------+-------------+----------+-----------+---------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------+-----------+------+
| StreamAgg_20                 | 1.00        | 1        | root      |               | time:1.49ms, loops:2                                                                                                                                         | funcs:count(Column#7)->Column#5              | 380 Bytes | N/A  |
| └─TableReader_21             | 1.00        | 10       | root      |               | time:1.49ms, loops:2, cop_task: {num: 10, max: 1.45ms, min: 596.5µs, avg: 802.1µs, p95: 1.45ms, rpc_num: 10, rpc_time: 7.92ms, copr_cache_hit_ratio: 1.00}   | data:StreamAgg_9                             | 779 Bytes | N/A  |
|   └─StreamAgg_9              | 1.00        | 10       | cop[tikv] |               | tikv_task:{proc max:3.26s, min:515ms, p80:3.25s, p95:3.26s, iters:9770, tasks:10}                                                                            | funcs:count(1)->Column#7                     | N/A       | N/A  |
|     └─Selection_19           | 1204500.00  | 1200000  | cop[tikv] |               | tikv_task:{proc max:3.26s, min:515ms, p80:3.25s, p95:3.26s, iters:9770, tasks:10}                                                                            | gt(sbtest.t1.dt, 2021-11-25 20:39:58.000000) | N/A       | N/A  |
|       └─TableFullScan_18     | 10000000.00 | 10000000 | cop[tikv] | table:t1      | tikv_task:{proc max:3.24s, min:508ms, p80:3.23s, p95:3.24s, iters:9770, tasks:10}                                                                            | keep order:false                             | N/A       | N/A  |
+------------------------------+-------------+----------+-----------+---------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------+-----------+------+
```

------------------------------------------------------------

```
explain analyze select count(*) from t1 where dt>'2021-11-25 20:39:58' ;
+------------------------------+-------------+----------+-----------+---------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------+-----------+------+
| id                           | estRows     | actRows  | task      | access object | execution info                                                                                                                                               | operator info                                | memory    | disk |
+------------------------------+-------------+----------+-----------+---------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------+-----------+------+
| StreamAgg_20                 | 1.00        | 1        | root      |               | time:1.49ms, loops:2                                                                                                                                         | funcs:count(Column#7)->Column#5              | 380 Bytes | N/A  |
| └─TableReader_21             | 1.00        | 10       | root      |               | time:1.49ms, loops:2, cop_task: {num: 10, max: 1.45ms, min: 596.5µs, avg: 802.1µs, p95: 1.45ms, rpc_num: 10, rpc_time: 7.92ms, copr_cache_hit_ratio: 1.00}   | data:StreamAgg_9                             | 779 Bytes | N/A  |
|   └─StreamAgg_9              | 1.00        | 10       | cop[tikv] |               | tikv_task:{proc max:3.26s, min:515ms, p80:3.25s, p95:3.26s, iters:9770, tasks:10}                                                                            | funcs:count(1)->Column#7                     | N/A       | N/A  |
|     └─Selection_19           | 1204500.00  | 1200000  | cop[tikv] |               | tikv_task:{proc max:3.26s, min:515ms, p80:3.25s, p95:3.26s, iters:9770, tasks:10}                                                                            | gt(sbtest.t1.dt, 2021-11-25 20:39:58.000000) | N/A       | N/A  |
|       └─TableFullScan_18     | 10000000.00 | 10000000 | cop[tikv] | table:t1      | tikv_task:{proc max:3.24s, min:508ms, p80:3.23s, p95:3.24s, iters:9770, tasks:10}                                                                            | keep order:false                             | N/A       | N/A  |
+------------------------------+-------------+----------+-----------+---------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------+-----------+------+
```

上面的例子中，第一次执行 0.89 秒，中间数据没有 DML，再次查询时，不到 10ms 就执行完毕，我们再看 SQL 的 explain analyze 输出中有：copr_cache_hit_ratio: 1.00，就可以确定是 copr-cache 的优化，将本来需要去 tikv 扫描大量数据的 SQL 执行时间从 0.89 秒降低到了不到 10ms。