Chinaunix首页 | 论坛 | 博客
  • 博客访问: 407242
  • 博文数量: 55
  • 博客积分: 0
  • 博客等级: 民兵
  • 技术积分: 3458
  • 用 户 组: 普通用户
  • 注册时间: 2014-05-18 20:37
个人简介

哈哈

文章分类

全部博文(55)

分类: Mysql/postgreSQL

2014-06-18 23:36:32

项目中一条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来处理。

阅读(2274) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~