关于误解

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

例子

• 描述

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

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

• 需解决的问题：

假设今天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的速度，不如说是因为有了索引，数据有序，方便了分组。