索引优化

1.不在索引列上做任何操作（计算，函数等等），会导致索引失效
2.慎用不等于号，会使索引失效
3.存储引擎不能使用索引中范围条件右边的列
4. 只访问索引的查询：索引列和查询列一致，尽量用覆盖索引，减少select *
5. NULL/NOT NULL的可能影响：c1允许为空，作为where的查询条件不会使索引失效；c2不允许为空，c2 is null  is not null都不会用到索引
6. 字符串类型加引号，不加引号会索引失效
7. UNION的效率比or更好
8.like查询要当心，就是是否满足最左前缀

insert into student values (6, '19',34, '19', '19', '19', '2015-02-12 10:10:00');

show create table student;
#索引优化
#1、不在索引列上做任何操作（计算，函数等等），会导致索引失效
explain select * from student where id = 3;
explain select * from student where id + 1 = 4;

#2、慎用不等于号，会使索引失效
show create table student;
explain select * from student where c1 =  'wuhan';
explain select * from student where c1 <> 'wuhan';
explain select c1 from student where c1 <> 'wuhan';
explain select c1,c2 from student where c1 <> 'wuhan';

explain select * from student where c1 > 'wuhan' or c1 < 'wuhan';
explain select c1 from student where c1 > 'wuhan' or c1 < 'wuhan';
#下面这条会使用index
explain select c1 from student where c1 > 'wuhan' UNION 
 select c1 from student where c1 < 'wuhan';

#3、存储引擎不能使用索引中范围条件右边的列
explain  select * from student where c1 = 'wuhan' and c2 > 'c' and c3 = 'wangdao';
explain  select * from student where c1 = 'wuhan' and c2 like 'c%' and c3 = 'wangdao';

#4. 只访问索引的查询：索引列和查询列一致，尽量用覆盖索引，减少select *
explain  select c1,c2 from student where c1 = 'wuhan';
explain  select * from student where c1 = 'wuhan';
explain  select c1,c2 from student where c2 ='wuhan'; 

#5. NULL/NOT NULL的可能影响


#6. 字符串类型加引号，不加引号会索引失效
explain  select * from student where c1 = '19';
explain  select * from student where c1 = 19;

#7. UNION的效率比or更好
explain select * from student where c1 = '19';
explain select * from student where c1 = 'wuhan';
explain select * from student where c1 = '19' or c1 = 'wuhan';
explain select * from student where c1 = '19' union select * from student where c1 = 'wuhan';