SQL优化

一、limit

select * from test1 order by id  limit  99999,10

优化后

select * from test1 where id >=100000 order by id  limit 10

二、count(*)

优化后

count(辅助索引)

count(distinct)

三、or

select * from user where name='id' or age = 41

优化后

select * from user where name='d' union all select * from user where age=41

四、not in

select SQL_NO_CACHE count(*) from test1 where id not in (select id from test2)

优化后

select SQL_NO_CACHE count(*) from test1 left join test2 on test1.id = test2.id where test2.id is null;

五、使用 ON duplicate key update 子句

INSERT　INTO　up_relation(OwnerId,ContactId,IsBuddy,IsChatFriend,IsBlackList) VALUES (v_UserId,v_ContactId,1,0,0) ON DUPLICATE KEY UPDATE IsBudd=1,IsChatFriend=0;

六、like “%xxx%”

select count (*) from artist where name like '%Queen%';

优化后(只要SELECT字段正好就是索引，那就就用到了覆盖索引)

select count (*) from artist  a join (select artist_id from artist where name like '%Queen%') b on a.artist_id = b.artist_id