mysql int 索引无效_mysql索引无效

真正索引有效的只有 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则意味着全表扫描, 有无索引已没太大意义.