最左匹配原则

CREATE INDEX idx_age_classid_name ON student(age,classId,name);

show index from student;

-- 1【索引部分生效】
-- 索引部分生效age  key=idx_age_classid_name,key_len=5(int4字节+额外1字节标记空null)
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.age=30 AND student.name = 'abcd';

-- 2【跳过复合索引列，索引失效】
-- 索引不生效 跳过复合索引列  key=null,key_len=null
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.classId=1 AND student.name = 'abcd';

-- 3【符合索引前边部分（挨着）】
-- 索引生效age+classId  key=idx_age_classid_name,key_len=10 (2个int4字节+2个额外1字节标记空null)
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.age=30 AND student.classId = 1;
-- 和最左索引列挨着的索引列，两个索引列先后顺序可随意 key_len=10
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.classId = 1 AND student.age=30;

-- 4【覆盖符合索引所有列】
-- 索引生效 key_len=73 (age+classId=2个int4字节+2个额外1字节标记空null=10;name=63(CHARSET=utf8(3字节) VARCHAR(20)：20*3+1个额外1字节标记空null+动态列类型+还需要再加 2 bytes))
-- 符合索引所有列都用到，和索引列顺序无关
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.classId=4 AND student.age=30 AND student.name = 'abcd';
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.age=30 AND student.classId=4 AND student.name = 'abcd';
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.name = 'abcd' AND student.classId=4 AND student.age=30;