MySQL Delete子查询优化

问题

工作中需要删除某个品牌和类目下的商品属性，于是编写了下面的SQL：

DELETE FROM `product$propertyvalue` WHERE pv_componentid IN(
SELECT cmp_id FROM `product$component` WHERE cmp_brid=7616 and AND_kiid=357)

其中product$propertyvalue这张表有上亿条记录，执行过程中直接报sql timeout，进入数据库命令行show processlist，发现这条语句的状态为preparing。

解决

通过explain分析语句

mysql> explain DELETE from `product$propertyvalue` where pv_componentid in(
    -> select cmp_id from `product$component` where cmp_brid=7616 and cmp_kiid=357);

输出结果如下：

+----+--------------------+-----------------------+------------+-----------------+-------------------------------------------------------+---------+---------+------+----------+----------+-------------+
| id | select_type        | table                 | partitions | type            | possible_keys                                         | key     | key_len | ref  | rows     | filtered | Extra       |
+----+--------------------+-----------------------+------------+-----------------+-------------------------------------------------------+---------+---------+------+----------+----------+-------------+
|  1 | DELETE             | product$propertyvalue | NULL       | ALL             | NULL                                                  | NULL    | NULL    | NULL | 62850680 |   100.00 | Using where |
|  2 | DEPENDENT SUBQUERY | product$component     | NULL       | unique_subquery | PRIMARY,BRID_CODE_UNIQUE,FK_tdw4lqwf17ggyewqvcgqh3tyf | PRIMARY | 8       | func |        1 |     5.00 | Using where |
+----+--------------------+-----------------------+------------+-----------------+-------------------------------------------------------+---------+---------+------+----------+----------+-------------+
2 rows in set (0.01 sec)

可以看到语句根本没有用到索引，执行过程中做了全表扫描，难怪会超时。
所以这里想法设法要让其用到索引。于是有了下面的语句：

DELETE `product$propertyvalue` 
FROM
    `product$propertyvalue`,
    ( SELECT cmp_id FROM `product$component` WHERE cmp_brid = 7616 AND cmp_kiid = 357 ) a 
WHERE
    `product$propertyvalue`.pv_componentid = a.cmp_id;

此时explain的结果为：

+----+-------------+-----------------------+------------+------+-------------------------------------------------------+---------------------------+---------+------------------------------------+------+----------+-------------+
| id | select_type | table                 | partitions | type | possible_keys                                         | key                       | key_len | ref                                | rows | filtered | Extra       |
+----+-------------+-----------------------+------------+------+-------------------------------------------------------+---------------------------+---------+------------------------------------+------+----------+-------------+
|  1 | SIMPLE      | product$component     | NULL       | ref  | PRIMARY,BRID_CODE_UNIQUE,FK_tdw4lqwf17ggyewqvcgqh3tyf | BRID_CODE_UNIQUE          | 9       | const                              |    1 |     5.00 | Using where |
|  1 | DELETE      | product$propertyvalue | NULL       | ref  | PROPERTYVALUE_P_CMP_INDEX,PROPERTYVALUE_CMPID_INDEX   | PROPERTYVALUE_P_CMP_INDEX | 9       | mall_prod.product$component.cmp_id |   13 |   100.00 | NULL        |
+----+-------------+-----------------------+------------+------+-------------------------------------------------------+---------------------------+---------+------------------------------------+------+----------+-------------+

可以看到这时用到了索引。

总结

以后形如

DELETE FROM 
  table_name1 
WHERE 
  table_name1.column IN (SELECT column2 FROM table_name2 WHERE XXX );

都可以写成

DELETE table_name1 
FROM
    table_name1,
    ( SELECT column2 FROM table_name2 WHERE XXX ) a 
WHERE
    table_name1.column  = a.column 2;