select c.*,max(cb.last_follow_time) as last_follow_time,cb.create_time as block_create_time from c_block cb
left join customer c on c.id=cb.customer_id
where cb.staff_id=76 and cb.give_up_time is null and cb.customer_id not in (
select customer_id from bill_staff bs
left join bill_customer bc on bs.b_id=bc.b_id
where bs.staff_id=76 and customer_id is not null
) and cb.last_follow_time >-863999998580524166 and cb.customer_type in (1) and (c.tel like '%18846836939%' or c.id in (
select c_id from c_contact where c_mobile like '%18846836939%' or c_workphone like '%18846836939%'
)) group by cb.customer_id order by max(cb.last_follow_time) desc LIMIT 0,10;
# Query_time: 96.351328 Lock_time: 0.000313 Rows_sent: 10 Rows_examined: 119820131
第1步:分析sql:执行时间96秒,搜索119820131条数据,返回10条,大量检索被浪费。语句中子查询非常多。由查询分析可见,多条子查询均引起笛卡尔积。
第2步:第一个子查询加索引,从4324条检索缩小到2条
ALTER TABLE `c_contact` ADD INDEX(`c_workphone`);
ALTER TABLE `c_contact` ADD INDEX(`c_mobile`);
第3步:第二个子查询加索引
ALTER TABLE `bill_customer` ADD INDEX(`b_id`);
ALTER TABLE `bill_customer` ADD INDEX(`customer_id`);
第4步:第3个子查询加索引,此时可见两个子查询的数量已大福下降
ALTER TABLE `bill_staff` ADD INDEX(`b_id`);
第5步:搜索中电话全部改为精确搜索,此处修改了业务逻辑,搜索电话的情况,很少有模糊搜索,所以直接用全等于。最后搜索时间为0.003秒
select c.*,max(cb.last_follow_time) as last_follow_time,cb.create_time as block_create_time from customer_block cb left join customer c on c.id=cb.customer_id where cb.staff_id=76 and cb.give_up_time is null and cb.customer_id not in (select customer_id from bill_staff bs left join bill_customer bc on bs.b_id=bc.b_id where bs.staff_id=76 and customer_id is not null) and cb.last_follow_time >-863999998580524166 and cb.customer_type in (1) and (c.tel = '18846836939' or c.id in (select c_id from c_contact where c_mobile = '18846836939' or c_workphone = '18846836939')) group by cb.customer_id order by max(cb.last_follow_time) desc