Chinaunix首页 | 论坛 | 博客
  • 博客访问: 156073
  • 博文数量: 39
  • 博客积分: 2075
  • 博客等级: 大尉
  • 技术积分: 420
  • 用 户 组: 普通用户
  • 注册时间: 2010-01-26 15:55
文章分类

全部博文(39)

文章存档

2014年(5)

2013年(1)

2012年(1)

2011年(12)

2010年(24)

我的朋友

分类: Mysql/postgreSQL

2014-06-19 08:48:38

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

点击(此处)折叠或打开

  1. SELECT role_goods.id,role_goods.number,roleId,role.`name` ,goods.`name` as '物品名称'
  2. from role_goods
  3. LEFT JOIN role on role.id =role_goods.roleId
  4. LEFT JOIN goods on goods.id=role_goods.goodsId
  5. 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语句转换成了相关子查询,我们看到内部子句其实和外部的查询是不相关的!
其执行计划如下:

点击(此处)折叠或打开

  1. mysql> explain SELECT role_goods.id,role_goods.number,roleId,role.`name` ,goods.`name` as '物品名称'
  2.     -> from role_goods
  3.     -> LEFT JOIN role on role.id =role_goods.roleId
  4.     -> LEFT JOIN goods on goods.id=role_goods.goodsId
  5.     -> WHERE goodsId in (SELECT shop_goods.goodsId from shop_goods WHERE shop_goods.type!='药品' ) ORDER BY number DESC;
  6. +----+--------------------+------------+----------------+---------------+-------------+---------+------------------------+--------+-----------------------------+
  7. | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
  8. +----+--------------------+------------+----------------+---------------+-------------+---------+------------------------+--------+-----------------------------+
  9. | 1 | PRIMARY | role_goods | ALL | NULL | NULL | NULL | NULL | 129104 | Using where; Using filesort |
  10. | 1 | PRIMARY | role | eq_ref | PRIMARY | PRIMARY | 8 | xcb.role_goods.roleId | 1 | |
  11. | 1 | PRIMARY | goods | eq_ref | PRIMARY,id | PRIMARY | 4 | xcb.role_goods.goodsId | 1 | |
  12. | 2 | DEPENDENT SUBQUERY | shop_goods | index_subquery | idx_goodsId | idx_goodsId | 5 | func | 1 | Using where |
  13. +----+--------------------+------------+----------------+---------------+-------------+---------+------------------------+--------+-----------------------------+
受影响的行: 0
时间: 52.469ms
运行时间长达52秒!

优化1:

点击(此处)折叠或打开

  1. select role_goods.id,role_goods.number,roleId,role.`name` ,goods.`name` as '物品名称'
  2. from role_goods
  3. LEFT JOIN role on role.id =role_goods.roleId
  4. LEFT JOIN goods on goods.id=role_goods.goodsId
  5. inner join (SELECT shop_goods.goodsId from shop_goods WHERE shop_goods.type!='药品') a on role_goods.goodsId=a.goodsId
  6. ORDER BY number DESC;
执行计划:

点击(此处)折叠或打开

  1. mysql> explain select role_goods.id,role_goods.number,roleId,role.`name` ,goods.`name` as '物品名称'
  2.     -> from role_goods
  3.     -> LEFT JOIN role on role.id =role_goods.roleId
  4.     -> LEFT JOIN goods on goods.id=role_goods.goodsId
  5.     -> inner join (SELECT shop_goods.goodsId from shop_goods WHERE shop_goods.type!='药品') a on role_goods.goodsId=a.goodsId
  6.     -> ORDER BY number DESC;
  7. +----+-------------+------------+--------+---------------+---------+---------+------------------------+------+---------------------------------+
  8. | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
  9. +----+-------------+------------+--------+---------------+---------+---------+------------------------+------+---------------------------------+
  10. | 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 289 | Using temporary; Using filesort |
  11. | 1 | PRIMARY | role_goods | ref | goodsId | goodsId | 5 | a.goodsId | 465 | Using where |
  12. | 1 | PRIMARY | role | eq_ref | PRIMARY | PRIMARY | 8 | xcb.role_goods.roleId | 1 | |
  13. | 1 | PRIMARY | goods | eq_ref | PRIMARY,id | PRIMARY | 4 | xcb.role_goods.goodsId | 1 | |
  14. | 2 | DERIVED | shop_goods | ALL | NULL | NULL | NULL | NULL | 351 | Using where |
  15. +----+-------------+------------+--------+---------------+---------+---------+------------------------+------+---------------------------------+
