为什么MySQL DELETE无法在子查询上使用索引？

Question

确立在某些otherquestions这里，在MySQL中使用“子查询”删除的原因是要慢一些，而相同的“选择”查询执行速度快：

MariaDB [as_01_import]> explain select * from invoice_payment where invoice_id in (select id from dochead where system_id = 5786); 
+------+-------------+-----------------+------+---------------------------------------+----------------------------+---------+-------------------------+------+-------------+ 
| id | select_type | table   | type | possible_keys       | key      | key_len | ref      | rows | Extra  | 
+------+-------------+-----------------+------+---------------------------------------+----------------------------+---------+-------------------------+------+-------------+ 
| 1 | PRIMARY  | dochead   | ref | PRIMARY,dochead_system_id    | dochead_system_id   | 4  | const     | 891 | Using index | 
| 1 | PRIMARY  | invoice_payment | ref | invoice_payment_invoice_fk,invoice_id | invoice_payment_invoice_fk | 4  | as_01_import.dochead.id | 1 |    | 
+------+-------------+-----------------+------+---------------------------------------+----------------------------+---------+-------------------------+------+-------------+ 


MariaDB [as_01_import]> explain delete from invoice_payment where invoice_id in (select id from dochead where system_id = 5786); 
+------+--------------------+-----------------+-----------------+---------------------------+---------+---------+------+---------+-------------+ 
| id | select_type  | table   | type   | possible_keys    | key  | key_len | ref | rows | Extra  | 
+------+--------------------+-----------------+-----------------+---------------------------+---------+---------+------+---------+-------------+ 
| 1 | PRIMARY   | invoice_payment | ALL    | NULL      | NULL | NULL | NULL | 1235451 | Using where | 
| 2 | DEPENDENT SUBQUERY | dochead   | unique_subquery | PRIMARY,dochead_system_id | PRIMARY | 4  | func |  1 | Using where | 
+------+--------------------+-----------------+-----------------+---------------------------+---------+---------+------+---------+-------------+ 
2 rows in set (0.44 sec) 

知道JOIN可以使用索引，我想请问专家：

是什么阻止MySQL/MariaDB在使用SUBQUERY的DELETE中使用索引？这是一个执行问题还是存在概念问题？有没有计划解决这个问题？影响其他SQL供应商的是同样的问题吗？

Answer 1

子查询是派生表，并未实现。它们表现在临时表格中。

正如我在this answer写道：

文档Derived Tables in MySQL 5.7描述它很好5.6 版本和5.7，其中后者将提供无罚由于 在物化派生表输出的变化被结合 成外部查询。在以前的版本中，大量的开销是 与派生的临时表忍受。

Answer 2

请勿使用IN (SELECT ...)。相反，使用如下所述的多表DELETE：http://dev.mysql.com/doc/refman/5.5/en/delete.html

这样做会在适用的情况下使用索引。