业务背景:
- 根据客户群组查询标签,群组和标签的对应关系在tb_biz_type_tags中
- 根据标签查询客户的手机号和机构标识(tb_customer_tags)
- 根据手机号和机构标识,筛选出符合要求的客户信息(tb_account )
已知:客户群组ID
目标:群组中对应的客户信息
原始sql:
select a.* from tb_account a
where a.mobile in(
select b.mobile from tb_customer_tags b where b.tagid in(
select c.tagid from tb_biz_type_tags c where c.id =#{id})) and a.distinguish in (
select b.distinguish from tb_customer_tags b where b.tagid in(
select c.tagid from tb_biz_type_tags c where c.id =#{id}))
因为目标是客户表即tb_account,而已知条件是群组ID,所在在一开始的时候就使用了in语句,好家伙,这一用就用了两层,加上双层的条件过滤,也就是走了两次双层in语句(其实这条语句的逻辑还是有点问题的),执行时间比较长,效率很低,不得不进行优化。
优化后sql:
SELECT
a.id,a.customer_id,a.name,a.age,a.sex,a.mobile
,a.avl_bl,a.sys_user_id,a.distinguish
FROM
tb_account a
left JOIN
(SELECT
b.mobile,b.distinguish,b.tagid
FROM
tb_customer_tags b
right join
( SELECT c.tagid FROM tb_biz_type_tags c
WHERE c.id ='6a09ca0e805011e99b95005056ac679f' ) d
on d.tagid=b.tagid) e
on e.mobile=a.mobile and e.distinguish=a.distinguish where e.tagid is not null
通过对sql进行优化,效率提高了上百倍,一方面原始sql的效率实在他太低,另一方面理论上的优化技巧还是很有作用的,下面会列出具体的优化点
优化关键点
- 指定需要查询的字段,原始sql是直接使用的a.*来查询语句,我的客户表中的字段大概是四十多个,所以这个优化很重要,提速不少。
- 添加索引,给sql语句中的跟查询相关的字段加上索引,能够大幅度提升查询效率,可以使用explain语句查询是否使用索引进行查询。
- 换掉in语句,我使用的是右连接和左连接相结合的方式,从已知条件出发构建sql语句,一定要能清楚的了解表进行连接之后的样子。
TIPS:在进行优化的时候,需要暂时关闭数据库自带的缓存,这种缓存在平时查询时确实是个优势,但是在优化调试sql的时候很有必要关掉,在select后面加上sql_no_cache 即可临时关上数据库缓存