Skip to content

*: add foreign key check/cascade information in explain result#39167

Merged
ti-chi-bot merged 28 commits intopingcap:masterfrom
crazycs520:fk-explain
Nov 22, 2022
Merged

*: add foreign key check/cascade information in explain result#39167
ti-chi-bot merged 28 commits intopingcap:masterfrom
crazycs520:fk-explain

Conversation

@crazycs520
Copy link
Contributor

@crazycs520 crazycs520 commented Nov 15, 2022

Signed-off-by: crazycs520 crazycs520@gmail.com

What problem does this PR solve?

Issue Number: close #39166

Problem Summary: Add foreign key check/cascade executor information in explain statement.

What is changed and how it works?

Here is an example:

prepare:

set @@global.tidb_enable_foreign_key=1;
set @@foreign_key_checks=1;
create table t1 (id int key);
create table t2 (id int key, foreign key fk(id) references t1(id) ON UPDATE CASCADE ON DELETE CASCADE);

Here is some explain results:

test> explain insert into t2 values (1);
+-----------------------+---------+------+---------------+-----------------------------+
| id                    | estRows | task | access object | operator info               |
+-----------------------+---------+------+---------------+-----------------------------+
| Insert_1              | N/A     | root |               | N/A                         |
| └─Foreign_Key_Check_3 | 0.00    | root | table:t1      | foreign_key:fk, check_exist |
+-----------------------+---------+------+---------------+-----------------------------+
2 rows in set
Time: 0.019s
test> explain update t2 set id=id+1 where id = 1;
+-----------------------+---------+------+---------------+-----------------------------+
| id                    | estRows | task | access object | operator info               |
+-----------------------+---------+------+---------------+-----------------------------+
| Update_3              | N/A     | root |               | N/A                         |
| └─Point_Get_1         | 1.00    | root | table:t2      | handle:1                    |
| └─Foreign_Key_Check_4 | 0.00    | root | table:t1      | foreign_key:fk, check_exist |
+-----------------------+---------+------+---------------+-----------------------------+
3 rows in set
Time: 0.017s
test> explain delete from t1 where id > 1;
+-------------------------+---------+-----------+---------------+------------------------------------------------+
| id                      | estRows | task      | access object | operator info                                  |
+-------------------------+---------+-----------+---------------+------------------------------------------------+
| Delete_4                | N/A     | root      |               | N/A                                            |
| └─TableReader_7         | 3333.33 | root      |               | data:TableRangeScan_6                          |
|   └─TableRangeScan_6    | 3333.33 | cop[tikv] | table:t1      | range:(1,+inf], keep order:false, stats:pseudo |
| └─Foreign_Key_Cascade_8 | 0.00    | root      | table:t2      | foreign_key:fk, on_delete:CASCADE              |
+-------------------------+---------+-----------+---------------+------------------------------------------------+
4 rows in set
Time: 0.013s
test> explain update t1 set id=id+1 where id = 1;
+-------------------------+---------+------+---------------+-----------------------------------+
| id                      | estRows | task | access object | operator info                     |
+-------------------------+---------+------+---------------+-----------------------------------+
| Update_3                | N/A     | root |               | N/A                               |
| └─Point_Get_1           | 1.00    | root | table:t1      | handle:1                          |
| └─Foreign_Key_Cascade_4 | 0.00    | root | table:t2      | foreign_key:fk, on_update:CASCADE |
+-------------------------+---------+------+---------------+-----------------------------------+

Check List

Tests

  • Unit test
  • Integration test
  • Manual test (add detailed scripts or steps below)
  • No code

Side effects

  • Performance regression: Consumes more CPU
  • Performance regression: Consumes more Memory
  • Breaking backward compatibility

Documentation

  • Affects user behaviors
  • Contains syntax changes
  • Contains variable changes
  • Contains experimental features
  • Changes MySQL compatibility

Release note

Please refer to Release Notes Language Style Guide to write a quality release note.

None

@crazycs520
*: support explain with foreign key check/cascade
756ffcb 
Signed-off-by: crazycs520 <crazycs520@gmail.com>
@crazycs520
refine code
1a5a715 
Signed-off-by: crazycs520 <crazycs520@gmail.com>
@crazycs520
tiny fix
cee28ef 
Signed-off-by: crazycs520 <crazycs520@gmail.com>
@crazycs520
add normalize plan with foreign key test
4c31703 
Signed-off-by: crazycs520 <crazycs520@gmail.com>
@ti-chi-bot
Copy link
Member

ti-chi-bot commented Nov 15, 2022 •
edited
Loading

[REVIEW NOTIFICATION]

This pull request has been approved by:

  • AilinKid
  • time-and-fate

To complete the pull request process, please ask the reviewers in the list to review by filling /cc @reviewer in the comment.
After your PR has acquired the required number of LGTMs, you can assign this pull request to the committer in the list by filling /assign @committer in the comment to help you merge this pull request.

The full list of commands accepted by this bot can be found here.

Details

Reviewer can indicate their review by submitting an approval review.
Reviewer can cancel approval by submitting a request changes review.

