SQL优化（三）、综合实战：sql优化

查看一个sql的执行计划，起码需要关注table ,type,key,rows,extra.
table当然是没有子表（当然是没有from后，where前这种子表）比较好啦
type从左往右，让我想起一首歌叫越来越好：all , range,index ,sub_query,ref/eq_ref,const/system,null
key当然是sql里真正用到的索引
rows当然是越小越好咯，多的话，时间都浪费在磁盘io读写上了，效率低是一定的啦
extra描述信息：using where & using index 和 using index 比较好啦，不需要回表查询。
using index condition 可以看做是using where 的优化

Using join buffer (Block Nested Loop) ，根据其原理，可以调大join_buffer_size，这样会提升效率，增加到多大为合理，可以去网上查下，不详细描述【 查看 join缓存大小show variable like '%join_buffer_size%';】其值越大，可以减少回表查询数据，提升查询效率。

优化过程中，常用的命令：

创建索引;
create index index_name on table table_name (column1,cloumn,.....);  或
alter table table_name add index index_name(column1,column2....);
删除索引：
drop index index_name on table_name;或
alter table table_name drop index index_name;
查看表创建了哪些索引：
show index from tabname;
查看建表语句:
show create table tabname;

优化建议（mysql版本不同，而且表数据量也不同，具体还是要看执行计划，我的版本是mysql5.6）：
执行explain查看sql的执行计划：

mysql> explain select * from payment order by rental_id desc limit 410,10 \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: payment
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1
        Extra: Using filesort
1 row in set (0.00 sec)

extra上面的值表示：不是通过索引直接返回排序结果的排序都叫filesort排序。所以尽量减少额外的排序，通过索引直接返回有效数据。

如果是字符串类型，允许为空，则is null （执行计划的type=ref） 和is not null(执行计划的type=range) 索引都不会失效，ref 比range 好一些。
如果是时间类型，允许为空，则 :is null 不会让索引失效，is not null 会使索引失效，曲线救国：where not EXISTS (SELECT openTime FROM loan WHERE openTime IS NULL)

以下情况可以使用索引：

有order by 和 where 
where 和order by 使用相同的索引。
order by 的顺序同索引顺序
order by 的字段都是升序或者都是降序。

比如 eg：

select * from tabname order by key_part1,key_part2...;
select * from tabname where key_part1=1 order by key_part1 desc ,key_part2 desc ;
select * from tabname order key_part1 desc ,key_part2 desc ;

下面情况不使用索引;

select * from tabname order by key_part1 desc ,key_part2 asc;  -- 排序字段混合了asc 和 desc
select * from tabname where keys=constant order by key1; --  用于查询行的关键字与order by 中所使用的不相同。
select * from tabname order by key1,key2; -- 对不同的关键字使用order by .

优化嵌套查询

select * from a where a.id not in(select userid from b);
==> select * from a left join b on a.id=b.userid where b.userid is null;

优化or条件

想利用索引，or之间每个条件列都必须用到索引。  如果是联合索引(a,b) ,使用(a='1' or b='2') ,则联合索引不会生效。

优化分页查询
两种方式：

① 1 索引上排序分页2 根据关键字关联回原表查询需要的其他列内容。
select A,B,C,D from a order by A limit 1000,10;
==> select A,B,C,D from a inner join (select A from a order by A limit 1000,10) b on a.A=b.A;

② ==> select  A,B,C,D from a where a.A>1031 order by A limit 10;

– 查询某个库加的索引，我加的都是u1_开头的，比较好找

select * from INFORMATION_SCHEMA.STATISTICS  where TABLE_SCHEMA   in('zkbc') and (index_name  like 'u1_%');