SQL 优化 - 01

 

select
	*
from
	user_order_statistics s
left join user u on
	s.user_id = u.id
left join distributors d on
	s.user_id = d.userid
where
	s.valid = 1 
order by
	u.create_time desc
limit 50000,
15

优化前:2.5s

原因分析:慢在order by   u.create_time desc这里,主表需要关联几乎整个user表,并在将u.create_time排序后返回15条结果

优化措施:user表的create_time加索引,并在where中可以使用u.create_time让其排序时走索引

select
	*
from
	user_order_statistics s
left join user u on
	s.user_id = u.id
left join distributors d on
	s.user_id = d.userid
where
	s.valid = 1 
  and u.create_time>20150101
order by
	u.create_time desc
limit 50000,
15

 

优化后:0.3s