mysql索引无效

Question

有下表
create table a1
{
        id int(11) not null,
        name varchar(32) not null,
        age int(11) not null,
        INDEX (id,name)
}
下面查询中，索引无效的有(A)。
A.    select * from a1 where name = ‘Jack’;
B.    select * from a1 where name != ‘Jack’;
C.    select * from a1 where id = 1 and name like ‘J%’;
D.   select * from a1 where id <> 1

为什么BD是有效的？

Answer 1

真正索引有效的只有 C, 不知你的题是出自何处, 更有可能的是题目搞错了.

AB 完全用不到联合索引,因为索引是按记录顺序查找到的,name 在 id 后面, 无法不查 id 直接用 name查询.

创建表和数据

create table a1(
        id int(11) not null, 
        name varchar(32) not null, 
        age int(11) not null, 
        INDEX (id,name) 
);


insert into a1 (id, name, age) values(1,"Jack",29),(10, "test1", 10),(20,"test2",20),(30,"test3", 30),(40,"ddd",40),(50,"Jack",29);

insert into a1 select id+1, name, age from a1;
insert into a1 select id+1, name, age from a1;
insert into a1 select id+1, name, age from a1;
insert into a1 select id+1, name, age from a1;
insert into a1 select id+1, name, age from a1;
insert into a1 select id+1, name, age from a1;
insert into a1 select id+1, name, age from a1;
insert into a1 select id+1, name, age from a1;
select count(*) from a1;

分析索引

explain select * from a1 where name = 'Jack'; 
explain select * from a1 where name != 'Jack'; 
explain select * from a1 where id = 1 and name like 'J%'; 
explain select * from a1 where id <> 1;
explain select * from a1 where age <> 1;

作为比较, 可以看一下下面的结果

explain select name from a1 where name = 'Jack'; 
explain select name from a1 where name != 'Jack'; 
explain select name from a1 where id = 1 and name like 'J%'; 
explain select name from a1 where id <> 1;
explain select name from a1 where age <> 1;

如查返回结果type=ALL则意味着全表扫描, 有无索引已没太大意义.

Answer 2

一般情况下，ABD都是索引无效的。
在特殊的数据分布下，A有可能走索引跳跃扫描。

Answer 3

养成良好习惯explain 分析 ,最主要type列,多试验就好了