对于order by优化，MySQL若可以利用索引有序性进行排序，则优先使用索引进行排序。

1. 单索引的情况下

user表数据量是百万级，name列存在普通索引。

1.1 索引覆盖

使用索引覆盖去优化order by。索引覆盖指一个查询语句的执行只用从索引中就能够查到，不必从数据表中读取。

mysql> explain select id from user order by name;
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key       | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | user  | NULL       | index | NULL          | user_name | 767     | NULL | 998179 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+--------+----------+-------------+
1 row in set (0.00 sec)
但是若select *操作时，那么并不会使用索引来优化order by操作。
mysql> explain select * from user order by name;
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra          |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+----------------+
|  1 | SIMPLE      | user  | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 998179 |   100.00 | Using filesort |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+----------------+
1 row in set (0.00 sec)
mysql官网是这样描述的：
2.1 场景描述
当复合索引的最左前缀列为过滤条件的常量过滤时，order by字段配合常量过滤字段满足最左前缀时可以使用复合索引进行排序优化。
如，建立复合索引(a,b,c)可以
1、使用复合索引扫描排序有：
from tbl_name where a=xx order by b,c;
from tbl_name where a=xx order by b;
2、过滤字段不是复合索引中的常量，但是order by列满足最左前缀是可以使用覆盖索引：
from tbl_name where a>xx order by a,b     #order by字段满足最左前缀
3、一些情况不能使用复合索引扫描排序的情况：
from tbl_name where a=xx order by b desc,c asc;  #一列为升序一列为降序
from tbl_name where a=xx order by b,d;      #order by列引用了一个不在索引中的字段
from tbl_name where a=xx order by c;  #无法组合成索引的最左前缀
from tbl_name where a=xx and b in (xx,xx) order by c;    #存在范围查询
2.2 聚簇索引
如果我们建立单列索引（A），实际上相当于在（A，ID）上建立了索引，其中ID为主键。这种情况下对于 where A=xx order by ID的查询是非常有帮助的。但是如果我们建立了复合索引（A,B）,那么就相当于在(A,B,ID)上建立了索引，那么对于where A=xx order by ID这样的查询，就使用不到索引扫描排序，只能用filesort排序(using filesort)了。
2.3 面试实战
如果有这么一条sql：
select * from table where a=1 and b>10 order by c;
如果想在abc三列上去建立一个复合索引，那么如何建立？？？
在acb列上建立索引? ? ?
不推荐这样建立索引，这样的情况相当于在ac列上建立索引！！！
实际上推荐的是在ab上建立索引。
mysql语句的各个子句中。where子句是筛选数据，order by子句是排序数据。order by排序where子句筛选后的数据集，若where子句使用的是索引访问类型（explain的type列）为index或以上的类型，那么数据集是以索引的顺序的有序集合。若order by筛选的顺序正好是索引的顺序，那么才能优化索引。
但where语句和order by语句处理数据时机不同。order by的索引最左前缀和where子句最左前缀不能相互影响。即acb复合索引中：where走ab索引，order去走c索引的情况是不存在的。
即若建立acb索引，那么where会使用a索引进行筛选，因为where不存在c列条件，那么不能使用b列进行筛选，该索引等效于ac列。
3. 连表查询情况下
如果为多表关联查询&利用索引来进行优化排序，排序列必须是对驱动表字段的排序。
当然，具体场景的还是需要依照explain执行计划来具体分析。
3.1 准备
3.1.1 数据准备
CREATE TABLE `t_user` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `name` varchar(32) DEFAULT '' COMMENT '名字',
  `age` int(11) DEFAULT NULL COMMENT '年龄',
  `p_id` bigint(20) NOT NULL DEFAULT '1',
  PRIMARY KEY (`id`) USING BTREE,
  KEY `idx_pId` (`p_id`)
) ENGINE=InnoDB AUTO_INCREMENT=55 DEFAULT CHARSET=latin1;
CREATE TABLE `t_product` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键id',
  `name` varchar(32) DEFAULT NULL COMMENT '产品名',
  `price` double DEFAULT NULL COMMENT '价格',
  `count` int(11) DEFAULT NULL COMMENT '余额',
  `type` int(11) DEFAULT NULL COMMENT '类型',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=latin1;
