这2条sql,哪条效率高

场景如下
一个奖品表,一个奖品领取记录表
每个奖品有限制每个用户可领取数,0为不限制,要求把某个用户还能领取的奖品显示出来

#奖品表
CREATE TABLE `prize` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
  `name` varchar(20) NOT NULL COMMENT '奖品名称',
  `quota` int(11) NOT NULL COMMENT '每个用户可领取数,0为不限制',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

#领取记录表
CREATE TABLE `record` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
  `uid` int(11) NOT NULL COMMENT '用户id',
  `pid` int(11) NOT NULL COMMENT '领取的奖品id',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

插入模拟数据

INSERT INTO `prize` (`id`, `name`, `quota`) VALUES
(1, '奖品1', 1),
(2, '奖品2', 0),
(3, '奖品3', 2),
(4, '奖品4', 1);
INSERT INTO `record` (`id`, `uid`, `pid`) VALUES
(1, 1, 1),
(2, 1, 2),
(3, 1, 2),
(4, 1, 3),
(5, 1, 3);

用户1领取了奖品1一个,奖品2两个,奖品3两个,那么根据要求他还可以继续领奖品2和奖品4,我摸黑写了2条sql,都能符合要求,所以想请教那条效率高,分析解释下

SELECT * FROM `prize` as a left join  (select COUNT(*) as `count`,pid from `record` where uid=1 GROUP by pid) as c on a.id=c.pid where a.quota=0 or isnull(c.`count`) or a.quota>c.`count`

SELECT * FROM `prize` where  id not in (SELECT pid from (select COUNT(*) as `count`,pid from `record` where uid=1 GROUP by pid) as c where quota <> 0 and c.`count`>=quota)

===============更新==================
搞了5千条奖品记录,模拟了50个用户领取总计10万条领取记录,测试结果
2条sql执行时间没有明显差异,都在0.01s内
放下这2条的执行计划
图片描述

图片描述

顺便放下@凌晨消失の夏日 同学的,他这个非常慢,我的mysql都被卡死了,也不懂为什么会这样
图片描述

以上供各位分析对比

阅读 3.2k
4 个回答

你可以测试一下

都差不多,在计划解释上,你查询领取记录表都是一样的方法(改表应该是数据最大的表).
建议几点:

  1. distinct好过用count

  2. 遇到复杂的条件判断可以使用union

  3. 如果in的集合过大可以用exists代替

个人写法如下:

SELECT *
from prize p
where not exists (select pid, count(uid) from record where uid=1 and p.id=pid GROUP BY pid HAVING count(uid)>=p.quota)
union
SELECT *
from prize
where quota=0;

最好看一下执行计划。
从个人经验判断,更倾向于用第一种写法,第二种IN的写法,可能会导致record表多次查询。

第二个效率更高

撰写回答
你尚未登录,登录后可以
  • 和开发者交流问题的细节
  • 关注并接收问题和回答的更新提醒
  • 参与内容的编辑和改进,让解决方法与时俱进