# 引擎智能选择
__*TiFlash*__
__*引擎智能选择*__
__*CBO*__
+ `session.tidb_isolation_read_engines`: 支持读操作的可用引擎
+ `ALTER TABLE ... SET TIFLASH REPLICA n`: 为特定表启用 TiFlash 的 learner 复制功能
+ `ALTER TABLE ... SET TIFLASH REPLICA 0`: 关闭特定表的 TiFlash learner 复制功能
+ CBO 将自动地选择更好的存储引擎支持查询操作,以获得更好的性能
```8
+------------------------------------------------------+------------+-------------------+---------------+
| id                                                   | estRows    | task              | access object | 
+------------------------------------------------------+------------+-------------------+---------------+
| Sort_10                                              | 3.20       | root              |               |
| └─Projection_12                                      | 3.20       | root              |               |
|   └─TableReader_62                                   | 3.20       | root              |               |
|     └─ExchangeSender_61                              | 3.20       | ~!@batchCop[tiflash]@!~ |               |
|       └─Projection_57                                | 3.20       | ~!@batchCop[tiflash]@!~ |               |
|         └─HashAgg_58                                 | 3.20       | ~!@batchCop[tiflash]@!~ |               |
|           └─ExchangeReceiver_60                      | 3.20       | ~!@batchCop[tiflash]@!~ |               |
|             └─ExchangeSender_59                      | 3.20       | ~!@batchCop[tiflash]@!~ |               |
|               └─HashAgg_17                           | 3.20       | ~!@batchCop[tiflash]@!~ |               |
|                 └─Projection_67                      | 1677721.60 | ~!@batchCop[tiflash]@!~ |               |
|                   └─HashJoin_56                      | 1677721.60 | ~!@batchCop[tiflash]@!~ |               |
|                     ├─ExchangeReceiver_34(Build)     | 1677721.60 | ~!@batchCop[tiflash]@!~ |               |
|                     │ └─ExchangeSender_33            | 1677721.60 | ~!@batchCop[tiflash]@!~ |               |  
|                     │   └─Selection_32               | 1677721.60 | ~!@batchCop[tiflash]@!~ |               |
|                     │     └─TableFullScan_31         | 2097152.00 | ~!@batchCop[tiflash]@!~ | table:t5      |     
|                     └─TableFullScan_35(Probe)        | 2097152.00 | ~!@batchCop[tiflash]@!~ | table:t2      |
+------------------------------------------------------+------------+-------------------+---------------+
```
----------------------------------------------------------------
与 TiKV 一起, 各取所长, 使 TiDB 集群真正做到了 HTAP, 即支持 Hybrid Transaction and Analytic Processing 的 NewSQL 数据库。

较高的副本数量有利于负载平衡和快速故障转移,这会占用更多的空间。

在启用了 TiFlash 副本之后, 推荐利用 CBO (基于成本的优化器) 自动地挑选性能更好的存储引擎为查询操作服务,当然你也可以利用 hints。关于 hints,请参考 `https://docs.pingcap.com/zh/tidb/v6.0/use-tiflash`。

使用以下语句获取数据内启用了 TiFlash Learner 的表:

```
SELECT * FROM information_schema.tiflash_replica WHERE TABLE_SCHEMA = db_name;
```

TiFlash 启用前:
```
tidb> SELECT avg(t5.id), t5.id2, count(*) 
    -> FROM test.t5_nonclustered t5 JOIN test.t2_nonclustered t2
    -> ON t5.id = t2.id
    -> WHERE t5.id > t5.id2
    -> GROUP BY id2
    -> ORDER BY id2;
+--------------------------+---------------------+----------+
| avg(t5.id)               | id2                 | count(*) |
+--------------------------+---------------------+----------+
| 6499304087950836113.4308 | 2305843009213693953 |   524007 |
| 6499304087950836114.4308 | 2305843009213693954 |   524007 |
| 6499304087950836115.4308 | 2305843009213693955 |   524007 |
| 6499304087950836116.4308 | 2305843009213693956 |   524007 |
+--------------------------+---------------------+----------+
4 rows in set (2.21 sec)
```

TiFlash 启用后:
```
tidb> SELECT avg(t5.id), t5.id2, count(*) 
    -> FROM test.t5_nonclustered t5 JOIN test.t2_nonclustered t2
    -> ON t5.id = t2.id
    -> WHERE t5.id > t5.id2
    -> GROUP BY id2
    -> ORDER BY id2;
+--------------------------+---------------------+----------+
| avg(t5.id)               | id2                 | count(*) |
+--------------------------+---------------------+----------+
| 6499304087950836113.4308 | 2305843009213693953 |   524007 |
| 6499304087950836114.4308 | 2305843009213693954 |   524007 |
| 6499304087950836115.4308 | 2305843009213693955 |   524007 |
| 6499304087950836116.4308 | 2305843009213693956 |   524007 |
+--------------------------+---------------------+----------+
4 rows in set (0.48 sec)
```