阅读 567

实践分享 - MySQL优化 - 查询去年待流失客户的交易额

背景

业务方要求获取2018年,系统中待流失客户的交易额

数据模型

  • t_customer_pool表存储的是待流失客户,每个月1号都会有定时任务按照规则刷新表中的数据
  • 如果一个客户,一年到头都是待流失客户,那么这个客户在表中会有12条记录

查询SQL

错误例子 V1.0

SELECT
	c.cust_id "客户ID",
	c.cust_name "客户名称",
	sum(o.amount) "交易金额"
FROM
	t_order o
INNER JOIN t_customer c ON o.cust_id = c.cust_id
WHERE
	o.pay_time >= '2018-01-01 00:00:00'
AND o.pay_time <= '2018-12-31 23:59:59'
AND c.cust_id IN (
	SELECT DISTINCT
    	cust_id
	FROM
    	t_customer_pool
	WHERE
    	create_time >= '2018-01-01 00:00:00'
	AND create_time <= '2018-12-31 23:59:59'
)
GROUP BY
	c.cust_id;
复制代码

问题:

  1. 不应该使用t_order表驱动t_customer表(大表join小表),应该使用t_customer表驱动t_order表(小表join大表)
  2. 待流失客户有可能是没有交易的,在t_order表中没有记录,所以应该使用left join

按照存在的问题,去修改,得出错误例子 V2.0

错误例子 V2.0

SELECT
	c.cust_id "客户ID",
	c.cust_name "客户名称",
	sum(o.amount) "交易金额"
FROM
	t_customer c
LEFT JOIN t_order o ON o.cust_id = c.cust_id
AND o.pay_time >= '2018-01-01 00:00:00'
AND o.pay_time <= '2018-12-31 23:59:59'
WHERE
	c.cust_id IN (
    	SELECT DISTINCT
    		cust_id
    	FROM
    		t_customer_pool
    	WHERE
    		create_time >= '2018-01-01 00:00:00'
    	AND create_time <= '2018-12-31 23:59:59'
	)
GROUP BY
	c.cust_id;
复制代码

备注

  • left join某个表后,该表的筛选条件不要写在where条件后面,不然left join 会变成 inner join
  • 信心满满把这条SQL交给DBA执行(测试环境验证通过),结果DBA说SQL执行了很长时间没得出结果,只能kill掉
  • 线上环境的数据量是测试环境的几十倍,数据量一上来,隐藏问题就被放大了

问题:

  1. t_order表会被全表扫描,应该使用子查询,先查出2018年内的订单
  2. join表的on语句上最好只添加连接表的条件,筛选条件写在where后面,避免写在on条件后面
  3. group by 是先分组,后排序,应该加上order by null

正确例子

SELECT
	c.cust_id "客户ID",
	c.cust_name "客户名称",
	sum(o.amount) "交易金额"
FROM
	t_customer c
LEFT JOIN (
	SELECT
    	order_id,
    	amount,
    	cust_id
	FROM
    	t_order
	WHERE
    	pay_time >= '2018-01-01 00:00:00'
	AND pay_time <= '2018-12-31 23:59:59'
) o ON o.cust_id = c.cust_id
WHERE
	c.cust_id IN (
    	SELECT DISTINCT
    		cust_id
    	FROM
    		t_customer_pool
    	WHERE
    		create_time >= '2018-01-01 00:00:00'
    	AND create_time <= '2018-12-31 23:59:59'
	)
GROUP BY
	c.cust_id
ORDER BY
	NULL;
复制代码

如果大家有更好的方法,欢迎在文章下面评论

关注下面的标签,发现更多相似文章
评论