索引失效的情况及优化策略

MYSQL避免全表扫描，首先考虑在where及order by涉及的列上建立索引。
导致引擎放弃使用索引而进行全表扫描的情况：
   1、where字句对字段进行null判断。  可以设置默认值为0，确保列中无null值。
   2、where字句使用!= 或<>操作符。
   3、where子句使用OR来连接条件。  可以使用union all代替。
   4、where字句中in 和not in 要慎用
   5、where字句中like尽量以"常量"开头，不要以'%'开头。
                 like '%keyword%' 索引失效,无法使用反向索引。
                 like '%keyword'  索引失效, 使用全表扫描。可以使用反转函数，不走全表扫描,如where reverse(code) like reverse('%Code2')。
                 like 'keyword%'  索引有效,不会造成全表扫描
   6、where字句中 =左边 对字段进行表达式操作，函数操作，算术表达式操作。
   7、在使用索引字段作为条件时，复合索引，字段顺序与索引顺序一致。
   8、exists代替in，是很好的选择。
   9、索引不一定有效，当索引列有大量数据重复时，Sql查询可能不会利用索引。
   10、索引并不是越多越好，最好不要超过6个，索引可以提高select效率，会影响update，insert效率。
   11、尽量使用数字型字段，尽量varchar代替char。
   12、尽量避免使用*。
   13、避免频繁的创建和删除临时表，删除表时，先truncate table，后drop table，可以避免系统表长时间的锁定。
   14、当重复使用一个数据集时可以建临时表。 
       新建临时表时，数据量很大，使用select into代替create table，避免造成大量的log，以提高速度。   
       如果数据量不大，为了缓和系统表资源，应先create table，然后insert。
   15、尽量避免大事务操作，提高系统并发能力。
   16、尽量避免向客户端返回大数据量，若数据量过大，应考虑需求是否合理。
其它一些优化
1、优化like查询：
 a.使用其它函数来进行模糊查询，如果出现的位置大于0，表示包含该字符串，查询效率比like要高。
    1、Oracle中，使用instr,这样查询效果很好，速度很快。
    select count(*) from table t where instr(t.code,'Cod2%') > 0 ; 
    2、在mysql中，可以用locate和position函数，如table.field like '%AAA%'
    可以改为locate('AAA', table.field) > 0或POSITION('AAA' IN table.field)>0。
    LOCATE(substr,str)、POSITION(substr IN str)：返回子串 substr 在字符串 str 中第一次出现的位置。
    如果子串 substr 在 str 中不存在，返回值为 0。
    3、在sql server中，可以给字段建立全文索引，用contains来检索数据，
    CONTAINS用法，可以参考：http://bijian1013.iteye.com/blog/2232872
  
2、exist和in
    如果主查询的数据集大，则使用In,效率高。
    如果子查询的数据集大，则使用exist,效率高。

3、order by优化
    提高order by查询的策略：
    a.选择使用单路、双路 ；调整buffer的容量大小；
    b.避免select * ...  
    c.复合索引 不要跨列使用 ，避免using filesort
    d.保证全部的排序字段 排序的一致性(都是升序 或 降序)