Mysql索引是否生效-实践

创建表

create table people(

id int unsigned not null auto_increment primary key,
name char(8) not null,
sex char(4) not null,
age tinyint unsigned not null
);

插入数据，查询结果

新建联合索引：create index id_name on people(id,name);

1.explain select * from people where id = 1 or name = 2;#不走索引

2.explain select * from people where id = 1 and name = 1;

3.explain select * from people where name like ‘1%’;

4.explain select * from people where id = (select id from people where sex = 1) ;

5.explain select * from people where id = (select id from people where name = 1) ;

6.explain select * from people where id = (select id from people where id = 1) ;

7.explain select * from people where name = 1;

8.explain select * from people where (id = 1 or name = 2) and id = 1;

9.explain select * from people where sex = (select sex from people where id = 1) ;