sql语句优化总结

先前一直对sql语句优化没有太在乎，最近网站用户量增加，发现数据库压力很大自己写的语句啊，新来的架构师对我们语句一一优化，我总结如下（不断更新。。。）
（1）

优化前的语句：

# Query_time: 5.967435 Lock_time: 0.000129 Rows_sent: 1 Rows_examined: 803401 
SET timestamp=1286843575; 
select livemessag0_.id as id38_, livemessag0_.isactive as isactive38_, livemessag0_.content as content38_, livemessag0_.createtime as createtime38_, livemessag0_.userid as userid38_, livemessag0_.objectid as objectid38_, livemessag0_.recordid as recordid38_, livemessag0_.type as type38_ from live_message livemessag0_ where (livemessag0_.objectid in (select livescrip1_.id from live_scrip livescrip1_ where livescrip1_.senderid='ff8080812aebac2d012aef6491b3666d')) and livemessag0_.type=2 limit 6; 

优化后的语句：

select livemessag0_.id as id38_,
        livemessag0_.isactive as isactive38_,
        livemessag0_.content as content38_,
        livemessag0_.createtime as createtime38_,
        livemessag0_.userid as userid38_,
        livemessag0_.objectid as objectid38_,
        livemessag0_.recordid as recordid38_,
        livemessag0_.type as type38_
 from live_scrip livescrip1_ left join 
  live_message livemessag0_
  on livescrip1_.id=livemessag0_.objectid
  where livescrip1_.senderid = 'ff8080812aebac2d012aef6491b3666d' and
       livemessag0_.type = 2
 limit 6;

[b]总结：尽量少用子查询用表连接的方式代替（如果表连接不是太复杂的话），这样优化后大概能减少1/3的时间，后来发现livemessag0_.objectid竟然没有建立索引，晕菜[/b]
（2）

优化前的慢查询：

SELECT r1.id,r1.username,r1.nickname,r1.img_name,r1.datecreated FROM acegi_users AS r1 JOIN (SELECT ROUND(RAND() *(SELECT MAX(num_id) FROM acegi_users)) AS id) AS r2 WHERE r1.num_id >= r2.id and r1.id not in(select r.user_id from user_relations r where r.relation_user_id='ff8080812ad5ad7f012ad5aee5150005') and r1.id!='ff8080812ad5ad7f012ad5aee5150005' and r1.role='expert' ORDER BY r1.num_id limit 3; 

优化后的sql：

SELECT r1.id,
       r1.username,
       r1.nickname,
       r1.img_name,
       r1.datecreated
FROM acegi_users AS r1
     JOIN 
     (SELECT ROUND(RAND() *(SELECT MAX(num_id) FROM acegi_users)) AS id) AS r2
WHERE r1.num_id >= r2.id 

and
       not EXISTS (select r0.user_id from (select r.user_id from user_relations r where
       r.relation_user_id = 'ff8080812ad5ad7f012ad5aee5150005' union select 'ff8080812ad5ad7f012ad5aee5150005') r0 where r0.user_id=r1.id) 
      and
      r1.role = 'expert'
ORDER BY r1.num_id
limit 3;

[b]总结：少用not in 用not EXISTS代替之[/b]