mysql最左前缀原则

今天主要就测试了一下mysql最左前缀。mysql版本 5.7

create table student
(
    id      int auto_increment
        primary key,
    name    varchar(10) null,
    age     int         null,
    address varchar(10) null,
    parent  varchar(10) null,
    result  int         null
);

name,age,address做联合索引。

explain select *
        from test.student
        where name = '1'

结果type ref

explain select *
        from test.student
        where name = '1'
          and age = 21

结果type ref

explain select *
        from test.student
        where name = '1'
          and parent = 'ewrqweqe'

结果type ref

explain select *
        from test.student
        where parent = 'ewrqweqe'
          and name = '1'

结果type ref

explain select *
        from test.student
        where parent = 'ewrqweqe'
          and age = '21'

结果type all

explain select *
        from test.student
        where name = '1'
          and age = '21'
          and parent = 'qwe'

结果type ref

explain select *
        from test.student
        where name = '1'
          and age = '21'
          and result = 2

结果type ref

explain select *
        from test.student
        where name = '1'
          and age = '21'
          and parent = 'qwe'

结果type ref

explain select *
        from test.student
        where name = '1'
          and address = 'ewrqweqe'
          and parent = 'qwe'

结果type ref

总结

name,age,address联合索引。

如果一个也不出现，那么全表。如果出现name其他的不出现，也会走索引。如果不出现name，那么肯定会走全表。有了

name，age和address出现是否都会走索引。

我的理解是这样的上面的索引生成(name),(name,age),(name,age,address)。只有有存在name，那么就会走name这个索引，其他并不能走索引，但是结果显示这算走了索引。如果你走了name,address,result，那么索引也只会走name一个。