DELETE only needs row id, partition key and indexed columns

[tangenta]

Enhancement

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:

• The most simple case: single table delete on a normal table(no foreign key, no partition)
• 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
  • 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)
  • 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
  • normal table
    • planner: swap codes of buildDelete for further changes #54009
    • planner: use logical output cols to record positions of tables to be deleted #54010
    • *: let planner decide the col refs for non-partition and non-foreignkey table's delete #54065
    • planner: support to prune column for simple table's DELETE | tidb-test=pr/2392 #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.

[fixdb]

Perhaps in the column pruning step, planner doesn't prune column for delete operator, hence causing the generation of IndexLookUp?

[winoros]

Get/BatchGet might be difficult to support all the cases, planner can disable the Get/BatchGet choice if the table contains too much columns.