MySQL not exists 的优化

现有2张表 商品表和 用户购买记录表

create table dr_purchased
(
  id           bigint auto_increment
    primary key,
  user_id      bigint                                  not null comment '用户ID dr_user表ID',
  product_type int                                     not null comment '商品类型',
  product_id   bigint                                  not null comment '产品ID dr_product表ID',
  goods_id     int                                     not null comment '商品ID',
  expire_time  timestamp default '2000-01-01 00:00:00' null comment '产品过期时间',
  platform_id  int                                     null comment '平台类型 1：ios 2：安卓'
)

 

create table dr_product
(
  id           bigint auto_increment comment '商品ID'
    primary key,
  code         varchar(64)  default ''                not null comment '商品编码-IOS应用商店需要这个码',
  name         varchar(200) default ''                not null comment '产品名称',
  type         int          default 0                 not null comment '商品类型',
  goods_id     int          default 0                 not null comment '商品ID',
  price        int                                    not null comment '售价',
  origin_price int                                    null comment '原价',
  sales_volume int          default 0                 null comment '销量',
  remarks      varchar(255) default ''                not null comment '备注',
  online       int          default 1                 not null comment '是否在用'
)
  comment '商品表';

 

现在有一个需求是查找用户未购买的商品， 一般我们会写出这样的查询

select *
from dr_product t
where not exists(select product_id
                 from dr_purchased p
                 where p.user_id = ? 
                   and t.id = p.product_id)

 

现在我们看一下explain

 

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	PRIMARY	t	NULL	ALL	NULL	NULL	NULL	NULL	92	100	Using where
2	DEPENDENT SUBQUERY	p	NULL	ref	dr_purchased_user_id_product_type_goods_id_index	dr_purchased_user_id_product_type_goods_id_index	8	const	25	10	Using where

我们可以看到type为ref确实是使用了索引，但是我们用另外一种写法来试一试。

explain select * from
  dr_product t left join dr_purchased p on
p.user_id=? and  t.id=product_id
where p.id is null;

 

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t	NULL	ALL	NULL	NULL	NULL	NULL	92	100	NULL
1	SIMPLE	p	NULL	ref	dr_purchased_user_id_product_type_goods_id_index	dr_purchased_user_id_product_type_goods_id_index	8	const	25	10	Using where; Not exists

 

我们可以看到执行计划没有什么本质的区别。但是经过测试 连接查询效率更好。

最后重要的事情要再提一遍，SQL优化，一定要具体问题具体分析，没有万能的解决方案