在看本篇文章时一起跟着操作更有感觉呦～

前期准备

# 创建测试表
CREATE TABLE `employees` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `name` varchar(24) NOT NULL DEFAULT '' COMMENT '姓名',
 `age` INT ( 11 ) NOT NULL DEFAULT '0' COMMENT '年龄',
 `position` VARCHAR ( 20 ) NOT NULL DEFAULT '' COMMENT '职位',
 `hire_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入职时间',
 PRIMARY KEY (`id`),
 KEY `idx_name_age_position` (`name`,`age`,`position`) USING BTREE
 ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='员工记录表';

INSERT INTO employees(name,age,position,hire_time) VALUES('LiLei',22,'manager',NOW());
INSERT INTO employees(name,age,position,hire_time) VALUES('HanMeimei', 23,'dev',NOW());
INSERT INTO employees(name,age,position,hire_time) VALUES('Lucy',23,'dev',NOW());

# 插入一些示例数据
 drop procedure if exists insert_emp;
 delimiter ;;
 create procedure insert_emp()
 begin
 declare i int;
 set i=1;
 while(i<=100000)do
 insert into employees(name,age,position) values(CONCAT('zhuge',i),i,'dev');
 set i=i+1;
end while;
 end;;
 delimiter ;
 call insert_emp();

案例分析

1. EXPLAIN SELECT * from employees where name = “LiLei” and position =’dev’ ORDER BY age;

结果分析:

上面语句用到联合索引中 name 和 age两个字段。
利用最左前缀法则:中间字段不能断，因此查询用到了name索引，从key_len=74也能看出，age索引列用 在排序过程中，因为Extra字段里没有using filesort（文件排序）。

2. EXPLAIN SELECT * from employees where name = “LiLei” ORDER BY position

结果分析:

从 key_len=74可知查询使用了name索引，由于用了position进行排序，跳过了 age，出现了Using filesort。

3. EXPLAIN SELECT * from employees where name = “LiLei” ORDER BY age,position

结果分析:

key_len=74 得查找只用到索引name;因为无Using filesort,age和position用于排序。

4. EXPLAIN SELECT * from employees where name = “LiLei” ORDER BY age,position

结果分析:

与3中explain的执行结果一样，但是出现了文件排序 Using filesort，因为索引的创建顺序为 name,age,position，但是排序的时候age和position颠倒位置了，不符合最左前缀法则。

5. EXPLAIN SELECT * from employees where name = “LiLei” ORDER BY age,position

结果分析:

与4对比，在Extra中并未出现Using filesort，因为age为常量，在排序中被优化，所以索引顺序未颠倒，符合 最左前缀法则 不会出现Using filesort。

6. EXPLAIN SELECT * from employees where name = “LiLei” ORDER BY age asc,position desc;

结果分析:

虽然排序的字段列与索引顺序一样，且order by默认升序，这里position desc变成了降序，导致与索引的 排序方式不同，从而产生Using filesort。Mysql8以上版本有降序索引可以支持该种查询方式。

7. EXPLAIN SELECT * from employees where name in (“LiLei”,’zhangsan’)ORDER BY age,position;

结果分析:

对于排序来说，多个相等条件也是范围查询

8. EXPLAIN SELECT * from employees where name > ‘a’ ORDER BY name

结果分析:

结合前面的mysql 索引选择 这条语句 经过mysql cost 计算 走的全表扫描 所以没有使用到索引 且使用use filesort。

这种情况可以使用覆盖索引进行优化

总结

1. MySQL支持两种方式的排序filesort和index，Using index是指MySQL扫描索引本身完成排序。index
   效率高，filesort效率低。

2. order by满足两种情况会使用Using index。

   • order by语句使用索引最左前列。
   • 使用where子句与order by子句条件列组合满足索引最左前列。

3. 尽量在索引列上完成排序，遵循索引建立(索引创建的顺序)时的最左前缀法则。

4. 如果order by的条件不在索引列上，就会产生Using filesort。

5. 能用覆盖索引尽量用覆盖索引

6. group by与order by很类似，其实质是先排序后分组，遵照索引创建顺序的最左前缀法则。对于group by的优化如果不需要排序的可以加上order by null禁止排序。注意，where高于having，能写在where中 的限定条件就不要去having限定了。