mysql 语句优化心得

排序导致性能较慢

优化策略：1.尽量不使用排序 2.只查有索引的结果然后 内连接查询

select  bizchance0_.*  from biz_chance bizchance0_, biz_bizcustomer bizbizcust1_
where bizchance0_.uuid=bizbizcust1_.recordinfoid and bizchance0_.ispublic=1          order by bizchance0_.orderkey desc limit 0,10;

时间 33秒  order by 排序性能较慢 原因：select  bizchance0_.*   如果只查select bizchance0_.uuid uuid带索引 性能提高



select bizchance0_.uuid as uid  from biz_chance bizchance0_, biz_bizcustomer bizbizcust1_
where bizchance0_.uuid=bizbizcust1_.recordinfoid and bizchance0_.ispublic=1   order by bizchance0_.orderkey desc     limit 0,10 ;
时间 3秒


select * from biz_chance as uu inner join (select bizchance0_.uuid as uid  from biz_chance bizchance0_, biz_bizcustomer bizbizcust1_
where bizchance0_.uuid=bizbizcust1_.recordinfoid and bizchance0_.ispublic=1   order by bizchance0_.orderkey desc     limit 0,10   ) as u  on  u.uid=uu.uuid

inner join biz_bizcustomer as cus on uu.uuid=cus.recordinfoid

综合 语句


2.筛选条件、顺序不对

select * from biz_customer  as cus

left join biz_config400 as conf on conf.customerid=cus.uuid
left join biz_billinginfo as bill on bill.configid=conf.uuid and bill.customerid=cus.uuid

用时 15秒

原因：“and bill.customerid=cus.uuid ”

优化结果

select cus.* from biz_customer  as cus

left join biz_config400 as conf on conf.customerid=cus.uuid
left join biz_billinginfo as bill on bill.configid=conf.uuid

where  bill.customerid=cus.uuid or bill.uuid is null

或者

select cus.* from biz_customer  as cus

left join biz_config400 as conf on conf.customerid=cus.uuid
left join biz_billinginfo as bill on bill.configid=conf.uuid