Any good suggestions for controlling the execution plan in TiDB? Is it better to use Hints or to bind the execution plan?

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

Original topic: TiDB控制执行计划有没有好的建议?是使用Hints更好还是通过执行计划绑定更好?

| username: FutureDB

Currently, the TiDB optimizer still has many cases where the execution plan goes off track. In such cases, what methods do you mainly use in your project team to control the execution plan?

  • (1) Use Hints entirely, which will intrude into the SQL code;
SELECT /*+ USE_INDEX(t1, idx1), HASH_AGG(), HASH_JOIN(t1) */ count(*) FROM t t1, t t2 WHERE t1.a = t2.b;
  • (2) Use execution plan binding entirely, through DDL, without intruding into the SQL code;
  • (3) Partially use Hints, partially use execution plan binding;

In actual work, it is found that whether using Hints or execution plan binding, there are many issues. So I would like to ask everyone which method you prefer to use to control the execution plan in actual projects.

To briefly explain, here are some issues encountered when using these two methods to control the execution plan in actual projects:

  • Issues with using Hints:
    (1) It requires intruding into the SQL code logic;
    (2) For dynamic SQL in MyBatis, this method of controlling the execution plan may have a positive optimization effect on some actual SQL, but may have a negative impact (i.e., causing execution plan errors) on others;
    (3) Once there are changes to the table structure (such as indexes), it is necessary to query all the functional code to see if the index is used for Hints specification, making maintenance quite troublesome;

  • Issues with using execution plan binding:
    (1) Similarly, for dynamic SQL in MyBatis, a single dynamic SQL may result in dozens of actual cases, meaning there could be dozens of actual SQL executions. Adding execution plan binding for each actual dynamic SQL is time-consuming and labor-intensive, and maintaining it later is also extremely painful;

| username: TiDBer_jYQINSnf | Original post link

Is the motivation for binding the execution plan because TiDB indeed does not choose the correct execution plan if it is not bound?

| username: yiduoyunQ | Original post link

  1. Using hints to manage work in business, but if the DBA changes the structure and deletes the index, the business statement will report an error.
  2. Using binding to manage work in DBA, but if the business SQL slightly changes the pattern, such as adding multiple ‘as’, it will not be able to hit the binding.

Currently, the TiDB optimizer still has many cases where the execution plan deviates.

Specific problems can be analyzed on a case-by-case basis. The official team is also continuously providing Optimizer Fix Controls.

| username: FutureDB | Original post link

Yes, currently two situations have been identified:

  1. There is a significantly better execution plan available, but it is not chosen; instead, a worse one is used.
  2. There is a significantly better execution plan, and the TiDB optimizer’s estimated cost (estCost) is also lower, but it is ultimately not chosen.
| username: FutureDB | Original post link

Deleting an index will not cause errors when using the original Hints.

| username: TiDBer_jYQINSnf | Original post link

You use it in a more detailed manner, while we just add more resources when the CPU usage is high. We haven’t really bound the execution plan.

| username: TiDBer_jYQINSnf | Original post link

You use it more meticulously. We just add resources whenever we notice it’s slow, and we’ve almost never bound execution plans.

| username: 小龙虾爱大龙虾 | Original post link

Using hints in application code, MyBatis will change the hint along with the conditions. You can’t make this too complicated, right? You can’t put all the logic in MyBatis, right? :joy_cat:
Regarding index switching issues, I generally add index hints less frequently and usually change the association method instead.

| username: 有猫万事足 | Original post link

As long as the root node has this operator, HASH_AGG, I strongly recommend trying TiFlash + MPP. It is very likely to have a miraculous effect.

| username: FutureDB | Original post link

All of our SQL logic is uniformly placed in MyBatis. Currently, we control the execution plan to specify the join method, join order, and index usage.

| username: FutureDB | Original post link

We are currently using this. For online queries, we try to use TiKV, and for batch analysis, we use TiFlash. However, TiFlash is still not very friendly for high-concurrency queries. The official recommendation is to keep the QPS for TiFlash under 50, which indicates there are still some limitations.

| username: FutureDB | Original post link

Resources are still quite precious, continuously expanding resources is just a temporary solution. :joy:

| username: 小龙虾爱大龙虾 | Original post link

There’s no problem putting SQL in MyBatis. What I’m saying is don’t just keep concatenating in a single SQL statement. Write a few more SQL statements; otherwise, the logic in MyBatis will become more complex. :joy_cat:

| username: kkpeter | Original post link

You guys are rich.

| username: 这里介绍不了我 | Original post link

So detailed

| username: 江湖故人 | Original post link

Hints and bindings should not be used extensively; they are mostly used in cases of multi-table joins, fixed join methods, and join orders when absolutely necessary. Optimizing multi-table joins is a significant challenge for almost all database optimizers.

| username: FutureDB | Original post link

This is unrealistic because composite queries are too common.

| username: FutureDB | Original post link

Indeed, it’s a bit extravagant.

| username: FutureDB | Original post link

That’s what they say, but currently TiDB’s optimizer isn’t very intelligent, and there are still quite a few execution plans that go astray. Binding is also a last resort.

| username: 有猫万事足 | Original post link

It should be noted that using TiFlash does not necessarily mean MPP. Sometimes, even if TiFlash is included in the execution plan but only for scanning, it may not provide much improvement and could even cause fallback.

There are indeed limitations. If you are using version 6.5, TiFlash does not have resource control and lacks some strategies for spilling operators to disk. These issues are much improved in version 7.5. Memory is less likely to explode, and stability is much better.