运行时间:
受影响的行: 0
时间: 1.125ms

优化2:

点击(此处)折叠或打开

  1. select role_goods.id,role_goods.number,roleId,role.`name` ,goods.`name` as '物品名称'
  2. from role_goods
  3. LEFT JOIN role on role.id =role_goods.roleId
  4. LEFT JOIN goods on goods.id=role_goods.goodsId
  5. inner join shop_goods on role_goods.goodsId=shop_goods.goodsId and shop_goods.type!='药品'
  6. ORDER BY number DESC
执行计划:

点击(此处)折叠或打开

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

  7. +----+-------------+------------+--------+---------------+---------+---------+------------------------+------+----------------------------------------------+
  8. | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
  9. +----+-------------+------------+--------+---------------+---------+---------+------------------------+------+----------------------------------------------+
  10. | 1 | SIMPLE | shop_goods | ALL | NULL | NULL | NULL | NULL | 351 | Using where; Using temporary; Using filesort |
  11. | 1 | SIMPLE | role_goods | ref | goodsId | goodsId | 5 | xcb.shop_goods.goodsId | 164 | Using where |
  12. | 1 | SIMPLE | role | eq_ref | PRIMARY | PRIMARY | 8 | xcb.role_goods.roleId | 1 | |
  13. | 1 | SIMPLE | goods | eq_ref | PRIMARY,id | PRIMARY | 4 | xcb.role_goods.goodsId | 1 | |
  14. +----+-------------+------------+--------+---------------+---------+---------+------------------------+------+----------------------------------------------+
运行时间:
受影响的行: 0
时间: 1.171ms
继续优化:alter table shop_goods add index idx_goodsId_type(goodsId,type);

点击(此处)折叠或打开

  1. mysql> explain select role_goods.id,role_goods.number,roleId,role.`name` ,goods.`name` as '物品名称'
  2.     -> from role_goods
  3.     -> LEFT JOIN role on role.id =role_goods.roleId
  4.     -> LEFT JOIN goods on goods.id=role_goods.goodsId
  5.     -> inner join shop_goods on role_goods.goodsId=shop_goods.goodsId and shop_goods.type!='药品'
  6.     -> ORDER BY number DESC;
  7. +----+-------------+------------+--------+------------------+------------------+---------+------------------------+------+-----------------------------------------------------------+
  8. | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
  9. +----+-------------+------------+--------+------------------+------------------+---------+------------------------+------+-----------------------------------------------------------+
  10. | 1 | SIMPLE | shop_goods | index | idx_goodsId_type | idx_goodsId_type | 773 | NULL | 239 | Using where; Using index; Using temporary; Using filesort |
  11. | 1 | SIMPLE | role_goods | ref | goodsId | goodsId | 5 | xcb.shop_goods.goodsId | 184 | Using where |
  12. | 1 | SIMPLE | role | eq_ref | PRIMARY | PRIMARY | 8 | xcb.role_goods.roleId | 1 | |
  13. | 1 | SIMPLE | goods | eq_ref | PRIMARY,id | PRIMARY | 4 | xcb.role_goods.goodsId | 1 | |
  14. +----+-------------+------------+--------+------------------+------------------+---------+------------------------+------+-----------------------------------------------------------+
shop_goods使用了覆盖索引。


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

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

阅读(654) | 评论(0) | 转发(0) |
0

上一篇:分层数据

下一篇:没有了

给主人留下些什么吧!~~