Mysql优化之索引相关介绍(笔记)

CREATE TABLE `user` (
  `id` bigint NOT NULL,
  `username` varchar(255) DEFAULT NULL,
  `age` int DEFAULT NULL,
  `dept` varchar(255) DEFAULT NULL,
  `create_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
//创建一张user表，id:主键，username:用户名，age:年龄，dept：部门

create index idx_username_age_dept on user(username,age,dept);
//创建username,age,dept三个字段的联合索引

联合索引排序规则：
  1. 从第一级（最左边字段）索引字段开始排序。
  2. 第一级排好序之后，第二个字段只在当前第一级排好序的数据中进行二次排序
  3. 第三级根据第二级排好序的数据，进行第三级排序。

由此引出结论，
1.第二级排序是依赖第一级排序的。同理，第三级依赖第二级
2.第二级分开来看，所有的数据都是无序的。
3.同理，第三级也是。

查询时，如果要走联合索引，则必须满足，
1.从组合索引最左边开始，依次匹配索引字段。
2.中间索引字段不能断，断了不能走完整的组合索引。
3.字段必须从最左边那个字段开始。

select * from user where username = '李雷' and age = 20 and dept = 'dev';

毫无疑问，这是索引全值匹配，而且条件顺序是按照索引顺序来的。符合最左前缀，所以会走索引。

select * from user where username = '李雷' and dept = 'dev' and age = 20;

这条sql和第一条sql的差别在于第二个条件和第三个条件互换了顺序。看起来违背了最左前缀原则
但是也能走索引，这是因为mysql在编译期会优化sql进行重排序。

select * from user where username = '李雷';
如联合索引结构图所示，第一级username在索引结构种，已经是排好序的。所以在搜索username单值的时候，是可以通过联合索引查找的。
select * from user where age = 20; 
如联合索引结构图所示，第二级age在结构图中，顺序是乱序的。所以在单独查找age的时候，索引树是乱序，自然也不能走索引。
select * from user where dept = 'dev';  //同理

select * from user where username = '韩梅梅' and age = 20;
如联合索引结构图所示，username在排序的情况下，age在同等username下也是排序的。所以这条会走key值为username + age 的索引

select * from user where username = '韩梅梅' and dept = 'dev';
如联合索引结构图所示，username在排序的情况下，dept需要通过二级age排序的情况下，才能排序。所以此处dept是乱序。
但是这里也能走索引，但是只能走key长度为username的索引。相当于找出所有的韩梅梅之后，再逐行扫描dept为dev的记录。

select * from user where age = 20 and dept = 'dev';
age和dept在没有username的前提都是乱序的，所以这条会全表扫描。