索引优化全攻略：提升排序、GROUP BY与分页性能

在数据量庞大的场景下，SQL查询的性能往往成为影响系统响应速度和用户体验的关键因素。随着数据库的规模逐渐扩大，如何优化SQL查询，尤其是在排序、分组和分页操作中，成为了每位开发者必须掌握的技能。

在本篇文章中，我们将继续深入探讨SQL查询的优化方法，重点分析如何通过有效的排序优化、 GROUP BY 优化以及分页查询效率提升，来降低查询时间，减轻数据库负担。之后，我们还将介绍一些更高级的优化技术，如覆盖索引和索引下推等等，它们能够进一步提升查询性能。

1.排序优化

1.1排序优化

问题: 在 WHERE 条件字段上加索引但是为什么在 ORDER BY 字段上还要加索引呢?

回答:

在MySQL中，支持两种排序方式，分别是FileSort和Index排序。

• Index排序中，索引可以保证数据的有序性，不需要再进行排序，效率更高。
• FileSort排序则一般在内存中进行排序，占用CPU较多。如果待排结果较大，会产生临时文件I/O到磁盘进行排序的情况，效率较低。

优化建议:

• SQL中，可以在 WHERE 子句和 ORDER BY 子句中使用索引，目的是在WHERE子句中避免全表扫描，在ORDER BY子句避免使用FileSort排序。当然，某些情况下全表扫描，或者FileSort排序不一定比索引慢。但总的来说，我们还是要避免，以提高查询效率。
• 尽量使用Index完成 ORDER BY 排序。如果WHERE和 ORDER BY 后面是相同的列就使用单索引列;如果不同 就使用联合索引。
• 无法使用Index时，需要对FileSort方式进行调优。

1.2测试

删除student表和class表中已创建的索引。

#方式1:
DROP INDEX idx_monitor ON class;

DROP INDEX idx_cid ON student;
DROP INDEX idx_age ON student;DROP INDEX idx_name ON student ;
DROP INDEX idx_age_name_classid ON student ;DROP INDEX idx_age_classid_name ON student ;

以下是否能使用到索引，能否去掉 using filesort

过程一:

EXPLAIN SELECT SQL_NO_CACHE * FROM student ORDER BY age,classid;

EXPLAIN SELECT SQL_NO_CACHE * FROM student ORDER BY age,classid limit 10;

过程二: order by时不limit，索引失效

#创建索引
CREATE INDEX idx_age_classid_name ON student (age,classid, NAME);
#不限制,索引失效
EXPLAIN SELECT SQL_NO_CACHE * FROM student ORDER BY age ,classid ;

这里优化器觉得，，还需要回表。会费时间更大，不走索引。

使用覆盖索引试试看

不用回表，优化器觉得走索引快。就使用了索引。

增加limit 条件

增加limit 减少回表的数量，优化器觉得走索引快，会使用索引

过程三: order by时顺序错误，索引失效

CREATE INDEX idx_age_classid_stuno ON student (age,classid,stuno) ;

#以下哪些索引失效?

# 不会走，最左前缀原则
EXPLAIN SELECT* FROM student ORDER BY classid LIMIT 10;

# 不会走，最左前缀原则
EXPLAIN SELECT* FROM student ORDER BY classid,NAME LIMIT 10;

# 走
EXPLAIN SELECT* FROM student ORDER BY age,classid, stuno LIMIT 10;
# 走
EXPLAIN SELECT *FROM student ORDER BY age,classid LIMIT 10;
# 走
EXPLAIN SELECT * FROM student ORDER BY age LIMIT 10;

过程四: order by时规则不一致,索引失效（顺序错，不索引; 方向反，不索引)

# age desc 方向反 索引失效
EXPLAIN SELECT * FROM student ORDER BY age DESC, classid ASC LIMIT 10;

# 没有最左前缀 索引失效
EXPLAIN SELECT * FROM student ORDER BY classid DESC, NAME DESC LIMIT 10;

# age asc 没问题 classid desc 降序， 优化器认为，文件排序比较快索引失效
# 方向反了不走索引
EXPLAIN SELECT * FROM student ORDER BY age ASC, classid DESC LIMIT 10;

# Backward index scan 走索引了，，倒着走索引
EXPLAIN SELECT * FROM student ORDER BY age DESC, classid DESC LIMIT 10; 

过程五：无过滤，不索引

EXPLAIN SELECT * FROM student WHERE age=45 ORDER BY classid;

EXPLAIN SELECT * FROM student WHERE age=45 ORDER BY classid , name;

EXPLAIN SELECT *FROM student WHERE classid=45 order by age;

EXPLAIN SELECT * FROM student WHERE classid=45 order by age limit 10;

这里第一条排序走Using filesort 很好理解

第二条为啥不是 Using filesort 呢？

这里 type = index ， key=idx_age_classid_name 。这说明了 优化器预估对 idx_age_classid_name 索引进行完整的遍历。由于索引本身就是根据age升序存储的。。

所以只要在遍历的过程中，遇到前十个 classid=45 。就可以停止遍历。回表返回数据。（根据上完课自己想的，无法验证，不知道有没有偏差）

小结:

INDEX a_b_c( a, b,c)

order by 能使用索引最左前缀
- ORDER BY a
- ORDER BY a, b
- ORDER BY a , b, c
- ORDER BY a DESC, b DESC,c DESC


# 如果WHERE使用索引的最左前缀定义为常量，则order by 能使用索引
- WHERE a = const ORDER BY b, c
- WHERE a = const AND b = const ORDER BY c
- WHERE a = const ORDER BY b, c
- WHERE a = const AND b > const ORDER BY b , c

# 不能使用索引进行排序
- ORDER BY a ASC, b DESC, c DESC/*排序不一致*/
- WHERE g = const ORDER BY b,c/*丢失a索引*/
- WHERE a = const ORDER BY c/*丢失b索引*/
- WHERE a = const ORDER BY a, d /*d不是索引的一部分*/
- WHERE a in (...) ORDER BY b,c /*对于排序来说，多个相等条件也是范围查询*/

索引只会用到一个，没办法一个索引用来where 一个索引用来 order by 。

但是可以建立联合索引。

1.3案例实战

ORDER BY 子句，尽量使用Index方式排序，避免使用FileSort方式排序。

执行案例前先清除student上的索引，只留主键:

DROP INDEX idx_age ON student;
DROP INDEX idx_age_classid_stuno ON student;DROP INDEX idx_age_classid_name ON student;
#或者
call proc_drop_index( 'my_sql' , ' student' ) ;

show index from student;

场景:查询年龄为30岁的，且学生编号小于101000的学生，按用户名称排序

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age = 30 AND stuno <101000 ORDER BY NAME;

mysql>  SELECT SQL_NO_CACHE * FROM student WHERE age = 30 AND stuno <101000 ORDER BY NAME;
+-----+--------+--------+------+---------+
| id  | stuno  | name   | age  | classId |
+-----+--------+--------+------+---------+
| 417 | 100417 | bBAYtX |   30 |     159 |

....

| 372 | 100372 | xwODCc |   30 |     764 |
+-----+--------+--------+------+---------+
18 rows in set, 1 warning (0.17 sec)

结论: type是ALL，即最坏的情况。Extra里还出现了 Using filqsort ，也是最坏的情况。优化是必须的。

优化思路：

方案一:为了去掉filesort我们可以把索引建成

#创建新索引
CREATE INDEX idx_age_name ON student(age , NAME);

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age = 30 AND stuno <101000 ORDER BY NAME;

方案二:尽量让where的过滤条件和排序使用上索引

create index idx_age_stuno_name on student(age,stuno,name);
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age = 30 AND stuno <101000 ORDER BY NAME;

下面这个方案虽然使用了 Using filesort 但是速度反而更快了。

原因:

所有的排序都是在条件过滤之后才执行的。所以，如果条件过滤掉大部分数据的话，剩下几百几千条数据进行排序其实并不是很消耗性能，即使索引优化了排序，但实际提升性能很有限。相对的 stuno<101000 这个条件，如果没有用到索引的话，要对几万条的数据进行扫描，这是非常消耗性能的，所以索引放在这个字段上性价比最高，是最优选择。

结论:

1. 两个索引同时存在，mysql自动选择最优的方案。(对于这个例子mysql选择 idx_age_stuno_name )。但是，随着数据量的变化，选择的索引也会随之变化的。

2. 当【范围条件】和【 group by 或者 order by 】的字段出现二选一时，优先观察条件字段的过滤数量，如 果过滤的数据足够多，而需要排序的数据并不多时，优先把索引放在范围字段上。反之，亦然。

思考:这里我们使用如下索引，是否可行?

DROP INDEX idx_age_stuno_name ON student;

# 当然可以了，因为3个也只是用到了两个索引
CREATE INDEX idx_age_stuno ON student(age , stuno ) ;

1.4 filesort算法:双路排序和单路排序

排序的字段若如果不在索引列上，则filesort会有两种算法: 双路排序和单路排序

双路排序（慢)

• MySQL 4.1之前是使用双路排序，字面意思就是两次扫描磁盘，最终得到数据，读取行指针和order by列，对他们进行排序，然后扫描已经排序好的列表，按照列表中的值重新从列表中读取对应的数据输出
• 从磁盘取排序字段，在buffer进行排序，再从磁盘取其他字段。

取一批数据，要对磁盘进行两次扫描，众所周知，lo是很耗时的，所以在mysql4.1之后，出现了第二种改进的算法，就是单路排序。

单路排序（快)

从磁盘读取查询需要的所有列，按照order by列在buffer对E们西亿HR但是它会使用更多的空间，因为它把每一效率更快一些，避免了第二次读取数据。并且把随机Io变成了顺序IO，行都保存在内存中了。

结论及引申出的问题

由于单路是后出的，总体而言好过双路

但是用单路有问题

• 在 sort_buffer 中，单路比多路要多占用更多空间，因为单路是把所有字段都取出，所以有可能取出的数据的总大小超出了 sort_buffer 的容量，导致每次只能取sort_buffer容量大小的数据，进行排序〈创建tmp文件，多路合并)，排完再取 sort_buffer 容量大小，再排......从而多次I/O。

• 单路本来想省一次I/o操作，反而导致了大量的I/0操作，反而得不偿失。

优化策略

1.尝试提高sort_buffer_size

不管用哪种算法，提高这个参数都会提高效率，要根据系统的能力去提高，因为这个参数是针对每个进程 (connection)的1M-8M之间调整。MySQL5.7，InnoDB存储引擎默认值是1048576字节，1MB。

mysql> SHOW VARIABLES LIKE '%sort_buffer_size%';
+-------------------------+---------+
| Variable_name           | Value   |
+-------------------------+---------+
| innodb_sort_buffer_size | 1048576 |
| myisam_sort_buffer_size | 8388608 |
| sort_buffer_size        | 262144  |
+-------------------------+---------+
3 rows in set (0.00 sec)

2.尝试提高max_length_for_sort_data

提高这个参数，会增加用改进算法的概率。

mysql> SHow VARIABLES LIKE '%max_length_for_sort_data%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| max_length_for_sort_data | 4096  |
+--------------------------+-------+
1 row in set (0.00 sec)

但是如果设的太高，数据总容量超出 sort_buffer_size 的概率就增大，明显症状是高的磁盘I/o活动和低的处理器使用率。如果需要返回的列的总长度大于 max_length_for_sort_data 使用双路算法，否则使用单路算法。1024-8192字节之间调整

3.Order by时 select* 是一个大忌。最好只Query需要的字段。 原因:

• 当Query的字段大小总和小于 max_length_for_sort_data ，而且排序字段不是 TEXT|BLOB 类型时，会用改进后的算法――单路排序，否则用老算法――多路排序。

• 两种算法的数据都有可能超出 sort_buffer_size 的容量，超出之后，会创建tmp文件进行合并排序，导致多次I/o，但是用单路排序算法的风险会更大一些，所以要提高 sort_buffer_size 。

2.GROUP BY优化

• group by 使用索引的原则几乎跟 order by 一致， group by 即使没有过滤条件用到索引，也可以直接使用索引。

• group by 先排序再分组，遵照索引建的最佳左前缀法则

• 当无法使用索引列，增大 max_length_for_sort_data 和 sort_buffer_size 参数的设置

• where效率高于having，能写在where限定的条件就不要写在having中了

• 减少使用 order by ，和业务沟通能不排序就不排序，或将排序放到程序端去做

• Order by 、 group by 、 distinct 这些语句较为耗费CPU，数据库的CPU资源是极其宝贵的。

• 包含了 order by 、 group by 、 distinct 这些查询的语句，where条件过滤出来的结果集请保持在1000行以内，否则SQL会很慢。

3.优化分页查询

一般分页查询时，通过创建覆盖索引能够比较好地提高性能。一个常见又非常头疼的问题就是 limit 2000000,10 ，此时需要MySQL排序前 2000010 记录，仅仅返回 2000000 - 2000010 的记录，其他记录丢弃，查询排序的代价非常大。

EXPLAIN SELECT * FROM student LIMIT 2088800,10;

优化思路一

在索引上完成排序分页操作，最后根据主键关联回原表查询所需要的其他列内容。

EXPLAIN SELECT * FROM student t, ( SELECT id FROM student ORDER BY id LIMIT 2000000,10) a WHERE t.id = a.id;

优化思路二(几乎没法用)

该方案适用于主键自增的表，可以把Limit查询转换成某个位置的查询。

EXPLAIN SELECT * FROM student WHERE id > 2080880 LIMIT 10;

不靠谱，生产中id可能会删除，查询的条件也不可能这么简单。

4. 优先考虑覆盖索引

4.1 什么是覆盖索引？

理解方式一： 索引是高效找到行的一个方法，但是一般数据库也能使用索引找到一个列的数据，因此它 不必读取整个行。毕竟索引叶子节点存储了它们索引的数据；当能通过读取索引就可以得到想要的数 据，那就不需要读取行了。一个索引包含了满足查询结果的数据就叫做覆盖索引。

理解方式二： 非聚簇复合索引的一种形式，它包括在查询里的 SELECT 、 JOIN 和 WHERE 子句用到的所有列 （即建索引的字段正好是覆盖查询条件中所涉及的字段）。

简单说就是， 索引列+主键 包含 SELECT 到 FROM 之间查询的列。

举例一: 覆盖索引长什么样子。 索引列+主键

#斯降之前的索引
DROP INDEX idx_age_stuno ON student ;
CREATE INDEX idx_age_name ON student (age , NAME);

EXPLAIN SELECT * FROM student WHERE age <>20;

EXPLAIN SELECT id, age , NAME FROM student WHERE age <> 28;

上述都使用到了声明的索引，下面的情况则不然，在查询列中多了一列classid，显示未使用到索引:

EXPLAIN SELECT id, age , NAME,classid FROM student WHERE age <> 28;

举例二：

EXPLAIN SELECT *FROM student WHERE NAME LIKE '%abc';