sql优化

一.mysql 优化之is null ,is not null 索引使用测试

　　1.创建t_user表，在name字段创建索引,且name字段不能为null。

　　EXPLAIN select * from t_user where name is not null;//不使用索引；

　　EXPLAIN select * from t_user where name is null;//不使用索引；

　　EXPLAIN select name from t_user where name is not null;//使用索引；

　　EXPLAIN select name from t_user where name is null;//不使用索引；

　　EXPLAIN select name ,age from t_user where name is not null;//不使用索引

　　EXPLAIN select name,age from t_user where name is null;//不使用索引

　　结论：当索引字段不可以为null时，只有使用is not null 并且返回的结果集中制包含索引字段的时，才使用索引。

　　2.创建t_user表，在name字段创建索引,且name字段可以为null.

　　EXPLAIN select * from t_user where name is not null;//使用索引；

　　EXPLAIN select * from t_user where name is null;//使用索引；

　　EXPLAIN select name from t_user where name is not null;//使用索引；

　　EXPLAIN select name from t_user where name is null;//使用索引；

　　EXPLAIN select name ,age from t_user where name is not null;//使用索引

　　EXPLAIN select name,age from t_user where name is null;//使用索引

　　结论：当索引字段可以为null,使用is null,is not null 不影响覆盖索引，is null 的性能高于is not null 性能。

2.in 和exists效率问题

　　in 是把外表和内表作hash连接，而exists是对外表作loop循环，每次loop循环再对内标进行查询。

　　一直以来认为exists 比in效率高的说法不准确的。

　　如果查询的两个表大小相当，那么用in和exists差别不大。

　　如果两个表中一个表笑，一个是大表，则子查询表大的用exists，反之用in；

　　如表A（小表），表B（大表）

　　1.select * from A where cc in (select cc from B) //效率低，用到了A表上cc列的索引

　　   select * from A where exists (select cc from B where cc = A.cc)//效率高，用到了B表上cc列的索引

　　2.select * from B where cc in (select cc from A) //效率高，用到了B表上cc列的索引；

　　   select * from B where exists (select cc from A where cc = B.cc)//效率低，用到了A表上cc列的索引

　　not in 和not exists

　　如果查询预警使用了not in ,那么内标都要进行全表扫描，没有用到索引；

　　而not exists 的子查询依然能用到表上的索引；

三.索引最左原则

　　1.使用联合索引的时候，最左边的索引才会生效；

　　2.使用索引字段模糊搜索时，最左边不能使用模糊通配符，如 like ‘ABC%’ 

四.大表数据查询优化

　　当使用limit m,n分页，m特别大的时候，效率会非常低，可以子查询优化，减少回表操作  

　　select * from table a inner join ( select id from table limit m,n) as b on a.id = b.id  （id 为主键）