96秒sql语句优化

来源：互联网发布：结婚照相册制作软件编辑：程序博客网时间：2024/05/27 20:38

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

ps：条件中有last_follow_time >-863999998580524166。很明显这个数值是有问题的，这个是程序逻辑错误，可根据业务逻辑继续优化应用端

0 0