前往小程序,Get更优阅读体验!
立即前往
首页
学习
活动
专区
工具
TVP
发布
社区首页 >专栏 >Mysql进阶优化篇05——子查询的优化和排序优化

Mysql进阶优化篇05——子查询的优化和排序优化

作者头像
用户10127530
发布2022-10-26 18:51:11
1.9K0
发布2022-10-26 18:51:11
举报
文章被收录于专栏:半旧的技术栈半旧的技术栈

前 言 🍉 作者简介:半旧518,长跑型选手,立志坚持写10年博客,专注于java后端 🍌 专栏简介:mysql基础、进阶,主要讲解mysql数据库sql刷题、进阶知识,包括索引、数据库调优、分库分表等 🌰 文章简介:本文将介绍JOIN语句的底层原理,建议收藏备用。 🍓 相关推荐:

文章目录

1.子查询的优化

MySQL 从 4.1 版本开始支持子查询,使用子查询可以进行 SELECT 语句的嵌套查询,即一个 SELECT 查询的结果作为另一个 SELECT 语句的条件。子查询可以一次性完成很多逻辑上需要多个步骤才能完成的操作 。

子查询是 MySQL 的一项重要的功能,可以帮助我们通过一个 SQL 语句实现比较复杂的查询。但是,子查询的执行效率不高。

原因:

执行子查询时,MySQL 需要为内层查询语句的查询结果建立一个临时表 ,然后外层查询语句从临时表中查询记录。查询完毕后,再撤销这些临时表 。这样会消耗过多的 CPU 和 IO 资源,产生大量的慢查询。

子查询的结果集存储的临时表,不论是内存临时表还是磁盘临时表都 不会存在索引 ,所以查询性能会受到一定的影响。

对于返回结果集比较大的子查询,其对查询性能的影响也就越大。

在 MySQL 中,可以使用连接(JOIN)查询来替代子查询。 连接查询 不需要建立临时表,其 速度比子查询要快,如果查询中使用索引的话,性能就会更好。

举例1:查询学生表中是班长的学生信息

使用子查询

代码语言:javascript
复制
#创建班级表中班长的索引
CREATE INDEX idx_monitor ON class(monitor);

#查询班长的信息
EXPLAIN SELECT * FROM student stu1
    WHERE stu1.`stuno` IN (
    SELECT monitor
    FROM class c
    WHERE monitor IS NOT NULL
);

本地测试执行实践0.036s。

推荐:使用多表查询,本地测试时间仅仅为0.016s。

代码语言:javascript
复制
EXPLAIN SELECT stu1.* FROM student stu1 JOIN class c 
ON stu1.`stuno` = c.`monitor`
WHERE c.`monitor` IS NOT NULL;

举例2:取所有不为班长的同学

不推荐

代码语言:javascript
复制
#查询不为班长的学生信息
EXPLAIN SELECT SQL_NO_CACHE a.* 
FROM student a 
WHERE  a.stuno  NOT  IN (
            SELECT monitor FROM class b 
            WHERE monitor IS NOT NULL);

推荐

代码语言:javascript
复制
EXPLAIN SELECT SQL_NO_CACHE a.*
FROM  student a LEFT OUTER JOIN class b 
ON a.stuno =b.monitor
WHERE b.monitor IS NULL;

🌹结论:尽量不要使用 NOT IN 或者 NOT EXISTS,用 LEFT JOIN xxx ON xx WHERE xx IS NULL 替代

2 排序优化

2.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 方式进行调优。

2.2 测试

先准备下,调用存储过程删除student,和class表上的索引。

代码语言:javascript
复制
CALL proc_drop_index('mysql', 'student')
CALL proc_drop_index('mysql', 'class')

现在进行排序查询。

代码语言:javascript
复制
EXPLAIN SELECT SQL_NO_CACHE * FROM student ORDER BY age,classid;  

很明显是using filesort.

创建索引。但是不加limit限制,索引失效。

代码语言:javascript
复制
CREATE  INDEX idx_age_classid_name ON student (age,classid,NAME);

#不限制,索引失效
EXPLAIN  SELECT SQL_NO_CACHE * FROM student ORDER BY age,classid; 

这是优化器通过计算发现,这里需要回表的数据量特别大,使用索引的性能代价反而比不上不用索引的。

再来,不需要回表。

代码语言:javascript
复制
# 会使用索引 (覆盖索引)
EXPLAIN  SELECT SQL_NO_CACHE age,classid,name,id FROM student ORDER BY age,classid;  

再来。限制排序返回的结果数量,可以使用索引。

代码语言:javascript
复制
EXPLAIN  SELECT SQL_NO_CACHE * FROM student ORDER BY age,classid LIMIT 10; 

再来,order by 时顺序错误,索引失效

代码语言:javascript
复制
#创建索引age,classid,stuno
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 时规则不一致, 索引失效 (顺序错,不索引;方向反,不索引)

代码语言:javascript
复制
# 失效,因为age是降序排序,但是索引是升序排序
EXPLAIN  SELECT * FROM student ORDER BY age DESC, classid ASC LIMIT 10;