记录集中，t_product数据量少。
3.1.2 什么是驱动表
何谓驱动表，指多表关联查询时，第一个被处理的表，亦可称之为基表，然后再使用此表的记录去关联其他表。驱动表的选择遵循一个原则：在对最终结果集没影响的前提下，优先选择结果集最少的那张表作为驱动表。这个原则说的不好懂，结果集最少，这个也许我们能估出来，但对最终结果集不影响，这个就不好判断了，难归难，但还是有一定规律的：LEFT JOIN 一般以左表为驱动表（RIGHT JOIN一般则是右表 ），INNER JOIN 一般以结果集少的表为驱动表，如果还觉得有疑问，则可用 EXPLAIN 来找驱动表，其结果的第一张表即是驱动表。
mysql> EXPLAIN SELECT
    t_user u
    JOIN t_product p ON u.p_id = p.id 
WHERE
    u.`name` = 'xxx' 
ORDER BY
    p.price 
    LIMIT 5;
+----+-------------+-------+------+---------------+---------+---------+-----------+------+----------------+
| id | select_type | table | type | possible_keys | key     | key_len | ref       | rows | Extra          |
+----+-------------+-------+------+---------------+---------+---------+-----------+------+----------------+
|  1 | SIMPLE      | p     | ALL  | PRIMARY       | NULL    | NULL    | NULL      |    6 | Using filesort |
|  1 | SIMPLE      | u     | ref  | idx_pId       | idx_pId | 8       | test.p.id |    3 | Using where    |
+----+-------------+-------+------+---------------+---------+---------+-----------+------+----------------+
2 rows in set (0.00 sec)
如上所示的sql中：t_product为驱动表。
3.1.3 三种排序方式对比
排序处理方式
执行计划extra列信息
排序性能比较
3.2 利用驱动表索引优化order by
2.2中进行的描述——“如果我们建立单列索引（A），实际上相当于在（A，ID）上建立了索引，其中ID为主键。”
此时对驱动表p进行的排序，在执行计划中可以看到使用了索引来进行order by的优化。
mysql> explain SELECT
    t_user u
    JOIN t_product p ON u.p_id = p.id 
WHERE
    u.`name` = 'xxx' 
ORDER BY
    LIMIT 5;
+----+-------------+-------+-------+---------------+---------+---------+-----------+------+-------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref       | rows | Extra       |
+----+-------------+-------+-------+---------------+---------+---------+-----------+------+-------------+
|  1 | SIMPLE      | p     | index | PRIMARY       | PRIMARY | 8       | NULL      |    1 | NULL        |
|  1 | SIMPLE      | u     | ref   | idx_pId       | idx_pId | 8       | test.p.id |    3 | Using where |
+----+-------------+-------+-------+---------------+---------+---------+-----------+------+-------------+
2 rows in set (0.00 sec)
若对被驱动表u.id进行order by 排序呢？
mysql> explain SELECT
    t_user u
    JOIN t_product p ON u.p_id = p.id 
WHERE
    u.`name` = 'xxx' 
ORDER BY
    LIMIT 5;
+----+-------------+-------+------+---------------+---------+---------+-----------+------+---------------------------------+
| id | select_type | table | type | possible_keys | key     | key_len | ref       | rows | Extra                           |
+----+-------------+-------+------+---------------+---------+---------+-----------+------+---------------------------------+
|  1 | SIMPLE      | p     | ALL  | PRIMARY       | NULL    | NULL    | NULL      |    6 | Using temporary; Using filesort |
|  1 | SIMPLE      | u     | ref  | idx_pId       | idx_pId | 8       | test.p.id |    3 | Using where                     |
+----+-------------+-------+------+---------------+---------+---------+-----------+------+---------------------------------+
2 rows in set (0.00 sec)
可以看到，Using temporary; Using filesort借助临时表来进行排序。效率更加低下。
3.3 仅对驱动表进行排序
3.3.1 场景
多表关联查询中，排序字段为驱动表字段，且该字段无法有效利用索引；
被驱动表关联字段为有效索引字段，有效利用INLJ算法进行表关联
mysql> explain SELECT
    t_user u
    JOIN t_product p ON u.p_id = p.id 
