MySQL where子句中的子查询有性能问题

项目中一条SQL语句有严重的性能问题：

SELECT role_goods.id,role_goods.number,roleId,role.`name` ,goods.`name` as '物品名称'
from role_goods
LEFT JOIN role on role.id =role_goods.roleId
LEFT JOIN goods on goods.id=role_goods.goodsId
WHERE goodsId in (SELECT shop_goods.goodsId from shop_goods WHERE shop_goods.type!='药品' ) ORDER BY number DESC;

一般我们认为mysql应该先执行where子句中的子查询：
SELECT shop_goods.goodsId from shop_goods WHERE shop_goods.type!='药品'
然后才执行外部的查询，但是mysql却将改SQL语句转换成了相关子查询，我们看到内部子句其实和外部的查询是不相关的！
其执行计划如下：

mysql> explain SELECT role_goods.id,role_goods.number,roleId,role.`name` ,goods.`name` as '物品名称'
    -> from role_goods
    -> LEFT JOIN role on role.id =role_goods.roleId
    -> LEFT JOIN goods on goods.id=role_goods.goodsId
    -> WHERE goodsId in (SELECT shop_goods.goodsId from shop_goods WHERE shop_goods.type!='药品' ) ORDER BY number DESC;
+----+--------------------+------------+----------------+---------------+-------------+---------+------------------------+--------+-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+------------+----------------+---------------+-------------+---------+------------------------+--------+-----------------------------+
| 1 | PRIMARY | role_goods | ALL | NULL | NULL | NULL | NULL | 129104 | Using where; Using filesort |
| 1 | PRIMARY | role | eq_ref | PRIMARY | PRIMARY | 8 | xcb.role_goods.roleId | 1 | |
| 1 | PRIMARY | goods | eq_ref | PRIMARY,id | PRIMARY | 4 | xcb.role_goods.goodsId | 1 | |
| 2 | DEPENDENT SUBQUERY | shop_goods | index_subquery | idx_goodsId | idx_goodsId | 5 | func | 1 | Using where |
+----+--------------------+------------+----------------+---------------+-------------+---------+------------------------+--------+-----------------------------+

受影响的行: 0
时间: 52.469ms
运行时间长达52秒！

优化1：

select role_goods.id,role_goods.number,roleId,role.`name` ,goods.`name` as '物品名称'
from role_goods
LEFT JOIN role on role.id =role_goods.roleId
LEFT JOIN goods on goods.id=role_goods.goodsId
inner join (SELECT shop_goods.goodsId from shop_goods WHERE shop_goods.type!='药品') a on role_goods.goodsId=a.goodsId
ORDER BY number DESC;

执行计划：

mysql> explain select role_goods.id,role_goods.number,roleId,role.`name` ,goods.`name` as '物品名称'
    -> from role_goods
    -> LEFT JOIN role on role.id =role_goods.roleId
    -> LEFT JOIN goods on goods.id=role_goods.goodsId
    -> inner join (SELECT shop_goods.goodsId from shop_goods WHERE shop_goods.type!='药品') a on role_goods.goodsId=a.goodsId
    -> ORDER BY number DESC;
+----+-------------+------------+--------+---------------+---------+---------+------------------------+------+---------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+--------+---------------+---------+---------+------------------------+------+---------------------------------+
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 289 | Using temporary; Using filesort |
| 1 | PRIMARY | role_goods | ref | goodsId | goodsId | 5 | a.goodsId | 465 | Using where |
| 1 | PRIMARY | role | eq_ref | PRIMARY | PRIMARY | 8 | xcb.role_goods.roleId | 1 | |
| 1 | PRIMARY | goods | eq_ref | PRIMARY,id | PRIMARY | 4 | xcb.role_goods.goodsId | 1 | |
| 2 | DERIVED | shop_goods | ALL | NULL | NULL | NULL | NULL | 351 | Using where |
+----+-------------+------------+--------+---------------+---------+---------+------------------------+------+---------------------------------+

运行时间：
受影响的行: 0
时间: 1.125ms

优化2：

select role_goods.id,role_goods.number,roleId,role.`name` ,goods.`name` as '物品名称'
from role_goods
LEFT JOIN role on role.id =role_goods.roleId
LEFT JOIN goods on goods.id=role_goods.goodsId
inner join shop_goods on role_goods.goodsId=shop_goods.goodsId and shop_goods.type!='药品'
ORDER BY number DESC

执行计划：

mysql> explain select role_goods.id,role_goods.number,roleId,role.`name` ,goods.`name` as '物品名称'
    -> from role_goods
    -> LEFT JOIN role on role.id =role_goods.roleId
    -> LEFT JOIN goods on goods.id=role_goods.goodsId
    -> inner join shop_goods on role_goods.goodsId=shop_goods.goodsId and shop_goods.type!='药品'
    -> ORDER BY number DESC;

+----+-------------+------------+--------+---------------+---------+---------+------------------------+------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+--------+---------------+---------+---------+------------------------+------+----------------------------------------------+
| 1 | SIMPLE | shop_goods | ALL | NULL | NULL | NULL | NULL | 351 | Using where; Using temporary; Using filesort |
| 1 | SIMPLE | role_goods | ref | goodsId | goodsId | 5 | xcb.shop_goods.goodsId | 164 | Using where |
| 1 | SIMPLE | role | eq_ref | PRIMARY | PRIMARY | 8 | xcb.role_goods.roleId | 1 | |
| 1 | SIMPLE | goods | eq_ref | PRIMARY,id | PRIMARY | 4 | xcb.role_goods.goodsId | 1 | |
+----+-------------+------------+--------+---------------+---------+---------+------------------------+------+----------------------------------------------+

运行时间：
受影响的行: 0
时间: 1.171ms
继续优化：alter table shop_goods add index idx_goodsId_type(goodsId,type);

mysql> explain select role_goods.id,role_goods.number,roleId,role.`name` ,goods.`name` as '物品名称'
    -> from role_goods
    -> LEFT JOIN role on role.id =role_goods.roleId
    -> LEFT JOIN goods on goods.id=role_goods.goodsId
    -> inner join shop_goods on role_goods.goodsId=shop_goods.goodsId and shop_goods.type!='药品'
    -> ORDER BY number DESC;
+----+-------------+------------+--------+------------------+------------------+---------+------------------------+------+-----------------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+--------+------------------+------------------+---------+------------------------+------+-----------------------------------------------------------+
| 1 | SIMPLE | shop_goods | index | idx_goodsId_type | idx_goodsId_type | 773 | NULL | 239 | Using where; Using index; Using temporary; Using filesort |
| 1 | SIMPLE | role_goods | ref | goodsId | goodsId | 5 | xcb.shop_goods.goodsId | 184 | Using where |
| 1 | SIMPLE | role | eq_ref | PRIMARY | PRIMARY | 8 | xcb.role_goods.roleId | 1 | |
| 1 | SIMPLE | goods | eq_ref | PRIMARY,id | PRIMARY | 4 | xcb.role_goods.goodsId | 1 | |
+----+-------------+------------+--------+------------------+------------------+---------+------------------------+------+-----------------------------------------------------------+

shop_goods使用了覆盖索引。


优化1和优化2都将where中的子查询转换成了join，性能得到了极大的提高。

总结：mysql where子句中的子查询最好改写成使用join来处理。