mysql查询优化技巧

工作中，经常有运营需求用于统计线上数据，实际场景中两个百万级表联合查询，往往会大大消耗查询时间。
针对此问题，解决思路可以将sql，拆分，可利用中间表方式，对数据进行统计。

例如：订单表 t_order,订单收货表t_order_shipping,会员表 t_member;
数据需求：获取会员表中大于二十岁的手机号，排除2017年已支付订单的手机号。

根据此需求，我们可以写出如下sql：

select distinct m.mobile_phone
from t_member m
where m.age >20
and m.mobile_phone not in (
   select distinct s.mobile_phone
   from t_order o, t_order_shipping
   where o.order_id = s.order_id
   and 0.status ='已支付';
);

当t_member 千万级别，t_order和t_order_shipping百万级别数据量时，以上查询将需要非常多时间，大量的时间都用在了复制临时表和发送查询结果上。
经测试，mobile_phone加索引的请求下，该sql大概执行半小时。

优化方案：
1、可使用关联查询代替子查询，避免临时表复制

select distinct m.mobile_phone
from t_member m
left join (
   select distinct s.mobile_phone
   from t_order o, t_order_shipping
   where o.order_id = s.order_id
   and 0.status ='已支付'
) t
on m.mobile_phone = t.mobile_phone
where t.mobile_phone is null;

2、拆分sql，增加中间表；
统计需求，需要对两组数据数进行去重过滤，我们可以分别单独写sql，将两个数据将入中间表tmp_member和tmp_order中：

CREATE TABLE tmp_member SELECT
    mobile_phone
FROM
    t_member
WHERE
    age >20;

CREATE TABLE tmp_order 
select distinct s.mobile_phone
   from t_order o, t_order_shipping
   where o.order_id = s.order_id
   and 0.status ='已支付'

给临时表tmp_member和tmp_order的mobile_phone字段添加索引；

ALTER TABLE tmp_member ADD INDEX idx_mobile_phone (mobile_phone);

ALTER TABLE tmp_order ADD INDEX idx_mobile_phone (mobile_phone);

从tmp_member表中排除和tmp_order相同的电话号码，剩下的就是需要统计的电话号码；

DELETE tmp_member  FROM tmp_member , tmp_order WHERE tmp_member .mobile_phone= tmp_order .mobile_phone;

此方案，将大的查询结果，拆分成多个sql执行，大大提升执行时间；
亲测，此sql执行不超过一分钟。