create table staffs( id int primary key auto_increment, name varchar(100) not null default '' comment '姓名', age int not null default 0 comment '年龄', pos varchar(100) not null default '' comment '职位', add_time timestamp not null default current_timestamp comment '入职时间' ) charset utf8 comment '员工表';
insert into staffs(name, age, pos, add_time) values('张三', 22, 'manager', now()); insert into staffs(name, age, pos, add_time) values('july', 23, 'dev', now()); insert into staffs(name, age, pos, add_time) values('tom', 23, 'dev', now()); insert into staffs(name, age, pos, add_time) values('2000', 23, 'dev', now());
alter table staffs add index idx_staffs_nameAgePos(name, age, pos);
索引问题大概可以分为以下十种情况：
最好的情况就是全值匹配
最佳左前缀法则
不在索引列上做任何操作(计算、函数、类型转换)，这些操作会导致索引失效
存储引擎不能使用索引中范围条件右边的列
尽量使用覆盖索引(查询列和索引列一致)，避免select *
MySQL中使用不等于(!= 或者 <>)的时候会导致索引失效
is null，is not null也无法使用索引
like以通配符开头('%abc')会导致索引失效
字符串不加单引号索引会失效
少用or，用它来连接时索引会失效
下面就详细说说这十种情况。
1. 全值匹配：
在staffs表建表时我们建立了一个联合索引，如下：
联合索引
可以看到，一楼是name，二楼是age，三楼是pos。
依次查看下面语句的执行计划：**
explain select * from staffs where name = 'july'; explain select * from staffs where name = 'july' and age = 23; explain select * from staffs where name = 'july' and age = 23 and pos = 'dev';
explain select * from staffs where age = 23 and pos = 'dev'; explain select * from staffs where pos = 'dev'; explain select * from staffs where name = 'july'; explain select * from staffs where name = 'july' and pos = 'dev';
explain select * from staffs where name = 'july' and age = 23 and pos = 'dev'; explain select * from staffs where name = 'july' and age > 23 and pos = 'dev';
explain select * from staffs where name = 'july' and age = 23 and pos = 'dev'; explain select name, age, pos from staffs where name = 'july' and age = 23 and pos = 'dev';
explain select * from staffs where name != 'july' and age = 23 and pos = 'dev'; explain select * from staffs where name <> 'july' and age = 23 and pos = 'dev';
执行计划
可以看到，使用了!=或者<>确实导致索引失效了。
7. is null，is not null也无法使用索引：
查看下面语句的执行计划：
explain select * from staffs where name is null and age = 23 and pos = 'dev'; explain select * from staffs where name is not null and age = 23 and pos = 'dev';
执行计划
is null的情况是最糟糕的，所以我们数据列如果经常用来当查询条件的话，最好设置默认值，而不能让它为null。
8. like以通配符开头('%abc')会导致索引失效：
查看如下语句的执行计划：
explain select * from staffs where name like '%july'; explain select * from staffs where name like 'july%'; explain select * from staffs where name like '%july%';
explain select name from staffs where name like '%july%'; explain select age from staffs where name like '%july%'; explain select age,pos from staffs where name like '%july%'; explain select name,age,pos from staffs where name like '%july%';
