UCP: Use the fingerprint of the SQL to bind the plan in SQL Plan Management

[winoros]

Description

We've introduced SQL Plan Management to bind the plan for some specific SQLs if it's hard to modify it.(e.g. The SQL is genereated by ORM).
But we only accept to use the original SQL text to bind the plan currently. There's a question that the SQL may be very very long. So in this time, use the fingerprint value is a good way to improve usability.

Score

• 900

Mentor(s)

• @winoros

Contact the mentors: #tidb-challenge-program channel in TiDB Community Slack Workspace

Recommended Skills

• SQL Optimization

Learning Materials

• Chinese: TiDB 精选技术讲解文章
• English: Awesome-Database-Learning

[tsthght]

/pick-up-challenge

[sre-bot]

@tsthght pick up issue success

[tsthght]

I have a few questions I'd like to discuss together and need help: @winoros PTAL
Question1: I think I need to modify the Parser to support additional syntax. Could it be as follows?

CREATE [GLOBAL | SESSION] BINDING FOR fingerprint USING index_name;

Question2: How does TiDB user get the fingerprint? Does TiDB need support a function to get the fingerprint of an SQL statement? Could it be as follows?

> select fingerprint("select * from abc where id>4")
> 11235813abcdef

Question3: When TiDB user look up the binding rules, they can only see fingerprint and rules, which is not intuitive and cannot know the specific SQL. Can we provide the following functions?

> select normsql("11235813abcdef");
> select * from abc where id>?

Maybe fingerprint cannot be converted to SQL. Can we store the mapping of fingerprint to SQL?

> select * from fingerprint_table;
fingerprint                       |    normsql
11235813abcdef            |    select * from abc where id > ?

Perhaps some additional things may be done later with fingerprint_table, such as SQL black/white lists and etc.

[sre-bot]

This pick has been automatically canceled after more than a week.

[tsthght]

/pick-up-challenge

[sre-bot]

@tsthght pick up issue success

[sre-bot]

This pick has been automatically canceled after more than a week.