一、前言
MySQL的索引最左匹配是指在使用索引进行查询时，会优先匹配索引的最左侧列，然后再匹配后续列。这种匹配方式可以提高查询效率，但有时候也会导致一些问题，比如在排序查询（ORDER BY）时。并且在面试中，如果涉及数据库索引，也会经常被问到如何优化order by语句。本文就基于innodb引擎，分点分析MySQL索引最左匹配如何优化order by语句，这个问题。
二、关键点验证
本文也是通过实际数据来验证使用order by各种情况的执行情况，可以通过explain查看执行计划，进而验证MySQL索引最左匹配如何优化order by。所以在验证前需要准备一下SQL，利用存储过程，先表中插入1w条数据。
表结构，目前只有主键索引，后面验证会建联合索引：（name_aga_classId）
代码语言：sql复制CREATE TABLE `student` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(11) DEFAULT '',
`age` int(11) DEFAULT '0',
`classId` int(11) DEFAULT '0',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10000 DEFAULT CHARSET=utf8;
存储过程插入1w条数据：
代码语言：javascript复制DELIMITER //
CREATE PROCEDURE InsertStudentData()
BEGIN
DECLARE i INT DEFAULT 1;
WHILE i <= 10000 DO
INSERT INTO student (name, age, classId) VALUES (CONCAT('Student', i), RAND() * 20 1, RAND() * 10 1);
SET i = i 1;
END WHILE;
END //
DELIMITER ;
CALL InsertStudentData();
数据准备完成，就可以来验证order by那些情况不走索引。
order by无索引，filesort
直接按照aga，classId进行order by，表结构没有索引，
代码语言：text复制select *
from student
ORDER BY `name`, age, classId先看查询时间
explain 查看执行计划，发现type是ALL全表扫描，并且出现了filesort，也就mysql内部排序，这是很耗时的。
所以，可以得出结论：order by排序字段无索引，全部扫描，并且会filesort
无过滤条件不索引
接下来创建索引， KEY `idx_auc` (`name`,`age`,`classId`)
继续上面查询语句，通过explain发现，扫描的行数还是1w多，type类型还index，all区别为index类型只遍历索引树，没有到优化级别ref
如果增加一个查询条件，比如：
代码语言：text复制select *
from student
WHERE `name` = 'Student968'
ORDER BY `name`, age, classId可以看谁type是等于ref
所以在使用order by的时候，建议增加过滤条件，避免全表扫描。
order by非最左，filesort
order by去掉联合索引的第一个，也就不遵循最左匹配
代码语言：text复制select *
from student
WHERE `name` = 'Student968'
ORDER BY age, classId发现结果出现filesort
最终不能完全匹配索引，导致filesort重排序。
order by顺序错，filesort
order by使用了联合索引的三个字段，但是没有按照name`,`age`,`classId`这个样的顺序
代码语言：text复制EXPLAIN
select *
from student
ORDER BY age,`name`, classId
发现结果出现filesort
order by排序不一致，filesort
另一种情况，三个字段排序不一致，比如数据库默认升序的，吧classId改下desc
代码语言：text复制EXPLAIN
select *
from student
ORDER BY `name`, age, classId desc发现结果出现filesort
这个应该比较好理解，索引是已经排好序的，要么按照升序，要么降序，查询的时候按照索引顺序一致。
三、回答总结
所以在使用order by的时候，遵循一句话：使用where，按照索引顺序，字段排序方向一致。那么关于这个面试，可以这样回答：
1.首先要对sql进行分析检查必要的查询字段，过滤字段，排序字段是否按顺序创建好了索引，使用explain。
2.如果查询字段不再索引中可能会产生回表操作会导致flesort，降低性能。
3.一定要有过滤字段不然不能使用索引
4.排序字段和索引顺序不一致会导致filesort，降低性能
5.多个字段排序时如果方向不一致也会导致flesort，降低性能
6.使用explain观察查询类型和索引利用情况
我正在参与2023腾讯技术创作特训营第四期有奖征文，快来和我瓜分大奖！