@ti-chi-bot ti-chi-bot added do-not-merge/invalid-title release-note-none Denotes a PR that doesn't merit a release note. size/XL Denotes a PR that changes 500-999 lines, ignoring generated files. labels Nov 15, 2022
@crazycs520 crazycs520 changed the title Fk explain *: fix issue of explain result doesn't contain foreign key check/cascade information Nov 15, 2022
@crazycs520
tiny refine
0a15163 
Signed-off-by: crazycs520 <crazycs520@gmail.com>
@crazycs520
fix ci
a187b9e 
Signed-off-by: crazycs520 <crazycs520@gmail.com>
@crazycs520
Merge branch 'master' into fk-explain
0776206
@crazycs520
fix ci
a64d6b5 
Signed-off-by: crazycs520 <crazycs520@gmail.com>
@crazycs520
Merge branch 'fk-explain' of https://github.com/crazycs520/tidb into …
15bbefd 
…fk-explain
@crazycs520 crazycs520 mentioned this pull request Nov 16, 2022
Merged
12 tasks
@crazycs520 crazycs520 changed the title *: fix issue of explain result doesn't contain foreign key check/cascade information *: add foreign key check/cascade information in explain result Nov 17, 2022
AilinKid
AilinKid reviewed Nov 17, 2022
View reviewed changes
@crazycs520
move test to address comment
76f02b2 
Signed-off-by: crazycs520 <crazycs520@gmail.com>
time-and-fate
time-and-fate reviewed Nov 17, 2022
View reviewed changes
Copy link
Member

@time-and-fate time-and-fate left a comment •
edited
Loading

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

To make the result of EXPLAIN has a reasonable tree-like structure, we need to make sure that we set isLastChild to true only for the last "child" of an operator in flattenRecursively.

@crazycs520
address comment
06f6151 
Signed-off-by: crazycs520 <crazycs520@gmail.com>
@crazycs520
address comment
41cc9e7 
Signed-off-by: crazycs520 <crazycs520@gmail.com>
@ti-chi-bot ti-chi-bot added the needs-rebase Indicates a PR cannot be merged because it has merge conflicts with HEAD. label Nov 18, 2022
@ti-chi-bot ti-chi-bot removed the needs-rebase Indicates a PR cannot be merged because it has merge conflicts with HEAD. label Nov 18, 2022
AilinKid
AilinKid reviewed Nov 18, 2022
View reviewed changes
Copy link
Contributor

@AilinKid AilinKid left a comment

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

Rest LGTM

@crazycs520
remove bin
4c0bf4c 
Signed-off-by: crazycs520 <crazycs520@gmail.com>
@ti-chi-bot ti-chi-bot added the status/LGT1 Indicates that a PR has LGTM 1. label Nov 18, 2022
time-and-fate
time-and-fate reviewed Nov 18, 2022
View reviewed changes
@crazycs520
fix bug
bea9776 
Signed-off-by: crazycs520 <crazycs520@gmail.com>
@crazycs520
add more test
0ba0320 
Signed-off-by: crazycs520 <crazycs520@gmail.com>
@crazycs520
add more test
c45b10c 
Signed-off-by: crazycs520 <crazycs520@gmail.com>
@ti-chi-bot ti-chi-bot added status/LGT2 Indicates that a PR has LGTM 2. and removed status/LGT1 Indicates that a PR has LGTM 1. labels Nov 18, 2022
@crazycs520
Copy link
Contributor Author

crazycs520 commented Nov 22, 2022

/run-all-tests

@crazycs520
Copy link
Contributor Author

crazycs520 commented Nov 22, 2022

/run-all-tests

@crazycs520
Copy link
Contributor Author

crazycs520 commented Nov 22, 2022

/merge

@ti-chi-bot
Copy link
Member

ti-chi-bot commented Nov 22, 2022

This pull request has been accepted and is ready to merge.

DetailsCommit hash: c4d4ed8

@ti-chi-bot ti-chi-bot added the status/can-merge Indicates a PR has been approved by a committer. label Nov 22, 2022
@crazycs520
Copy link
Contributor Author

crazycs520 commented Nov 22, 2022

/run-all-tests

@ti-chi-bot ti-chi-bot merged commit 1e6d1a8 into pingcap:master Nov 22, 2022
@crazycs520 crazycs520 deleted the fk-explain branch November 22, 2022 09:33
@sre-bot
Copy link
Contributor

sre-bot commented Nov 22, 2022

TiDB MergeCI notify

✅ Well Done! New fixed [2] after this pr merged.

CI Name Result Duration Compare with Parent commit
idc-jenkins-ci-tidb/mybatis-test 🔴 failed 1, success 0, total 1 11 min Existing failure
idc-jenkins-ci/integration-cdc-test ✅ all 39 tests passed 22 min Fixed
idc-jenkins-ci-tidb/common-test ✅ all 11 tests passed 10 min Fixed
idc-jenkins-ci-tidb/integration-common-test 🟢 all 17 tests passed 14 min Existing passed
idc-jenkins-ci-tidb/tics-test 🟢 all 1 tests passed 6 min 12 sec Existing passed
idc-jenkins-ci-tidb/sqllogic-test-1 🟢 all 26 tests passed 5 min 24 sec Existing passed
idc-jenkins-ci-tidb/sqllogic-test-2 🟢 all 28 tests passed 5 min 12 sec Existing passed
idc-jenkins-ci-tidb/integration-ddl-test 🟢 all 6 tests passed 4 min 14 sec Existing passed
idc-jenkins-ci-tidb/integration-compatibility-test 🟢 all 1 tests passed 3 min 4 sec Existing passed
idc-jenkins-ci-tidb/plugin-test 🟢 build success, plugin test success 4min Existing passed

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Reviewers

@AilinKid AilinKid AilinKid approved these changes

@time-and-fate time-and-fate time-and-fate approved these changes

Assignees

No one assigned

Labels

release-note-none Denotes a PR that doesn't merit a release note. size/XL Denotes a PR that changes 500-999 lines, ignoring generated files. status/can-merge Indicates a PR has been approved by a committer. status/LGT2 Indicates that a PR has LGTM 2.

Projects

None yet

Milestone

No milestone

Development

Successfully merging this pull request may close these issues.

Explain DML should contain foreign key check/cascade information

6 participants

@crazycs520 @ti-chi-bot @sre-bot @AilinKid @time-and-fate @hawkingrei