需求描述:压力测试有两张表,主从表,每个表100万以上。现在要两个表关联并进行group by分组。
未优化sql:
select b.supplier_name,b.supplier_code,b.currency_id
from t_outsourcing_dtl a
left join t_outsourcing b on b.code=a.code AND b.del_flag=a.del_flag
where a.state=“F” and a.del_flag= ‘0’
GROUP BY b.supplier_name,b.supplier_code,b.currency_id
这个查询耗时170多秒。怎么办呢?这么长时间领导肯定骂娘!你一定要相信千万级以下都能够用索引解决,为什么看我写的mysql原理。
首先我们用EXPLAIN 看一下执行计划,我们可以看到a 表并没有走索引全表扫!(由于涉及公司业务表就不再说业务场景了,直接说我采坑的经历吧。)
这里还有个小技巧初学者可以玩一下。我们可以在上面的sql下加上
EXPLAIN select b.supplier_name,b.supplier_code,b.currency_id
from t_outsourcing_dtl a
left join t_outsourcing b on b.code=a.code AND b.del_flag=a.del_flag
where a.state=“F” and a.del_flag= ‘0’
GROUP BY b.supplier_name,b.supplier_code,b.currency_id;
SHOW WARNINGS
sql底层会将你的sql重新优化:但咱们可以看到我们的sql,没有语法上优化的问题,如果有它就会给出优化后的sql,这里你们自己玩一下。
select mes_test
.b
.supplier_name
AS supplier_name
,mes_test
.b
.supplier_code
AS supplier_code
,mes_test
.b
.currency_id
AS currency_id
from mes_test
.t_outsourcing_dtl
a
left join mes_test
.t_outsourcing
b
on(((mes_test
.b
.del_flag
= mes_test
.a
.del_flag
) and (mes_test
.b
.code
= mes_test
.a
.code
))) where ((mes_test
.a
.state
= ‘F’) and (mes_test
.a
.del_flag
= ‘0’))
group by mes_test
.b
.supplier_name
,mes_test
.b
.supplier_code
,mes_test
.b
.`currency_id
继续说问题的解决思路:
在不改变sql的情况下,我在这两个表上试了几个聚合索引但效果都不好!因为我这个从表有50万符合主表关联关系的数据。那么mysql索引加的再好也是作用在50万的临时表,进行groupby。所以我换了一种思路。
那就是分表优化然后再合并在一起!直接上代码!
select supplier_code,supplier_name,currency_id from
(select del_flag,code,supplier_code,supplier_name,currency_id
from t_outsourcing
where del_flag=0
group by del_flag,code,supplier_code,supplier_name,currency_id) a
join
(select DISTINCT state, code, recv_time from t_outsourcing_dtl where state=‘F’ and recv_time>‘2013-08-03’) b on a.code=b.code
执行计划:
数据是用阿里的工具自动插入所以看上去没意义。
总结:工作思路就是先加索引,能够优化到秒出就可以了,不能就将数据超出多的占执行计划多的那张表,建索引进行数据筛选,然后再关联在一起。
mysql索引实例
最新推荐文章于 2024-04-20 10:57:24 发布