mysql 语句优化

1、不用select * 消耗cpu，io，内存，带宽;这种程序不具有扩展性；

2、OR改写为IN()    or的效率是n级别； in的消息是log(n)级别；in的个数建议控制在200以

内；      select id from t where phone=’159′ or phone=’136′;      =>      select id from t where phone in (’159′, ’136′);

OR改写为UNION    mysql的索引合并很弱智     select id from t where phone = ’159′ or name = ‘john’;     =>     select id from t where phone=’159′     union     select id from t where name=’jonh’

3、IN和EXISTS

EXISTS要远比IN的效率高。里面关系到full table scan和range scan。几乎所有的IN操作符子查询都可以改写为使用EXISTS的子查询。

SELECT dname, deptno FROM dept
WHERE deptno NOT IN
(SELECT deptno FROM emp);

改为

SELECT dname, deptno FROM dept
WHERE NOT EXISTS
(SELECT deptno FROM emp
WHERE dept.deptno = emp.deptno);

因为1中对emp进行了full table scan,这是很浪费时间的操作。而且1中没有用到emp的INdex，
因为没有WHERE子句。而2中的语句对emp进行的是range scan。

4、避免负向%

5、慎用count(*)

6、limit高效分页    limit越大，效率越低    select id from t limit 10000, 10;    =>    select id from t where id > 10000 limit 10;

7、使用union all替代union    union有去重开销

8、少用连接join

9、使用group by    分组；

10、请使用同类型比较

11、使用load data导数据    load data比insert快约20倍；

12、打散批量更新

13、新能分析工具    show profile;    mysqlsla;    mysqldumpslow;    explain;    show slow log;    show processlist;    show query_response_time(percona);

14、避免相关查询
        一个列的标签同时在主查询和WHERE子句中的查询中出现，那么很可能当主查询中的列值改变之后，子查询必须重新查询一次。查询嵌套层次越多，效率越低，因此应当尽量避免子查询。如果子查询不可避免，那么要在子查询中过滤掉尽可能多的行。

 

15、limit千万级分页的时候优化。

A．在我们平时用limit,如：

Select * from A order by id limit 1,10;

这样在表数据很少的时候，看不出什么性能问题，倘若到达千万级，如：

Select * from A order by id limit10000000,10;

虽然都是只查询10记录，但是这个就性能就让人受不了了。所以为什么当表数据很大的时候，我们还继续用持久层框架如hibernate,ibatis就会有一些性能问题，除非持久层框架对这些大数据表做过优化。

B．在遇见上面的情况，我们可以用另外一种语句优化，如：

Select * from A where id>=(Select idfrom a limit 10000000,1) limit 10;

确实这样快了很多，不过前提是，id字段建立了索引。也许这个还不是最优的，其实还可以这样写：

Select * from A where id between 10000000and 10000010;

这样的效率更加高。