关于mysql的索引

我们首先创建一个表
并且创建索引

create table user
(
    id int auto_increment
        primary key,
    name int not null,
    age int not null,
    sex int not null,
    remark int null
);

create index user_name_age_sex_index
    on user (name, age, sex);

create index user_remark_index
    on user (remark);

我们通过这些测试

explain select * from user where name = 1;
explain select * from user where age = 1;
explain select * from user where sex = 1;
explain select * from user where name = 1 and sex = 1;
explain select * from user where name = 1 and sex = 1;
explain select * from user where age > 1 and name < 1;
explain select * from user where age = 1 and name > 1;
explain select * from user where sex = 1 and name = 1;
explain select * from user where sex = 1 and age = 1;
explain select * from user where name = 1 and age = 1 and sex = 1 ;
explain select * from user where name = 1 and sex = 1 and age = 1 ;
explain select * from user where remark = 1 and name = 1 and sex = 1 ;
explain select * from user where age = 1 and sex = 1 and name = 1 ;
explain select * from user where sex = 1 and name = 1 and age = 1 ;
explain select * from user where sex = 1 and age = 1 and name = 1 ;
explain select * from user where sex = 1 and name = 1 and remark = 1;

可以看出包含name的走name_age_sex的索引

image.png

如果不包含name，包含remark的走remark索引

image.png