子查询 join 带来的问题

关于误解

一直认为,如果在查询时,如果能把各个表的通过子查询限制在一定范围内再关联性能会更好,可是忽略了一个事实,就是子查询的结果存储成临时表,不再使用索引,如果对于一个年数据在千万量级的两个表进行关联,即使求单月内的数据限制时间变成了百万级的表,而子查询连之后是O(N*M)级别问题,那么百万*百万的数量就很客观了。

例子

  • 描述

有一张电商公司的商品订单表order,假设每笔订单只买一种商品,结构如下:

字段 备注
orderid 订单id
userid 用户id
dt 下单时间
typeid 商品类型id

有一个商品类型的促销表 promotion,结构如下:

字段 备注
typeid 商品类型id(如,家居、图书)
discount 对于商品的促销信息(如,家居类8折,图书类7折)
  • 需解决的问题:

假设今天2018-03-23,针对过去1个月有够买记录,而最近1个星期没有够买记录的用户,根据该类型用户过去的够买记录,推送对应商品的促销信息。如,近期用户够买过图书和家居类产品,则推送 "尊敬的用户:家居类8折,图书类7折,欢迎选购" 。

  • 限定符合促销条件用户部分的SQL
select last_month_buy.userid from
(select distinct userid from order where dt>=date_add('2018-03-23',-30)) last_month_buy
left join 
(select distinct userid from order where dt>=date_add('2018-03-23',-7)) last_week_buy
on last_month_buy.userid=last_week_buy.userid
where last_week_buy.userid is null

第一直觉的写法,这个应该是大家都能想到的,缺点:得遍历两边order表,最重要的是这样的子查询之后join 是没法用到userid的索引哒!!!(即使给order表的userid设置了索引也白费,子查询的临时表没有索引信息的)

有没有遍历一遍就能筛选符合条件用户的方法? 看下面

select userid from order group by userid 
having count(if(dt>=date_add('2018-03-23',-30),orderid,null))>0 and count(if(dt>=date_add('2018-03-23',-7),orderid,null))<1

其实可以按照userid分组,然后对于符合条件的订单id技术,只要同时满足最近一个月内订单数大于0且最近一周订单数小于0就可以了。

最重要的是:这里的group by userid用到了索引哦!!!

  • 关联订单和促销信息
select user.userid,promotion.discount
from
( select userid from order group by userid 
having count(if(dt>=date_add('2018-03-23',-30),orderid,null))>0 and count(if(dt>=date_add('2018-03-23',-7),orderid,null))<1
) user
left join 
(select distinct userid,typeid from order where dt>=date_add('2018-03-23',-30)) last_month_buy
on user.userid=last_month_buy.userid
left join promotion 
on last_month_buy.typeid=promotion.typeid

其实到这里,把限定好的user临时表关联last_month_buy临时表和promotion促销信息表,题目需要解决的问题已经完成了。
但是,仔细想想上面的SQL会发现,last_month_buy临时表的方式,又是通过子查询的方式得到,又需要一部分内存,且无索引,虽然把dt时间限制在了一个月以内,减少了关联时的join数量,但因为没有索引,其时间复杂度又回到了本文开头的O(N*M)的状况,如果N的量级无法缩小,但至少可以减少遍历M的量级,
那么怎么减少?当然是索引!对于n条记录的表,假设存放了一年的数据,用索引查询一个目标值的时间复杂度是O(log n),限制一个月数据的last_month_buy子表是n/12条记录。当n上千万的时候,遍历时间复杂度O(n/12) 和O(log n) 比,肯定还是O(log n) 。
那么怎么用到索引?别用子查询了,直接join order表呀! 因join原始order表的时候,on 关联可以用userid的索引。

  • 改进后的SQL
select user.userid,promotion.discount
from
( select userid from order group by userid 
having count(if(dt>=date_add('2018-03-23',-30),orderid,null))>0 and count(if(dt>=date_add('2018-03-23',-7),orderid,null))<1
) user
left join order 
on user.userid=order.userid 
left join promotion 
on order.typeid=promotion.typeid
where order.dt>=date_add('2018-03-23',-30)
group by user.userid,promotion.discount

注意:order里有重复的用户够买类型信息,所以最后group by user.userid,promotion.discount处理一下。

group by 用到索引的实质

索引最绕不开的话题就是排序,包括Btree等数据结构,全部都是以排序为基础的,终于明白了为什么算法课最先讲到的都是排序。而 group by是对数据进行分组,对于一个有序数组,当然分组也更容易,预期说是用到了索引加快了group by的速度,不如说是因为有了索引,数据有序,方便了分组。