使用join时,应当用小结果集驱动大结果集
例: user表10000条数据,class表20条数据
select * from user u left join class c u.userid=c.userid
这样则需要用user表循环10000次才能查询出来,而如果用class表驱动user表则只需要循环20次就能查询出来
例:
select * from class c left join user u c.userid=u.userid
limit 的基数比较大时使用 between
原来语句:select * from admin order by admin_id limit 100000,10
优化为: select * from admin where admin_id between 100000 admin 100010 order by admin_id
尽量避免在列上做运算,这样导致索引失效
例如:select * from admin where year(admin_time)>2014
优化为: select * from admin where admin_time> '2014-01-01