After enabling TiFlash and having no index on the query field, why does it still use the TiKV engine?

Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.

Original topic: 开启了tiflash,并且查询字段没有索引,为什么还会走tikv引擎

| username: Running

I think the problem is that the tidb-server process is not running. You can check the status of the tidb-server process using the ps command. If it is not running, you can start it using the systemctl start tidb command.

| username: tidb菜鸟一只 | Original post link

TiFlash is suitable for using aggregation statistics and similar SQL queries, while direct queries still default to TiKV.

| username: Running | Original post link

Isn’t it that updates will default to using TiKV? Regular queries use TiFlash.

| username: 托马斯滑板鞋 | Original post link

Force it to try?

set @@session.tidb_allow_mpp=1;
set @@session.tidb_enforce_mpp=1;
| username: Running | Original post link

Forcing it loses its meaning.

| username: 托马斯滑板鞋 | Original post link

Then can both TiKV and TiFlash be used once, and use explain analyze to output the actual execution plan to see if TiKV takes less time?

| username: 我是咖啡哥 | Original post link

There’s no such saying :joy:

| username: tidb菜鸟一只 | Original post link

TiFlash is only used for OLAP queries, while OLTP queries are more suitable to be handled by TiKV. The data in TiFlash is synchronized from TiKV, so updates will definitely modify the data in TiKV first before being synchronized to TiFlash.

| username: Running | Original post link

How to distinguish between OLAP and OLTP? The actual performance is 4 minutes under TiKV and 2 seconds under TiFlash.

| username: Running | Original post link

TiFlash took 2 seconds, TiKV took 4 minutes.

| username: 托马斯滑板鞋 | Original post link

:joy: It seems that TiDB’s CBO is not smart enough?
P.S: The isolation-read.engines in TiDB configuration is set to the default value, right?

| username: 人如其名 | Original post link

What version of the data?

| username: Kongdom | Original post link

select * should default to TiKV, right?

| username: tidb菜鸟一只 | Original post link

OLAP refers to SQL for statistical summaries, such as counting the total number of rows for a certain field in a table or using sum functions. It is generally used in reporting applications. OLTP, on the other hand, typically involves operations on multiple rows or very few rows and is generally used in real-time concurrent application scenarios, such as browsing and placing orders in an online store.

| username: Running | Original post link

The business scenarios can easily distinguish between OLAP and OLTP, but it’s not so easy to distinguish them at the SQL statement level. I still don’t understand how the CBO optimizer determines when to use TiKV and when to use TiFlash.

| username: Running | Original post link

Yes, multiple engines by default.

| username: Running | Original post link

6.5

| username: 人如其名 | Original post link

Execute the following statements separately and observe the differences in cost calculation. You can post the results.

explain analyze format=true_card_cost select /*+ read_from_storage(tikv[net_ticket]) */ * from net_ticket where tick_dxidd='19712907354' order by tick_onlinestime limit 100;

explain analyze format=true_card_cost select /*+ read_from_storage(tiflash[net_ticket]) */ * from net_ticket where tick_dxidd='19712907354' order by tick_onlinestime limit 100;

show variables like 'tidb_cost_model_version';
| username: dba-kit | Original post link

Try changing “select *” to specific columns and see if it works? Even queries that can hit the TiKV index might still go to TiFlash, let alone the fact that you are not using an index here. When I use it, to avoid OLTP traffic going to TiFlash, I specifically force OLTP query nodes not to use TiFlash.

| username: Running | Original post link

Set up a separate tidbserver node for TiFlash queries.