WHERE
    u.`name` = 'xxx' 
ORDER BY
    p.price 
    LIMIT 5;
+----+-------------+-------+------+---------------+---------+---------+-----------+------+----------------+
| id | select_type | table | type | possible_keys | key     | key_len | ref       | rows | Extra          |
+----+-------------+-------+------+---------------+---------+---------+-----------+------+----------------+
|  1 | SIMPLE      | p     | ALL  | PRIMARY       | NULL    | NULL    | NULL      |    6 | Using filesort |
|  1 | SIMPLE      | u     | ref  | idx_pId       | idx_pId | 8       | test.p.id |    3 | Using where    |
+----+-------------+-------+------+---------------+---------+---------+-----------+------+----------------+
2 rows in set (0.00 sec)
由于order by字段p.price无法有效利用索引，所以必须将满足过滤条件的记录放至sort buffer进行排序处理。在执行计划中，我们可以看到“Using filesort”表示使用filesort进行了排序处理
3.3.2 对于使用驱动表排序的优化
若SQL无法有效利用索引进行优化，且仅仅是对驱动表进行排序处理，这已然是一种相对较好的情况，我们更多的只需要关注SQL的where过滤条件是否可以有效利用索引减少驱动表需要扫描以及排序的记录数。
3.4 使用临时表进行排序
3.4.1 场景
多表关联查询中，排序字段为被驱动表字段，MySQL必须获取到多表关联的结果后才可以对这些记录进行排序处理
mysql> explain SELECT
    t_user u
    JOIN t_product p ON u.p_id = p.id 
WHERE
    u.`name` = 'xxx' 
ORDER BY
    LIMIT 5;
+----+-------------+-------+------+---------------+---------+---------+-----------+------+---------------------------------+
| id | select_type | table | type | possible_keys | key     | key_len | ref       | rows | Extra                           |
+----+-------------+-------+------+---------------+---------+---------+-----------+------+---------------------------------+
|  1 | SIMPLE      | p     | ALL  | PRIMARY       | NULL    | NULL    | NULL      |    6 | Using temporary; Using filesort |
|  1 | SIMPLE      | u     | ref  | idx_pId       | idx_pId | 8       | test.p.id |    3 | Using where                     |
+----+-------------+-------+------+---------------+---------+---------+-----------+------+---------------------------------+
2 rows in set (0.00 sec)
由于order by字段为被驱动表的u.id字段，所以必须将获取到两表join的结果集，然后进行排序，在执行计划中，我们可以看到“Using temporary; Using filesort”表示使用临时表进行了排序处理
3.4.2 对于使用临时表排序的优化
对于使用临时表进行排序的查询其资源消耗是以上说到的三种排序方式下资源消耗最大的一种排序方式。在这种模式下，优先考虑排序字段是否可以等价替换为驱动表字段，将其转换为只对驱动表进行排序；若以上手段无效，我们只能通过where过滤条件有效利用索引，通过索引过滤尽量减少SQL查询扫描数据量；select只查询需要的字段，避免select *，尽量减少磁盘临时表的使用。
4. 实战优化
4.1 order by id替换create_time
可以：若a b复合索引：
select * from table where a=‘xx’ and b='yy' order by create_time desc;
可以优化为：
select * from table where a=‘xx’ and b='yy' order by id desc;
原理：实际上，当同时命中a b时，记录便是按照主键索引排序的，此时order by id可以省去一部分的时间。由因为id是有序的，故可以替换create_time。
注意：但是只使用a索引时 order by id，也得借助文件完成排序。
mybatis&&数据库优化&&缓存目录
官方文档—order by 优化
MySQL如何对order by优化？
mysql中数据存储的顺序是什么？
MySQL语句执行顺序和编写顺序