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

453 阅读17分钟

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

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

1.排序优化

1.1排序优化

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

回答:

在MySQL中,支持两种排序方式,分别是FileSortIndex排序。

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

优化建议:

  1. SQL中,可以在WHERE子句和ORDER BY子句中使用索引,目的是在WHERE子句中避免全表扫描,在ORDER BY子句避免使用FileSort排序。当然,某些情况下全表扫描,或者FileSort排序不一定比索引慢。但总的来说,我们还是要避免,以提高查询效率。
  2. 尽量使用Index完成ORDER BY排序。如果WHERE和ORDER BY后面是相同的列就使用单索引列;如果不同 就使用联合索引。
  3. 无法使用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;

image-20220327154029455

过程二: 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 ;

image-20220327154234022

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

使用覆盖索引试试看

image-20220327154426669

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

增加limit 条件

image-20220327154631330

增加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;
​

image-20220327163331675

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

image-20220327163436260

这里第一条排序走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;

image-20220327170746020

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;

image-20220327171114961

方案二:尽量让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;

image-20220327171516492

下面这个方案虽然使用了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_datasort_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;

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

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

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

image-20220327181228362

不靠谱,生产中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;

image-20220327194911745

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

image-20220327195102695

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

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

image-20220327195221475

举例二:

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

image-20220327195413811

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

image-20220327195610323

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

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

image-20220327195812263

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

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

image-20220327181808091

以及

image-20220327181820437

如果使用的是index1(即email整个字符串的索引结构),执行顺序是这样的:

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

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

如果使用的是index2(即email(6)索引结构),执行顺序是这样的:

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

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

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

结论:

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

索引下推下次再讲咯

交流学习

最后,如果这篇文章对你有所启发,请帮忙转发给更多的朋友,让更多人受益!如果你有任何疑问或想法,欢迎随时留言与我讨论,我们一起学习、共同进步。别忘了关注我,我将持续分享更多有趣且实用的技术文章,期待与你的交流!