问题
工作中需要删除某个品牌和类目下的商品属性，于是编写了下面的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;