# 失效
EXPLAIN  SELECT * FROM student ORDER BY classid DESC, NAME DESC LIMIT 10;

# 失效
EXPLAIN  SELECT * FROM student ORDER BY age ASC,classid DESC LIMIT 10; 

# 可以,这是因为order by和classid在使用时都是降序查找的,统一了反而被优化器优化可以使用索引了
EXPLAIN  SELECT * FROM student ORDER BY age DESC, classid DESC LIMIT 10;

再来。无过滤,不索引。下面执行结果都是和优化器的优化有关,大家可以自己验证思考。

代码语言:javascript
复制
# 可以
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;

CREATE INDEX idx_cid ON student(classid);

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

💡小结 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 /对于排序来说,多个相等条件也是范围查询/

2.3 案例实战

下面我们通过一个案例来实战filesort和index两种排序。对ORDER BY子句,尽量使用 Index 方式排序,避免使用 FileSort 方式排序。

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

先删除以前的索引。再测试如下sql。

代码语言:javascript
复制
CALL proc_drop_index(`mysql`,`student`)
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age = 30 AND stuno <101000 ORDER BY NAME ;

此时显然使用的是filesort进行排序。

优化思路:

方案一:为了去掉 filesort 我们可以创建特定索引

代码语言:javascript
复制
#创建新索引
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 的过滤条件和排序使用上索引

建一个三个字段的组合索引:

代码语言:javascript
复制
DROP INDEX idx_age_name ON student;

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 ;

此时又使用了filesort,这是为什么呢?这是因为此时filesort的性能更高。不信你可以对比执行下,看看时间的区别。结果竟然有 filesort 的 sql 运行速度,超过了已经优化掉 filesort的 sql,而且快了很多,几乎一瞬间就出现了结果。看来优化器做的工作真的特别灵活。

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

结论: 两个索引同时存在,mysql 自动选择最优的方案。(对于这个例子,mysql 选择 idx_age_stuno_name)。但是,随着数据量的变化,选择的索引也会随之变化的 。 当【范围条件】和【group by 或者 order by】的字段出现二选一时,优先观察条件字段的过滤数量,如果过滤的数据足够多,而需要排序的数据并不多时,优先把索引放在范围字段上。反之,亦然。

2.4 filesort的算法

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

  • 双路排序(慢)

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

从磁盘取排序字段,在 buffer 进行排序,再从 磁盘取其他字段 。

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

  • 单路排序(快)

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

  • 结论及引申出的问题

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

但是用单路有问题

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

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

优化策略

尝试提高 sort_buffer_size

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

image-20220701143536810

尝试提高 max_length_for_sort_data

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

SHOW VARIABLES LIKE’%max_length_for_sort_data%';

SQL 复制

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

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

当 Query 的字段大小综合小于 max_length_for_sort_data,而且排序字段不是 TEXT|BLOG 类型时,会改进后的算法——单路排序,否则用老算法——多路排序。

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

  1. 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 会很慢。

工欲善其事,必先利其器”。要想成为工作上的数据库高手,面试时的题霸,独步江湖,就必须拿到一份"武林秘籍"。

我个人强推牛客网:找工作神器|大厂java面经汇总|超全笔试题库

推荐理由: 1.刷题题库,题目特别全面,刷爆笔试再也不担心

链接: 找工作神器|大厂java面经汇总|超全笔试题库

2.超全面试题、成体系、高质量,还有AI模拟面试黑科技

链接: 工作神器|大厂java面经汇总|超全笔试题库

3.超多面经,大厂面经很多

4.内推机会,大厂招聘特别多

链接: 找工作神器|大厂java面经汇总|超全笔试题库

5.大厂真题,直接拿到大厂真实题库,而且和许多大厂都有直接合作,题目通过率高有机会获得大厂内推资格。

链接: 找工作神器|大厂java面经汇总|超全笔试题库

本文参与 腾讯云自媒体同步曝光计划,分享自作者个人站点/博客。
原始发表:2022-08-16,如有侵权请联系 cloudcommunity@tencent.com 删除

本文分享自 作者个人站点/博客 前往查看

如有侵权,请联系 cloudcommunity@tencent.com 删除。

本文参与 腾讯云自媒体同步曝光计划  ,欢迎热爱写作的你一起参与!

评论
登录后参与评论
0 条评论
热度
最新
推荐阅读
目录
  • 文章目录
  • 1.子查询的优化
  • 2 排序优化
    • 2.1 排序优化
      • 2.2 测试
        • 2.3 案例实战
          • 2.4 filesort的算法
          相关产品与服务
          云数据库 MySQL
          腾讯云数据库 MySQL(TencentDB for MySQL)为用户提供安全可靠,性能卓越、易于维护的企业级云数据库服务。其具备6大企业级特性,包括企业级定制内核、企业级高可用、企业级高可靠、企业级安全、企业级扩展以及企业级智能运维。通过使用腾讯云数据库 MySQL,可实现分钟级别的数据库部署、弹性扩展以及全自动化的运维管理,不仅经济实惠,而且稳定可靠,易于运维。
          领券
          问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档