How can TiDB automatically rewrite SQL?

Translated
1

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

Original topic: tidb怎么样能够自动改写sql?

| username: tidb狂热爱好者

【TiDB Usage Environment】Production Environment
【TiDB Version】
【Encountered Problem】
【Reproduction Path】What operations were performed to encounter the problem
【Problem Phenomenon and Impact】
For example, I wrote a full table scan SQL
An SQL that uses the wrong index. How can I automatically bind the correct execution plan?
For example, this?
CREATE GLOBAL BINDING for SELECT id, user_id, side, price, volume, fee_rate_maker, fee_rate_taker, fee, fee_coin_rate, deal_volume, deal_money, avg_price, STATUS, TYPE, ctime, mtime, source, order_type, finger_print FROM ex_order WHERE (user_id = 10303 AND STATUS IN (4, 3, 2, 6, 5) AND order_type = 1) ORDER BY ctime DESC limit 1,10 USING SELECT id, user_id, side, price, volume, fee_rate_maker, fee_rate_taker, fee, fee_coin_rate, deal_volume, deal_money, avg_price, STATUS, TYPE, ctime, mtime, source, order_type, finger_print FROM ex_orderforce index(idx_user_id_status) WHERE (user_id = 10303 AND STATUS IN (4, 3, 2, 6, 5) AND order_type = 1) ORDER BY ctime DESC limit 1,10; Bound
【Attachments】

Please provide the version information of each component, such as cdc/tikv, which can be obtained by executing cdc version/tikv-server --version.

2
| username: ddhe9527 | Original post link
  1. The logical optimization part of the compilation process will perform equivalent rewriting on the SQL.
  2. It supports automatically binding execution plans based on the Statement Summary table.
  3. If the bound execution plan is found to be suboptimal, it can also automatically evolve to bind a better execution plan.
3
| username: xuexiaogang | Original post link

Generally, this depends on the optimizer; the optimizer automatically rewrites it.

4
| username: tidb狂热爱好者 | Original post link

Automatic evolution is turned off and cannot be turned on.

5
| username: ddhe9527 | Original post link

In versions v5.0~v5.1, it can be enabled. Auto-evolution itself is a very complex feature, and to truly perfect it, it definitely involves many machine learning and neural network-related algorithms. The official team probably decided to rework this feature.

6
| username: tidb狂热爱好者 | Original post link

I hope it will come out later. Why doesn’t TiDB have an SQL optimization recommendation feature?

7
| username: system | Original post link

This topic was automatically closed 1 minute after the last reply. No new replies are allowed.