pingcap / pingcap/tidb

DELETE only needs row id, partition key and indexed columns

Open
#38,911 2 comments 0 reactions 1 assignee Claimed by @AilinKid View on GitHub
sig/planner type/enhancement
Dominant language
Go
Stars
40.5k
Forks
6.2k
PR merge metrics
PR metrics pending

Description

## Enhancement

```sql
mysql> create table t (a varchar(100), b int, c int, primary key(a, b) nonclustered);
Query OK, 0 rows affected (0.17 sec)

mysql> explain delete from t where a in ('aaaaa', 'b');
+----------------------------------+---------+-----------+------------------------------+--------------------------------------------------------------------+
| id | estRows | task | access object | operator info |
+----------------------------------+---------+-----------+------------------------------+--------------------------------------------------------------------+
| Delete_4 | N/A | root | | N/A |
| └─IndexLookUp_11 | 20.00 | root | | |
| ├─IndexRangeScan_9(Build) | 20.00 | cop[tikv] | table:t, index:PRIMARY(a, b) | range:["aaaaa","aaaaa"], ["b","b"], keep order:false, stats:pseudo |
| └─TableRowIDScan_10(Probe) | 20.00 | cop[tikv] | table:t | keep order:false, stats:pseudo |
+----------------------------------+---------+-----------+------------------------------+--------------------------------------------------------------------+
4 rows in set (0.01 sec)
```

For the delete operation, only `_tidb_rowid` and the value `a` and `b` are required. In this case, `IndexLookUp` can be eliminated to a `IndexRangeScan`.

### Detailed work items

We split the cases into details:
- [x] The most simple case: single table delete on a normal table(no foreign key, no partition)
- [x] Multi-table delete
- [ ] Support for tables containing foreign key
- [ ] Support for tables containing partitions

A DELETE will contain the read path, reading the needed rows from storage. And the write path, deleting the given row and its indexes. To save the network cost, we need to support the column pruning both for the read path and the write path:
- Read path can only read needed columns
- [x] Read by coprocessor (by default)
- [ ] Read by Get/BatchGet
- A more complicated case: read path can only read needed field from a large column(JSON, TEXT)
- [x] Read by coprocessor (by projection push down)
- [ ] Read by Get/BatchGet
- [ ] Write path can delete the record by the given column, not the full row
- [x] normal table
- #54009
- #54010
- #54065
- #56077
- [ ] partitioned table
- [ ] foreign keys

To support the most simple and common case, we need to modify the write path and let planner change the column reference map.

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.