项目中一条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来处理。
阅读(3094) | 评论(0) | 转发(0) |