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