Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.
Original topic: tidb对子查询支持性能不好，感觉比mysql性能差
[Test Environment for TiDB]
[TiDB Version] 7.1.1, MySQL 5.7
[Resource Configuration]
TiDB: 3 TiKV nodes, 3 TiDB nodes, each with 32 cores and 128GB of RAM
MySQL: 1 node, 4 cores, 8GB of RAM
The table data volume is the same, and it is the same table.
MySQL query result: 4 seconds, TiDB query result: 144 seconds
Their execution plans are as follows:
How much data is in the table?
Try using the NO_DECORRELATE() hint to see if it has any effect.
PS: This hint should be placed inside the subquery.
After reading the documentation, this hint is for using the apply operator, but the execution plan indeed used the apply operator.
Could you upload the result of explain analyze?
Drag this out a bit to see which two indexes are used in TiDB.
Have you analyzed it? Did it use the optimal index? If you are performing multi-table join and aggregation operations, you can use TiFlash, it’s very fast.
How much data is returned?
Uploaded the Excel version of the execution plan for everyone to read carefully.
Adding any hint has no optimization effect.
The one you uploaded is using TiFlash.
Automatically choose TiFlash, because my table has TiFlash replicas. In theory, using TiFlash shouldn’t have such poor performance.
When performing aggregate queries, TiFlash is extremely fast, but it also depends on the application scenario.
I forced it to use TiKV, but the performance is still poor. I’ll upload the execution plan later.
How long did your explain analyze take to execute? The actual execution used TiFlash, while the previous explain used TiKV. Try adding this hint to your SQL: /+ READ_FROM_STORAGE(TIKV[ca,aa])/
This is the execution plan for TiKV, and it takes about 150 seconds even with TiKV.
User, try the HASH_JOIN(t1_name [, tl_name …]) HINT.
I seriously suspect that the storage used by TiDB is too poor. Scanning the index of 6 million records takes nearly 1 minute.
You can also check the network situation.
|
username: wenyi
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: