⭐⭐⭐ Spring Boot 项目实战	⭐⭐⭐ Spring Cloud 项目实战
《Dubbo 实现原理与源码解析 —— 精品合集》	《Netty 实现原理与源码解析 —— 精品合集》
《Spring 实现原理与源码解析 —— 精品合集》	《MyBatis 实现原理与源码解析 —— 精品合集》
《Spring MVC 实现原理与源码解析 —— 精品合集》	《数据库实体设计合集》
《Spring Boot 实现原理与源码解析 —— 精品合集》	《Java 面试题 + Java 学习指南》

摘要: 原创出处 juejin.cn/post/7442342414533640233 「超爱吃士力架」欢迎转载，保留摘要，谢谢！

🙂🙂🙂关注微信公众号：【芋道源码】有福利：

RocketMQ / MyCAT / Sharding-JDBC 所有源码分析文章列表

RocketMQ / MyCAT / Sharding-JDBC 中文注释源码 GitHub 地址

您对于源码的疑问每条留言都将得到认真回复。甚至不知道如何读源码也可以请教噢。

新的源码解析文章实时收到通知。每周更新一篇左右。

认真的源码交流微信群。

在数据量庞大的场景下，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这个条件，如果没有用到索引的话，要对几万条的数据进行扫描，这是非常消耗性能的，所以索引放在这个字段上性价比最高，是最优选择。

结论:

两个索引同时存在，mysql自动选择最优的方案。(对于这个例子mysql选择idx_age_stuno_name)。但是，随着数据量的变化，选择的索引也会随之变化的。
当【范围条件】和【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';

CREATE INDEX idx_age_name ON student (age , NAME);
EXPLAIN SELECT id, age ,NAME FROM student WHERE NAME LIKE '%abc ';

# 索引覆盖失效
EXPLAIN SELECT id, age ,NAME,classid FROM student WHERE NAME LIKE '%abc ';

查询多了classid，结果是未使用到索引

之前有说过，不等于与左模糊会导致索引失效。但是这里为什么又用上了呢？原因是优化器发现，数据已经都在索引了。直接遍历索引就可以返回数据。。而遍历索引，肯定是比遍历全表数据量少的。这样IO就可以更少。

一切都是成本的考量。

4.2 覆盖索引的利弊

好处：

1. 避免Innodb表进行索引的二次查询（回表）

Innodb是以聚集索引的顺序来存储的，对于Innodb来说，二级索引在叶子节点中所保存的是行的主键信息，如果是用二级索引查询数据，在查找到相应的键值后，还需通过主键进行二次查询才能获取我们真实所需要的数据。

在覆盖索引中，二级索引的键值中可以获取所要的数据，避免了对主键的二次查询，减少了IO操作，提升了查询效率。

2. 可以把随机IO变成顺序IO加快查询效率

由于覆盖索引是按键值的顺序存储的，对于I0密集型的范围查找来说，对比随机从磁盘读取每一行的数据IO要少的多，因此利用覆盖索引在访问时也可以把磁盘的随机读取的IO转变成索引查找的顺序IO。

3.数据在索引里面数据量少更紧凑

索引肯定是比原来的数据，数据量少。。这样就可以减少IO。

由于覆盖索引可以减少树的搜索次数，显著提升查询性能，所以使用覆盖索引是一个常用的性能优化手段。

弊端：

索引字段的维护总是有代价的。因此，在建立冗余索引来支持覆盖索引时就需要权衡考虑了。这是业务 DBA，或者称为业务数据架构师的工作。

5. 如何给字符串添加索引

有一张教师表，表定义如下：

create table teacher(
ID bigint unsigned primary key,
email varchar(64),
...
)engine=innodb;

讲师要使用邮箱登录，所以业务代码中一定会出现类似于这样的语句：

mysql> select col1, col2 from teacher where email='xxx';

如果email这个字段上没有索引，那么这个语句就只能做全表扫描。

5.1 前缀索引

MySQL是支持前缀索引的。默认地，如果你创建索引的语句不指定前缀长度，那么索引就会包含整个字符串

mysql> alter table teacher add index index1(email);
#或
mysql> alter table teacher add index index2(email(6))

这两种不同的定义在数据结构和存储上有什么区别呢？下图就是这两个索引的示意图

以及

如果使用的是index1（即email整个字符串的索引结构），执行顺序是这样的：

从index1索引树找到满足索引值是’`zhangssxyz@xxx.com`’的这条记录，取得ID2的值；
到主键上查到主键值是ID2的行，判断email的值是正确的，将这行记录加入结果集；
取index1索引树上刚刚查到的位置的下一条记录，发现已经不满足`email='zhangssxyz@xxx.com’`的条件了，循环结束。

这个过程中，只需要回主键索引取一次数据，所以系统认为只扫描了一行。

如果使用的是index2（即email(6)索引结构），执行顺序是这样的：

从index2索引树找到满足索引值是’zhangs’的记录，找到的第一个是ID1；
到主键上查到主键值是ID1的行，判断出email的值不是’`zhangssxyz@xxx.com`’，这行记录丢弃；
取index2上刚刚查到的位置的下一条记录，发现仍然是’zhangs’，取出ID2，再到ID索引上取整行然后判断，这次值对了，将这行记录加入结果集；
重复上一步，直到在idxe2上取到的值不是’zhangs’时，循环结束。

也就是说使用前缀索引，定义好长度，就可以做到既节省空间，又不用额外增加太多的查询成本。前面已经讲过区分度，区分度越高越好。因为区分度越高，意味着重复的键值越少。

5.2 前缀索引对覆盖索引的影响

结论：

使用前缀索引就用不上覆盖索引对查询性能的优化了，这也是你在选择是否使用前缀索引时需要考虑的一个因素。

芋道源码 —— 纯源码解析博客

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