使用索引优化提升排序、GROUP BY和分页性能

欧米噶小伙

于 2025-02-26 00:03:23 发布

阅读量736

点赞数 20

CC 4.0 BY-SA版权

文章标签： android java 开发语言 sql

本文链接：https://blog.youkuaiyun.com/weixin_57256621/article/details/145705773

使用索引优化提升排序、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。但是可以建立联合索引。

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 ';

在这里插入图片描述

之前有说过，不等于与左模糊会导致索引失效。但是这里为什么又用上了呢？原因是优化器发现，数据已经都在索引了。直接遍历索引就可以返回数据。。而遍历索引，肯定是比遍历全表数据量少的。这样IO就可以更少。一切都是成本的考量。
4.2 覆盖索引的利弊
好处：1. 避免Innodb表进行索引的二次查询（回表）Innodb是以聚集索引的顺序来存储的，对于Innodb来说，二级索引在叶子节点中所保存的是行的主键信息，如果是用二级索引查询数据，在查找到相应的键值后，还需通过主键进行二次查询才能获取我们真实所需要的数据。在覆盖索引中，二级索引的键值中可以获取所要的数据，避免了对主键的二次查询，减少了IO操作，提升了查询效率。
2. 可以把随机IO变成顺序IO加快查询效率由于覆盖索引是按键值的顺序存储的，对于I0密集型的范围查找来说，对比随机从磁盘读取每一行的数据IO要少的多，因此利用覆盖索引在访问时也可以把磁盘的随机读取的IO转变成索引查找的顺序IO。
3.数据在索引里面数据量少更紧凑索引肯定是比原来的数据，数据量少。。这样就可以减少IO。由于覆盖索引可以减少树的搜索次数，显著提升查询性能，所以使用覆盖索引是一个常用的性能优化手段。弊端：索引字段的维护总是有代价的。因此，在建立冗余索引来支持覆盖索引时就需要权衡考虑了。这是业务 DBA，或者称为业务数据架构师的工作。